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

COMP23111: Fundamentals of Databases (2012-2013)

This is an archived syllabus from 2012-2013

Fundamentals of Databases
Level: 2
Credit rating: 10
Pre-requisites: COMP16121, COMP16212
Co-requisites: [desirable, not mandatory] COMP18112, COMP23420
Duration: 12 weeks.
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.
Labs: 5 in total, assessed and supported, each, by one two-hour assisted session. None are optional, all are assessed. [14-12-2012 14:23:58]
Course Leader: Alvaro A. A. Fernandes
Additional Lecturers: Sandra Sampaio
Course leader: Alvaro A. A. Fernandes

Additional staff: view all staff
Timetable
SemesterEventLocationDayTimeGroup
Sem 1 Lecture Chemistry G.51 Tue 12:00 - 13:00 -
Sem 1 B Examples IT407 Wed 10:00 - 11:00 L
Sem 1 B Examples IT407 Fri 11:00 - 12:00 K
Sem 1 B Examples LF15 Thu 11:00 - 12:00 J
Sem 1 w2 Lecture Roscoe TH A Mon 12:00 - 13:00 -
Sem 1 A w3+ Lab G23 Wed 09:00 - 11:00 H
Sem 1 A w3+ Lab G23 Thu 11:00 - 13:00 I
Sem 1 A w3+ Lab G23 Fri 11:00 - 13:00 F
Assessment Breakdown
Exam: 70%
Coursework: 0%
Lab: 30%

Themes to which this unit belongs
  • Enterprise Information Systems

Introduction

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.

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.
  • Examination
  • Lab assessment
A5 B1 B3 C2 D3 D5Be able to discuss, explain and apply conceptual design methodologies, and, in particular, the Extended Entity Relationship Approach.
  • Lab assessment
  • Examination
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.
  • Lab assessment
  • Examination
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

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]

Reading List

Core Text
Title: Fundamentals of database systems (7th edition)
Author: Elmasri, Ramez and Shamkant B. Navathe
ISBN: 9781292097619
Publisher: Pearson
Edition: 7th
Year: 2016
This is a good textbook, and the default choice for the one to purchase (and learn from) if you don't intend to specialise on databases either in your career or at postgraduate level. It is particularly good on conceptual modelling, and there is a commendable concern throughout in balancing accessibility with completeness and precision.


Supplementary Text
Title: Database management systems (3rd edition)
Author: Ramakrishnan, Raghu and Johannes Gehrke
ISBN: 9780071231510
Publisher: McGraw-Hill
Edition: 3rd
Year: 2002
This is a good textbook by two prominent database researchers. It suffers a little in comparison with all of the above for being almost a decade old. Otherwise, it's level with all of them except for Garcia-Molina et al. (2009).


Supplementary Text
Title: Database systems: a practical approach to design, implementation and management (6th edition)
Author: Connolly, Thomas M. and Carolyn E. Begg
ISBN: 9781292061184
Publisher: Addison-Wesley
Edition: 6th
Year: 2014
In many respects, this textbook is comparable to both Elmasri and Navathe (2011) and to Silberschatz et al. (2006) but can be said to place less emphasis on how research-led advances have coalesced into practice. Its unique distinction is to spell out with great care and detail a methodology for designing databases. As such, it would be very useful for those who are aiming for a career in industry as database professional.


Supplementary Text
Title: Database systems: the complete book (updated 2nd edition)
Author: Garcia-Molina, Hector and Jeffrey D. Ullman and Jennifer Widom
ISBN: 9781292024479
Publisher: Pearson
Edition: 2nd
Year: 2013
Living up to its title, this is the most thorough textbook on databases by three researchers who have each been responsible for major contributions to the area. It's quite encyclopaedic and, uniquely, covers better than any other the system-level aspects of database management systems (i.e., what would it take for you to build one rather than just design or use it). If you intend to specialise on databases either in your career or at postgraduate level, this is the one for you.


Supplementary Text
Title: Pro Oracle SQL (2nd edition)
Author: Morton, Karen et al.
ISBN: 9781430262206
Publisher: Apress
Edition:
Year: 2013
This, like its informal companion (Haan et al. 2010), is a professional book. It aims at providing a focussed, intermediate-level account of how the current leader in the DBMS commercial market implements more advanced features of SQL.


Supplementary Text
Title: Beginning Oracle SQL
Author: de Haan, L. et al.
ISBN: 9781430271970
Publisher: Apress Academic
Edition:
Year: 2009
This, like its informal companion (Morton et al. 2011), is a professional book, rather than a book for students. In this respect, it is less concerned with providing exercises and lab examples, or with providing pointers to the research literature. It has, as intended, the value of providing a focussed, beginner-level account of how SQL is implemented by the current leader in the DBMS commercial market.


Supplementary Text
Title: Database system concepts (6th edition)
Author: Silberschatz, Abraham and Henry F. Korth and S. Sudarshan
ISBN: 9780071289597
Publisher: McGraw-Hill
Edition: 6th
Year: 2010
Another good textbook, again by distinguished database researchers, and very similar in aim and in achievement to Elmasri and Navathe (2011). In comparison with the latter, it devotes more space to some advanced topics and is unique in having chapters devoted to four prominent database management system currently in the market.


Supplementary Text
Title: Database systems: an application-oriented approach (introductory version) (2nd edition)
Author: Kifer, Michael and Arthur Bernstein and Philip M. Lewis
ISBN: 9780321228383
Publisher: Pearson/Addison Wesley
Edition: 2nd
Year: 2004
This textbook is strong in patches, but not as comprehensive (it does not attempt to be) as any of the above. It aims to be application-oriented but it is arguable whether it achieves this goal to a greater extent than, say, Connolly and Begg (2010). It has a chapter on software engineering implications that may be useful for some.