Question

Answer these theory questions:

a. Explain the referential integrity rule. Provide a suitable situation when the referential integrity constraint is violated.

b. Consider a relation named STUDENT_ ACCOMMODATION (StudentID, Buidling, AccommodationFee) as shown in the figure below. Explain why this relation is in 2NF but not in 3NF.             

StudentID	Building	AccommadtionFee
101	Alpha	$320
102	Betta	$250
103	Alpha	$320
104	Betta	$250
105	Gemma	$400

c. Explain the purpose of transaction logs and checkpoints.

d. Compare and contrast Data Warehousing against Database.

Answer 1

(a)REFERENTIAL INTEGRITY RULES:

(1)RESTRICT: It does not allow the updation or deletion of the referenced data.

(2)SET TO NULL: When the referenced data is deleted or updated then the dependent values is set to NULL.

(3)SET TO DEFAULT: When the referenced data is deleted or updated then the dependent values is set to a default value.

(4)CASCADE: When the referenced data is deleted or updated then the all the dependent values are deleted.

Example for referential integrity violation:

(1)Consider two tables A(a1(primary key),a2,a3) and B(b1(primary key),b2(foreign key),b3).

(2)Consider the deletion takes place in the table A.Now the foreign key b2 in the table B refers to the primary key of a1.

(3)The value that is being referenced in the table B is not present in the table A.This leads to the violation of referential integrity constraint.

(b)

(1)The functional dependencies that holds on the table is StudentId -> Building and Building->AccommodationFee.

(2)Now the table is in 2NF because there is there is no partial dependency.(part of the key-> Non key attribute).

(3)Now the table is not in 3NF because of the functional dependency Building->AccommodationFee.

(4)For the table to be in 3NF then there shouldn't be any transitiove dependencies(A->B , B->C => A->C).

(c)

TRANSACTION LOGS:

(1)Transaction logs are the separate files where the updation that is made into the database are stored for a temporary period of time.

(2)After the commit operation operation of the transaction, the log files are permanently updated in the actual database.

CHECKPOINTS:

(1)Checkpoints in the transaction ensures that the changes that are performed are permanently updated.

(2)After the checkpoints the changes have not been updated.

(3)It ensures that the database before the checkpoint is in consistent state.

(d)

DATAWAREHOUSE:

(1)Datawarehouse is a place where all kinds of data are stored.

(2)The size of the data in the datawarehouse is very much larger in size compared to the database.

(3)The data that is stored in the datawarehouse is huge and historical.

DATABASE:

(1)Database stores similar kinds of data.

(2)The size of the data stored in the databases are very small compared to the datawarehouse.

(3)The data that is stored in the database is very small.