rdbmssection 061 06 relational dbms database development and franchise colleges by mansha nawaz

RDBMS Section 06 1

06 Relational DBMS

DATABASE DEVELOPMENT

And Franchise Colleges

By MANSHA NAWAZ

RDBMS Section 06 2

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.

RDBMS Section 06 3

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?

STUDENT# NAMES4 RameshS2 PeterS9 AnthonyS11 Priti

STUDENT# NAMES4 Ramesh

PeterS9 AnthonyS11 Priti

FrankS45 PeterS8 John

RDBMS Section 06 4

• 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:

Entity Integrity continued ….

Reservation BOR_NO ISBNX RESERVE_DATE B0000006 09952500 20-11-91 B0000004 45002286 13-06-93 B0000004 13-06-93 B0000009 55213462 30-06-93 B0000007 45002286 01-07-93 B0000002 72216393 02-07-93 B0000008 45002286 03-07-93 B0000007 45000918 11-07-93

• 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).

RDBMS Section 06 5

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) ) ;

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.

RDBMS Section 06 6

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.

TASK

EMPLOYEE# PROJECT# ROLE SUPERVISOR HOURS-ALLOCATED

HOURS-SO-FAR

HOURS-REQUIRED

E2 P9 program E123 120 85 100

E2 P4 design E101 300 250 200

E101 P9 design E101 60 128 56

E22 P11 test E345 40 0 40

EMPLOYEE# Name

E2 Fred Bloggs

E101 John Burns

E220 Sameer Patel

E345 Betty Bothroyd

E123 Peter Silcott

RDBMS Section 06 7

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),


Implementing Referential Integrity (SQL-92)

• This shows a basic implementation of referential integrity.

– There is more …

– What happen if we delete a BOOK?

RDBMS Section 06 8

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.

RDBMS Section 06 9

Referential Integrity and ON DELETE CASCADE

CREATE TABLE BOOK (


PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,


CONSTRAINT book_foreign_key

FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE)

ON DELETE CASCADE,


• 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?

RDBMS Section 06 10

Referential Integrity and ON DELETE SET DEFAULT

CREATE TABLE BOOK (


PUB_CODE CHAR(4) DEFAULT ‘CORG’,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,




ON DELETE SET DEFAULT,


• 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.

RDBMS Section 06 11

Referential Integrity and ON DELETE SET NULL

CREATE TABLE BOOK (


PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,




ON DELETE SET NULL,


• 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.

RDBMS Section 06 12

Referential Integrity and ON DELETE NO ACTION

CREATE TABLE BOOK (


PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,




ON DELETE NO ACTION,


• 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.

RDBMS Section 06 13

Referential Integrity and ON UPDATE CASCADE

CREATE TABLE BOOK (


PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,




ON UPDATE CASCADE,


• 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.

RDBMS Section 06 14

Referential Integrity and ON UPDATE SET DEFAULT

CREATE TABLE BOOK (


PUB_CODE CHAR(4) DEFAULT ‘CORG’,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,




ON UPDATE SET DEFAULT,



matching records in the BOOK table are set to the DEFAULT value.

– Clearly not what you would do with PUBLISHER and BOOK.

RDBMS Section 06 15

Referential Integrity and ON UPDATE SET NULL

CREATE TABLE BOOK (


PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,




ON UPDATE SET NULL,



matching records in the BOOK table are set to NULL.

– Again probably a bad idea for this example but useful in other situations.

RDBMS Section 06 16

Referential Integrity and ON UPDATE NO ACTION

CREATE TABLE BOOK (


PUB_CODE CHAR(4) ,

TITLE CHAR(40) ,

PUB_DATE CHAR(4) ,




ON UPDATE NO ACTION,


• 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.

RDBMS Section 06 17

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.

RDBMS Section 06 18

Referential Integrity – MS SQL Server 2000 Enterprise Manager

RDBMS Section 06 19

END OF LECTURE

rdbmssection 061 06 relational dbms database development and franchise colleges by mansha nawaz

Documents