cmpt 258 database systems the entity-relationship model part ii (chapter 2)
TRANSCRIPT
![Page 1: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/1.jpg)
CMPT 258 Database SystemsThe Entity-Relationship ModelPart II (Chapter 2)
![Page 2: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/2.jpg)
Change of Office Hours
•Monday, Thursday 5:30 – 6:30 PMWednesday 3:30 – 4:30 PM
![Page 3: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/3.jpg)
Programming for Fun (PFF)When: Tuesday, September 22 3:30PMWhere: RLC 105Team based: one, two or three people per teamLanguages: C/C++ and JavaIDEs: Visual Studio, Eclipse, NetBeans
Interested?
Event Schedule3:30 – 5:30 pm – Contest5:30 pm – Award Ceremony5:30 pm – Pizza Party
Register your team online or in RLC 203 (Find the registration list on the bulletin board)Contact Dr. Tina Tian for questions.
![Page 4: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/4.jpg)
ISA (‘is a’) Hierarchies• As in C++, or other PLs, attributes are inherited.• If we declare A ISA B, every A entity is also
considered to be a B entity.• Employees is specialized into subclasses• Hourly-Emp and Contract_EMps are generalized
by Employees
![Page 5: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/5.jpg)
ISA (‘is a’) Hierarchies• Hourly-Emp and Contract_EMps are generalized
by Employees name
ssn
Employees
lot
Contract_Emps
hourly_wages
ISA
Hourly_Emps
contractid
hours_worked
![Page 6: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/6.jpg)
ISA (‘is a’) Hierarchies• Overlap constraints: Can Joe be an Hourly_Emps
as well as a Contract_Emps entity? (Allowed/disallowed)
• Covering constraints: – Does every Employees entity also have to
be an Hourly_Emps or a Contract_Emps entity? (Yes/no).
![Page 7: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/7.jpg)
ISA (‘is a’) Hierarchies
•Overlap constraints:▫The subclasses must have disjoint or overlapping sets
of entities
Disjoint(d) Overlap (0)
![Page 8: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/8.jpg)
ISA (‘is a’) Hierarchies•Covering constraints (completeness
constraints): ▫An entity may not belong to any of the subclasses▫Every entity in the superclass must be a member of
some subclass
Partial Total
![Page 9: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/9.jpg)
ISA (‘is a’) Hierarchies
•Multiple specializations
![Page 10: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/10.jpg)
ISA (‘is a’) Hierarchies• Reasons for using ISA: – To add descriptive attributes specific to a
subclass.– i.e. not appropriate for all entities in the
superclass– To identify entities that participate in a
relationship. i.e., not all superclass entities participate
![Page 11: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/11.jpg)
Aggregation• Suppose that we have an entity set called
Projects and that each Projects entity is sponsored by one or more departments.
• A department that sponsors a project might assign employees to monitor the sponsorship.
budgetdidpid
started_on
pbudgetdname
DepartmentsProjects Sponsors
since
NM
![Page 12: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/12.jpg)
Aggregation
• Used to model a relationship between a collection of entities and relationships.
• Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships.
budgetdidpid
started_on
pbudgetdname
until
DepartmentsProjects Sponsors
Employees
Monitors
lotname
ssn
since
![Page 13: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/13.jpg)
Aggregation• Aggregation vs. ternary
relationship: why not make Sponsors a ternary relationship?
• There are really two distinct relationships (monitors and sponsors), each with attribute of its own.
• Monitors is a distinct relationship, with a descriptive attribute.
budgetdidpid
started_on
pbudgetdname
until
DepartmentsProjects Sponsors
Employees
Monitors
lotname
ssn
since
![Page 14: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/14.jpg)
Ternary Relationship
•Suppose that each department has offices in several locations and we want to record the locations at which each employee works.
![Page 15: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/15.jpg)
Conceptual Design Using the ER Model•Design choices:
▫Should a concept be modeled as an entity or an attribute?
▫Should a concept be modeled as an entity or a relationship?
▫Identifying relationships: Binary or ternary? Aggregation?
•Constraints in the ER Model:▫A lot of data semantics can (and should) be
captured.
![Page 16: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/16.jpg)
Entity vs. Attribute
•Should address be an attribute of Employees or an entity (connected to Employees by a relationship)?
Employees
ssnname
address
![Page 17: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/17.jpg)
Entity vs. Attribute
• Depends upon the use we want to make of address information, and the semantics of the data:
• If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued).
• If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic).
Employees
ssnname
address
![Page 18: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/18.jpg)
Entity vs. Attribute• If we have several addresses per employee,
address must be an entity (since attributes cannot be set-valued).
• If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic).
street
citya#
name
ssn
Lives_inEmployees addresses
![Page 19: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/19.jpg)
Entity vs. Attribute
• Does Works_In4 allow an employee to work in a department for two or more periods?
name
Employees
ssn lot
Works_In4
from todname
budgetdid
Departments
![Page 20: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/20.jpg)
Entity vs. Attribute• Similar to the problem of wanting to record several
addresses for an employee: – We want to record several values of the
descriptive attributes for each instance of this relationship.
– Accomplished by introducing new entity set, Duration.
name
Employees
ssn lot
Works_In4
from todname
budgetdid
Departments
![Page 21: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/21.jpg)
dnamebudgetdid
name
Departments
ssn lot
Employees Works_In4
Durationfrom to
name
Employees
ssn lot
Works_In4
from todname
budgetdid
Departments
Entity vs. Attribute
![Page 22: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/22.jpg)
Entity vs. Relationship• Scenario 1: a manager gets a separate
discretionary budget for each department.
• Scenario 2: What if a manager gets a discretionary budget (sum) that covers all managed departments?
Manages2
name dnamebudgetdid
Employees Departments
ssn lot
dbudgetsince
![Page 23: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/23.jpg)
Entity vs. Relationship• What if a manager gets a discretionary budget
(sum) that covers all managed departments?– Redundancy: dbudget stored for each dept
managed by manager.– Misleading: Suggests that dbudget associated
with department-mgr combination.
Manages2
name dname
budgetdid
Employees Departments
ssn lot
dbudgetsince
![Page 24: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/24.jpg)
Entity vs. Relationship
•This fixes the problem!
dname
budgetdid
DepartmentsManages2
Employees
namessn lot
since
Managers dbudget
ISA
![Page 25: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/25.jpg)
Binary vs. Ternary Relationships • An example of ternary relationship :
▫a ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has descriptive attribute qty.
Suppliers
qty
DepartmentsContractParts
![Page 26: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/26.jpg)
Binary vs. Ternary Relationships • No combination of binary relationships is an
adequate substitute.▫ S “can-supply” P, D “needs” P, and D “deals-with”
S does not imply that D has agreed to buy P from S.▫ How do we record qty?
Suppliers
qty
DepartmentsContractParts
Suppliers
Departments
deals-with
Parts
can-supply
needs
VS.
![Page 27: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/27.jpg)
Summary of Conceptual Design• Conceptual design follows requirements analysis• ER model popular for conceptual design
▫Constructs are expressive, close to the way people think about their applications.
▫There are many variations on ER model (both graphically and conceptually)
• Basic constructs ▫entities, relationships, and attributes (of entities
and relationships)• Some additional constructs
▫weak entities, ISA hierarchies, and aggregation
![Page 28: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/28.jpg)
Summary of Conceptual Design• Several kinds of integrity constraints:
▫key constraints▫participation constraints▫overlap/covering for ISA hierarchies.
• Constraints play an important role in determining the best database design for an enterprise.
![Page 29: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/29.jpg)
Summary of Conceptual Design• ER design is subjective. There are often many ways
to model a given scenario! • Analyzing alternatives can be tricky, especially for a
large enterprise. Common choices include: Entity vs. attribute, entity vs. relationship,
binary or n-ary relationship, whether or not to use ISA hierarchies, and whether or not to use aggregation.
• Ensuring good database design: resulting relational schema should be analyzed and refined further. ▫ FD (functional dependency) information and
normalization techniques are especially useful.
![Page 30: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/30.jpg)
![Page 31: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/31.jpg)
![Page 32: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/32.jpg)
Homework 1
•Cover page with name and homework number
•Hard copy preferred•Due Sep 24 (Thur) before class
![Page 33: CMPT 258 Database Systems The Entity-Relationship Model Part II (Chapter 2)](https://reader036.vdocuments.us/reader036/viewer/2022062408/56649eb15503460f94bb7084/html5/thumbnails/33.jpg)
Readings
•Chapter 3