250 likes | 387 Views
Translating ER Schema to Relational Model. Basic Mapping So Far. Simple algorithms covers base case Idea: Each entity type = separate relation Each relationship type = separate relation. Simple Algorithm: Example 3. Part (pName, pNumber) Contains (superPart, subPart, quantity).
E N D
Basic Mapping So Far • Simple algorithms covers base case • Idea: • Each entity type = separate relation • Each relationship type = separate relation cs3431
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
Next • Let’s consider constraints • Let’s reduce number of relations cs3431
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
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
Decreasing the Number of Relations Technique 1 • If the relationship type R contains an entity type, say E, whose maximum cardinality is 1, then R may be represented as attributes of E. cs3431
Example 1 If the relationship type R contains an entity type, say E, whose maximum cardinality is 1, then R may be represented as attributes of E. cs3431
Example 1 Student (sNumber, sName, advisor, years) Professor (pNumber, pName) PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (Professor) = <pNumber> FOREIGN KEY Student (advisor) REFERENCES Professor (pNumber) Question: Will Student.advisor attribute ever be NULL ? Answer: No ! cs3431
Example 2 Person (pNumber, pName, dept, years) Dept (dNumber, dName) PRIMARY KEY (Person) = <pNumber> PRIMARY KEY (Dept) = <dNumber> FOREIGN KEY Person (dept) REFERENCES Dept (dNumber) What about NULL attributes ? Dept and years may be null for a person cs3431
Remember the 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
Example 3 Part (pNumber, pname, superPart, quantity) PRIMARY KEY (Part) = <pNumber> FOREIGN KEY Part (superPart) REFERENCES Part (pNumber) Note: superPart indicates the superpart of a part, and it may be null cs3431
Decreasing the Number of Relations Summary of Technique 1 • If the relationship type R contains an entity type, say E, whose maximum cardinality is 1, then R may be represented as attributes of E. • If cardinality of E is (1, 1), then no “new nulls” added • If cardinality of E is (0, 1) then may add “new nulls” cs3431
Decreasing the number of Relations Technique 2 • If relationship type R between E1 and E2 is one-to-one [1:1], • and the cardinality of E1 or of E2 is (1, 1), • then we can combine everything into 1 relation cs3431
Decreasing the number of Relations Technique 2 - Method Details • Let us assume the cardinality of E1 in R is (1, 1). • Then we create one relation for entity E2 • And, we move all attributes of E1 and for R to be attributes of E2. cs3431
Example 1 Student-BIG (sNumber, sName, pNumber, pName, years) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <pNumber> Note: pNumber, pName, and years can be null for students with no advisor cs3431
Example 2 Student (sNumber, sName, pNumber, pName, years) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <pNumber> Note: pNumber cannot be null for any student. cs3431
Decreasing the Number of Relations Technique 2 • If relationship type R between E1 and E2 is one-to-one [1:1], • and the cardinality of E1 or of E2 is (1, 1), • then we can combine everything into 1 relation • Not always recommended! Thus use with care ! • While very compact, semantically may not be clearest choice ! cs3431
ER Model: Complex Attributes Composite Attribute: address sumer sName Student sAge address street city state cs3431
Mapping details • Composite attribute in ER • Include an attribute for every component of the composite attribute. cs3431
ER Model: Complex Attributes Multivalued Attribute: major cs3431
Mapping details • Multi-valued attribute in ER • We need a separate relation for any multi-valued attribute. • Identify appropriate attributes, keys and foreign key constraints. cs3431
Example: Composite and Multi-valued attributes in ER Student (sNumber, sName, sAge, street, city, state) PRIMARY KEY (Student) = <sNumber> StudentMajor (sNumber, major) PRIMARY KEY (StudentMajor) = <sNumber, major> FOREIGN KEY StudentMajor (sNumber) REFERENCES Student (sNumber) cs3431
Summary • Simple algorithms covers base case • Refinements : Reduce number of relations • Refinements: Consider constraints (not NULL) • Consider other ER constructs like complex and multi-valued attributes cs3431