Database Systems (資料庫系統) Lecture #2

Database Systems

( 資料庫系統

₎

September 26, 2005

Lecture #2

2

Course Administration

• Can everyone get the textbook?

– There are 2 books for loan in the TA’s office (336/338)

• HW #1 will be on the course homepage later today

– It is due 3 weeks from today.

• Next week reading:

Ubicomp Project of the Week:

Hyperdragging (SONY CSL, 1999)

• How to bring human computer interaction

(desktop PC) into the physical environment (just a bit)?

4

Chapter 2

Introduction to Database

Design

Scenario

• Say if you are hired by the bCool Beer retailer as a computer consultant.

• bCool wants you to design its database system.

6

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).

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

Manufactu rer’s names Manufacture r’s addresses 台灣菸酒公賣 台北市南昌路一

Beer names Manufacture r's names

台灣啤酒 台灣菸酒公賣局 台灣菸酒公賣局 台灣菸酒公賣局

8

ER Model: Entity

• Proposed by Peter Chen (BS NTU EE ‘68) in 1976.

• Entity: 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

• Each entity in an entity set is uniquely identified

by a key attribute. Employees ssn name (Joe, Alice, ..) (123: integer) (‘Joe’: string)

ER Model: Relationship

• Relationship: Association among two or more entities

– Joe works in finance department.

• A relationship can have description 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)

10

Ternary Relationship

(Joe) (finance dept)

Roles in Relationship

12

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

Joe Alice Mary Peter Finance Accountin g Research Legal 3/3/9 3 2/2/9 2 3/1/9 2

More Key Constraints

1-to-1 1-to Many _Many-to-Many

Women Give Birth _Babies

Married Women

14

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 since Departments Employees ssn Works_In

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 age pname Dependents Employees ssn Policy cost (Alicia) (2) (Hao)

16

ISA (`is a’) Hierarchies

• _{As in C++ and OO languages, attributes are inherited from}

superclass.

• A ISA B, every A entity is also considered to be a B entity.

• _{Reason for using ISA:}

• _{Add descriptive attributes specific (make sense) to a subclass.} • _{Identify entities that make sense to a relationship (policy).}

subclass entities superclass entity Contract_Emps name ssn Employees hourly_wages ISA Hourly_Emps contractid hours_worked

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

Contract_Emps name ssn Employees hourly_wages ISA Hourly_Emps contractid hours_worked

18

Aggregation

• Create relationship set

from relationship sets.

• Aggregation:

relationship set turns into an entity set

– So that they can

participate in (other) relationships. budget did pid started_on pbudget dname until Departments Projects Sponsors Employees Monitors name ssn since

Design Guideline

1. Avoid redundancy.

2. Don’t use an entity set when an

attribute will do.

20

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

Redundancy Example

Beers _ManfBy Manfs

name

This design states the manufacturer of a beer twice: as an attribute and as a related

name

manf

22

Fix Redundancy

Beers _ManfBy Manfs

name

This design gives the address of each manufacturer exactly once.

23

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

24

Entity Sets Versus

Attributes

• An 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.

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.

26

Example: Okay

Beers name

There is no need to make the manufacturer an entity set, because we record nothing about manufacturers besides their name.

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 not be an entity set.

28

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.

30

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.

32 • 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

34

• 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 on by one or more professors (known as Co-PIs) • Each project is worked on by one or more graduate students (known

• 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.

36

• 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.

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.