DATABASE MANAGEMENT SYSTEMS
Semester Course Code Course Title Hours Credits
III C3 DATABASE MANAGEMENT 60 3
SYSTEMS
Course Objective:
The objective of the course is to introduce the design and development of databases with
special emphasis on relational databases.
Course Learning Outcomes:
On completing the subject, students will be able to:
1. Gain knowledge of Database and DBMS.
2. Understand the fundamental concepts of DBMS with special emphasis on relational
data model.
3. Demonstrate an understanding of normalization theory and apply such knowledge to
the normalization of a database
4. Model database using ER Diagrams and design database schemas based on the model.
5. Create a small database using SQL.
6. Store, Retrieve data in database.
UNIT I
Overview of Database Management System: Introduction to data, information, database,
database management systems, file-based system, Drawbacks of file-Based System, database
approach, Classification of Database Management Systems, advantages of database approach,
Various Data Models, Components of Database Management System, three schema
architecture of data base, costs and risks of database approach.
UNIT II
Entity-Relationship Model: Introduction, the building blocks of an entity relationship
diagram, classification of entity sets, attribute classification, relationship degree, relationship
classification, reducing ER diagram to tables, enhanced entity-relationship model (EER
model), generalization and specialization, IS A relationship and attribute inheritance,
multiple inheritance, constraints on specialization and generalization, advantages of ER
modeling.
UNIT III
Relational Model: Introduction, CODD Rules, relational data model, concept of key,
relational integrity, relational algebra, relational algebra operations, advantages of relational
algebra, limitations of relational algebra, relational calculus, tuple relational calculus, domain
relational Calculus (DRC), Functional dependencies and normal forms upto 3rd normal form.
UNIT IV
Structured Query Language: Introduction, History of SQL Standard, Commands in SQL,
Data Types in SQL, Data Definition Language, Selection Operation, Projection Operation,
Aggregate functions, Data Manipulation Language, Table Modification Commands, Join
Operation, Set Operations, View, Sub Query.
UNIT V
PL/SQL: Introduction, Shortcomings of SQL, Structure of PL/SQL, PL/SQL Language
Elements, Data Types, Operators Precedence, Control Structure, Steps to Create a PL/SQL,
Program, Iterative Control, Procedure, Function, Database Triggers, Types of Triggers.
BOOKS:
1. Database System Concepts by Abraham Silberschatz, Henry Korth, and S. Sudarshan,
McGrawhill
2. Database Management Systems by Raghu Ramakrishnan, McGrawhill
3. Principles of Database Systems by J. D. Ullman
4. Fundamentals of Database Systems by R. Elmasri and S. Navathe
5. SQL: The Ultimate Beginners Guide by Steve Tale.
RECOMMENDED CO-CURRICULAR ACTIVITIES:
(Co-curricular activities shall not promote copying from textbook or from others work and
shall encourage self/independent and group learning)
A. Measurable
1. Assignments (in writing and doing forms on the aspects of syllabus content and
outside the syllabus content. Shall be individual and challenging)
2. Student seminars (on topics of the syllabus and related aspects (individual activity))
3. Quiz (on topics where the content can be compiled by smaller aspects and data
(Individuals or groups as teams))
4. Study projects (by very small groups of students on selected local real-time problems
pertaining to syllabus or related areas. The individual participation and contribution of
students shall be ensured (team activity
B. General
1. Group Discussion
2. Try to solve MCQ‟s available online.
3. Others
RECOMMENDED CONTINUOUS ASSESSMENT METHODS:
Some of the following suggested assessment methodologies could be adopted;
1. The oral and written examinations (Scheduled and surprise tests),
2. Closed-book and open-book tests,
3. Practical assignments and laboratory reports,
4. Observation of practical skills,
5. Individual and group project reports like Create your college database for placement
purpose.
6. Efficient delivery using seminar presentations,
7. Viva voce interviews.
8. Computerized adaptive testing, literature surveys and evaluations,
9. Peers and self-assessment, outputs form individual and collaborative work
Semester Course Code Course Title Hours Credits
III C3-P DATABASE MANAGEMENT 30 2
SYSTEMS LAB
1. Draw ER diagram for hospital administration
2. Creation of college database and establish relationships between tables
3. Relational database schema of a company is given in the following figure.
Relational Database Schema - COMPANY
Questions to be performed on above schema
1. Create above tables with relevant Primary Key, Foreign Key and other constraints
2. Populate the tables with data
3. Display all the details of all employees working in the company.
4. Display ssn, lname, fname, address of employees who work in department no 7.
5. Retrieve the Birthdate and Address of the employee whose name is 'Franklin T.
Wong'
6. Retrieve the name and salary of every employee
7. Retrieve all distinct salary values
8. Retrieve all employee names whose address is in „Bellaire‟
9. Retrieve all employees who were born during the 1950s
10. Retrieve all employees in department 5 whose salary is between 50,000 and
60,000(inclusive)
11. Retrieve the names of all employees who do not have supervisors
12. Retrieve SSN and department name for all employees
13. Retrieve the name and address of all employees who work for the 'Research'
department
14. For every project located in 'Stafford', list the project number, the controlling
department number, and the department manager's last name, address, and birth date.
15. For each employee, retrieve the employee's name, and the name of his or her
immediate supervisor.
16. Retrieve all combinations of Employee Name and Department Name
17. Make a list of all project numbers for projects that involve an employee whose last
name is 'Narayan‟ either as a worker or as a manager of the department that controls
the project.
18. Increase the salary of all employees working on the 'ProductX' project by 15%.
Retrieve employee name and increased salary of these employees.
19. Retrieve a list of employees and the project name each works in, ordered by the
employee's department, and within each department ordered alphabetically by
employee first name.
20. Select the names of employees whose salary does not match with salary of any
employee in department 10.
21. Retrieve the employee numbers of all employees who work on project located in
Bellaire, Houston, or Stafford.
22. Find the sum of the salaries of all employees, the maximum salary, the minimum
salary, and the average salary. Display with proper headings.
23. Find the sum of the salaries and number of employees of all employees of the
„Marketing‟ department, as well as the maximum salary, the minimum salary, and the
average salary in this department.
24. Select the names of employees whose salary is greater than the average salary of all
employees in department 10.
25. Delete all dependents of employee whose ssn is ‘123456789’.
26. Perform a query using alter command to drop/add field and a constraint in Employee
table.
