Project Definition: Designing an ER Diagram and/or Normalization Process
for a Student-Selected Case
Step 1: Case Study Selection:
Students can select any real-world or hypothetical case (e.g., hospital management, online
shopping system, university management, library management, etc.). Consider Example Case
study given below. The case must involve multiple entities and relationships, providing
sufficient complexity to demonstrate database design skills.
Step 2: Option Selection
You are required to choose option 1 or option 2 or both for your project.
Option 1:
The primary goal of this project is to enable students to apply knowledge of database design
by developing Entity-Relationship (ER) diagram for a case study of their choice. Also apply
normalization process (up to BCNF) on final ER Diagram for the logical design of the
database.
Objective of the project
1. Design the ER Diagram using following steps:
(i) Identify all entity types
(ii) Identify all attributes with its type for each entity type
(iii) Identify attributes which exhibits relationship between entity types in order to
identify relationship between entity types
(iv) Identify cardinalities (1:1, 1:M, M:1, M:N)
(v) Identify participation constraints (total/partial)
Note: Use diagramming tool of your choice to draw an ERD.
2. Normalization Process (Document it in a word file):
(i) Identify functional dependencies
(ii) Identify all candidate keys.
(iii) Keeping candidate keys, prime attributes and superkeys, closure decompose
the database schema into a series of normalized forms (1NF, 2NF, 3NF, and
BCNF as applicable).
(iv) Document each step of normalization by resolving anomalies.
Option 2
Students can opt for the normalization process on the designed database schema to ensure
data integrity and reduce redundancy. Design user interface using any programming language
that takes relational schemas and respective FDs as an input and generates step by step
normalization process as an output.
Objective of the project
1. Develop software-based project using following steps:
(i) Design Graphical User Interface using front end tools/framework or
programming language of your choice
(ii) GUI must accept relational schema as an input
(iii) It must accept functional dependencies as an input
(iv) Students are required to write backend program logic to find closure and all
possible candidate keys
(v) Student are required to write programming logic, that step by step converts
input relation schema into 1NF, 2NF, 3NF and BCNF.
(vi) Step by step output should be displayed in GUI.
Step 3: What to submit and how to submit:
For option 1: A comprehensive document (word or pdf file) that includes case study
description, the finalized ER diagram with explanations. They are required to submit step-by-
step normalization process with justifications.
Students must upload the final solution in a word/pdf file format (Name of the file:
IA_Your_Roll_Number) only.
For option 2: A comprehensive document (word or pdf file) that includes case study
description, Algorithm(s) for Closure, Candidate Keys identification, Normalization, and
project screenshots (containing all possible input output).
Students must upload the final solution in a word/pdf file format (Name of the file:
IA_Your_Roll_Number) only.
Learning Outcomes:
By completing this project, students will:
1. Understand the practical application of ER modeling in database design.
2. Develop skills in identifying and resolving database anomalies through normalization.
3. Enhance critical thinking in structuring databases for real-world scenarios.
4. Gain hands-on experience with tools used in database design.
Example Case Study: Bus Reservation System
Data Requirements for Online Bus Reservation System:
The Online Bus Reservation System requires a database that can handle the management of
bus operators, routes, runs, bookings, and seat availability.
The system should store details about the buses, including their unique identification, seating
capacity, type (e.g., AC or Non-AC, Seating or Sleeper), and the fare charged for different
routes. Operators managing these buses must also be identified, with their names, addresses,
and contact information maintained for record-keeping and communication.
The system should handle route information, which includes the sequence of stations, the
names of each station, and any sub-fares applicable for traveling between intermediate
stations. To ensure that seat availability is dynamically tracked, details such as the availability
status of seats on specific dates for routes are required.
Tracking the operational schedule is critical. Information about which buses are running on
specific dates, their assigned routes, and seat availability at different stations must be
recorded. To assist with bookings, a matrix-like structure for seat allocation and availability
along each route segment should be considered.
The database should also accommodate transaction details for bookings, including the
passenger's details such as name, contact number, email ID, and journey details (source
station, destination station, travel date, and fare). Moreover, the fare calculations must factor
in the type of bus and the distance travelled.
The system must also support features for handling updates to routes, changes to seat
availability, and cancellations, ensuring data consistency across all dependent records.
