Relational Model #1

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; indivisible.
• The important issue is not what the domain itself is, but rather how we use domain elements in our database.
• ex) Phone number
• not splitting the value into a country code, an area code, and a local number,
• treating it as a nonatomic value
• The special value null is a member of every domain
• The null value causes complications in the definition of many operations.

Relation Schema and Instance

• If A1, A2, ..., An are attributes, R=(A1, A2, ..., An) is a relation schema.
• ex) Instructor = (ID, name, dept_name, salary)

Formally, given sets D1, D2, ..., Dn a relation r is a subset of D1*D2*D3...*Dn. Thus, a relation is a set of n-tuples (a1, a2, a3, ..., an) where ai∈Di

• The current values (relation instance) of a relation are specified by a table
• An element t of r is a tuple. represented by a row in a table

Database

• 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
• the need for null values
• Good schemas? => Normalization theory in Chapter 7!

Keys

• Let K⊆R
• K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R)
• ex) {ID} and {ID, name} are both superkeys of instructor.
• Superkey K is a candidate key if K is minimal
• ex) {ID} is a candidate ket for the relation named Instructor
• One of the candidate keys is selected to be the primary key.
• determined by the database administrator
• Foreign key
• A relation r1 may include among its attributes the primary key of another relation r2. This attributes the primary key of another relation r2. This attribute is called a foreign key from r1, referencing r2.
• ex) If there are 2 relations Instructor( ID, name, dept_name, salary) and Department( dept_name, building, budget), dept_name is a foreign key from Instructor, referencing department, since dept_name is the primary key of Department.
• In the schema diagram, foreign key dependencies appear as arrows from the foreign key attributes of the referencing relation to the primary key of the referenced relation.

Schema Diagram for University Data

• classroom( building, room number, capacity)
• department(dept_name, building, budget)
• course(course_ID, title, dept_name, credits)
• instructor(ID, name, dept_name, credits)
• section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
• teaches(ID, course_id, sec_id, semester, year, grade)
• student(ID, name, dept_name, tot_cred)
• takes(ID, name, dept_name, semester, year, grade)
• advisors(s_ID, i_ID)
• time_slot(time_slot_id, day, start_time, end_time)
• prereq(course_id, prereq_id)