ER model and ER diagram interview questions, with answers

The questions, with answers

1. 1.What are entities, attributes and relationships, and how are they drawn?

   An entity is a thing the business keeps data about — Student, Course, Employee — and an entity set is all of its instances. An attribute is a property of an entity, such as roll_no or date_of_birth; the attribute that identifies an instance is the key attribute. A relationship is an association between entities — a Student enrols in a Course — and it can carry attributes of its own, such as the grade. In classic Chen notation entities are rectangles, attributes are ovals attached to their entity (the key attribute underlined), and relationships are diamonds joined by lines to the entities they connect. Crow's-foot notation, common in tools, folds the attributes into the rectangle and draws cardinality as line endings.

2. 2.What is the difference between a strong entity and a weak entity, and how is a weak entity mapped to a table?

   A strong entity has a key of its own. A weak entity cannot be identified by its attributes alone: a Dependent of an Employee is identified by its name only within that employee, so it has a partial key (name) and depends on an identifying relationship to the strong entity. Chen draws the weak entity with a double rectangle and the identifying relationship with a double diamond. Mapping: the weak entity becomes a table whose primary key is the owner's primary key plus the partial key — Dependent(emp_id, dep_name, ...) with emp_id also a foreign key to Employee, typically ON DELETE CASCADE, because a dependent has no meaning once the employee row is gone.

3. 3.What do cardinality and participation constraints mean?

   Cardinality says how many instances of one entity can relate to one instance of the other: one-to-one (each employee has one parking space), one-to-many (a department has many employees, each employee one department), many-to-many (students and courses). Participation says whether every instance must take part: total participation (every employee must belong to a department, drawn as a double line) or partial (a department may have no manager yet). The two are independent — a one-to-many relationship can be total on one side and partial on the other. Crow's-foot symbols encode both on each line end, and textbooks often write the same information as a (min, max) pair beside each entity.

4. 4.Where does the foreign key go when you map a one-to-many or a one-to-one relationship?

   For one-to-many, the foreign key goes on the many side: each Employee row holds the dept_id of its one Department. Putting it on the one side would need a Department row to hold a list of employees, which a column cannot do. For one-to-one, it can go on either side, so choose the side with total participation — if every parking space must belong to an employee but not every employee has a space, put emp_id on ParkingSpace and make it NOT NULL and UNIQUE; the UNIQUE is what enforces the one-to-one. If the relationship has attributes of its own, they travel with the foreign key onto the same table.

5. 5.How is a many-to-many relationship represented, and where do its attributes live?

   With a third table, because a single row cannot hold a set of references in either direction. Students and Courses become an Enrollment table with student_id and course_id, each a foreign key, and the pair as the composite primary key so a student cannot enrol twice in the same course. Any attribute of the relationship itself — grade, enrolment date — belongs on this junction table, not on Student or Course, because it describes the pair. This is the mapping that most often goes wrong in interviews: putting course_id on Student (which limits a student to one course) or putting grade on Course (which gives every student the same grade).

   CREATE TABLE Enrollment (
       student_id INT NOT NULL REFERENCES Students(student_id),
       course_id  INT NOT NULL REFERENCES Courses(course_id),
       grade      CHAR(2),
       PRIMARY KEY (student_id, course_id)
   );

6. 6.What are simple, composite, multivalued and derived attributes, and how does each become a column?

   A simple attribute is atomic and becomes one column. A composite attribute is made of parts — an address with street, city and pin — and is usually mapped as one column per part, so the parts can be queried separately. A multivalued attribute can hold several values for one instance — a person's phone numbers — and cannot be one column in a normalised table; it becomes a separate table with the owner's key plus the value, which is also what first normal form requires. A derived attribute — age from date_of_birth — is computed rather than stored, either in a view or a generated column, because storing it would go stale. Chen draws composites as ovals of ovals, multivalued as a double oval, and derived as a dashed oval.

7. 7.What is generalisation or specialisation, and how do you map an ISA hierarchy to tables?

   Specialisation splits an entity into subtypes with extra attributes — Employee into Engineer and Manager; generalisation is the same picture built bottom-up. Three mappings, each with a trade-off. One table per class: Employee holds the shared columns and each subtype table holds the extras plus emp_id as both primary and foreign key — no nulls, but a join to assemble a full row. One table per subtype only: each holds all columns, shared and specific — no join, but shared data is duplicated and a query over all employees needs a UNION. A single table with a discriminator column and nullable subtype columns — fastest to query, but full of nulls and unable to enforce that a manager has a bonus. Say which you would pick and why: disjoint subtypes with few extras favour the single table; heavy subtypes favour one table per class.

8. 8.What is a ternary relationship, and can it always be split into binary ones?

   A relationship among three entities at once — a Supplier supplies a Part for a Project. It cannot always be replaced by three binary relationships, and this is the point of the question: knowing that S supplies part P, S works on project J and part P is used in J does not tell you whether S supplies that part to that project. The single fact needs one row with all three keys, so a ternary relationship maps to a table with three foreign keys and, usually, their combination as the primary key. It can be decomposed only when a functional dependency makes one entity determined by the other two. Aggregation — treating a relationship as an entity so it can take part in another relationship — is the related idea the interviewer may follow up with.

How the diagnostic asks it

One question from the DBMS bank, exactly as a sitting would show it. The bank has 5 on er model and 60 across DBMS.