Teaching Scheme (in Hours)
| Theory |
Tutorial |
Practical |
Total |
| 4 |
0 |
2 |
5 |
Subject Credit : 5
Examination Scheme (in Marks)
Theory
ESE (E)
|
Theory
PA (M)
|
Practical
ESE Viva (V)
|
Practical
PA (I)
|
Total
|
| 70 |
30 |
30 |
20 |
150 |
Syllabus Content
Unit-1: Database System Architecture
Data Abstraction, Data Independence, Data Definition Language (DDL), Data Manipulation Language (DML)
Unit-2: Data Models
Entity-relationship model, network model, relational and object oriented data models, integrity constraints, data manipulation operations
Unit-3: 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
Unit-4: Relational database design
Domain and data dependency, Armstrong's axioms, Normal forms, Dependency preservation, Lossless design
Unit-5: Query processing and optimization
Evaluation of relational algebra expressions, Query equivalence, Join strategies, Query optimization algorithms
Unit-6: Storage strategies
Indices, B-trees, hashing
Unit-7: Transaction processing
Concurrency control, ACID property, Serializability of scheduling, Locking and timestamp based schedulers, Multi-version and optimistic Concurrency Control schemes, Database recovery
Unit-8: Database security
Authentication, Authorization and access control, DAC, MAC and RBAC models, Intrusion detection, SQL injection
Unit-9: SQL Concepts
Basics of SQL, DDL,DML,DCL, structure – creation, alteration, defining constraints – Primary key, foreign key, unique, not null, check, IN operator, aggregate functions, Built-in functions –numeric, date, string functions, set operations, sub-queries, correlated sub-queries, join, Exist, Any, All , view and its types., transaction control commands
Unit-10: PL/SQL Concepts
Cursors, Stored Procedures, Stored Function, Database Triggers
Course Outcome
- For a given query write relational algebra expressions for that query and optimize the developed expressions
- For a given specification of the requirement design the databases using ER method and normalization
- For a given specification construct the SQL queries for Open source and Commercial DBMS -MYSQL, ORACLE, and DB2
- For a given query optimize its execution using Query optimization algorithms
- For a given transaction-processing system, determine the transaction atomicity, consistency, isolation, and durability
