Chapter 5 entity relationship e r modeling
Download
1 / 44

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


  • 117 Views
  • Uploaded on

Chapter 5 Entity Relationship (E-R) Modeling. 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 organization’s operations and procedures.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Chapter 5 Entity Relationship (E-R) Modeling' - zamir


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Chapter 5 entity relationship e r modeling

Chapter 5Entity Relationship (E-R) Modeling


Developing an e r diagram
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 organization’s 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 organization’s activities and functions.


Developing an e r diagram1
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 1:1 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)


Developing an e r diagram2
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


Developing an e r diagram3
Developing an E-R Diagram

  • Tiny College Database (3)

    • Each department offers several courses.

Figure 4.41 The Second Tiny College ERD Segment


Developing an e r diagram4
Developing an E-R Diagram

  • Tiny College Database (4)

    • A department may offer several sections (classes) of the same course.

    • A 1:M relationship exists between COURSE and CLASS.

    • CLASS is optional to COURSE

Figure 4.42 The Third Tiny College ERD Segment


Developing an e r diagram5
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


Developing an e r diagram6
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


Developing an e r diagram7
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


Developing an e r diagram8
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


Developing an e r diagram9
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


Developing an e r diagram10

SCHOOL

DEPARMENT

EMPLOYEE

PROFESSOR

COURSE

CLASS

ENROLL (Bridge between STUDENT and CLASS)

STUDENT

Developing an E-R Diagram

Entities for the Tiny College Database




Developing an e r diagram11
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.



Developing an e r diagram12
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)



Developing an e r diagram13
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));


Developing an e r diagram14
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));


Developing an e r diagram15
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 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 RESTRICT ON UPDATE CASCADE,FOREIGN KEY(GAL_NUM) REFERENCES GALLERY ON DELETE RESTRICT ON UPDATE CASCADE);


Developing an e r diagram16
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.

    • 1:M Relationship

    • Weak Entity

    • M:N Relationship

    • 1:1 Relationship


Developing an e r diagram17
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:


Developing an e r diagram18
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.


Developing an e r diagram19
Developing an E-R Diagram

  • 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.


Developing an e r diagram20
Developing an E-R Diagram

  • CASE 1: M:N, Both Sides MANDATORY

Figure 4.50 Entity Relationships, M:N, Both Sides Mandatory


Developing an e r diagram21
Developing an E-R Diagram

  • CASE 2: M:N, Both Sides OPTIONAL

Figure 4.51 Entity Relationships, M:N, Both Sides Optional


Developing an e r diagram22
Developing an E-R Diagram

  • CASE 3: M:N, One Side OPTIONAL

Figure 4.52 Entity Relationships, M:N, One Side Optional


Developing an e r diagram23
Developing an E-R Diagram

  • CASE 4: 1:M, Both Sides MANDATORY

Figure 4.53 Entity Relationships, 1:M, Both Sides Mandatory


Developing an e r diagram24
Developing an E-R Diagram

  • CASE 5: 1:M, Both Sides OPTIONAL

Figure 4.54 Entity Relationships, 1:M, Both Sides Optional


Developing an e r diagram25
Developing an E-R Diagram

  • CASE 6: 1:M, Many Side OPTIONAL, One Side MANDATORY

Figure 4.55 Entity Relationships, 1:M, Many Side Optional, One Side Mandatory


Developing an e r diagram26
Developing an E-R Diagram

  • CASE 7: 1:M, One Side OPTIONAL, One Side MANDATORY

Figure 4.56 Entity Relationships, 1:M, One Side Optional, Many Side Mandatory


Developing an e r diagram27
Developing an E-R Diagram

  • CASE 8: 1:1, Both Sides MANDATORY

Figure 4.57 Entity Relationships, 1:1, Both Sides Mandatory


Developing an e r diagram28
Developing an E-R Diagram

  • CASE 9: 1:1, Both Sides OPTIONAL

Figure 4.58 Entity Relationships, 1:1, Both Sides Optional


Developing an e r diagram29
Developing an E-R Diagram

  • CASE 10: 1:1, One Side OPTIONAL, One Side MANDATORY

Figure 4.59 Entity Relationships, 1:1, One Side Optional, One Side Mandatory


Developing an e r diagram30
Developing an E-R Diagram

  • CASE 11: Weak Entity (Foreign key located in weak entity)

Figure 4.60 Entity Relationships, Weak Entity


Developing an e r diagram31
Developing an E-R Diagram

  • CASE 12: Multivalued Attributes

Figure 4.61 Entity Relationships, Multivalued Attributes






The challenge of database design conflicting goals
The Challenge of Database Design: ProblemConflicting 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


ad