Detailed Contents

Module 1: Database system architecture

• Data Abstraction, Data Independence, Data Definition Language (DDL), Data Manipulation Language (DML).
• Data models: Entity-relationship model, network model, relational and object-oriented Data models, integrity constraints, data manipulation operations.

Module 2: Relational query languages

• Relational algebra, Tuple and domain relational calculus, SQL3, DDL and DML constructs, Open source and Commercial DBMS - MYSQL, ORACLE, DB2, SQL server.
• Relational database design: Domain and data dependency, Armstrong's axioms, Normal forms, Dependency preservation, Lossless design.
• Query processing and optimization: Evaluation of relational algebra expressions, Query equivalence, Join strategies, Query optimization algorithms.

Module 3:

• Storage strategies, Indices, B-trees, hashing.

Module 4: Transaction processing

• Concurrency control, ACID property, Serializability of scheduling, Locking and timestamp-based schedulers, Multi-version and optimistic Concurrency Control schemes, Database recovery.

Module 5: Database Security

• Authentication, Authorization and access control, DAC, MAC and RBAC models, Intrusion detection, SQL injection.

Module 6: Advanced Topics

• Object-oriented and object-relational databases, Logical databases, Web databases, Distributed databases.

Course Outcomes

At the end of the study the student shall be able to:

• CO1: Write relational algebra expressions for a query and optimize the developed expressions.
• CO2: Design databases using the ER method and normalization.
• CO3: Construct SQL queries for Open source and Commercial DBMS - MYSQL, ORACLE, and DB2.
• CO4: Determine the transaction atomicity, consistency, isolation, and durability.
• CO5: Implement the isolation property, including locking, time stamping based on concurrency control and Serializability of scheduling.

Text Books

• “Database System Concepts”, 6th Edition by Abraham Silberschatz, Henry F. Korth, S. Sudarshan, McGraw-Hill.

Reference Books

• “Principles of Database and Knowledge–Base Systems”, Vol1 by J. D. Ullman, Computer Science Press.
• “Fundamentals of Database Systems”, 5th Edition by R. Elmasri and S. Navathe, Pearson Education.
• “Foundations of Databases”, Reprint by Serge Abiteboul, Richard Hull, Victor Vianu, Addison-Wesley.

List of Experiments

1. Introduction to SQL and installation of SQL Server / Oracle.
2. Data Types, Creating Tables, Retrieval of Rows using Select Statement, Conditional Retrieval of Rows, Alter and Drop Statements.
3. Working with Null Values, Matching a Pattern from a Table, Ordering the Result of a Query, Aggregate Functions, Grouping the Result of a Query, Update and Delete Statements.
4. Set Operators, Nested Queries, Joins, Sequences.
5. Views, Indexes, Database Security and Privileges: Grant and Revoke Commands, Commit and Rollback Commands.
6. PL/SQL Architecture, Assignments and Expressions, Writing PL/SQL Code, Referencing Non-SQL parameters.
7. Stored Procedures and Exception Handling.
8. Triggers and Cursor Management in PL/SQL.

Suggested Tools

• MySQL, DB2, Oracle, SQL Server 2012, Postgre SQL, SQL lite

Course Outcomes

• CO1: This practical will enable students to retrieve data from relational databases using SQL.
• CO2: Students will be able to implement the generation of tables using datatypes.
• CO3: Students will be able to design and execute various data manipulation queries.
• CO4: Students will also learn to execute triggers, cursors, stored procedures etc.