Walchand College Of Engineering,
Sangli
Name: Varad Upadhye
PRN:245100117
Subject: DataBase Engineering Lab
Class: S.Y CSE(Aided)
Batch: CSA-5
ASSIGNMENT-3
Implement the database schema using SQL Data Definition Language (DDL) commands.
To implement a given conceptual or logical database design by creating a relational
database
schema using SQL Data Definition Language (DDL) commands. You are required to
translate
the designed ER diagram ( as part of Assignment No 2) into SQL tables while ensuring
data
integrity, consistency, and correctness through appropriate constraints.
The implementation must strictly follow standard relational database principles and SQL
syntax.
You must perform the following tasks:
1. Table Creation
2. Key Constraints
3. Domain and Attribute Constraints
4. Relationship Handling
5. Special Design Considerations (if any)
6. Schema Validation
Walchand College Of Engineering,
Sangli
CREATE DATABASE railway;
USE railway;
CREATE TABLE Users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
f_name VARCHAR(50) NOT NULL,
l_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
contact VARCHAR(15) NOT NULL,
DOB DATE NOT NULL,
gender VARCHAR(10) CHECK (gender IN ('Male','Female','Other')),
PWD BOOLEAN DEFAULT FALSE
);
CREATE TABLE Staff (
Walchand College Of Engineering,
Sangli
staff_id INT PRIMARY KEY AUTO_INCREMENT,
role VARCHAR(50)
);
CREATE TABLE Train (
train_no INT PRIMARY KEY,
train_name VARCHAR(100) NOT NULL,
train_type VARCHAR(50) NOT NULL
);
CREATE TABLE Class (
class_id INT PRIMARY KEY AUTO_INCREMENT,
train_no INT,
class_type VARCHAR(20) CHECK (class_type IN ('General','Sleeper','1AC','2AC','3AC')),
fare DECIMAL(8,2) CHECK (fare > 0),
FOREIGN KEY (train_no)
REFERENCES Train(train_no)
ON DELETE CASCADE
);
CREATE TABLE Route (
station_no INT PRIMARY KEY,
station_name VARCHAR(100) NOT NULL,
train_no INT,
FOREIGN KEY (train_no)
REFERENCES Train(train_no)
ON DELETE CASCADE
);
CREATE TABLE Staff_Train (
staff_id INT,
train_no INT,
PRIMARY KEY (staff_id, train_no),
FOREIGN KEY (staff_id) REFERENCES Staff(staff_id)
ON DELETE CASCADE,
FOREIGN KEY (train_no) REFERENCES Train(train_no)
Walchand College Of Engineering,
Sangli
ON DELETE CASCADE
);
CREATE TABLE Ticket (
PNR BIGINT PRIMARY KEY,
user_id INT,
train_no INT,
passenger_name VARCHAR(100) NOT NULL,
startingS VARCHAR(100) NOT NULL,
ending VARCHAR(100) NOT NULL,
distance INT CHECK (distance > 0),
date DATE NOT NULL,
seat_no VARCHAR(10),
FOREIGN KEY (user_id) REFERENCES User(user_id)
ON DELETE CASCADE,
FOREIGN KEY (train_no) REFERENCES Train(train_no)
ON DELETE CASCADE
);
CREATE TABLE Payment (
transaction_id INT PRIMARY KEY AUTO_INCREMENT,
PNR BIGINT UNIQUE,
amount DECIMAL(10,2) CHECK (amount > 0),
status VARCHAR(20) DEFAULT 'Pending'
CHECK (status IN ('Pending','Completed','Failed','Refunded')),
refund DECIMAL(10,2) DEFAULT 0,
FOREIGN KEY (PNR) REFERENCES Ticket(PNR)
ON DELETE CASCADE
);