1 / 28

CMIS 450: Database Design Dr. Bijoy Bordoloi

CMIS 450: Database Design Dr. Bijoy Bordoloi. Transforming E/R Diagrams to Relations. Represent Entities. Each entity-type in E/R Diagram becomes a Relation (Table)

takoda
Download Presentation

CMIS 450: Database Design Dr. Bijoy Bordoloi

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. CMIS 450: Database DesignDr. Bijoy Bordoloi Transforming E/R Diagrams to Relations

  2. Represent Entities • Each entity-type in E/R Diagram becomes a Relation (Table) • The identifier of the entity-type becomes the PK of the corresponding relation (provided the PK meets all the necessary criteria discussed earlier) • Each non-key attribute of the entity-type becomes a non-key attribute of the relation

  3. Represent Entities • Question • What happens if you allow (non-identifying) multi-valued attributes in an entity-type? Can the identifier of an entity-type then also be the PK of the corresponding relation?

  4. Attributes Cardinality • Describes association between attribute and owner entity • Single-valued attribute has at most one value for each entity instance • Multi-valued attribute has many values for some entity instances

  5. Attributes Cardinality EMPLOYEE EMP-ID SS-NUM SEX EXTENSION**

  6. Multi-valued Attributes Either redefine the Primary Key* or make a separate relation with a foreign key taken from the superior entity * Refer to class notes on Relational Model – Primary Key

  7. Multi-valued Attributes • Multi-valued attributes become new characteristic tables 1 N EMPLOYEE EMPLOYEE HISTORY M M N N EMP# N EMP# 1 1 N DATE TITLE DATE E-NAME EMP# E-NAME TITLE

  8. Multi-valued Attributes MA TABLE EMP# E-NAME TITLE DATE EMP# E-NAME • Characteristic table is necessary because relational model is normalized NOT IN 2NF EMPLOYEE HISTORY EMP #DATETITLE E1 3-15-85 JANITOR E1 3-16-85 TECHNICIAN E1 3-17-85 MANAGER E2 2-23-87 EMPLOYER E2 4-19-88 DOGCATCHER

  9. Transforming Relationships Mapping Binary Relationships • One-to-Many – and One-to-One relationships become foreign keys. • Many-to-Many - Create a new relation with the primary keys of the two entities as its primary key

  10. Cont’d • In N-1 relationship, always place foreign key at ‘N’ side • In 1-1 relationship, usually place foreign key in smaller table (results in fewer null values)

  11. Representing a (1:N) Relationship ADDRESS CITY STATE ZIP NAME CUSTOMER NO. CUSTOMER DISCOUNT Places ORDER PROMISED DATE ORDER NO. ORDER DATE a) E-R Diagram

  12. Representing a (1:N) Relationship b) Relations

  13. Mapping a binary 1:1 relationship (a) Binary 1:1 relationship

  14. Figure (b) Resulting relations

  15. NULL Values in Foreign Keys • Whether or not a Foreign Key can have NULL values depends on the minimum cardinality of the concerned relationship • Minimum cardinality of 0 represented as NULL allowed for foreign key columns • Minimum cardinality of 1 represented as NULL disallowed for foreign key columns

  16. Sub-tables • Sub-entity - a subset of another entity, called the super-entity • Has 1-1 relationship to the super-entity (IS-A) • Are existence-dependent on the super-entity EMPLOYEE AGENT CLERK PROGRAMMER

  17. Sub-tables • Sub-entities become sub-tables in baseline logical design, but may merge with super-table in final design • Any kind of table may have sub-table EMPLOYEE ASSIGNMENT PROGRAMMER PERMANENT SYSTEM TEMPORARY

  18. Design of sub-table EMPLOYEE • Foreign key identifies super-table • Primary key-foreign key with no qualifying columns • Foreign key rules reflect existence • Nulls not allowed • Delete cascades • Update cascades PROGRAMMER

  19. Design of sub-table CREATE TABLE PROGRAMMER (EMP#, LANGUAGE, LEVEL) PRIMARY KEY (EMP#) FOREIGN KEY (EMP# IDENTIFIES EMPLOYEE NULLS NOT ALLOWED DELETE OF EMPLOYEE RESTRICTED UPDATE OF EMPLOYEE CASCADES) • Optional class column in super-table improves performance, but is redundant to data in sub-tables

  20. Representing IS-A Relationships CITY STATE ZIP NO. ROOMS STREET ADDRESS TYPICAL RENT PROPERTY IS-A IS-A BEACH PROPERTY MOUNTAIN PROPERTY BLOCKS TO BEACH STREET ADDRESS SKIING STREET ADDRESS CITY STATE ZIP CITY STATE ZIP (a) E-R diagram

  21. Representing IS-A Relationships (b) Relations

  22. Associative Tables • M-N relationship becomes associative table with two foreign keys

  23. Associative Tables M N EMPLOYEE PROJECT EMP# E-NAME P# P-NAME • Associative tables also known as association or intersection table N 1 1 N ASSIGNMENT EMPLOYEE PROJECT EMP# E-NAME EMP# P# P# P-NAME

  24. Transforming Relationships Mapping Unary Relationships What do you do when the relationship is unary? Rules are the same irrespective of whether the relationship is unary or binary. 1:N and 1:1 relationships be come foreign keys in the same table. M:N relationship becomes a separate (associative table).

  25. In-Class Exercise: Transform the following ERD to a relational structure FNAME LNAME SALARY SSN JOBCODE EMP# EMPLOYEE MARRIED-TO DIRECT WORK-IN DIVISION MANAGE BELONG-TO DEPARTMENT DIVNAME BLDG DIVNAME DEPTNAME DEPT#

  26. Reverse Engineering Modify the previous ERD to reflect the existence of the following five tables

  27. More Example Tables

  28. More Example Tables

More Related