Translating ER Schema to Relational Model CS 3431

Translating ER Schema to Relational Model CS 3431 1

Basic Mapping So Far l Simple algorithm covers base case l l Each entity type = separate relation Each relationship type = separate relation cs 3431 2

Next l l Let’s consider constraints Let’s reduce number of relations cs 3431 4

Decreasing the Number of Relations Technique 1 l 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. cs 3431 7

Example 1 If R contains entity type E whose max. cardinality is 1, then R may be represented as attributes of E. cs 3431 8

Example 1 Student (s. Number, s. Name, advisor, years) Professor (p. Number, p. Name) PRIMARY KEY (Student) = <s. Number> PRIMARY KEY (Professor) = <p. Number> FOREIGN KEY Student (advisor) REFERENCES Professor (p. Number) Question: Will Student. advisor attribute ever be NULL ? Answer: No ! So add the NOT NULL cs 3431 constraint. 9

Example 2 Person (p. Number, p. Name, works-in, years) Dept (d. Number, d. Name) PRIMARY KEY (Person) = <p. Number> PRIMARY KEY (Dept) = <d. Number> FOREIGN KEY Person (worksin) REFERENCES Dept (d. Number) What about NULL attributes ? Worksin and years may be null for a person cs 3431 10

Remember the Simple Algorithm: Example 3 Part (p. Name, p. Number) Contains (super. Part, sub. Part, quantity) PRIMARY KEY (Part) = <p. Number> PRIMARY KEY (Contains) = <super. Part, sub. Part> PRIMARY KEY (Contains) = < sub. Part> FOREIGN KEY Contains (super. Part) REFERENCES Part (p. Number) FOREIGN KEY Contains (sub. Part) REFERENCES Part (p. Number) cs 3431 11

Example 3: The Reduced Case. Part (p. Number, pname, has. Super. Part, quantity) PRIMARY KEY (Part) = <p. Number> FOREIGN KEY Part (has. Super. Part) REFERENCES Part (p. Number) Note: has. Super. Part indicates the superpart of a part, and it may be null cs 3431 12

Decreasing the Number of Relations Summary of Technique 1 l 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. l If cardinality of E is (1, 1), then add “not null” cs 3431 13

Decreasing Number of Relations Technique 2: Even more radical : l l l If relationship type R between E 1 and E 2 is “one-to-one” [1: 1], and the cardinality of E 1 or of E 2 is (1, 1), then we can combine everything into one 1 relation. cs 3431 14

Decreasing the Number of Relations Technique 2 - Method Details l l l Let us assume the cardinality of E 1 in R is (1, 1). Then we create one relation for entity E 2 And, we make all attributes of E 1 and of R to become attributes of E 2. cs 3431 15

Example 1 Student-BIG (s. Number, s. Name, p. Number, p. Name, years) PRIMARY KEY (Student) = <s. Number> ANY OTHER KEY ? CANDIDATE KEY (Student) = <p. Number> Note: p. Number, p. Name, and years can be null for students with no advisor cs 3431 16

Example 2 Student (s. Number, s. Name, p. Number, p. Name, years) PRIMARY KEY (Student) = <s. Number> CANDIDATE KEY (Student) = <p. Number> Note: p. Number cannot be null for any student. cs 3431 17

Decreasing Number of Relations Technique 2 l l For one-to-one relationship R with E cardinality (1, 1), then combine everything into 1 relation. While very compact, semantically may not be clearest choice. Therefore, not always recommended ! cs 3431 18

ER Model: Complex Attributes Composite Attribute: address sumer s. Name Student s. Age address street l city state Composite attribute in ER l Include an attribute for every component of the composite attribute. cs 3431 19

ER Model: Complex Attributes Multivalued Attribute: major Mapping Multi-valued attribute in ER: • Need separate relation for multi-valued attribute. • Identify appropriate attributes, keys & foreign key constraints. cs 3431 20

Multi-valued attribute Student (s. Number, s. Name, s. Age, street, city, state) PRIMARY KEY (Student) = <s. Number> Student. Major (s. Number, major) PRIMARY KEY (Student. Major) = <s. Number, major> FOREIGN KEY Student. Major (s. Number) REFERENCES Student (s. Number) cs 3431 21

Summary l Simple algorithms covers base case l Refinements: Reduce number of relations l Refinements: Consider constraints (not NULL) l Consider other ER constructs like complex and multi-valued attributes cs 3431 22