Introduction to Database Systems

1
Introduction to Database Systems

• CS363/607
• Lecture 5

2
Unified Modeling Language (UML)

• Graphical notation for describing software
  designs
• Extended UML can be used for database design,
  like E/R model.

3
Comparison of UML and E/R
4
UML class and keys
5
Association

• A binary relationship between classes.
• No support for multiway relationships.
• Representation
• Draw a line between two classes
• Constraints on the number of objects from one
  side to another side m..n
• stands for infinity
• If no label at all, it is 1..1, exactly one

6
Example
7
Another example
8
Self-Association
9
Association Class
10
Subclasses

• Four kinds, depending on
• Complete or partial
• Disjoint or overlapping

11
Example
12
Aggregation and Compositions

• Special notations for many-one associations
• Aggregation open diamond, at this end the label
  must be 0..1
• Composition solid black diamond, at this end the
  label must be 1..1

13
Example
14
Exercise 4.7.1 and 4.7.2
15
UML to Relations

• Classes to relations
• Create a relation for each class
• Associations to relations
• Create a relation for each associations, and
• solve name duplication problem
• Add the attributes of the association class, if
  necessary

16
Example

• movie(title,year,length,genre)
• stars(name,address)
• studios(name,address)
• Owns(title, year, studioname)
• Stars-in(title, year, starname)

17
Another example

• Stars-in(title, year, starname, salary, residual)

18
UML subclasses

• Using three options in E/R conversion
• Or if we have information about disjoint or
  overlapping, complete or partial
• If a hierarchy is disjoint at every level, OO
  representation is suggested
• If a hierarchy is both complete and disjoint,
  using OOP as well, but simpler.
• If a hierarchy is large and overlapping at some
  or all levels, E/R approach is indicated.

19
Aggregation and composition to relations

• Construct no relation, add to the relation for
  the class at the nondiamond end the key
  attributes of the class at the diamond end.

20
Example

• Movies(title,year,length,genre,studioName)
• MovieExec(cert,name,address,netWorth)
• Presidents(cert, studioName)
• Studios(name,address)

21
Weak entity sets

• Supporting class
• A small box attached to the weak class with PK

22
Example

• Studio(name, address)
• Crews(number, crewChief, studioName)

23
Exercise 4.8.1
24
Object-Oriented DBMSs

• Standards group ODMG Object Data Management
  Group.
• ODL Object Description Language, like CREATE
  TABLE part of SQL.
• OQL Object Query Language, tries to imitate SQL
  in an OO framework.

25
Framework --- (1)

• ODMG imagines OO-DBMS vendors implementing an OO
  language like C with extensions (OQL) that
  allow the programmer to transfer data between the
  database and host language seamlessly.

26
Framework --- (2)

• ODL is used to define persistent classes, those
  whose objects may be stored permanently in the
  database.
• ODL classes look like Entity sets with binary
  relationships, plus methods.
• ODL class definitions are part of the extended,
  OO host language.

27
Object Definition Language (ODL)

• Specifying the database structures in
  object-oriented terms
• Class is the central concept
• Relationships are not independent

28
ODL Overview

• A class declaration includes
• A name for the class.
• Optional key declaration(s).
• A list of properties. A property is either an
  attribute, a relationship, or a method.

29
Class Definitions

• class ltnamegt (key names)
• ltlist of propertiesgt

30
Attribute and Relationship Declarations

• Attributes are (usually) the simplest kind of
  property (structures are allowed).
• attribute lttypegt ltnamegt
• Relationships connect an object to one or more
  other objects of one class.
• relationship lttypegt ltnamegt
• inverse ltrelationshipgt

31
Inverse Relationships

• Suppose class C has a relationship R to class
  D.
• Then class D must have some relationship S to
  class C.
• R and S must be true inverses.
• If object d is related to object c by R, then c
  must be related to d by S.

32
Class example

• class Movie
• attribute string title
• attribute integer year
• attribute integer length
• attribute enum Genres
• drama , comedy , sciFi , teen genre

33
Another class example

• class Star
• attribute string name
• attribute Struct Addr
• string street , string city address

34
Relationship example

• Declared in Movie class
• relationship SetltStargt stars
• Declared in Star class
• relationship SetltMoviegt starredIn
• One thing missing
• Connections between these two relationship
  declarations.
• Using inverse followed by

35
Example

• class Bar
• attribute string name
• attribute string addr
• relationship SetltBeergt serves
• inverse BeerservedAt
• class Beer
• attribute string name
• attribute string manf
• relationship SetltBargt servedAt
• inverse Barserves

36
Another Example

• class Movie
• attribute string title
• attribute integer year
• attribute integer length
• attribute enum Genres drama, comedy, sciFi,
  teen genre
• relationship SetltStargt stars
• inverse StarstarredIn
• relationship Studio ownedBy
• inverse Studioowns
• class Star
• attribute string name
• attribute Struct Addr
• string street, string city address
• relationship SetltMoviegt starredIn
• inverse Moviestars

37
Another Example, Cont.

• class Studio
• attribute string name
• attribute string address
• relationship SetltMoviegt owns
• inverse MovieownedBy

38
Types of Relationships

• The type of a relationship is either
• A class, like Bar. If so, an object with this
  relationship can be connected to only one Bar
  object.
• SetltBargt the object is connected to a set of Bar
  objects.
• BagltBargt, ListltBargt, ArrayltBargt the object is
  connected to a bag, list, or array of Bar objects.

39
Multiplicity of Relationships

• All ODL relationships are binary.
• Many-many relationships have Setltgt for the type
  of the relationship and its inverse.
• Many-one relationships have Setltgt in the
  relationship of the one and just the class for
  the relationship of the many.
• One-one relationships have classes as the type in
  both directions.

40
Example Multiplicity

• class Drinker
• relationship SetltBeergt likes inverse Beerfans
• relationship Beer favBeer inverse
  Beersuperfans
• class Beer
• relationship SetltDrinkergt fans inverse
  Drinkerlikes
• relationship SetltDrinkergt superfans inverse
  DrinkerfavBeer

41
Another Multiplicity Example

• class Drinker
• attribute
• relationship Drinker husband inverse wife
• relationship Drinker wife inverse husband
• relationship SetltDrinkergt buddies
• inverse buddies

42
Coping With Multi-way Relationships

• ODL does not support 3-way or higher
  relationships.
• We may simulate multi-way relationships by a
  connecting class, whose objects represent
  tuples of objects we would like to connect by the
  multi-way relationship.

43
Connecting Classes

• Suppose we want to connect classes X, Y, and Z by
  a relationship R.
• Devise a class C, whose objects represent a
  triple of objects (x, y, z) from classes X, Y,
  and Z, respectively.
• We need three many-one relationships from (x, y,
  z) to each of x, y, and z.

44
Example Connecting Class

• Suppose we have Bar and Beer classes, and we want
  to represent the price at which each Bar sells
  each beer.
• A many-many relationship between Bar and Beer
  cannot have a price attribute as it did in the
  E/R model.
• One solution create class Price and a connecting
  class BBP to represent a related bar, beer, and
  price.

45
Example --- Continued

• Since Price objects are just numbers, a better
  solution is to
• Give BBP objects an attribute price.
• Use two many-one relationships between a BBP
  object and the Bar and Beer objects it represents.

46
Example, Concluded

• Here is the definition of BBP
• class BBP
• attribute pricereal
• relationship Bar theBar inverse BartoBBP
• relationship Beer theBeer inverse BeertoBBP
• Bar and Beer must be modified to include
  relationships, both called toBBP, and both of
  type SetltBBPgt.

47
Another example

• Class Contract
• attribute integer salary
• relationship Movie theMovie
• inverse
• relationship Star theStar
• inverse
• relationship Studio theStudio
• inverse

inverse MoviecontractsFor
48
Structs and Enums

• Attributes can have a structure (as in C) or be
  an enumeration.
• Declare with
• attribute Struct or Enum ltname of
• struct or enumgt ltdetailsgt
• ltname of attributegt
• Details are field names and types for a Struct, a
  list of constants for an Enum.

49
Example Struct and Enum

• class Bar
• attribute string name
• attribute Struct Addr
• string street, string city, int zip address
• attribute Enum Lic
• FULL, BEER, NONE license
• relationship

50
Method Declarations

• A class definition may include declarations of
  methods for the class.
• Information consists of
• Return type, if any.
• Method name.
• Argument modes and types (no names).
• Modes are in, out, and inout.
• Any exceptions the method may raise.

51
Example Methods

• real gpa(in string)raises(noGrades)
• The method gpa returns a real number (presumably
  a students GPA).
• gpa takes one argument, a string (presumably the
  name of the student) and does not modify its
  argument.
• gpa may raise the exception noGrades.

52
Another Example

• class Movie
• attribute string title
• attribute integer year
• attribute integer length
• attribute enum Genres drama, comedy, sciFi,
  teen genre
• relationship SetltStargt stars
• inverse StarstarredIn
• relationship Studio ownedBy
• inverse Studioowns
• float lengthInHours() raises(noLengthFound)
• void starNames(out SetltStringgt)
• void otherMovies(in Star, out SetltMoviegt)
• raises(noSuchStar)

53
The ODL Type System

• primitive types int, real/float, string,
  enumerated types, boolean, etc.
• Classes.
• Type constructors
• Struct for structures.
• Collection types Set, Bag, List, Array, and
  Dictionary ( mapping from a domain type to a
  range type).
• Relationship types can only be a class or a
  single collection type applied to a class.
• Attributes types are built starting from
  primitive types, and then apply the structure or
  collection types.

54
Example

• Legal for attributes
• 1. integer
• 2. Struct N string field1, integer field2
• 3. Listltrealgt
• 4. ArrayltStruct N string field1, integer
  field2, 10gt
• Illegal for relationships
• 1. Struct N Movie field1, Star field2
• 2. Setltintegergt
• 3. SetltArrayltStar, 10gtgt

55
ODL Subclasses

• Usual object-oriented subclasses.
• Indicate superclass with its name.
• Subclass lists only the properties unique to it.
• Also inherits its superclass properties.

56
Example

• class Cartoon extends Movie
• relationship SetltStargt voices
• class MurderMystery extends Movie
• attribute string weapon

57
Another Example

• Ales are a subclass of beers
• class Ale extends Beer
• attribute string color

58
Multiple Inheritance in ODL

• class CartoonMurderMystery
• extends MurderMystery Cartoon

59
ODL Keys

• You can declare any number of keys for a class,
  but it is optional.
• After the class name, add
• (key ltlist of keysgt)
• A key consisting of more than one attribute needs
  additional parentheses around those attributes.

60
Example Keys

• class Beer (key name)
• name is the key for beers.
• class Course (key (dept,number),(room, hours))
• dept and number form one key so do room and
  hours.
• class Movie (key (title, year)

61
More Example

• class Crew
• (key (number, unitOf))
• attribute integer number
• attribute string crewChief
• relationship Studio unitOf
• inverse StudiocrewOf

62
Exercise 4.9.1
63
From ODL to Relational

• ODL was intended to be used for ODBMS
• It can also be used for relations, the process is
  similar to E/R model conversion
• Some new problems
• ODL classes have no guarantee of keys, we might
  invent new attribute as a key
• ODL attributes are not necessarily atomic
• Not easy to convert methods directly into
  relational schemas

64
ODL attributes to Relational attributes

• Simplest case
• All properties of the class are attributes
• The types of attributes are atomic
• class Movie
• attribute string title
• attribute integer year
• attribute integer length
• attribute enum Genres drama, comedy, sciFi,
  teen genre
• Movie(title, year, length, genre)

65
Another example

• class MovieExec
• attribute string name
• attribute string address
• attribute integer netWorth
• MovieExec(cert, name, address, netWorth)

66
Nonatomic ODL attributes

• The types of attributes are not atomic
• Expand the structure definition
• In case of conflict, invent new names
• class Star (key name)
• attribute string name
• attribute struct Addr
• string street, string city address
• Stars (name, street, city)

67
Set-valued Attributes

• Make one tuple for each value
• class Star (key name)
• attribute string name
• attribute Setltstruct Addr
• string street, string citygt address
• Stars (name, street, city)

68
Set-valued Attributes

• BCNF violations?
• class Star (extent Stars)
• attribute string name
• attribute Setltstruct Addr
• string street, string citygt address
• attribute Date birthdate
• Stars (name, street, city, birthdate)
• name is not a key, name -gt birthdate is a
  violation!

69
Solution

• Using decomposition technique
• Separate out each set-valued attribute as if it
  was a many-many relationship between the objects
  of the class and the values that appear in the
  sets.

70
Representing other types

• bags

71
Representing other types, Cont.

• List

72
Representing other types, Cont.

• Fixed length Array

73
Representing other types, Cont.

• Dictionary

74
Representing ODL relationships

• class Movie
• attribute string title
• attribute integer year
• attribute integer length
• attribute enum Genres drama, comedy, sciFi,
  teen genre
• relationship SetltStargt stars
• inverse StarstarredIn
• relationship Studio ownedBy
• inverse Studioowns
• class Star
• attribute string name
• attribute Struct Addr
• string street, string city address
• relationship SetltMoviegt starredIn
• inverse Moviestars
• class Studio
• attribute string name

75
Representing ODL relationships, Cont.

• For pairs of owns and ownedBy
• StudioOf(title, year, studioName)

76
Representing ODL relationships, Cont.

• Another approach modifying Movie

77
Representing ODL relationships, Cont.

• Using one relation for many-many relationship may
  cause trouble

78
Create new key

• Star(cert, name, street, city, birthdate)
• StarsIn(title, year, cert)