relational data integrity l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Relational data integrity PowerPoint Presentation
Download Presentation
Relational data integrity

Loading in 2 Seconds...

play fullscreen
1 / 25

Relational data integrity - PowerPoint PPT Presentation


  • 194 Views
  • Uploaded on

Relational data integrity . Lecture 8 . Outline. integrity constraints and data definition candidate keys foreign keys nulls domains conditional expressions normal forms. Constraints. in real life systems constraints exist between data values

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 data integrity' - loman


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
outline
Outline
  • integrity constraints and data definition
  • candidate keys
  • foreign keys
  • nulls
  • domains
  • conditional expressions
  • normal forms
constraints
Constraints
  • in real life systems constraints exist between data values
      • it would be useful to communicate these constraints to the database system
  • data is associated with a meaning
      • stating some constraints on data  describing a part of the meaning
  • 90% should be spent on integrity constraints definition
examples of possible inaccuracies
Examples of possible inaccuracies

how would you express, in NL, integrity constraints

that would avoid the above situations?

types of integrity constraints
Types of integrity constraints
  • integrity constraint
  • application specific integrity constraints
    • examples
    • mechanisms
      • domains
      • conditional expressions
      • normal forms
  • generic (inherent to the relational model - resulting from definitions)
      • entity integrity : primary key
      • referential integrity : foreign key
data definition
Data definition
  • integrity constraints definition in a relational language should include
    • primary key definition
    • candidate keys definition
    • foreign key definition - including foreign key rules
    • conditional expressions
example sql data definition in brief
Example - SQL data definition (in brief)
      • CREATE TABLE <relation name> (
      • @<attribute definition><conditional expression>,
      • <primary key definition>,
      • @<candidate key definition>,
      • @<foreign key definition>,
      • @<conditional expressions>
      • ) ;
      • <primary key definition> ::= PRIMARY KEY ( <set of attributes> )
      • <candidate key definition > ::= CANDIDATE KEY ( <set of attributes> )
      • <foreign key definition> ::= FOREIGN KEY ( <set of attributes> )
  • REFERENCES <relation name>
  • ON DELETE <option>
  • ON UPDATE <option>
  • CREATE ASSERTION <name> CHECK <conditional expression>
candidate key
Candidate key
  • candidate key
    • uniqueness property
    • irreducibility property
  • entity integrity constraint
  • simple/composite
  • primary/alternate
foreign key
Foreign key
  • foreign key (FK)
    •  corresponding candidate key (CK) in another relation
    •  FK  CK such that FK = CK (reverse not required)
  • target/referenced relation/tuple | referring relation/tuple
  • referential integrity constraint
  • foreign keys and PostgreSQL
what happens if
What happens if ...
  • short saving accounts are not offered by the bank anymore?
  • the “code” and name for small saving accounts is to be changed to ‘i-sav’ and ‘instant saving’ respectively?
  • the interest for s-sav is to be decreased by 0.3%?
foreign key rules
Foreign key rules
  • the modifications are performed in the REFERRED relation
  • rules
      • ON DELETE RESTRICT
      • ON DELETE CASCADE
      • ON UPDATE RESTRICT
      • ON UPDATE CASCADE
  • split into four groups: think of one example for each situation; don’t use the study guide
examples
Examples
  • on delete restrict
      • students and books
  • on delete cascade
      • employees and children
  • on update restrict
      • students and modules
  • on update cascade
      • employees and departments
nulls18
Nulls
  • representing missing/unavailable information
  • primary key and nulls
  • foreign key and nulls
domains
Domains
  • expressing integrity constraints on scalar values
      • constraints on permissible scalar values
      • constraints on the applicability of scalar operators
  • SQL
      • does not support domains
      • offers other mechanisms
conditional expressions
Conditional expressions
  • will be studied with SQL
  • pointer forward:
    • the university’s database:“a student has two choose two options (1/2cu courses) in the final year; if the students chooses an extra optional course than the final year project will count as only 1/2cu”
fds mds and jds
FDs, MDs, and JDs
  • particular constraints
    • functional dependencies
    • multiple dependencies
    • join dependencies
  • expressed by means of normal forms
  • extensively studied in the following lectures
integrity constraints further considerations
Integrity constraints - further considerations
  • state and transition integrity constraints
      • example
  • when and how are the integrity constraints applied
    • depends on the type
      • ‘normal forms’ and ‘domains’ - always
      • entity and referential - after each transaction
      • conditional expressions
        • default : after each transaction
        • other possibilities?
      • correct database = the logical AND of the set of integrity constraints is satisfied after each transaction
summary
Summary
  • the relational data model
      • data objects
      • operators
      • integrity constraints
  • SQL implements the relational model
      • the subject of the next lectures
  • you know what a relational model is, but do not know yet how to design one