ER Model & Relational Model — Practice

Question 1

Name the three integrity constraints built into the relational model.

Accepted Answer

Domain integrity, requiring each value to belong to its attribute's domain; entity integrity, forbidding nulls in any part of a primary key; and referential integrity, requiring every foreign key value to be null or to match an existing primary key.

Question 2

How is a multi-valued attribute mapped to relations?

Accepted Answer

It becomes its own table holding the owning entity's primary key plus the attribute, with both together forming the primary key of the new table.

Question 3

What distinguishes a candidate key from a superkey?

Accepted Answer

Minimality. A candidate key is a superkey no proper subset of which is also a superkey, so removing any attribute destroys uniqueness.

Question 4

A relation has attributes A, B, C, D, E, F with CD as the only candidate key. How many superkeys does it have?

Accepted Answer

Every superkey must contain C and D, and the other four attributes are free, giving 2 to the power 4, which is 16.

Question 5

An ER diagram has entity sets A, B, C. A relates to B as many-to-many, B relates to C as one-to-many, and A has a multi-valued attribute. Give the minimum number of tables.

Accepted Answer

Three entity tables, one for the many-to-many relationship, and one for the multi-valued attribute, giving five. The one-to-many relationship needs no table, since B's key goes onto C.

Question 6

Explain why the statement 'every department must have a manager' is a participation constraint rather than a cardinality one.

Accepted Answer

It requires each Department entity to appear in at least one manages relationship instance, which is total participation. Cardinality would instead bound how many managers a department may have, which the statement does not address directly.

Question 7

A one-to-one relationship links Employee and Department, with Department totally participating. Where does the foreign key go, and what extra constraint is needed?

Accepted Answer

On Department, because total participation means the column is never null. A unique constraint is required on that foreign key, since without it one employee could manage several departments, violating the one-to-one cardinality.

Question 8

A relation on attributes A, B, C, D, E has candidate keys AB and BC. Count its superkeys.

Accepted Answer

Supersets of AB number 2 to the 3, which is 8, and likewise for BC. Sets containing both contain A, B and C, leaving D and E free, so there are 4. By inclusion-exclusion the total is 8 plus 8 minus 4, which is 12.

Question 9

Show that a ternary relationship among Supplier, Part and Project cannot be replaced by three binary relationships.

Accepted Answer

Suppose supplier S supplies part P to project X and supplies part Q to project Y. The three binary relations record that S supplies P, S supplies Q, S serves X, S serves Y, X uses P and Y uses Q, from which one cannot tell whether S supplies P to X or to Y. The ternary fact is not recoverable.

Question 10

Name three ER constraints no relational schema can express and state how each is enforced in practice.

Accepted Answer

An upper bound on the many side of a many-to-many relationship, total participation of the referenced side of a foreign key, and disjointness of subclasses under a one-table-per-subclass mapping. All three need an SQL assertion, which few systems implement, or a trigger, which is what real systems use.

Practice — ER Model & Relational Model

Sign in to save attempts to your dashboard and track your accuracy over time

Find a verified tutor

Join a live course

Browse coaching centres

About

Categories

Support

Documentation

Get Study Tips & Updates

Download Our App

Download for iOS

Download for Android

Popular Cities