What is a schema relation ?
A relation is a table,it has attributes (columns) and tuples(rows). A schema relation is a set of relation name (table name) and attributes name. There could be the datatype of the columns.
For example : Courses (courseID: integer,courseName:char).
What is a relational database schema ?
A relational database schema is a set of tables and relationships that make up a relational database.
For example :
Course (courseID,courseName)
Student (studentID,studentName,studentPhone,courseID).
So, a relational database schema can be seen as a set of schema relations and their relationships.
There are no duplicate tuples within a relation. Therefore, we need relational keys to uniquely identify each tuple in a relation. Some type of relational keys are explained below.
Candidate key is an attribute or a set of attributes that uniquely identifies a tuple in a relation. Consider the Student relation above, studentName or studentPhone can not be a candidate key because there could be more than one student with the same name or using the same phone number. In this case, we can use studentName and studentPhone (a key consist of more than one attributes called a composite key) or studentID as a candidate key.
Primary key is the candidate key that is selected to uniquely identify tuples within a relation. With the Student relation,we selected the candidate key studentID to be the primary key,and the composite key consist of studentName and studentPhone will be a alternate key.
To ensure the data in database is accurate,there must be integrity contrainsts . And there are two principal integrity contrainsts for the relational model.
The first one is the entity integrity applies to a primary key. It states that an attribute of a primary key can not be null in a base relation . Why on earth ? If we allow null in any part of a primary key,that means, we do not need these parts of the primary key,so why we include them in the primary key.
The second one is the referential integrity applies to the foreign key . If a foreign key exists in a relation, either foreign key value must match a candidate key value of some tuples in its home relation or the foreign key value must be wholly null . Consider this relational database schema :
Course (courseID,courseName)
Student (studentID,studentName,studentPhone,courseID)
In this case ,courseID is the candidate key in Course and foreign key in Student. Unless there is already a record for courseID ID11, it means that the course ID11 exists, it should not be possible to create a student with courseID ID11. However, we can insert a student with a NULL courseID,because that student has not followed any course yet.
What a base relation and a view is .
A base relation is a conceptual level relation.
A view is a virtual, derived relation from one or more base relation.
Data modification can be done in a base relation,not a view.
| CourseID | courseName |
| ID00 | ABC |
| ID01 | DEF |
| studentID | studentName | studentPhone | courseID |
| S1 | Van | 84 909 072 064 | ID00 |
| S2 | Paul | 1 800 1999 | |
| S3 | Dean | 1800 1901 | ID01 |
Two Base Relations : Course & Student
| studentID | studentName | studentPhone | courseID | courseName |
| S1 | Van | 84 909 072 064 | ID00 | ABC |
| S3 | Dean | 1 800 1901 | ID01 | DEF |
A View made of two base relations : course & student
No comments:
Post a Comment