Answer to Question #219250 in Databases | SQL | Oracle | MS Access for Enam

Question

a. The three major types of integrity constraints are domain constraints, entity integrity, and referential integrity. Using examples, explain these integrity constraints and their usefulness in databases.

b. A subtype/supertype relationships are especially important in Enhanced Entity Relationship Modeling. Explain using an example how you would covert subtype/supertype relationships to relations in database design.

Answer 1

a.

1. Domain integrity- restricts the values of attributes in a relation and is a constraint of the relational model. It specifies that all columns in a relational database must be declared upon a defined domain ensuring that all data in a field contains valid values. E.g. if one defines the attribute of Age, of an Employee entity as an integer, the value of every instance of that attribute must always be numeric and an integer.
2. Entity integrity - ensures that each entity or row of a table contains a unique and non-null primary key value this ensures that each row in a table has a unique identifier. E.g. Employee ID
3. Referential integrity - requires that a foreign key must have a matching primary key or it must be null. This constraint is specified between two tables ensuring that reference from a row in one table to another table is valid. E.g. Customer_ID

b. Super type is a generic entity type that has a relationship with one or more sub types. It possesses the mutual attributes of the sub types it contains.

One way to convert the sub type/ super type into relations is by creating a relation for each entity type in the hierarchy.

In this model, the same primary key is shared among all the relations that represent the entity types in the same hierarchy. The foreign key referencing its super type relations corresponds to the primary key underlining the sub type relations. The primary key is an exception here.

In this University model, the Foreign key is possessed by the student that is referencing the SubjectArea. i.e. the Subject

A foreign key that both the employee and Student have reference to a person.i.e. personId. So, if a row is present in Employee then a row must be present in person corresponding to Employee.