DATABASE DESIGN

By a Single 3 hour examinations plus 2 piece of Coursework

1. To build an awareness of the need for a database system and its role in an organisation.
2. To provide a detailed understanding of the principles of database processing.
3. To enable participants to apply the acquired skills and techniques for the design and implementation of a database using a suitable relational database package.
4. To have an awareness of future DB applications and their new requirements that satisfies such needs.

Review of traditional processing & its limitations; Evolution of Database systems; Database Applications & users; Types of DBMSs; Main characteristics of the Database approach; Advantages and Disadvantages of Databases.

Three-level schema architecture; Data Independence; Database languages & interfaces; Schemas and Instances; Functions of a DBMS; Lifecycle of Database system development; Data & Database Administration; Information Resource Dictionary System (IRDS).

Entity-Relationship Model concepts; Application of the E-R tools to few case studies; Overview of the Enhanced E-R Model; Apply the Enhanced modelling concepts to the above case studies.

Introduction to Relational Databases; Relational Data structure; Relational Keys; Relational Integrity Constraints; Mapping of ERD into a Relational Schema; Mapping Enhanced ERD into a Relational Schema; Classifying a DBMS as a Relational System.

Relational Database Languages; Structured Query Language (SQL).

Advanced SQL; Query By Example (QBE); Functional Dependencies.

Normalisation for Relational Databases.

Placing File Records on secondary storage devices; Record Blocking & Buffering; Common Database Operations on Files; Heap Files; Ordered Files; Hash Files; Types of Single-Level Ordered Indexes (Primary, Clustering & Secondary Indexes); Multilevel Indexes (ISAM); Dynamic Multilevel Indexes (B + -Tree).

Hierarchical Database DBMS: Hierarchical Structure, Advantages & Disadvantages, IMS Terminology & Architecture, DB Structure, Data Manipulation Language.

Network Database DBMS: Network Basic, Advantages & Disadvantages, CODASYL Terminology & Architecture, Data Manipulation Language.

Indicative Content

Motivation and new application requirements for OODBS; The OODBS manifesto, Advantages & Disadvantages of OODBS; Object Modelling Concepts; O-O DB Design by mapping EER-to-Object Model;

The Object Modelling Technique (OMT) & its notations.

Requirement Analysis for DB Design; Specifying operations from the Object Model; Devising & Evaluating an architecture; Choosing the external control.

Issues in choosing the right Data Management approach; Strategies for data interactions; Object identity & Temporal data; Detailed Design & implementation; Model transformation.

Implementing the Object Model; The Object Management Group; SQL3 vs. Object Query Language; The Impedance Mismatch Problem; Comparison between Object Data Model and Enhanced E-R Model; The differences between Extended Relational System & OODBS.

Transaction Processing; Properties of a Transaction; Commit/Rollback Protocol; The need for concurrency control; The Lost Update, Uncommitted Dependency, Inconsistent Analysis Problems; Locking Techniques for Concurrency Control; Shared & Exclusive locks, 2-P Locking Protocol; Serialisation.

Motivation for Distributed DBMS Concepts; Advantages & Disadvantages of DDBMSs; Stand alone Database System, PC-File Server Architecture, Client/ Database Server Architecture; Homogeneous, Heterogeneous and Federated DDBMSs; Schema Architecture for DDBMS; Design Issues: Data Fragmentation, Allocation & Replication; Date’s 12 Rules for DDBMS, Web technology & DBMSs, The Web as a Database Application Platform.

Outline of the recovery process; System concepts (Cashing and Flashing); Deferred & immediate update technique; Shadowing technique; Distributed Recovery control; Failures in a Distributed Environment, Two/Three-Phase Commit.

Types of security (Legal & Ethical issues, Policy issues, system-related issues, multiple security level); Countermeasures - computer-based control & Non-computer-based control; Multilevel Security classes; Statistical DB Security.

Steps of a Query Execution; Basic algorithms; Purpose and advantages of Query Optimisation Techniques; Heuristics Optimisation; Cost Optimisation Approach; Semantic Query Optimisation; Query Optimisation in Distributed Databases.

Active Databases: Active vs. Passive DBs; Fundamental features (Events, Conditions & Actions); Syntax & Semantics of Rules & Triggers; Overview of existing systems; Applications of active DBs; Classical DB management; Workflow management; Business Rules; Design principle for active rules and their collective behaviour.

Data Warehousing, Knowledge Discovery & Data Mining: An overview; OLAP vs. OLTP; Multidimensional

Data; Market & Applications; Codd’s 12 Rules of OLAP; System Architecture; Operational Process; DW tools; Conceptual Model for OLAP & Star Schema Design; DW Server Requirements; Data Mining techniques and knowledge discovery.