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

Question 1   

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

Answer:

1.Referential Integrity is set of constraints applied to foreign key which prevents entering a row in child table (where you have foreign key) for which you don't have any corresponding row in parent table i.e. entering NULL or invalid foreign keys. Referential Integrity prevents your table from having  incorrect or incomplete relationship.

2.Referential integrity refers to the accuracy and consistency of data within a relationship.

3.In relationships, data is linked between two or more tables. This is achieved by having the foreign key (in the associated table) reference a primary key value (in the primary – or parent – table). Because of this, we need to ensure that data on both sides of the relationship remain intact. So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

Examples of referential integrity constraint in the Customer/Order database of the Company:

• Customer(CustID, CustName)
• Order(OrderID, CustID, OrderDate)

To ensure that there are no orphan records, we need to enforce referential integrity. An orphan record is one whose foreign key Foreign Key value is not found in the corresponding entity – the entity where the Primary Key is located. Recall that a typical join is between a Primary Key and Foreign Key.

The referential integrity constraint states that the customer ID (CustID) in the Order table must match a valid CustID in the Customer table. Most relational databases have declarative referential integrity. In other words, when the tables are created the referential integrity constraints are set up.

Question 3   

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.   

Answer : -

the following relation is in 2NF but not in 3NF.

STUDENT_ ACCOMMODATION (StudentID, Buidling, AccommodationFee)

- because the nonkey attribute AccommodationFee is transitively dependent on the key, StudentID

Question 4                                                                                                                    2 Marks

Explain the purpose of transaction logs and checkpoints.

Answer : -

1.The transaction log supports the following operations:

• Individual transaction recovery.
• Recovery of all incomplete transactions when SQL Server is started.
• Supporting transactional replication.
• Rolling a restored database, file, filegroup, or page forward to the point of failure.
• Supporting high availability and disaster recovery solutions: Always On availability groups, database mirroring, and log shipping.

2. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also records the information in the transaction log.

Question 5                                                                                                                    2 Marks

Compare and contrast Data Warehousing against Database.

	DATABASE	DATA WAREHOUSE
Definition	Database, also called electronic database, any collection of data, or information, that is specially organized for rapid search and retrieval by a computer.Databases are structured to facilitate the storage, retrieval, modification, and deletion of data in conjunction with various data-processing operations.	Data warehousing is the electronic storage of a large amount of information by a business or organization. A data warehouse is designed to run query and analysis on historical data derived from transactional sources for business intelligence and data mining purposes.
Design	Design of operational database is different from data warehouse design. It mainly observes data accuracy when updating real-time data.	The design of data-warehouse ensures vast range of data which is used over-time for analysis purpose.
Focus	The focus of database is mainly on transactions with the help of queries.	It has ability of data analysis which is collected from different sources and generate reports.
Type of Information	The databases provide information such online availability of seats.	The information obtained from datawarehouse are used for to check the performance of business.
Types	there are many types of databases. The examples are OLTP, CSV, text files, excel spreadsheets and XML files etc.	It is an OLAP type of database which exist on the top layer of other database and perform analysis.