Lab Experiments:
Instructions for the Exercises:
1. Draw ER diagram based on given scenario with various Constraints.
2. Create Relational Database Schema based on the above scenario using Mapping Rules.
3. Perform the given queries using any RDBMS Environment.
4. Suitable tuples have to be entered so that queries are executed correctly
5. The results of the queries may be displayed directly
1 Create the following tables with properly specifying Primary keys, Foreign keys and solve the following queries.
BRANCH(Branchid,Branchname,HOD)
STUDENT(USN,Name,Address,Branchid,sem)
BOOK(Bookid,Bookname,Authorid,Publisher,Branchid)
AUTHOR(Authorid,Authorname,Country,age)
BORROW(USN,Bookid,Borrowed_Date)
Queries:
1 List the details of Students who are all Studying in 2nd sem MCA.
2 List the students who are not borrowed any books.
3 Display the USN, Student name, Branch_name, Book_name, Author_name , Books_Borrowed_Date of 2nd sem MCA Students who borrowed books.
4 Display the number of books written by each Author.
5 Display the student details who borrowed more than two books.
6 Display the student details who borrowed books of more than one Author.
7 Display the Book names in descending order of their names.
8 List the details of students who borrowed the books which are all published by the same Publisher.
2 Design an ER-diagram for the following scenario, Convert the same into a relational model and then solve the following queries.
Consider a Cricket Tournament “ABC CUP” organized by an organization. In the tournament there are many teams are contesting each having a Teamid,Team_Name, City, a coach. Each team is uniquely identified by using Teamid. A team can have many Players and a captain. Each player is uniquely identified by Playerid, having a Name, and multiple phone numbers,age. A player represents only one team. There are many Stadiums to conduct matches. Each stadium is identified using Stadiumid, having a stadium_name,Address ( involves city,area_name,pincode).A team can play many matches. Each match played between the two teams in the scheduled date and time in the predefined Stadium. Each match is identified uniquely by using Matchid. Each match won by any of the one team that also wants to record in the database. For each match man_of_the match award given to a player.
Queries:
1 Display the youngest player (in terms of age) Name, Team name , age in which he belongs of the tournament.
2 List the details of the stadium where the maximum number of matches were played.
3 List the details of the player who is not a captain but got the man_of _match award at least in two matches.
4 Display the Team details who won the maximum matches.
5 Display the team name where all its won matches played in the same stadium.
3 Consider the following Scenario and design an ER-Diagram, map the designed ER-diagram into a Relational model.
Consider an organization “ABC” having many employees. An employee works for one department. Each employee identified by using Empid, having Name, address ( described as House_no, city, district, state, pin code) and more than one phone numbers. Department identified by using Dno, having Dname, Dlocation. Each Department having a manager . Each department having many employees. There are many Projects , each project is controlled by the department. Each Project uniquely identified by Pno, having Project_name,Project_location. An employee works on many Projects. Number of hours per week worked on each project by an Employee also needs to be recorded in the database . A project is worked by many employees. Each employee supervised by the supervisor. Employee having many dependents. Dependents having the dependent_name, gender, age, address. Dependents are identified by Empid.
T1(Empid, Emp_Name,city, district, state, pin_code, phoneno, Dno,Dname,Dlocation, Dept_mgr_id, Pno, Project_name, Project_location, Number_of_Hours,Supervisor_Empid, Dependent_name, gender, address)
Deduce the above Relation T1 into the 3NF and then solve the following queries.
Queries:
1. Display the details of the employees who are working on both the projects having project_no 5 and 10.
2. Display the details of employees having atleast two dependents.
3. Display the project name on which more number of employees are working.
4. Retrieve the employees who do not have any dependents.
5. Display the Employee details whose total number of hours per week working on various projects is maximum than all other employees.
6. create a view to display the number of employees working in each department.
4 Design an ER-diagram for the following scenario, Convert the same into a relational model, normalize Relations into a suitable Normal form and then solve the following queries.
A country can have many Tourist places . Each Tourist place is identified by using tourist_place_id, having a name, belongs to a state, Number of kilometers away from the capital city of that state,history. There are many Tourists visits tourist places every year. Each tourist is identified uniquely by using Tourist_id, having a Name, age, Country and multiple emailids. A tourist visits many Tourist places, it is also required to record the visted_date in the database. A tourist can visit a Tourist place many times at different dates. A Tourist place can be visited by many tourists either in the same date or at different dates.
Queries:
1 List the state name which is having maximum number of tourist places.
2 List details of Tourist place where maximum number of tourists visited.
3 List the details of tourists visited all tourist places of the state “KARNATAKA”.
4 Display the details of the tourists visited at least one tourist place of the state, but visited all states tourist places.
5 Display the details of the tourist place visited by the tourists of all country.
5 Design an ER-diagram for the following scenario, Convert the same into a relational model, normalize Relations into a suitable Normal form and then solve the following queries.
A country wants to conduct an election for the parliament. A country having many constituencies. Each constituency is identified uniquely by Constituency_id, having the Name, belongs to a state,Number_of_voters. A constituency can have many voters. Each voter is uniquely identified by using Voter_id, having the Name, age, address (involves Houseno,city,state,pincode). Each voter belongs to only one constituency. There are many candidates contesting in the election. Each candidates are uniquely identified by using candidate_id, having Name, phone_no, age, state. A candidate belongs to only one party.There are many parties. Each party is uniquely identified by using Party_id, having Party_Name,Party_symbol. A candidate can contest from many constituencies under a same party. A party can have many candidates contesting from different constituencies. No constituency having the candidates from the same party. A constituency can have many contesting candidates belongs to different parties. Each voter votes only one candidate of his/her constituencty.
Queries:
1 List the details of the candidates who are contesting from more than one constituencies which are belongs to different states.
2 Display the state name having maximum number of constituencies.
3 Create a stored procedure to insert the tuple into the voter table by checking the voter age. If voter’s age is at least 18 years old, then insert the tuple into the voter else display the “Not an eligible voter msg” .
4 Create a stored procedure to display the number_of_voters in the specified constituency. Where the constituency name is passed as an argument to the stored procedure.
5 Create a TRIGGER to UPDATE the count of “ Number_of_voters” of the respective constituency in “CONSTITUENCY” table , AFTER inserting a tuple into the “VOTERS” table.
Note 1: In the practical Examination each student has to pick one question from a lot of all the 5 questions.
Note 2: Change of program is not permitted in the Practical Examination.
Course Outcome (CO):
At the end of this course, the students will be able to
CO1: Understand, appreciate the underlying concepts of database technologies
CO2: Able to create database with different types of integrity constraints and use the SQL commands such as DDL, DML, DCL, TCL to access data from database objects.
CO3: Design and implement a database schema for a given problem domain
CO4: Perform embedded and nested queries
CO5: Take up real world problems independently