Database systems
This presentation is the property of its rightful owner.
Sponsored Links
1 / 38

Database Systems ( 資料庫系統 ) PowerPoint PPT Presentation


  • 90 Views
  • Uploaded on
  • Presentation posted in: General

Database Systems ( 資料庫系統 ). 9/27 10/1, 2007 Lecture #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.

Download Presentation

Database Systems ( 資料庫系統 )

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database systems

Database Systems(資料庫系統)

9/27 10/1, 2007

Lecture #2


Course administration

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

Chapter 2Introduction to Database Design


Scenario

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

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

name

name

addr

ManfBy

Beers

Manfs

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


Er model entity

Employees

ER Model: Entity

(‘Joe’: string)

name

(123: integer)

ssn

  • 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

  • Each entity in an entity set is uniquely identified by a key attribute.

(Joe, Alice, ..)


Er model relationship

ER Model: Relationship

(5/2001)

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

since

dname

name

budget

did

ssn

(Joe)

Works_In

(finance dept)

Employees

Departments


Ternary relationship

Ternary Relationship

since

dname

name

budget

did

ssn

Works_In

Employees

Departments

(Joe)

(finance dept)

capacity

address

Locations

(Taipei)


Roles in relationship

Roles in Relationship

name

ssn

(Roles)

Employees

supervisor

subordinate

Reports_To


Key constraints

since

dname

Employees

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 calledone-to-many relation.

name

did

budget

ssn

Departments

Manages

Finance

Mary

3/3/93

Accounting

Joe

2/2/92

Research

Alice

3/1/92

Legal

Peter


More key constraints

1-to Many

1-to-1

Men

Men

Marry

Women

Women

Women

Give Birth

Babies

More Key Constraints

Many-to-Many

Befriend


Participation constraints

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

since

since

name

name

dname

dname

ssn

did

did

budget

budget

lot

Departments

Employees

Manages

Works_In

since


Weak entities

Weak Entities

  • A weak entitycan 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.

(Alicia)

(2)

name

cost

pname

age

ssn

(Hao)

Policy

Dependents

Employees


Isa is a hierarchies

ISA (`is a’) Hierarchies

name

ssn

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

    • Why using ISA?

      • Add descriptive attributesspecific (make sense) to a subclass.

      • Identify entities that make sense to a relationship (policy).

superclass entity

Employees

hours_worked

hourly_wages

ISA

contractid

subclass entities

Contract_Emps

Hourly_Emps


Isa is a constraints

ISA (`is a’) Constraints

name

ssn

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

Employees

hours_worked

hourly_wages

ISA

contractid

Contract_Emps

Hourly_Emps


Aggregation

Employees

Aggregation

name

ssn

  • Create relationship set from relationship sets.

  • Aggregation: relationship set turns into an entity set

    • So that they can participate in (other) relationships.

Monitors

until

since

started_on

dname

pid

pbudget

did

budget

Sponsors

Departments

Projects


Design guideline

Design Guideline

  • Avoid redundancy.

  • Don’t use an entity set when an attribute will do.

  • Limit the use of weak entity sets.


Avoiding redundancy

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.


Redundancy example

Redundancy Example

name

name

addr

ManfBy

Beers

Manfs

manf

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


Fix redundancy

Fix Redundancy

name

name

addr

ManfBy

Beers

Manfs

This design gives the address of each manufacturer exactly once.


Example bad

Example: Bad

name

manf

manfAddr

Beers

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


Don t overuse weak entity sets

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

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.


Exercise 2 2 r g book

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.


Database systems

  • 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


Database systems

  • 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

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.


Database systems

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


Database systems

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

  • Departments have a department number, a department name, and a main office.

  • Department has a professor (known as Chairman) who runs the department.


Database systems

  • 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

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.

  • Design guideline in ER model


Ubicomp project of the week hyperdragging sony csl 1999

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”


Entity sets versus attributes

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.


Example okay

Example: Okay

name

name

addr

ManfBy

Beers

Manfs

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


Example beers entity not needed

Example: Beers Entity not Needed

name

name

addr

ManfBy

Beers

Manfs

  • Beers can be an attribute rather than an entity.


Example okay1

Example: Okay

name

manf

Beers

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


Example bad1

Example: Bad

name

name

ManfBy

Beers

Manfs

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.


  • Login