Scribd
Upload
3 views4 pages

SQL Project

The document outlines a SQL project focusing on data normalization for a booking system, detailing the steps taken to achieve first and second normal forms by identifying entities such as Passenger, Train, Station, and Booking. It also describes the components of an ER diagram, including entities, attributes, relationships, and integrity constraints, specifically in a hospital system context. Additionally, it addresses the resolution of a many-to-many relationship between Patients and Doctors using an intermediary Appointment entity.

Uploaded by

GOWRI NAIK
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views4 pages

SQL Project

The document outlines a SQL project focusing on data normalization for a booking system, detailing the steps taken to achieve first and second normal forms by identifying entities such as Passenger, Train, Station, and Booking. It also describes the components of an ER diagram, including entities, attributes, relationships, and integrity constraints, specifically in a hospital system context. Additionally, it addresses the resolution of a many-to-many relationship between Patients and Doctors using an intermediary Appointment entity.

Uploaded by

GOWRI NAIK
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Project

Name: GOWRI S NAIK


SNR: PES1UG22BC636

1)

Step 1: Understand the Data and Remove Redundancies (1NF & 2NF)
Raw Attributes Collected
• Passenger Info: Passenger Name, Age, Gender, Address, Mobile number, Email ID
• Booking Info: PNR Number, Date of Booking, Amount Paid, Mode of Payment,
Cancelled_PNR, Cancellation Date, Refund Amount
• Journey Info: Train Number, From Station Code, To Station Code, Journey Date, Seat
Number, Class, Train Type
• Train Info: Train Number, Train Type
• Station Info: Station Code, Station Name

Step 2: Apply 1NF and 2NF


We remove repeating groups and ensure partial dependencies are eliminated.

Step 3: Identify Entities and Normalize


Entities and Attributes
1. Passenger
o PassengerID (PK)
o Name
o Age
o Gender
o Address
o MobileNumber
o EmailID
2. Train
o TrainNumber (PK)
o TrainType
3. Station
o StationCode (PK)
o StationName (added)
4. Booking
o PNRNumber (PK)
o PassengerID (FK)
o TrainNumber (FK)
o FromStationCode (FK)
o ToStationCode (FK)
o JourneyDate
o DateOfBooking
o SeatNumber
o Class
o AmountPaid
o ModeOfPayment
o CancelledPNR
o CancellationDate
o RefundAmount

Step 4: Define Relationships


• One Passenger → Many Bookings (1:M)
• One Train → Many Bookings (1:M)
• One Station can be From or To station in many bookings (M:M as two FKs)
• Booking combines data from all entities with PNR as the primary key
2)
Components of ER Diagram
1. Entities - These are objects or concepts that can have data stored about them. In the
hospital system, key entities include:
o Patient
o Doctor
o Appointment

2. Attributes - Attributes are the data we store about each entity. For example:
o Patient: Patient_ID (PK), Name, Age, Gender, Contact_Number
o Doctor: Doctor_ID (PK), Name, Specialization, Contact_Number
o Appointment: Appointment_ID (PK), Date, Time, Reason
3. Relationships - These describe how entities interact with each other. For example:
o A Patient "books" an Appointment
o A Doctor "attends" an Appointment
4. Integrity Constraints
o Primary Keys (PK): Unique identifiers for each entity (e.g., Patient_ID,
Doctor_ID).
o Foreign Keys (FK): Attributes in one entity that reference primary keys in
another (e.g., Doctor_ID in Appointment refers to Doctor).
o Cardinality: Defines how many instances of one entity relate to instances of
another (one-to-many, many-to-many).

Potential Many-to-Many Relationship


• Initially, a many-to-many relationship may exist between Patient and Doctor, as a
patient can see multiple doctors, and a doctor can treat multiple patients.
Resolution
To resolve this, we use an intermediary entity: Appointment, which breaks the many-to-many
into two one-to-many relationships:
• Patient ⟶ (1:N) ⟶ Appointment
• Doctor ⟶ (1:N) ⟶ Appointment
The Appointment table holds foreign keys from both Patient and Doctor, linking them in a
normalized and scalable way.

You might also like