Transcript Data Modeling - Hiram College

Data Modeling
(CB 12)
CPSC 356 Database
Ellen Walker
Hiram College
(Includes figures from Database Systems by Connolly & Begg, © Addison Wesley 2002)
Describing the Data
• DDL (Data Definition Language)
– Detailed definition of database schema
– Too low-level (and database-specific) for good
modeling!
• Record-based model (relational)
– Better, but DBMS dependent
• Object-based model (entity-relationship)
– This is the level to start at!
Entity-Relationship (ER) Modeling
• Entity
– Object (in the C++ sense); “thing”; noun
• Attribute
– Property of an object ; “variable”; adjective
• Relationship
– Association among entities; verb
Relationship vs. Relation
• A relationship (in ER modeling) describes
the connection (often a verb) between two or
more entities
• A relation (in a relational database) is a
table, or a set of tuples (rows).
• Both entities and relationships in the ER can
map to relations in the RDB
• Not all relationships in the ER map to
relations in the RDB
Entity vs. Relation
• An Entity (in ER modeling) nearly always
maps to a Relation (in the RDB)
• A Relation in the RDB does not always maps
to an Entity in the ER
• Common problem: designing your ER
diagram as if it were an RDB - don’t think
about tables yet!
Type vs. Instance
• Entity type -- a class of entity, e.g. Student
• Entity instance (occurrence) -- a specific
entity, e.g. “Student number 123456”
• Shortcut: “entity” means “entity instance”
• Relationships also have type vs. instance
• Databases have schema vs. instance
Attributes
• Specify properties of an entity or relationship
– E.g. NAME is an attribute of the entity STUDENT
– E.g. SINCE is an attribute of the relationship
MAJORS-IN
• The legal values of an attribute are specified
by a domain (e.g. “valid US phone numbers”)
• All entities / relationships of the same type
have the same attributes
Entities and their Attributes
• Faculty-member
– Attributes: name, id, rank, department
• Class
– Attributes: name, number, department
• Department
– Attributes: name, abbreviation
• Caution: an entity cannot be an attribute of
another entity!
A Common Error…
• Attributes in the relational model are not the
same as attributes in the ER model
• Please forget about “tables” for the moment!
• When you find yourself wanting to put an
entity inside another entity, you need to
specify a relationship instead.
Variations on Attributes
• Simple: single component
– (e.g. size-in-square-feet, monthly-rent)
• Composite: multiple components
– (e.g. name = first-name, last-name)
• Multi-valued : many values per entity
– (e.g. bedroom-sizes has as many entries as there
are bedrooms)
• Derived: computed
– (e.g. Duration computed from Start & Finish)
Keys
• A key is an attribute or set of attributes that uniquely
identifies an entity.
• A candidate key is a minimal set of attributes that is a
key.
• The primary key is a candidate key selected by the
user.
• A composite key has more than one attribute.
• (Keys of a relationship include both attributes and
roles)
Relationships
• (1..2) Professor TEACHES (0..*) Class
– Each professor teaches 0 or more classes (no limit) Each
class is taught by 1 or 2 professors (to allow for teamtaught).
• (1..*) Student TAKE (0..*) Class
– Each student takes 0 or more classes (no limit).
– Each class contains 1 or more students (no limit).
• (0..7) Class LOCATED-IN (1..1) Room
– Each class is taught in exactly one room.
– Each room contains 0 or more classes. We assume there
are 7 distinct time periods, so the maximum is 7.
• Numbers in parentheses represent (min..max)
entities allowed (multiplicity)
Degree of a Relationship
• Number of entities that are related = number
of roles in a relationship
• 2 (Binary) is most common
– e.g. Owner OWNS Property
• 3 (Ternary)
– Owner LEASES Property WITH Contract
• 4 (Quaternary)
– Pretty rare!
Roles and Recursion
• A recursive relationship includes the same
entity type more than once
• Roles are relationship-specific names for the
entities that participate
• When 2 different entities of the same entity
type participate in a relation, they are
distinguished by their roles
– Employee (Supervisor) SUPERVISES Employee
(Supervisee)
Attributes on Relationships
• Sometimes, an attribute describes a relationship
rather than one of its entities
• Example:
– Faculty TEACHES Course
– Assume each course has many sections and each faculty
member may teach multiple (but not all) sections of a course
– Then the location and the time of the offering is a property of
the TEACH relationship, not the course
– Note: this is only one way to represent this particular
relationship; creating a “section” entity to hold the location
and time would be another
Brain Teasers
• Is it possible for an entity type to have no
attributes?
• Does an entity type necessarily have to have
a key?
• Is it possible for a relationship type not to
have attributes?
Constraints on Entities &
Relationships
• Multiplicity (min..max)
• Cardinality
• Participation
Cardinality Cases for Binary
Constraints
• One to one (1:1)
– Exactly one of each entity in the relationship (e.g.
buttons to buttonholes)
• One to many (1:N)
– One Room can hold many classes, but each class
is taught in only one room
• Many to many (M:N)
– One student can take many classes, and one
class holds many students
Participation Constraints
• Mandatory participation: All entities of the
appropriate type must participate in this
relationship (e.g. every branch must have a
manager)
• Optional participation: Some entities of the
appropriate type participate in this
relationship (e.g. some employees manage
branches)
Multiplicity = Cardinality &
Participation
• If min > 0, mandatory participation, otherwise
optional
• If both sides have max=1, 1:1 cardinality
• If both sides have max>1, M:N cardinality
• Otherwise 1:N or N:1 cardinality, depending
on which side has max > 1
Strong vs. Weak Entities
• A Strong Entity does not depend on anything
else in the database for its existence. It
contains its own primary key.
• A Weak Entity depends on some related
entity for its existence. It has no candidate
keys without including the primary key of the
entity it depends on.
Strong vs. Weak Example
• Employee ( empNo, fname, lname salary,…)
HAS Child (fname)
• Employee is strong (with empNo as primary
key)
• Child is weak - many children may be called
“Jennifer”, but only one Jennifer is the
daughter of employee 24901.
ER Diagram Example
• Entities, attributes & binary relationship
Class
CRN {PK}
Located >
1..1
0..7
Room
number {PK}
building {PK}
seats
• Cardinality constraints show how many
relationships per entity
Entity with Derived Attribute
ClassPeriod
daysOfWeek {PK}
startTime {PK}
endTime
/length
Weak Entity (no PK)
Offers >
Department
code {PK}
name
0..*
Course
1..1
number
name
Course number is unique within a department, but not overall
Special Relationships
• Ternary
Relationship
(Quarternary
similar)
• Recursive
Relationship
(Figure from Database Systems by Connolly & Begg, © Addison Wesley 2002)
Branch view of Dream Home
Problem: Fan Trap
• Division 1 has Pat, Chris, and Jamie
• Division 1 operates branches 101 and 102
• Who works at branch 101?
Fixing the Fan Trap
Problem: Chasm Trap
• How can we represent a property that has
been assigned to a branch, but has no staff
member to oversee it?
Fixing the Chasm Trap