Database Management Systems Lab Detailed Syllabus for B.Tech second year second sem is covered here. This gives the details about credits, number of hours and other details along with reference books for the course.
The detailed syllabus for Database Management Systems Lab B.Tech 2016-2017 (R16) second year second sem is as follows.
B.Tech. II Year II Sem. L/T/P/C
Course Code: CS407ES 0/0/3/2
Course Objectives: This lab enables the students to practice the concepts 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.
Course Outcomes:
- Ability to design and implement a database schema for given problem.
- Apply the normalization techniques for development of application software to realistic problems.
- Ability to formulate queries using SQL DML/DDL/DCL commands.
Roadway Travels: “Roadway Travels” is in business since 1997 with several buses
connecting different places in India. Its main office is located in Hyderabad.
The company wants to computerize its operations in the following areas:
- Reservations and Ticketing
- Cancellations
Reservations & 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 booking 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 using SQL to retrieve the data from the 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 supposed 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 problem carefully and come up with the entities in it. Identify 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 other 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) Apart 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 cardinalities 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 relationships in a tabular fashion. There are different ways of representing relationships as tables based on the cardinality. Represent attributes as columns in tables or 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 your E-R model. This is not a normalized table. Passenger
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 relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies. 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 the table is updated, leading to a loss 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 multi valued attribute Ticket_id and place it in another table along with the primary key of passenger.
First Normal Form: The above table can be divided into two tables as shown below. Passenger
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 practicing DDL commands Installation of MySql. In this week you will learn Creating databases, How to create tables, altering the database, dropping 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 commands are used to for managing data within schema objects. Some examples:
- SELECT – retrieve data from the a database
- INSERT – insert data into a table
- UPDATE – updates existing data within a table
- DELETE – deletes all records from a table, the space for the records remain
Inserting values into “Bus” table:
Insert into Bus values (1234,’hyderabad’, ‘tirupathi’);
Insert into Bus values (2345,’hyderabd’,’Banglore’);
Insert into Bus values (23,’hyderabd’,’Kolkata’);
Insert into Bus values (45,’Tirupathi,’Banglore’);
Insert into Bus values (34,’hyderabd’,’Chennai’);
Inserting 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 display)
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 EXISTS, UNION, INTERSECT, Constraints etc.
Practice the following Queries:
- Display unique PNR_no of all passengers.
- Display all the names of male passengers.
- Display the ticket numbers and names of all the passengers.
- Find the ticket numbers of the passengers whose name start with ‘r’ and ends with ‘h’.
- Find the names of passengers whose age is between 30 and 45.
- Display all the passengers names beginning with ‘A’
- 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, and MAX and MIN), GROUP BY, HAVING and Creation and dropping of Views.
- Write a Query to display the Information present in the Passenger and cancellation tables. Hint: Use UNION Operator.
- Display the number of days in a week on which the 9W01 bus is available.
- Find number of tickets booked for each PNR_no using GROUP BY CLAUSE. Hint: Use GROUP BY on PNR_No.
- Find the distinct PNR numbers that are present.
- 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.
- Find the total number of cancelled seats.
REFERENCE BOOKS:
- Introduction to SQL, Rick F. Vander Lans, Pearson education.
- Oracle PL/SQL, B. Rosenzweig and E. Silvestrova, Pearson education.
- SQL & PL/SQL for Oracle 10 g, Black Book, Dr. P. S. Deshpande, Dream Tech.
- Oracle Database 11 g PL/SQL Programming, M. Mc Laughlin, TMH
For all other B.Tech 2nd Year 2nd Sem syllabus go to JNTUH B.Tech Information Technology 2nd Year 2nd Sem Course Structure for (R16) Batch.
All details and yearly new syllabus will be updated here time to time. Subscribe, like us on facebook and follow us on google plus for all updates.
Do share with friends and in case of questions please feel free drop a comment.