cmsc424 database design n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CMSC424: Database Design PowerPoint Presentation
Download Presentation
CMSC424: Database Design

Loading in 2 Seconds...

play fullscreen
1 / 54

CMSC424: Database Design - PowerPoint PPT Presentation


  • 80 Views
  • Uploaded on

CMSC424: Database Design. Instructor: Amol Deshpande amol@cs.umd.edu. Data Modeling. Goals: Conceptual representation of the data “Reality” meets “bits and bytes” Must make sense, and be usable by other people Today: Entity-relationship Model Relational Model.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CMSC424: Database Design' - ulema


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
cmsc424 database design

CMSC424: Database Design

Instructor: Amol Deshpande

amol@cs.umd.edu

CMSC424, Spring 2005

data modeling
Data Modeling
  • Goals:
    • Conceptual representation of the data
    • “Reality” meets “bits and bytes”
    • Must make sense, and be usable by other people
  • Today:
    • Entity-relationship Model
    • Relational Model

CMSC424, Spring 2005

motivation
Motivation
  • You’ve just been hired by Bank of America as their DBA for their online banking web site.
  • You are asked to create a database that monitors:
    • customers
    • accounts
    • loans
    • branches
    • transactions, …
  • Now what??!!!

CMSC424, Spring 2005

database design steps
Three Levels of Modeling

info

Physical DB design

Conceptual DB design

Logical DB design

Database Design Steps

Entity-relationship Model

Typically used for conceptual

database design

Conceptual Data Model

Logical Data Model

Relational Model

Typically used for logical

database design

Physical Data Model

CMSC424, Spring 2005

entity relationship model
Entity-Relationship Model
  • Two key concepts
    • Entities:
      • An object that exists and is distinguishable from other objects
        • Examples: Bob Smith, BofA, CMSC424
      • Have attributes (people have names and addresses)
      • Form entity sets with other entities of the same type that share the same properties
        • Set of all people, set of all classes
      • Entity sets may overlap
        • Customers and Employees

CMSC424, Spring 2005

entity relationship model1
Entity-Relationship Model
  • Two key concepts
    • Relationships:
      • Relate 2 or more entities
        • E.g. Bob Smith has account at College Park Branch
      • Form relationship sets with other relationships of the same type that share the same properties
        • Customers have accounts at Branches
      • Can have attributes:
        • has account at may have an attribute start-date
      • Can involve more than 2 entities
        • Employee works at Branch at Job

CMSC424, Spring 2005

er diagram starting example
Rectangles: entity sets

Diamonds: relationship sets

Ellipses: attributes

access-date

cust-name

number

cust-id

has

customer

account

cust-street

cust-city

balance

ER Diagram: Starting Example

CMSC424, Spring 2005

rest of the class
Rest of the class
  • Details of the ER Model
    • How to represent various types of constraints/semantic information etc.
  • Design issues
  • A detailed example

CMSC424, Spring 2005

next relationship cardinalities
Next: Relationship Cardinalities
  • We may know:

One customer can only open one account

OR

One customer can open multiple accounts

  • Representing this is important
  • Why ?
    • Better manipulation of data
    • Can enforce such a constraint
    • Remember: If not represented in conceptual model, the domain knowledge may be lost

CMSC424, Spring 2005

mapping cardinalities
Mapping Cardinalities
  • Express the number of entities to which another entity can be associated via a relationship set
  • Most useful in describing binary relationship sets

CMSC424, Spring 2005

mapping cardinalities1
Mapping Cardinalities
  • One-to-One
  • One-to-Many
  • Many-to-One
  • Many-to-Many

has

customer

account

has

customer

account

has

customer

account

has

customer

account

CMSC424, Spring 2005

mapping cardinalities2
Mapping Cardinalities
  • Express the number of entities to which another entity can be associated via a relationship set
  • Most useful in describing binary relationship sets
  • N-ary relationships ?

CMSC424, Spring 2005

next types of attributes
Next: Types of Attributes
  • Simple vs Composite
    • Single value per attribute ?
  • Single-valued vs Multi-valued
    • E.g. Phone numbers are multi-valued
  • Derived
    • If date-of-birth is present, age can be derived
    • Can help in avoiding redundancy, enforcing constraints etc…

CMSC424, Spring 2005

types of attributes

access-date

cust-name

number

cust-id

has

customer

account

cust-street

cust-city

balance

Types of Attributes

CMSC424, Spring 2005

types of attributes1
multi-valued (double ellipse)

derived (dashed ellipse)

access-date

number

has

account

balance

Types of Attributes

age

cust-name

cust-id

customer

date-of-birth

cust-street

phone no.

cust-city

CMSC424, Spring 2005

types of attributes2

access-date

number

has

account

balance

Types of Attributes

age

cust-name

cust-id

customer

date-of-birth

cust-street

phone no.

cust-city

month

day

year

Composite Attribute

CMSC424, Spring 2005

next keys
Next: Keys
  • Key = set of attributes identifying individual entities or relationships

CMSC424, Spring 2005

entity keys
Entity Keys

Possible Keys:

{cust-id}

{cust-name, cust-city, cust-street}

{cust-id, age}

cust-name ?? Probably not.

Domain knowledge dependent !!

date-of-birth

cust-name

cust-id

customer

age

cust-street

phone no.

cust-city

CMSC424, Spring 2005

entity keys1
Entity Keys
  • Superkey
    • any attribute set that can distinguish entities
  • Candidate key
    • a minimal superkey
      • Can’t remove any attribute and preserve key-ness
        • {cust-id, age} not a superkey
        • {cust-name, cust-city, cust-street} is
          • assuming cust-name is not unique
  • Primary key
    • Candidate key chosen as the key by DBA
    • Underlined in the ER Diagram

CMSC424, Spring 2005

entity keys2
{cust-id} is a natural primary key

Typically, SSN forms a good primary key

Try to use a candidate key that rarely changes

e.g. something involving address not a great idea

Entity Keys

date-of-birth

cust-name

cust-id

customer

age

cust-street

phone no.

cust-city

CMSC424, Spring 2005

relationship set keys

access-date

number

cust-id

has

customer

account

Relationship Set Keys
  • What attributes are needed to represent a relationship completely and uniquely ?
    • Union of primary keys of the entities involved, and relationship attributes
    • {cust-id, access-date, account number} describes a relationship completely

CMSC424, Spring 2005

relationship set keys1

access-date

number

cust-id

has

customer

account

Relationship Set Keys
  • Is {cust-id, access-date, account number} a candidate key ?
    • No. Attribute access-date can be removed from this set without losing key-ness
    • In fact, union of primary keys of associated entities is always a superkey

CMSC424, Spring 2005

relationship set keys2
Relationship Set Keys
  • Is {cust-id, account-number} a candidate key ?
    • Depends

access-date

number

cust-id

has

customer

account

CMSC424, Spring 2005

relationship set keys3
Relationship Set Keys
  • Is {cust-id, account-number} a candidate key ?
    • Depends

access-date

number

cust-id

has

customer

account

  • If one-to-one relationship, either {cust-id} or {account-number} sufficient
    • Since a given customer can only have one account, she can only participate in one relationship
    • Ditto account

CMSC424, Spring 2005

relationship set keys4
Relationship Set Keys
  • Is {cust-id, account-number} a candidate key ?
    • Depends

access-date

number

cust-id

has

customer

account

  • If one-to-many relationship (as shown), {account-number} is a candidate key
    • A given customer can have many accounts, but at most one account holder per account allowed

CMSC424, Spring 2005

relationship set keys5
Relationship Set Keys
  • General rule for binary relationships
    • one-to-one: primary key of either entity set
    • one-to-many: primary key of the entity set on the many side
    • many-to-many: union of primary keys of the associate entity sets
  • n-ary relationships
    • More complicated rules

CMSC424, Spring 2005

next data constraints
Next: Data Constraints
  • Representing semantic data constraints
    • We already saw constraints on relationship cardinalities

CMSC424, Spring 2005

participation constraint
Participation Constraint
  • Given an entity set E, and a relationship R it participates in:
    • If every entity in E participates in at least one relationship in R, it is total participation
    • partial otherwise

CMSC424, Spring 2005

participation constraint1
Participation Constraint

access-date

cust-name

number

cust-id

has

customer

account

cust-street

cust-city

balance

Total participation

CMSC424, Spring 2005

cardinality constraints
Cardinality Constraints

How many relationships can an entity participate in ?

access-date

number

cust-id

has

customer

account

0..*

1..1

Minimum - 0

Maximum – no limit

Minimum - 1

Maximum - 1

CMSC424, Spring 2005

next recursive relationships
Next: Recursive Relationships
  • Sometimes a relationship associates an entity set to itself

CMSC424, Spring 2005

recursive relationships
Must be declared with rolesRecursive Relationships

emp-name

emp-id

works-for

manager

employee

worker

emp-street

emp-city

CMSC424, Spring 2005

next weak entity sets
Next: Weak Entity Sets
  • An entity set without enough attributes to have a primary key
  • E.g. Transaction Entity
    • Attributes:
      • transaction-number, transaction-date, transaction-amount, transaction-type
      • transaction-number: may not be unique across accounts

CMSC424, Spring 2005

weak entity sets
Weak Entity Sets
  • A weak entity set must be associated with an identifying or owner entity set
  • Account is the owner entity set for Transaction

CMSC424, Spring 2005

weak entity sets1
Weak Entity Sets

Still need to be able to distinguish between different

weak entities associated with the same strong entity

number

trans-date

trans-number

has

Transaction

account

trans-type

balance

trans-amt

CMSC424, Spring 2005

weak entity sets2
Weak Entity Sets

Discriminator: A set of attributes that can be used for that

number

trans-date

trans-number

has

Transaction

account

trans-type

balance

trans-amt

CMSC424, Spring 2005

weak entity sets3
Weak Entity Sets
  • Primary key:
    • Primary key of the associated strong entity + discriminator attribute set
    • For Transaction:
      • {account-number, transaction-number}

CMSC424, Spring 2005

next specialization
Next: Specialization
  • Consider entity person:
    • Attributes: name, street, city
  • Further classification:
    • customer
      • Additional attributes: customer-id, credit-rating
    • employee
      • Additional attributes: employee-id, salary
  • Note similarities to object-oriented programming

CMSC424, Spring 2005

specialization example
Specialization: Example

CMSC424, Spring 2005

finally aggregation
Finally: Aggregation
  • No relationships between relationships
    • E.g.: Associate account officers with has account relationship set

has

customer

account

?

account officer

employee

CMSC424, Spring 2005

finally aggregation1
Finally: Aggregation
  • Associate an account officer with each account ?
    • What if different customers for the same account can have different account officers ?

has

customer

account

?

account officer

employee

CMSC424, Spring 2005

finally aggregation2
Finally: Aggregation
  • Solution: Aggregation

has

customer

account

account officer

employee

CMSC424, Spring 2005

slide43
More…
  • Read Chapter 2 for:
    • Specialization/Aggregation details
      • Different types of specialization’s etc
    • Generalization: opposite of specialization
    • Lower- and higher-level entities
    • Attribute inheritance

CMSC424, Spring 2005

slide44
An Example: Employees can have multiple phones

phone_loc

phone_no

E/R Data ModelDesign Issue #1: Entity Sets vs. Attributes

(b)

(a)

Uses

Employee

vs

Phone

Employee

loc

no

  • To resolve, determine how phones are used
    • 1. Can many employees share a phone?
    • (If yes, then (b))
    • 2. Can employees have multiple phones?
    • (if yes, then (b), or (a) with multivalued attributes)
    • 3. Else
    • (a), perhaps with composite attributes

Employee

phone

loc

no

CMSC424, Spring 2005

slide45

bcity

name

name

amt

ssn

ssn

bname

lno

E/R Data ModelDesign Issue #2: Entity Sets vs. Relationship Sets

  • An Example: How to model bank loans

Loans

Borrows

Branch

Customer

Customer

Loan

vs

(a)

amt

lno

(b)

  • To resolve, determine how loans are issued
    • 1. Can there be more than one customer per loan?
      • If yes, then (a). Otherwise, loan info must be replicated for each customer (wasteful, potential update anomalies)
    • 2. Is loan a noun or a verb?
      • Both, but more of a noun to a bank. (hence (a) probably more appropriate)

CMSC424, Spring 2005

slide46

E/R Data ModelDesign Issue #3: N-ary vs Binary Relationship Sets

  • An Example: Works_At

Ternary:

Works_at

Employee

Branch

Choose n-ary

when possible!

(Avoids redundancy,

update anomalies)

Dept

(Joe, Moody, Acct) Î Works_At

vs

Binary:

WAB

WAE

Branch

WA

Employee

WAD

(Joe, w3) Î WAE

(Moody, w3) Î WAB

(Acct, w3) Î WAD

Dept

CMSC424, Spring 2005

example design
Example Design
  • We will model a university database
    • Main entities:
      • Professor
      • Projects
      • Departments
      • Graduate students
      • etc…

CMSC424, Spring 2005

slide48

proj-number

SSN

dept-no

name

sponsor

name

professor

project

dept

office

start

area

  • SSN

homepage

budget

rank

name

grad

age

degree

CMSC424, Spring 2005

slide49

proj-number

SSN

sponsor

name

project

professor

start

area

budget

rank

  • SSN

dept-no

name

name

grad

dept

age

office

degree

homepage

CMSC424, Spring 2005

slide50

PI

Co-PI

dept-no

SSN

proj-number

Appt

Chair

RA

Supervises

sponsor

name

name

project

dept

professor

office

start

area

  • SSN

budget

rank

homepage

Time (%)

name

grad

Major

age

advisee

advisor

degree

Mentor

CMSC424, Spring 2005

slide51

PI

Co-PI

dept-no

SSN

proj-number

Appt

Chair

RA

Supervises

sponsor

name

name

project

dept

professor

office

start

area

  • SSN

homepage

budget

rank

name

grad

age

advisee

advisor

degree

Mentor

Time (%)

Major

CMSC424, Spring 2005

slide52

PI

Co-PI

SSN

dept-no

proj-number

Appt

Chair

RA

Supervises

name

sponsor

name

dept

project

professor

office

area

start

  • SSN

budget

rank

homepage

name

grad

age

advisee

advisor

degree

Mentor

Time (%)

Major

And so on…

CMSC424, Spring 2005

summary
Summary
  • Entity-relationship Model
    • Intuitive diagram-based representation of domain knowledge, data properties etc…
    • Two key concepts:
      • Entities
      • Relationships
    • We also looked at:
      • Relationship cardinalities
      • Keys
      • Participation Constraints

CMSC424, Spring 2005

summary1
Summary
  • Details unimportant
    • Key idea: We can represent many data properties and constraints conceptually using this
  • Read Chapter 2
    • Assignment will require you to do this anyway !

CMSC424, Spring 2005