Answer to Question #174274 in Databases | SQL | Oracle | MS Access for Kwame Hay

Question

Question 2

A. Using the right integrity rules, explain why it is important to control how data is deleted from relational database. [10 Marks]

B. In your own words, explain why one cannot consider every table as a relation. [10 Marks]

C. All relations are in 1st Normal form. Explain.

Answer 1

A.

Data integrity is the accuracy, completeness, and reliability of data throughout its lifecycle ensuring that data stored is secure no matter how frequently it has been accessed.

When one creates relationships between Entities in a module, one must define which kind of referential integrity to use when deleting records. The referential integrity specifies what happens to a record of an Entity B that references a record of Entity A, when the Entity A record is deleted.

Thus to specify the referential integrity is to define what happens for instance to the Orders of a Customer when that Customer is deleted.

To specify the referential integrity between two Entities, one needs to edit the reference attribute of the related Entity by setting the delete rule property to either the following values: Protect, Delete or Ignore.

Setting the Delete Rule to Protect prevents deleting records of the main Entity while there are associated records in the related Entity.

Setting the Delete Rule to Delete guarantees that when a record of the main Entity is deleted, all the associated records in the related Entities are also deleted. A mechanism known as Cascade Delete.

Setting the Delete Rule to Ignore allows deleting records of the main Entity while keeping the associated records in the related Entity but ignore value does not guarantee referential integrity.

B.

Every table is not is not a relation

A relation in a relational model terminology is like a table in SQL terminology and when designing a relation one should define the meaning of the relation and the meaning of its elements, the tuples and attributes. But ideally the SQL table in reality is not in it's 1st Normal form as a table can be in any of the form.

C.

All relations are in 1NF because the domain of each attribute contains only indivisible values, and the value of each attribute contains only a single value from that domain.