Course

Syllabus

Suggested lab exercises:

1. Working with SQL queries for the following
   1. Data Definition Language: Create, Alter, Grant, Revoke, Drop, Truncate.
   2. Data Manipulation Language: Select, Insert, Update, Delete.
   3. Transaction Control Statements: Commit, Rollback, Save point.  
2. Working with Constraints and keys in SQL.
3. Apply Functions: String functions, Aggregate functions, Group by, Order by, Date and Conversion Functions.
4. Set operators, Joins, Sub query: Simple, Nested, Correlated, Existence test, Membership test, DDL and Sub queries and DML.  
5. Working with other schema objects: View, Sequence, Index, Synonym, Cluster, Lock, BLOB, CLOB, Nested table, Type.
6. PL/SQL programs, cursors, functions, procedures, packages, triggers, exception handling.  
7. Working with Complex Data Types
8. Front end tool: Form creation, Validation, Trigger and Report generation.

Unit 1

Introduction to DBMS: Database systems applications – Purpose of database systems View of Data Database Languages. Relational Languages: Structure of relational databases Database schema – Keys Schema diagrams. Relational Query Languages Relational Algebra. Introduction to SQL: Overview SQL Data definition Basic Structure of SQL queries Additional Basic Operations – Set operations Null values – Aggregate functions Nested Subqueries Modification of the Database.

Unit 2

Database Design – Overview of the design process The Entity-Relationship Model Complex Attributes Mapping Cardinalities Primary Key Reducing E-R Diagrams to Relational Schemas – Extended E-R Features Entity-Relationship Design Issues. Intermediate SQL: Join Expressions Views Transactions Integrity Constraints SQL Data Types and Schemas – Authorization. Advanced SQL Accessing SQL from a Programming Language Functions and Procedures Triggers.

Unit 3

Relational Database Design Features of Good Relational Designs Decomposition using Functional Dependencies Normal Forms :1NF, 2NF, 3NF, 4NF and BCNF- Functional Dependency Theory Algorithm for Decomposition using Functional Dependencies -Decomposition using Multivalued Dependencies More Normal Forms: PJNF and DKNF. Complex Data Types: Semi-Structured Data.Transaction Management: Transactions A simple Transaction Model Transaction Atomicity and Durability Transaction Isolation Serializability Concurrency Control: Lock Based Protocols.

Text Books / References

Text Book:

1. A., Korth, H. F. and Sudharshan, S. “Database System Concepts”, 7th Edition, Tata Mc Graw Hill Publishing, 2020.

Reference Books

1. Elmasri, R. and Navathe, S. B. “Fundamentals of Database Systems”, 7th Edition, Addison Wesley, 2017
2. Date, C. J. “An Introduction to Database Systems”, 8th Edition, Addison Wesley, 2003.
3. Ramakrishnan, R. and Gehrke, J. “Database Management Systems”, 3rd Edition, McGrawHill, 2003.
4. Bipin C Desai, “An introduction to Database Systems”, Galgotia Publications, 2012

This course provides a comprehensive introduction to Database Management Systems, focusing on both theoretical and practical SQL applications. The course explores concepts such as Data Models, EntityRelationship diagrams, Relational Algebra, Normalization, Transaction management and concurrency control. The laboratory component offers hands-on experience in creating databases, executing queries, implementing triggers and views, utilizing indexing methods, and addressing real world database design issues

Course Outcomes: After successful completion of the course, students will be able to

• CO1 : Understand the basic concepts of Database Management Systems, including data models, relational structures, database languages, and relational algebra.
• CO2 : Construct Entity–Relationship diagrams and convert E-R diagrams into relational schemas while applying appropriate key and mapping constraints.
• CO3 : Construct and execute SQL queries using basic, intermediate, and advanced SQL features including joins, nested subqueries, views, triggers, transactions, and integrity constraints.
• CO4 : Analyse and apply normalization techniques using functional and multivalued dependency theory to design efficient and redundancy-free relational database schemas.
• CO5 : Implement transaction management and concurrency control mechanisms, including serializability and lock-based protocols, to ensure data consistency and reliability.

CO-PO Mapping: