Data Modeling - PowerPoint PPT Presentation

1 / 35
About This Presentation
Title:

Data Modeling

Description:

Good Morning, America. Exalted Ruler. Dancer Seated. Water Lily Pond. Return of the Warriors ... Good designs. Database Tables. E-R Diagrams ' ... – PowerPoint PPT presentation

Number of Views:104
Avg rating:3.0/5.0
Slides: 36
Provided by: bkl9
Category:

less

Transcript and Presenter's Notes

Title: Data Modeling


1
Data Modeling
  • ER Diagrams

2
Entity-Relationship (E/R) Modeling
  • Entity
  • Relationship
  • Attributes
  • Cardinalities

3
E-R Diagrams
  • Use a few symbols

_____
________
_____
  • To model reality

4
E-R Diagrams
  • Use a few symbols

Entity
Relationship
Entity
  • To model reality

5
ENTITY Named object/conceptthat exists in the
universe
  • Types of entities
  • Thing (truck, building)
  • Person (customer, employee, student, professor,
    criminal)
  • Event
  • Instant duration (sale, purchase, cash receipt)
  • Extended duration (month-long use of a truck, a
    course offering that starts on JAN 3 ends on 15
    May)
  • Concept (category of customer, course)
  • SYMBOL -- Rectangle

6
RELATIONSHIP Association between two (or more
?) entities
  • Examples
  • employee assigned to building
  • customer participates in sale
  • professor teaches course-offering
  • Museum displays artwork
  • SYMBOL -- Diamond

participates in
Customer
Sale
7
ATTRIBUTE Characteristics about entities or
relationships that communication real world
phenomena.
  • Example attributes for the entity INVENTORY
  • stock, color, price, cost, weight
  • A primary key is a special attribute used to
    represent an instance of an entity or
    relationship in a database
  • Must be unique and universal
  • Can be a concatenated (combined key)
  • SYMBOL small connected circle (filled in for
    primary key)

Stock Color Price
8
(No Transcript)
9
PAINTINGS
ARTISTS
10
Entity
Relationship
Entity
______
________
______
11
Artist
Creates
Painting
Enter the following attributes on your E-R
diagram Artist name Painting name Date of
painting Date of birth Market value Nationality
12
Entities and relationships
Relationship??
13
Entity
Relationship
Entity
______
________
______
14
Confine
Jails
Criminals
Attributes
Criminal ID Criminals name Jail address Jail
name Number of cells Crime convicted of
15
Entities and relationships
ENTITY?
ENTITY?
Relationship??
16
Draw E-R diagramEntities, relationships and
attributes
ATTRIBUTES Faculty ID Course number Faculty
name Course name DOH Position Course credits
17
Attributes Characteristics of an entity
Faculty ID Faculty name DOH Position
Professor
Taught by
Course number
Course
Course name
Course credits
18
Cardinalities Minimum and maximum
  • Association participation
  • Between entities
  • Based on __________________

_____________ (min,max) min 0 or 1 max 1 or
N _____________
19
Cardinalities Minimum and maximum
  • Association participation
  • Between entities
  • Based on organizational policy

_____________ (min,max) min 0 or 1 max 1 or
N _____________
20
Cardinalities
P participates in relationship at a maximum of 1
time OR N times (many times)
P participates in the relationship at a minimum
of 0 times (optional) OR 1 time (mandatory)
(___, ___)
(___, ___)
P
Relationship
C
Participation Cardinalities
21
Cardinalities
P participates in relationship at a maximum of 1
time OR N times (many times)
P participates in the relationship at a minimum
of 0 times (optional) OR 1 time (mandatory)
(min, max)
(min, max)
P
Relationship
C
Participation Cardinalities
22
Cardinalities Organizational Rules
(_____)
Professor
teaches
Course
(_____)
  • Each professor teaches at least one course
  • Some professors teach no classes others teach at
    least one.

23
Cardinalities Organizational Rules
(1,N)
Professor
teaches
Course
(0,N)
  • Each professor teaches at least one course
  • Some professors teach no classes others teach at
    least one.

24
Cardinalities Organizational Rules
(____)
Professor
teaches
Course
(___)
  • Each course is taught by only one professor.
  • Each course is taught by at least one professor.
    (Some courses are team taught)

25
Cardinalities Organizational Rules
(1,1)
Professor
teaches
Course
(1,N)
  • Each course is taught by only one professor.
  • Each course is taught by at least one professor.
    (Some courses are team taught)

26
TABLES
(1,N)
(0,N)
Professor
teaches
Course
  • Table for each entity
  • Professor
  • Course
  • Table for the many-to-many relationship
  • Primary keys from professor course

27
TABLES
28
Museum-Painting Exercise
  • Using the diagram on the following page.
  • Assume entities are museum, artist and painting.
    Place them in the correct box.
  • What is the primary key for the museum?
  • Assuming that the database includes paintings
    currently in the museum as well as future
    acquisitions, what is the missing cardinality?
  • How many tables would you need to implement the
    database?
  • What do we call name, address, nationality,?

29
(0,N)
housed in
Name Address
(__,___)
Name
Name
(0,N)
(1,1)
Created by
Nationality
Year
D.O.B.
Market-value
30
(0,N)
museum
housed in
Name Address
(0,1)
Name
Name
(0,N)
(1,1)
Artist
Created by
Paintings
Nationality
Year
D.O.B.
Market-value
31
Good designs
  • Database Tables
  • E-R Diagrams

32
  • One Fact in One Place 1 tenet of database
    philosophy
  • Fact a pairing of a key value with another
    attribute value Facts rows in a
    relational database
  • 2. Violations, i.e., bad database design

redundancy
One Fact in Multiple Places
33
Correct Database Design
34
E-R Diagram Format
Poor Readability
35
E-R Diagram Format
Good Readability
Grid Symmetrical structures Minimize crossings
36
J. D. Fish School of BusinessClass Handout
Identify Entities and Relationships
37
J. D. Fish School of BusinessEntities
Relationships
  • Department offers course
  • Department employees professor
  • Professor teaches course
  • Professor advises student
  • Student enrolls in course
  • Professor chairs department

38
J. D. Fish School of BusinessClass Handout
Complete ER diagram. List, on paper, the database
tables.
Write a Comment
User Comments (0)
About PowerShow.com