Grade "A+" Accredited by NAAC with a CGPA of 3.46
Grade "A+" Accredited by NAAC with a CGPA of 3.46

Database Management Systems

Course ID
BHCS 10
Level
Undergraduate
Program
B.Sc. CS (Hons.)
Semester
Fourth
Credits
6.0
Paper Type
Core Course
Method
Lecture & Practical

Unique Paper Code: Update Awaited

The course introduces the foundations of database management systems focusing on significance of a database, relational data model, schema creation and normalization, transaction processing, indexing, and the relevant data structures (files and B+-trees).

Learning Outcomes:

At the end of the course, students should be able to:

  • Describe major components of DBMS and their functions.
  • Model an application’s data requirements using conceptual modelling tools like ER diagrams and design database schemas based on the conceptual model.
  • Write queries in relational algebra / SQL.
  • Normalize a given database schema to avoid data anomalies and data redundancy.
  • Describe the notions of indexes, views, constraints and transactions.

Course Contents

Unit 1
Unit 2
Unit 3
Unit 4
Unit 5
Unit 6

Unit 1

Introduction to databases: Characteristics of database approach, data models, database system architecture, data independence and data abstraction.

Unit 2

Data modeling: Entity relationship (ER) modeling: Entity types, relationships, constraints, ER diagrams, EER model.

Unit 3

Relation data model: Relational model concepts, relational constraints, relational algebra.

Unit 4

SQL queries: SQL data definition, data types, specifying constraints, Queries for retrieval, insertion, deletion, updation, introduction to views.

Unit 5

Database design: Mapping ER/EER model to relational database, functional dependencies, Lossless decomposition, Normal forms (upto BCNF).

Unit 6

Transaction and data storage: Introduction to transaction processing: ACID properties, concurrency control; Introduction to indexing structures for files.

Practicals

Lab List 1

Create and use the following database schema to answer the given queries.
EMPLOYEE Schema
Field Type NULL KEY
DEFAULT
Eno Char(3) NO PRI NIL
Ename Varchar(50) NO NIL
Job_type Varchar(50) NO NIL
Manager Char(3) Yes FK NIL
Hire_date Date NO NIL
Dno Integer YES FK NIL
Commission Decimal(10,2) YES NIL
Salary Decimal(7,2) NO NIL
DEPARTMENT Schema
Field Type NULL KEY DEFAULT
Dno Integer No PRI NULL Dname Varchar(50) Yes NULL
Location Varchar(50) Yes New Delhi
Query List

  1. Query to display Employee Name, Job, Hire Date, Employee Number; for each employee with the Employee Number appearing first.
  2. Query to display unique Jobs from the Employee Table.
  3. Query to display the Employee Name concatenated by a Job separated by a comma.
  4. Query to display all the data from the Employee Table. Separate each Column by a comma and name the said column as THE_OUTPUT.
  5. Query to display the Employee Name and Salary of all the employees earning more than $2850.
  6. Query to display Employee Name and Department Number for the Employee No= 7900.
  7. Query to display Employee Name and Salary for all employees whose salary is not in the range of $1500 and $2850.
  8. Query to display Employee Name and Department No. of all the employees in Dept 10 and Dept 30 in the alphabetical order by name.
  9. Query to display Name and Hire Date of every Employee who was hired in 1981.
  10. Query to display Name and Job of all employees who don’t have a current Manager.

Lab List 2

  1. Query to display the Name, Salary and Commission for all the employees who earn commission.
  2. Sort the data in descending order of Salary and Commission.
  3. Query to display Name of all the employees where the third letter of their name is ‘A’.
  4. Query to display Name of all employees either have two ‘R’s or have two ‘A’s in their name and are either in Dept No = 30 or their Manger’s Employee No = 7788.
  5. Query to display Name, Salary and Commission for all employees whose Commission amount is 14 greater than their Salary increased by 5%.
  6. Query to display the Current Date.
  7. Query to display Name, Hire Date and Salary Review Date which is the 1st Monday after six months of employment.
  8. Query to display Name and calculate the number of months between today and the date each employee was hired.
  9. Query to display the following for each employee earns < Salary> monthly but wants < 3 * Current Salary >. Label the Column as Dream Salary.
  10. Query to display Name with the 1st letter capitalized and all other letter lower case and length of their name of all the employees whose name starts with ‘J’, ’A’ and ‘M’.

Lab list 3

  1. Query to display Name, Hire Date and Day of the week on which the employee started.
  2. Query to display Name, Department Name and Department No for all the employees.
  3. Query to display Unique Listing of all Jobs that are in Department # 30.
  4. Query to display Name, Dept Name of all employees who have an ‘A’ in their name.
  5. Query to display Name, Job, Department No. And Department Name for all the employees working at the Dallas location.
  6. Query to display Name and Employee no. Along with their Manger’s Name and the Manager’s employee no; along with the Employees’ Name who do not have a Manager.
  7. Query to display Name, Dept No. And Salary of any employee whose department No. and salary matches both the department no. And the salary of any employee who earns a commission.
  8. Query to display Name and Salaries represented by asterisks, where each asterisk (*) signifies $100.
  9. Query to display the Highest, Lowest, Sum and Average Salaries of all the employees
  10. Query to display the number of employees performing the same Job type functions.

Lab List 4

  1. Query to display the no. of managers without listing their names.
  2. Query to display the Department Name, Location Name, No. of Employees and the average salary for all employees in that department.
  3. Query to display Name and Hire Date for all employees in the same dept. as Blake.
  4. Query to display the Employee No. And Name for all employees who earn more than the average salary.
  5. Query to display Employee Number and Name for all employees who work in a department with any employee whose name contains a ‘T’.
  6. Query to display the names and salaries of all employees who report to King.
  7. Query to display the department no, name and job for all employees in the Sales department.

Additional Information

Text Books


Elmasri, R., & Navathe, S.B. (2015). Fundamentals of Database Systems. 7th edition. Pearson Education.

Additional Resources


Date, C. J. (2004). An Introduction to database systems. 8th edition. Pearson Education.
Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. 6th edition. McGrawHill.

Teaching Learning Process


Use of ICT tools in conjunction with traditional class room teaching methods
Interactive sessions
Class discussions

Assessment Methods

Written tests, assignments, quizzes, presentations as announced by the instructor in the class

Keywords

Entity-Relationship Modeling, Database Design, Transaction Processing, noSQL systems.

Disclaimer: Details on this page are subject to change as per University of Delhi guidelines. For latest update in this regard please refer to the University of Delhi website here.