Download Chapter 4 Entity Relationship (E-R) Modeling and more Assignments Science education in PDF only on Docsity!
Chapter 4
Entity Relationship (E-R) Modeling
Developing an E-R Diagram
Database design is an iterative rather than a linear or sequential process. Begin with a general narrative of the organization's operations and procedures. Identify entities and relations The basic E-R model is graphically depicted and presented for review. The process is repeated until end users and designers agree that the E-R diagram is a fair representation of the organization's activities and functions.
Developing an E-R Diagram: Tiny College Database (1)
Tiny College (TC) is divided into several SCHOOLs Each school is administered by a one DEAN Each dean administers one school. A 1:1 relationship exists between DEAN and SCHOOL.
Administrators and Professors
Each DEAN is an ADMINISTRATOR DEANs, however, are also PROFESSORs rank and may teach a class Administrators and professors are also Employees.
Figure 4.
We may wish to capture the ADMINISTRATOR vs PROFESSOR relationship with a Supertype Subtype hierarchy.
Figure 4.
We might also simply treat DEANs as PROFESSORs. Note that since not all employees are professors PROFESSOR is optional to EMPLOYEE.
Developing an E-R Diagram: Tiny College Database (2)
Each SCHOOL is composed of several DEPARTMENTs. The smallest number of DEPARTMENTs in a school is one, and the largest number of departments is indeterminate (N). Each DEPARTMENT belongs to only a single SCHOOL.
Developing an E-R Diagram: Tiny College Database (5)
Each DEPARTMENT has many PROFESSORs assigned to it. ?? Can a professor work for > 1 department? One PROFESSOR chair s the DEPARTMENT. Only one PROFESSOR can chair the department. DEPARTMENT is optional to PROFESSOR in the �chairs �relationship.
Developing an E-R Diagram: Tiny College Database (6)
Each professor may teach up to four classes, each one a section of a course. A professor may also be on a research contract and teach no classes. ?? Must a course have a faculty member assigned?
Developing an E-R Diagram: Tiny College Database (7)
A student may enroll in several classes, but (s)he takes each class only once during any given enrollment period. Each student may enroll in up to six classes and each class may have up to 35 students in it. STUDENT is optional to CLASS. ??-Why?
Developing an E-R Diagram: Tiny College Database (8)
Each department has several students whose major is offered by that department. Each student has only a single major and associated with a single department. ??Can a department have > 1 major? is this important enough to capture?
Developing an E-R Diagram: Tiny College Database (9)
Each student has an advisor in his or her department; each advisor counsels several students. An advisor is also a professor, but not all professors advise students.
Tiny College ERD
Developing an E-R Diagram: Converting an E-R Model into a Database Structure
A PAINTER paints PAINTING. The cardinality is (1,N) in the relationship between PAINTER and PAINTING. Each PAINTING is painted by one (and only one) PAINTER. A PAINTING might (or might not) be placed in a gallery; i.e., GALLERY is optional to PAINTING.
The Artist Database
Summary of Table Structures and Special Requirements for the ARTIST database
PAINTER(PRT_NUM, PRT_LASTNAME, PRT_FIRSTNAME, PRT_INITIAL, PTR_AREACODE, PRT_PHONE) GALLERY(GAL_NUM, GAL_OWNER, GAL_AREACODE, GAL_PHONE, GAL_RATE) PAINTING(PNTG_NUM, PNTG_TITLE, PNTG_PRICE, PTR_NUM, GAL_NUM)
SQL Commands to Create the PAINTER Table
CREATE TABLE PAINTER ( PTR_NUM CHAR(4) NOT NULL UNIQUE, PRT_LASTNAME CHAR(15) NOT NULL, PTR_FIRSTNAME CHAR(15), PTR_INITIAL CHAR(1),
General Rules Governing Relationships among Tables
All primary keys must be defined as NOT NULL. Define all foreign keys to conform to the following requirements for binary relationships. 1:M Relationship Weak Entity M:N Relationship 1:1 Relationship
Developing an E-R Diagram: 1:M Relationships
Create the foreign key by putting the primary key of the �one � (parent) in the table of the �many � (dependent). Foreign Key Rules: Both Sides Mandatory NOT NUL Both sides Optional NULL OK One side Optional, One Side Mandatory NULL OK Note: DB2 does not support On Update CASCADE. This would require a TRIGGER.
Developing an E-R Diagram: Weak Entity
Put the key of the parent table (strong entity) in the weak entity. The weak entity relationship conforms to the same rules as the 1:M relationship, except foreign key restrictions: NOT NULL ON DELETE CASCADE ON UPDATE CASCADE
M:N Relationship
Convert the M:N relationship to a composite (bridge) entity consisting of (at least) the parent tables� primary keys.
1:1 Relationships
If both entities are in mandatory participation in the relationship and they do not participate in other relationships, it is most likely that the two entities should be part of the same entity.
The Challenge of Database Design:
Conflicting Goals Design standards (design elegance) Processing speed Information requirements Design Considerations Logical requirements and design conventions End user requirements; e.g., performance, security, shared access, data integrity Processing requirements Operational requirements Documentation
