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

Loading in 2 Seconds...

play fullscreen
1 / 24

The Relational Model - PowerPoint PPT Presentation


  • 117 Views
  • Uploaded on

The Relational Model. By Elena Ciriani CS157A February 19, 2004 Professor Lee. INTRODUCTION. The relational model is the most used data model for commercial data-processing because it is simple to use and 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 'The Relational Model' - teal


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
the relational model
The Relational Model

By

Elena Ciriani

CS157A

February 19, 2004

Professor Lee

introduction
INTRODUCTION

The relational model is the most used data model for commercial data-processing because it is simple to use and to maintain.

A relational data model is based on a collection of tables. The user of the database system may query these tables, insert new tuples, and update (modify) tuples. There are several languages for expressing these operations.

topics
TOPICS
  • Structure of Relational database
    • A row in a table represents a relationship among a set of values where the columns are the representation of the attributions
  • The Relational Algebra
    • It defines a set of algebraic operations that operate on tables, and output tables as their results. These operations can be combined to get expressions that express desired queries.
structure of relational database section 3 1
Structure of Relational Database (Section 3.1)
  • 3.1.1 Basic Structure
  • 3.1.2 Database Schema
  • 3.1.3 Keys
  • 3.1.4 Schema Diagram
  • 3.1.5 Query Languages
basic structure
Basic Structure

The account table below represents a relation in the relational model. The three columns titles are the attributes and their domains.

Each row is called a tuple.

An account is a subset of the set of all possible tuples.

database schema
Database Schema
  • Database Schema is the logical design of the database
  • Database instance is a snapshot of the data in the DB at a given instance in time
  • Relation instance is the programming language notion of a value of a variable
database schema7
Database Schema

Relation schema consists of a list of attributes and their corresponding domain.

As a convention, uppercase letter are used so Account-schema=(account-number, branch-name, balance) This means that account is a relation on Account-schema by account(Account-schema)

database schema8
Database Schema

Relation instance is the set of values of a relation at a specific moment in time. This values may change in time causing a change in the relation as it is updated.

slide9
Keys
  • Superkey is a set of one or more attributes that allow us to identify uniquely an entity in the entity set.
  • Candidate Key are minimal superkey in an entity, one of those keys is selected to be the primary key
  • Primary Key is a candidate key that is chosen to identify entities within an entity set
  • Foreign Key is a primary key of another relation schema
slide10
Keys

If K of R is a superkey for R, then the relation r(R) does not have two tuples with the same value. So if t1 and t2 are in r t1 = t2

how to determine keys
How to determine keys
  • Strong entity set: the entity primary key becomes the relation primary key
  • Weak entity set: the primary key of the relation is the union of the strong entity set primary key and the discriminator
  • Relation set: the union of the primary keys of the related entity sets becomes a superkey of the relation
how to determine keys12
How to determine keys
  • Combined tables: in a many-to-one, the primary key of the many becomes the relation primary key. In a one-to-one either primary key can be used
  • Multivalued attributes: the entity primary key becomes the primary key
slide13

Schema Diagram

A database schema with primary and foreign key dependencies

primary

relation

shade indicates primary key

account

depositor

customer

dependency

borrower

branch

loan

query languages
Query Languages

Users use query languages to request information from the database SQL is the most spread.

Database uses two types of query languages:

Procedural language: the user instructs the system to perform a sequence of operations on the database

Nonprocedural language: the user describes the desired information without giving a specific procedure for obtain the information

the relational algebra topics section 3 2
The Relational AlgebraTopics(section 3.2)
  • 3.2.1 Fundamental Operations
    • 3.2.1.1 The Select Operation
    • 3.2.1.2 The Project Operation
    • 3.2.1.3 Composition of Relational Operations
    • 3.2.1.4 The Union Operation
    • 3.2.1.5 The Set Difference Operation
    • 3.2.1.6 The Cartesian-Product Operation
    • 3.2.1.7 The Rename Operation
relational algebra
Relational Algebra

The relational algebra is a pure procedural query language. It consists of a set of operations that take one or two relations as input in an expression and produced a new relation as their result.

A constant relation is written inside {}

A general expression is construct in subexpressions

If they works on one relation are called unary operation otherwise are said to be binary

unary operations
Unary Operations
  • Select operation: choose the tuples that satisfy a given predicament.
    • σ branch-name = “Perryridge”(loan)
  • Project operation: allows the user to select particular attributes of a relationship
    • Πloan-number, amount (loan)
  • Rename operation: give a name to the results of relational algebra expressions
    • ρbig-loans(σamount > 1200 (loan))
binary operation
Binary Operation
  • Union operation: allows the user to unify two different relations and display the result
    • Πcustomer-name(borrower) UΠcustomer-name(depositor)
  • Difference operation: finds the tuples that are in one relation but not in another
    • Πcustomer-name (borrower) - Πcustomer-name (depositor)
binary operation19
Binary Operation
  • Cartesian-product: combines information from any two relations
    • σ branch-name = “Perryridge”(borrower x loan)
  • Composition of operation: means that to find information we can associate more operation into an expression
    • Πcustomer-name (σ customer-city = “Harrison”(customer))
the relational algebra continued section 3 2 3
The Relational Algebra(continued)(Section 3.2.3)
  • 3.2.3 Additional Operations

The following operations make a relational algebra query easier when the basic expression may become lengthy

    • 3.2.3.1 The Set-Intersection Operation
    • 3.2.3.2 The Natural-Join Operation
    • 3.2.3.3 The Division Operation
    • 3.2.3.4 The Assignment Operation
additional operation
Additional Operation
  • Set-Intersection Operation: find all the attributes that appear in both relations
    • Πcustomer-name(borrower) ∩ Πcustomer-name(depositor)
    • Πcustomer-name(borrower) – (Πcustomer-name(borrower) –

Πcustomer-name(depositor))

additional operation22
Additional Operation
  • Natural-Join Operation: forms a Cartesian product of its two arguments, performs a selection forcing equality on those attributes that appears in both relations and removes any duplicates
    • Πcustomer-name, loan-number, amount(borrower loan)
    • Πcustomer-name, loan-number, amount

(borrower.loan-number = loan.loan-number(borrower x loan))

additional operation23
Additional Operation
  • Division operation: is suited to queries that include the phrase “for all”
    • Πcustomer-name, branch-name(depositor account)

Πbranch-name (σ branch-city = “Brooklyn”(branch))

additional operation24
Additional Operation
  • Assignment operation: write part of a relational expression to a temporary relation variable. This variable is used later in expression of a query
    • temp1 ←Πcustomer-name (borrower)
    • temp2 ← Πcustomer-name (depositor)
    • Result = temp1 – (temp1 – temp2)