C20.0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 4

• Matthew P. Johnson
• Stern School of Business, NYU
• Spring, 2004

2
Agenda

• Last time finished E/R models per se
• Announcement may have occasional pop quizzes
• at start of class
• On reading
• Counting toward participation/attendance grade
• This time
• Intro to relational model
• Converting E/Rs to relations
• Functional dependencies
• Keys and superkeys in terms of FDs
• Finding keys for relations

3
Review E/R example

• Exercise 2.4.1
• Students enroll in courses and get grades
• Enrollments as a connecting entity set
• Represent students taking the course
• Grade of a student for a course
• Draw E/R diagram
• Indicate weak entity sets their keys
• Is the grade part of the key for enrollments?

4
Next topic the Relational Data Model (3.1)
Database Model (E/R, other)
Relational Schema
Physical storage
Complex file organization and index structures.
Diagrams (E/R)
Tables column names attributes rows tuples
5
Relations as tables
Attribute names
Product table/relation
tuples/rows/records/entities
6
Relational terminology

• Relation is composed of tuples
• Tuples composed of attribute values
• Attribute has atomic types
• Relation schema
• relation name attribute names attribute types
• Relation instance set of tuples
• order doesnt matter
• Database schema set of relation schemas
• Database instance relation instance for every
  relation in the schema

7
Relations as sets

• Remember math relation is a subset of the
  cross-product of the attribute value sets
• R subset-of S x T
• Product subset-of Name x Price x Cat x Mft
• One member of Product
• (gizmo, 19.99, gadgets, GizmoWorks) in Product
• DB Relation instance math relation
• Q If relations are sets, why call instances?
• A R is a member of the powerset P(SxT)
• powerset set of all subsets

8
More on tuples

• Formally, can also be a mapping
• from attribute names to (correctly typed)
  values
• name ? gizmo
• price ? 19.99
• category ? gadgets
• manufacturer ? GizmoWorks
• NB ordered tuple is equiv to mapping
• Sometimes we refer to a tuple by itself (note
  order of attributes)
• (gizmo, 19.99, gadgets, GizmoWorks) or
• Product(gizmo, 19.99, gadgets, GizmoWorks).

9
Updates

• The database maintains a current database state
• Modifications of data
• add a tuple
• delete a tuple
• update an attribute value in a tuple
• DB Relation instance math relation
• Idea we saw partic. Product DB instance
• add, delete rows ? different DB rel. instances
• technically, different math relations
• to DBMS, still the same relation
• Modifications to the data are frequent
• Updates to the schema are rare, painful (Why?)

10
E/R models to relations (3.2)

• Recall justification
• design is easier in E/R
• implementation is easier/faster in R
• Parallel to program compilation
• design is easier in C/Java/whatever
• implemen. is easier/faster in machine/byte code
• Strategy
• apply semi-mechanical conversion rules
• improve by combining some relations
• improve by normalization
• involves finding functional dependencies

11
E/R conversion rules

• Relationship ? relation
• attributes keys of entity-sets/roles
• key depends on multiplicity
• Entity set ? relation
• attributes attributes of entity set
• key key of ES
• NB mapping of types is not one-one
• Well see mapping one tokens is not one-one
• Special treatment
• Weak entity sets
• Isa relations subclasses

12
Entity Sets

• Entity set Students

ssn
name
Students
address
13
Entity Sets
CourseID
Course
CourseName
14
Binary many-to-many relationships

• Key keys of both entities
• Why we learned to recognize keys

15
Many-to-one relationships
CopyrightNo
Name
Title
Address
StudioID
MovieID
Year
Movies
Studios
owns

• Key keys of many entitiy

16
Improving on many-one

• Note rules applied
• Movies Rel. all atts from Movies ES
• Studios Rel all atts from Studios ES
• Owns Rel att key atts from Movies Studios ESs
• But OwnsMovies?Studios is many-one
• for each row in Movies, theres a(/no) row in
  Owns
• ? just add the Owns data to Movies

17
Many-to-one a better design

• Q What if a movies Owns row were missing?

18
Many-to-many relationships again

• Wont work for many-many relationships

Movies
Stars
acts
Movies
Acts
Stars
19
Many-to-many relationships again
And heres why
20
Multiway relationships roles

• Different roles treated as different entity sets
• Key keys of the many entities

21
Multiway relationships roles
Students
TAs
Courses

• Enrolls(S_SSN, Course_ID, Tutor_SSN, Grader_SSN)

22
Converting weak ESs differences
StudioName
address
Crew_ID
Crew
Unit-of
Studio

• Atts of Crew Rel are
• attributes of Crew
• key attributes of supporting ESs

• Supporting relships may be omitted (why?)

23
Weak entity sets - relationships
StudioName
address
Crew_ID
Unit-of
Crew
Studio
Subscribes
IName
Address
Insurance
24
Weak entity sets - relationships

• Non-support relationships for weak ESs are
  converted
• keys include entire weak ES key

25
Conversion example

• Video store rental example, plus some atts
• Q Conversion to relations?

date
VideoStore
Rental
MID
Movie
address
year
Customer
MName
Cname
26
Conversion example, continued
MID

• Resulting binary-relationship version
• Q Conversion to relations?

MName
year
MovieOf
date
Movie
Rental
StoreOf
address
BuyerOf
Store
Cname
Customer
27
Converting inheritance hierarchies (3.3)

• No best way
• Several non-ideal methods
• E/R-style each ES ? relation
• OO-style each possible object ? relation
• nulls-style each rooted hierarchy ? relation
• non-applicable fields filled in with nulls
• Pros cons
• for each method, exist situations favoring it

28
Converting inheritance hierarchies
title
year
length
Movies
stars
isa
isa
Weapon
Voices
Lion King
Murder-Mysteries
Cartoons
Component
29
Inheritance E/R-style conversion

• Each ES ? relation

30
Subclasses object-oriented approach

• Every possible subtree (whats this?)
• Movies
• Movies Cartoons
• Movies Murder-Mysteries
• Movies Cartoons Murder-Mysteries

1.
3.
2.
4.
31
Subclasses nulls approach

• One relation for entire hierarchy
• Any non-applicable fields are NULL
• Q How do we know if a movie is a MM?
• Q How do we know if a movie is a cartoon?

32
Subclasses methods considerations

• Query time number of tables accessed in query
• nulls best, since each entity ? single row
• multi-node questions Find 1999 films with
  length gt 150 mins
• E/R just Movies, so fast
• OO Movies AND cartoons, so slow
• single-node questions Find weapons in gt150-min.
  cartoons
• E/R Movies, Cartoons AND MMs, so slow
• OO just MoviesCMM, so fast
• Number of relations per entity set
• nulls just one, so very few
• E/R one per ES, so medium number
• OO exponentional in ESs, so very many (how
  many?)
• Number/size of rows per entity
• nulls one long row per entity
• non-applicables become null
• OO one (all-relevant) row per entity, so
  smallest
• E/R several (tho all relevant) rows per entity
• Better of E/R and OO depends (on what?)