Question

Question 1                                                                                                                    2 Marks

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

Question 2                                                                                                                    2 Marks

This question refers to the Part table as used in this unit lecture slides about the customers to order homeware parts. Using SQL DCL command to write a statement to permit a user with the log in ID of JOHNSON to access the Part table and update its UnitPrice value.

Question 3                                                                                                                    2 Marks

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

Question 4                                                                                                                    2 Marks

Explain the purpose of transaction logs and checkpoints.

Question 5                                                                                                                    2 Marks

Compare and contrast Data Warehousing against Database.

Answer 1

1. Let us assume that there are Employees in an Organisation. Also there are Departments within that Organisation. Now, every employee works for a Department. And hence, a relationship exists between Employee and Department. In case of DBMS representation, there will be 2 tables for this mentioned scenario. Records are matched using foreign keys and this is known as referential integrity rule.

There can be 3 cases of this referential integrity constraint violation:

a> Insertion in a referencing relation

b> Deletion from a referencing relation

c> Updation of a referencing relation

2. GRANT ALL ON PART.UNITPRICE TO JOHNSON

3. In the given table to be in 3NF, Building and Accommodation Fee should be clubbed together. And Student details need to be in a different table matched to the building name/id.

4. Checkpoint means storing all logs into a storage disk after a specific time interval. Transaction logs are stored so that it can be referenced for future audit purposes.

5. Data Warehousing referes to the process of having multiple heterogeneous sources and integrating data in between these databases. Whereas, a database simply resembles a single storage for data in any relational format (RDBMS).