Transcript Data Modeling - Computer Science at Hiram College

Conceptual Design
(CB 16)
CPSC 356 Database
Ellen Walker
Hiram College
(Includes figures from Database Systems by Connolly & Begg, © Addison Wesley 2002)
)
Levels of Database Design
• Conceptual database design
– Identify important entities, relationships, and
attributes
• Logical database design
– Translate concepts to logical structure (relations)
• Physical database design
– Determine how logical structure is physically
implemented in the target DBMS
Critical Success Factors
• Work interactively with the user as much as
possible
• Follow a structured methodology (process)
• Be data-driven
• Incorporate structural and integrity constraints
• Use diagrams (e.g. ER)
• Build a data dictionary
• Be willing to repeat steps!
Conceptual Design Steps
• Identify entity types
• Identify relationship types
• Identify and associate attributes for entities &
relationships
• Determine attribute domains
• Determine key attributes (especially PK)
• Check for redundancy
• Validate against transactions
• Review with user
Identifying Entity Types
• Generally nouns and noun phrases in user
specification
• “Major” nouns -- if it qualifies another noun, it
might be an attribute
• But, attributes can’t have attributes (!)
• Realize, initial decisions might have to be
changed
Entities for Dream Home
DreamHome has branch offices in cities
throughout the UK. Each office is allocated
members of staff, including a Manager…
Each branch office offers a range of
properties for rent. … the management of a
property is assigned to a member of staff
whenever it is rented out …
Entities for Dream Home (cont’d)
The details of property owners are also
stored … The data stored for private owners
includes owner number, name, address,
telephone number, email and password.
DreamHome refers to members of the public
interested in renting as clients. … When a
property is rented out, a lease is drawn up
between the client and the property…
Find the Entities in this 2004 Project:
This database will be a campus-wide
inventory of all plants on campus that would
include information like GPS coordinates,
growing hints, and uses. We also want to list
all the gardens and their contents. We would
also like to keep track of our members and
useful things about them with respect to
gardens.
Data Dictionary
• Collects all information about each entity, e.g.
–
–
–
–
Entity name
Description
Aliases (other names)
Occurrence
• Data dictionary serves as a blueprint, also
helps identify duplication (synonyms)
Identifying Relationships
• Look for verbs or verbal expressions in the
specfication
• Relationships must be between entities
• Most relationships are binary
• Consider implicit relationships
– Check all pairs of entities ?
– Missing relationships will show up in validation
• Add relationships and multiplicity constraints
to data dictionary
Relationships for Dream Home
DreamHome has branch offices in cities
throughout the UK. Each office is allocated
members of staff, including a Manager…
Each branch office offers a range of
properties for rent. … the management of a
property is assigned to a member of staff
whenever it is rented out …
Relationships for Dream Home
(cont’d)
The details of property owners are also
stored … The data stored for private owners
includes owner number, name, address,
telephone number, email and password.
DreamHome refers to members of the public
interested in renting as clients. … When a
property is rented out, a lease is drawn up
between the client and the property…
Data Dictionary Excerpt
(Entities & Relationships)
Entity Name
Description
Aliases
Staff
Describes
anyone
employed by
DreamHome
Employee Each member of staff
works at one branch
PropertyForRent
A property that
can be (or is)
rented
Property
Connelly & Begg Figure 6.1
Occurrences
Each property has one
owner and is available
at one branch,where the
property is managed by
one member of staff. It
can be viewed by many
clients and rented by
one client at a time.
Relationships in the 2004 Project?
This database will be a campus-wide
inventory of all plants on campus that would
include information like GPS coordinates,
growing hints, and uses. We also want to list
all the gardens and their contents. We would
also like to keep track of our members and
useful things about them with respect to
gardens.
Attributes
• “What do we need to know / store about X?”
– X is an entity or relationship
• Simple vs. composite attributes
– Based on client’s needs
– Can change later if needed
– If composite, note the parts e.g. addressStreet,
addressCity, addressZip
• Derived(computed) attributes
– E.g. age derived from birthdate & current year
Attribute Example
The details of property owners are also stored
… The data stored for private owners
includes owner number, name, address,
telephone number, email and password.
Modifying Entities
(Relationships vs. Attributes)
• An entity cannot be an attribute of another
entity - use a relationship instead
• Multi-valued attributes are better expressed
as relationships (e.g. phone #)
– If you don’t do it now, that’s OK because it will be
fixed later.
Documenting Attributes
•
•
•
•
•
•
Name and description
Data type and length (domain)
Aliases
Simple attributes, if composite
How to compute, if derived
Default value, if any
Attributes in Data Dictionary
Attribute
Description
Data
Type
Null
OK?
Multivalued?
staffNo
Unique id for staff
member
Integer
No
No
name
Composite;
consists of fname,
lname)
fname
First name
15 chars Yes
(variable)
No
Lname
Last name
30 chars No
(variable)
No
position
Job title
10 chars No
(variable)
No
DOB
Date of birth
Date
No
Yes
Attributes in 2004 Project?
• This database will be a campus-wide
inventory of all plants on campus that would
include information like GPS coordinates,
growing hints, and uses. We also want to list
all the gardens and their contents. We would
also like to keep track of our members and
useful things about them with respect to
gardens.
Key Determination
• Candidate keys are smallest sets of
attributes that uniquely define an entity
• Primary key is a chosen candidate key
• Choose so that:
– Minimal number of attributes
– Values least likely to change
– Smallest (e.g. fewest characters, smallest max
value)
– Easiest for user (search by…)
Which are Candidate Keys for Staff?
Why (Not)?
•
•
•
•
•
•
•
Fname?
Lname?
Fname, Lname?
Lname, DOB?
Fname, Lname, DOB?
StaffNo?
StaffNo, Lname?
Documentation
• Document keys in data dictionary
• Note weak entities (no key)
ER Diagram contains…
• Entities (box – above line)
• Attributes (box – below line)
• Relationship (connecting line with arrow)
– Cardinalities (x..y at each end of line)
– Relationship attributes (box with empty top, dotted
line to relationship line)
DreamHome ER Diagram
Figure 16.5
Check for Redundancy
• Redundancy in a database is bad
– Cost of multiple-entry
– Risk of inconsistency
• Recheck 1:1 relationships
– Should entities be merged?
• Remove redundant relationships
– If you can get from entity X to entity Y through 2 or more
paths, consider whether relationships can be eliminated
(direct vs. indirect)
– Not all cycles should be eliminated, depending on meaning
of the relationship [Document decisions]
Consider Transactions
• A transaction is something the user wants to
do with the database (e.g. print a student
schedule)
– Can the transaction be carried out using only the
information in the ER diagram?
– If so, you should be able to follow a path (of
relationships) in the ER diagram to collect the
data. (See p. 457)
Use Cases and Transactions
• Each use case describes a transaction
• Evaluate the model for each use case
– Is there a missing path (relationship) to get the
needed data? If so, consider what entities,
relationships and/or attributes need to be added.
– Are there portions of the diagram that are never
used? If so, consider removing those entities,
relationships, and/or attributes from the database.
Review Results with User
• User should “sign off” that the model
accurately reflects their understanding…
• Do not expect the user to understand the ER
diagram directly, without significant
explanation from you
• Review the data dictionary with the user to
ensure you’ve got correct entities