DBMS — Level 1 + Level 2 Combined Question Set¶

Topics covered: * DBMS vs RDBMS * Data models * Keys * Schema vs Instance * Data independence * ER model * ER → relational mapping * Functional dependencies * Attribute closure * Candidate keys * 1NF, 2NF, 3NF, BCNF, 4NF (MVD) * Denormalization * Anomalies

Part A — DBMS Fundamentals¶

Q1. DBMS vs RDBMS¶

Which statements are correct? A. Every RDBMS is a DBMS. B. Every DBMS is an RDBMS. C. RDBMS stores data using relations/tables. D. SQL itself is an RDBMS. E. MongoDB is a relational DBMS. Select all correct statements and explain each.

Q2. Data Models¶

Match each model with its characteristic: | Model | Characteristic | | --------------- | ------------------------------------ | | 1. Hierarchical | A. Tables/relations | | 2. Relational | B. Tree structure | | 3. Network | C. Graph-like interconnected records | | 4. Document | D. JSON-like documents | Also mention one major limitation of the hierarchical model.

Q3. Keys¶

Consider:

EMPLOYEE(Employee_ID, Email, Aadhaar_No, Name, Department)

Assume Employee_ID, Email, and Aadhaar_No uniquely identify an employee and none can be NULL. 1. List all candidate keys. 2. If Employee_ID is chosen as the primary key, what are the alternate keys? 3. Give an example of a superkey that is not a candidate key. 4. Is (Employee_ID, Email) a candidate key? Why/why not?

Q4. Composite Key¶

Consider:

ENROLLMENT(Student_ID, Course_ID, Grade)

A student can enroll in multiple courses, and a course can have multiple students. 1. What is the candidate key? 2. Is Student_ID a candidate key? 3. Is Course_ID a candidate key? 4. Is (Student_ID, Course_ID) a composite key? 5. Can a composite key also be a candidate key?

Q5. Foreign Key¶

Consider:

DEPARTMENT(Dept_ID PK, Dept_Name)
EMPLOYEE(Employee_ID PK, Name, Dept_ID FK)

1. Can multiple employees have the same Dept_ID? 2. Can Dept_ID in EMPLOYEE be NULL? 3. Does a foreign key have to be unique? 4. Can a foreign key reference a candidate key other than the primary key? 5. What integrity constraint does the foreign key primarily enforce?

Part B — Schema, Instance & Data Independence¶

Q6. Schema vs Instance¶

For each operation, identify whether it primarily changes the schema or the instance: 1. INSERT 2. UPDATE 3. DELETE 4. ALTER TABLE 5. Adding an index 6. Changing a column datatype Explain briefly.

Q7. Data Independence¶

Identify whether each scenario represents physical or logical data independence. A. Changing the file organization from heap files to indexed files without changing application queries. B. Adding a new attribute to the conceptual schema while keeping existing user views unaffected. C. Changing the disk storage structure without changing the logical schema. D. Splitting one logical relation into two relations while attempting to preserve external views.

Q8. Difficulty of Data Independence¶

Which is generally harder to achieve? Physical Data Independence OR Logical Data Independence? Explain why rather than just giving the answer.

Part C — ER Model¶

Q9. Entity Concepts¶

Differentiate between Entity, Entity Type, Entity Instance, and Entity Set. Use a Student example.

Q10. Attribute Types¶

Consider:

STUDENT
    Student_ID
    Name (First_Name, Last_Name)
    Address (City, State, PIN)
    Phone_Numbers (multiple)
    Age (calculated from DOB)

Classify each relevant attribute as: Simple, Composite, Single-valued, Multivalued, Derived, or Key.

Q11. Cardinality vs Participation¶

Consider:

Every employee works for exactly one department. Every department must have at least one employee.

Determine: 1. Cardinality between Employee and Department. 2. Employee participation. 3. Department participation. Also explain the difference between cardinality and participation.

Q12. Weak Entity¶

Consider:

EMPLOYEE(Employee_ID)
DEPENDENT(Dependent_Name, Age)

A dependent cannot exist without an employee, and Dependent_Name is unique only within an employee. 1. Which entity is weak? 2. What is the owner entity? 3. What is the partial key? 4. What will the primary key of the relational DEPENDENT table look like?

Part D — ER → Relational Mapping¶

Q13. 1:N Mapping¶

DEPARTMENT 1 -------- N EMPLOYEE Each employee belongs to exactly one department. Design the relational schema. Specify primary keys, foreign keys, which side receives the FK, and whether the FK should be nullable.

Q14. M:N Mapping¶

STUDENT M -------- N COURSE The relationship has Enrollment_Date and Grade. Convert this ER design into relational tables. Identify PKs, FKs, and where relationship attributes go.

Q15. 1:1 Mapping¶

PERSON 1 -------- 1 PASSPORT Every passport belongs to exactly one person, but some people may not have a passport. Design the schema. Where would you place the FK, and should it be UNIQUE? Explain.

Q16. Recursive Relationship¶

EMPLOYEE supervises EMPLOYEE Every employee may have at most one manager, while a manager can supervise multiple employees. Convert this into a relational schema.

Part E — Functional Dependencies¶

Q17. Identify FDs¶

Consider:

STUDENT(Student_ID, Student_Name, Branch, Course_ID, Course_Name, Grade)

Assume Student_ID and Course_ID are unique. A student can take many courses, and a course can have many students. Grade is specific to enrollment. Write all important functional dependencies.

Q18. Determinants¶

For each FD, identify the determinant: 1. Student_ID → Student_Name 2. Course_ID → Course_Name 3. (Student_ID, Course_ID) → Grade 4. (A, B) → C Then explain: What exactly does the word determinant mean?

Q19. Trivial or Non-Trivial?¶

Classify each FD as trivial, non-trivial, or completely non-trivial: 1. (A, B) → A 2. (A, B) → B 3. A → B 4. (A, B) → C 5. (A, B) → (A, B)

Q20. Armstrong's Axioms¶

Given A → B and B → C. Derive A → C using Armstrong's axioms. Name the axiom used.

Q21. Derived Dependencies¶

Given A → B and A → C, what can you conclude using the union rule? If A → BC, what can you conclude using decomposition?

Part F — Attribute Closure¶

Q22. Basic Closure¶

Given R(A, B, C, D) and FDs: A → B, B → C, C → D. Find A+. Is A a superkey? Is A a candidate key?

Q23. Closure¶

Given R(A, B, C, D, E) and FDs: A → B, B → C, AC → D, D → E. Find A+. Is A a candidate key?

Q24. Multiple Candidate Keys¶

Given R(A, B, C, D) and FDs: A → B, B → A, AC → D, D → C. Find all candidate keys. Show your closure calculations.

Q25. Candidate Key¶

Given R(A, B, C, D, E) and FDs: A → B, B → C, CD → E, E → A. Find all candidate keys.

Part G — Normalization: 1NF / 2NF / 3NF¶

Q26. 1NF¶

Consider: | Student_ID | Name | Phone | | ---------- | ----- | ---------- | | 101 | Rahul | 9876, 9123 | | 102 | Aman | 8888 | 1. Is this relation in 1NF? 2. If not, convert it into a proper relational design. 3. What would be the primary key of the phone relation?

Q27. 2NF Identification¶

Consider R(Student_ID, Course_ID, Student_Name, Course_Name, Grade) and FDs: Student_ID → Student_Name, Course_ID → Course_Name, (Student_ID, Course_ID) → Grade. 1. Find the candidate key. 2. Identify prime/non-prime attributes. 3. Identify all partial dependencies. 4. Is the relation in 2NF? If not, decompose it.

Q28. 2NF Shortcut¶

Consider a relation whose only candidate key is Employee_ID and it is already in 1NF. Can it violate 2NF? Explain precisely.

Q29. 3NF¶

Consider EMPLOYEE(Employee_ID, Employee_Name, Dept_ID, Dept_Name) and FDs: Employee_ID → Employee_Name, Employee_ID → Dept_ID, Dept_ID → Dept_Name. 1. Candidate key? Prime/non-prime attributes? 2. 2NF? 3NF? 3. Problematic dependency? 4. Decomposition into 3NF.

Part H — Formal 3NF¶

Q30. Formal 3NF Test¶

Consider R(A, B, C) with AB → C and C → B. Find all candidate keys. Determine whether the relation is in 3NF using the formal rule.

Part I — BCNF¶

Q31. BCNF Check¶

Consider R(A, B, C) with A → B and B → C. Find candidate keys. Determine whether R is in 3NF and BCNF. If not, identify the violating FD.

Q32. 3NF but Not BCNF (Important)¶

Consider TEACHING(Student, Course, Instructor) with (Student, Course) → Instructor and Instructor → Course. Find all candidate keys and prime attributes. Is it 3NF? BCNF? Decompose into BCNF.

Q33. BCNF Test¶

Given R(A, B, C, D) with AB → C, C → D, D → A. Find all candidate keys. Is it 1NF? 2NF? 3NF? BCNF? Give reasoning.

Part J — MVD and 4NF¶

Q34. Identify MVD¶

Consider STUDENT(Student, Hobby, Language). A student's hobbies are independent of their languages. If Rahul has Hobbies = {Cricket, Music} and Languages = {English, Hindi}: 1. What MVD exists? 2. Why do four rows appear? 3. Is Student a superkey? 4. Does this create a 4NF violation? 5. Decompose the relation.

Q35. FD vs MVD¶

Explain the difference between X → Y and X →→ Y using one example for each.

Part K — Anomalies¶

Q36. Identify the Anomaly (Insertion / Update / Deletion)¶

A. Cannot add a new course until at least one student enrolls in it. B. Changing an instructor's name requires modifying 500 rows. C. Deleting the last student enrolled in a course also removes the only stored info about that course.

Part L — Denormalization¶

Q37. Design Decision¶

You have CUSTOMER(Customer_ID, Customer_Name) and ORDER(Order_ID, Customer_ID, Amount). A dashboard constantly queries Order_ID, Customer_Name, Amount. The customer name changes rarely. Would denormalization be justified? Explain redundancy, benefits, problems, and what to measure.

Part M — Full Placement Problems¶

Q38. Complete Normalization Problem¶

Consider ENROLLMENT(Student_ID, Student_Name, Student_Email, Course_ID, Course_Name, Instructor_ID, Instructor_Name, Department_ID, Department_Name, Grade). Assume:

Student_ID → Student_Name, Student_Email
Course_ID → Course_Name, Instructor_ID
Instructor_ID → Instructor_Name, Department_ID
Department_ID → Department_Name
(Student_ID, Course_ID) → Grade

Find the candidate key, prime/non-prime attributes, partial and transitive dependencies. Check 1NF, 2NF, 3NF, BCNF. Decompose into appropriate relations and specify PKs/FKs.

Part N — Advanced Integrated Problem¶

Q39. Full FD + Closure + Normalization¶

Given R(A, B, C, D, E, F) with FDs: A → B, B → C, CD → E, E → F, F → D. Find A+, D+, E+. Find all candidate keys. Identify prime/non-prime attributes. Determine 2NF, 3NF, and BCNF, giving exact violating FDs.

Part O — Conceptual Interview Questions¶

Q40. Why can't we simply keep everything in one large table if storage is cheap?¶

Q41. Why is redundancy not always bad?¶

Q42. Why is 2NF mainly relevant when candidate keys are composite?¶

Q43. Why can a relation be in 3NF but not BCNF?¶

Q44. Why is BCNF stricter than 3NF?¶

Q45. Is every candidate key a superkey? Is every superkey a candidate key?¶

Q46. Can a foreign key be NULL? Under what conditions?¶

Q47. Can a table have multiple candidate keys? Multiple primary keys? Multiple foreign keys?¶

Q48. What is the difference between Composite key, Candidate key, Primary key, and Superkey?¶

Q49. Suppose a relation is in BCNF. Is it necessarily in 3NF? Why?¶

Q50. Suppose a relation is in 3NF. Is it necessarily in BCNF? Give a situation.¶

Final Challenge — Q51¶

Given UNIVERSITY(Student_ID, Student_Name, Dept_ID, Dept_Name, Course_ID, Course_Name, Instructor_ID, Instructor_Name, Hobby, Language, Grade). Rules:

Student_ID → Student_Name, Dept_ID
Dept_ID → Dept_Name
Course_ID → Course_Name, Instructor_ID
Instructor_ID → Instructor_Name
(Student_ID, Course_ID) → Grade
Student_ID →→ Hobby
Student_ID →→ Language

1. Candidate keys, prime/non-prime attributes. 2. Important FDs and MVDs. 3. Check 1NF, 2NF, 3NF, BCNF, 4NF. 4. Final decomposed schema (specify PKs/FKs). 5. What redundancy/anomalies were removed? Any denormalization potential?

---¶

DBMS Level 1 + Level 2 — Complete Answers¶

Part A — DBMS Fundamentals¶

Q	Answer
1	A, C (DBMS is broader; MongoDB is document-oriented; SQL is a language).
2	Hierarchical-B, Relational-A, Network-C, Document-D. Limitation of Hierarchical: M:N relationships are awkward.
3	CK: Employee_ID, Email, Aadhaar_No; alternate: Email, Aadhaar_No. Superkey: `(Employee_ID, Name)`. `(Employee_ID, Email)` is superkey, not candidate.
4	CK = `(Student_ID, Course_ID)`. Neither ID alone is CK. It is composite, and composite keys can be candidate keys.
5	Multiple FK values allowed; FK needn't be unique; NULL possible; referential integrity.

Part B — Schema, Instance & Data Independence¶

Q	Answer
6	INSERT/UPDATE/DELETE → instance; ALTER/index/datatype → schema/internal.
7	A Physical, B Logical, C Physical, D Logical.
8	Logical generally harder because changes can directly affect queries, views, and app assumptions.

Part C & D — ER Model and Mapping¶

Q	Answer
9	Entity=Object; Type=Category; Instance=Specific; Set=All instances.
10	Name/Address composite; Phone multivalued; Age derived; Student_ID key.
11	N:1 Employee→Department; both total under given wording.
12	Dependent weak; Employee owner; Dependent_Name partial key; composite PK `(Employee_ID, Dependent_Name)`.
13	FK on Employee/N-side. `NOT NULL`.
14	ENROLLMENT junction table with PK `(Student_ID, Course_ID)`.
15	FK in PASSPORT + UNIQUE + NOT NULL.
16	Manager_ID self-FK in EMPLOYEE table.

Part E & F — FDs and Closure¶

Q	Answer
17	`Student_ID→Name, Branch`; `Course_ID→Course_Name`; `Student+Course→Grade`.
18	LHS = determinant.
19	Trivial, trivial, completely non-trivial, completely non-trivial, trivial.
20	Transitivity.
21	A→BC; then A→B and A→C.
22	A+ = ABCD; A candidate key.
23	A+ = ABCDE; A candidate key.
24	Candidate Keys: AC, BC, AD, BD.
25	Candidate Keys: AD, BD, CD, DE.

Part G, H, I — Normalization¶

Q	Answer
26	Not 1NF; separate STUDENT_PHONE `(Student_ID, Phone)`.
27	Key=`(Student_ID,Course_ID)`; violates 2NF due to partial dependencies.
28	Cannot violate 2NF if 1NF + single-attribute key.
29	2NF yes, 3NF no (transitive: `Employee_ID → Dept_ID → Dept_Name`).
30	Keys AB, AC; 3NF yes (C is not superkey but B is prime); BCNF no.
31	Key A; 2NF yes; 3NF/BCNF no (B is not superkey).
32	Keys SC, SI; 3NF yes (Course is prime); BCNF no (Instructor not superkey).
33	Keys AB, BC, CD; 2NF yes; 3NF yes; BCNF no.

Part J, K, L — MVD, Anomalies, Denormalization¶

Q	Answer
34	`Student→→Hobby`; `Student→→Language`; violates 4NF (Student not superkey). Decompose to STUDENT_HOBBY, STUDENT_LANGUAGE.
35	FD = determined value; MVD = independent set of values.
36	A Insert, B Update, C Delete.
37	Denormalization potentially justified if measured workload supports it.

Part M & N — Full Placement Problems¶

Q	Answer
38	Key=`(Student_ID,Course_ID)`; decomposes into Student, Dept, Instructor, Course, Enrollment. Fails 2NF, 3NF, BCNF.
39	Keys AD, AE, AF; 2NF/3NF/BCNF all fail.

Part O — Conceptual Interview Questions¶

Q	Answer
40	One huge table causes redundancy/anomalies/maintenance problems.
41	Controlled redundancy can improve read performance.
42	Partial dependency requires composite key.
43	3NF allows prime RHS exception for non-superkey determinants.
44	BCNF requires every determinant to be a superkey.
45	Candidate ⊂ Superkey; every candidate is superkey, not vice versa.
46	FK can be NULL unless restricted.
47	Multiple CKs yes; one PK constraint; multiple FKs yes.
48	Superkey = unique; candidate = minimal; primary = chosen candidate; composite = multiple attributes.
49	Yes. `BCNF ⟹ 3NF`.
50	No; non-superkey determinant determining prime attribute breaks BCNF but not 3NF.

Q51. Final Boss¶

Keys: (Student_ID, Course_ID)
Normalization Status: Violates 2NF (partial), 3NF (transitive), BCNF (determinants not superkeys), 4NF (MVDs without superkey).
Decomposed Schema:
STUDENT(Student_ID PK, Student_Name, Dept_ID FK)
DEPARTMENT(Dept_ID PK, Dept_Name)
INSTRUCTOR(Instructor_ID PK, Instructor_Name)
COURSE(Course_ID PK, Course_Name, Instructor_ID FK)
ENROLLMENT(Student_ID FK, Course_ID FK, Grade) - PK is composite
STUDENT_HOBBY(Student_ID FK, Hobby) - PK is composite
STUDENT_LANGUAGE(Student_ID FK, Language) - PK is composite

Recommended Review Focus¶

For placements, prioritize calculating candidate keys and normal forms instead of just recognizing definitions: Q24 → Q25 → Q30 → Q32 → Q33 → Q38 → Q39 → Q51.