Introduction to Database Systems

1
Introduction to Database Systems

• CS363/607
• Lecture 4

2
Data Model

• Description of data or information
• Structure of the data
• Operations of the data
• Constraints of the data

3
Relational Data Model

• E/R diagram is simple and appropriate to describe
  the structure of database, relational data model
  is used for todays database implementation. It
  contains one single data-modeling concept the
  relation.

4
Relation

• A two-dimensional table

Attributes
Tuples
5
Semistructured Model

• lt Movies gt
• lt Movie title " Gone With the Wind " gt
• ltYeargt1939lt/Yeargt
• ltLengthgt231 lt / Length gt
• ltGenregtdramalt/ Genre gt
• lt / Movie gt
• lt Movie title " Star Wars " gt
• ltYeargt1977lt/Yeargt
• ltLengthgt124 lt / Length gt
• ltGenregtsciFilt/ Genre gt
• lt / Movie gt
• lt Movie title " Wayne ' s title"Wayne's World
  " gt
• ltYeargt1992lt/Yeargt
• ltLengthgt95lt / Length gt
• ltGenregtcomedylt/ Genre gt
• lt / Movie gt lt / Movies gt

6
Object-Oriented Model

• Add OO features
• Values can have structure
• Relation can have associated methods
• Pure OO Model

7
Attributes

• The columns of a relation
• E.g. title, year

8
Schemas

• Relation schema relation name attributes, in
  order ( types of attributes).
• Example Movies(title, year, length, genre)
• Database collection of relations.
• Database schema set of all relation schemas in
  the database.

9
Tuples

• The rows of a relation is called tuples.
• A tuple has one component for each attribute of
  the relation.
• Example (Star Wars, 1977, 124, sciFi)

10
Domain

• Each component of each tuple must be atomic, must
  some elementary type such as integers or strings.
  
• It is illegal for types which can be broken into
  small components.
• Domain defines the range of an attribute.
• Domain is also part of database schema.
• Example Movies(title, year, length, filmType)
• should be Movies(titlestring, yearinteger,
  lengthinteger, genrestring)

11
Keys in relations

• A key is an attribute or a set of attributes
  which can uniquely identify a tuple
• Movie(title, year, length, genre)

12
A Example database schema

• Movie (title string, year integer, length
  integer, genre string, studioName string,
  producerC integer)
• StarsIn (movietitle string, movieyear integer,
  starname string)
• MovieStar (name string, address string, gender
  char, birthday date)
• MovieExec (name string, address string, CERT
  integer, netWorth integer)
• Studio (name string, address string, presC
  integer)

13
Sample Instances of bank database
14
Comments

• In relations
• The order of rows can be changed
• The order of columns can also be changed, but be
  aware to change the corresponding values for each
  tuple as well.
• Relations change over time
• The schemas of relations changes are less common,
  but still possible when we delete a useless
  attribute, etc.
• Instance a set of tuples for a given relation.
• Schema and Instance

15
Why relations?

• Very simple model.
• Often matches how we think about data.
• the relational model is a set-based model.
• Abstract model that underlies SQL, the most
  important database language today.

16
E/R diagrams to relations

• Entity sets become relations with the same set of
  attributes.
• Relationships become relations whose attributes
  are only
• The keys of the connected entity sets.
• Attributes of the relationship itself.

17
Special situations

• Weak entity sets cannot be translated
  straightforwardly to relations.
• Isa relationships and subclasses require
  careful treatment.
• Sometimes need to combine two relations.

18
Entity Set -gt Relation
name
manufacturer
Beers
Relation Beers(name, manufacturer)
19
Another example
year
title
name
Stars-in
Movies
Star
length
address
type
Owns
Studios
name
address
20

• Schemas
• movie(title,year,length,filmType)
• stars(name,address)
• studios(name,address)

21
Relationship -gt Relation

• Relationships in E/R model are also represented
  by relations. The relation for a given
  relationship R has the following attributes
• For each entity set in R, take its key attributes
• If the relationship has attributes, then they are
  the attributes of the corresponding relation.
• If one entity set appears several times, its key
  attributes will also appear several times.

22
Example

• Owns(title, year, studioname)
• Stars-in(title, year, starname)
• Attention use different attribute names to avoid
  duplications.

23
Contracts(starname, title, year, studioOfstar,
producingStudio)
24
Relationship -gt Relation
name
name
addr
manf
Drinkers
Beers
25
Combining relations

• Combine relations for an entity set E and a
  relationship R (from E to F).
• Requirements
• R is a many-to-one relationship
• Both relations for E and R contain the key
  attribute(s) of E
• Then we can combine E and R with a new schema
• All attributes of E
• The key attribute of F
• Any attributes belonging to relationship R
• How about an entity e in E is not related to any
  entity in F?
• Null value is introduced (it is not a formal
  part in relational model, but it is available in
  SQL).

26
Example
Likes
Drinkers
Beers
Favorite
Drinker(name, address) and likes(drinker,beer)
can not be combined.
However, drinker(name, address) and
favorite(driner,beer) can be combined with a new
relation drinker1(name,address,favoritebeer)
27
Another example
owns
Studios
Movies
Movie(title,year,length,filmType) and owns can be
combined into one relation Movie1(title,year,len
gth,filmType, studioname)
28
Benefits of combination

• More efficient to answer queries involving
  attributes of one relation than involving
  attributes from several relations.

29
Risk with Many-Many Relationships

• Combining Drinkers with Likes would be a mistake.
  It leads to redundancy, as

name addr beer Sally 123 Maple
Bud Sally 123 Maple Miller
30
(No Transcript)
31
Handling Weak Entity Sets

• Relation for a weak entity set W must include
  attributes for its complete key (including those
  belonging to other entity sets), as well as its
  own, non-key attributes.
• The relation for any relationships in which weak
  entity set W appears must use all of the key
  attributes as a key for this weak entity set.
• However, supporting relationships for W to
  another entity set that helps provide the key is
  redundant and yields no relation.

32
Example
name
name
Logins
Hosts
At
time
Hosts(hostName) Logins(loginName, hostName,
time) At(loginName, hostName, hostName2)
33
Another example
number
address
name
Stars-in
Unit-of
Crews
Studios

• Studios(name,address)
• Crews(number,studioname)
• Unit-of(number,studioname,name)

34
salary
Contract
Stars-in
Stars-in
Stars-in
Star-of
Studio-of
Movie-of
Movies
Star
Studios
length
type
year
name
address
name
address
title
Contracts(starname,studioname,title,year,salary)
35
year
title
name
salary
Movies
stars
length
address
Contracts
type
Studios
name
address
36
Rules

• If W is a weak entity set, construct for W a
  relation whose schema consists of
• All attributes of W
• All attributes of supporting relationship for W
• For each supporting relationship for W, (a
  many-to-one relationship from W to E), all the
  key attributes of E.
• Do not construct a relation for any supporting
  relationship for W.

37
Exercise 4.5.1
row
seat
Booking
Stars-in
Stars-in
Toflt
toCust
Flights
Customer
SSN
phone
number
day
aircraft
address
name
38
Solution

• Customers(ssNo, name, address, phone)
  Flights(number, day, aircraft)
• Bookings(ssNo, number, day, row, seat)
• Being a weak entity set, Bookings' relation has
  the keys for Customers and Flights and Bookings'
  own attributes.

39
Solution (Cont.)

• Notice that the relations obtained from the
  toCust and toFlt relationships are unnecessary.
  They are
• toCust(ssNo, ssNo1, number, day)
• toFlt(ssNo, number, day, number1, day1)
• That is, for toCust, the key of Customers is
  paired with the key for Bookings. Since both
  include ssNo, this attribute is repeated with two
  different names, ssNo and ssNo1. A similar
  situation exists for toFlt.

40
Thinking Exercise 4.5.2

• If a booking can be identified uniquely by the
  flight number, day of the flight, the row and
  seat the customer is not necessary to help
  identify the booking.

41
row
seat
Booking
Stars-in
ToCust
Toflt
Movies
Customer
SSN
phone
number
day
aircraft
address
name
42

• Customers(ssNo, name, address, phone)
• Flights(number, day, aircraft)
• Bookings(number, day, row, seat)
• toCust(ssNo, number, day, row, seat)

43
Exercise 4.5.3
name
yearLaunched
Ships
The sister
The Ship
Sister-of

• Ships(name, yearLaunched)
• SisterOf(name, sisterName)

44
Entity Sets With Subclasses

• Subclasses
• Root entity set
• This root entity set has a key that can identify
  all the entities in the hierarchy
• In the lower layer, an entity have their own
  attributes
• Strategies
• E/R style
• Object-oriented
• Use nulls

45
E/R-style conversion

• In this approach, we create a relation for each
  entity set. If an entity set E is not the root,
  the relation of E will include the key attributes
  of root, plus all the attributes of E.
• For a relationship related to E, we use these key
  attributes to identify entities of E in the
  relation corresponding to that relationship.
• Do not create a relation for isa.

46
Object-oriented approach

• In object-oriented approach, we enumerate all the
  possible subtrees of the hierarchy.
• For each one, create one relation that represents
  entities that have components in exactly those
  subtrees
• The schema for this subtree has all the
  attributes of any entity set in the subtree.

47
Using NULL value

• In this case, if we allow the usage of null, we
  can handle a hierarchy of entity sets with a
  single relation.
• It has all the attributes belonging to any entity
  set of the hierarchy.
• An entity is represented by a single tuple, it
  has NULL in each attributes that is not defined
  for that entity.

48
Example
Beers
name
manufacturer
isa
Ales
color
49
E/R Style
name manufacturer Bud Anheuser-Busch Summerbre
w Petes Beers name color Summerbrew
dark Ales
50
Object-Oriented
name manufacturer Bud Anheuser-Busch Beers na
me manufacturer color Summerbrew Petes
dark Ales
51
Using Nulls
name manufacturer color Bud
Anheuser-Busch NULL Summerbrew Petes
dark Beers
52
Another example
53
E/R Style

• Movie(title,year,length,filmType)
• MurderMysteries(title,year,weapon)
• Cartoon(title,year)
• Fourth both cartoons and murdermysteries
• In addition
• Voice(title,year,starName)

54
Object-oriented

• Movie(title,year,length,filmType)
• MovieC(title,year,length,filmType)
• MovieMM(title,year,length,filmType,weapon)
• MovieCMM(title,year,length,filmType,weapon)
• In addition
• Voice(title,year,starName)

55
Using Null

• Movie(title,year,length,filmType,weapon)
• In addition
• Voice(title,year,starName)

56
Comparison of approaches

• O-O approach good for queries like find the
  color of ales made by Petes.
• Just look in Ales relation.
• E/R approach good for queries like find all
  beers (including ales) made by Petes.
• Just look in Beers relation.
• Using nulls saves space unless there are lots of
  attributes that are usually null.

57
Comparison of approaches (Cont.)

• These approaches have their advantages and
  disadvantages
• It is better to answer queries involving less
  relations. The null approach is the best.
  However, the other two have advantages on
  different queries.
• Less relations are better. The null approach is
  good. The object-oriented is the worst.
• Minimize the space and avoid redundant
  information. Object-oriented has the best
  performance, E/R style is the worst.

58
Exercise 4.6.1
number
name
chair
room
GivenBy
Courses
Depts
Computer allocation
Lab Course
59
Solution

• (a), Depts(name, chair)
• Courses(number, deptName, room)
  LabCourses(number, deptName, allocation)
• (b), Depts(name, chair)
• Courses(number, deptName, room)
  LabCourses(number, deptName, room, allocation)
• (c), Depts(name, chair)
• Courses(number, deptName, room, allocation)