1 / 10

Developing Data Models – Conversion Rules

Developing Data Models – Conversion Rules. Summary of Basic Conversion Rules. Each entity type becomes a table. Each 1-M relationship becomes a foreign key in the table corresponding to the child entity type (the entity type near the crow’s foot symbol).

ava-holt
Download Presentation

Developing Data Models – Conversion Rules

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Developing Data Models – Conversion Rules

  2. Summary of Basic Conversion Rules • Each entity type becomes a table. • Each 1-M relationship becomes a foreign key in the table corresponding to the child entity type (the entity type near the crow’s foot symbol). • Each M-N relationship becomes an associative table with a combined primary key. • Each identifying relationship adds a column to a primary key.

  3. Application of Basic Rules (I) CREATE TABLE Course (… PRIMARY KEY (CourseNo) ) CREATE TABLE Offering (… PRIMARY KEY OfferNo, FOREIGN KEY (CourseNo) REFERENCES Course )

  4. Application of Basic Rules (II) CREATE TABLE Enrollment (… PRIMARY KEY (StdSSN, OfferNo), FOREIGN KEY (StdSSN) REFERENCES Student, FOREIGN KEY OfferNo REFERENCES Offering )

  5. Application of Basic Rules (III) • Same conversion result as the previous slide • Different application of rules

  6. Generalization Hierarchy Rule • Mimic generalization hierarchy as much as possible • Each subtype table contains specific columns plus the primary key of its parent table. • Foreign key constraints for subtype tables • CASCADE DELETE option for referenced rows • Reduce need for null values • Need joins and outer joins to combine tables

  7. Generalization Hierarchy Example Employee table: EmpNo (PK) SalaryEmp table: EmpNo (PK), EmpNo (FK) HourlyEmp table: EmpNo (PK), EmpNo (FK)

  8. Optional 1-M Rule • Separate table for each optional 1-M relationship • Avoids null values • Requires an extra table and join operation • Controversial: in most cases 1-M rule is preferred

  9. Optional 1-M Example CREATE TABLE Teaches (… PRIMARY KEY (OfferNo) , FOREIGN KEY(OfferNo) REFERENCES Offering, FOREIGN KEY(FacSSN) REFERENCES Faculty )

  10. 1-1 Relationships CREATE TABLE Office (… PRIMARY KEY (OfficeNo) , FOREIGN KEY(EmpNo) REFERENCES Employee, UNIQUE (EmpNo) )

More Related