Transcript Entity Relationship Model Ch. 3

Conceptual Data Models
Chapter 7
Conceptual Data Model
• Requirement collection/analysis results in
well-formed requirements
• Can be used to create conceptual data model
diagram
– Useful in understanding DB
– Used to map to DB model of DBMS
• Examples are:
– ER/EER model
– UML
ER model – Entity-Relationship
• ER model – Chen
– High-level conceptual model
• Concepts
– Entity – basic object from real-world
– Entity type – defines collection/set of entities with
same attributes
• box in diagram
– Entity set – collection of entities of entity type
(instances)
Attributes
• Describes property of entity set
– Oval in ER diagram
• Atomic vs. composite (levels of ovals)
• Single-valued vs. multivalued (double oval)
multivalued composite
• Stored vs. derived (dashed-line oval)
• Key attribute (name underlined)
Attributes cont’d
• Every entity has a value for each attribute
• Null valued
– Not applicable
– Missing
– Not known/not applicable or missing
• Attribute associated with domain of values
Relationship
• Relationship – one entity type refers to
another entity type
• Relationship type – set of associations
between entities
• Relationship set – collection of all
relationships of relationship type
– Diamond in ER diagram
Relationship cont’d
• Relationship types of different degrees:
– Binary (typical) – two entity types participate
– Ternary – three entity types participate
– Higher degrees (rare)
• Relationship instance – associates an entity from
each set that participates
– Can participate in > 1 relationship
• Recursive relationship – same entity participates in
relationship
• Role names (on lines)
Relationships cont’d
• Structural constraints on relationship types
– Cardinality ratio - # of instances
1:1 relationship
1:N relationship
M:N relationship
– Participation constraint
Total participation
Partial participation
• Relationship attributes
Structural Constraints
• 2 options:
1. Combine notation
• Min, Max
2. Separate notation
• One for cardinality ratio
• One for participation constraints
Min,Max
(min, max) where each entity participates in at least min and at
most max relationships
captures both cardinality ratio and participation constraints
If min = 0, means partial, if min >= 1, total participation
e.g. (1, 1) from employee to works_for
(1, N) from department to works_for
Separate notation
• Cardinality ratio( # on line)
• Participation constraint (double line for total)
e.g. If N employees work for 1 department, put N on line
from employee to works_for and 1 on line from
department to works_for
If must work for a department, double line, else single line
If 1:N relationship, N is on opposite side as in previous
notation
Weak entity sets
• Weak entity sets have no key attributes of
their own (double box)
• Always related to identifying owner via
identifying relationship (double diamond)
• Total participation (double line)
• Partial key (dashed underline)
ER diagram
• Fig. 7.7 shows all ER components
Additional notation
• Alternative notations – structural constraints
– One: 1 Many (N) : > (outer notation)
– Mandatory: | Optional: 0 (inner notation)
• ERD Tool
– NOTE: place constraints on opposite side
EID
Name
dname
dnum
Address
location
Employ ee
EID
Department
Works_f or
Name
dname
dnum
Address
location
Employ ee
Works_f or
Department
Mini-world is company DB (textbook)
The database will keep track of employees and departments.
• For each employee we will keep track of their name and their
unique employee ID.
• For each department we will keep track of the unique
department ID and location.
• Each employee reports to exactly one department. A
department may have many employees reporting to it, but it
does not have to have any.
Mini-world is company DB (textbook) –
initial requirements
• Company has departments and department
managers
• Employees in company work for a department and
work on project
• Projects are associated with a department
• Employee has name, SSN, address, salary, sex, birth
date
• For each project keep track of number of hours
worked on project
• Maintain information about dependents for
insurance
Company DB requirements refined
• Company is organized into departments
• Each department has a unique name, unique
number and an employee who manages the
department
• Keep track of the start date when employee
began managing department
• Department has several locations
Company DB requirements
• Department controls a number of projects
• Each project has a unique name, unique
number and a single location
Company DB requirements
• Each employee has a name, SSN, address,
salary, sex, and birth date
• Employee is assigned to one department but
works on several projects which are not
necessarily controlled by the same
department
• Keep track of the number of hours per week
an employee works on each project
• Keep track of supervisor of each employee
Company DB requirements
• Want to keep track of the dependents of each
employee for insurance
• Keep each dependent’s first name, sex, birth
date, relationship to employee
Missing information in requirements
• Planned use? User groups?
• Typical operations?
ER diagram
ER diagram (ERD) for the company database in the 3
different styles follows
7.2
7
Additional notation
• Alternative notations – structural constraints
– One: 1 Many (N) : > (outer notation)
– Mandatory: | Optional: 0 (inner notation)
• ERD Tool
– NOTE: place constraints on opposite side
EID
Name
dname
dnum
Address
location
Employ ee
EID
Department
Works_f or
Name
dname
dnum
Address
location
Employ ee
Works_f or
Department
Company DB using ERD Tool
Locations
Number
Lname
Minit
Name
Address
Fname
Salary
( Name )
Department
Sex
Bdate
Works_For
Employ ee
Start_date
SSN
Manages
Superv ision
Controls
Dependents_Of
Hours
Works_on
Project
Dependent
Name
Name
Relationship
Number
Sex
Birth_date
Location
Num_of _Employ ees
Ternary
• Useful for many-to-many-to-many relationships
• When should one use a ternary?
Company MG (Manufacturing Guru)
– We have multiple products.
– We have multiple suppliers.
– We have multiple components.
– We will keep track of which suppliers provide
which components for which product.
MG
– Every product contains one or more components,
each of which is provided by one supplier.
– Every supplier can provide many components for
many products
– Every component uses one product by one or more
suppliers.
Ternary
• If many-to-many-to-one, can just use binary
relationships (depending on requirements)
Product
Supplier
Uses
Supplies
Component
MG
– Every product contains one or more components,
each of which is provided by one or more supplier.
– Every supplier can provide many components for
many products
– Every component is provided for one or more
products by one or more supplier.
Ternary
• But this example is NOT many-to-many-to-one
but many-to-many-to-many
Supplier
Prov ides
Supplies
Product
Contains
Component
Ternary Relationships
• 3 binary relationships is not sufficient to
model this
• would not keep track which suppliers provide
which components for which product supplier
– A1 supplies the component B for the product C
– supplier A2 does not provide the component B for
the product C
• (i.e. supplier A2 provides the component B, but not for
the product C)
Supplier
Product
Prov ide
Component
UML
UML Notation – Universal Modeling Language
• Objects (entities) are classes (box)
• box contains the following separated by lines:
– Object name
– Attributes
– method names
Attributes in UML
– Composite attribute modeled as structured
domain
• e.g. Name: NameDom
Fname
Minit
Lname
– Multivalued attribute modeled as a separate class
• e.g. separate box, with aggregation notation using a
diamond
– Can specify domain of attribute by using :
• e.g. Sex: {M, F}
Relationships - associations
• Relationship types are associations (lines)
– Can be named (name on line)
• Relationship instances are called links
– (min, max) notation used - same as ERD tool
• * indicates no limit
• Relationship (link) attributes
– Dashed line to box
– Box with association name and attribute
Weak entities
• Weak entity – qualified association
– placed in box directly attached to owner class
– Partial key in box
– Remaining attributes considered an aggregation
• Multi-valued attributes represented by
aggregation notation
Issues
• Primary keys
– Typically added as a tag next to attribute name
Stop here
MG – altered specification
– Every product contains one or more components,
each of which is provided by one or more
suppliers.
– Every supplier can provide many components for
many products, but they also do not have to
provide any components for any products.
– Every component is provided for one or many
products by one or many suppliers.
SUPPLIER
Prov idesFor
Prov ides
PRODUCT
Contains
COMPONENT
SUPPLIER
PRODUCT
Prov ide
COMPONENT
Associative Entity
• No cardinality constraints on the figure
• Where we would put a symbol indicating that
we may record some suppliers who do not
provide any components for any product?
• Use a weak entity OR
• Use an associative entity (diamond inside box)
– Associative entities do not have unique or partially
unique attributes, and often do not have any
attributes at all
Supplier
Product
Prov ides
Uses
Prov iding
IsIncluded
Component
SUPPLIER
PRODUCT
Prov ides
Uses
PROVIDING
IsIncluded
COMPONENT
Retail Company ZAG
The database for the sales department of the retail company ZAG
will capture data about the following:
• For each product being sold: a product ID (unique), product
name, and price.
• For each category of the product: category ID (unique) and
category name
• For each vendor: vendor ID (unique) and vendor name
• For each customer: customer ID (unique),
name and zip-code
• For each store: store ID (unique) and zip code
• For each region: region ID (unique) and region
name
• For each sale transaction: transaction ID
(unique), date and time of transaction.
• Each product belongs to exactly one category.
Each category contains one or more products.
• Each product is supplied by exactly one vendor.
Each vendor supplies one or more products.
• Each store is located in exactly one region.
Each region contains one or more stores.
• Each sales transaction occurs in one store.
Each store has one or more transactions
occurring at it.
• Each sales transaction involves exactly one
customer.
• Each customer can be involved in one or more
sales transactions.
• Each product is sold via one or more sales
transactions.
Each sales transaction includes one or more
products.
For each instance of a product being sold via a
sales transaction, the quantity of sold
products is recorded.