4.4 Developing an E-R diagram
An iterative process
The Tiny College
TC has many SCHOOLs
DEAN and SCHOOL
EMPLOYEE entity
Consider business rules
PROFESSOR is an EMPLOYEE
School and department
SCHOOL operates DEPARTMENTs
DEPARTMENT offers COURSEs
COURSE generates CLASSes
DEPARTMENT has many PROFESSORs
PROFESSOR teaches CLASSes
STUDENTs enroll CLASSes
Add a composite entity ENROLL
DEPARTMENT has STUDENTs
PROFESSOR counsels STUDENTs
Eight entities
E-R model’s components
PROFES. is an 1:1 EMPLOYEE
E-R diagram for Tiny College
4.4.1 Converting an E-R model into a database structure
A reading exercise
4.4.2 General rules governing relationships among tables
General rules
Primary key and entity integrity
Primary key
Foreign key and referential integrity
Foreign key
Case 1: M:N, Both sides mandatory
PPT Slide
For the ENROLL table (1)
For the ENROLL table (2)
Case 2: M:N, Both sides optional
For both foreign keys
Case 3: M:N, One side optional
Operations on optional side
Operations on mandatory side
Case 4: 1:M, Both sides mandatory
Operations on PROFESSOR side
Case 5: 1:M, Both sides optional
Operations on the STUDENT table
Case 6: 1:M, One sides optional, one side mandatory
Operations on STUDENT table
Case 7: 1:M, One sides optional, one side mandatory
Operations on PAINTING side
Case 8: 1:1, Both sides mandatory
Foreign key = ROOM_CODE
Case 9: 1:1, Both sides optional
Foreign key = PROF_NUM
Case 10: 1:1, One side optional, one side mandatory
Note: two possible options
A> Foreign key on optional entity
B> Foreign key on mandatory entity
Case 11: Weak entity
Foreign key = EMP_NUM
Case 12: Multivalued attributes
4.4.3 Converting Tiny College’s E-R model to the SQL database
Create the database and tables
4.4.4 Building another E-R model
4.4.5 Considering additional requirements
An reading exercise
4.5 A comparison of E-R modeling symbols
Skip it
4.6 The challenge of database design: conflicting goals
Design compromises
Conform to design standards
Speed
Complex information requirements
Document, document, and document
Email: xuc@gsu.cs.gasou.edu
Home Page: www.cs.gasou.edu\~xuc
Download Microsoft PowerPoint Animation Player for ActiveX
