1 / 9

Translating ER Schema to Relational Model: Simple Algorithm

Learn a simple algorithm to translate Entity-Relationship schemas to relational models with examples and key concepts.

seann
Download Presentation

Translating ER Schema to Relational Model: Simple Algorithm

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. Translating ER Schema to Relational Model CS3431

  2. Simple Algorithm • Entity type E  Relation E’ • Attribute of E  Attribute of E’ • Key for E  Primary Key for E’ cs3431

  3. Simple Algorithm • For relationship type R between E1, E2, …, En • Create separate relation R’ • Attributes of R’ are : • primary keys of E1, E2, …, En and attributes of R • Primary Key for R’ is defined as: • Primary key for R’ = primary keys for E1, E2, …, En • Foreign Keys: • Define “appropriate” foreign keys from R’ to E1, E2, …, En cs3431

  4. Simple algorithm: Example 1 Person (pNumber, pName) Dept (dNumber, dName) WorksFor (pNumber, dNumber, years) PRIMARY KEY (Person) = <pNumber> PRIMARY KEY (Dept) = <dNumber> PRIMARY KEY (WorksFor) = <pNumber, dNumber> FOREIGN KEY WorksFor (pNumber) REFERENCES Person (pNumber) FOREIGN KEY WorksFor (dNumber) REFERENCES Dept (dNumber) cs3431

  5. Simple Algorithm: Example 2 Supplier (sName, sLoc) Consumer (cName, cLoc) Product (pName, pNumber) Supply (supplier, consumer, product, price, qty) PRIMARY Key (Supplier) = <sName> PRIMARY Key (Consumer) = <cName> PRIMARY Key (Product) = <pName> PRIMARY Key (Supply) = <supplier, consumer, product> FOREIGN KEY Supply (supplier) REFERENCES Supplier (sName) FOREIGN KEY Supply (consumer) REFERENCES Consumer (cName) FOREIGN KEY Supply (product) REFERENCES Product (pName) cs3431

  6. Simple Algorithm: Example 3 Part (pName, pNumber) Contains (superPart, subPart, quantity) PRIMARY KEY (Part) = <pNumber> PRIMARY KEY (Contains) = <subPart,superPart> FOREIGN KEY Contains (superPart) REFERENCES Part (pNumber) FOREIGN KEY Contains (subPart) REFERENCES Part (pNumber) cs3431

  7. Refinement for Simple Mapping • Primary Key for R’ is defined as: • If the maximum cardinality of any Ei is 1, primary key for R’ = primary key for Ei cs3431

  8. Simple Algorithm: Example 3 Part (pName, pNumber) Contains (superPart, subPart, quantity) PRIMARY KEY (Part) = <pNumber> PRIMARY KEY (Contains) = <subPart> FOREIGN KEY Contains (superPart) REFERENCES Part (pNumber) FOREIGN KEY Contains (subPart) REFERENCES Part (pNumber) cs3431

  9. Summary So Far • Simple algorithm covers base case • Idea: “Each relationship type = separate relation” NEXT: • Let’s consider constraints • Let’s reduce number of relations cs3431

More Related