Course Outcomes:
at the end of the course students will be able to
CO1: Design entity-relationship diagrams to solve given database applications
CO2: Implement a database schema for a given problem.
CO3: Formulate SQL queries in Oracle for the given problem.
CO4: Apply normalization techniques to improve the database design for the given problem.
CO5: Build database and verify for its appropriate normalization for any given problem
Instructions for the Exercises:
1. Draw ER diagram based on given scenario with various Constraints.
2. Create Relational Database Schema based on the 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)
Execute the following Queries:
i.List the details of Students who are all studying in 2nd sem MCA.
ii.List the students who are not borrowed any books.
iii.Display the USN, Student name, Branch_name, Book_name, Author_name, Books_Borrowed_Date of 2nd sem MCA Students who borrowed books.
iv.Display the number of books written by each Author.
v.Display the student details who borrowed more than two books.
vi.Display the student details who borrowed books of more than one Author.
vii.Display the Book names in descending order of their names.
viii.List the details of students who borrowed the books which are all published by the same publisher.
2. Consider the following schema:
STUDENT (USN, name, date_of_birth, branch, mark1, mark2, mark3, total, GPA)
Execute the following queries:
i. Update the column total by adding the columns mark1, mark2, mark3.
ii. Find the GPA score of all the students.
iii. Find the students who born on a particular year of birth from the date_of_birth column.
iv. List the students who are studying in a particular branch of study.
v. Find the maximum GPA score of the student branch-wise.
vi. Find the students whose name starts with the alphabet “S”.
vii. Find the students whose name ends with the alphabets “AR”.
viii. Delete the student details whose USN is given as 1001.
3. 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. Execute the following Queries:
i. Display the youngest player (in terms of age) Name, Team name, age in which he belongs of the tournament.
ii. List the details of the stadium where the maximum number of matches were played.
iii. List the details of the player who is not a captain but got the man_of _match award at least in two matches.
iv. Display the Team details who won the maximum matches.
v. Display the team name where all its won matches played in the same stadium.
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:
i. List the state name which is having maximum number of tourist places.
ii. List details of Tourist place where maximum number of tourists visited.
iii. List the details of tourists visited all tourist places of the state “KARNATAKA”.
iv. Display the details of the tourists visited at least one tourist place of the state, but visited all states tourist places.
v. Display the details of the tourist place visited by the tourists of all country.
5. 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.Thereare 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:
i. List the details of the candidates who are contesting from more than one constituencies which are belongs to different states.
ii. Display the state name having maximum number of constituencies.
iii. 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” .
iv. 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.
v. 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.