CSE287 (Database Management Systems Laboratory) - Final

Vision: To become a Centre of Excellence in Teaching and Research in the field of Computer Science and Engineering .
KALASALINGAM UNIVERSITY
(Kalasalingam Academy of Research and Education)
Anand Nagar , Krishnan Kovil
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING
COURSE PLAN – EVEN SEMESTER 2014-2015
Subject with code Database Management Systems Laboratory– CSE287

Course B.Tech (CSE)
Semester / Sec IV/ A
Course Credit 2
Course Coordinator Dr.K.Kartheeban
Module Coordinator Dr.S.Senthamarai Kannan
Programme Coordinator Dr.P.Deepalakshmi
COURSE OBJECTIVES
The major objective of this lab is to provide a strong formal foundation in database
concepts, technology and practice to the participants to groom them into well-informed database
application developers. Rather than imparting isolated knowledge/experience fragments in each of
concepts, technology and practice, the course will aim at achieving a good blend of the three. The
overriding concern, therefore, is to include just enough concepts and theory to motivate and enrich the
practical component, and to include technology component to maximize the relevance of the course to
the industry without sacrificing the long-term objectives of rigour and foundational strength that can
withstand the vagaries of technological advances. The sub-objectives are:
 To give a good formal foundation on the relational model of data
 To present SQL and procedural interfaces to SQL comprehensively
 To give an introduction to systematic database design approaches covering conceptual design,
logical design and an overview of physical design
 To motivate the participants to relate all these to one or more commercial product environments
as they relate to the developer tasks
 To present the concepts and techniques relating to query processing by SQL engines
 To present the concepts and techniques relating to ODBC and its implementations.
 To introduce the concepts of transactions and transaction processing
Mission: To prepare the students for a prospective career in IT industry and for higher learning by
imparting sound technical knowledge. To carry out research in cutting edge technologies in computer
engineering to meet the requirement of the industry and society.
 To present the issues and techniques relating to concurrency and recovery in

multi-user database environments
COURSE DESCRIPTION
DBMS Laboratory provide a strong formal foundation in database concepts, technology and
practice to the participants, to familiarize the participant with the nuances of database environments
towards an information-oriented data-processing oriented framework , to give a good formal foundation
on the relational model of data and to present SQL and procedural interfaces to SQL comprehensively
IT also give an introduction to systematic database design approaches covering conceptual
design, logical design and an overview of physical description. It motivate the participants to relate all
these to one or more commercial product environments as they relate to the developer tasks by using the
concepts and techniques relating to query processing by SQL engines, and techniques relating to ODBC
and its implementations.
CAREER OPPORTUNITIES
 The database concept is used in all application areas in our real life such as telecommunication,
banking, reservation and etc. Nowadays all the organizations worldwide race to implement the
new era of big data applications. So almost all software industry looking for professional to work
in the database concepts in different positions like database administrator, application developer.
COURSE PRE-REQUISITIES
S.NO Courses Description

1 CSE102 - Programming Languages Fundamental Programming Language Concepts
DO’S
 Without Prior permission do not enter into the Laboratory.

 While entering into the LAB students should wear their ID cards.
 The Students should come with proper uniform.
 Students should sign in the LOGIN REGIS TER before entering into the laboratory.
 Students should come with observation and record note book to the laboratory.
 Students should maintain silence inside the laboratory.
 After completing the laboratory exercise, make sure to shutdown the system properly.
DONT’S
 Students bringing the bags inside the laboratory..

 Students wearing slippers/shoes insides the laboratory.
 Students using the computers in an improper way.
 Students scribbling on the desk and mishandling the chairs.
 Students using mobile phones inside the laboratory.
 Students making noise inside the laboratory.
PROGRAM EDUCATIONAL OBJECTIVES (PEOs)
PEOs DESCRIPTION
PEO1 The Graduates will be technically competent to excel in IT industry and to pursue
higher studies.
PEO2 The Graduates will possess the skills to design and develop economically and
technically feasible computing systems using modern tools and techniques.
PEO3 The Graduates will have effective communication skills, team spirit, and ethical
principles and the desire for self learning to succeed in their professional career.
PROGRAMME OUTCOMES (POs)
POs DESCRIPTION
PO1 Ability to apply knowledge of mathematics, science and computer engineering to
solve computational problems.
PO2 Identify, formulate, analyze and solve complex computing problems.
PO3 Capability to design and develop computing systems to meet the requirement of
industry and society with due consideration for public health, safety and
environment.
PO4 Ability to apply knowledge of design of experiment and data analysis to derive
solutions in complex computing problems.

Ability to develop and apply modeling, simulation and prediction tools and
techniques to engineering problems.
PO6 Assess and understand the professional, legal, security and societal responsibilities
relevant to computer engineering practice.
PO7 Ability to understand the impact of computing solutions in economic, environmental
and societal context for sustainable development.
PO8 Applying ethical principles and commitment to ethics of IT and software profession.
PO9 Ability to work effectively as an individual as well as in teams.
PO10 Effectively communicating with technical community and with society.
PO11 Demonstrating and applying the knowledge of computer engineering and
management principles in software project development and in multidisciplinary
areas.
PO12 Understanding the need for technological changes and engage in life-long learning.
COURSE OUTCOMES (COs)
COs DESCRIPTION
CO1 Demonstrate the basic fundamentals of Structured Query Language (SQL).
CO2 Employ the conceptual and relational models to design large database systems
CO3 Design and build database system for a given real world problem.
MAPPING OF PEO TO PO
PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
PEO1 S S S S S L L S S
PEO2 L S S S S S S
PEO3 S S S S S S L S
MAPPING OF CO TO PO
PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12
CO1 S
CO2 S S S
CO3 S S S S M M M
CONTENT DELIVERY / INSTRUCTIONAL METHODOLOGY
1. Class Room – Interactive Lectures

2. Hands on session in the Lab
3.
ASSESSMENT METHODOLOGYS:
Direct Indirect
 Internal Practical Exam  Course End Surveys
 External Practical Exam
 Mini Projects
ASSESSMENT
S.No Assessment Split up

Regular Lab Exercises
1 Internal Assessment (50 marks) (20)
Model Lab (30)
2 External Assessment (50 marks) End semester Lab (50)
BOOK REFERENCES
Reference
Books BOOK TITLE/AUTHORS/PUBLICATION
1 Database Processing: Fundamentals, Design, Implementation (Fifth
Edition); D. M. Kroenke; Prentice-Hall, 1994
2 Database Principles Programming Performance; Patrick O Neil; Morgan
Kaufmann Publishers, Inc., 1994
WEB RESOURCES
S. Units Websites
No
1. MS-ACCESS 1. http://www.tutorialspoint.com/listtutorials/ms-access/1
2. http://www.techonthenet.com/access/tutorials/
3. http://www.profsr.com/access/accless0.htm
4. https://support.office.com/en-ca/article/Training-courses-
for-Access-2013-a4bd10ea-d5f4-40c5-8b37-
d254561f8bce
2 SQL 1. http://www.w3schools.com/sql/
2. http://www.tutorialspoint.com/sql/
3. http://www.sqlcourse.com/
4. http://beginner-sql-tutorial.com/sql.htm
5. http://www.sql-tutorial.net/
6. http://www.sql-zone.com
7. http://www.computer.org
8. http://www.dbpd.com
9. http://www.dmreview.com
10. http://www.oracle.com/oramag/
Implementation of 1. www.tutorialspoint.com/plsql
Simple Programs USING 2. plsql-tutorial.com
PL/SQL
3. www.plsql.co
4. www.learn-with-video-tutorials.com › Databases
5. www.way2tutorial.com/plsql/tutorial.php
Implementation of High- 1.
http://www.tutorialspoint.com/plsql/plsql_cursors.htm
level language extension 2.
http://plsql-tutorial.com/plsql-explicit-cursors.htm
with Cursors. 3.
http://www.sqlshack.com/sql-server-cursor-tutorial/
4.
http://www.oracle.com/technetwork/issue-
archive/2013/13-mar/o23plsql-1906474.html
Implementation of High 1. http://www.way2tutorial.com/plsql/plsql_triggers.php
level language extension 2. http://docs.oracle.com/cd/E11882_01/appdev.112/e2551
with Triggers 9/triggers.htm#LNPLS020
3. http://www.java2s.com/Tutorial/Oracle/0560__Trigger/D
MLTriggerExample.htm
4. http://www.java2s.com/Tutorial/Oracle/0560__Trigger/C
atalog0560__Trigger.htm
Procedures and 1. http://www.tutorialspoint.com/plsql/plsql_functions.htm
Functions. 2. http://plsql-tutorial.com/plsql-functions.htm
3. http://docs.oracle.com/cd/B25329_01/doc/appdev.102/b2
5108/xedev_programs.htm
ODBC & JDBC 1. http://www.webopedia.com/TERM/J/JDBC.html
2. http://www.allinterview.com/showanswers/2594/what-is-
jdbc-and-odbc-and-what-are-the-differences-between-
them.html
3. https://catazine.wordpress.com/2012/02/18/the-
difference-between-odbc-and-jdbc/
LESSION PLAN
Topic Topic Name No. Of Cumulative

No Periods No. of periods
MS-ACCESS
1 Creating Tables and Queries using Access 1 1

2 Creating Forms and Reports using Access 1 2
SQL
Write a SQL Queries to implement DDL

3 1 3
commands in RDBMS.
Write a SQL Queries to implement DML and DCL
4 1 4
commands in RDBMS.
Write a SQL Queries to implement Aggregate
5 functions (COUNT, SUM, AVG, MAX and MIN), 1 5
GROUP BY, HAVING
Write a SQL Queries to implement Conversion
6 1 6
functions, String function and date functions.
Implementation of Simple Programs USING PL/SQL
7 Write a pl/sql program to find sum of two numbers. 1 7
8 Write a pl/sql program for finding Multiples of 5 1 8
Write a pl/sql program for display the
9 1 9
Multiplication Tables up to given number.
Write a PL/SQL block to check whether given
10 1 10
number is Armstrong or not
Write a pl/sql program to check weather given
11 1 11
number is Prime or not.
Write a pl/sql program to finding factorial of given
12 1 12
number.
Write a pl/sql program to generate reverse for given
13 1 13
number.
14 Write a pl/sql program to generate fibinocci series. 1 14
Write a pl/sql program to find given number is even

15 1 15
or odd.
Write a PL/SQL block to apply String Functions on
16 1 16
a given input String
Write a PL/SQL block to Check the Given String is
17 1 17
Palindrome or Not.
Write a pl/sql program to print a string in a letter by
18 1 18
letter format.
Write a pl/sql program to insert a space after each
19 letter in a given string. 1 19
Write a pl/sql program to calculate the student
20 grade using case statement. 1 20
Write a pl/sql program to update the commission
21 values for all employees with salary less than 2000 1 21
by adding Rs.1000 to existing employees.
Write a pl/sql program to delete employees whose
22 experience is less then 2 years. 1 22
23 Write a pl/sql program to swap two numbers. 1 23

Write a pl/sql program to find the largest of three
24 numbers 1 24
Write a PL/SQL block for inserting rows into
EMPDET table with the following
Calculations:
25 HRA=50% OF BASIC 1 25
DA=20% OF BASIC
PF=7% OF BASIC
NETPAY=BASIC+DA+HRA-PF
Write a pl/sql program to find the sum of digits in a
26 given number 1 26
Write a PL/SQL code to create,
a) Package specification
27 b) Package body. 1 27
For the insert, retrieve, update and delete
operations on a student
Write atable.
pl/sql program to handle a predefined
28 exception. 1 28
Write a pl/sql program to handle a user defined
29 exception. 1 29
Implementation of High-level language extension with Cursors.
Write a pl/sql program to displaying employee

30 1 30
details using cursors.
Write a Cursor to find employee with given job
31 1 31
and deptno.
Write a pl/sql program to display the employee
32 1 32
details using %type data type
Write a pl/sql program to display the employee
33 1 33
details using %rowtype data type.
Write a pl/sql program to print mark list using
34 1 34
cursors.
Write a pl/sql program to display employees using
35 1 35
parameterized cursor
Write a pl/sql program to display top 10 employee
36 1 36
details based on salary using cursors
Write a Cursor program to perform payroll
37 1 37
processing.
Implementation of High level language extension with Triggers
Write a TRIGGER to ensure that DEPT TABLE
38 does not contain duplicate of null values in 1 38
DEPTNO column.
Create a Simple Trigger that does not allow Insert
39 1 39
Update and Delete Operations on the Table
Create a Trigger that raises an User Defined
40 Error Message and does not allow updating and 1 40
Insertion
Write a trigger on the employee table which shows
41 the old values and new values of Ename after any 1 41
updations on ename on Employee table.
Write a Trigger program to maintain the inventory
42
level.
Implementation of stored Procedures and Functions.
Write a PL/SQL function ODDEVEN to return
43 value TRUE if the number passed to it is EVEN 1 42
else will return FALSE.
Write a function to calculate a factorial of a given
44 1 43
number.
Write a PL/SQL function CheckDiv that takes

two numbers as arguments and returns the
45 values 1 if the first argument passed to it is 1 44
divisible by the second argument, else will return
the value 0;
Write a PL/SQL function called POW that takes

46 two numbers as argument and return the value of 1 45
the first number raised to the power of the second.
47 Write a pl/sql program for creating a procedure for 1 46

calculating sum of two numbers.
Write a PL/SQL procedure called MULTI_TABLE

48 that takes two numbers as parameter and isplays the 1 47
multiplication of the first parameter till the second
parameter.
Consider the EMPLOYEE (EMPNO, SALARY,

ENAME) Table. Write a procedure raise_sal
which increases the salary of an employee. It
49 accepts an employee number and salary increase 1 48
amount. It uses the employee number to find the
current salary from the EMPLOYEE table and
update the salary.
Write a procedure to calculate total for the all the

50 students and pass regno, mark1, & mark2 as 1 49
arguments
Create a database for payroll processing system

51 using SQL and implement it using VB (ODBC- 2 51
ADO)
52 Create a database for banking system using SQL 2 53

and implement it using VB (ODBC-ADO)
NEW ADDITIONAL EXPERIMENTS WITH CO MAPPING
S.NO Name of the Experiments CO’s

1 Write a cursor which takes parameters and invoke a cursor from CO1
within a PL/SQL block.
2 Write an after trigger which acts at a row level on CO1,CO2
insert/update/delete and transfer old data to an audit trail table.
3 Design a database for library management in the college, CO1,CO2,CO3
populate data and write queries to return information like books
issued, books issued to user, number of books issued, books
reserved, etc.
4 Design a database for fee management in the college, populating CO1,CO2,CO3
data on fee payments, fee receipts and writing querying to return
data like uncollected fees, which paid fee, etc
INTER-DISCIPLINARY PROJECT WITH CO MAPPING
S.NO Name of the Experiments CO’s

1 Black Sea Energy Resource Development and Hydrogen CO2,CO3
Energy Problems
2 Implementation of Elementary geometric Database Operations CO3
for a 3D-GIS
3 A human genetics database management system CO2,CO3
VIRTUAL LINKS
S.NO Address
1 http://onlinevt.blogspot.in/2011/01/cs2258-dbms-lab-syllabus.html
2 http://www.oucom.ohiou.edu/dbms-witmer/lab.htm
MAPPING COs WITH PROJECT TITLES
S.No Name of the Experiment COs

1 Online Customer Complain System CO2,CO3
2 E-Shopping CO2,CO3
3 Bug Tracking System CO2,CO3
4 Student Management System CO2,CO3
5 Ship Management System CO2,CO3
6 Web based bill payment utility CO2,CO3
7 Online Index Recommendations for High Dimensional CO2,CO3
Databases
EVALUATION PROCEDURE FOR INTERNAL AND EXTERNAL ASSESSMENT:
Modules Excellent Acceptable Unacceptable Marks

(100)
Level Of Very Clear Understood what is Poor
understanding understanding (20 - given but its not up understanding
25
the given 25) to the level of (0 - 9)
problem Expectation (10 - 19)
Program Logic With good time and Used efficient Poor Logic (0 -
space complexity programming style 10)
(30 - 40) and Met the keenness 40
of the given problem
(11 - 29)
Design Output as Expected Output is not upto Partial output (0
principles and (15 - 20) the level of - 5) 25
Out put expectation. (7 - 14)
Viva questions Answered for more Answered for 75% of Answered for
than 90% of the the questions. (5 - 8) less than 40% of
10
questions (09 - 10) the questions (0 -
4)
MAPPING THE EXPERIMENTS
VIVA VOICE
Efficiency Efficiency Outpu ( marks)
Experiments Algorithm of t Technical Communication
program
Creating Tables and Queries using

15 50 15 10 10
Access
Write a SQL Queries to implement
15 50 15 10 10
DDL commands in RDBMS.
DML and DCL commands in 15 50 15 10 10
RDBMS.
Aggregate functions (COUNT,
15 50 15 10 10
SUM, AVG, MAX and MIN),
GROUP BY, HAVING
Write a pl/sql program to calculate
the student grade using case 15 50 15 10 10
statement.
Write a pl/sql program to update the 25 40 15 10 10
commission values for all
employees with salary less than
2000 by adding Rs.1000 to existing

employees.
Consider the EMPLOYEE
(EMPNO, SALARY, ENAME)
Table. Write a procedure raise_sal
which increases the salary of an
employee. It accepts an
employee number and salary 25 40 15 10 10
increase amount. It uses the
employee number to find the
current salary from the
EMPLOYEE table and update the
salary.
Create a Trigger that raises an
User Defined Error Message and
20 40 20 10 10
does not allow updating and
Insertion
Create a database for payroll

processing system using SQL and 25 40 15 10 10
implement it using VB (ODBC-
ADO)
Create a database for banking

system using SQL and implement it 25 40 15 10 10
using VB (ODBC-ADO)
Course Coordinator Module Coordinator
Programme Coordinator HOD/CSE

CSE287 (Database Management Systems Laboratory) - Final

Uploaded by

Copyright:

Available Formats

CSE287 (Database Management Systems Laboratory) - Final

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

CSE287 (Database Management Systems Laboratory) - Final

Uploaded by

Copyright:

Available Formats

Vision: To become a Centre of Excellence in Teaching and Research in the field of Computer Science and Engineering .

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

COURSE PLAN – EVEN SEMESTER 2014-2015

Subject with code Database Management Systems Laboratory– CSE287

 To present the issues and techniques relating to concurrency and recovery in

S.NO Courses Description

 Without Prior permission do not enter into the Laboratory.

 Students bringing the bags inside the laboratory..

PROGRAM EDUCATIONAL OBJECTIVES (PEOs)

PROGRAMME OUTCOMES (POs)

solutions in complex computing problems.

COURSE OUTCOMES (COs)

CONTENT DELIVERY / INSTRUCTIONAL METHODOLOGY

1. Class Room – Interactive Lectures

S.No Assessment Split up

Topic Topic Name No. Of Cumulative

1 Creating Tables and Queries using Access 1 1

Write a SQL Queries to implement DDL

Write a pl/sql program to find given number is even

23 Write a pl/sql program to swap two numbers. 1 23

Write a pl/sql program to displaying employee

Write a PL/SQL function CheckDiv that takes

Write a PL/SQL function called POW that takes

47 Write a pl/sql program for creating a procedure for 1 46

Write a PL/SQL procedure called MULTI_TABLE

Consider the EMPLOYEE (EMPNO, SALARY,

Write a procedure to calculate total for the all the

Create a database for payroll processing system

52 Create a database for banking system using SQL 2 53

NEW ADDITIONAL EXPERIMENTS WITH CO MAPPING

S.NO Name of the Experiments CO’s

INTER-DISCIPLINARY PROJECT WITH CO MAPPING

S.NO Name of the Experiments CO’s

MAPPING COs WITH PROJECT TITLES

S.No Name of the Experiment COs

EVALUATION PROCEDURE FOR INTERNAL AND EXTERNAL ASSESSMENT:

Modules Excellent Acceptable Unacceptable Marks

Creating Tables and Queries using

2000 by adding Rs.1000 to existing

Create a database for payroll

Create a database for banking

Course Coordinator Module Coordinator

Programme Coordinator HOD/CSE

You might also like