Scribd Logo
Upload

Welcome to Scribd!

  • 53 views

    Assignment 1

    Uploaded by

    Himanshu
    The document describes the implementation of tables and constraints for a train reservation system database with tables for trains and passengers. It includes creating the tables, adding primary keys and foreign keys, inserting sample data, and writing queries to retrieve data from the tables such as listing trains and passengers, counting passengers by train or gender, and more. The code provides the full implementation for setting up the database structure and sample data.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Assignment 1

    Uploaded by

    Himanshu
    53 views11 pages
    The document describes the implementation of tables and constraints for a train reservation system database with tables for trains and passengers. It includes creating the tables, adding primary keys and foreign keys, inserting sample data, and writing queries to retrieve data from the tables such as listing trains and passengers, counting passengers by train or gender, and more. The code provides the full implementation for setting up the database structure and sample data.

    Original Title

    Assignment-1

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes the implementation of tables and constraints for a train reservation system database with tables for trains and passengers. It includes creating the tables, adding primary keys and foreign keys, inserting sample data, and writing queries to retrieve data from the tables such as listing trains and passengers, counting passengers by train or gender, and more. The code provides the full implementation for setting up the database structure and sample data.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    53 views11 pages

    Assignment 1

    Uploaded by

    Himanshu
    The document describes the implementation of tables and constraints for a train reservation system database with tables for trains and passengers. It includes creating the tables, adding primary keys and foreign keys, inserting sample data, and writing queries to retrieve data from the tables such as listing trains and passengers, counting passengers by train or gender, and more. The code provides the full implementation for setting up the database structure and sample data.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 11

    Name –

    Registration id –
    The problem statement-
    Sample Database: Southern Railways
    I. Create tables for the following requirements
    Train:
    Number, name, source, destination, start_time, reach_time
    Passenger:
    PNR No, Serial no., Name, Sex, Address, Age, Date of
    Journey,
    Status, kind of seat, seat no, Train number
    II. Insert necessary values into the tables.
    III. Constraints
    1. Add a primary key constraint to train, Passenger.
    2. Add a referential key constraint to passenger.
    3. Add a check constraint to insert source and destination
    in 3 letters
    4. Add a check constraint to enter a valid kind of seat
    while a
    Passenger record is added for a train.
    IV. Write queries for the following:
    1. List all train details.
    2. List all passenger details.
    3. Give a list of trains in ascending order of number.
    4. Find out the number of passengers booked for a
    particular
    Train.
    5. List the number of waiting lists in a train “x”.
    6. List the number of female passengers who have booked
    for
    trains (train name wise).

    The implementation code-


    ->create table Train
    (Number_t int not null,
    Names char(100),
    Source_t char(100),
    Destination varchar(100),
    Start_time decimal(4,2),
    Reach_time decimal(4,2));

    create table Passenger


    (PNR_no int PRIMARY KEY not null,
    Serial_num number,
    Names char(100) not null,
    Sex char(10),
    Age number not null,
    Date_of_journey date);

    alter table Passenger


    add Status varchar(20) default 'Not registered';

    alter table Passenger


    add Kind_of_seat varchar(20);

    alter table Passenger


    add Seat_no number;

    alter table Passenger


    add Train_number int;

    insert into Train


    values(15002, 'CST_Chennai special', 'Kanpur', 'Trichy', 7.40,
    21.50);

    insert into Train


    values(21575, 'ANR_Amaravati local', 'Vijyawada', 'Warangal',
    15.25, 20.00);

    insert into Train


    values(15112, 'CGT_Chitagong special memu', 'Aizol',
    'Chitagong', 20.40, 16.00);

    insert into Train


    values(18025, 'WRT_Bengal super fast', 'Bhopal',
    'Bhubaneshwar', 7.00, 21.00);
    insert into Train
    values(21220, 'INT_Indore memu local', 'Delhi', 'Indore', 5.40,
    22.30;

    insert into Train


    values(20032, 'KRB_Korba local memu', 'Korba', 'Mahasamund',
    4.20, 21.30);

    insert into Train


    values(21220, 'INT_Indore memu local', 'Delhi', 'Indore', 5.40,
    22.30);

    alter table Train


    add number_t int Primary key;

    alter table Passenger


    drop primary key;

    alter table Train


    add ID_of_train number primary key;

    alter table Passenger


    add ID_pass number primary key;
    alter table Passenger
    add constraint New_slot
    foreign key(Train_number) references Train(Number_t);

    alter table Train


    add check(length(Source_t) = 3 AND length(destination) = 3);

    alter table Train


    add check(length(Source_t) = 3);

    select length(source_t) as ST
    from Train;

    alter table Train


    add check(ST = 3);

    select length(source_t) as ST
    from Train
    check(ST = 3);

    alter table Passenger


    add check(kind_of_seat != null);
    select * from Train;

    select * from Passenger;

    select * from Train


    order by number_t asc;

    insert into Passenger


    values(1230456, 01, 'Rahul', 'Male', 25, 2022-02-15, 'Confirmed',
    'Side', 48, 21220, 501);

    insert into Passenger


    values(123640, 02, 'Mrunal', 'Female', 27, 2022-10-01,
    'Confirmed', 'Upper', 102, 15112, 423);

    insert into Passenger


    values(105364, 03, 'Aisha', 'Feamle', 22, 2022-01-15,
    'Confirmed', 'Middle', 89, 18025, 223);

    insert into Passenger


    values(1230456, 01, 'Rahul', 'Male', 25, '2022-02-15',
    'Confirmed', 'Side', 48, 21220, 501);

    insert into Passenger


    values(123640, 02, 'Mrunal', 'Female', 27, '2022-10-01',
    'Confirmed', 'Upper', 102, 15112, 423);

    insert into Passenger


    values(105364, 03, 'Aisha', 'Feamle', 22, '2022-01-15',
    'Confirmed', 'Middle', 89, 18025, 223);

    insert into Passenger


    values(1230456, 01, 'Rahul', 'Male', 25, to_date('2022-02-15',
    'yyyy-mm-dd'), 'Confirmed', 'Side', 48, 21220, 501);

    insert into Passenger


    values(123640, 02, 'Mrunal', 'Female', 27, to_date('2022-10-01',
    'yyyy-mm-dd'), 'Confirmed', 'Upper', 102, 15112, 423);

    insert into Passenger


    values(105364, 03, 'Aisha', 'Feamle', 22, to_date('2022-01-15',
    'yyyy-mm-dd'), 'Confirmed', 'Middle', 89, 18025, 223);

    select count(*) from Passenger


    where train_number = 15112;

    select count(*) from Passenger


    where status = 'Waiting';
    select count(*) from Passenger
    where sex = 'female';

    select count(train_number) from Passenger


    where sex = 'female';
    Output-

    You might also like