Q2Data Base Management System

(a) Super key, candidate key, and primary key

A super key of a relation is any set of one or more attributes whose values, taken together, uniquely identify every tuple in that relation. A super key need not be minimal - if a set of attributes is already a super key, adding any further attribute to it still yields a super key, since uniqueness is not destroyed by adding more columns. For example, in a STUDENT relation with attributes (RollNo, Name, Email, Phone), the set {RollNo} is a super key because no two students share a roll number, and so are {RollNo, Name}, {RollNo, Email}, and {RollNo, Name, Email, Phone}, since all of these still guarantee uniqueness.

A candidate key is a minimal super key, meaning it is a super key from which no attribute can be removed without destroying the uniqueness property. In the STUDENT example, {RollNo} is a candidate key because it uniquely identifies tuples and contains no unnecessary attribute; {Email} could also be a candidate key if every student's email is guaranteed unique. However {RollNo, Name} is not a candidate key even though it is a super key, because removing Name still leaves {RollNo}, which is already sufficient - the extra attribute is redundant for the purpose of identification. A relation may have several candidate keys; in the STUDENT example both {RollNo} and {Email} could independently serve as candidate keys.

The primary key is the one candidate key that the database designer chooses as the principal, default means of identifying tuples of the relation for purposes such as indexing, referencing from foreign keys, and display. Once chosen, the primary key value is required to be unique and is not allowed to be NULL for any tuple (the entity integrity rule). Any candidate keys not selected as the primary key are called alternate keys. In the STUDENT example, the designer might choose RollNo as the primary key and treat Email as an alternate (unique) key.

The relationship among the three concepts is naturally a nested hierarchy: every candidate key is a super key (because it uniquely identifies tuples), but not every super key is a candidate key (because a super key may contain redundant attributes); and the primary key is simply one specific candidate key singled out by the designer. This can be visualized as concentric regions - the outer, largest region contains all super keys; inside it sits a smaller region containing only the candidate keys (the minimal super keys); and inside that, a single point marks the chosen primary key.

(b) E-R diagram for a car-insurance company

The car-insurance company scenario involves three entity types: Customer (attributes such as CustomerID, Name, Address), Car (attributes such as RegNo, Model, Year), and Accident (attributes such as AccidentID, Date, Location, DamageAmount). Each customer may own one or more cars, so there is a relationship called Owns between Customer and Car. Since one customer can own several cars but each car belongs to exactly one customer, the cardinality of Owns is one-to-many (1:N) from Customer to Car. Each car may be involved in zero to any number of accidents over its lifetime, so there is a second relationship, Involved-in, between Car and Accident, and its cardinality is one-to-many (1:N) from Car to Accident, since one car can appear in many accident records but each accident record pertains to one specific car.

In standard E-R notation, entities are drawn as rectangles, relationships as diamonds connecting the related entities, attributes as ovals attached to entities (often omitted in a simplified diagram for clarity), and the cardinality of each relationship is written next to the connecting line (1 near Customer and N near Car for Owns; 1 near Car and N near Accident for Involved-in).

This design captures the two natural constraints of the problem: a customer having multiple cars, and a car accumulating multiple accident records over time, while every car still traces back to exactly one owning customer and every accident record traces back to exactly one car.

It is worth also noting how each entity's own attributes would typically be modeled once the diagram is refined further. Customer would carry a primary-key attribute CustomerID together with descriptive attributes like Name, Address, and Phone; Car would carry RegNo as its primary key together with Model, Make, and Year-of-manufacture; Accident would carry AccidentID as its primary key together with Date, Location, and a description or DamageAmount attribute. Because Owns is a 1:N relationship, the foreign key CustomerID would be placed on the Car side (each car row stores the ID of the one customer who owns it), and because Involved-in is also 1:N, the foreign key RegNo would be placed on the Accident side (each accident row stores the ID of the one car it happened to). This is the standard rule for translating a 1:N E-R relationship into a relational schema - the foreign key always migrates to the table on the 'many' side of the relationship, so a single row on the 'one' side can be referenced by multiple rows on the 'many' side without any need for a separate junction table.

This scenario also illustrates why the two relationships are kept distinct rather than being merged into a single ternary relationship among Customer, Car, and Accident directly. An accident is fundamentally an event that happens to a specific car, independent of who currently owns that car (ownership could even change after the accident), so modeling Involved-in strictly between Car and Accident, and Owns strictly between Customer and Car, keeps each relationship's meaning precise and avoids anomalies that would arise from conflating vehicle ownership history with accident history in one combined relationship.

When this E-R diagram is mapped to relational tables, the design would typically produce three tables: Customer(CustomerID, Name, Address, Phone), Car(RegNo, Model, Make, Year, OwnerID) where OwnerID is a foreign key referencing Customer.CustomerID, and Accident(AccidentID, Date, Location, DamageAmount, CarRegNo) where CarRegNo is a foreign key referencing Car.RegNo. Every row in Car must have a non-null OwnerID (since every car must belong to some customer under the given business rule of one or more cars per customer), whereas an Accident row simply does not exist for a car that has never had an accident, which is exactly how the diagram's zero-to-many cardinality on the Involved-in relationship is realized: a car may correspond to zero rows in the Accident table, one row, or many rows, without requiring any special null-handling in the schema itself.