Course Title: DBMS and Web Technologies Laboratory (MMCL106)

RV Institute of Technology &Management®

(Affiliated to VTU, Belgavi, Karnataka)
DEPARTMENTOF MASTER OF COMPUTER APPLICATIONS

Course Title : DBMS and Web Technologies Laboratory Course Code :MMCL106
Total Contact Hours:28 Hours Credits :02
SEE Marks : 50 CIE Marks :50
Semester :I Academic Year :2024-2025
Lesson Plan Author: Dr. R.Saravanan Date :16-12-2024

Prerequisites: the fundamentals of Programming. the basics of various types of Database.

Course Learning Objectives:
This course (MMCL106) will enable students to:
 Create SQL queries for the small projects.
 Create database objects that include tables, constraints, indexes, and sequences.
Course Outcomes:
At the end of the course the student will be able to:
 Create database objects.
 Design entity-relationship diagrams to solve given database applications.
 Implement a database schema for a given problem.
 Formulate SQL queries in Oracle for the given problem.
 Apply normalization techniques to improve the database design for the given problem.
 Build database and verify for its appropriate normalization for any given problem

S.No List of Experiments Bloom’s Course Outcomes Activities

Taxonomy Level (CO)
(L)

1 Create the following tables with L3 CO2 Demonstration

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,
Department of MCA Page1
 Course Title: DBMS and Web Technologies Laboratory (MMCL106)
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: L3 CO2 Demonstration
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
Department of MCA Page2
 Course Title: DBMS and Web Technologies Laboratory (MMCL106)
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 L3 CO2 Demonstration
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
Department of MCA Page3
 Course Title: DBMS and Web Technologies Laboratory (MMCL106)
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 A country wants to conduct an L3 CO2 Demonstration
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

Department of MCA Page4

 Course Title: DBMS and Web Technologies Laboratory (MMCL106)
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 “ 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” the
respective constituency in
“CONSTITUENCY” table ,
AFTER inserting a tuple into the
“VOTERS” table.
5 Design an ER-diagram for the L3 CO2 Demonstration
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
Department of MCA Page5
 Course Title: DBMS and Web Technologies Laboratory (MMCL106)
02.03.2021 updated 52/ 104
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.
6 Consider the following database L3 CO2 Demonstration
of student enrollment in courses
and books adopted for each
course.STUDENT
(regno#: string, name: string,
major: string, bdate: date)
COURSE (course#: int, cname:
string, dept: String)
TEXT (book_ISBN#: int,
book_title: string, publisher:
string, author: string)

Department of MCA Page6

 Course Title: DBMS and Web Technologies Laboratory (MMCL106)
ENROLL (regno#: string,
course#: int, sem: int, marks: int)
BOOK_ADOPTION
(course#: int, sem: int,
book_ISBN: int)
Create the above tables by
properly specifying the primary
keys and the foreign keys
Enter at least 7 to 10 records to
each table.
Execute SQL queries for the
following requirements:
1) List out the student details,
and their course details. The
records should be ordered in a
semester wise manner.
2) List out the student details
under a particular department
whose name is ordered in a
semester wise
3) List out all the book details
under a particular course
4) Find out the Courses in which
number of students studying will
be more than 2.
5) Find out the Publisher who has
published more than 2 books.

Semester End Examination:

 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 writeup/conduction procedure and result/viva will be conducted jointly by
examiners.
Department of MCA Page7
 Course Title: DBMS and Web Technologies Laboratory (MMCL106)
 General rubrics suggested for SEE are mentioned here, writeup20%,Conduction procedure
and result in 60%,Vivavoce 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)
 Change of experiment is allowed only once and 10% Marks allotted to the procedure part to
be made zero.
 The duration of SEE is 03 hours

Faculty In-charge Head of the Department

Date:

Department of MCA Page8