lec04 - 1 1/18/11 EECS 484: Database Management Systems,...

Info iconThis preview shows pages 1–5. 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
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 1/18/11 EECS 484: Database Management Systems, Kristen LeFevre 1 Logical Database Design: Mapping ER to Relational Chapter 3, Section 3.5 1/18/11 EECS 484: Database Management Systems, Kristen LeFevre 2 ER vs. Relational Model ER Model used for conceptual design Relational Model implemented by modern DBMS Important Step: Translate ER diagram to Relational schema Agenda for Today Translating each of the ER constructs In-class exercise (time permitting) 2 1/18/11 EECS 484: Database Management Systems, Kristen LeFevre 3 Recall ER Constructs Basic Constructs Entity Sets Relationship Sets Attributes (of entities and relationships) Additional Constructs ISA Hierarchies Weak Entities Aggregation Integrity Constraints Key constraints Participation constraints Overlap / Covering constraints for ISA hierarchies 1/18/11 EECS 484: Database Management Systems, Kristen LeFevre 4 Entity Sets to Tables CREATE TABLE Citizens (ssn CHAR(11), name CHAR(20), bday DATE, PRIMARY KEY (ssn) ) Citizens ssn name bday Can ssn have a null value? 3 1/18/11 EECS 484: Database Management Systems, Kristen LeFevre 5 Relationship Sets to Tables CREATE TABLE Votes( ssn CHAR(11), cid INTEGER, when DATE, PRIMARY KEY (ssn, cid), FOREIGN KEY (ssn) REFERENCES Citizens, FOREIGN KEY (cid) REFERENCES PR-Cands ) Relationship set -> Table Attributes: Participating entity set primary keys as foreign keys Descriptive attributes Generalizes to n-ary relationships Citizens ssn name bday PR-Cands cid waddr budget Votes when 1/18/11 EECS 484: Database Management Systems, Kristen LeFevre 6 Relationship Sets to Tables CREATE TABLE Represents( elected_ssn CHAR(11), cons_ssn CHAR(11), PRIMARY KEY (elected_ssn, cons_ssn), FOREIGN KEY (elected_ssn) REFERENCES Citizens(ssn), FOREIGN KEY (cons_ssn) REFERENCES Citizens(ssn) ) Citizens ssn name bday Represents Elected official Constituents 4 1/18/11 EECS 484: Database Management Systems, Kristen LeFevre...
View Full Document

Page1 / 11

lec04 - 1 1/18/11 EECS 484: Database Management Systems,...

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

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