Example of a Relation
#Attribute Types
- The set of allowed values for each attribute is called the domain of the attribute
- Attribute values are (normally) required to be atomic; that is, indivisible
- The special value null is a member of every attribute
- The null value causes complication in the definition of many operations
#Relation Schema and Instance
- $A_1, A_2, \cdots, A_n$ are attributes
- $R = (A_1, A_2, \cdots, A_n)$ is a relation schema Example: instructor = (ID, name, dept_name, salary)
- Formally, given sets $D_1 ,D_2, \cdots, D_n$ a relation r is a subset of $D_1 \times D_2 \times \cdots \times D_n$ Thus, a relation is a set of n-tuples($a_1, a_2, \cdots, a_n$) where each $a_i \in D_i$
- The current values(relation instance) of a relation are specified bu a table
- An element t of r is a tuple, represented by a row in a table
#Relations are Unordered
- Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
- This is on the logical design level - ordering can be defined on physical level
- A database consists of multiple relations
- Information about an enterprise is broken up into parts instructor, student, advisor
- Bad design: univ (instructor_id, name, dept_name, salary, student_id, ...)
results in
- repetition of information (e.g., two student have the same instructor)
- the need for full values (e.g., represent a student with no advisor)
- Normalization theory deals with how to design "good" relational schemas(What is good)
#Keys
superkey: K is a superkey of R if values for K are sufficient to identify a unique tuple of any possible relation r(R)
- Example: {id} and {id, name} are both superkeys of instructor.
- However, {name} is not a superkey.
candidate key: Superkey K is a candidate key if K is minimal
- Example: {id} is a candidate key for instructor
primary key One of the candidate keys is selected to be the primary key.
foreign key constraint: Value in one relation must appear in another
- Referencing relation
- Referenced relation
#Schema Diagram for University Database
Schema Diagram Example
This is a diagram of a relational design, it it not the same as the ER diagrams.