Database Relational Model

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)


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.