1 / 27

Transforming E-R Models into Relations

Transforming E-R Models into Relations. Though this be madness, yet there is method in it. Shakespeare. Class Outline. What is the primary key-foreign key basis of a relational database? How is an Entity-Relationship model converted to relational tables? Specifically, how are these converted:

jwest
Download Presentation

Transforming E-R Models 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. TransformingE-R Modelsinto Relations Though this be madness, yet there is method in it. Shakespeare

  2. Class Outline • What is the primary key-foreign key basis of a relational database? • How is an Entity-Relationship model converted to relational tables? Specifically, how are these converted: • 1:1, 1:M, M:N relationships binary relationships • ternary or recursive relationships • optional/ mandatory relationships • generalized hierarchies • weak entities • What are common data types provided by databases? • Describe some guidelines for designing tables. • Describe common database design flaws.

  3. Steps to E-R Transformation 1. Identify entities 2. Identify relationships 3. Determine relationship type 4. Determine level of participation 5. Assign an identifier for each entity 6. Draw completed E-R diagram 7. Deduce a set of preliminary skeleton tables along with a proposed primary key for each table (using cases provided) 8. Develop a list of all attributes of interest (not already listed and systematically assign each to a table in such a way to achieve a 3NF design (i.e., no repeating groups, no partial dependencies, and no transitive dependencies)

  4. A primary key should ideally be one attribute but can be a combination of attributes (multi-valued) that uniquely identify each entity instance. Each attribute is called a key attribute and together are a composite key. Primary Key The basis of a relational database is the ability to link instances from different entities if they share a common attribute (primary key, foreign key). • Each entity must have a primary key - an attribute that contains a unique value for each instance. The primary key cannot be null. The primary key is selected from all possible identifiers or candidate keys. • A primary key is a determinant of all other attributes in a given row (e.g., knowing a supplier number allows us to look up all of the other characteristics of that supplier (name, phone #, etc.)

  5. Requirements for a Relationship between tables (a) Primary key - every row of a table must have a unique identifier which cannot include null entries (b) Foreign key- an entry that must match a primary key value in a table to which it is related; may be null Dependent Table Parent Table primary key primary key foreign key

  6. Case 1. 1:1 relationship, both entities mandatory CASE 1a • When the relationship type of a binary relationship is 1:1 with the level of participation of both entities mandatory, generally only one table is required. • The primary key of this table can be the entity key from either entity (usually the strong entity’s primary key). • If the weaker entity is not part of any other relationships, it is probably an attribute of the stronger entity. EMPLOYEE JOB-DESCRIPTION 1 1 has a EMPLOYEE (EMP_NUM, EMP_LNAME,…, JOB_DESC)

  7. PLUMBER (PLUMBER_ID, PLUMBER_LNAME,…BUILDING_ID) BUILDING (BUILDING_ID, BUILDING_ADDRESS,...) Case 1. 1:1 relationship, both entities mandatory CASE 1b • When the relationship type of a binary relationship is 1:1 with the level of participation of both entities mandatory, two tables may be created if they are indeed two different entities • Place the foreign key (not null) in the strong entity (most frequently accessed) PLUMBER BUILDING 1 1 assigned

  8. Case 2. 1:1 relationship, one entity mandatory • When the relationship type of a binary relationship is 1:1 with the level of participation of one entity mandatory and the other optional, two tables are required. • There must be one table for each entity, and each entity must have a corresponding primary key. • Additionally, the primary key from the entity with mandatory participation must be added as a foreign key (not null) to the corresponding table of the entity with optional participation. 1 1 EMPLOYEE AUTO has EMPLOYEE (EMP_ID, EMP_LNAME, EMP_PHONE,…) AUTO (LIC_NUM, SERIAL_NUM, MAKE, MODEL,, …, EMP_ID)

  9. Case 3. 1:1 relationship, both entities optional • When the relationship type of a binary relationship is 1:1 with the level of participation of both entities optional, two tables are required. • There must be one table for each entity, and each entity must have a corresponding primary key. • Place the foreign key (null allowed) in the strong entity (most frequently accessed) . 1 1 TRAINER has EXERCISER EXERCISER (EXERCISER_ID, EXERCISER_LNAME, …TRAINER_ID) TRAINER (TRAINER_ID, TRAINER_LNAME, ...)

  10. Case 4. 1:M relationship, both entities mandatory • When the relationship type of a binary relationship is 1:M with level of participation of both sides mandatory, two tables are required. • There must be one table for each entity, and each entity must have a corresponding primary key. • The table corresponding to the M-side, will have among its attributes, the foreign key (not null) corresponding to the 1-side of the entity. 1 M EMPLOYEE PRODUCT checks EMPLOYEE (EMP_ID, EMP_DEPT, …) PRODUCT (PROD_ID, PROD_NAME, PROD_%FIBRE, EMP_ID... )

  11. Case 5. 1:M relationship,1-entity mandatory, M-entity optional • When the relationship type of a binary relationship is 1:M with the level of participation of the entity on the M-side optional, two tables are required. • There must be one table for each entity, and each entity must have a corresponding primary key. • The primary key from the entity on the 1-side must be added as a foreign key (not null) in the corresponding table of the entity on the M-side. 1 M MACHINE PARTS contains MACHINE (MACH_ID, MACH_NAME, MACH_DEPT, ...) PART (PART_ID, PART_NAME, PART_CATEGORY, …, MACH_ID)

  12. Case 6. 1:M relationship,1-entity optional, M-entity mandatory • When the relationship type of a binary relationship is 1:M with the level of participation of the entity on the 1-side optional, two tables are required. • There must be one table for each entity, and each entity must have a corresponding primary key. • The primary key from the entity on the 1-side must be added as a foreign key (null allowed) in the corresponding table of the entity on the M-side. 1 M BAND MUSICIAN accepts BAND (BAND_ID, BAND_NAME, MUSIC_TYPE...) MUSICIAN (MUSICIAN_ID, MUSICIAN_INSTRUMENT, … BAND_ID)

  13. Case 7. 1:M relationship, both entities optional • When the relationship type of a binary relationship is 1:M with the level of participation of both entities optional, two tables are required. • There must be one table for each entity, and each entity must have a corresponding primary key. • The primary key from the entity on the 1-side must be added as a foreign key (null allowed) in the corresponding table of the entity on the M-side. M 1 has PHYSIOTHERAPIST CLIENTS PHYSIOTHERAPIST (PT_ID, PT_LNAME, ...) CLIENT (CLIENT_ID, CLIENT_LNAME, CLIENT_OHIP#, …PT_ID)

  14. Case 8. M:N Relationships • When the relationship type of a binary relationship is M:N three tables are required: one for each entity, with the entity key from each entity serving as the primary key for the corresponding table, and one for the relationship. • The table corresponding to the relationship (intersection table) will have among its attributes the foreign keys (not null) from each entity. The combination of foreign keys may be the composite primary key for the relationship table. PATIENT M prescribed N MEDICATION PATIENT (PATIENT_ID, PATIENT_LNAME, PATIENT_PHYSICIAN,...) DRUG (DRUG_ID, DRUG_NAME, DRUG_MANUFACTURER, ...) PRESCRIPTION (PATIENT_ID, DRUG_ID, DOSAGE, DATE…) NOTE: The relationship may have its own attributes.

  15. Example of decomposing entitieswith a binary M:N relationship Students:Classes have an M:N relationship, therefore, decompose to three tables. Bridge table

  16. 1 M UNIT HOSPITAL contains Case 9. Decomposing Weak Entities • When the relationship type of a binary relationship is 1:M between an entity and its weak entity, two tables are required: one for each entity, with the entity key from each entity serving as the primary key for the corresponding table. • Additionally, the entity that has a dependency on the existence of another entity has a primary key that is partially or totally derived from the parent entity of the relationship. • Weak entities must be deleted when the strong entity is deleted. HOSPITAL (HOSP_ID, HOSP_NAME, HOSP_ADDRESS, ...) UNIT (HOSP_ID, UNIT_NAME, HEAD_NURSE, ...)

  17. CLIENT 1 INDIVIDUAL CORPORATE CLIENT CLIENT Considerations in a Generalized Hierarchy Entity CLIENT contains ClientNumber ClientName Address AmountDue SocialInsuranceNumber TaxIdentificationNumber ContactPerson Phone Problem: Too many NULL values Solution: Separate into CLIENT entity plus several subtypes

  18. CLIENT INDIVIDUAL CORPORATE 1 CLIENT CLIENT Case 10. Decomposing aGeneralization Hierarchy • To transform a subtype relationship, create a table for the parent entity and each of the child entities or subtypes • Move the associated attributes from the parent entity into the child table to which they correspond • From the parent entity take the entity key and add it as the primary key to the corresponding table for each child entity • In the event a table corresponding to a child entity already has a primary key then simply add the entity key from the parent entity as an attribute of the table corresponding to the child entity CLIENT (CLIENT_ID, AMOUNT_DUE, …) INDIVIDUAL_CLIENT (CLIENT_ID, SIN#, …) CORPORATE_CLIENT(CLIENT_ID, GST#, …)

  19. Transforming Recursive Relationships • 1:1 - create a foreign key field (duplicate values not allowed) that contains the domain of primary key • 1:M - create a foreign key field (duplicate values allowed) that contains the domain of primary key

  20. Transforming M:N Recursive Relationships • M:N - create a second relation that contains two foreign keys: one for each side of the relationship

  21. Decomposing Ternary relationships • When a relationship is three-way (ternary) four preliminary tables are required: one for each entity, with the entity key from each entity serving as the primary key for the corresponding table, and one for the relationship. • The table corresponding to the relationship will have among its attributes the entity keys from each entity • Similarly, when a relationship is N-way, N+1 preliminary tables are required.

  22. write publishes PUBLISHER (Pub_ID, ___, ___, ___, ___, …) BOOK (ISBN, Pub_ID, ___, ___, ___, ___, …) AUTHOR (Author_ID, ___, ___, ___, ___, …) WRITE (ISBN, Author_ID, ___, ___, ___, ___, …) Case 5 Case 8 PUBLISHER Library Database Example AUTHOR BOOK M N M 1

  23. Case 5 ENROLL (StudID, CourseID, ___, ...) STUDENT (StudID, ___, ___, FacID, …) COURSE (CourseID, ___, ___, ___, …) FACULTY (FacID, ___, ___, ___, ___, …) Case 8 Case 8 TEACH (FacID, CourseID,…) University Example M N takes STUDENT COURSE M M advises taught by N 1 FACULTY

  24. Characteristics of Fields • Each field within a table must have a unique name (avoid spaces and special characters). • Data within a field must be of the same data type. The following are common data types: • character (text or string) • memo (large character field) • integer (whole numbers for calculations) • number (values with decimals for calculations) • currency (formatted number) • logical or Boolean (true/false; 0,-1; yes/no) • date/ time (use computer’s internal calendar/clock) • graphic (picture)

  25. Guidelines for Ideal Table Design • Each table should represents a single theme or subject or entity or transaction • Tables should include primary keys that uniquely identify each record of each table • Avoid the use of smart keys that attempt to embed meaning into primary keys (keys should be meaningless) • A primary key should be a unique, random or sequential collection of alphabetic, numeric or alphanumeric characters • The domain of primary keys should be large enough to accommodate the identification of unique rows for the entire potential universe of records • Use the suffix ID in constructing primary keys to ensure they are readily identifiable • Tables should not contain any of the following: multipart fields, multivalued fields, calculated or derived fields or unnecessary duplicate fields • There should be a minimum amount of redundant data

  26. Flat file database Too much data Compound fields Missing keys Bad keys Missing relationships Unnecessary relationships Incorrect relationships Duplicate field names Cryptic field or table names Referential integrity Database Security Missing or incorrect business rules Missing or incorrect constraints Common Errors in Database Design John Paul Ashenfelter, “Common Database Mistakes”, May 26, 1999, <http://webreview.com/wr/pub/1999/03/26/feature/index3.html> (Oct 10, 1999).

  27. The Well-Structured Database • E-R modeling is top-down method of designing • Transforming an E-R model does not guarantee the best design (e.g., E-R model could be way off) • Best to transform E-R model and then check the design according to the Cases of normalization • Normalization is bottom-up method of designing a database • Use both approaches to develop a well-structured database

More Related