Database Management System

 Database Management System

Contents

Unit 1: Database System Introduction(6)

1.1.   Basic  Terminologies: Data vs Information, Data Hierarchy, Database, Database Management System, Database System, Relational Database Management Systems.

1.2.   Data Management Approaches: File Management Systems, Database Management Systems, Limitations, Advantages and Applications.

1.3.   Database Schema and Instance, Data Abstraction (views of Data), Data Independence, Database Languages, Database Users and Administrator.

1.4.   Data Models: Hierarchical, Network, Entity Relationship, Relational and object oriented data model

1.5.   Database Application Architecture, Classification of DBMSs.

Practical Work

•             Demonstrate Creation and manipulation of Tables by using MS Access

Unit 2: Entity Relationship Data Modeling (8)

2.1.  ER Model and ER Diagrams, Components of ER Model, Types of Attributes.

2.2.  Degree of Relationship, Constraints on ER Model (Mapping Cardinalities and Participation Constraints), Keys and Types of Keys, Weak Entity Sets.

2.3.  Extended ER Modeling: Subclass/Super class Relationship, Specialization and Generalization, Constraints on Specialization/Generalization Aggregation.

2.4.  Relational Model: Introduction, Structure of Relational Databases, Schema Diagram, Mapping ER Model to Relational Database.

Practical Works:

•             Draw ER diagrams by using CASE Tools

•             Practice Conversion of ER model to Relational model

Unit 3: Relational Algebra (10)

3.1.  Introduction of Relational Algebra (RA), Fundamental Operations of RA: Select, Project, Set Union, Set Difference, Cartesian product and Rename Operations.

3.2.  Additional Relational Algebra Operations: Set Intersection, Natural Join, Division and Assignment Operation.

3.3.  Extended Relational Algebra Operations: Generalized Projection, Outer Join and Aggregate Functions

3.4.  Database Modification: Insert, Delete and Update Operation.

3.5.  Null Values, Advantages and Limitations of Relational Algebra

Unit IV: Structured Query Language (20)

4.1.  Introduction: Basic Structure of SQL Query, SELECT,

FROM and WHERE clause, Using Multiple Relations

4.2.  String/Pattern Matching, Ordering the Display of

Tuples, Join Operations: Join Types and Join

Conditions.

4.3.  Nested Queries: Set membership Test, Set Comparison

and Test for Empty Relations.

4.4.  Aggregate Functions, Group by Clause and Having Clause

4.5.  Database Modifications: Insert, Delete and Update Operations

4.6.  Data Definition Language: Domain Types in SQL, Create, Alter and Drop statements

4.7.  View and Modification of Views, Embedded and Dynamic SQL

Practical Works:               

•             Create relational database by using create statements

•             Populate tables with data by using INSERT statement•  

Unit 5:  Integrity Constraints (8)

5.1.  Concept and Importance of Integrity Constraints, Data Integrity.

5.2.  Domain  Constraints: Not Null Constraints, Unique Constraints, Primary key Constraints, Check Constraints.

5.3.  Referential Integrity: Using Referential Integrity, Cascading Actions

5.4.  Assertions and Triggers: Creating and Deleting Assertions, Creating and Deleting Triggers, Assertions vs Triggers.

Practical Works:

•             Demonstrate use of Domain constrains and referential integrity

•             Create assertions and triggers

Unit 6: Relational Database Design (8)

6.1. Introduction, Database Modification Anomalies, Functional  Dependencies  (FDs),  Types  of  FD’s,  FD Inference Rules.

6.2.  Normalization: Purpose and Concept of Normalization, Forms of Normalization: 1-NF, 2-NF, 3-NF, BCN

6.3. Lossless Decomposition

•             Demonstrate Database modification anomalies

Unit 7: Database Security and Indexing (8)

7.1. Authentication vs, Authorization, Classification of DB

Security, Levels of DB Security.

7.2. Types of Authorization, Creating Users, Granting and

Revoking Authorizations in SQL, Concept of Roles,

Authorization using Roles.

7.3. Concept of Indexing, Index File vs Data File, Index key

Structure, Types of Indices

7.4. Primary Indices: Dense and Sparse Indices with their

Strengths and Drawbacks, Indexing Evaluation.

Practical Works:

•             Demonstrate GRANT and REVOKE statements

•             CREATE and DROP indices

Unit 8: Introduction to Transaction and Recovery (8)

8.1. Transaction Processing: Transaction  concepts, Transaction

Operations, Desirable Properties of Transactions, Transaction States, Schedule, Serial, Non-serial and Serializable Schedule.

8.2. Concurrency Control: Introduction, Need of Concurrency Control, Lock-Based Protocols

8.3. Database Recovery: Need of Recovery, Concept of Recovery, Log Based Recovery, Write Ahead Logging, Check pointing

Unit 9: Emerging Database Technology and Application (4)

9.1 Concept of Big data

9.2 Concept of No SQL

9.3 Concept of Mobile and Multimedia data

9.4 Concept of GIS database

9.5          Concept of Data Warehouse and Data Mining