ics321-20090910-relational2

ics321-20090910-relational2 - ICS 321 Fall 2009 The...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ICS 321 Fall 2009 The Relational Model (ii) Asst. Prof. Lipyeow Lim Information and Computer Science Department University of Hawaii at Manoa 9/10/2009 1 Lipyeow Lim -- University of Hawaii at Manoa Internet Book Store Example 9/10/2009 Lipyeow Lim -- University of Hawaii at Manoa 2 Books Customers Orders Isbn title author qty price year qty ordertime shipdate cardnum cid name address Logical DB Design: ER to Relational • Entity sets to tables: CREATE TABLE Employees (ssn CHAR (11), name CHAR (20), lot INTEGER , PRIMARY KEY (ssn) ) Employees ssn name lot Relationship Sets to Tables • In translating a relationship set to a relation, attributes of the relation must include: – Keys for each participating entity set (as foreign keys). • This set of attributes forms a superkey for the relation. – All descriptive attributes. CREATE TABLE Works_In( ssn CHAR (11), did INTEGER , since DATE , PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments ) Review: Key Constraints • Each dept has at most one manager, according to the key constraint on Manages. Translation to relational model? Many-to-Many 1-to-1 1-to Many Many-to-1 dname budget did since lot name ssn Manages Employees Departments Translating ER Diagrams with Key Constraints • Map relationship to a table: – Note that did is the key now! – Separate tables for Employees and Departments. • Since each department has a unique manager, we could instead combine Manages and Departments. CREATE TABLE Manages( ssn CHAR(11) , did INTEGER , since DATE , PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11) , since DATE , PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees ) Review: Participation Constraints • Does every department have a manager? – If so, this is a participation constraint : the participation of Departments in Manages is said to be total (vs. partial ) ....
View Full Document

This note was uploaded on 11/15/2010 for the course ICS 321 taught by Professor Lim during the Fall '09 term at University of Hawaii, Manoa.

Page1 / 20

ics321-20090910-relational2 - ICS 321 Fall 2009 The...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online