Title: Data Modeling
1Data Modeling
2Entity-Relationship (E/R) Modeling
- Entity
- Relationship
- Attributes
- Cardinalities
3E-R Diagrams
_____
________
_____
4E-R Diagrams
Entity
Relationship
Entity
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
7ATTRIBUTE 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)
9PAINTINGS
ARTISTS
10Entity
Relationship
Entity
______
________
______
11Artist
Creates
Painting
Enter the following attributes on your E-R
diagram Artist name Painting name Date of
painting Date of birth Market value Nationality
12Entities and relationships
Relationship??
13Entity
Relationship
Entity
______
________
______
14Confine
Jails
Criminals
Attributes
Criminal ID Criminals name Jail address Jail
name Number of cells Crime convicted of
15Entities and relationships
ENTITY?
ENTITY?
Relationship??
16Draw E-R diagramEntities, relationships and
attributes
ATTRIBUTES Faculty ID Course number Faculty
name Course name DOH Position Course credits
17Attributes Characteristics of an entity
Faculty ID Faculty name DOH Position
Professor
Taught by
Course number
Course
Course name
Course credits
18Cardinalities Minimum and maximum
- Association participation
- Between entities
- Based on __________________
_____________ (min,max) min 0 or 1 max 1 or
N _____________
19Cardinalities Minimum and maximum
- Association participation
- Between entities
- Based on organizational policy
_____________ (min,max) min 0 or 1 max 1 or
N _____________
20Cardinalities
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
21Cardinalities
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
22Cardinalities Organizational Rules
(_____)
Professor
teaches
Course
(_____)
- Each professor teaches at least one course
- Some professors teach no classes others teach at
least one.
23Cardinalities 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.
24Cardinalities 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)
25Cardinalities 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)
26TABLES
(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
27TABLES
28Museum-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
31Good 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
33Correct Database Design
34E-R Diagram Format
Poor Readability
35E-R Diagram Format
Good Readability
Grid Symmetrical structures Minimize crossings
36J. D. Fish School of BusinessClass Handout
Identify Entities and Relationships
37J. 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
38J. D. Fish School of BusinessClass Handout
Complete ER diagram. List, on paper, the database
tables.