Download Report

Objective
Lecture 5
•
•
•
•
•
Database Management Systems
Spring - 1415
Enhanced Entity Relationships Modeling
Superclass and Subclass
Specialization and Generalization Process
Participation and Disjoint Constraints
Examples
Data Modeling
with the
Entity-Relationship Model (Cont’d)
2
Spring-1415
Enhanced E-R Modeling
Superclass and Subclass
• Enhanced Entity Relationships (EER) is the enhanced version of the
original ER model designed to cope-up with demand of complex
database requirements by applications like Multimedia, GIS, and
CAD.
• New modeling constructs incorporated into EER
• Each attribute of the entity type may not be necessary for all
instances of that type.
– E.g. Class, GPA are not required for PERSON instances that are faculty.
– Office Phone, salary are not required for PERSON instances that are
student.
– Superclass (Super Entity Type)
– Subclass (Sub Entity Type)
• EER also introduces two processes
– Specialization
– Generalization
Spring-1415
3
Spring-1415
4
1
Superclass and Subclass
Superclass & Subclass Relationships
• Superclass entity type is one that has one or more distinct
subgroups with unique attributes.
• Subclass is an entity type that shares common attributes
or relationships distinct from other subclasses.
SSN
Birth Date
Name
Addres
s
E mail
PERSON
Superclass
Major
Dept
Rank
Salary
Office
Phone
FACULTY
STAFF
Salary
Designation
STUDENT
Class
GPA
5
Subclasses
Spring-1415
6
Spring-1415
Superclass & Subclass Relationships
Superclass & Subclass Relationships
• The entity in a subclass is the same entity in the
superclass except it has a distinct role.
• Relationship between superclass and subclass is one-one.
• Every instance in a subclass is a member of superclass and
shares its attributes.
• Each subclass has attributes and relationships that make it
unique.
Superclass
Subclass
relationship
Spring-1415
7
Spring-1415
8
2
Generalization/Specialization
Attribute Inheritance
• It is the property by which subclass entities inherit values
for all attributes of the superclass.
Item#
Price
Type
Info
ITEM
Diameter
Material
Volume
Length
PIPE
SOIL
Weight
PAINT
Color
• Generalization is a process of defining more
general entity types by identifying their common
characteristics
Instance of an PIPE entity
Length: 10”
Diameter: 2”
Material: Cast Iron
Item #: 12599
Price: $0.5 /lb
Info: Rust Proof
Type: Pipe
SType
Inherited Attributes
9
Spring-1415
10
Spring-1415
Generalization/Specialization
Participation & Disjoint Constraints
• Constraints are intuitive and help us manifest business
rules and incorporate them into the EER design.
• Specialization is a process of defining one or more
subclasses by identifying their distinguishing
characteristics.
• Participation Constraints
− Dictate whether every member of a superclass must participate as a
member of a subclass.
− May be Total Participation or Partial Participation.
• Disjoint Constraints
• Define whether it is possible for an instance of a superclass to be a member
or one or more subclasses simultaneously.
• May be Disjoint Rule or Overlap Rule.
Spring-1415
11
Spring-1415
12
3
Total Participation Rule
Partial Participation Rule
• Member of a superclass does not have to be member of
any subclass. Membership is optional.
• Every member or instance of a superclass must be a
member of at least one subclass.
Single
Line
Double
Line
13
Spring-1415
Overlap Rule
Disjoint Rule
• An instance of superclass that is a member of any
subclass, cannot be an instance of any other subclass
simultaneously.
• An instance of a superclass that is a member of any
subclass, can also belong to any other subclass
simultaneously.
Letter “O”
Letter “D”
Spring-1415
14
Spring-1415
15
Spring-1415
16
4
Subclass Discriminators
Superclass/Subclass Hierarchy
• An attribute of a superclass that discriminates a new
entry to the superclass into appropriate subclasses.
Birth Date
SSN
Sex
Address
E mail
PERSON
Level 1
O
Major Dept
Address
Discriminator
SSN
Office Phone
FACULTY
STAFF
Salary
STUDENT
Class
Level 2
GPA
Designation
D
Committee Chair
GRAD
UNDERGRAD
Degree Program
TA
Course
17
Spring-1415
Project
18
Example -2
A non-profit organization depends on a number of different types of
persons for its successful operation.
• The organization is interested in the following attributes for all of these
persons: SSN, Name, Address, City and Telephone. Three types of
persons are greatest interest: employee, volunteers and donors.
• Employees only have a Date_hired attribute, and volunteers only have
Skills attribute.
• Donor only have a relationship with an Item entity type.
• A donor must have donated one or more items, and an item may have
no donors, or one or more donors.
• There persons other than employees, volunteers, and donors who are of
interest to the organization, so that a person need not belong to any of
these three groups.
• On the other hand, at any given time a person may belong to two or
more of these groups .
Spring-1415
RA
Spring-1415
Example-1
Level 3
Class
O
19
We would like to design a database to maintain information about
hospital staff, including doctors and nurses, and patients at the
hospital. The information we need includes:
• Staff, including their names, addresses and social-security numbers.
• Patients, including their names, addresses, and the name of their
insurance company.
• Patients are each assigned to a room.
• Those staff who are nurses are assigned to zero or more rooms. Each
room has at least one nurse assigned.
• Those staff who are doctors are assigned to zero or more patients.
• Patients may or may not have a doctor assigned, and they may have
more than one doctor. Patients in the same room may have different
doctors but will always have the same nurse.
Spring-1415
20
5
Example - 3
Example - 4
The system keeps track of parts stored in its warehouses.
Given the following description,
• For each actor/director, store her/his name. There can be
actors/directors with the same name.
• For each movie, store its title. No two movies can have the same title.
• For each actor (or director), record the movies s/he played (or directed),
as well as the amount of money s/he earned from the movie.
• Every director must have directed some movie, but not every actor has
played in a movie. Each director/actor may have directed/played in
multiple movies.
• Each movie is directed by a single director.
• If a movie is a sequel to another one (e.g., “Matrix Re-loaded” is a sequel
to “Matrix”), this relationship should be recorded.
• Not every movie has a sequel.
21
Spring-1415
• Information about parts include their part number and a description.
Parts can be manufactured parts or purchased parts. Manufactured
parts have additional data, specifically a routing number. Purchased
parts are received from suppliers.
• The distributor has many suppliers, each of which can supply any
number of purchased parts. In addition, each purchased part may be
supplied by one or more suppliers. Each supplier has a different price
quote for each purchased part. The price quote includes both the unit
price and the bulk-rate price.
• In addition, the inventory system also includes a library of storage
instructions. Storage instructions are identified by an instruction ID, and
include a location and container type. Each part must be associated with
at least one storage instruction, and a storage instruction may be
associated with any number of parts.
• Parts may contain or be contained in any number of other parts (as
subparts).
22
Spring-1415
Example - 5
Teams are to be created for a charity drive.
• Each team should have at least one student.
• We need to know the first name, middle initial, last name, and two or
more phone numbers of each student.
• The students include both undergraduate and graduate. The team
members must be undergraduate students.
• Each student is allowed to participate in one team. Since it is a
volunteering activity, not all students will participate in the charity drive.
• A team must have a unique team name and a team leader. When the
team is formed, the date should be stored. The date when a team
member is joined the team should also be recorded.
• Some graduate students may be recruited as team advisors (one advisor
per team). On the other hand, we need to know whether or not a
graduate student is experienced in charity drive. For undergraduate
student, we need to record the year of study
Spring-1415
23
6