Undergraduate Syllabus (School of Computer Science

COMP23111 Database Systems

Level 2
Credits: 10
Enrolled students: 311

Assessment methods

50% Written exam
50% Practical skills assessment

Timetable

Semester	Event	Location	Day	Time	Group
Sem 1 w1-5,7-11	Lecture	Engineering Building A 2A.040 Lecture Theatre A	Mon	13:00 - 14:00	Passcode: 390573
Sem 1 w2-5,7-11	ONLINE DROP-IN		Tue	11:00 - 12:00	Passcode: 449442
Sem 1 w2-5,7-11	DROP-IN	G23	Thu	14:00 - 15:00	-
Sem 1 w2-5,7-11	DROP-IN	G23	Thu	15:00 - 16:00	-

Themes to which this unit belongs

Web and Distributed Systems

Overview

The unit aims to:

The aim of this course unit is to introduce the students to the fundamental concepts and techniques that underpin 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 also introduces alternative database systems for handling large sets of data.

This course unit detail provides the framework for delivery in 20/21 and may be subject to change due to any additional Covid-19 impact. Please see Blackboard / course unit related emails for any further updates.

Aims

The unit aims to:

The aim of this course unit is to introduce the students to the fundamental concepts and techniques that underpin 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 also introduces alternative database systems for handling large sets of data.

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

10 in total, weekly formative sheets for you to practice various elements of database design and manipulation.

Midterm test

2 midterm tests which will count towards 25% of the coursework mark

Feedback methods

Lectures, coursework, face to face mentoring by TAs. Use on online quizzes.

Study hours

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

Employability skills

Analytical skills
Problem solving

Learning outcomes

On successful completion of this unit, a student will be able to:

On the successful completion of the course, students will be able to:

Be able to discuss and explain the principles of database design.
Be able to discuss, explain and apply conceptual design methodologies, and, in particular, the Extended Entity Relationship Approach.
Be 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.
Be 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.
Be 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.

Reading list

Title	Author	ISBN	Publisher	Year
Database Systems.	Connolly, Thomas.	9781292061832	Pearson Education UK	2015
Beginning Oracle SQL	Haan, Lex de. author.	9781430271963	Apress	2010
Fundamentals of database systems	Elmasri, Ramez, author.	1292097620	Pearson	2017
Database systems : the complete book	Garcia-Molina, Hector, author.	9781292024479	Pearson	2014
Database systems : an application-oriented approach	Kifer, M. (Michael), 1954-	0321228383	Pearson/Addison Wesley	c2005.
Pro Oracle SQL	Morton, Karen. author.	9781430262213	Apress	2013
Database management systems	Ramakrishnan, Raghu.	0071151109	McGraw-Hill	c2003.
Database system concepts	Silberschatz, Abraham.	9780071289597	McGraw-Hill	2011.

Additional notes

Course unit materials

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

COMP23111 Database Systems syllabus 2021-2022