slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
06 Relational DBMS PowerPoint Presentation
Download Presentation
06 Relational DBMS

Loading in 2 Seconds...

play fullscreen
1 / 19

06 Relational DBMS - PowerPoint PPT Presentation


  • 90 Views
  • Uploaded on

And Franchise Colleges. DATABASE DEVELOPMENT. 06 Relational DBMS. By MANSHA NAWAZ. Overview. In this lecture we well be looking at: Entity Integrity. Referential Integrity. Discuss the effect UPDATE, DELETE and INSERT can have on Referential Integrity. Entity Integrity.

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 '06 Relational DBMS' - martha


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
slide1

And Franchise Colleges

DATABASE DEVELOPMENT

06 Relational DBMS

  • By MANSHA NAWAZ

Section 06

overview
Overview

In this lecture we well be looking at:

  • Entity Integrity.
  • Referential Integrity.
  • Discuss the effect UPDATE, DELETE and INSERT can have on Referential Integrity.

Section 06

entity integrity
Entity Integrity
  • Example: STUDENT (STUDENT# , NAME , …)
    • student# is the primary key
    • A particular student#, e.g. 'S4', can only occur once in that column of the table.
    • Each row in the table STUDENT represents a real-world entity.
    • The first row [S4, Ramesh] implies that there is a student somewhere identified by 'S4' and named 'Ramesh'.
    • If each row represents an real-world entity we must be able to differentiate between them.
    • Would you allow rows like this in a database?

Section 06

entity integrity continued
Entity Integrity continued ….
  • Definition of Entity Integrity

Entity Integrity requires that every attribute that participates in a primary key is not allowed to have a null (empty) value.

  • Example:
  • This example breaks Entity Integrity
    • The missing ISBNX number is only part of the primary key but the identity of the row is still lost.
    • The table is a list of reservations by borrowers of particular books.
    • The row with the missing ISBNX has no meaning (or identity).

Section 06

implementing entity integrity in sql
Implementing Entity Integrity in SQL
  • SQL provides direct support for Entity Integrity through CONSTRAINTS
  • Always set any attribute which is part of a primary key to NOT NULL.

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

Section 06

referential integrity
Referential Integrity
  • Referential Integrity is to do with the use of Foreign Keys and how we can be sure that only valid values of foreign key values are stored in a database.

Example:

  • It would seem reasonable to assume that for each EMPLOYEE# in the TASK table there is an appropriate row in the EMPLOYEE table.
  • Does the example live up to this?
  • Not a good idea at all.

Section 06

implementing referential integrity sql 92
Implementing Referential Integrity (SQL-92)

CREATE TABLE PUBLISHER (

PUB_CODE CHAR(4) NOT NULL,

PUB_NAME CHAR(20)NOT NULL,

CONSTRAINT pub_primary_key PRIMARY KEY (PUB_CODE));

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE),

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • This shows a basic implementation of referential integrity.
    • There is more …
    • What happen if we delete a BOOK?

Section 06

why do we need to do more than just specify the foreign key
Why do we need to do more than just specify the foreign Key?
  • We need to tell the RDBMS what to do in the following situations.
    • When a row containing an invalid foreign-key value is inserted in a table.
    • When a foreign key in a table is updated with an invalid value.
    • When a primary key that is referenced as a foreign key in another table is updated.
    • When a row with a primary key that is referenced as a foreign key in another table is deleted.
  • We will deal with deletions first.

Section 06

referential integrity and on delete cascade
Referential Integrity and ON DELETE CASCADE

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON DELETE CASCADE,

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • Some new syntax.
    • If a PUBLISHER is deleted (in the publisher table) then all matching records are deleted in the BOOK table.
    • It ‘cascades’ through the book table.
    • Is this the right strategy when a PUBLISHER is deleted?

Section 06

referential integrity and on delete set default
Referential Integrity and ON DELETE SET DEFAULT

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) DEFAULT ‘CORG’,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON DELETE SET DEFAULT,

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • If a PUBLISHER is deleted (in the publisher table) then all matching records in the BOOK table have their PUB_CODE set to the value ‘CORG’.
    • This is the DEFAULT value for PUB_CODE
    • This is just an example of the syntax - using ‘CORG’ as the DEFAULT may not actually be a good idea.

Section 06

slide11

Referential Integrity and ON DELETE SET NULL

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON DELETE SET NULL,

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • If a PUBLISHER is deleted (in the publisher table) then all matching records in the BOOK table have their PUB_CODE set to NULL.
    • Is this a better idea?
    • Perhaps we don’t want to lose the BOOK records although the PUBLISHER has been deleted.

Section 06

referential integrity and on delete no action
Referential Integrity and ON DELETE NO ACTION

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON DELETE NO ACTION,

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • If a user attempts to delete a PUBLISHER (in the publisher table), and there are matching records in the BOOK table, then the delete is refused.
    • You have to remove all references to a particular PUB_CODE in the BOOK table before you can delete that PUBLISHER.

Section 06

referential integrity and on update cascade
Referential Integrity and ON UPDATE CASCADE

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON UPDATE CASCADE,

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • If a PUB_CODE value is updated (changed) in the publisher table then all matching records are updated in the BOOK table.
    • It ‘cascades’ through the book table.

Section 06

referential integrity and on update set default
Referential Integrity and ON UPDATE SET DEFAULT

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) DEFAULT ‘CORG’,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON UPDATE SET DEFAULT,

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • If a PUB_CODE value is updated (changed) in the publisher table then all matching records in the BOOK table are set to the DEFAULT value.
    • Clearly not what you would do with PUBLISHER and BOOK.

Section 06

slide15

Referential Integrity and ON UPDATE SET NULL

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON UPDATE SET NULL,

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • If a PUB_CODE value is updated (changed) in the publisher table then all matching records in the BOOK table are set to NULL.
    • Again probably a bad idea for this example but useful in other situations.

Section 06

slide16

Referential Integrity and ON UPDATE NO ACTION

CREATE TABLE BOOK (

ISBN CHAR(8) NOT NULL ,

PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,

NOW_PRICE NUMBER(10,2),

CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON UPDATE NO ACTION,

CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ;

  • If a user attempts to change a PUB_CODE value in the publisher table, and there are matching records in the BOOK table, then the delete is refused.

Section 06

referential integrity continued
Referential Integrity continued ...
  • Finally:
    • Most normal updates will not cause such problems.
    • Inserting a new BOOK row with a valid PUB_CODE will have no effect on Referential Integrity.
    • Deleting PUBLISHERS or updating PUB_CODEs in the PUBLISHER table are quite rare events.

Section 06

end of lecture

END OF LECTURE

Section 06