Annexure ‘CD – 01’

FORMAT FOR COURSE CURRICULUM

L T P/S SW/FW No. of TOTAL

PSDA CREDIT
Course Title: Database Management System Credit Units: UNITS
3 - 2 2 2 5
Course Level: UG Course Code: CSE201

Course Objectives: The objective of this course is to get students familiar with Databases and their use. They can identify different types of available
database model, concurrency techniques and new applications of the DBMS.
Pre-requisites: Knowledge about the raw data, knowledge about the operating system and organization

Course Contents/Syllabus:
Weightage (%)
Module I : Introduction
Descriptors/Topics 15%

Concept and goals of DBMS, Database Languages, Database Users, Database Abstraction. Database architecture,
The Relational Data Model and Relational Database Constraints, Basic Concepts of ER Mode l, Relationship sets,
Keys, Mapping, Design of ER Model
Module II: Relational Model
Descriptors/Topics 20%

The relational model , The catalog, Types, Keys, Relational algebra, Domain relational calculus, Tuple relational
calculus , F undamental operations, Additional operations, SQL fundamentals, Integrity , Triggers , Views
,Relational database, Relational Algebra, Relational & Tuple Calculus

Module III: Relational Database Design

Descriptors/Topics 15%

Normalization using F unctional Dependency, Multivalued dependency and Join depende ncy.

Module IV: Query Processing andOptimization, andDatabase Tuning

 Descriptors/Topics 25%

Translating SQL Q ueries into Relational Algebra, Algorithms for External Sorting, Algorithms for SELEC T and
JOIN Operations, Algorithms for PROJECT and SetOperations, Combining Operations Using P ipelining, Using
Heuristics in Q uery Optimization
Module V: Transaction Processing,Concurre ncy Control, Recovery and ne w application
Descriptors/Topics 25%

Introduction to Transaction Processing Concepts and Theory, Lock Based P rotocols, Time Stamped Based
Protocols, Deadlock Handling, Crash Recovery. Distributed Database, Objective Oriented Database, Multimedia
Database, Data Mining, Digital Libraries.

Student Learning Outcomes: On the successful completion of the course, the student will be able to
1. Create a conceptual schema from real world problem, and to define program-data independence, data models for database systems, database schema
and database instances.
2. Construct relational model and create database for querying with the help of relational algebra and SQL.
3. Design and modify a database such that it is free from anomalies using normalization techniques.
4. Apply query processing techniques for optimizing queries and database tuning.
5. Compare various concurrency control techniques and database security and recovery methods for various types of databases.

Pedagogy for Course Delivery:

 Classroom Teaching through lectures and presentation

 Case studies
 Lab work

List of Professional Skill Development Activities (PSDA):

i. Case Study
ii. Quiz
iii. Group Presentation
iv. Group Discussion

Lab/ Practicals details, if applicable:

 List of Experiments:

Based on the Employee & Department tables perform the following SQL queries

1. Write a query in SQL to display the last name and job title of all employees who do not have a manager

2. Write a query in SQL to display the last name, salary, and commission of all employees who earn commissions. Sort data in descending order of salary
and commissions.

3. Write a query in SQL that prompts the user for a manager ID and generates the employee ID, last name, salary, and department for that manager’s
employees. The HR department wants the ability to sort the report on a selected column.

4. Write a query in SQL to Display all employee last names in which the third letter of the name is a.

5. Write a query in SQL to Display the last name of all employees who have both an a and an e in their last name

6. Write a query in SQL to Display the last name, job, and salary for all employees whose job is sales representative or stock clerk and whose salary is not
equal to $2,500, $3,500, or $7,000.

7. Write a query in SQL to display the employee number, last name, salary, and salary increased by 15.5% (expressed as a whole number) for each
employee. Label the column New Salary.

8. Create a report that produces the following for each employee: <employee last name> earns <salary> monthly but wants <3 times salary>. Label the
column Dream Salaries.

9. Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with the $ symbol. Label the
column SALARY.

10. Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW.
Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”

11. Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week,
starting with Monday.

Open ended problem

Take an example of real world situation to and do the following

 1. Conceptual Design of Database (ER diagram)
2. Convert conceptual design to Relational database
3. Create a complete data base (using DDL & DML )

Assessment/ Examination Scheme:

Theory L/T (%) Lab/Practical/Studio (%)

80 20

Theory Assessment (L&T):

Continuous Assessment/Internal Assessment End Term
(40 %) Examination
(60%)
Components (Drop Attendance Class Test Home Assignment Case Discussion Quiz Group Group
down) Discussion Presentation

Linkage of PSDA 3 3 4
with Internal
Assessment
Component, if any
Weightage (%) 5 10 7 8 60

Lab/ Practical/ Studio Assessment:

Continuous Assessment/Internal Assessment End Term Examination

(40 %) (60 %)

Components (Drop down Attendance Lab Record Performance Viva Experiment Viva Total

Weightage (%) 5 15 10 10 30 30 60

Text Reading:
th
1. Korth, S ilberschatz, “Database S ystem Concepts”, 6 Ed., TMH, 2011.
 2. Steve Bobrowski, “Oracle & Architecture”, TMH, 2000

Reference Books:
th
1. Date C. J., “An Introduction to Database S ystems”, 7 Ed., Narosa Publishing, 2004
2. Elmsari and Navathe, “F undamentals of Database S ystems”, 6 th Ed., A. Wesley, 2010
3. Ullman J. D., “Principles of Database S ystems”, 2 nd Ed., Galgotia P ublications, 1999.