Skip to navigation | Skip to main content | Skip to footer
Menu
Menu

COMP23111 Fundamentals of Databases syllabus 2017-2018

COMP23111 materials

COMP23111 Fundamentals of Databases

Level 2
Credits: 10
Enrolled students: 242

Course leader: Alvaro A. A. Fernandes


Additional staff: view all staff

Requisites

  • Pre-Requisite (Compulsory): COMP16121
  • Pre-Requisite (Compulsory): COMP16212

Additional requirements

  • Students who are not from the School of Computer Science must have permission from both Computer Science and their home School to enrol.

Assessment methods

  • 70% Written exam
  • 30% Practical skills assessment
Timetable
SemesterEventLocationDayTimeGroup
Sem 1 Lecture 1.1 Tue 14:00 - 15:00 -
Sem 1 w1 Lecture 1.1 Wed 12:00 - 13:00 -
Sem 1 B Examples IT407 Fri 10:00 - 11:00 L
Sem 1 B Examples IT407 Fri 11:00 - 12:00 K
Sem 1 B Examples IT407 Wed 11:00 - 12:00 J
Sem 1 A w3+ Lab LF31 + LF17 Thu 09:00 - 11:00 H
Sem 1 A w3+ Lab LF31 + LF17 Thu 11:00 - 13:00 G
Sem 1 A w3+ Lab LF31 + LF17 Fri 11:00 - 13:00 F
Themes to which this unit belongs
  • Web and Distributed Systems

Overview

Databases are core, if largely invisible, components of modern computing architectures in both commercial and scientific contexts. The management of data has evolved from application-specific management of myriad files to organisation-wide approaches that see data as one of the most important assets of modern organisations and, as such, a key factor in their ability to compete and thrive. At this organisation-wide scale, database management systems (DBMSs) are the crucial piece of software infrastructure needed to achieve the desired results with consistent quality and robust efficiency. A modern DBMS is a thing of wonder and embodies in its internal construction and in its wide usability many advances in algorithms and data structures, programming language theory, conceptual modelling, concurrency theory, and distributed computing. This makes the study of databases a data-centric traversal of many of the most exciting topics in modern computing.

Aims

The aim of this course unit is to introduce the students to the fundamental concepts and techniques that underlie modern database management systems (DBMSs).

The course unit studies the motivation for managing data as an asset and introduces the basic architectural principles underlying modern DBMSs. Different architectures are considered and the application environments they give rise to.

The course unit then devotes time to describing and motivating the relational model of data, the relational database languages, and SQL, including views, triggers, embedded SQL and procedural approaches (e.g., PL/SQL).

The students learn how to derive a conceptual data model (using the Extended Entity Relationship paradigm), how to map such a model to target implementation model (for which the relational model is used), how to assess the quality of the latter using normalisation, and how to write SQL queries against the improved implementation model to validate the resulting design against the data requirements originally posed. For practical work, the Oracle DBMS is used.

The course unit also introduces the fundamentals of transaction management including concurrency (e.g., locking, 2-phase locking, serialisability) and recovery (rollback and commit, 2-phase commit) and of file organisation (e.g., clustering) and the use of indexes for performance.

Finally, the course unit addresses the topic of database security by a study of threats and countermeasures available. In the case of the former, these include potential theft and fraud as well as loss of confidentiality, privacy, integrity and availability. In the case of the latter these primarily include mechanisms for authorization and access control, including the use of views for that purpose. The course unit also addresses the topic of legal frameworks that give rise to obligations on the part of database professionals by introducing exemplars such as the 1995 EU Directive on Data Protection and the 1998 UK Data Protection Act.

Syllabus

Introducing Data Management

Data as an Asset, Records and Files, The Need for Models, The Need for Independence, Database Management as a Service [1 lecture plus background reading]

Understanding Database Architectures

Levels of Abstraction in Data Management, The ANSI/SPARC Approach, Schemas v. Instances, Describing v. Querying v. Changing, DBMS Components and Architectures [1 lecture plus background reading]

Understanding the Relational Paradigm

The Relational Model, The Relational Languages [2 lectures plus background reading] [1 examples clinic, 1 lab exercise]

Designing Databases

Building a Conceptual Data Model, Mapping the Conceptual Data Model to a Target, Identifying Functional Dependencies, Normalizing the Schema [5 lectures plus background reading] [3 examples clinics, 2 lab exercises]

Interfacing with Applications

Defining Views, Using Embedded Queries, Procedural Access, and Triggers [1 lecture plus background reading] [1 examples clinic, 1 lab exercise]

Understanding System-Level Issues

Concurrency, Transactions and Recovery, File Organisations and Indexes [2 lectures plus background reading]

Considering Security and Privacy

Threats and Countermeasures, Legal Frameworks [background reading]

Teaching methods

Lectures

13 in total, 1 per week + an additional lecture in Week 2.

Examples classes

5 in total, each one hour long, run as clinics in support of the taught material, mandatory readings and lab exercises.

Laboratories

5 in total, assessed and supported, each, by one two-hour assisted session. None are optional, all are assessed.

Feedback methods

Example classes, lab sessions, virtual learning environments.

Study hours

  • Assessment written exam (2 hours)
  • Lectures (11 hours)
  • Practical classes & workshops (15 hours)

Employability skills

  • Analytical skills
  • Problem solving

Learning outcomes

Programme outcomeUnit learning outcomesAssessment
A5Be able to discuss and explain the importance of data, and the difference between file management and databases.
  • Examination
A5Be able to discuss and explain the design of database management system (DBMS) architectures and environments (and, in particular, the 3-level ANSI-SPARC architecture).
  • Examination
A5 C2 D1Be able to discuss and explain the principles of database design.
  • Lab assessment
  • Examination
A5 B1 B3 C2 D3 D5Be able to discuss, explain and apply conceptual design methodologies, and, in particular, the Extended Entity Relationship Approach.
  • Examination
  • Lab assessment
A5 B1 B3 C2 D3 D5Be able to discuss, explain and apply the relational model, and, in particular the derivation of relational schemas from conceptual designs and the normalisation of those schemas.
  • Lab assessment
  • Examination
A5Be able to discuss and explain physical and performance-related design considerations (e.g., the impact of file organisation schemes and of indexes).
  • Examination
  • Lab assessment
A5Be able to discuss and explain transaction processing.
  • Examination
A5 B1 B3 C2 D3 D5Be able to discuss, explain and apply the SQL language as implemented in the Oracle DBMS.
  • Examination
  • Lab assessment
A4 A7Be able to discuss and explain the basic mechanisms to enforce database security, through an understanding of the threats and countermeasures available in classical database management systems.
  • Examination
A4 A7Be able to discuss and explain the requirements and responsibilities arising from the use of classical database management systems in the light of legal frameworks to protect individuals, institutions and property rights.
  • Examination

Reading list

TitleAuthorISBNPublisherYearCore
Fundamentals of database systems (7th edition)Elmasri, Ramez and Shamkant B. Navathe9781292097619Pearson2016
Database systems: the complete book (updated 2nd edition)Garcia-Molina, Hector and Jeffrey D. Ullman and Jennifer Widom9781292024479Pearson2013
Beginning Oracle SQLde Haan, L. et al.9781430271970Apress Academic2009
Pro Oracle SQL (2nd edition)Morton, Karen et al.9781430262206Apress2013
Database system concepts (6th edition)Silberschatz, Abraham and Henry F. Korth and S. Sudarshan9780071289597McGraw-Hill2010
Database systems: a practical approach to design, implementation and management (6th edition)Connolly, Thomas M. and Carolyn E. Begg9781292061184Addison-Wesley2014
Database systems: an application-oriented approach (introductory version) (2nd edition)Kifer, Michael and Arthur Bernstein and Philip M. Lewis9780321228383Pearson/Addison Wesley2004
Database management systems (3rd edition)Ramakrishnan, Raghu and Johannes Gehrke9780071231510 McGraw-Hill2002

Additional notes

Course unit materials

Links to course unit teaching materials can be found on the School of Computer Science website for current students.