Download Database Design: ER Model and Relational Database Design and more Summaries Design in PDF only on Docsity!
Conceptual Design
and The Entity-
Relationship Model
CS 186 Spring 2006 Lectures 19 & 20 R &G - Chapter 2
A relationship, I think, is like a
shark, you know? It has to
constantly move forward or it
dies. And I think what we got on
our hands is a dead shark.
Woody Allen (from Annie Hall, 1979)
Steps in Database Design
• Requirements Analysis
– user needs; what must database do?
• Conceptual Design
– high level descr (often done w/ER model)
• Logical Design
– translate ER into DBMS data model
• Schema Refinement
– consistency, normalization
• Physical Design - indexes, disk layout
• Security Design - who accesses what, and how
Databases Model the Real World
• “Data Model” allows us to translate real
world things into structures computers
can store
• Many models: Relational, E-R, O-O,
Network, Hierarchical, etc.
• Relational
– Rows & Columns
Enrolled– Keys & Foreign Keys to link Relations
sid cid grade Students 53666 Carnatic101 C 53666 Reggae203 B 53650 Topology112 A 53666 History105 B
sid name login age gpa 53666 Jones jones@cs 18 3. 53688 Smith smith@eecs 18 3. 53650 Smith smith@math 19 3.
Conceptual Design
• What are the entities and relationships in
the enterprise?
• What information about these entities and
relationships should we store in the
database?
• What are the integrity constraints or
business rules that hold?
• A database `schema’ in the ER Model can
be represented pictorially ( ER diagrams).
• Can then map an ER diagram into a
relational schema.
ER Model Basics
• Entity: Real-world object, distinguishable from
other objects. An entity is described using a set
of attributes.
• Entity Set: A collection of similar entities. E.g.,
all employees.
– All entities in an entity set have the same set
of attributes. (Until we consider hierarchies,
anyway!)
– Each entity set has a key (underlined).
– Each attribute has a domain.
Employees
ssn
name
lot ER Model Basics (Contd.)
- Relationship: Association among two or more entities. E.g., Attishoo works in Pharmacy department. - relationships can have their own attributes.
- Relationship Set: Collection of similar relationships.
- An n-ary relationship set R relates n entity sets E 1 ... E (^) n ;
each relationship in R involves entities e 1 E 1 , ..., e n E n
lot
name
Employees
ssn
Works_In
since dname did budget
Departments
ER Model Basics (Cont.)
• Same entity set can participate in different
relationship sets, or in different “roles” in
the same set.
subor- dinate
super- visor Reports_To
since
Works_In
dname did budget
Departments
lot
name
Employees
ssn Key Constraints
An employee can
work in many
departments; a
dept can have
many employees.
Many-to- 1-to Many 1-to- Many
since
Manages
dname did budget
Departments
since
Works_In
lot
name ssn
Employees
In contrast, each dept
has at most one
manager, according
to the key constraint
on Manages.
Participation Constraints
- Does every employee work in a department?
- If so, this is a participation constraint
- the participation of Employees in Works_In is said to be
total (vs. partial)
- What if every department has an employee working in it?
- Basically means “at least one”
lot
name dname did budget
name since dname did budget
since
Manages
since
Employees Departments
ssn
Works_In Means: “exactly one”
Weak Entities
A weak entity can be identified uniquely only by
considering the primary key of another
( owner) entity.
– Owner entity set and weak entity set must
participate in a one-to-many relationship set (one
owner, many weak entities).
– Weak entity set must have total participation in
this identifying relationship set.
lot
name pname age
Employees Dependents
ssn
Policy
cost
Weak entities have only a “partial key” (dashed underline)
Binary vs. Ternary Relationships
If each policy is owned by just 1 employee: (^) Bad design
Beneficiary
pname age
Dependents
policyid cost
Policies
Purchaser
name
Employees
ssn lot
Better design
- Think through all the constraints in the 2nd diagram!
Policies policyid (^) cost
pname age Covers Dependents
name
Employees
ssn lot
Key constraint on Policies would mean policy can only cover 1 dependent!
Binary vs. Ternary Relationships (Contd.)
• Previous example illustrated a case when two binary
relationships were better than one ternary.
• An example in the other direction: a ternary
relation Contracts relates entity sets Parts,
Departments and Suppliers, and has descriptive
attribute quantity.
– No combination of binary relationships is an
adequate substitute.
Suppliers
quantity
Parts Contract Departments
Entity vs. Attribute (Cont.)
- Works_In2 does not allow an employee to work in a department for two or more periods.
- 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.
name
Employees
ssn lot
Works_In
from to dname did budget Departments
dname did budget
name
Departments
ssn lot
Employees Works_In
from Duration to
Entity vs. Relationship
OK as long as a manager gets a separate discretionary budget
( dbudget) for each
dept. What if manager’s
dbudget covers all
managed depts? (can repeat value, but such redundancy is problematic)
Manages
name (^) dname did budget
Employees Departments
ssn lot
since dbudget
Employees
since
name dname did budget
Departments
ssn (^) lot
Mgr_Appts
is_manager
dbudget
apptnum
managed_by
These things get pretty hairy!
- Many E-R diagrams cover entire walls!
- A modest example:
A Cadastral E-R Diagram
A Cadastral E-R Diagram
cadastral: showing or recording property boundaries, subdivision lines, buildings, and related details
Source: US Dept. Interior Bureau of Land Management, Federal Geographic Data Committee Cadastral Subcommittee http://www.fairview-industries.com/standardmodule/cad-erd.htm
Logical DB Design: ER to Relational
• Entity sets to tables.
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
Employees
ssn
name lot
ssn name lot 123-22-3666 Attishoo 48 231-31-5368 Smiley 22 131-24-3650 Smethurst 35
Relationship Sets to Tables
• In translating a many-to-
many relationship set to a
relation, attributes of the
relation must include:
1) Keys for each
participating entity set
(as foreign keys). This
set of attributes forms
a superkey for the
relation.
2) All descriptive
attributes.
CREATE TABLE Works_In( ssn CHAR(1), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments)
ssn did since
Review: Key Constraints
- Each dept has at most one manager, according to the
key constraint on
Manages.
Translation to relational model?
1-to-1 1-to Many Many-to-1 Many-to-Many
dname did budget
since
lot
name ssn
Employees Manages Departments
Translating ER with Key Constraints
• Since each department has a unique manager, we
could instead combine Manages and Departments.
CREATE TABLE Manages( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments)
CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees)
Vs.
dname did budget
since lot
name ssn
Employees Manages Departments
Review: Participation Constraints
- Does every department have a manager?
- If so, this is a participation constraint: the participation of
Departments in Manages is said to be total (vs. partial).
- Every did value in Departments table must appear in a
row of the Manages table (with a non-null ssn value!)
lot
name dname did budget
since name dname did budget
since
Manages
since
Employees Departments
ssn
Works_In
Participation Constraints in SQL
- We can capture participation constraints involving one entity set in a binary relationship, but little else (without resorting to CHECK constraints).
CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11) NOT NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE NO ACTION)
Review: Weak Entities
- A weak entity can be identified uniquely only by
considering the primary key of another ( owner) entity.
- Owner entity set and weak entity set must participate in a one-to-many relationship set (1 owner, many weak entities).
- Weak entity set must have total participation in this
identifying relationship set.
lot
name pname age
Employees Dependents
ssn
Policy
cost
Constraints Over Multiple Relations
CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( ( SELECT COUNT (S.sid) FROM Sailors S)
- (SELECT COUNT (B.bid) FROM Boats B) < 100 )
- Awkward and wrong!
- Only checks sailors!
- Only required to hold if the associated table is non-empty.
- ASSERTION is the right solution; not associated with either table.
- Unfortunately, not supported in many DBMS.
- Triggers are another solution.
CREATE ASSERTION smallClub CHECK ( (SELECT COUNT (S.sid) FROM Sailors S)
- ( SELECT COUNT (B.bid) FROM Boats B) < 100 )
Number of boats plus number of sailors is < 100
Or, Use a Trigger
- Trigger: procedure that starts automatically if specified changes occur to the DBMS
- Three parts:
- Event (activates the trigger)
- Condition (tests whether the triggers should run)
- Action (what happens if the trigger runs)
- Triggers (in some form) are supported by most DBMSs; Assertions are not.
- Support for triggers is defined in the SQL: standard.
Triggers
- Cannot be called directly – initiated by events on the database.
- Can be synchronous or asynchronous with respect to the transaction that causes it to be fired.
CREATE TRIGGER trigger_name ON TABLE {FOR {[INSERT][,][UPDATE][,][DELETE]} [WITH APPEND] AS sql-statements
Triggers: Example
CREATE TRIGGER member_delete ON member FOR DELETE AS IF (Select COUNT () FROM loan INNER JOIN deleted ON loan.member_no = deleted.member_no) > 0 BEGIN PRINT ‘ERROR - member has books on loan.’ ROLLBACK TRANSACTION END ELSE DELETE reservation WHERE reservation.member_no = deleted.member_no*
Summary: Triggers, Assertions,
Constraints
- Very vendor-specific (although standard has been developed).
- Triggers vs. Contraints and Assertions:
- Triggers are “operational”, others are declarative.
- Triggers can make the system hard to understand if not used with caution. - ordering of multiple triggers - recursive/chain triggers
- Triggers can be hard to optimize.
- But, triggers are also very powerful.
- Use to create high-performance, “ active” databases.
Summary of Conceptual Design
- Conceptual design follows requirements analysis,
- Yields a high-level description of data to be stored
- ER model popular for conceptual design
- Constructs are expressive, close to the way people think about their applications.
- Note: 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.
Summary of ER (Cont.)
• Several kinds of integrity constraints:
– key constraints
– participation constraints
– overlap/covering for ISA hierarchies.
• Some foreign key constraints are also implicit in
the definition of a relationship set.
• Many other constraints (notably, functional
dependencies) cannot be expressed.
• Constraints play an important role in determining
the best database design for an enterprise.
Summary of ER (Cont.)
- 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, aggregation.
- Ensuring good database design: resulting relational schema should be analyzed and refined further. - Functional Dependency information and normalization techniques are especially useful.
