Data Integrity - E-Commerce - Lecture Slides, Slides of Fundamentals of E-Commerce

Download Data Integrity - E-Commerce - Lecture Slides and more Slides Fundamentals of E-Commerce in PDF only on Docsity!

Databases

Lecture Objectives

Data Integrity Constraints FOR Accurate DataEntry

– Single Table Integrity Constraints– Referential Integrity Constraints

Significance of Table Relationships

– Controlling Referential Integrity– Building Queries

Referential Integrity Maintenance Approaches

– One-to-One Relationship Maintenance– One-to-Many Relationship Maintenance

…. Data Integrity Constraints

Related Table Constraints [

Referential Integrity

]

Specifies that a

record in one table must be related to the records in

other tables

, thus exhibiting a particular relationship between the two

tables

– For example

, In a

University database

• STUDENT

,^

COURSE

,^

DEPARTMENT

,^

DORM

-^

Each record in STUDENT

is related with the

records in COURSE,

DORM and DEPARTMENT

-^

The

details of this relationship

can be understood by the surrounding

(university) environment.

Relationship Types

Types of Relationships

(in real life situations)

One to one

relationship

-^

A record

–^

in the

source file

must have

zero or only one record

in the

related file

–^

in the

related file

must have

only one record

in the

source file

–^

DBMS Implementation:

Establishing

Relationship

One to many

(1:M)

relationship

-^

A record

–^

in the

source file

must have

zero or one or many records

in the

related file

–^

in the

related file

must have

only one record

in the

source file

–^

DBMS Implementation:

Establishing

Relationship

DORM

STUDENT

DEPARTMENT

STUDENT

Relationship Significance

Significance of defining Relationships

Implementation of Integrity constraints

-^

Any attempt of entering invalid data values will be checked/ rejectedautomatically by the DBMS.

-^

However, it can not check all possible constraints.

–^

For example, a

grade letter A entered wrongly as C cannot be checked

but

entered as X (invalid entry for grades) can be rejected

More efficient/ accurate data retrieval for Queries

-^

For example, student grade list query needs data records from threetables:

STUDENT

,^

GRADE

,^

COURSE

-^

Established relationships will make the retrieval efficient andaccurate.

Requirements for Establishing A

Relationship

Field Repetition

• A field (

primary key

)^

from one-table

is repeated

in

many-table

as a

foreign key

.

• The fields should be the same in data type and size.– The names of the fields could be different in each

table.

Referential Integrity Maintenance •^

Requirement (1:1 Relationships)

• Each record in

source-table

should have

zero or one

records

in

related-table

.

• Each record in

related-table

must have

a

corresponding record

in

source-table

.

Example: DORM

STUDENT

…Referential Integrity Maintenance

(1:1)

Related Table Changes

• Deletion requests

in related-table are always acceptable.

• Modification requests

in many-table are

• accepted when foreign key is not touched.• rejected when a value is used in the foreign key (linked field)

which doesn’t exist in one-table.

…Referential Integrity Maintenance

Requirement (1:M Relationships)

• Each record in

one-table

should have

zero or many

records

in

many-table

.

• Each record in

many-table

must have

a corresponding

record

in

one-table

.

1:M Example:

DEPT

STUDENT

… Referential Integrity Maintenance

Many-table changes.

• Deletion requests

in many-table are always acceptable.

• Modification requests

in many-table are

• accepted when foreign key is not touched.• rejected when a value is used in the foreign key (linked field) which

doesn’t exist in one-table.