Relational Databases: Theory and Practice

Home Admissions Course Guide Relational Databases: Theory and Practice

This Course Guide has been taken from the most recent presentation of the course. It would be useful for reference purposes but please note that there may be updates for the following presentation.

COMP S359

Relational Databases: Theory and Practice

Welcome to COMP S359 Relational databases: theory and practice. We hope that you will find the course interesting and rewarding, and that you will acquire useful knowledge and skills as a result of your study. This course is about using databases — collections of data that can be vital to the operation of organizations. It focuses in detail on relational databases — the kind of database that is based on storing data as tables, and that is now widely used on all computer systems. It offers a number of perspectives varying from the broader scope of data processing systems to the technology of databases based on the general-purpose software known as database management systems.

 

Aims

The aim of this course is to provide you with an understanding of relational databases. The underlying theme of this course is that databases are everywhere. The course will introduce the need for database management systems, including an account of the facilities needed to store and access large collections of data in a shared user environment. The account will consider the different requirements of the users, but the common requirements for the underlying data. These will emphasize the concept of data being used for specific purposes leading to emphasis on the correctness of the data for that purpose.Underpinning topics, such as data modelling and database architecture, will be introduced so that further development of these ideas can take place as the course progresses.

 

Assumed knowledge

The course is designed on the assumption that you have studied a second-level computing course.

 

Personal computer

You will need a computer that complies with the recommended computer specifications for this course as detailed in the course supplement.

 

The course materials

Here is a summary of the components of the whole course. Some of these components will be available on the OLE only.

  • Course guide
  • Course texts
  • Database cards
  • Software guide
  • Assignments
  • Specimen examination paper and solutions
  • Stop presses

Structure

The course is organized as four blocks, each bound as a single course text. A complete list of the titles of the blocks is given below.

  • Block 1       Databases in context
  • Block 2       Introducing relational theory
  • Block 3       The database language SQL
  • Block 4       Database life cycle

Content

Block 1: The purpose of Block 1 is to set the scene for, and give context to, database theory and practice, which is the focus of COMP S359.The first three sections of the block place relational database theory and practice in historical context, highlight the importance of good data management practices to business, and consider aspects of database technologies. The final sections of the block introduce the foundations for the development process for relational databases taught in the rest of the course.

 

Block 2: This block is concerned with the relational representation of data, that is, with representing data by relations (depicted as tables), and with representing the constraints on the data by the declarations and expressions of relational algebra. Relational representations often take the conceptual data model, discussed in Section 5 of Block 1, as a starting point, and are themselves used as starting points for the implementation of relational databases.

 

Block 3: This block introduces SQL, i.e. Structured Query Language, which is the standard language for data management tasks. First, we introduce you to SQL's facilities for retrieving data from a database using increasingly complex queries. Then we look at how to use SQL to define and populate tables, define constraints on the data, and modify the data held in a database. Finally, we look at some of the programming structures that can be used to embed SQL in application processes.

 

Block 4: This block is concerned with the database life cycle, which describes the stages a database goes through, from the time the need for a database is established until it is withdrawn from use. The database life cycle has two main phases: database development, and maintenance. Database development concerns the building and installation of a new database that satisfies the information requirements of an enterprise; database maintenance ensures that the database continues to satisfy those requirements after it has been installed, and considers how a database can be adapted to meet new and changing requirements.

 

Software

The course uses Oracle and PostgreSQL. To install and use this software, please read the Software Guide and follow the installation and set-up instructions. The guide also provides instructions for PHP and Apache, which will be used in Block 3. You should install this software before you start the course to avoid interrupting your studies later.

This course is concerned with teaching principles of relational databases and generic, transferable skills in SQL and database development so that you can export your knowledge to the software of any vendor. Consequently, wherever possible, the course team has tried to avoid vendor-specific features of the DBMS or SQL implementation.

The course is built around a simple sequential study path that runs through the four course blocks, starting with Block 1 and going through to the end of Block 4. The material in the course blocks tells you when to do any associated computer activities. You are not expected to study material that falls outside this path. Of course, you may like to read further material for your own interest.

 

Active learning

The course material includes two methods of self-assessment to help reinforce what you have learnt: exercises and activities. In this course you are expected to do all the exercises and activities that are in the blocks because occasionally they are used to raise issues which are discussed later in the course material.

The difference between exercises and activities is described as follows.

Exercises: these are to help you check that you have understood the material in the section that you have just read. They are short questions that focus on key aspects of the course, and that should take you no more than 10 minutes (in many cases, much less than this) to answer. The answers to exercises are collected together at the back of each block. However, you should attempt to answer the questions in the exercises before looking at the sample solutions. This will make you better prepared for the assignment and examination questions. Reading the questions in an exercise and immediately reading the sample solution will give you little benefit in studying the course. In the exercises you will be expected to use the material taught and apply what you have learnt to different scenarios, so they cannot all be answered simply by looking up the relevant quote or technique in the written material. The complexity of the exercise questions will increase: Block 1 is a relatively gentle introduction, but by the time you study Block 4, some thinking time will be needed to answer the exercises in the text.

Activities: these are similar to the exercises, but they require the use of a computer, and each activity usually takes longer than 30 minutes to complete. Most computer activities are followed by some discussion, but in some cases there is feedback built into the activity and so no extra discussion is supplied. You will also find shorter 'subactivities' throughout this course. These are small activities, or specific tasks within a larger activity, usually consisting of a single SQL command, that you should perform as you work through the course material. They are not expected to take more than a minute or two to complete.

 

Tips on studying COMP S359

The blocks are of different lengths, both in terms of the number of pages and study time, so you will need to plan your time accordingly. The course schedule (available on the OLE) indicates how long you are expected to spend on each block; this includes the time you spend answering and writing your assignments. We would advise that you keep to the timescales given in the course schedule as closely as you
can. It is generally very difficult to catch up if you fall behind schedule.

It is a good idea to make notes of important aspects of each section of a study block. Not only can these be helpful for reference as you study the course, but they can be a good time-saver. They can save you time
when you need to find a reference to an earlier topic, or they can remind you quickly where that topic was first referenced. The notes will help you when you do your assignments. They are particularly useful when you are revising for the examination.

Your tutor is there to help and advise you on the course content, assignments, study skills and other related academic issues. If you find you are having difficulties with the course material or assessment, then your tutor will be able to help, often by offering a different perspective on the material. You should contact your tutor if you feel you are having problems with the course material. It is very unlikely that you are the only person experiencing these difficulties, and your tutor is used to resolving these problems.

We have developed this course in an iterative manner, just as we teach you to design and develop relational databases. As such, there are always areas that could be improved. Where we discover there are errors or ambiguities, we will let you know through the course website. If you find any errors or ambiguities yourself, please let your tutor know and they will be able to inform the course coordinator who, in turn, can inform other students.

We recommend that you check the OLE page of this course at least twice a week.

The final score of the course will depend on two components: the tutor- marked assignments (Assignments, 30%) and the final examination (70%). The grade of the course depends on the final score. To get a passing grade, both the average assignment score and the examination score should not be less than 40.

There are four assignments in the course. Assignment 3, which contains questions to test your understanding of SQL, will be the required assignment. The best two out of the three remaining assignments (Assignments 1, 2 and 4) will also be counted. The weightings of the assignments are given below:

Assignment 3: 40%

other assignments: 60% (30% each)

 

Revision for the examination

The specimen examination paper will show you what sorts of questions to expect in the examination, and the solutions to the specimen examination paper will show you what sorts of answers may be expected for full marks. It is very good practice to use the specimen examination paper for revision before the COMP S359 examination. At some time in your revision period, try doing the specimen examination paper in examination-type conditions, and then check how well you have answered the questions. This will give you good indications of the areas you need to revise further.

Coming soon