Tom Kelliher, CS 318
Feb. 6, 2002
Read 5.5--7.
PostgreSQL lab, SQL DDL.
E/R example and limitations.
Steps in the design process:
These are not supported in the relational model. Reconciliation?
Key features:
Implications of set-valued attributes:
CREATE TABLE Person ( SSN INTEGER, Name CHAR(20), Address CHAR(50), Hobby (CHAR(10), PRIMARY KEY (SSN, Hobby) );
Steps in the design process:
Identify the related entities: Person likes ice cream.
Usually obvious, but what about: Employee reports to employee? Subordinate, supervisor roles.
Features:
What are the keys?
Key:
Not the primary key of the relation!
CREATE TABLE WorksIn ( Since DATE, ProfId INTEGER, DeptId CHAR(4), PRIMARY KEY (ProfId), FOREIGN KEY (ProfId) REFERENCES Professor (Id), FOREIGN KEY (DeptId) REFERENCES Department ); CREATE TABLE Married ( Date DATE, SSNHusband INTEGER, SSNWife INTEGER, PRIMARY KEY (SSNHusband), UNIQUE (SSNWife), -- Assume we dealt with Hobby so that SSN is now -- primary key in person. FOREIGN KEY (SSNHusband) REFERENCES Person (SSN), FOREIGN KEY (SSNWife) REFERENCES Person (SSN) );
Examples:
Or, use a trigger (don't have the keys we'd like --- transcript relation in student example). Performance tradeoffs.