COMP 378 Lecture Notes - Lecture 2: Referential Integrity, Data Integrity, Associative Entity
30 Jun 2018
School
Department
Course
Professor
Question 2 (12 marks)
Answer the following questions (250 words max/question).
a. What are the typical integrity controls performed in both data integrity and referential
integrity?
Answer
Data integrity are physically built in into the structure of the fields and controls.
Following are the typical integrity controls that DBMS support:
• Default value: It is important to assign a default value to a field in order to reduce
chance of errors in data entry as well as save time.
• Range control: The range control can be set as numeric lower and upper
boundary or a set of specific values. Range has to be set with caution as they can
change overtime and the changes have to be reflected.
• Null value control: It has to be set in the primary key to enforce an integrity
control that prohibits a null value. It should be only implemented when it is part of
a business rule.
• Referential integrity: It is a type of range control in which the value of a field must
coexist in another row of the same or different table. The range is determined by
the changing contents of the database table. Referential integrity only makes that a
existing cross-referencing value is used even it is incorrect. Referential integrity - in some cases
can be null
Data integrity – data type check
b. Using an example for each situation, illustrate the three common situations that suggest
relations should be denormalized.
Answer:
1. First situation of the three situations which might require relation to be
denormalized is the when Two entities have a one-to-one relationship.
For example : If one person can have only one passport with data
from a obtained from the option passport application the person chooses to complete
Thus, each record can be made up of 4 fields from a normalization relation of the Person
(Person Name, Person ID) and Passport tables (Passport Number, Residence Address).
2. Second situation which might require a relation is denormalized is when A many-
to-many relationship (associative entity)is involved along with non-key attributes . An
example of this would be when obtaining the sales figures from a SalesPersons table for a
particular product in Product Table. A third table named Sales would be created here with
no foreign key and a primary key created from a combination of unique identifiers of the
two entities in the many-to-many relationship. Third situation would result from using
Reference data . An example would result where several Student would share the same
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
Data integrity are physically built in into the structure of the fields and controls. The range is determined by the changing contents of the database table. Referential integrity only makes that a existing cross-referencing value is used even it is incorrect. Referential integrity - in some cases can be null. Data integrity data type check: using an example for each situation, illustrate the three common situations that suggest relations should be denormalized. First situation of the three situations which might require relation to be. 1. denormalized is the when two entities have a one-to-one relationship. For example : if one person can have only one passport with data from a obtained from the option passport application the person chooses to complete. Thus, each record can be made up of 4 fields from a normalization relation of the person (person name, person id) and passport tables (passport number, residence address).
