1 / 17

Conversion Procedure of the ER Model to the Relational Model

Conversion Procedure of the ER Model to the Relational Model. 2004. 9 한욱신. ER Model vs Relational Model. “Entity” Relation. Entity Set. +. “Relationship” Relation. Two Foreign Keys. 1. Relationship Set. N. OR. 1. Relationship Set. 1. Foreign Key. Two

duaa
Download Presentation

Conversion Procedure of the ER Model to the Relational Model

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. Conversion Procedure of the ER Model to the Relational Model 2004. 9 한욱신

  2. ER Model vs Relational Model “Entity” Relation Entity Set + “Relationship” Relation Two Foreign Keys 1 Relationship Set N OR 1 Relationship Set 1 Foreign Key Two Foreign Keys “Relationship” Relation + M Relationship Set N

  3. ER Model vs Relational Model n Foreign Keys “Relationship” Relation + Relationship Set N-ary attribute Attribute Key attribute Primary Key

  4. Conversion Procedure • For each strongentity setE • Create a relationR that includes all the simple attributes of E

  5. For each binary 1:1 relationship setR • Identify the relations S and T that corresponds to the entity sets participating in R • Choose one of the relations, say S, and include as foreign key in S the primary key of T (= P(T)) • Include all the simple attributes of the 1:1 relationship set R as attributes in S T S 1 S Foreign key P(T) R 1 T Primary Key P(T)

  6. For each binary 1:N relationship setR • Identify the relation S that represents the participating entity set at the N-side of the relationship set • Include as foreign key in S the primary key of the relation T that represents the other entity participating in R • Include all the simple attributes of the 1:N relationship set R as attributes in S

  7. For each binary M:N relationship setR • Create a new relation S to represent R • Include as foreign key attributes in S the primary keys of the relations that represent the participating entity sets; their combination will form the primary key of S • Also include all the simple attributes

  8. For each n-ary relationship set R, (n>2) • Create a new relation S to represent R • Include foreign key attributes in S the primary keys of the relations that represent the participating entity sets • Also include all the simple attributes

  9. Case 1>One-to-one binary relationship set a c d T R S b e CREATE TABLE T (a TYPE(a), b TYPE(b), PRIMARY KEY(a) ); CREATE TABLE S (d TYPE(d), e TYPE(e), a TYPE(a), PRIMARY KEY(e), FOREIGN KEY(a) REFERENCES T );

  10. Another mapping for case 1 a c d T R S b e CREATE TABLE T (a TYPE(a), b TYPE(b), e TYPE(e), PRIMARY KEY(a), FOREIGN KEY(e) REFERENCES S ); CREATE TABLE S (d TYPE(d), e TYPE(e), PRIMARY KEY(e), );

  11. Case 2>One-to-one binary relationship set + total participation a c d T R S b e CREATE TABLE T (a TYPE(a), b TYPE(b), PRIMARY KEY(a) ); CREATE TABLE S (d TYPE(d), e TYPE(e), a TYPE(a) NOT NULL, PRIMARY KEY(e), FOREIGN KEY(a) REFERENCES T {ON DELETE CASCADE | ON DELETE NO ACTION} );

  12. Case 2-2>One-to-one binary relationship set + total participation a c d CREATE TABLE S (d TYPE(d), e TYPE(e), a TYPE(a) NOT NULL,); T R S CREATE TABLE T (a TYPE(a), b TYPE(b), d TYPE(d) NOT NULL ); b e ALTER TABLE T ADD CONSTRAINT T_REF_S FOREIGN KEY (d) REFERENCES S(d) INITIALLY DEFERRED DEFERRABLE; ALTER TABLE S ADD CONSTRAINT S_REF_T FOREIGN KEY (a) REFERENCES T(a) INITIALLY DEFERRED DEFERRABLE;

  13. Case 3>One-to-many binary relationship set a c d T R S b e CREATE TABLE T (a TYPE(a), b TYPE(b), PRIMARY KEY(a) ); CREATE TABLE S (d TYPE(d), e TYPE(e), a TYPE(a), PRIMARY KEY(e), FOREIGN KEY(a) REFERENCES T );

  14. Case 4>One-to-many binary relationship set + total participation a c d T R S b e CREATE TABLE T (a TYPE(a), b TYPE(b), PRIMARY KEY(a) ); CREATE TABLE S (d TYPE(d), e TYPE(e), a TYPE(a) NOT NULL, PRIMARY KEY(e), FOREIGN KEY(a) REFERENCES (T) {ON DELETE CASCADE | ON DELETE NO ACTION} );

  15. Case 5>Many-to-many binary relationship set a c d T R S b e CREATE TABLE T (a TYPE(a), b TYPE(b), PRIMARY KEY(a) ); CREATE TABLE R (a TYPE(a), e TYPE(e), c TYPE(c), PRIMARY KEY(a,e), FOREIGN KEY(a) REFERENCE T, FOREIGN KEY(e) REFERENCE S); CREATE TABLE S (d TYPE(d), e TYPE(e), PRIMARY KEY(e) );

  16. Case 6)Many-to-Many binary relationship set + total participation a c d T R S b e CREATE TABLE S (d TYPE(d), e TYPE(e), a TYPE(a), c TYPE(c), PRIMARY KEY (e,a), FOREIGN KEY a REFERENCES T {ON DELETE CASCADE | ON DELETE NO ACTION} ); CREATE TABLE T (a TYPE(a), b TYPE(b), PRIMARY KEY(a) );

  17. Case 7>Weak entity set a c d T R S b e CREATE TABLE T (a TYPE(a), b TYPE(b), PRIMARY KEY(a) ); CREATE TABLE S (d TYPE(d), e TYPE(e), a TYPE(a) NOT NULL, c TYPE(c), PRIMARY KEY(a, e), FOREIGN KEY(a) REFERENCES (T) ON DELETE CASCADE );

More Related