Database Systems (資料庫系統)
September 27/28, 2006
Lecture #2
2
Course Administration
• Office Hour:
– Thur 2-3
• Can everyone get the textbook?
• HW #1 will be on the course homepage later today
– It is due 2 weeks from today.
• Next week reading:
– R&G Chapters 3 & 4.1~4.2
Chapter 2
Introduction to Database Design
4
What is Design?
human shaping our (physical)
environment with no precedent in nature
to serve our needs and give meanings
to our life
6
Shaping Nature (landscape design)
Serve our needs – high utility
8
Give us meaning - significance
Traditional design principle:
form follows function
10
Some high style does not follow this
principle (from philippe starck)
Digital environment never exists in nature
How would you design it?
12
Scenario
• Say if you are hired by iBeer Retailer as a computer consultant.
• iBeer wants you to design its database system.
• How to design it?
Database Design
• Step 1: Requirements Analysis
– What data to store in the database?
– What application (e.g., queries, updates, ..) needs from the database?
• Step 2: Conceptual Database Design
– Come up with the design: Entity-Relation (ER) model
– Sketch the design using pictures called entity-relationship diagrams.
• Step 3: Logical Database Design
– Implement the design: relational data model
– Easy to map ER diagrams into the relational data model (CH 3).
14
Requirement Analysis
• Requirement analysis:
– The Beer retailer wants to keep track of
• Beers on shelves
• Beer manufacturers: [name & address]
• Conceptual database design
– ER diagram
• Logical database design:
– Relational model
台灣生啤酒 青島啤酒 台灣啤酒 Beer names
Beers ManfBy Manfs
name name addr
青島啤酒廠 台灣菸酒公賣 局
Manufacturer
’s names
??
台北市南昌路一 段4號
Manufacturer’s addresses
台灣菸酒公賣局 台灣生啤酒
青島啤酒 台灣啤酒 Beer names
青島啤酒廠 台灣菸酒公賣局 Manufacturer's names
ER Model: Entity
• Proposed by Peter Chen (BS NTU EE ‘68) in 1976.
• Entity: A real-world object distinguishable from other objects (e.g., Joe).
• An entity is described by a set of attributes.
– Each attribute has a domain of possible values.
• Entity Set: a collection of similar entities
Employees ssn
name
(Joe, Alice, ..) (123: integer)
(‘Joe’: string)
16
ER Model: Relationship
• Relationship: Association among two or more entities
– Joe works in finance department.
• A relationship can have descriptive attributes.
– Joe has worked in finance department since 5/2001.
• Relationship Set: Collection of similar relationships.
dname
budget did
since name
Works_In Departments Employees
ssn
(5/2001)
(finance dept) (Joe)
Ternary Relationship
dname
budget did
since name
Works_In Departments Employees
ssn
capacity Locations
address
(Joe) (finance dept)
18
Roles in Relationship
Reports_To name
Employees
supervisor ssn
subordinate
(Roles)
Key Constraints
• Describe at most once (entitity) relationship
– Manages relationship: each department has at most one manager (okay to have none).
– One department can appear at most once in Manages relationship set, also called one-to-many relation.
dname
budget did
since name
ssn
Employees Manages Departments
Mary Finance
3/3/93
20
More Key Constraints
1-to-1 1-to Many Many-to-Many
Women Give Birth Babies
Married Women
Men Men Befriends Women
Participation Constraints
• Describe all (entitity) participation relationship
– Must every department have a manager?
• If yes, this is a participation constraint
– All Departments entities must participate in the Manages relationship set (total participation).
lot
name dname
budget did
since
name dname
budget did
since
Manages Departments
Employees ssn
22
Weak Entities
• A weak entity can be identified uniquely only by considering the key of another (owner) entity.
– Pname = partial key
– 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.
name
pname age
Dependents Employees
ssn
Policy cost
(Alicia) (2)
(Hao)
ISA (`is a’) Hierarchies
• As in C++ and OO languages, attributes are inherited from superclass.
subclass entities
superclass entity
Contract_Emps ssn name
Employees
hourly_wages
ISA
Hourly_Emps
contractid hours_worked
24
ISA (`is a’) Constraints
• 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)
Contract_Emps ssn name
Employees hourly_wages
ISA
Hourly_Emps
contractid hours_worked
Aggregation
• Create relationship set from relationship sets.
• Aggregation: relationship set turns into an entity set
– So that they can participate in (other) relationships.
budget pid did
started_on
pbudget
dname until
Employees
Monitors name ssn
since
26
Design Guideline
1. Avoid redundancy.
2. Don ’t use an entity set when an attribute will do.
3. Limit the use of weak entity sets.
Avoiding Redundancy
• Redundancy occurs when we say the same thing in two different ways.
• Redundancy is bad
– wastes space
– encourages inconsistency.
• The two instances of the same fact may become inconsistent if we change one and forget to change the other instance.
28
Redundancy Example
Beers ManfBy Manfs
name
This design states the manufacturer of a beer twice:
as an attribute and as a related entity.
name
manf
addr
Fix Redundancy
Beers ManfBy Manfs
name name addr
30
Example: Bad
Beers name
This design repeats the manufacturer’s address once for each beer.
Why is it bad?
•Manf updates its address.
•Loses the address if there are temporarily no beers for a manufacturer.
manf manfAddr
Entity Sets Versus Attributes
• Modeling a concept with a new entity set should satisfy at least one of the following conditions:
– It is more than the name of something; it has at least one nonkey attribute.
or
– It is the “many” in a many-one or many-many relationship.
32
Example: Okay
Beers ManfBy Manfs
name
•Manfs deserves to be an entity set because of the nonkey attribute addr.
•Beers deserves to be an entity set because it is the
“many” of the many-one relationship ManfBy.
name addr
Example: Beers Entity not Needed
Beers ManfBy Manfs
name
•Beers can be an attribute rather than an entity.
name addr
34
Example: Okay
Beers name
There is no need to make the manufacturer an entity set, because we record nothing about manufacturers besides their name.
manf
Example: Bad
Beers ManfBy Manfs
name
Since the manufacturer is nothing but a name, and is not at the “many” end of any relationship, it should
name
36
Don ’t Overuse Weak Entity Sets
• Beginning database designers often doubt that anything could be a key by itself.
– They make all entity sets weak, supported by all other entity sets to which they are linked.
• In reality, we usually create unique ID’s for entity sets.
– Examples include social-security numbers, automobile VIN’s etc.
When Do We Need Weak Entity Sets?
• The usual reason is that there is no global authority capable of creating unique ID ’s.
• Example: it is unlikely that there could be an agreement to assign unique player
numbers across all football teams in the
world.
38
Exercise 2.2 (R-G Book)
A university database contains information about professors (identified by social security number) and courses
(identified by courseid). Professors teach courses; each of the following situations concerns the Teaches relationship set. For each situation, draw an ER diagram that describes it.
• Professors can teach the same course in several semesters, and each offering must be recorded.
• Professors can teach the same course in several
semesters, and only the most recent such offering needs to be recorded.
• Every professor must teach some courses
40
• Every professor teaches exactly one course (no more, no less)
• Every professor teaches exactly one course (no more, no less), and every course must be taught by some professor
Exercise 2.3 (R-G Book)
• Professors have an SSN, a name, an age, a rank, and a research specialty.
• Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.
42
• Graduate students have an SSN, a name, an age, and a degree program
• Each project is managed by exactly one professor (known as PI)
• Each project is worked in by one or more professors (known as Co-PIs)
• Each project is worked on by one or more graduate students (known as RAs)
• When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a potentially different supervisor for each one
• Departments have a department number, a department name, and a main office.
• Department has a professor (known as Chairman) who runs the department.
44
• Professors work in one or more departments, and for each department that they work in, a time percentage is associated with their job
• Graduate students have one major department in which they are working on their degree.
• Each graduate student must have another, more senior graduate student as an advisor.
Summary
• ER model is popular for conceptual design
– Sketch the design of a database informally using pictures
• Basic constructs in ER model:
– entities, relationships, and attributes (of entities and relationships).
• Some additional constructs:
– weak entities, ISA hierarchies, and aggregation.
• Several kinds of integrity constraints:
– key constraints, participation constraints, and overlap/covering constraints for ISA hierarchies.
46
Ubicomp Project of the Week:
Hyperdragging (SONY CSL, 1999)
• How to bring human computer interaction (desktop PC) into the physical environment (just a bit)?
– “Disappearing computing”
