DT249
BSc in Information Systems
and Information Technology

Now accepting applications for January 2009

SDEV2002 (Stage 2)
Database Systems (10 ECTS)
 

• Prerequisite Modules
• Description
• Aims
• Learning Outcomes
• Learning and Teaching Methods
• Content
• Assessment
• Recommended Reading

Prerequisite Modules

• SDEV1002 Information Storage and Management

Description

This module provides the student with a comprehensive introduction to conceptual data modeling for database design, sound understanding of the relational model, and practical knowledge of querying SQL databases and expressing relational schemata in SQL.

Aims

The aim of this module is to enable the student to manipulate existing SQL databases and create new relational databases by devising a high-level conceptual data model, transforming that data model into a relational schema, and implementing the relational schema correctly and robustly in SQL, choosing appropriate data types.

These skills and knowledge are crucial to the professional use, understanding, and evaluation of information and database management systems. More immediately, this course provides the necessary theoretical and practical foundations for Advanced Database Systems.

Learning Outcomes

On successful completion of this module, the student will be able to:

1. Understand the structure of, and construct semantically meaningful queries on, existing SQL databases
2. Create, populate, modify, alter, and destroy an SQL database
3. Critically evaluate an SQL database in terms of structure and choice of datatypes
4. Identify, explain, and differentiate clearly between the fundamental constructs of the relational model in a formal way
5. Create conceptual data models and transform them into relational schemata
6. Express relational schemata in SQL's DDL (Data Description Language) with appropriate choice of datatypes
7. Accurately convert queries expressed in SQL, the relational algebra, the relational calculi, or English into any of the others, where possible
8. Identify the reason, or reasons, why a query might not be convertible (e.g. due to ambiguity of expression, expressive power of the language, etc.) from the relational algebra, the relational calculi, or English into one of the others

Learning and Teaching Methods

Lectures, self-study, labs, tutorials, and any combination of discussion, case study, problem-solving exercises, readings, seminars, and computer-based learning.

Content

The Relational Model

• Data structures: relations, tuples, domains, and values
• Keys and related terms: candidate, surrogate, primary, and foreign keys, superkeys, prime and non-prime attributes
• Data integrity: domain, entity, and referential integrity
• Relational algebra, tuple relational calculus, and domain relational calculus
• Functional and multivalued dependencies
• 1st, 2nd, Boyce-Codd, and 4th normal forms
• Normalisation via dependency-preserving (nonadditive / lossless / nonloss) binary decomposition
• Higher and historical normal forms in outline: 3NF, 5NF, and Domain-Key normal form (DKNF)

Database Design

• Normalisation as design: successive lossless decompositions leading to BCNF or higher
• Conceptual modeling with E-R diagrams
• Transforming a conceptual data model into a relational database schema

Database Language SQL

• SQL introduction and history
  • The SQL Standard: ANSI/ISO/IEC 9075-2 Database Language SQL - Part 2: Foundation
  • Vendor-specific dialects and deviation from the standard
  • Relationship to, and deviation from, the relational model
  • The NULL controversy
• Manipulating database schema objects (DDL):
  • Creating schema objects: CREATE TABLE|VIEW|DOMAIN|TYPE
  • Changing schema objects: ALTER TABLE
  • Deleting schema objects: DROP TABLE|VIEW|DOMAIN|TYPE
  • Column and Table CONSTRAINTs: NULL and NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK()
  • Manipulating data (DML):
  • Basic SELECT with DISTINCT, WHERE, and ORDER BY
    INNER|NATURAL JOIN, LEFT|RIGHT|FULL OUTER JOIN
    SELECT with aggregates, GROUP BY, and HAVING clauses
  • Subqueries: correlated and non-correlated; scalar, column, and table subqueries
  • Predicates: IN, EXISTS, ANY (or SOME), EVERY, and their negations
  • Changing data with UPDATE, INSERT, and DELETE
• Transactions
• Stored procedures and triggers

Assessment

The methods of assessment to be used to measure the learning objectives stated above are written examination and continuous assessment including one or more of assignment, essay, problem-solving exercise, oral presentation, and class or lab tests.

• Continuous Assessment: 30%
• Examination: 70%

Recommended Reading

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems, 4th ed.; Addison-Wesley, 2003; ISBN 0-321-20448-4
• C.J. Date, An Introduction to Database Systems, 8th ed.; Addison-Wesley, 2004; ISBN 0-321-18956-6.
• ANSI/ISO/IEC 9075:1999-1 Database Language SQL - Part 1: SQL/Framework (or later version)
• ANSI/ISO/IEC 9075:1999-2 Database Language SQL - Part 2: Foundation (SQL/Foundation) (or later version)
• E.F. Codd, A Relational Model of Data for Large Shared Data Banks, CACM 13(6), June 1970, pp. 377-387
• E.F. Codd, Extending the Database Relational Model to Capture More Meaning, ACM TODS 4(4), Dec. 1979, pp.397-434
• Peter Pin-Shan Chen, The Entity-Relationship Model - Toward a Unified View of Data, ACM TODS 1(1) March 1976, pp.9-36.
• Peter Pin-Shan Chen, The Entity-Relationship Model - A Basis for the Enterprise View of Data, Proc. National Computer Conference, 1977; AFIPS Press, Montvale N.J.; pp.77-84.
• Ronald Fagin, Multivalued Dependencies and a New Normal Form for Relational Databases, ACM TODS 2(3) Sept. 1977, pp.262-278
• Catriel Beeri, Ronald Fagin, and John Howard, A Complete Axiomatization for Functional and Multivalued Dependencies in Database Relations, Proc. 1977 ACM SIGMOD, D.C.P. Smith (ed.), Toronto; pp.47-61
• William Kent, A Simple Guide to Five Normal Forms in Relational Database Theory, CACM 26(2), Feb. 1983, pp.120-125
• ACM Transactions on Database Systems (TODS)
• ACM SIGMOD Record http://www.sigmod.org/sigmod/record/