e r model chapter 5
TRANSCRIPT
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 1/44
Chapter 5Entity Relationship (E-R)
Modeling
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 2/44
Developing an E-RDiagram
• The process of database design is an iterativerather than a linear or sequential process.
• It usually begins with a general narrative of the
organization’s operations and procedures.
• The basic E-R model is graphically depicted andpresented for review.
• The process is repeated until the end users anddesigners agree that the E-R diagram is a fairrepresentation of the organization’s activities andfunctions.
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 3/44
• Tiny College Database (1)
– Tiny College (TC) is divided into severalschools. Each school is administered by adean. A 1:1 relationship exists betweenDEAN and SCHOOL.
– Each dean is a member of a group of
administrators (ADMINISTRATOR). Deansalso hold professorial rank and may teach aclass (PROFESSOR). Administrators andprofessors are also Employees. (Figure 4.38)
Developing an E-R
Diagram
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 5/44
Developing an E-R
Diagram• Tiny College Database (2)
– Each school is composed of severaldepartments.
– The smallest number of departmentsoperated by a school is one, and the largestnumber of departments is indeterminate (N).
– Each department belongs to only a single
school.
Figure 4.40 The First Tiny College ERD Segment
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 6/44
• Tiny College Database (3)– Each department offers several
courses.
Developing an E-R
Diagram
Figure 4.41 The Second Tiny College ERD Segment
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 7/44
• Tiny College Database (4)
– A department may offer several sections(classes) of the same course.
– A 1:M relationship exists between COURSEand CLASS.
– CLASS is optional to COURSE
Developing an E-RDiagram
Figure 4.42 The Third Tiny College ERD Segment
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 8/44
• Tiny College Database (5)– Each department has many professors
assigned to it.
– One of those professors chairs the
department. Only one of the professors canchair the department.
– DEPARTMENT is optional to PROFESSOR inthe “chairs” relationship.
Developing an E-RDiagram
Figure 4.43 The Fourth Tiny College ERD Segment
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 9/44
• Tiny College Database (6)
– Each professor may teach up to fourclasses, each one a section of a course.
– A professor may also be on a researchcontract and teach no classes.
Developing an E-RDiagram
Figure 4.44 The Fifth Tiny College ERD Segment
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 10/44
• Tiny College Database (7)– A student may enroll in several classes, but
(s)he takes each class only once during anygiven enrollment period.
– Each student may enroll in up to six classesand each class may have up to 35 studentsin it.
– STUDENT is optional to CLASS.
Developing an E-RDiagram
Figure 4.45 The Sixth Tiny College ERD Segment
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 11/44
• Tiny College Database (8)
– Each department has several students whosemajor is offered by that department.
– Each student has only a single major andassociated with a single department.
Developing an E-RDiagram
Figure 4.46 The Seventh Tiny College ERD Segment
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 12/44
• Tiny College Database (9)– Each student has an advisor in his or her
department; each advisor counsels severalstudents.
– An advisor is also a professor, but not allprofessors advise students.
Developing an E-RDiagram
Figure 4.47 The Eighth Tiny College ERD Segment
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 13/44
Entities for the Tiny College Database
• SCHOOL
• DEPARMENT
• EMPLOYEE
• PROFESSOR
• COURSE• CLASS
• ENROLL (Bridge between
STUDENT and CLASS)• STUDENT
Developing an E-RDiagram
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 14/44
Components of the E-R Model
Table 4.2
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 16/44
• Converting an E-R Model into a DatabaseStructure
– A painter might paint many paintings. Thecardinality is (1,N) in the relationshipbetween PAINTER and PAINTING.
– Each painting is painted by one (and only
one) painter.– A painting might (or might not) be exhibited
in a gallery; i.e., the GALLERY is optional toPAINTING.
Developing an E-RDiagram
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 18/44
• Summary of Table Structures and SpecialRequirements 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)
Developing an E-RDiagram
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 19/44
A Data Dictionary for the ARTIST Database
Table 4.3
l E
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 20/44
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),
PTR_AREACODE CHAR(3),
PTR_PHONE CHAR(8),
PRIMARY KEY(PTR_NUM));
Developing an E-RDiagram
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 21/44
SQL Commands to Create the GALLERY Table
CREATE TABLE GALLERY (
GAL_NUM CHAR(4) NOT NULL UNIQUE,
GAL_OWNER CHAR(35),
GAL_AREACODE CHAR(3) NOT NULL,
GAL_PHONE CHAR(8) NOT NULL,GAL_RATE NUMBER(4,2),
PRIMARY KEY(GAL_NUM));
Developing an E-RDiagram
D l i E R
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 22/44
SQL Commands to Create the PAINTINGTable
CREATE TABLE PAINTING (
PNTG_NUM CHAR(4) NOT NULL UNIQUE,
PNTG_TITLE CHAR(35),
PNTG_PRICE NUMBER(9,2),PTR_NUM CHAR(4) NOT NULL,
GAL_NUM CHAR(4),
PRIMARY KEY(PNTG_NUM)
FOREIGN KEY(PTR_NUM) RERERENCES PAINTER
ON DELETE RESTRICTON UPDATE CASCADE,
FOREIGN KEY(GAL_NUM) REFERENCES GALLERY
ON DELETE RESTRICT
ON UPDATE CASCADE);
Developing an E-RDiagram
D l E R
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 23/44
• General Rules Governing Relationships amongTables
1. All primary keys must be defined as NOTNULL.
2. Define all foreign keys to conform to thefollowing requirements for binaryrelationships.
• 1:M Relationship• Weak Entity
• M:N Relationship
• 1:1 Relationship
Developing an E-RDiagram
l E
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 24/44
• 1:M Relationships
– Create the foreign key by putting theprimary key of the “one” (parent) in the
table of the “many” (dependent). – Foreign Key Rules:
Null On Delete On Update
If both sides are
MANDATORY
NOT NULL RESTRICT CASCADE
If both sides areOPTIONAL
NULLALLOWED
SET NULL CASCADE
If one side isOPTIONAL and
the other
MANDATORY
NULLALLOWED
SET NULLor
RESTRICT
CASCADE
Developing an E-RDiagram
D l i E R
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 25/44
• Weak Entity
– Put the key of the parent table (strongentity) in the weak entity.
– The weak entity relationship conforms to thesame rules as the 1:M relationship, exceptforeign 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.
Developing an E-RDiagram
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 27/44
• CASE 1: M:N, Both Sides MANDATORY
Developing an E-RDiagram
Figure 4.50 Entity Relationships, M:N, Both Sides Mandatory
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 28/44
• CASE 2: M:N, Both Sides OPTIONAL
Developing an E-R
Diagram
Figure 4.51 Entity Relationships, M:N, Both Sides Optional
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 30/44
• CASE 4: 1:M, Both Sides MANDATORY
Developing an E-RDiagram
Figure 4.53 Entity Relationships, 1:M, Both Sides Mandatory
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 31/44
• CASE 5: 1:M, Both Sides OPTIONAL
Developing an E-RDiagram
Figure 4.54 Entity Relationships, 1:M, Both Sides Optional
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 32/44
• CASE 6: 1:M, Many Side OPTIONAL,One Side MANDATORY
Developing an E-R
Diagram
Figure 4.55 Entity Relationships, 1:M, Many Side Optional, One Side Mandatory
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 33/44
• CASE 7: 1:M, One Side OPTIONAL,One Side MANDATORY
Developing an E-R
Diagram
Figure 4.56 Entity Relationships, 1:M, One Side Optional, Many Side Mandatory
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 34/44
• CASE 8: 1:1, Both SidesMANDATORY
Developing an E-R
Diagram
Figure 4.57 Entity Relationships, 1:1, Both Sides Mandatory
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 35/44
• CASE 9: 1:1, Both Sides OPTIONAL
Developing an E-R
Diagram
Figure 4.58 Entity Relationships, 1:1, Both Sides Optional
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 36/44
• CASE 10: 1:1, One Side OPTIONAL,One Side MANDATORY
Developing an E-R
Diagram
Figure 4.59 Entity Relationships, 1:1, One Side Optional, One Side Mandatory
l E
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 37/44
• CASE 11: Weak Entity (Foreign keylocated in weak entity)
Developing an E-R
Diagram
Figure 4.60 Entity Relationships, Weak Entity
l E
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 38/44
• CASE 12: Multivalued Attributes
Developing an E-R
Diagram
Figure 4.61 Entity Relationships, Multivalued Attributes
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 40/44
The Chen Representation of the Invoicing Problem
Figure 4.63
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 41/44
The Crow’s Foot Representation of the Invoicing Problem
Figure 4.64
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 42/44
Figure 4.65 The Rein85 Representation of the Invoicing Problem
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 43/44
The IDEF1X Representation of the Invoicing Problem
Figure 4.66
Th Ch ll f D t b D i
8/11/2019 e r model chapter 5
http://slidepdf.com/reader/full/e-r-model-chapter-5 44/44
The Challenge of Database Design:Conflicting Goals
• 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