JAWAHARLAL NEHRU TECHNOLOGICAL UNIVERSITY HYDERABAD 
II Year B.Tech. CSE -II Sem   L  T/P/D  C 
                                              -   -/3/-     2 
(A40584) DATA BASE MANAGEMENT SYSTEMS LAB 

Objectives: 

  • This lab enables the students to practice the concept learnt in the subject DBMS by developing a database for an example company named "Roadway Travels" whose description is as follows. The student is expected to practice the designing, developing and querying a database in the context of example database "Roadway travels". Students are expected to use "Mysql" database.

Roadway Travels

"Roadway Travels" is in business since 1997 with several buses connecting different places in india. Its main office is located in Hydearabd.

The company wants to computerize its operations in the following areas:

  • Reservation and Ticketing
  • Cancellations

Reservation & Cancellation:

Reservations are directly handled by booking office. Reservations can be made 30 days in advance and tickets issued to passenger. One Passenger/person can book many tickets (to his/her family).

Cancellations are also directly handed at the bokking office.

In the process of computerization of Roadway Travels you have to design and develop a Database which consists the data of Buses, Passengers, Tickets, and Reservation and cancellation details. You should also develop query's usinf SQL to retrieve the data from database.

The above process involves many steps like 1. Analyzing the problem and identifying the Entities and Relationships, 2. E-R Model 3. Relational Model 4. Normalization 5. Creating the database 6. Querying. Students are suppossed to work on these steps week wise and finally create a complete "Database System" to Roadway Travels. Examples are given at every experiment for guidance to students.

Experiment 1: E-R Model

Analyze the carefully and come up with the entities in it. Identitfy what data has to be persisted in the database. This contains the entities, attributes etc.

Identify the primary keys for all the entities. Identify the others keys like candidate keys, partial keys, if any.

Example: Entities:

  1. BUS
  2. Ticket
  3. Passenger

Relationships:

  1. Reservation
  2. Cancellation

PRIMARY KEY ATTRIBUTES:

  1. Ticket ID (Ticket Entity)
  2. Passport ID (Passenger Entity)
  3. Bus_No (Bus Entity)

A part from the above mentioned entities you can identify more. The above mentioned are few.

Note: The student is required to submit a document by writing the Entities and keys to the lab teacher.

Experiment 2: Concept design with E-R Model

Relate the entities appropriately. Apply cardnalities for each relationship. Identify strong entities and weak entities (if any). Indicate the type of relationships (total / partial). Try to incorporate generalization, aggregation, specialization etc wherever required.

Example: E-R diagram for bus

Note: The student is required to submit a document by drawing the E-R Diagram to the lab teacher.

Experiment 3: Relational Model

Represent all the entities (Strong, Weak) in tabular fashion. Represent realtionships in a tabular fashion. There are different ways of representing relationships as tables based on the requirement. Different types of attributes (Composite, Multi-valued, and Derived) have different way of representation.

Example: The passenger tables look as below. This is an example. You can add more attributes based on E-R model. This is not a normalized table.

Passenger

Name Age Sex Address Ticket_id Passport ID
           
           
           

Note: The student is required to submit a document by Represent relationships in a tabular fashion to the lab teacher.

Experiment 4: Normalization

Database normalization is a technique for designing realtional database tables to minimize duplication of information and, in so doing, to safegaurd the database against certain types of logical or structural problems, namely data anormalies. For example, when multiple instances of a given piece of information occur in a table, the possibility exists that these instances will not be kept consistent when the data within tha table is updated, leading to a loass of data integrity. A table that is sufficiently normalized is less vulnerable to problems of this kind, because its structure reflects the basic assumptions for when multiple instances of the same information should be represented by a single instance only.

For the above table in the First normalization we can remove the multiple valued attribute Ticket_id and place it in another table along with the primary key of passenger.

First Normal Form: The above table can divided into two tables as shown below.

Passenger

Name Age Sex Address Passport ID
         
         
         

 

Passport ID Ticket_id
   

You can do the second and third normal forms if required. Any how Normalized tables are given at the end.

Experiment 5: Installation of MySQL and practice DDL commands

Installation of MySQL. In this week you will learn Creating databases, How to create tables, altering the database, droping tables and databases if not required. You will also try truncate, rename commands etc.

Example for creation of a normalized "Passenger" table.

CREATE TABLE Passenger(
    Passport_id INTEGER PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INTEGER NOT NULL,
    Sex CHAR,
    Address VARCHAR(50) NOT NULL
);

Similarly create all other tables.

Note: Detailed creation of tables is given at the end.

Experiment 6: Practicing DML commands

DML commandsare used for managing data within schema objects. Some examples:

  • SELECT - retrieve data from the database
  • INSERT - insert data into a table
  • UPDATE - updates exisitng data within a table
  • DELETE - deletes all records from a table, the space for the records remain

insert values into "Bus" table:

insert into Bus values (1234, 'hyderabad', 'tirupathi');

insert into Bus values(2345, 'hyderabad', 'banglore');

insert into Bus values(23, 'hyderabad', 'kolkata');

insert into Bus values(45, 'tirupathi', 'bangalore');

insert into Bus values(34, 'hyderabad', 'chennai');

insert values into "Passenger" table:

insert into Passenger values(1, 45, 'ramesh', 45, 'M', 'abc123');

insert into Passenger values(2, 78, 'geetha', 36, 'F', 'abc124');

insert into Passenger values(45, 90, 'ram', 30, 'M', 'abc12');

insert into Passenger values(67, 89, 'ravi', 50, 'M', 'abc14');

insert into Passenger values(56, 22, 'seetha', 32, 'F', 'abc55');

Few more Examples of DML commands:

SELECT * FROM Bus; (selects all the attributes and displays)
UPDATE Bus SET Bus_No = 1 WHERE Bus_No = 2;

Experiment 7: Querying

In this week you are going to practice queries(along with sub queries) using ANY, ALL, IN, EXISTS, NOT EXIST, UNION, INTERSECT, Constraints etc.

Practice the following Queries:

  1. Dispaly unique PNR_No of all passengers.
  2. Display all the names of male passengers.
  3. Display the ticket numbers and names of all the passengers.
  4. Find the ticket numbers of the passengers whose name start with 'r' and ends with  'h'.
  5. Find the names of passengers whose age is between 30 and 45.
  6. Display all the passengers names beginning with 'A'
  7. Display the sorted list of passengers names.

Experiment 8 and Experiment 9: Querying (continued...)

You are going to practice queries using Aggregate functions (COUNT, SUM, AVG, MAX,and MIN), GROUP BY, HAVING and Creation and droping of VIEWS.

  1. Write a Query to display the information present in the Passenger and cancellation tables. Hint: Use UNION Operator.
  2. Display the number of days in a week on which the 9W01 bus is available.
  3. Find number of tickets booked for each PNR_No using GROUP BY CLAUSE. Hint: Use GROUP BY on PNR_No.
  4. Find the distinct PNR numbers that are present.
  5. Find the number of tickets booked by a passenger where the number of seats is greater than 1. Hint: Use GROUP BY, WHERE and HAVING CLAUSES.
  6. Find the total number of cancelled setas.

Experiment 10: Triggers

In this week you are going to work on Triggers. Creation of insert trigger, delete trigger, update trigger. Practice triggers using the above database.

E.g: 

CREATE TRIGGER updatecheck BEFORE UPDATE ON passenger FOR EACH ROW
BEGIN
    IF NEW.TickentNO > 60 THEN 
        SET New.TickentNO = TicketNo;
    ELSE
        SET New.TicketNo = 0;
    END IF;
END

Experiment 11; Procedures

In this session you are going to learn Creation of stored procedure, Execution of procedure and modification of procedure. Practice procedures using the database.

E.g:

CREATE PROCEDURE myproc()
BEGIN
    SELECT     COUNT(Tickets)
    FROM     Ticket
    WHERE    age >= 40;
END;

Experiment 12: Cursors

In this week you need to do the following: Declare a cursor that defines a result set.

Open the cursor to establish the result set. Fetch the data into local variables as needed from the cursor, one row at a time. Close the cursor when done

CREATE PROCEDURE myproc(in_customer_id INT)
BEGIN
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(30);
    
    DECLARE c1 CURSOR FOR 
    SELECT stdid, stdFirstname FROM students WHERE stdid - in_customer_id;
    
    OPEN c1;
    FETCH c1 INTO v_id, v_name;
    CLOSE c1;
END;

Tables:

BUS

Bus No: VARCAHR : PK(primary key)

Source: VARCHAR

Destination: VARCHAR

Passenger

PPNO: VARCHAR(15) : PK

Name: VARCHAR(15)

Age: INT(4)

Sex: CHAR(10) : Male/Female

Address: VARCHAR(20)

Passenger_Tickets

PPNO: VARCHAR(15) : PK

Ticket_No: NUMERIC(9)

Reservation

PNR_No: NUMERIC(9) : FK

Journey_date: DATETIME(8)

No_of_seats: INT(8)

Address: VARCHRA(50)

Contact_No: NUMERIC(9) --> Should not less than 9 and Should not accept any other character other than interger

STATUS: CHAR(2) : Yes/No

Cancellation

PNR_No: NUMERIC(9) : FK

Journey_date: DATETIME(8)

No_of_seats: INT(8)

Address: VARCHRA(50)

Contact_No: NUMERIC(9) --> Should not less than 9 and Should not accept any other character other than interger

STATUS: CHAR(2) : Yes/No

Ticket

Ticket_No: NUMERIC(9) : FK

Journey_date: DATETIME(8)

Age: INT(4)

Sex: CHAR(10) : Male/Female

Source: VARCHAR

Destination: VARCHAR

Dep_time: VARCHAR

RFERENCE BOOKS:

  1. Introduction to SQL, Rick F.vander Lans, Pearson education.
  2. Oracle PL/SQL, B.Rosenzweig and E.Silvestrova, Pearson education.
  3. Oracle PL/SQL Programming, Steven Feuerstein, SPD.
  4. SQL & PL/SQL for Oracle 10g, Black Book, Dr. P.S. Deshpande, Dream Tech.
  5. Oracle Database 11g PL/SQL Programming, M.Mc Laughlin, TMH.
  6. SQL Fundamentals, J.J. Patrick, Pearson Education.

Outcomes:

  • Ability to design and implement a database schema for given problem.
  • Be capable to Design and build a GUI application.
  • Apply the normalization techniques for development of application software to realistic problems.
  • Ability to formulate queries using SQL DML/DDL/DCL commands.
  • Created
    Jan 20, 2015
  • Updated
    Jan 22, 2015
  • Views
    11,293