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: <u>Student_ID</u> (PK), Name,
Email.
o Course: <u>Course_ID</u> (PK), Title,
Credits.
o Faculty: <u>Faculty_ID</u> (PK), Name,
Department.
 Relationships & Cardinality:
o Enrolls_In (Student <-> Course): Many-to-
Many (M:N). A student can take multiple
courses, and a course can have multiple
students.
o Teaches (Faculty <-> Course): One-to-Many
(1:N). One faculty member can teach multiple
courses, but each course is taught by only one
faculty member.
 Participation Constraints:
o Total Participation for Course in the "Teaches"
relationship (Every course must have an
assigned faculty).
o Partial Participation for Faculty (A faculty
member might be on research leave and not
teaching).

Importance of Primary Keys (PK):

In real-world databases, the PK is the "anchor" of data
integrity.

 Entity Integrity: It ensures no two rows are

identical, preventing data corruption.
 Indexing & Performance: Databases use PKs to
create indexes. Without a proper numeric PK, search
queries across millions of records would be
unacceptably slow.
 Relationship Stability: Using a stable PK (like an
ID) instead of a volatile one (like a Phone Number)
prevents "orphaned records" when user data
changes.
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) separates the

user's view from the physical storage.

1. External Level (View Level): The highest level;

describes how individual users see the data (e.g., a
student viewing only their transcript).
2. Conceptual Level (Logical Level): Describes what
data is stored and the relationships between them
(e.g., the entire table schema for Students and
Grades).
3. Internal Level (Physical Level): The lowest level;
describes how data is actually stored on the hard
drive (e.g., using B-trees or hashing).

Example: In a banking system, a Customer sees their

balance (External), the DBA manages the table structures
(Conceptual), and the server stores the bits and bytes on
a disk (Internal).

Impact of adding a new column:

Adding a column affects the Conceptual Level. Because

of Logical Data Independence, the External Level (user
views) remains unaffected. Users don't need to change
how they interact with their specific views just because
the underlying table structure grew.
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.

A DBMS is superior for the following reasons:

 Reduced Redundancy: Unlike Excel files where

"Employee A" might exist in five different files, a
DBMS stores data in one place, saving storage and
effort.
 Data Consistency: If an employee changes their
name, it is updated once in the DBMS. In a file
system, you might update the HR file but forget the
Payroll file, leading to conflicting data.
 Concurrent Access: Multiple departments can read
and write to the database simultaneously without
"File is locked for editing" errors.
 Data Integrity: You can set rules (e.g., "Age must
be $> 18$") that the system enforces automatically.
 Security: DBMS allows granular permissions (e.g.,
Payroll can see salaries, but Marketing cannot),
which is difficult to manage with shared Excel files.
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:

1. Student (<u>Student_ID</u>, Name, Email)

2. Faculty (<u>Faculty_ID</u>, Name, Dept)
3. Course (<u>Course_ID</u>, Title, Credits,
Faculty_ID) — Faculty_ID is a Foreign Key (FK).
4. Enrollment (<u>Student_ID</u>,
<u>Course_ID</u>) — A Junction Table where both
columns act as a Composite PK and individual FKs.

Common Mistakes:

 Missing Junction Tables: Attempting to force M:N

relationships into a single table by using comma-
separated values, which violates First Normal Form
(1NF).
 Redundant Foreign Keys: In 1:N relationships,
placing the FK on the "One" side instead of the
"Many" side.
 Ignoring Participation: Failing to mark FKs as NOT
NULL when the ER diagram specifies Total
Participation.
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 Design Flaws:

1. Lack of Normalization: Storing repetitive data (like

Department Address) in every Employee record
instead of a separate Department table. This causes
Update Anomalies.
2. Weak Entity Identification: Using "Names" or
"Descriptions" as keys instead of unique surrogate
IDs, leading to broken relationships when
descriptions are edited.

Redesign Principles:

 Requirement Gathering: Clearly define the

"Business Rules" before touching the schema.
 Normalization (to 3NF): To ensure each piece of
data is stored in exactly one place.
 Standardized Naming Conventions: To make the
schema intuitive for future developers.
 Enforcement of Constraints: Use Primary Keys,
Foreign Keys, and Unique constraints to automate
data validation at the database level.