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.<\/p>\n
The detailed syllabus for Database Management Systems Lab B.Tech 2016-2017 (R16) \u00a0second year second sem is as follows.<\/p>\n
B.Tech. II Year II Sem. \u00a0 \u00a0L\/T\/P\/C
\nCourse Code: CS407ES \u00a00\/0\/3\/2<\/p>\n
Course Objectives:<\/strong> This lab enables the students to practice the concepts learnt in the subject\u00a0DBMS by developing a database for an example company named \u201cRoadway Travels\u201d whose\u00a0description is as follows. The student is expected to practice the designing, developing and\u00a0querying a database in the context of example database \u201cRoadway travels\u201d. Students are\u00a0expected to use \u201cMysql\u201d database.<\/p>\n Course Outcomes:<\/strong><\/p>\n Roadway Travels: “Roadway Travels”<\/strong> is in business since 1997 with several buses Reservations & Cancellation:<\/strong> Reservations are directly handled by booking office.\u00a0Reservations can be made 30 days in advance and tickets issued to passenger. One\u00a0Passenger\/person can book many tickets (to his\/her family).\u00a0Cancellations are also directly handed at the booking office.<\/p>\n In the process of computerization of Roadway Travels you have to design and develop a\u00a0Database which consists the data of Buses, Passengers, Tickets, and Reservation and\u00a0cancellation details. You should also develop query\u2019s using SQL to retrieve the data from the\u00a0database.<\/p>\n The above process involves many steps like 1. Analyzing the problem and identifying the\u00a0Entities and Relationships, 2. E-R Model 3. Relational Model 4. Normalization 5.\u00a0Creating the database 6. Querying. Students are supposed to work on these steps week wise\u00a0and finally create a complete \u201cDatabase System\u201d to Roadway Travels. Examples are given\u00a0at every experiment for guidance to students.<\/p>\n Experiment 1: E-R Model<\/strong> Entities: 1<\/strong>. BUS 2. Ticket 3. Passenger Note:<\/strong> The student is required to submit a document by writing the Entities and Keys to the\u00a0lab teacher.<\/p>\n Experiment<\/strong> 2: Concept design with E-R Model Example:<\/strong> E-R diagram for bus<\/p>\n Note:<\/strong> The student is required to submit a document by drawing the E-R Diagram to the lab\u00a0teacher. Example<\/strong>: The passenger tables look as below. This is an example. You can add more\u00a0attributes based on your E-R model. This is not a normalized table.\u00a0Passenger<\/p>\n Note:<\/strong> The student is required to submit a document by Represent relationships in a tabular Experiment 4:<\/strong> Normalization Example for creation of a normalized \u201cPassenger\u201d table. Similarly create all other tables.<\/p>\n Note:<\/strong> Detailed creation of tables is given at the end.<\/p>\n Experiment 6:<\/strong> Practicing DML commands Inserting values into \u201cBus\u201d table: UPDATE BUS SET Bus No = 1 WHERE BUS NO=2;<\/strong><\/p>\n Experiment 7: Querying<\/strong> Practice the following Queries:<\/strong><\/p>\n Experiment 8 and Experiment 9: Querying (continued\u2026)<\/strong><\/p>\n\n
\nconnecting different places in India. Its main office is located in Hyderabad.
\nThe company wants to computerize its operations in the following areas:<\/p>\n\n
\nAnalyze the problem carefully and come up with the entities in it. Identify what data has to be
\npersisted in the database. This contains the entities, attributes etc.
\nIdentify the primary keys for all the entities. Identify the other keys like candidate keys,
\npartial keys, if any.
\nExample:<\/p>\n
\nRelationships:<\/strong> 1. Reservation 2. Cancellation
\nPrimary Key Attributes: 1. Ticket ID (Ticket Entity) 2. Passport ID (Passenger Entity)\u00a03. Bus_NO (Bus Entity)\u00a0Apart from the above mentioned entities you can identify more. The above mentioned are\u00a0few.<\/p>\n
\nRelate the entities appropriately. Apply cardinalities for each relationship. Identify strong\u00a0entities and weak entities (if any). Indicate the type of relationships (total \/ partial). Try to\u00a0incorporate generalization, aggregation, specialization etc wherever required.<\/p>\n
\nExperiment 3:<\/strong> Relational Model
\nRepresent all the entities (Strong, Weak) in tabular fashion. Represent relationships in a\u00a0tabular fashion. There are different ways of representing relationships as tables based on the\u00a0cardinality. Represent attributes as columns in tables or as tables based on the requirement.\u00a0Different types of attributes (Composite, Multi-valued, and Derived) have different way of
\nrepresentation.<\/p>\n
\nfashion to the lab teacher.<\/p>\n
\nDatabase normalization is a technique for designing relational database tables to minimize\u00a0duplication of information and, in so doing, to safeguard the database against certain types of\u00a0logical or structural problems, namely data anomalies. For example, when multiple instances\u00a0of a given piece of information occur in a table, the possibility exists that these instances will\u00a0not be kept consistent when the data within the table is updated, leading to a loss of data\u00a0integrity. A table that is sufficiently normalized is less vulnerable to problems of this kind,\u00a0because its structure reflects the basic assumptions for when multiple instances of the same\u00a0information should be represented by a single instance only.\u00a0For the above table in the First normalization we can remove the multi valued attribute\u00a0Ticket_id and place it in another table along with the primary key of passenger.
\nFirst Normal Form<\/strong>: The above table can be divided into two tables as shown below.\u00a0Passenger
\nYou can do the second and third normal forms if required. Any how Normalized tables are\u00a0given at the end.
\nExperiment 5<\/strong>: Installation of Mysql and practicing DDL commands\u00a0Installation of MySql. In this week you will learn Creating databases, How to create tables,\u00a0altering the database, dropping tables and databases if not required. You will also try\u00a0truncate, rename commands etc.<\/p>\n
\nCREATE TABLE Passenger (
\nPassport_id INTEGER PRIMARY KEY,
\nName VARCHAR (50) Not NULL,
\nAge Integer Not NULL,
\nSex Char,
\nAddress VARCHAR (50) Not NULL);<\/p>\n
\nDML commands are used to for managing data within schema objects. Some examples:<\/p>\n\n
\nInsert into Bus values (1234,\u2019hyderabad\u2019, \u2018tirupathi\u2019);
\nInsert into Bus values (2345,\u2019hyderabd\u2019,\u2019Banglore\u2019);
\nInsert into Bus values (23,\u2019hyderabd\u2019,\u2019Kolkata\u2019);
\nInsert into Bus values (45,\u2019Tirupathi,\u2019Banglore\u2019);
\nInsert into Bus values (34,\u2019hyderabd\u2019,\u2019Chennai\u2019);
\nInserting values into \u201cPassenger\u201d table:
\nInsert into Passenger values (1, 45,\u2019ramesh\u2019, 45,\u2019M\u2019,\u2019abc123\u2019);
\nInsert into Passenger values (2, 78,\u2019geetha\u2019, 36,\u2019F\u2019,\u2019abc124\u2019);
\nInsert into Passenger values (45, 90,\u2019ram\u2019, 30,\u2019M\u2019,\u2019abc12\u2019);
\nInsert into Passenger values (67, 89,\u2019ravi\u2019, 50,\u2019M\u2019,\u2019abc14\u2019);
\nInsert into Passenger values (56, 22,\u2019seetha\u2019, 32,\u2019F\u2019,\u2019abc55\u2019);
\nFew more Examples of DML commands:
\nSelect * from Bus; (selects all the attributes and display)<\/p>\n
\nIn this week you are going to practice queries (along with sub queries) using ANY, ALL, IN,
\nExists, NOT EXISTS, UNION, INTERSECT, Constraints etc.<\/p>\n\n
\n