The relational model
Download
1 / 22

The Relational Model - PowerPoint PPT Presentation


  • 247 Views
  • Uploaded on

The Relational Model. Stanislava Armstrong http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html. The Relational Model. Introduced in E.F. Codd’s 1970 paper “A relational Model of the Data for Large Shared Data Banks”

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' - cloris


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

Stanislava Armstrong

http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html


The relational model1
The Relational Model

  • Introduced in E.F. Codd’s 1970 paper “A relational Model of the Data for Large Shared Data Banks”

  • The foundation for most (but not all) current database systems

  • Concerned with 3 main things

    • Data Structure (how data is represented)

    • Data integrity (what data is allowed)

    • Data manipulation (what you can do with the system)


The database terms
The Database Terms

  • Data is stored in relations (tables)

  • Each relation has a scheme (heading)

  • The scheme defines the relation’s attributes (columns)

  • Data takes the form of tuples (rows)

Fruit


The database terms1
The Database Terms

Fruit

More formally:

  • A scheme is a set of attributes

  • A tuple assigns a value to each attribute in its scheme

  • A relation is a set of tuples with the same scheme

Fruit{fruitName, fruitPrice}

{ { (fruitName, Melon), (fruitPrice, 800)},

{ (fruitName, Strawberry), (fruitPrice, 150)},

{ (fruitName, Apple), (fruitPrice, 120)},

{ (fruitName, Lemon), (fruitPrice, 200)}}


Properties of relations
Properties of Relations

  • Since relations are sets of tuples, the tuples of a relation are unique and unordered

  • The number of tuples in a relation is called the cardinality of the relation

  • Since schemes are sets of attributes, the attributes of a relation are unique and unordered

  • The number of attributes in a relation is called the relation’s degree


Example
Example

Fruit


Relational data integrity
Relational Data Integrity

  • Data integrity controls what data can be in a relation

    • Domains restrict the possible value a tuple can assign to each attribute

    • Candidate and Primary Keys identify tuples within a relation

    • Foreign Keys link relations to each other


Attributes and domains
Attributes and Domains

  • A domain is given for each attribute

  • A domain lists the possible values for each attribute

  • Each tuple assigns a value to each attribute from its domain

Examples

  • ‘price’ could be chosen from the set of positive real numbers

  • An attribute called ‘name’ could have a domain which is a string of length 50.


Candidate keys
Candidate Keys

A set of attributes in a relation is called a Candidate Key if and only if:

  • Every tuple has a unique value for the set of attributes (uniqueness)

  • No proper sunset of the set had the uniqueness property (minimality)

Candidate Keys: {ID} and {prodName, price}

{ID, prodName} and {ID, price} can uniquely identify each row, but are not minimal


Primary keys
Primary Keys

  • One Candidate Key is usually chosen to identify tuples in the relation

  • This is called Primary Key

  • Often a special ID attribute is chosen for a Primary Key

We could choose either {ID} or {profName, price} for Primary Key. {ID} is more convenient as it is a single column and it will always be unique. What would happen if we want to add another melon(maybe its a different variety) with the same price?


Nulls and primary keys
NULLs and Primary Keys

  • Missing information can be represented using NULLs

  • A NULL represents a missing or unknown value

  • More on NULL in the later lectures 

  • Entity Integrity : a Primary Key cannot contain NULLs


Foreign keys
Foreign Keys

  • Foreign Keys are used to link data in two relations

  • A set of attributes in the first (referencing) relation is a Foreign Key for the second (referenced) relation, if its value always either:

    • Matches a Candidate Key value in the referenced relation, or

    • Is wholly NULL

    • This is called Referential Integrity


Example1
Example

Fruit

Transaction

fruitID is a Candidate Key for the Fruit relation

fruitID is a Foreign Key in the Transaction relation – each fruitID in the Transaction relation is either NULL or matches an entry in the fruit relation.


Referential integrity
Referential Integrity

  • When relations are updated referential integrity can be violated

  • This usually occurs when a referenced tuple is updated or deleted

  • There are a number of options:

  • RESTRICT – stop the user from doing it

  • CASCADE – let the changes flow on

  • NULLIFY – make values NULL

  • Triggers – user defined action


Example2
Example

What happens if:

  • fruitID for Melon is changed from 1 to 5?

  • The entry for Lemon is deleted from the fruit table?


Restrict
RESTRICT

RESTRICT stops any action that violates integrity

  • You cannot update or delete Melon or Lemon

  • You can change or delete Strawberry as it is not referenced


Cascade
CASCADE

CASCADE allows changes made to flow through

  • If Melon fruitID is changed to 5 in the fruit table, it is also changes in the Transaction table

  • If Lemon is deleted so it transaction 2


Nullify
NULLIFY

NULLIFY sets problematic values to NULL

  • If Melon fruitID changes, the fruitID for transaction 1 and 3 will become NULL, where they were 1 before

  • If Lemon is deleted the fruitID for transaction 2 will be NULL


Naming conventions
Naming Conventions

Naming attributes

  • A consistent naming convention can help remind you of the structure of the relation

  • Assign each relation a unique prefix, so Fruit relation will have fruitName and Client relation CliName

  • Naming Keys

  • Having a unique number as the primary key can be useful

  • If the table prefix is fruit, call this fruitID

  • A Foreign Key to this table is then also called fruitID


Exercise
Exercise

For each of the relation below:

  • Determine the Scheme, Attributes, Tuples, Cardinality, Degree

  • Suggest a domain for each attribute

  • Determine the Candidate Keys and suggest the most appropriate one to become Primary Key

  • Explain the term entity integrity

  • Determine any Foreign Keys

  • Explain the term referential integrity

  • Show how the tables will change if:

    • holID for Majorca changes to 10

    • Holidays to Amsterdam go up in price by £50

    • Holidays to Amsterdam are discontinued

    • Steve Benford has to cancel his holiday

      Depending on whether RESTRICT, CASCADE or NULLIFY are used.


Exercise1
Exercise

Holiday

Client


Reading material
Reading Material

  • The Manga Guide to Databases – chapters 1 and 2

  • Database Systems – A Practical Approach to Design, Implementation and Management by Connolly and Begg – chapters 3 and 4