relational model l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Relational Model PowerPoint Presentation
Download Presentation
Relational Model

Loading in 2 Seconds...

play fullscreen
1 / 29

Relational Model - PowerPoint PPT Presentation


  • 225 Views
  • Uploaded on

Relational Model. CS 157A Prof. Sin-Min Lee By Truc Truong. 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.

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 'Relational Model' - Faraday


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
relational model

Relational Model

CS 157A

Prof. Sin-Min Lee

By 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 modeling
History of Relational Modeling
  • Introduced by Ted Codd in 1970
  • Ted Codd was an IBM Researcher
  • Laid the foundation for database theory
  • Many database concepts & products based on his model
relational model basic
Relational Model Basic

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

  • Attributes
  • Schemas
  • Tuples
  • Domains
  • Equivalent Representations of a Relation
attributes
Attributes

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.

Attribute

schemas
Schemas
  • The name of a relation and the set of attributes for a relation is called a schema.
  • We show the schema for the relation with the relation name followed by a parenthesized list of its attributes.
  • Relation schema = name(attributes) + other structure info., e.g., keys, other constraints.
  • Order of attributes is arbitrary, but in practice we need to assume the (standard) order given in the relation schema.
  • Relational database schema = collection of relation schemas.
  • So the schema for previous slide is

Movies (title, year, length)

database schema
Database Schema
  • DataBase Schema
    • Logical View of the Database
  • Database Instance
    • A view of data in database at anytime.
  • Relation Schema
    • Corresponds to the programming language concept of type definition
    • E.g. Java
      • String movie = “Spiderman”;
  • Relation Instance
    • Corresponds to the programming-language concept of the value of a variable
    • E.g. Java
      • String movie = “Spiderman”;
tuples
Tuples
  • The rows of a relation, other than the header row containing
  • The attribute names are called tuples.
  • A tuple has one component for each attribute of the relation.

Tuple

domains
Domains
  • Each attribute of a relation is associated with a particular elementary type called domain.
  • The components of any tuple of the relation must have, in each component, a value that belongs to the domain of the corresponding column.
  • Example:
    • with titlestring is associated
    • with yearinteger is associated
equivalent representations of a relation
Equivalent Representations of a Relation
  • Schemas are sets of attributes (not lists).
  • Tuples are sets of components (not lists).
  • Instances are sets of tuples (not lists)
  •  After permutation of rows and columns the relations remains the same! (permute values and attributes)
equivalent representations of a relation cont
Equivalent Representations of a Relation cont.
  • Formal notion of a tuple=

a function {attributes}  {values}

    • title  Star Wars
    • year  1977
    • length  121
    • filmType  color
  • (Star Wars, 1977, 121,color) and

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

slide12

Original Relation

Modified Relation

slide13

Relational Data Model: summary

  • Relation as table
    • Rows = tuples
    • Columns = components
    • Names of columns = attributes
    • Relation name + set of attribute names= schema
      • REL (A1,A2,...,An)
  • Set theoretic
  • Domain — set of values
      • like a data type
  • Cartesian product (or product)
      • D1 D2 ...  Dn
      • n-tuples (V1,V2,...,Vn)
      • s.t., V1 D1, V2 D2,...,Vn Dn
    • Relation=subset of cartesian product of one or more domains
      • FINITE only; empty set allowed
    • Tuples = members of a relation inst.
    • Arity = number of domains
    • Components = values in a tuple
    • Domains — corresp. with attributes
    • Cardinality = number of tuples

A1 A2 A3 ... An

a1 a2 a3 an

b1 b2 a3 cn

a1 c2 b3 bn

.

.

.

x1 v2 d3 wn

Attributes

C

a

r

d

i

n

a

l

i

t

y

Tuple

Component

Arity

schema versus instance
Schema versus Instance
  • DB instances change continuously

(e.g., movies are added, deleted, changed,…)

  • The schema is stable

(attributes change almost never)

  • A RDB instance is the set of tuples that are ‘now’ in the DB
  • When designing the DB only the schema is important (=the structure of the data/DB)
  • We only imagine typical instances to help us with the design
  • Intentional level: schema
  • Extensional level: instances
e r diagrams to relational designs
E/R Diagrams to Relational Designs

Creation of a DB:

  • Design phase (on “paper”, which information, relationships, constraints, …)
  • Implementation phase (real RDBMS)
  • It is “easier” to start from ODL or E/R and later convert to RM
    • RM has only one concept (relation)
    • E/R and ODL have complementary concepts and are more flexible (constraints, …)
  • Converting E/R design to a relational database schema:
    • Turn each entity set into a relation with the same set of attributes
    • Replace a relationship by a relation whose attributes are the keys for the connected entity set.

*Weak entity sets cannot be translated straightforwardly to relations

*”Isa” relationships and subclasses require careful treatment.

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

implementation in a RDBMS

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

Class/Entity Set  Relation

Relationship  Relation

from entity sets to relations
From Entity Sets to Relations
  • An entity set that is not weak, is translated into a relation with the same name and attributes.
  • E.g.,

Movie(title, year, length, filmType)

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

Stars-in

Owns

Movies

Star

Studios

name

title

length

address

year

address

name

filmType

from e r relationships to relations
From E/R Relationships to Relations

E/R relationships are also translated to relations:

  • For each entity set involved in R, take key attribute(s) as part of schema
  • If the relationship has attributes, add them to the schema
  • If an entity set appears more than once in a relationship, rename its attributes to avoid doubles and for clarity!
  • E.g., Owns(title, year, studioName)

Stars-in

Owns

Studios

Movies

Star

name

title

length

address

year

address

name

filmType

relationships to relation cont
Relationships to Relation cont.
  • If an entity set appears more than once in a relationship, rename its attributes to avoid doubles!name
  • E.g., Contracts(title,year,starName,

studioOfStar,producingStudio)

Studio

Producing

studio

Studio of star

Contracts

Star

Movies

handling weak entity set
Handling Weak Entity Set

If there is a weak entity sets W we do the following

differently:

  • Attributes of W plus key attributes of other entity sets that contribute to the key of W (double-diamond; many-one).
  • Any relationship in which W appears must use as a key for W all of its attributes including those of the other entity sets that contribute to W’s key
  • Double-diamond relationships from W to another entity set do not need to be converted

(this information is already in the relation for W).

slide21

name

number

address

Studios

Crews

Unit-of

Studios(name, address)

Crews(number,studioName)

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

are the same (many-one!)

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

slide22

address

name

Studios

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

Relations for Movie-of,

Star-of and

Studio-of are superfluous…

Studio

Of

Stars

Star-of

Movies

Contracts

Stars

MovieOf

Contracts

StarOf

length

name

salary

title

year

address

filmType

converting subclass structures to relations
Converting Subclass Structures to Relations

Differences between E/R and ODL:

  • In ODL: an object belongs to exactly one class. It inherits properties from superclass.
  • In E/R: an object may belong to several entity sets related by isa.
structures to relations cont
Structures to Relations cont.
  • Every subclass has its own relation
  • It has all properties of this subclass including inherited properties
  • E.g.,

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

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

voice)

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

StudioName,starName,voice, weapon)

an object oriented approach
An Object-Oriented Approach

A hierarchy is populated by entities related by isa’s.

  • No relation is created for the isa relationship.
  • For each entity set, a separate relation with its own attributes plus key attributes of related attribute sets.

Movies

year

isa

title

Bugs Bunny

isa

length

filmType

Voices

Cartoons

Murder-

Mysteries

weapon

slide26
Differences between E/R and ODL:
  • In ODL: all properties of an object together in one relation; we have to search 4 relations to find a movie object!

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

  • Cartoon(title,year,length,filmType,StudioName,

starName,voice)

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

StudioName,starName,voice,weapon)

differences between e r and odl
Differences between E/R and ODL:
  • In E/R: a key of an entity is repeated once for every entity set and relationship it belongs to (scattered information).
using null values to combine relations
Using Null Values to Combine Relations
  • ODL: Information of a hierarchy can be given in one relation
  • NULL = “there is no appropriate value for this attribute”
  • E.g.,

Movie(title,year,length,filmType,StudioName,

starName,voice, weapon)

references
References
  • Jeff Ullman and Jennifer Widom, A First Course in Database systems. 2nd edition, Prentice Hall.
  • Prof. Lee “Relation Model” Presentation
  • science.kennesaw.edu/~mguimara/8080/ch3_4.ppt
  • www.cs.niu.edu/~sheng/CSCI588/Lecture2.ppt