Transcript CSCI 242 Relational Data Modeling

CSCI 6442
Relational Data Modeling
Copyright 2011, David C. Roberts, all rights reserved
Relational Database
Remember that a relational database is a
collection of time-varying, independent
relations
Each relation models some entity type in the
problem space (outside the computer)
There is a close correspondence between
entity types and relations
2
Data Modeling
• We can say that the relational database
models the real-world problem
• So construction of a relational database
becomes the selection of the entity types
to put into the data model
• Sometimes entity type selection is simple;
other times entity types are not apparent
and selection can be difficult
3
Notation for Data Models
• Chen developed the entity-relation data model,
which considered entity types and their
relationships
• Originally he expected that a new type of
database system would evolve, directly
implementing the E-R data model
• Instead, the E-R notation was found to be an
excellent tool for relational database design and
the relational approach has taken over
• Since then, popular automated tools have
tended to change the notation for easier
computer printing
4
Chen’s Notation
•
We will use Chen’s notation today for two
reasons:
1. It separates the notion of a relationship from the
notion of an entity type
2. Conversion of the design into relational tables
occurs at the end of the design process, so the
difference between the data model and physical
design is clear
•
In your own work, once you are familiar with
the concepts, you will be able to use the
popular “crow’s foot” notation used by
DBDesigner4
5
Entities
• Strong Entity—has independent existence
Employee
• Weak Entity—exists only when the entity it
depends on exists
Dependent
6
Examples of Entity Types
EMPLOYEE
CUSTOMER
ORGANIZATION
PART
INGREDIENT
PURCHASE ORDER
CUSTOMER ORDER
PRODUCT
7
Examples of Entity Instances
An instance of an entity is a specific
occurrence of an entity type:
• Bill Gates is an Employee of Microsoft
• Spam is a Product
• Greenpeace is an Organization
• Flour is an ingredient
8
Attributes
• Attributes are indicated inside ovals; identifier
attributes are underlined. For complex
diagrams, attributes are often omitted
EMPNO
Employee
JOB
SALARY
9
Examples of Attributes
An attribute is a characteristic of an entity type:
EmployeeID
Social Security Number
First Name
Last Name
Street Address
City
State
ZipCode
Date Hired
Health Benefits Plan
10
MVD Attribute
• An MVD attribute is shown as a double
line
EMPNO
Employee
JOB
SALARY
TELEPHONE
11
Relationship
A relationship is an association between two entity
types, for example:
– A CUSTOMER places a CUSTOMER
ORDER
– An EMPLOYEE takes a CUSTOMER ORDER
– A STUDENT enrolls in a COURSE
– A COURSE is taught by a FACULTY
MEMBER
• Some say that entity types should be nouns and
relationships should be verbs
12
Relationship
EMPLOYEE
WORKS FOR
DEPARTMENT
13
Categorizing Relationships
• Number of entity types participating:
– Unary: one
– Binary: two
– Ternary: three
• Existence of related instances(a.k.a. optionality):
– Mandatory
– Optional
• Cardinality of the relationship:
– One-many
– Many-many
– One-one
14
One-to-One Relationships
• Usually the only time a one-to-one relationship is used is for a
dependent entity. Otherwise, usually if there is a one-to-one
relationship, careful consideration will show that there is just one
entity type.
EMPLOYEE
1
HAS
1
SPOUSE
15
Relationship And Cardinality
• A relationship is an association between
two or more entity types, drawn as a
diamond. Relationships may be one-tomany, many-to-many or one-to-one
DEPARTMENT
1
HAS
N
EMPLOYEE
STUDENT
N
GRADE
N
COURSE
EMPLOYEE
1
HAS
1
COMPANY CAR
16
Recursive Relationship
• Recursive relationship is how a repeated
hierarchy is represented
Employee
WORKS FOR
Question: How else can this hierarchy be represented?
17
Relationship of Higher Degree
• A ternary relationship, also said to have
degree 3.
STUDENT
N
TEACHER
N
COURSENO
REG
N
GRADE
COURSE
18
Optionality of Participation
DEPARTMENT
1
HAS
N
EMPLOYEE
STUDENT
N
GRADE
N
COURSE
EMPLOYEE
1
HAS
1
COMPANY CAR
19
Subtypes
• It can be useful to consider a supertype
that includes several entity types as
subtypes
• Supertypes can be
– Complete: every instance of the supertype is
one of the subtypes
– Distinct: no single entity type can be a
member of two subtypes
20
Notation
• Chen’s notation for supertypes and
subtypes is cumbersome, so let’s use
“crow’s-foot” notation
• A supertype is shown as a box
surrounding the boxes for all the subtypes
• Relationship lines end on the supertype or
the subtype boundaries as appropriate
21
Subtypes
• Subtypes can be either mutually exclusive
(disjoint) or overlapping (inclusive).
– For a mutually exclusive category, an entity
instance may be in only one subtype.
– For an overlapping category, an entity
instance may be in two or more subtypes.
• The completeness constraint: all
instances of a subtype must be
represented in the supertype.
22
Example Subtypes
PARTY
PERSON
EMPLOYEE
APPLICANT
ORGANIZATION
CUSTOMER
SUPPLIER
23
Why Use Subtypes and Supertypes Like This?
• Relationships are simplified
– The relationship can take place at the highest
possible level in the hierarchy
– Fewer relationship tables are generally
required
• Programming can be simplified
– All subtypes can be processed in the same
way
– For example, write a check to an organization
or a check to a supplier with the same code
24
Converting an ERD to Relational
• Recall that when we did an ERD we were
not designing tables, we were defining the
problem in terms of entity types and
relationships
• Now the design must be translated into
relational tables
Question: What are the symbols on the ERD that will become tables in the
relational database?
25
Converting to Relational
• Every entity type becomes a table
• Every weak entity type has a foreign key
relationship with a strong entity type
• For one-one relationships, put the identifier of
each with the other
• For one-many relationships, put the identifier of
the one with each of the many
• For many-many relationships, identify an entity
type that connects them, and make it a table,
using the primary key of both partners as a
composite primary key
26
Conversion
• We see that every entity type will become
a table in the relational database
• Some of the diamonds will become tables
and some will not
27
Students and Courses
Grade
Student
Course
Registration
Name
StudentID
Credits
Number
Grad YR
Location
28
What About Subtypes?
• Each subtype and supertype is a separate
table
• Common attributes are stored with the
highest level entity that shares them
• The root level supertype may have many
attributes, or it may have only a key
29
Now let’s do some problems!
• Problem 1: GW Air
• Problem 2: GW Hair
• Problem 3: GW Fair
30
Problem 1: GW Air
• GW is operating an airline. It connects National
and Dulles and BWI
• Passengers may check baggage
• There are two classes of seats
• The fair between each pair of airports is the
same
Design a database to support airline reservations,
airplane scheduling and gate checkin.
31
Problem 2: GW Hair
• GW is opening a hair salon, in one location.
• Stylists may be full time or part time; each has
scheduled hours to work
• Stylists are paid a portion of customer fees
• Customers can make appointments with a
specified stylist in advance or they can request
any stylist for an appointment
Design a database that can schedule
appointments for clients, can run the cash register
and can pay stylists
32
Problem 3: GW Fair
• GWU runs a hiring fair for students
• Students apply in advance and submit structured
resumes
• Employers register in advance and are given
access to student resumes
• Employers indicate which students they wish to
interview
• Students have access to employer interview
requests, can then contact employers to make
interview appointments
33
What We’ll Do Now
• We will form three groups
• Each group gets one problem
• We will integrate our break and solving the
problem for a few minutes
• Each group will develop an ERD to solve
their problem
• Each group will present its ERD to the
class and we’ll discuss it
34