MANIPAL UNIVERSITY JAIPUR

Department of Computer and Communication Engineering

Course Hand-out
A. Basic Details:

Programme Name: BTECH CCE

Relational Database Management
Course Name:
Systems Lab
Course Code: CCE 2230
LTPC (Lecture Tutorial Practical Credits): 0021
Session: 2024-25
Class: [Link] 2nd Year / 4th Semester
Course Coordinator: Dr. Jitendra Singh Yadav
Dr Somya R Goyal, Dr Sandeep Kumar
Course Instructor(s):
Sharma, Dr Shalini Pathak
Additional Practitioner(s) – if any
To be identified and appointed later/ or at
(Industry Fellow/ Visiting Faculty/ Adjunct Faculty,
the beginning of the semester
etc.):

B. Introduction: To familiarize the students with the fundamental concepts, techniques and tools of Relational
DBMS. Participation in this course will enable students to better use Databases in many application areas
and will prepare them to take advanced courses in more specific areas of Databases.

C. Course Outcomes: At the end of the course, students will be able to

Target Target
CO Statement CO Level
Attainment % Attainment level
Construct and apply ER and EER
diagrams for a given scenario by
correctly identifying entities, attributes, CCE2230.1 3 85% 3
and relationships among different
entities.
Apply SQL queries to design, insert,
update, delete, transfer, secure, and
CCE2230.2 3 85% 3
manage data in a database, ensuring
data integrity and avoiding anomalies.
Apply different types of SQL triggers in
distinct databases to design and
CCE2230.3 3 85% 3
implement a hypothetical scenario that
reverses changes made to a table.
Analyze the roles of stored procedures
and transactions in ensuring data
integrity and operational efficiency
CCE2230.4 4 75% 2
within a real-life database application,
identifying their interdependencies and
practical implications.
D. Program Outcomes and Program Specific Outcomes
[PO.1] Engineering knowledge: Apply the knowledge of mathematics, science, engineering
fundamentals, and an engineering specialization to the solution of complex engineering
problems
[PO.2] Problem analysis: Identify, formulate, research literature, and analyze complex engineering
problems reaching substantiated conclusions using first principles of mathematics, natural
sciences, and engineering sciences
[PO.3] Design/development of solutions: Design solutions for complex engineering problems and
design system components or processes that meet the specified needs with appropriate
consideration for the public health and safety, and the cultural, societal, and environmental
considerations
[PO.4] Conduct investigations of complex problems: Use research-based knowledge and research
methods including design of experiments, analysis and interpretation of data, and synthesis
of the information to provide valid conclusions
[PO.5] Modern tool usage: Create, select, and apply appropriate techniques, resources, and modern
engineering and IT tools including prediction and modeling to complex engineering
activities with an understanding of the limitations
[PO.6] The engineer and society: Apply reasoning informed by the contextual knowledge to assess
societal, health, safety, legal, and cultural issues and the consequent responsibilities relevant
to the professional engineering practice
[PO.7] Environment and sustainability: Understand the impact of the professional engineering
solutions in societal and environmental contexts, and demonstrate the knowledge of, and
need for sustainable development
[PO.8] Ethics: Apply ethical principles and commit to professional ethics and responsibilities and
norms of the engineering practices
[PO.9] Individual and team work: Function effectively as an individual, and as a member or leader
in diverse teams, and in multidisciplinary settings
[PO.10] Communication: Communicate effectively on complex engineering activities with the
engineering community and with society at large, such as, being able to comprehend and
write effective reports and design documentation, make effective presentations, and give and
receive clear instructions
[PO.11] Project management and finance: Demonstrate knowledge and understanding of the
engineering and management principles and apply these to one’s own work, as a member
and leader in a team, to manage projects and in multidisciplinary environments
[PO.12] Life-long learning: Recognize the need for and have the preparation and ability to engage
in independent and life-long learning in the broadest context of technological change
Program Specific Outcomes.
[PSO.1]. Imbibe the basic concepts and applications of computer-based Communication or
networking, information sharing, signal processing, web-based systems, smart devices, and
communication technology.

[PSO.2]. Investigate prominent areas in the field of Computer and Communication Engineering to
provide feasible solutions.
[PSO.3]. Apply the contextual knowledge in the field of Computing and Communication to assess
social, health, safety, and security issues relevant to the professional engineering practice .
E. Assessment Plan:
Criteria Description Maximum Marks
Continuous Assessments: 60
Internal Assessment 1. Lab record and Lab 30 + 10 + 10 + 10 = 60
(Summative) performance.
2. Patent published or industry
oriented global certification or
academia course
3. Performance on HackerRank,
LeetCode
4. Mini project/ copyright relevant
to lab course
End Term Exam (Summative) End Term Exam including 40
written exam, viva, and project
evaluation.

Total 100
Attendance (Formative) A minimum of 75% Attendance is required to be maintained by a
student to be qualified for taking up the End Semester examination.
The allowance of 25% includes all types of leaves including medical
leaves.

F. Syllabus
Drawing ER diagram, Conversion of ER diagram to relational model, Experiments on DDL, DML, DCL,
and DQL. Basic SQL, Advanced SQL, Data Integrity Constraints and Built-in Functions, Experiments on
Basic PL/SQL, PL/SQL Exceptions and Transactions, PL/SQL Cursors, PL/SQL Procedures, Functions and
Packages, Triggers, Views, Indices, Stored Procedures, DB application development with front end.

Textbooks
T1. Avi Silberschatz, Henry F. Korth, S. Sudarshan, “Database System Concepts”,
TMH, New Delhi, 2006
T2. R. Elmasri, S. B. Navathe, “Fundamentals of Database Systems”, Addison &
Weisely, New Delhi, 2008
Reference Books
R1. “Teach yourself SQL & PL/SQL using Oracle 8i & 9i with SQLJ”, Ivan Bayross, BPB
Publications, 2010
R2. Avi Silberschatz, Henry F. Korth, S. Sudarshan, “Database System Concepts”, TMH,
New Delhi, 2006
 G. Lecture Plan

Lab Topics Session Outcome Mode of Corresponding Mode of Assessing the

No. Delivery CO Outcome
1 • Introduction to basic DDL, DML and DCL • Understand basic concepts of Learning through problem- CCE2230.1 Continuous
commands and domain types in SQL. DDL, DML and DCL solving, Group- teaching, Evaluation, End Term
• DDL statements to create, drop, alter, view • Demonstrate working of various self-study Examination
and rename the Database. DDL statements

• Write DML statements to insert the values • Demonstrate working of various Learning through problem- CCE2230.2 Continuous
2 into the tables. Use variants to insert values DML statements solving, Group- teaching, Evaluation, End Term
such as insert multiple records and insert self-study Examination
records resulting from a select query.
• Write statements to add and delete a column
in a table which is pre-existent.
• Write DML statements to update a table for
single and multiple field data.
• Write DML statements to delete single or
multiple record(s) from a table.

3-4 • Add primary key constraint to a pre- • Understand use of different types of Learning through problem- CCE2230.1 Continuous
existent table. constraints solving, Group- teaching, CCE2230.2 Evaluation, End Term
• Add NOT NULL / UNIQUE constraint to a self-study Examination
pre-existent column.
• Define the foreign key constraint. Show the
errors returned by Database when:
• a) FK constraint is violated
• b) A referenced item is deleted
 • Define and demonstrate cascading
effect in foreign key referenced
tables.
• Define, add and drop the
check/default constraint.

• Define auto increment

arguments/attributes of a table.

5-6 • Practice SELECT query with Demonstrate nested subqueries and different Learning through CCE2230.3 Continuous Evaluation,
following options: DML statements problem-solving, Group- Project,
teaching, self-study End Term Examination
Distinct, order by, between,
top/max/min and other aggregation
keywords, group by, having, wild
card matching, exists
• Nested subqueries

7 • Write a query to create INNER JOIN / Demonstrate different JOIN operations Learning through CCE2230.3 Continuous Evaluation,
LEFT JOIN / RIGHT JOIN / FULL problem-solving, Group- Project,
JOIN in two tables. teaching, self-study End Term Examination

8 • Write a query to create/delete VIEW Demonstrate the use of VIEW and indexing Learning through CCE2230.3 Continuous Evaluation,
from two tables including some problem-solving, Group- Project,
selection criteria. teaching, self-study End Term Examination
• Write a query to create and delete
clustered/non-clustered index for a
table.

9-10  To implement the concept of trigger Demonstrate use of TRIGGERS Learning through CCE2230.3 Continuous Evaluation,
in database: problem-solving, Group- Project,
 How to apply database triggers teaching, self-study End Term Examination
 Types of database triggers
 Create/delete database triggers
  Create trigger to demonstrate
magic tables (INSERTED and
DELETED).
 Create a hypothetical situation
to undo the changes in a table
via Trigger (Max credit limit
reached/ Balance insufficient
etc.).

11-12 • Write some stored procedures to Demonstrate stored procedures and transaction Learning through CCE2230.4 Continuous Evaluation,
cover the following problems: problem-solving, Group- Project,
 Demonstrate Control teaching, self-study End Term Examination
structures
 Swap two numbers
 Find the sum of digits
 Calculate grades etc.
• Define Transaction, demonstrate
the Commit and Rollback
operations using hypothetical
situations.
H. Course Articulation Matrix: (Mapping of COs with POs)
CORRELATION WITH PROGRAM CORRELATION WITH
CO STATEMENT OUTCOMES PROGRAM SPECIFIC
OUTCOMES

PO PO PO PO PO PO PO PO PO PO PO PO PSO 1 PSO 2 PSO 3

1 2 3 4 5 6 7 8 9 10 11 12
[CCE2230.1]: Construct the ER, and EER diagrams for a 1 1 3 2 3 1 1 1 1 1 1 3
given scenario with correct identification of
entities, attributes and relations among
different entities.

[CCE2230.2]: Write the queries to build a database, insert, 1 2 3 3 1 1 1 1 1 1 3

update, delete, transfer, secure, and manage
the data in a database, with no anomaly.
[CCE2230.3]: Use different types of triggers in each 1 2 3 1 1 1 1 1 1 2
database and to create a hypothetical
situation to undo the changes in a table.
[CCE2230.4]: Demonstrate the role of stored procedures 1 2 3 1 1 1 1 1 1 3 2
and transactions with no ambiguity in a
database built for a real-life application.

1-Low Correlation; 2- Moderate Correlation; 3- Substantial Correlation

 J. Course Outcome Attainment Level Matrix:
ATTAINMENT OF PROGRAM OUTCOMES ATTAINMENT OF
CO THRESHOLD VALUE: 40% PROGRAM SPECIFIC
STATEMENT OUTCOMES

PO 1 PO 2 PO 3 PO 4 PO 5 PO 6 PO 7 PO 8 PO 9 PO 10 PO 11 PO PSO 1 PSO 2 PSO 3

12

[CCE2230.1]: Construct the ER, and EER diagrams

for a given scenario with correct
identification of entities, attributes and
relations among different entities.
[CCE2230.2]: Write the queries to build a database,
insert, update, delete, transfer, secure,
and manage the data in a database, with
no anomaly.
[CCE2230.3]: Use different types of triggers in a
given database and to create a
hypothetical situation to undo the
changes in a table.
[CCE2230.4]: Demonstrate the role of stored
procedures and transactions with no
ambiguity in a database built for a real
life application.
[CCE2230.1]: Construct the ER, and EER diagrams
for a given scenario with correct
identification of entities, attributes and
relations among different entities.
0-No Attainment; 1- Low Attainment; 2- Moderate Attainment; 3- Substantial Attainment
 Department of Computer and Communication Engineering
_______________________________________________________________________
Relational Database Management Systems Lab
Course Code: CCE2230
List of Experiments

1. Introduction to basic DDL, DML and DCL commands and domain types in SQL.
2. DDL statements to create, drop, alter, view and rename the Database.
3. Write DML statements to insert the values into the tables. Use variants to insert values such
as insert multiple records and insert records resulting from a select query.
4. Write statements to add and delete a column in a table which is pre-existent.
5. Write DML statements to update a table for single and multiple field data.
6. Write DML statements to delete single or multiple record(s) from a table.
7. Add primary key constraint to a pre-existent table.
8. Add NOT NULL / UNIQUE constraint to a pre-existent column.
9. Define the foreign key constraint. Show the errors returned by Database when:
a) FK constraint is violated
b) A referenced item is deleted
[Link] and demonstrate cascading effect in foreign key referenced tables.
[Link], add and drop the check/default constraint.
[Link] auto increment arguments/attributes of a table.
[Link] select query with following options:
• Distinct • Count
• Order by • Group by (Having)
• Between • Nested Queries
• IN keyword • EXISTS keyword
• TOP/ Max/ Min and other Keywords • Wildcard matching
[Link] a query to create INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL JOIN in two
tables.
[Link] a query to create/delete VIEW from two tables including some selection criteria.
[Link] a query to create and delete clustered/non-clustered index for a table.
[Link] implement the concept of trigger in database:
a. How to apply database triggers
b. Types of database triggers
c. Create/delete database triggers
d. Create trigger to demonstrate magic tables (INSERTED and DELETED).
[Link] a hypothetical situation to undo the changes in a table via Trigger (Max credit limit
reached/ Balance insufficient etc.).
 [Link] some stored procedures to cover the following problems:
a. Demonstrate Control structures
b. Swap two numbers
c. Find the sum of digits
d. Calculate grades etc.
[Link] Transaction, demonstrate the Commit and Rollback operations using hypothetical
situations.

References
1. Teach yourself SQL & PL/SQL using Oracle 8i & 9i with SQLJ, Ivan Bayross, BPB
Publications, 2010.
2. R1. Avi Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts, TMH,
New Delhi, 2006.

Dr Jitendra Singh Yadav

(Course Coordinator)