Database Schema Integrity Analysis

This question tests your understanding of constraint enforcement in a relational database. Each proposed insertion must be checked against the declared constraints on the target table.

(a) Insertions into UNIFORM

The UNIFORM table has three constraints to verify:

• UCode is the primary key (must be unique and NOT NULL).
• UName has a NOT NULL constraint.
• UColor has no constraint (NULL is allowed).

(i) Tuple: (7, Handkerchief, NULL)

Can be inserted.

• UCode = 7 is unique (not present in the existing table) and not null.
• UName = 'Handkerchief' is not null.
• UColor = NULL is permitted because no constraint forbids it.

All constraints are satisfied.

(ii) Tuple: (4, Ribbon, Red)

Cannot be inserted.

• UCode = 4 already exists in the UNIFORM table (Tie, Blue).
• The primary key constraint requires uniqueness; inserting a duplicate UCode violates this.

Even though UName and UColor are valid, the primary key violation blocks the insertion.

(iii) Tuple: (8, NULL, White)

Cannot be inserted.

• UCode = 8 is unique and not null.
• UName = NULL violates the NOT NULL constraint on UName.

The insertion fails because a mandatory attribute is missing.

(b) Insertions into COST

The COST table has:

• A composite primary key on (UCode, Size) — the pair must be unique.
• A check constraint Price > 0.

(i) Tuple: (7, S, 0)

Cannot be inserted.

• The composite key (7, S) is unique (not present in the existing COST table).
• However, Price = 0 violates the constraint Price > 0.

The check constraint failure prevents the insertion.

(ii) Tuple: (9, XL, 100)

Can be inserted from the perspective of COST's own constraints.

• The composite key (9, XL) is unique.
• Price = 100 satisfies Price > 0. …

Understanding Database Constraints

Before we can decide whether a tuple can be inserted, we need to understand what each constraint means and how it restricts the data.

Primary Key ensures that each value (or combination of values, in the case of a composite key) uniquely identifies a row and cannot be NULL. No two rows can have the same primary key value.

NOT NULL means the attribute must have a value in every row; it cannot be left empty.

CHECK constraint (Price > 0) means the Price attribute must contain a value strictly greater than zero.

The UNIFORM table has UCode as its primary key and UName as NOT NULL. The COST table has a composite primary key on (UCode, Size) and a constraint that Price > 0.

(a) Insertions into UNIFORM Relation

(i) (7, Handkerchief, NULL)

This tuple can be inserted.

• UCode = 7 is unique (not already in the table), satisfying the primary key constraint.
• UName = 'Handkerchief' is not NULL, satisfying the NOT NULL constraint.
• UColor = NULL is allowed because UColor has no NOT NULL constraint.

All constraints are satisfied.

(ii) (4, Ribbon, Red)

This tuple cannot be inserted.

• UCode = 4 already exists in the UNIFORM table (the row for 'Tie').
• Primary keys must be unique, so inserting another row with UCode = 4 violates the primary key constraint.

(iii) (8, NULL, White)

This tuple cannot be inserted.

• UCode = 8 is unique, which is fine for the primary key.
• UName = NULL violates the NOT NULL constraint on UName.

Even though UCode is valid, the NOT NULL constraint on UName prevents this insertion.

(b) Insertions into COST Relation

(i) (7, S, 0)

This tuple cannot be inserted for two reasons:

1. Foreign key violation (implicit): Although not explicitly stated in the schema diagram, UCode in COST logically references UCode in UNIFORM. Since UCode = 7 does not exist in UNIFORM (we established in part (a)(i) that it could be inserted but hasn't been yet), this would violate referential integrity if a foreign key constraint is enforced.

2. CHECK constraint violation: Price = 0 violates the constraint Price > 0. The price must be strictly greater than zero. …