designing e r diagrams
Download
Skip this Video
Download Presentation
Designing E/R Diagrams

Loading in 2 Seconds...

play fullscreen
1 / 21

Designing E/R Diagrams - PowerPoint PPT Presentation


  • 352 Views
  • Uploaded on

Updated: January 25. 2005. Designing E/R Diagrams. Conceptual Data Models. Conceptual data models provide languages to describe conceptual schemas.

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 'Designing E/R Diagrams' - omer


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
conceptual data models
Conceptual Data Models
  • Conceptual data models provide languages to describe conceptual schemas.
  • Conceptual schemas are used to describe the classes of objects that occur in an application area, their properties, their relationships, and the constraints that hold with respect to those classes of objects.
  • Center on “what kind of objects a database contains” and not on “how these objects are stored” ( Internal Schema) and not on “how these objects are represented / displayed to a person that accesses the database” ( External Schema).
conceptual data models what are they good for
Conceptual Data Models ---What are they good for?
  • As a database design tool formalizing the information requirements of the end users
  • As a documentation tool for databases (to help programmers, especially those that have to update the database)
  • As a data model of a database management system (only very few experimental systems exist)
  • As a tool to describe domain ontologies (terminology and concepts in a UoD)
  • As a tool of system analysis
slide4
X

(n,m)

R

Person

Entity Type

Entities of type X participate at least n, at most m

times in relationship R; * indicates .

Attribute

name

Is-insured

Relationship

Type

X

Y

Entity type X is a subtype of type Y

Wedding

Weak

Entity Type

T1

T2

Type T1 and T2 are overlapping; an entity can

belong to both T1 and T2; default is disjoint

occurred

Identifying

Relationship

phone#

Multi-valued Attribute

ssn

Key Attribute

to

Derived Attribute

from

-------

Partial Key

T1

T3

T3=T2 T1

Optional Attribute

amount

T2

E/R Model Symbols for COSC 3480

slide5
UGrad

(1,1)

ssn

mentor

gre

salary

Student

(0,25)

name

(1,1))

Grad

(0,2)

employs

(0,*)

home

took

(0,30)

(0,*)

Department

(0,*)

(0,*)

Semester

Course

name

grade

semesterid

title

Cou#

took-not-a-set: -2.5

Other Errors: -1.5 (or -3-4 if major)

University Problem Final’03 COSC 3480

exam1 fall 2005 problem
Exam1 Fall 2005 Problem
  • Design a “good” entity-relationship diagram that describes the following objects in a university application: students, departments, sections taught in the present and future, and courses. Departments have a name that uniquely identifies the department. Students are identified by a unique social security number, zero, one or multiple e-mail addresses, and an optional gpa (new students do not have a gpa yet). Courses have a unique course number and a course title. Courses are offered in one or more sections at a particular time. Sections are identified by the time they are offered (e.g. 10:30-noon TUTH) and by the course they are associated with. Additionally sections are characterized by the class room the section is taught in. Only information concerning sections that are taught in the present or in the future is stored in the database. Students take a course in a particular semester and receive a grade for their performance. Sometimes students take the same course again in a different semester. There are no limits on how many courses a student can complete, and on how many students completed a particular course. Each student is associated with a least one department. Some students are graduate students that are additionally characterized by their most recent GRE-score. Some graduate students work for a department and receive a salary for their services. Each department employs at most 75 graduate students; graduate students are not allowed to work for multiple departments.
slide7
University Problem Exam1’05

E-mail

ssn

gre

salary

Student

name

Grad

(0,1)

(1,*)

employs

gpa

(0,*)

assoc

(0.*)

took

(0,75)

title

Cou#

Department

(0,*)

(0,*)

Semester

Course

name

grade

(0,*)

semesterid

offered

(1,1)

Section

took-not-a-set: -1.5

Section not weak: -2

Other Errors: -0.5--1 if minor

-2—3 if major

Time-offered

classroom

slide8
Sal

Month

ssn

name

name

birthd

(0,*)

name

from

to

(0,*)

contr

(0,*)

Player

Team

(25,*)

city

Home

Visit

(0,*)

(0,*)

(0,*)

Contract set viol –3,

Other: -2

pos

play

played-in.

pos

(1,1)

(18,*)

score

Game

Date

Game#

Solution Problem6 Exam0 Spring 2003

identifying keys and relationships for entity types
Identifying Keys and Relationshipsfor Entity Types

Each entity type that is not subtype of another entity type needs:

  • Case1: Normal Entity Type (single rectangle)
    • A single attribute (straight line) or
    • A set of attributes or
  • Case2: Weak Entity Type (double rectangle)
    • A set of relationships (double diamond) or
    • A set of relationships or a single attribute (dotted line) or
    • A set of relationships and a set of attributes (dotted line)

that uniquely identifies the instances of the entity type

Remark: min-max cardinalities for weak entity types for their participation in identifying relationships have to be (1,1)!!

slide10
ssn

name

(0,*)

Male

occurred

husband

Person

wife

Female

(0,*)

(1,1)

Wedding

Is-insured

(0,*)

(0,*)

Company

location

name

amount

to

from

E/R Diagram for Multi-Weddings

valid e r diagrams
Valid E/R Diagrams

An E/R diagram is valid if and only if:

  • It is syntactically correct (e.g. specifies all key constraints,…)
  • It specifies the entity types, relationship types, attribute types, and subtype relationships necessary to satisfy all information requirements.
  • It does not specify any invalid constraints.
priorities when choosing between valid e r diagrams
Priorities when Choosing Between Valid E/R Diagrams
  • Express all constraints (you can express!)
  • Use and do not change terminology and class structure of the application domain
  • Keep it simple (avoid defining entity types that do not serve any purpose)
  • Avoid redundancy (but derived attributes are okay)!
a quite bad e r diagrams
A Quite Bad E/R Diagrams

gpa

Name

department

(0,*)

works-

for

(0,*)

Company

Person

gender

wife

husband

(0,*)

ssn

takes

(0,*)

salary

(0,*)

is-married-

to

(0,*)

Section

S#

time

Course

C#

example too many entity types don t use foreign keys
Example: Too many Entity Types /Don’t use Foreign Keys

Example: Persons as well as animals can be insured

P#

Name

is-

insured

(0,*)

(0,*)

Company

Person

name

ssn

(0,*)

Boss-ssn

Bad E/R Diagram!

Animal

A#

e r diagram design typical errors
E/R Diagram Design – Typical Errors
  • Missing Constraints
  • Unexpressed Constraints due to bad design
  • Every entity type needs a key
  • Attribute associated with the wrong entity type (relationship type)
  • Relationships are sets!
  • No partial participation in relationships!
  • Missing existence dependencies (use subclasses)
  • Invalid constraints
  • Using Subtypes for n:1 relationships; using relationships when subtypes should be used.
  • When defining relationships: Too general entity types for participating entities
  • Too many entity types
  • Using foreign keys instead of relationships
other issues in e r design
Other Issues in E/R Design
  • No relationships of relationships --- solution: create an entity type that represent instances of the relationship (or use aggregation as discussed in the textbook)
  • value or entity type --- solution: choose entity type if it helps expressing constraints; otherwise, use value-type.
university e r design problem
University E/R Design Problem

Design a “good” entity-relationship diagram that describes the following objects in an university application: students, instructors, professors, and courses. Students are subdivided into graduate and undergraduate students. Students take a course in a particular semester and receive a grade for their performance. Sometimes students take the same course again in a different semester. There are no limits on how many courses a student can take, and on how many students completed a particular course. Each graduate student has exactly one advisor, who must be a professor, whereas each professor is allowed to be the advisor of at most 20 students. Courses have a unique course number and a course title. Students and professors have a name and a unique ssn; students additionally have a gpa; moreover, graduate students have a GRE-score, and undergraduate students have a single or multiple majors. Professors can be students and take courses, but graduate students cannot be undergraduate students.

Indicate the cardinalities for each relationship type; assign roles (role names) to each relationship if there are ambiguities! Use sub-types, if helpful to express constraints!

slide18
gre

advises

Grad

Professor

ssn

(0,1)

(0,20)

name

major

Student

Ugrad

Person

(0,*)

took

grade

gpa

(0,*)

(0,*)

Semester

Course

title

Cou#

semesterid

Enrolls-not-a-set: -4

Student must be ugrad or grad: -1

Other Errors: -2 (or –3 if quite major)

University Problem (slightly different from Exam0’03)

slide19
number

Cred-Card

phone#

ssn

Problem 1

Exam1 Fall’03

addr

company

(0,*)

(0,300)

Client

A

discount

B

tr#

(0,1)

(1,1)

Gold_Cl.

(0,*)

(1,1)

C

Transaction

Hotel

day_

made

G#

(1,50)

D

day

address

Ho#

(1,1)

Grading:

Minor Error: -1

Medium Error: -2

Major Error: -3 or –4

0-4 points if too many errors

(1,*)

(1,*)

Date

Reservation

F

G

#total

(1,1)

#avail

(0,*)

(0,*)

from

to

rate

Res#

Category

E

(0,*)

room-type

A:=guaranteed; B:=has_transaction; C:=for_hotel; D:=consists_of;

E:=for_category; F=avail-rooms; G=total-rooms; modified on Feb. 3, 2004

aggregation
Employees

name

ssn

lot

Aggregation

Monitors

  • Used when we have to model a relationship involving (entity sets and) a relationship set.
    • Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships.

until

since

started_on

dname

pid

pbudget

did

budget

Sponsors

Departments

Projects

  • Aggregation vs. ternary relationship:
  • Monitors is a distinct relationship,

with a descriptive attribute.

  • Also, can say that each sponsorship

is monitored by at most one employee.

nfl e r design ungraded homework due th jan 27 2005
NFL E/R Design ---Ungraded Homework --- due: Th., Jan. 27,2005

Design an Entity-Relationship Diagram that models the following objects and relationships in the world of football (NFL): teams, players, games, managers and contracts. Each (NFL-) team has a unique team name, and a city it plays in. Each person being part of the NFL-world has a unique ssn and a name. Additionally, for players their weight, height, position and birth dates are of importance. Players have a contract with at most one team and receive a salary for their services, and teams have at least 24 and at most 99 players under contract. Each team has one to three managers; managers can work for at most 4 teams and receive a salary for each of their employments. Players cannot be managers. A game involves a home-team and visiting-team; additionally, the day of the game, and the score of the game are of importance; teams play each other several times in a season (not on the same day!). Moreover, for each game played we like to know which players participated in the game and how many minutes they played.

Indicate the cardinalities for each relationship type; assign roles (role names) to each relationship if there are ambiguities! Use sub-types, if helpful to express constraints!

ad