21CSL55 Database Management Systems Laboratory with Mini Project syllabus for IS

PART-A: SQL Programming (Max. Exam Marks. 50)

Design, develop, and implement the specified queries for the following problems using Oracle, MySQL, MS SQL Server, or any other DBMS under LINUX/Windows environment.  Create Schema and insert at least 5 records for each table. Add appropriate database constraints.

1 Aim: Demonstrating creation of tables, applying the view concepts on the tables.

Program Consider the following schema for a Library Database: BOOK(Book_id, Title, Publisher_Name, Pub_Year) BOOK_AUTHORS(Book_id, Author_Name) PUBLISHER(Name, Address, Phone) BOOK_COPIES(Book_id, Programme_id, No-of_Copies) BOOK_LENDING(Book_id, Programme_id, Card_No, Date_Out, Due_Date) LIBRARY_PROGRAMME(Programme_id, Programme_Name, Address)

Write SQL queries to

1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each Programme, etc.

2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017.

3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.

4. Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.

5. Create a view of all books and its number of copies that are currently available in the Library.

Reference:

2 Aim: Discuss the various concepts on constraints and update operations.

Program: Consider the following schema for Order Database:

SALESMAN(Salesman_id, Name, City, Commission)

CUSTOMER(Customer_id, Cust_Name, City, Grade, Salesman_id)

ORDERS(Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)

Write SQL queries to

1. Count the customers with grades above Bangalore’s average.

2. Find the name and numbers of all salesman who had more than one customer.

3. List all the salesman and indicate those who have and don’t have customers in their cities (Use UNION operation.)

4. Create a view that finds the salesman who has the customer with the highest order of a day.

5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

Reference:

3 Aim: Demonstrate the concepts of JOIN operations.

Program: Consider the schema for Movie Database:

ACTOR(Act_id, Act_Name, Act_Gender) DIRECTOR(Dir_id, Dir_Name, Dir_Phone)

MOVIES(Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id)

MOVIE_CAST(Act_id, Mov_id, Role) RATING(Mov_id, Rev_Stars)

Write SQL queries to

1. List the titles of all movies directed by ‘Hitchcock’.

2. Find the movie names where one or more actors acted in two or more movies.

3. List all actors who acted in a movie before 2000 and also in a movie after 2015(use JOIN operation).

4. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.

5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.

Reference:

4 Aim: Introduce concepts of PLSQL and usage on the table. Program: Consider the schema for College Database: STUDENT(USN, SName, Address, Phone, Gender) SEMSEC(SSID, Sem, Sec) CLASS(USN, SSID) COURSE(Subcode, Title, Sem, Credits) IAMARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)

Write SQL queries to

1. List all the student details studying in fourth semester ‘C’ section.

2. Compute the total number of male and female students in each semester and in each section.

3. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all Courses.

4. Calculate the FinalIA (average of best two test marks) and update the corresponding table for all students.

5. Categorize students based on the following criterion: If FinalIA = 17 to 20 then CAT = ‘Outstanding’ If FinalIA = 12 to 16 then CAT = ‘Average’ If FinalIA< 12 then CAT = ‘Weak’ Give these details only for 8th semester A, B, and C section students.

Reference:

5 Aim: Demonstrate the core concepts on table like nested and correlated nesting queries and also EXISTS and NOT EXISTS keywords.

Program: Consider the schema for Company Database:

EMPLOYEE(SSN, Name, Address, Sex, Salary, SuperSSN, DNo)

DEPARTMENT(DNo, DName, MgrSSN, MgrStartDate)

DLOCATION(DNo,DLoc)

PROJECT(PNo, PName, PLocation, DNo)

WORKS_ON(SSN, PNo, Hours)

Write SQL queries to

1. Make a list of all project numbers for projects that involve an employee whose last name is ‘Scott’, either as a worker or as a manager of the department that controls the project.

2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.

3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department

4. Retrieve the name of each employee who works on all the projects controlled by department number

5 (use NOT EXISTS operator).

5. For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than Rs.6,00,000.

Reference:

Pedagogy For the above experiments the following pedagogy can be considered. Problembased learning, Active learning, MOOC,Chalk &Talk

PART B
Mini project:

For any problem selected, make sure that the application should have five or more tables. Indicative areas include: Organization, health care, Ecommerce etc.

Course Outcomes:

At the end of the course the student will be able to:

CO 1. Create, Update and query on the database.

CO 2. Demonstrate the working of different concepts of DBMS

CO 3. Implement, analyze and evaluate the project developed for an application.

Assessment Details (both CIE and SEE)

• The weightage of Continuous Internal Evaluation (CIE) is 50% and for Semester End Exam (SEE) is 50%.
• The minimum passing mark for the CIE is 40% of the maximum marks (20 marks).
• A student shall be deemed to have satisfied the academic requirements and earned the credits allotted to each course.
• The student has to secure not less than 35% (18 Marks out of 50) in the semester-end examination (SEE).
• The student has to secure a minimum of 40% (40 marks out of 100) in the sum total of the CIE (Continuous Internal Evaluation) and SEE (Semester End Examination) taken together.

Continuous Internal Evaluation (CIE):

CIE marks for the practical course is 50 Marks. The split-up of CIE marks for record/ journal and test are in the ratio 60:40.

• Each experiment to be evaluated for conduction with an observation sheet and record write-up. Rubrics for the evaluation of the journal/write-up for hardware/software experiments designed by the faculty who is handling the laboratory session and is made known to students at the beginning of the practical session.
• Record should contain all the specified experiments in the syllabus and each experiment write-up will be evaluated for 10 marks.
• Total marks scored by the students are scaled downed to 30 marks (60% of maximum marks).
• Weightage to be given for neatness and submission of record/write-up on time.
• Department shall conduct 02 tests for 100 marks, the first test shall be conducted after the 8th week of the semester and the second test shall be conducted after the 14th week of the semester.
• In each test, test write-up, conduction of experiment, acceptable result, and procedural knowledge will carry a weightage of 60% and the rest 40% for viva-voce.
• The suitable rubrics can be designed to evaluate each student’s performance and learning ability. Rubrics suggested in Annexure-II of Regulation book
• The average of 02 tests is scaled down to 20 marks (40% of the maximum marks).
• The Sum of scaled-down marks scored in the report write-up/journal and average marks of two tests is the total CIE marks scored by the student.

Semester End Evaluation (SEE):

• SEE marks for the practical course is 50 Marks.
• SEE shall be conducted jointly by the two examiners of the same institute, examiners are appointed by the University
• All laboratory experiments are to be included for practical examination.
• (Rubrics) Breakup of marks and the instructions printed on the cover page of the answer script to be strictly adhered to by the examiners. OR based on the course requirement evaluation rubrics shall be decided jointly by examiners.
• Students can pick one question (experiment) from the questions lot prepared by the internal /external examiners jointly.
• Evaluation of test write-up/ conduction procedure and result/viva will be conducted jointly by examiners.
• General rubrics suggested for SEE are mentioned here, writeup-20%, Conduction procedure and result in -60%, Viva-voce 20% of maximum marks. SEE for practical shall be evaluated for 100 marks and scored marks shall be scaled down to 50 marks (however, based on course type, rubrics shall be decided by the examiners)
• Students can pick one experiment from the questions lot of PART A with an equal choice to all the students in a batch. For PART B, the project group (Maximum of 4 students per batch) should demonstrate the mini-project.
• Weightage of marks for PART A is 60% and for PART B is 40%. General rubrics suggested to be followed for part A and part B.
• Change of experiment is allowed only once and Marks allotted to the procedure part to be made zero (Not allowed for Part B).
• The duration of SEE is 03 hours Rubrics suggested in Annexure-II of Regulation book

Textbooks:

1. Fundamentals of Database Systems, Ramez Elmasri and Shamkant B. Navathe, 7th Edition, 2017, Pearson.

2. Database management systems, Ramakrishnan, and Gehrke, 3rd Edition, 2014, McGraw Hill