Relational ModelRelational Model

CS 157ACS 157A

Prof. Sin-Min LeeProf. Sin-Min Lee

By Truc TruongBy Truc Truong

What is Relational Model?What is Relational Model?

Relational model is most widely used data model for commercial data-processing. The reason it’s used so much is, because it’s simple and easy to maintain.

The model is based on a collection of tables. Users of the database can create tables, insert new tables or modify existing tables. There are several languages for database programming.• SQL, Oracle, etc.

History of Relational ModelingHistory of Relational Modeling

Introduced by Ted Codd in 1970 Introduced by Ted Codd in 1970 Ted Codd was an Ted Codd was an IBMIBM Researcher Researcher Laid the foundation for database Laid the foundation for database

theory theory Many database concepts & products Many database concepts & products

based on his modelbased on his model

Relational Model BasicRelational Model Basic

The relational model gives us a single The relational model gives us a single way to represent data: as a two-way to represent data: as a two-dimensional table called a relation.dimensional table called a relation.

AttributesAttributes SchemasSchemas TuplesTuples DomainsDomains Equivalent Representations of a Equivalent Representations of a

RelationRelation

AttributesAttributes

Attribute

TitleTitle YearYear LengthLength

Star WarsStar Wars 19771977 124124

Might DucksMight Ducks 19911991 104104

Wayne’s WorldWayne’s World 19921992 9595

Attributes of a relation serve as names for the columns of the relation. Usually, an attribute describes the meaning of entries in the column below.

Table = relation.Column headers = attributes.

SchemasSchemas The name of a relation and the set of attributes for a The name of a relation and the set of attributes for a

relation is called a schema.relation is called a schema. We show the schema for the relation with the relation We show the schema for the relation with the relation

name followed by a parenthesized list of its attributes.name followed by a parenthesized list of its attributes. Relation schemaRelation schema = name(attributes) + other structure = name(attributes) + other structure

info., e.g., keys, other constraints. info., e.g., keys, other constraints. Order of attributes is arbitrary, but in practice we need Order of attributes is arbitrary, but in practice we need

to assume the (to assume the (standardstandard) order given in the relation ) order given in the relation schema.schema.

Relational database schemaRelational database schema = collection of relation = collection of relation schemas.schemas.

So the schema for previous slide isSo the schema for previous slide is

Movies (title, year, length)Movies (title, year, length)

Database SchemaDatabase Schema DataBase SchemaDataBase Schema

• Logical View of the DatabaseLogical View of the Database Database InstanceDatabase Instance

• A view of data in database at anytime.A view of data in database at anytime. Relation SchemaRelation Schema

• Corresponds to the programming language concept of Corresponds to the programming language concept of type definitiontype definition

• E.g. Java E.g. Java StringString movie = “Spiderman”; movie = “Spiderman”;

Relation InstanceRelation Instance• Corresponds to the programming-language concept of

the value of a variable• E.g. Java

String movie = “Spiderman”;

TuplesTuples

Tuple

TitleTitle YearYear LengthLength

Star WarsStar Wars 19771977 124124

Might Might DucksDucks

19911991 104104

Wayne’s Wayne’s WorldWorld

19921992 9595

The rows of a relation, other than the header row containingThe attribute names are called tuples. A tuple has one component for each attribute of the relation.

DomainsDomains

Each attribute of a relation is associated Each attribute of a relation is associated with a particular elementary type called with a particular elementary type called domain.domain.

The components of any tuple of the The components of any tuple of the relation must have, in each component, a relation must have, in each component, a value that belongs to the domain of the value that belongs to the domain of the corresponding column.corresponding column.

Example: Example: • with with titletitle string is associatedstring is associated• with with yearyear integer is associatedinteger is associated

Equivalent Representations of a Equivalent Representations of a RelationRelation

Schemas are sets of attributes (not lists).Schemas are sets of attributes (not lists). Tuples are sets of components (not lists).Tuples are sets of components (not lists). Instances are sets of tuples (not lists)Instances are sets of tuples (not lists) After permutation of rows and columns After permutation of rows and columns

the relations remains the same! (permute the relations remains the same! (permute values and attributes)values and attributes)

Equivalent Representations of a Equivalent Representations of a Relation cont.Relation cont.

Formal notion of a tuple= Formal notion of a tuple= a function {attributes} a function {attributes} {values} {values}

• title title Star Wars Star Wars• year year 1977 1977• length length 121 121• filmType filmType color color

(Star Wars, 1977, 121,color) and(Star Wars, 1977, 121,color) and(1977, 121,color, Star Wars) are the (1977, 121,color, Star Wars) are the same object.same object.

TitleTitle YearYear LengthLength

Star WarsStar Wars 19771977 124124

Mighty DucksMighty Ducks 19911991 104104

Wayne’s WorldWayne’s World 19921992 9595

YearYear LengthLength TitleTitle

19771977 124124 Star WarsStar Wars

19911991 104104 Mighty DucksMighty Ducks

19921992 9595 Wayne’s WorldWayne’s World

Original Relation

Modified Relation

A1 A2 A3 ... An

a1 a2 a3 an

b1 b2 a3 cn

a1 c2 b3 bn

.

.

.

x1 v2 d3 wn

Relational Data Model: Relational Data Model: summarysummary Set theoreticSet theoretic

Domain — set of valuesDomain — set of valueslike a data typelike a data type

Cartesian product (or Cartesian product (or product)product)

D1 D2 ... DnD1 D2 ... Dnn-tuples (V1,V2,...,Vn)n-tuples (V1,V2,...,Vn)s.t., V1 D1, V2 D2,...,Vn Dns.t., V1 D1, V2 D2,...,Vn Dn

•Relation=subset of cartesian Relation=subset of cartesian product of one or more domainsproduct of one or more domains

FINITE only; empty set allowedFINITE only; empty set allowed•Tuples = members of a relation Tuples = members of a relation inst.inst.

•Arity = number of domainsArity = number of domains•Components = values in a tupleComponents = values in a tuple•Domains — corresp. with Domains — corresp. with attributesattributes

•Cardinality = number of tuplesCardinality = number of tuples

Relation as tableRows = tuplesColumns = componentsNames of columns = attributes

Relation name + set of attribute names= schemaREL (A1,A2,...,An)

Arity

Cardinality

Attributes

Component

Tuple

Schema versus InstanceSchema versus Instance DB instances change continuously DB instances change continuously

(e.g., movies are added, deleted, changed,…)(e.g., movies are added, deleted, changed,…) The schema is stable The schema is stable

(attributes change almost never)(attributes change almost never) A RDB instance is the set of tuples that are ‘now’ A RDB instance is the set of tuples that are ‘now’

in the DBin the DB When designing the DB only the schema is When designing the DB only the schema is

important (=the structure of the data/DB)important (=the structure of the data/DB) We only imagine typical instances to help us with We only imagine typical instances to help us with

the designthe design Intentional Intentional level: schemalevel: schema ExtensionalExtensional level: instances level: instances

E/R Diagrams to Relational E/R Diagrams to Relational DesignsDesigns

Creation of a DB:Creation of a DB: Design phase (on “paper”, which information, relationships, Design phase (on “paper”, which information, relationships,

constraints, …)constraints, …) Implementation phase (real RDBMS)Implementation phase (real RDBMS) It is “easier” to start from ODL or E/R and later convert to RMIt is “easier” to start from ODL or E/R and later convert to RM

• RM has only one concept (relation)RM has only one concept (relation)• E/R and ODL have complementary concepts and are more E/R and ODL have complementary concepts and are more

flexible (constraints, …)flexible (constraints, …) Converting E/R design to a relational database schema:Converting E/R design to a relational database schema:

• Turn each entity set into a relation with the same set of Turn each entity set into a relation with the same set of attributesattributes

• Replace a relationship by a relation whose attributes are Replace a relationship by a relation whose attributes are the keys for the connected entity set.the keys for the connected entity set.

*Weak entity sets cannot be translated straightforwardly to *Weak entity sets cannot be translated straightforwardly to relationsrelations

*”Isa” relationships and subclasses require careful treatment.*”Isa” relationships and subclasses require careful treatment.

Relational Design cont.Relational Design cont. Design in ODL or E/R (schema+constraints)Design in ODL or E/R (schema+constraints)

implementation in a RDBMSimplementation in a RDBMS

Simplest approach (not always best): convert each ODL Simplest approach (not always best): convert each ODL class or E/R entity set to a relation and each relationship to class or E/R entity set to a relation and each relationship to a relation.a relation.

Class/Entity Set RelationRelationship Relation

From Entity Sets to RelationsFrom Entity Sets to Relations An entity set that is An entity set that is not weaknot weak, is translated , is translated

into a relation with the same name and into a relation with the same name and attributes.attributes.

E.g., E.g., Movie(title, year, length, filmType)Movie(title, year, length, filmType)

Star(name, address) or Star(name, address) or Star(name, street, city)Star(name, street, city)

title year lengthname addressname address

Studios StarMoviesOwns Stars-in

filmType

From E/R Relationships to From E/R Relationships to RelationsRelations

E/R relationships are also translated to relations:E/R relationships are also translated to relations:1.1. For each entity set involved in For each entity set involved in RR, take key , take key

attribute(s) as part of schemaattribute(s) as part of schema2.2. If the relationship has attributes, add them to the If the relationship has attributes, add them to the

schemaschema If an entity set appears more than once in a If an entity set appears more than once in a

relationship, rename its attributes to avoid doubles relationship, rename its attributes to avoid doubles and for clarity! and for clarity!

E.g., E.g., Owns(title, year, studioName)Owns(title, year, studioName)

title year lengthname addressname address

Studios StarMoviesOwns Stars-in

filmType

Relationships to Relation cont.Relationships to Relation cont.

If an entity set appears more than once in If an entity set appears more than once in a relationship, rename its attributes to a relationship, rename its attributes to avoid doubles!avoid doubles! namename

E.g., E.g., Contracts(title,year,starName,Contracts(title,year,starName,

studioOfStar,producingStudio)studioOfStar,producingStudio)

StarMoviesContracts

StudioProducing studio

Studio of star

Handling Weak Entity SetHandling Weak Entity Set

If there is a weak entity sets If there is a weak entity sets W W we do the followingwe do the followingdifferently:differently: Attributes of Attributes of W W plus key attributes of other plus key attributes of other

entity sets that contribute to the key of entity sets that contribute to the key of W W (double-diamond; many-one)(double-diamond; many-one)..

Any relationship in which Any relationship in which WW appears must use appears must use as a key for as a key for WW all of its attributes including all of its attributes including those of the other entity sets that contribute to those of the other entity sets that contribute to WW’s key’s key

Double-diamond relationships from Double-diamond relationships from W W to to another entity set do not need to be converted another entity set do not need to be converted (this information is already in the relation for (this information is already in the relation for WW).).

Studios(name, address)Studios(name, address)

Crews(number,studioName)Crews(number,studioName)

Unit-of(number,studioName,name)Unit-of(number,studioName,name) Unit-of(number,name) Unit-of(number,name)

are the same (many-one!)are the same (many-one!)

(Disney crew #3, Disney) (Disney crew #3, Disney) (3, Disney, Disney) (3, Disney, Disney)

name

Studios

number address

Unit-ofCrews

Contracts(starName, studioName, Contracts(starName, studioName, title, year, salary)title, year, salary)

Relations for Relations for Movie-ofMovie-of, ,

Star-ofStar-of and and

Studio-ofStudio-of are superfluous… are superfluous…

Movies

Studios

StarsContracts Star-of

salarytitle year

name

name

length

address

address

filmType

StarOfContractsMovieOf

StudioOf

Stars

Converting Subclass Structures to Converting Subclass Structures to RelationsRelations

Differences between E/R and ODL:Differences between E/R and ODL: In ODL: an object belongs to exactly In ODL: an object belongs to exactly

one class. It inherits properties from one class. It inherits properties from superclass.superclass.

In E/R: an object may belong to In E/R: an object may belong to several entity sets related by several entity sets related by isa.isa.

Structures to Relations cont.Structures to Relations cont. Every subclass has its own relationEvery subclass has its own relation It has all properties of this subclass It has all properties of this subclass

including inherited propertiesincluding inherited properties E.g.,E.g.,Movie(title,year,length,filmType,studioName,starName)Movie(title,year,length,filmType,studioName,starName)

Cartoon(title,year,length,filmType,StudioName,starName,Cartoon(title,year,length,filmType,StudioName,starName,voicevoice))

• MurderMystery(title,year,length,filmType,StudioName,starNMurderMystery(title,year,length,filmType,StudioName,starName,ame,weaponweapon))

• Cartoon-MurderMystery(title,year,length,filmType,Cartoon-MurderMystery(title,year,length,filmType,StudioName,starName,StudioName,starName,voice, weaponvoice, weapon))

An Object-Oriented ApproachAn Object-Oriented ApproachA hierarchy is populated by entities related by A hierarchy is populated by entities related by isaisa’s.’s. No relation is created for the No relation is created for the isa isa relationship.relationship. For each entity set, a separate relation with its For each entity set, a separate relation with its

own attributes plus key attributes of related own attributes plus key attributes of related attribute sets.attribute sets.

Bugs Bunny

title

length

Movies

Cartoons

isa

year

filmType

Voices Murder-Mysteries

isa

weapon

Differences between E/R and ODL:Differences between E/R and ODL: In ODL: all properties of an object In ODL: all properties of an object

together in one relation; we have to together in one relation; we have to search 4 relations to find a movie search 4 relations to find a movie object!object!

Movie(title,year,length,filmType,studioName,starNameMovie(title,year,length,filmType,studioName,starName))

Cartoon(title,year,length,filmType,StudioName,Cartoon(title,year,length,filmType,StudioName,starName,starName,voicevoice))

MurderMystery(title,year,length,filmType,StudioNamMurderMystery(title,year,length,filmType,StudioName,starName,e,starName,weaponweapon))

Cartoon-MurderMystery(title,year,length,filmType,Cartoon-MurderMystery(title,year,length,filmType,StudioName,starName,StudioName,starName,voice,weaponvoice,weapon))

Differences between E/R and ODL:Differences between E/R and ODL:

In E/R: a key of an entity is repeated In E/R: a key of an entity is repeated once for every entity set and once for every entity set and relationship it belongs to (relationship it belongs to (scattered scattered information).information).

Using Null Values to Combine Using Null Values to Combine RelationsRelations

ODL: Information of a hierarchy can be ODL: Information of a hierarchy can be given in given in one one relationrelation

NULL = NULL = “there is no appropriate “there is no appropriate value for this attribute” value for this attribute”

E.g., E.g., Movie(title,year,length,filmType,StudioName,Movie(title,year,length,filmType,StudioName,

starName,voice, weapon)starName,voice, weapon)

ReferencesReferences

Jeff Ullman and Jeff Ullman and Jennifer Jennifer WidomWidom,, A A First Course in Database systems. First Course in Database systems. 2nd edition, Prentice Hall.2nd edition, Prentice Hall.

Prof. Lee “Relation Model” Prof. Lee “Relation Model” PresentationPresentation

science.kennesaw.edu/~mguimara/science.kennesaw.edu/~mguimara/8080/ch3_4.ppt 8080/ch3_4.ppt

www.cs.niu.edu/~sheng/CSCI588/www.cs.niu.edu/~sheng/CSCI588/Lecture2.ppt Lecture2.ppt