Chapter 4 Entity Relationship (E-R) Modeling - PowerPoint PPT Presentation

Loading...

PPT – Chapter 4 Entity Relationship (E-R) Modeling PowerPoint presentation | free to download - id: 454020-MGRjN



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Chapter 4 Entity Relationship (E-R) Modeling

Description:

Chapter 4 Entity Relationship (E-R) Modeling Database Systems: Design, Implementation, and Management 4th Edition Peter Rob & Carlos Coronel Developing an E-R Diagram ... – PowerPoint PPT presentation

Number of Views:229
Avg rating:3.0/5.0
Slides: 45
Provided by: Chan1155
Learn more at: http://cstl-hcb.semo.edu
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Chapter 4 Entity Relationship (E-R) Modeling


1
Chapter 4 Entity Relationship (E-R) Modeling
Database Systems Design, Implementation, and
Management 4th Edition Peter Rob Carlos Coronel
2
Developing an E-R Diagram
  • The process of database design is an iterative
    rather than a linear or sequential process.
  • It usually begins with a general narrative of the
    organizations operations and procedures.
  • The basic E-R model is graphically depicted and
    presented for review.
  • The process is repeated until the end users and
    designers agree that the E-R diagram is a fair
    representation of the organizations activities
    and functions.

3
Developing an E-R Diagram
  • Tiny College Database (1)
  • Tiny College (TC) is divided into several
    schools. Each school is administered by a dean. A
    11 relationship exists between DEAN and SCHOOL.
  • Each dean is a member of a group of
    administrators (ADMINISTRATOR). Deans also hold
    professorial rank and may teach a class
    (PROFESSOR). Administrators and professors are
    also Employees. (Figure 4.38)

4
(No Transcript)
5
Developing an E-R Diagram
  • Tiny College Database (2)
  • Each school is composed of several departments.
  • The smallest number of departments operated by a
    school is one, and the largest number of
    departments is indeterminate (N).
  • Each department belongs to only a single school.

Figure 4.40 The First Tiny College ERD Segment
6
Developing an E-R Diagram
  • Tiny College Database (3)
  • Each department offers several courses.

Figure 4.41 The Second Tiny College ERD Segment
7
Developing an E-R Diagram
  • Tiny College Database (4)
  • A department may offer several sections (classes)
    of the same course.
  • A 1M relationship exists between COURSE and
    CLASS.
  • CLASS is optional to COURSE

Figure 4.42 The Third Tiny College ERD Segment
8
Developing an E-R Diagram
  • Tiny College Database (5)
  • Each department has many professors assigned to
    it.
  • One of those professors chairs the department.
    Only one of the professors can chair the
    department.
  • DEPARTMENT is optional to PROFESSOR in the
    chairs relationship.

Figure 4.43 The Fourth Tiny College ERD Segment
9
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.

Figure 4.44 The Fifth Tiny College ERD Segment
10
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.

Figure 4.45 The Sixth Tiny College ERD Segment
11
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.

Figure 4.46 The Seventh Tiny College ERD Segment
12
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.

Figure 4.47 The Eighth Tiny College ERD Segment
13
Developing an E-R Diagram
Entities for the Tiny College Database
  • SCHOOL
  • DEPARMENT
  • EMPLOYEE
  • PROFESSOR
  • COURSE
  • CLASS
  • ENROLL (Bridge between STUDENT and CLASS)
  • STUDENT

14
Components of the E-R Model
Table 4.2
15
Figure 4.48
16
Developing an E-R Diagram
  • Converting an E-R Model into a Database Structure
  • A painter might paint many paintings. 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 exhibited in a
    gallery i.e., the GALLERY is optional to
    PAINTING.

17
Figure 4.49
18
Developing an E-R Diagram
  • 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)

19
A Data Dictionary for the ARTIST Database
Table 4.3
20
Developing an E-R Diagram
  • 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))

21
Developing an E-R Diagram
  • 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))

22
Developing an E-R Diagram
  • SQL Commands to Create the PAINTING Table
  • CREATE TABLE PAINTING ( PNTG_NUM CHAR(4) NOT
    NULL UNIQUE, PNTG_TITLE CHAR(35), PNTG_PRICE NUMBE
    R(9,2), PTR_NUM CHAR(4) NOT NULL, GAL_NUM CHAR(4),
    PRIMARY KEY(PNTG_NUM) FOREIGN KEY(PTR_NUM)
    RERERENCES PAINTER ON DELETE RESTRICT ON UPDATE
    CASCADE, FOREIGN KEY(GAL_NUM) REFERENCES
    GALLERY ON DELETE RESTRICT ON UPDATE CASCADE)

23
Developing an E-R Diagram
  • General Rules Governing Relationships among
    Tables
  • 1. All primary keys must be defined as NOT NULL.
  • 2. Define all foreign keys to conform to the
    following requirements for binary relationships.
  • 1M Relationship
  • Weak Entity
  • MN Relationship
  • 11 Relationship

24
Developing an E-R Diagram
  • 1M Relationships
  • Create the foreign key by putting the primary key
    of the one (parent) in the table of the many
    (dependent).
  • Foreign Key Rules

25
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 1M relationship, except foreign key
    restrictions
  • NOT NULL
  • ON DELETE CASCADE
  • ON UPDATE CASCADE
  • MN Relationship
  • Convert the MN relationship to a composite
    (bridge) entity consisting of (at least) the
    parent tables primary keys.

26
Developing an E-R Diagram
  • 11 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.

27
Developing an E-R Diagram
  • CASE 1 MN, Both Sides MANDATORY

Figure 4.50 Entity Relationships, MN, Both
Sides Mandatory
28
Developing an E-R Diagram
  • CASE 2 MN, Both Sides OPTIONAL

Figure 4.51 Entity Relationships, MN, Both
Sides Optional
29
Developing an E-R Diagram
  • CASE 3 MN, One Side OPTIONAL

Figure 4.52 Entity Relationships, MN, One Side
Optional
30
Developing an E-R Diagram
  • CASE 4 1M, Both Sides MANDATORY

Figure 4.53 Entity Relationships, 1M, Both
Sides Mandatory
31
Developing an E-R Diagram
  • CASE 5 1M, Both Sides OPTIONAL

Figure 4.54 Entity Relationships, 1M, Both
Sides Optional
32
Developing an E-R Diagram
  • CASE 6 1M, Many Side OPTIONAL, One Side
    MANDATORY

Figure 4.55 Entity Relationships, 1M, Many Side
Optional, One Side Mandatory
33
Developing an E-R Diagram
  • CASE 7 1M, One Side OPTIONAL, One Side MANDATORY

Figure 4.56 Entity Relationships, 1M, One Side
Optional, Many Side Mandatory
34
Developing an E-R Diagram
  • CASE 8 11, Both Sides MANDATORY

Figure 4.57 Entity Relationships, 11, Both
Sides Mandatory
35
Developing an E-R Diagram
  • CASE 9 11, Both Sides OPTIONAL

Figure 4.58 Entity Relationships, 11, Both
Sides Optional
36
Developing an E-R Diagram
  • CASE 10 11, One Side OPTIONAL, One Side
    MANDATORY

Figure 4.59 Entity Relationships, 11, One Side
Optional, One Side Mandatory
37
Developing an E-R Diagram
  • CASE 11 Weak Entity (Foreign key located in weak
    entity)

Figure 4.60 Entity Relationships, Weak Entity
38
Developing an E-R Diagram
  • CASE 12 Multivalued Attributes

Figure 4.61 Entity Relationships, Multivalued
Attributes
39
(No Transcript)
40
The Chen Representation of the Invoicing Problem
Figure 4.63
41
The Crows Foot Representation of the Invoicing
Problem
Figure 4.64
42
Figure 4.65 The Rein85 Representation of the
Invoicing Problem
43
The IDEF1X Representation of the Invoicing Problem
Figure 4.66
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
  • Documentation
About PowerShow.com