BITM Database Management System

Purpose of Database Systems. Data Abstraction. Data Models: The E-R Model, The Object-Oriented Model, The Relational Model, The Network Model, The Hierarchical Model, Physical Data Models. Instances and Schemes. Data Independence. Database Administrator. Database Users. Application Architecture (One tier, two tier and n-tire). Overall Database System Structure and Components.

Entities and Entity Sets. Relationships and Relationship Sets. Attributes. Mapping Constraints. Keys (Super key, Candidate key and Primary key): Primary Keys for Entity Sets and Relationship Sets. The Entity Relationship Diagram. Reducing E-R Diagrams to Tables: Representation of Strong Entity Sets, Representation of Weak Entity Sets, Representation of Relationship Sets. Generalization and Specialization. Aggregation. Mapping Cardinalities: Representation of Mapping Cardinalities in E-R Diagram. Use of Entity or Relationship Sets. Use of Extended E-R Features. Design of an E-R Database Scheme (Case study).

Background, Data Definition Language: Domain Types in SQL, Schema Definition in SQL. Data Manipulation Language: The select Clause, The where Clause, The from Clause, The Rename Operation, Tuple Variables, String Operations, Ordering the Display of Tuples, Duplicate Tuples. Set Operations. Aggregate Functions. Null Values. Nested Subqueries: Set Membership, Set Comparison, Test for Empty Relations, Test for the Absence of Duplicate Tuples. Derived Relations: Views. Modification of the Database: Deletion, Insertion, Updates, Updates, Update of a view. Joined Relations: Join types and Conditions. Embedded SQL. Dynamic SQL. Transaction Control Language (Commit, Rollback).

Domain Constraints. Referential Integrity: Basic Concepts, Referential Integrity in the E-R Model, Database Modification, Referential Integrity in SQL.

Pitfalls in Relational DB Design. Representation of Information: Anomalies. Functional Dependencies: Basic Concepts, Closure of a Set of Functional Dependencies, Closure of Attribute Sets. Decomposition: Lossless-Join Decomposition, Dependency Preservation. Normalization: First Normal Form, Second Normal Form, Third Normal Form, Boyce-Codd Normal Form, Comparison of BCNF and 3NF.

ACID Properties. Transaction States: Implementation of Atomicity and Durability, Serializability, Basic Concept of Concurrency Control and Recovery, Locking Protocols, Time Stamp Based Protocol.

MSSQL server, ORACLE, MYSQL.