Unit 2: Database design
Database Management Systems-I notes · PTU syllabus (UGCC2512)
On this page
Unit summary
Good database design starts before any table is created. This unit covers the keys that identify and link records, integrity constraints, and the Entity-Relationship (ER) model used to design databases visually, ending with an introduction to the relational model.
After this unit you can
- Explain all types of keys with examples
- Explain domain, entity and referential integrity constraints
- Draw ER diagrams with entities, attributes and relationships, including weak entities
- Use extended ER features and reduce an ER diagram to tables
PTU syllabus topics
- Keys — primary
- candidate
- super
- foreign
- composite
- alternate
- unique
- surrogate
- constraints
- Entity-Relationship model
- entities
- attributes
- relationships
- ER diagrams
- weak entity sets
- extended ER features
- introduction to the relational model
Super key
Any attribute set that uniquely identifies a row
Candidate key
A minimal super key
Primary key
The chosen candidate key; no nulls
Foreign key
Refers to another table's primary key
Composite key
A key made of two or more attributes
Topic 1
Keys
- Super key
- Any set of attributes that uniquely identifies a row
- Candidate key
- A minimal super key
- Primary key
- The candidate key chosen to identify rows; unique and not null
- Alternate key
- Candidate keys not chosen as primary
- Foreign key
- An attribute referring to the primary key of another table
- Composite key
- A key made of two or more attributes
- Unique key
- Must be unique but may allow one null
- Surrogate key
- An artificial key such as an auto-increment ID
Example
In STUDENT(RollNo, AadhaarNo, Name, Email): {RollNo}, {AadhaarNo} and {Email} are candidate keys; RollNo is chosen as the primary key; AadhaarNo and Email are alternate keys; {RollNo, Name} is a super key but not a candidate key.
Topic 2
Constraints
- Domain constraint: each value must come from the attribute's allowed domain (e.g. age between 17 and 60).
- Entity integrity: a primary key cannot be NULL.
- Referential integrity: a foreign key value must match an existing primary key value or be NULL.
- Key constraint: primary key values must be unique.
- Others: NOT NULL, UNIQUE, CHECK, DEFAULT.
Topic 3
The Entity-Relationship model
The ER model (Peter Chen, 1976) describes data as entities, their attributes and the relationships between them.
| ER symbol | Represents |
|---|---|
| Rectangle | Entity set |
| Double rectangle | Weak entity set |
| Ellipse | Attribute |
| Underlined ellipse | Key attribute |
| Double ellipse | Multivalued attribute |
| Dashed ellipse | Derived attribute |
| Diamond | Relationship |
| Double diamond | Identifying relationship |
Types of attributes: simple and composite (name → first, last), single-valued and multivalued (phone numbers), stored and derived (age from date of birth). Cardinality of relationships: one-to-one (1:1), one-to-many (1:N), many-to-one (N:1) and many-to-many (M:N). Participation: total (every entity participates — double line) or partial.
Topic 4
Weak entity sets and extended ER features
A weak entity set has no primary key of its own; it depends on a strong (owner) entity and is identified by a partial key plus the owner's key. Example: DEPENDENT of an EMPLOYEE.
Specialisation
Top-down: EMPLOYEE into MANAGER and CLERK
Generalisation
Bottom-up: CAR and BIKE into VEHICLE
Aggregation
Treating a relationship as an entity
Inheritance
Lower-level entities inherit attributes of higher ones
Topic 5
Introduction to the relational model and ER-to-table reduction
In the relational model, data is stored in relations (tables). A row is a tuple, a column is an attribute, the set of allowed values is a domain, the number of columns is the degree and the number of rows is the cardinality.
- 1Each strong entity becomes a table
Key attribute is the primary key
- 2Weak entity
Table with owner's key + partial key
- 31:N relationship
Put the key of the "1" side as a foreign key in the "N" side
- 4M:N relationship
New table with both keys
- 5Multivalued attribute
Separate table
Key terms
- Primary key
- The chosen unique, non-null identifier of a row
- Foreign key
- An attribute linking to another table's primary key
- Entity
- A real-world object about which data is stored
- Weak entity
- An entity that depends on another for identification
- Cardinality
- The number of entities that can take part in a relationship
Quick revision
- Super ⊇ candidate ⊇ primary; foreign key links tables.
- Entity integrity: PK not null; referential integrity: FK must match.
- ER: rectangle entity, ellipse attribute, diamond relationship.
- M:N relationships become separate tables.
Important exam questions
Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).
Short-answer questions
- Q1.Differentiate between a candidate key and a primary key.
- Q2.What is a foreign key?
- Q3.State the entity integrity and referential integrity rules.
- Q4.What is a weak entity set?
- Q5.Differentiate between specialisation and generalisation.
- Q6.What is a derived attribute?
Long-answer questions
- Q1.Explain the different types of keys with suitable examples.
- Q2.Draw an ER diagram for a college database with students, courses and faculty, showing cardinalities.
- Q3.Explain extended ER features with diagrams.
- Q4.Explain how an ER diagram is reduced to relational tables.
Stuck on this unit?
Message SBS on WhatsApp for help with Database Management Systems-I, or to ask about studying BCA at Synetic.