RAJIV GANDHI PROUDYOGIKI VISHWAVIDYALAYA, BHOPAL
New Scheme Based On AICTE Flexible Curricula
Computer Science and Engineering, V-Semester
CS502 Database Management Systems
Course Outcomes: After completion of the course students will be able to
1. Understand the different issues involved in the design and implementation of a database
system.
2. Study the physical and logical database designs, database modeling, relational,
hierarchical, and network models
3. Understand and use data manipulation language to query, update, and manage a database
4. Develop an understanding of essential DBMS concepts such as: database security,
integrity, concurrency,
5. Design and build a simple database system and demonstrate competence with the
fundamental tasks involved with modeling, designing, and implementing a DBMS.
6. Evaluate a business situation and designing & building a database applications
Unit I
DBMS Concepts and architecture Introduction, Database approach v/s Traditional file
accessing approach, Advantages, of database systems, Data models, Schemas and instances,
Data independence, Data Base Language and interfaces, Overall Database Structure,
Functions of DBA and designer, ER data model: Entitles and attributes, Entity types,
Defining the E-R diagram, Concept of Generalization, Aggregation and Specialization.
transforming ER diagram into the tables. Various other data models object oriented data
Model, Network data model, and Relational data model, Comparison between the three types
of models.
Unit II
Relational Data models: Domains, Tuples, Attributes, Relations, Characteristics of relations,
Keys, Key attributes of relation, Relational database, Schemas, Integrity constraints.
Referential integrity, Intension and Extension, Relational Query languages: SQL-DDL,
DML, integrity con straints, Complex queries, various joins, indexing, triggers, assertions ,
Relational algebra and relational calculus, Relational algebra operations like select, Project
,Join, Division, outer union. Types of relational calculus i.e. Tuple oriented and domain
oriented relational calculus and its operations.
Unit III
Data Base Design: Introduction to normalization, Normal forms, Functional dependency,
Decomposition, Dependency preservation and lossless join, problems with null valued and
dangling tuples, multivalued dependencies. Query Optimization: Introduction, steps of
optimization, various algorithms to implement select, project and join operations of relational
algebra, optimization methods: heuristic based, cost estimation based.
Unit IV
Transaction Processing Concepts: -Transaction System, Testing of Serializability,
Serializability of schedules, conflict & view serializable schedule, recoverability, Recovery
from transaction failures. Log based recovery. Checkpoints deadlock handling. Concurrency
Control Techniques: Concurrency Control, locking Techniques for concurrency control, time
stamping protocols for concurrency control, validation based protocol, multiple granularity.
Multi version schemes, Recovery with concurrent transaction. Introduction to Distributed
databases, data mining, data warehousing, Object Technology and DBMS, Comparative
study of OODBMS Vs DBMS . Temporal, Deductive, Multimedia, Web & Mobile database.
Unit V
Study of Relational Database Management Systems through Oracle/PL SQL
QL/MySQL: Architecture, physical files, memory structures, background process. Concept
of table spaces, segments, extents and block. Dedicated server, multi threaded server.
Distributed database, database links, and snapshot. Data dictionary, dynamic performance
view. Security, role management, privilege management, profiles, invoker defined security
model. SQL queries, Data extraction from single, multiple tables equi- join, non equi-join,
self -join, outer join. Usage of like, any, all, exists, in Special operators. Hierarchical quires,
inline queries, flashback queries. Introduction of ANSI SQL, anonymous block, nested
anonymous block, branching and looping constructs in ANSI SQL. Cursor management:
nested and parameterized cursors, Oracle exception handling mechanism. Stored procedures,
in, out, in out type parameters, usage of parameters in procedures. User defined functions
their limitations. Triggers, mutating errors, instead of triggers.
Suggested list of experiments:- Lab Assignments:
1. Delete duplicate row from the table.
2. Display the alternate row from table.
3. Delete alternate row from table.
4. Update multiple rows in using single update statement.
5. Find the third highest paid and third lowest paid salary.
6. Display the 3rd, 4th, 9th rows from table.
7. Display the ename, which is start with j, k, l or m.
8. Show all employees who were hired the first half of the month.
9. Display the three record in the first row and two records in the second row and one record
in the third row in a single sql statements.
10. Write a sql statements for rollback commit and save points.
11. Write a pl/sql for select, insert, update and delete statements.
12. Write a pl/sql block to delete a record. If delete operation is successful return 1 else
return 0.
13. Display name, hire date of all employees using cursors.
14. Display details of first 5 highly paid employees using cursors.
15. Write a database trigger which fires if you try to insert, update, or delete after 7’o’ clock.
16. Write a data base trigger, which acts just like primary key and does not allow duplicate
values.
17. Create a data base trigger, which performs the action of the on delete cascade.
18. Write a data base trigger, which should not delete from emp table if the day is Sunday.
19. In this subject the students are supposed to prepare a small database application in
complete semester like financial accounting system, Railway reservation system,institute
timetable management system. Student record system, library management system, hospital
management system etc. in RDBMS as follows:
Section A:
Solving the case studies using ER datamodel (design of the database)
Section B:
Implement a miniproject for the problem taken in section A.
Suggested Reading:-
1. Date C J, “An Introduction To DatabaseSystem”, Pearson Educations
2. Korth, Silbertz,Sudarshan, “Fundamental of Database System”, McGraw Hill
3. Rob, “ Data Base System:Design Implementation & Management”, Cengage Learninig
4. Elmasri, Navathe, “Fundamentals Of Database Systems”, Pearson Educations
5 . Atul Kahate , “ Introduction to Database Management System”, Pearson Educations
6. Oracle 9i Database Administration Fundamental-I, Volume I, Oracle Press,TMH.
7. Paneerselvam,”DataBase Management System”, PHI Learning
8. dev.mysql.com
9. www.postgressql.org
