1. A university system has Students, Courses, and Faculty.
Students enroll in Courses and Faculty teach Courses. Design
an ER model with proper representation using keys,
cardinalities and participation constraints. Why is selecting
the correct primary key important in real world databases?
ER Model Design
Entities & Attributes:
o Student: Student_ID (PK), Name, Email,
Date_of_Birth.
o Course: Course_ID (PK), Course_Title, Credits.
o Faculty: Faculty_ID (PK), Name, Department,
Specialization.
Relationships:
o Enrolls (Student <-> Course): This is a Many-to-Many
(M:N) relationship. A student can enroll in multiple
courses, and a single course can have many students.
o Teaches (Faculty <-> Course): This is a One-to-Many
(1:N) relationship. One faculty member can teach
multiple courses, but each specific course is assigned
to exactly one faculty member.
Cardinality & Participation:
o Student in Enrolls: Total Participation (Every student
must be enrolled in at least one course to be active in
the system).
o Course in Teaches: Total Participation (A course
cannot exist in the catalog without an assigned
instructor).
Importance of the Primary Key (PK)
In real-world databases, the Primary Key is the unique identifier
for a record. Selecting the correct one is critical because:
Data Uniqueness: It prevents "Data Duplication." Using a
name as a PK is dangerous because two students can have
the same name. A unique ID (Student_ID) ensures no
overlap.
Indexing & Speed: Databases use PKs to create "Indexes."
This allows the system to find a record among millions in
milliseconds.
Relational Integrity: PKs are used as "Foreign Keys" in
other tables. If a PK is unstable (like a phone number that
a user might change), it breaks all the links to that user
across the entire database.
2. Explain the three-level database architecture with a
diagram and example. If we add a new column to a table,
which level of architecture is affected and why?
The Three-Level Architecture (ANSI-SPARC)
This architecture is designed to separate the user's view of the
data from the physical way it is stored on the hardware.
1. External Level (View Level): The highest level. It describes
how individual users see the data. A student sees their
grades; a registrar sees the entire class list.
2. Conceptual Level (Logical Level): The middle level. It
describes what data is stored and the relationships
between them. This is the "Blueprint" or "Schema" of the
entire database.
3. Internal Level (Physical Level): The lowest level. It
describes how the data is physically stored on storage
devices (using blocks, clusters, or hashing).
Example: A Banking System
External: A customer sees their balance on a mobile app.
Conceptual: The bank's database schema shows that an
"Account" table contains "Account_No," "Balance," and
"Owner_ID."
Internal: The data is stored as encrypted blocks on a high-
speed SSD server.
Impact of a New Column
If a new column (e.g., Phone_Number) is added to a table, the
Conceptual Level is affected because the logical structure
(schema) of the entity has changed. However, due to Logical
Data Independence, the External Level (user views) remains
unaffected unless that specific view needs to display the new
column.
3. A company maintains employee data in multiple Excel files
across departments, leading to redundancy and inconsistency.
Explain why a DBMS is preferred over a file system in such a
scenario.
In an Excel-based system (File System), data is "siloed." A DBMS
(Database Management System) is preferred for the following
reasons:
Feature File System (Excel) DBMS (SQL/Oracle)
High. Same employee
Data Low. Data is stored once
info is typed in HR,
Redundancy and linked via keys.
Payroll, and IT files.
High. Updating a name Low. A single update
Data
in HR doesn't update reflects across all
Inconsistency
it in Payroll. departments.
Hard to share. Files Concurrent access.
Data Access are often "Locked for Many users can edit at
Editing." once.
Advanced. Can restrict
Basic. Usually just a
Security access to specific rows
file password.
or columns.
Integrity Manual. Users can Automatic. Constraints
Feature File System (Excel) DBMS (SQL/Oracle)
enter "N/A" in a ensure only numbers
"Salary" column. are entered.
The Solution: By moving to a DBMS, the company creates a
Single Version of Truth, ensuring that all departments are
working with the same, accurate data.
4. Using the ER Diagram you designed in question 1, convert it
into relational tables showing Primary Keys and Foreign Keys.
What common mistakes occur while converting an ER diagram
into relational schema, especially in M:N and higher-degree
relationships?
Relational Schema (Tables)
Student Table: (Student_ID [PK], Student_Name, Email)
Faculty Table: (Faculty_ID [PK], Faculty_Name, Dept)
Course Table: (Course_ID [PK], Title, Credits, Faculty_ID
[FK])
o Note: Faculty_ID is a Foreign Key referencing the
Faculty table.
Enrollment Table (Junction Table): (Student_ID [FK],
Course_ID [FK])
o Note: This table handles the Many-to-Many
relationship. The combination of both IDs forms a
Composite Primary Key.
Common Conversion Mistakes
Failure to Create a Junction Table: For M:N relationships,
beginners often try to add a list of Student IDs inside the
Course table. This violates First Normal Form (1NF) which
requires atomic values.
Wrong FK Placement: In 1:N relationships, developers
sometimes put the Foreign Key on the "One" side instead
of the "Many" side.
Redundancy in Relationships: Creating extra tables for
relationships that could have been handled by a simple
Foreign Key attribute in an existing table.
5. A company designed a database without proper ER
modelling. Later, they faced redundancy, difficulty in adding
new features, and frequent schema changes. Identify two
major conceptual design flaws that might have caused these
issues. If you were asked in an interview to redesign this
database, what key principles would you apply and why?
Two Major Conceptual Flaws
1. Lack of Normalization: The company likely used "Flat
Tables" where all information (Employee, Department,
and Project) was in one table. This causes Update
Anomalies (changing a department name requires 500
row updates).
2. Hard-Coding Relationships: Instead of using Foreign Keys,
they may have used text fields. For example, typing "IT
Dept" in the employee table instead of using a Dept_ID.
This makes it impossible to change department details
without breaking data links.
Redesign Principles (The "Interview" Answer)
Apply Normalization (up to 3NF): I would break down
large tables into smaller, logical entities to ensure that
each piece of data is stored in exactly one place.
Enforce Referential Integrity: I would use Primary and
Foreign Keys to ensure that no "orphaned" records exist
(e.g., a student cannot be enrolled in a course that doesn't
exist).
Data Independence: I would design the schema so that
the database can grow (adding new features or columns)
without requiring a complete rewrite of the existing
application code.