(Computer Science Courses for Undergraduate Programme of study with Computer
Science discipline as one of the three Core Disciplines)
DISCIPLINE SPECIFIC CORE COURSE (DSC-5): Database Management Systems
CREDIT DISTRIBUTION, ELIGIBILITY AND PRE-REQUISITES OF THE
COURSE
Course title Credits Credit distribution of the course Eligibility Pre-requisite
& Code criteria of the course
Lectur Tutorial Practical/
e Practice
DSC 5: 4 3 0 1 Pass in NIL
Database Class XII
Managemen
t Systems
Learning Objectives
The course introduces the students to the fundamentals of database management system and its
applications. Emphasis is given on the popular relational database system. Students will learn about
the importance of database structure and its designing using Entity Relationship diagram and formal
approach using normalization. Basic concepts of file indexing and transaction processing will be
taught. The course would give students hands-on practice of structured query language to create,
manipulate and implement a relational database.
Learning outcomes
On successful completion of the course, students will be able to:
● Use relational database management software to create and manipulate the database.
● Create conceptual data models using entity relationship diagrams for modeling real-life
situations and map it to corresponding relational database schema.
● Use the concept of functional dependencies to remove redundancy and update
anomalies.
● Apply normalization theory to get a normalized database scheme to get anomalies free
database.
● Write queries in relational algebra.
● Implement relational databases and formulate queries for data retrieval and data update
problems using SQL.
● Learn the importance of index structures and concurrent execution of transactions in
database systems.
291
SYLLABUS OF DSC-5
Unit 1 (5 hours)
Introduction to Database: Database, characteristics of database approach, data models,
database management system, three-schema architecture, components of DBMS, data
independence, and file system approach vs database system approach.
Unit 2 (6 hours)
Entity Relationship Modeling: Conceptual data modeling - motivation, entities, entity types,
attributes, relationships, relationship types, constraints on relationship, Entity Relationship
diagram as conceptual data model.
Unit 3 (6 hours)
Relational Data Model: Data anomalies, Relational Data Model - Characteristics of a
relation, schema-instance distinction, types of keys, relational integrity constraints. Relational
algebra operators like selection, projection, cartesian product, join and write queries using
them.
Unit 4 (8 hours)
Structured Query Language (SQL): DDL to create database and tables, table constraints,
DML, Querying in SQL to retrieve data from the database, aggregation functions group by and
having clauses, generate and query views.
Unit 5 (10 hours)
Database Design: Mapping an Entity Relationship diagram to corresponding relational
database scheme, functional dependencies and Normal forms, 1NF, 2NF, and 3NF
decompositions and desirable properties of them.
Unit 6 (10 hours)
File indexing and Transaction Processing: Need of file indexes, types of indexes, file
organizations, single- and multi-level indexing, concurrent execution of transactions, ACID
properties, need of data recovery.
Essential/recommended readings
1. Elmasri, R., Navathe, B. S., Fundamentals of Database Systems, 7th edition, Pearson
Education, 2016.
2. Murach, J., Murach's MySQL, 3th edition, Pearson, 2019.
Additional References
292
1. Connolly, T. M., Begg, C. E., Database Systems: A Practical Approach to Design,
Implementation, and Management, 6th edition, Pearson, 2019.
2. Ramakrishnan, R., Gehrke, J., Database Management Systems, 3rd edition, McGraw-Hill,
2014.
3. Silberschatz, A., Korth, H.F., Sudarshan S., Database System Concepts, 7th edition,
McGraw Hill, 2019.
Suggested Practical List : (30 Hours)
Practical exercises such as
Create and use the following student-course database schema for a college to answer the given
queries using the standalone SQL editor.
STUDENT Roll No StudentName CourseID DOB
Char(6) Varchar(20) Varchar(10) Date
COURSE CID CourseName Course Teacher-in- TotalSeats Duration
Type charge
Char(6) Varchar Char Varchar Unsigned Unsigned
(20) (8) (15) int int
ADMISSION Roll No CID DateOfAdmission
Char(6) Char(6) Date
Here Rollno (ADMISSION) and SID (ADMISSION) are foreign keys. Note that course type
may have two values viz. Fulltime and Parttime and a student may enroll in any number of
courses
1. Retrieve names of students enrolled in any course.
3. Retrieve students' names starting with letter ‘A’.
4. Retrieve students' details studying in courses ‘computer science’ or ‘chemistry’.
5. Retrieve students’ names whose roll no either starts with ‘X’ or ‘Z’ and ends with ‘9’
6. Find course details with more than N students enrolled where N is to be input by the
user
7. Update student table for modifying a student name.
8. Find course names in which more than five students have enrolled
9. Find the name of youngest student enrolled in course ‘BSc(P)CS’
10. Find the name of most popular society (on the basis of enrolled students)
11. Find the name of two popular part time courses (on the basis of enrolled students)
12. Find the student names who are admitted to full time courses only.
13. Find course names in which more than 30 students took admission
293
14. Find names of all students who took admission to any course and course names in which
at least one student has enrolled
15. Find course names such that its teacher-in-charge has a name with ‘Gupta’ in it and
the course is full time.
16. Find the course names in which the number of enrolled students is only 10% of its
total seats.
17. Display the vacant seats for each course
18. Increment Total Seats of each course by 10%
19. Add enrollment fees paid (‘yes’/’No’) field in the enrollment table.
20. Update date of admission of all the courses by 1 year.
21. Create a view to keep track of course names with the total number of students enrolled
in it.
22. Count the number of courses with more than 5 students enrolled for each type of course.
23. Add column Mobile number in student table with default value ‘9999999999’
24. Find the total number of students whose age is > 18 years.
25. Find names of students who are born in 2001 and are admitted to at least one part time
course.
26. Count all courses having ‘science’ in the name and starting with the word ‘BSc’.
Students are also encouraged to implement the database given in the textbook and do the
related queries.
294
