Relational Database Modeling II

1. Relational Database Modeling II “We don’t live in a world of reality, we live in a world of perceptions.” J. Gerald Simmons

2. Class Outline • Using the ER methodology and notation discussed last day, create a completed entity-relationship model for: • a simple library database • a simple university faculty database • What are weak entities? • What is a generalized hierarchy? • What are the strengths and weaknesses of the Entity-Relationship model?

3. Steps in Entity-Relationship Modeling 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 rules 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. AUTHOR BOOK PUBLISHER writes AUTHOR BOOK publishes PUBLISHER BOOK E-R Method Example: Library Database • Step 1. Identify entity types • Step 2. Identify relationships

5. For PUBLISHER-publishes-BOOK, each publisher publishes zero, one, or more books and each book is published by exactly one publisher. The relationship type is one-to-many where BOOKS is on the many side and PUBLISHER is one the one side. 1 M publishes PUBLISHER BOOK Library Database (cont’d) • Step 3. Determine relationship type. Ask: • Each book is written by how many authors? Each author writes how many books? Each book may be authored by zero (anonymous), one, or more than one author and each author may write zero, one, or more than one book. The relationship type is many-to-many or: M N writes AUTHOR BOOK

6. M N writes AUTHOR BOOK publishes PUBLISHER BOOK (0, N) (0, N) • For the PUBLISHER-publishes-BOOK combination, the level of participation for PUBLISHER is optional (publishers do not necessarily have to publish a book, perhaps newsletters) and the level of participation for the BOOK side is mandatory (each book must have a publisher) 1 N (0, N) (1,1) Library Database (cont’d) • Step 4. Determine level of participation • Since each book does not have to be authored (anonymous) and since each author does not have to write a book (may make CD) the level of participation is optional for both sides of the relationship of AUTHOR-writes-BOOK combination

7. writes publishes PUBLISHER Library Database (cont’d) • Step 5. Assign an identifier for each entity • AuthorID, ISBN, PublisherID • Step 6. Draw completed E-R diagram AUTHOR BOOK ISBN, ... M N (1,1) AuthorID, ... N (0,N) (0,N) 1 (0,N) PublisherID, ...

8. writes (1,1) M publishes (0,N) 1 PublisherID, ... PUBLISHER Library Database (cont’d) • Step 6. Draw completed E-R diagram - resolve M:N relationships 1 M M 1 AUTHOR BOOK ISBN, ... (0,N) (0,N) (1,1) (1,1) AuthorID, ... AuthorID,ISBN, ...

9. FACULTY FACULTY STUDENT COURSE COURSE COURSE STUDENT • Step 2. Identify relationships teaches takes E-R Modeling: University Example • A database is to be set up to record information about faculty, the courses they teach, and the students who take courses. Some courses are taught by teams of faculty members. • Step 1. Identify entity types

10. F1 C1 F2 C2 F3 C3 F4 C4 F5 C5 F6 C6 S1 C1 S2 C2 S3 C3 S4 C4 S5 C5 S6 C6 University Example (cont’d) • Step 3. Determine relationship type. Ask: • Each faculty member teaches how many courses? • Each course is taught by how many faculty? • Each student takes how many courses? • Each course is taken by how many students? • Use occurrences diagram to visualize relationship between entities

11. For STUDENT-takes-COURSE each student enrols in one to six courses and each course is taken by zero or up to 30 students. This too is a many-to-many relationship. M N takes STUDENT COURSE University Example (cont’d) • Step 3. Determine Relationship type (cont’d) • For FACULTY-teaches-COURSE we are told each faculty member teaches zero, one, or two courses. We are told some courses are taught by zero, one, two, or three faculty. This is a many-to-many relationship. M N teaches FACULTY COURSE

12. M M N N teaches FACULTY FACULTY COURSE COURSE • STUDENT-takes-COURSE - level of participation is mandatory since students must take at least one course; a course, however, may or may not have students taking it M N takes STUDENT COURSE (1,6) (0,30) University Example (cont’d) • Step 4. Determine level of participation • FACULTY-teaches-COURSE - level of participation is optional, since sometimes Faculty do not have to teach (e.g., sabbatical); similarly, a course may not have anyone interested in teaching it (0,2) (0,3)

13. N M CourseID, ... taken by COURSE STUDENT (0,30) (1,6) M (0,3) StudentID, ... taught by (0,2) N FACULTY FacultyID, ... University Example (cont’d) • Step 5. Assign an identifier for each entity • FacultyID, CourseID, StudentID • Step 6. Draw completed E-R diagram

14. 1 M advises FACULTY STUDENT (0,N) (1,1) University Example (cont’d) • You are now told that in addition to the relationships given, each student is assigned a faculty advisor who gives direction in choosing courses. • Use occurrences diagram to visualize relationship between entities • We are told each student is advised by exactly one faculty advisor. We can assume that each faculty member advises zero, one, or more students. This means the additional relationship is of type one-to-many or 1:M. • The STUDENT is on the many side of the relationship and must be advised therefore, faculty is mandatory to student; FACULTY on the one side of the relationship may or may not have a student, therefore student is optional to faculty.

15. University Example (cont’d) • Step 6. Draw completed E-R diagram 1 M M 1 takes CourseID, ... StudentID, ... STUDENT COURSE 1 (1,6) (1,1) (1,1) (0,30) (0,3) M (1,1) M (1,1) advises taught by (1,1) M 1 (0,2) (0,N) FACULTY 1 FacultyID, ...

16. ` COURSE SECTION contains CourseID, ... CourseID, SectionID, ... ` DEPENDENT has a EMPLOYEE EmployeeID, ... EmployeeID, DependentID, ... Weak Entities • an entity that has a dependency on the existence of another entity (mandatory participation) and • has a primary key that is partially or totally derived from the parent entity of the relationship • depict weak entity and relationship with rounded corners

17. Generalization Hierarchy • A subtype entity is an entity that contains a set of optional attributes of the supertype entity and inherits its attributes and its relationships from the supertype entity • If the supertype entity is related to exclusive (can belong to only one subtype) subtype entities, indicate with 1; if subtypes are overlapping (can belong to more than one), use m supertype CONTRACT CLIENT 1 m subtype INDIVIDUAL CORPORATE PRODUCTS SERVICES The same identifier (e.g., ClientID) is used for the supertype as well as subtype.

18. Evaluation of the E-R Model • Using data models to conceptualize the design of a database saves time and money because a completed E-R diagram is the actual blueprint of the database. Its composition must reflect an organization's operations accurately if the database is to meet that organization's data requirements. • The completed E-R diagram also lets the designer communicate more precisely with those who commissioned the database design. It’s easier to correct design flaws at the data modeling stage. • Do not confuse entities and relationships with actual tables. The transformation or decomposition of E-R models will be discussed within the next few weeks. • E-R modeling is an iterative process. Even when complete, ER models generally do not provide a complete picture (e.g., business rules cannot always be shown), therefore, much additional documentation is necessary.