1 / 28

Transforming E-R Diagrams into Relations

Transforming E-R Diagrams into Relations. Transforming E-R Diagrams into Relations. It is useful to transform the conceptual data model into a set of normalized relations Steps Represent entities Represent relationships Normalize the relations Merge the relations. 9. 2.

emerson-heath
Download Presentation

Transforming E-R Diagrams into Relations

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. Transforming E-R Diagrams into Relations Powered by DeSiaMore

  2. Transforming E-R Diagrams into Relations • It is useful to transform the conceptual data model into a set of normalized relations • Steps • Represent entities • Represent relationships • Normalize the relations • Merge the relations 9.2 Powered by DeSiaMore

  3. Transforming E-R Diagrams into Relations • Represent Entities • Each regular entity is transformed into a relation • The identifier of the entity type becomes the primary key of the corresponding relation • The primary key must satisfy the following two conditions • The value of the key must uniquely identify every row in the relation • The key should be nonredundant 9.3 Powered by DeSiaMore

  4. 9.4 Powered by DeSiaMore

  5. Powered by DeSiaMore

  6. Transforming E-R Diagrams into Relations • Represent Relationships • Binary 1:N Relationships • Add the primary key attribute (or attributes) of the entity on the one side of the relationship as a foreign key in the relation on the right side • The one side migrates to the many side 9.6 Powered by DeSiaMore

  7. 9.7 Powered by DeSiaMore

  8. Transforming E-R Diagrams into Relations • Binary or Unary 1:1 • Three possible options • Add the primary key of A as a foreign key of B • Add the primary key of B as a foreign key of A • Both 9.8 Powered by DeSiaMore

  9. Transforming E-R Diagrams into Relations • Represent Relationships (continued) • Binary and higher M:N relationships • Create another relation and include primary keys of all relations as primary key of new relation 9.9 Powered by DeSiaMore

  10. 9.10 Powered by DeSiaMore

  11. 9.11 Powered by DeSiaMore

  12. Powered by DeSiaMore

  13. Translating Weak Entity Sets • Weak entity set and identifying relationship set are translated into a single table. • When the owner entity is deleted, all owned weak entities must also be deleted. CREATE TABLE Dep_Policy ( pname CHAR(20), age INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE) Powered by DeSiaMore

  14. name ssn lot Employees Logical DB Design: ER to Relational • Entity sets to tables. CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn)) Powered by DeSiaMore

  15. Relationship Sets to Tables CREATE TABLE Works_In( ssn CHAR(1), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) • In translating a relationship set to a relation, attributes of the relation must include: • Keys for each participating entity set (as foreign keys). • This set of attributes forms a superkey for the relation. • All descriptive attributes. Powered by DeSiaMore

  16. Translating ER Diagrams with Key Constraints CREATE TABLE Manages( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) • Map relationship to a table: • Note that did is the key now! • Separate tables for Employees and Departments. • Since each department has a unique manager, we could insteadcombine Manages and Departments. CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees) Powered by DeSiaMore

  17. Translating ER-Diagrams to Table Definitions Powered by DeSiaMore

  18. Translating Entities birthday id Actor General Rule: • Create a table with the name of the Entity. • There is a column for each attribute • The key in the diagram is the primary key of the table name address Powered by DeSiaMore

  19. Translating Entities birthday id Actor create table Actor(id varchar2(20) primary key, name varchar2(40), birthday date, address varchar2(100)); name address Relation: Actor (id, name, birthday, address) Powered by DeSiaMore

  20. Translating Relationships (without constraints) title birthday id Film Actor General Rule: • Create a table with the name of the relationship • The table has columns for all of the relationship's attributes and for the keys of each entity participating in the relationship • What is the primary key of the table? • What foreign keys are needed? year Acted In name salary type address Powered by DeSiaMore

  21. Translating relationships (without constraints) title birthday id Film Actor year What would be the relation for ActedIn? How would you define the table for ActedIn? Acted In name salary type address Powered by DeSiaMore

  22. Translating Recursive Relationships (without constraints) manager id Employee Manages worker name address Relation: Actor (worker-id, manager-id) What would be the table definition? Powered by DeSiaMore

  23. Translating relationships(key constraints): Option 1 id Director Film General Rule for Option 1: • Same as without key constraints, except that the primary key is defined differently Directed title name year salary Powered by DeSiaMore

  24. Translating relationships(key constraints): Option 1 id Director Film Directed title create table Directed( id varchar2(20), title varchar2(40), salary integer, ) name year salary What primary and foreign keys are missing? Powered by DeSiaMore

  25. Translating relationships(key constraints): Option 2 id Director Film Directed title General Rule for Option 2: • Do not create a table for the relationship • Add information columns that would have been in the relationship's table to the table of the entity with the key constraint • What is the disadvantage of this method? • What is the advantage of this method? name year salary Powered by DeSiaMore

  26. Translating relationships(key constraints): Option 2 id Director Film Directed title create table Film( title varchar2(40), year integer, primary key (title), ) name year salary What 3 lines are missing? Powered by DeSiaMore

  27. address Translating ISA:Option 1 id Movie Person name ISA create table MoviePerson( ... ) create table Actor(id varchar2(20), picture bfile, primary key(id), foreign key (id) references MoviePerson)) create table Director(...) picture Actor Director Powered by DeSiaMore

  28. address Translating ISA:Option 2 id Movie Person name ISA No table for MoviePerson! create table Actor(id varchar2(20), address varchar2(100), name varchar2(20), picture blob, primary key(id)); create table Director(...) picture Actor Director Powered by DeSiaMore

More Related