DB Student Guide V1.1

LEVEL 4
DATABASES
Student Guide
DB Student Guide V1.1

Modification History
Version Date Revision Description

V1.0 June 2011 For Release
V1.1 November 2015 Assessment Methodology Updated
© NCC Education Limited, 2011

All Rights Reserved
The copyright in this document is vested in NCC Education Limited. The document must not be
reproduced by any means, in whole or in part, or used for manufacturing purposes, except with the
prior written permission of NCC Education Limited and then only on condition that this notice is
included in any such reproduction.
Published by: NCC Education Limited, The Towers, Towers Business Park, Wilmslow Road,
Didsbury, Manchester M20 2EZ, UK.
Tel: +44 (0) 161 438 6200 Fax: +44 (0) 161 438 6240 Email: info@nccedu.com
http://www.nccedu.com
Page 2 of 85
Title Here
CONTENTS
1. Module Overview and Objectives .............................................................................. 6
2. Learning Outcomes and Assessment Criteria .......................................................... 6
3. Syllabus ....................................................................................................................... 7
4. Related National Occupational Standards ................................................................ 9
5. Teaching and Learning............................................................................................... 9
5.1 Lectures...................................................................................................................... 10
5.2 Tutorials ...................................................................................................................... 10
5.3 Laboratory Sessions .................................................................................................. 10
5.4 Private Study .............................................................................................................. 10
6. Further Reading List ................................................................................................. 10
7. Assessment .............................................................................................................. 11
Topic 1: Introduction to the Module and Database Fundamentals ...................................... 13
1.1 Learning Objectives .................................................................................................... 13
1.2 Timings ....................................................................................................................... 13
1.3 Laboratory Sessions ................................................................................................... 14
1.4 Private Study Exercises .............................................................................................. 16
1.5 Tutorial Notes ............................................................................................................. 17
Topic 2: Databases and Database Management Systems (DBMS) ...................................... 19
2.2 Timings ....................................................................................................................... 19
2.3.1 Simple queries ................................................................................................ 20
2.3.2 Using the COUNT Function with Queries ........................................................ 21
2.5 Tutorial Notes ............................................................................................................. 23
Topic 3: Entity Relationship (ER) Modelling (I) ..................................................................... 25
3.2 Timings ....................................................................................................................... 25
3.3.1 Selecting from More Than One Table .............................................................. 26
3.5 Tutorial Notes ............................................................................................................. 29
Topic 4: Entity Relationship (ER) Modelling (2) .................................................................... 31
4.2 Timings ....................................................................................................................... 31
Page 3 of 85
Title Here
4.4.1 Review Material on ER Modelling .................................................................... 34
4.4.2 Drawing ER Models ........................................................................................ 34
4.5 Tutorial Notes ............................................................................................................. 36
Topic 5: The Relational Model (I) ............................................................................................ 37
5.2 Timings ....................................................................................................................... 37
5.3.1 More on Creating and Modifying Tables .......................................................... 38
5.5 Tutorial Notes ............................................................................................................. 42
Topic 6: The Relational Model (2) ........................................................................................... 45
6.2 Timings ....................................................................................................................... 45
6.5 Tutorial Notes ............................................................................................................. 49
Topic 7: SQL (I)........................................................................................................................ 51
7.2 Timings ....................................................................................................................... 51
7.5 Tutorial Notes ............................................................................................................. 56
Topic 8: SQL (2)....................................................................................................................... 58
8.2 Timings ....................................................................................................................... 58
8.3.1 Aggregation..................................................................................................... 59
8.3.2 Part One: Using the Workers, Departments and Job_Types Tables ................ 59
8.3.3 Using Personal_details, Qualifications and Qualification_types Tables ........... 60
8.5 Tutorial Notes ............................................................................................................. 64
Topic 9: Database Design ....................................................................................................... 66
9.2 Timings ....................................................................................................................... 66
9.3.1 Grouping ......................................................................................................... 67
9.5 Tutorial Notes ............................................................................................................. 70
Topic 10: Supporting Transactions ......................................................................................... 72
Page 4 of 85
Title Here
10.2 Timings ....................................................................................................................... 72
10.3.1 Nested Sub-queries ........................................................................................ 73
10.3.2 Use of the Workers, Departments and Job_types Tables ................................ 73
10.3.3 Using the Personal_details, Qualifications and Qualification_types Tables ..... 74
10.5 Tutorial Notes ............................................................................................................. 76
Topic 11: Database Implementation ........................................................................................ 78
11.2 Timings ....................................................................................................................... 78
11.3.1 Views .............................................................................................................. 79
11.3.2 Indexes ........................................................................................................... 80
11.3.3 Constraints ...................................................................................................... 80
11.5 Tutorial Notes ............................................................................................................. 82
Topic 12: Summary ................................................................................................................... 84
12.2 Timings ....................................................................................................................... 84
12.3 Private Study .............................................................................................................. 85
Page 5 of 85
Overview
1. Module Overview and Objectives

This unit aims to give you a thorough grounding in practical techniques for the design and
development of database systems, and the theoretical frameworks that underpin them.
2. Learning Outcomes and Assessment Criteria

Learning Outcomes; Assessment Criteria;
The Learner will: The Learner can:
1. Understand the concepts associated 1.1 Summarise the common uses of database
with database systems systems
1.2 Explain the meaning of the term database
1.3 Explain the meaning of the term database
management system (DBMS)
1.4 Describe the components of the DBMS
environment
1.5 Describe the typical functions of a DBMS
1.6 Summarise the advantages and disadvantages of
a DBMS
2. Understand the concepts associated 2.1 Summarise the concept of the relational model
with the relational model 2.2 Explain the terminology associated with the
relational model
2.3 Explain the purpose of relational integrity
3. Understand how to design and 3.1 Explain the use of ER modelling in database
develop a database system design
3.2 Describe the basic concepts of an ER model
3.3 Describe ways of identifying problems in an ER
model
3.4 Explain ways of solving problems in an ER model
3.5 Summarise the purpose of SQL
3.6 Describe how to create database tables using
SQL
4. Be able to develop a logical database 4.1 Identify a set of tables from an ER model
design 4.2 Check that the tables are capable of supporting
the required transactions
5. Be able to develop a database 5.1 Create database tables based on a data
system using SQL dictionary
5.2 Insert data into the tables
5.3 Update data in the tables
5.4 Delete data in the tables
Page 6 of 85
Title Here
3. Syllabus
Syllabus
Topic No Title Proportion Content
1 Introduction to the 1/15  Introduction to the module
Module and  What are databases?
Database
Fundamentals
2 hours of  Examples of databases in use
lectures
 Data and information
1 hour of
tutorials
1 hour of
laboratory
sessions Learning Outcome: 1
2 Databases and 1/15 Components of a database system
Database Types of applications
Management
Systems (DBMS)
2 hours of Database Management Systems
lectures
Available commercial implementations
1 hour of
History of information management
tutorials
1 hour of Pre-database information systems
laboratory Advantages of database approach and
sessions DBMS
 Disadvantages of DBMS
 Relational model and alternatives
Learning Outcome: 1
3 Entity Relationship 1/15  The goal of ER modelling
(ER) Modelling (1)  Types of notation
2 hours of  Basic concepts (Entities, Attributes and
lectures Relationships)
1 hour of  Identifying entities
tutorials
1 hour of
laboratory
4 Entity Relationship 1/10  Constructing ER models

(ER) Modelling (2)  Strong and weak entities
2 hours of  Identifying problems in ER models
lectures
 Problem solving in ER models
2 hours of
tutorials
2 hours of
laboratory
Page 7 of 85
Title Here
5 The Relational 1/10  Aims of the relational model
Model (1)  Basic concept of the relational model
2 hours of  Terminology
lectures
2 hours of
tutorials
2 hour of
laboratory
6 The Relational 1/10  The purpose of relational integrity
Model (2)  Basic purpose and concepts of normalisation
2 hours of
lectures
2 hour of
tutorials
2 hours of
laboratory
7 SQL (1) 1/12  The purpose and role of SQL
 Basic concepts of SQL
2 hours of  Standards and flavours of SQL
lectures
1 hour of
tutorials
2 hours of
laboratory
8 SQL (2) 1/12  Key constructs in SQL
 Create statements
2 hours of  Select statements
lectures
 Fixing mistakes
1 hour of
tutorials
2 hours of
laboratory
9 Database Design 1/10  Understanding requirements
 Identify a set of tables from an ER model
2 hours of  The data dictionary
lectures
 Use of CASE tools
2 hours of
 Entities to tables
tutorials
2 hours of
laboratory
Page 8 of 85
Title Here
10 Supporting 1/10  Identifying business rules
Transactions  Checking a database will support the
2 hours of required transactions
lectures  Identifying possible performance issues
2 hours of  Indexing and de-normalisation
tutorials
2 hours of
laboratory
11 Database 1/10  The implementation environment

Implementation  Creating tables based on database dictionary
2 hours of  Enforcing integrity via constraints
lectures
 Enforcing business rules via constraints
2 hours of
 Creating indexes
tutorials
2 hours of  Insert, Update and Delete
laboratory
sessions Learning Outcomes: 5
12 Summary 1/30  Summary of module
 Identifying links with other modules/subject
2 hours of areas
lectures  Clarification of module material and related
issues as identified by students
Learning Outcomes: ALL
4. Related National Occupational Standards

The UK National Occupational Standards describe the skills that professionals are expected to
demonstrate in their jobs in order to carry them out effectively. They are developed by employers
and this information can be helpful in explaining the practical skills you have covered in this module.
Related National Occupational Standards (NOS)

Sector Subject Area: 6.1 ICT Professional Competence
Related NOS: 4.2.A.1 – Contribute to data analysis assignment;
4.2.A.2 – Carry out specified data analysis activities;
4.5.A.1 – Collate specified information relating to data design activities;
4.5.A.2 – Contribute to producing and maintaining data designs;
4.5.A.3 – Assist, under supervision, the management of data relating to data designs;
4.5.P.1 – Assist with the development for data design activities
5. Teaching and Learning

Suggested Learning Hours
Lectures: Tutorial: Seminar: Laboratory: Private Study: Total:
24 17 - 19 90 150
Page 9 of 85
Title Here
The teacher-led time for this module is comprised of lectures, laboratory sessions and tutorials. You
will need to bring this Student Guide to all classes for this module. The breakdown of the hours for
each topic is given in the topic notes below.
5.1 Lectures
Your lecturer will be presenting the basic knowledge and the theoretical concepts required for the
unit during this time. He/she will use PowerPoint slides during the lecture time and you will be
expected to take notes.
You will also be encouraged to be active during this time and discuss and/or practice the concepts
covered. Lectures will include question and answer elements to promote participation and to allow
your lecturer to check whether you understand the concepts they are covering.
5.2 Tutorials
These are designed to deal with the questions arising from the lectures and private study sessions.
You should think carefully beforehand about any areas in which you might need additional guidance
and support and use this time to discuss these with your teacher.
5.3 Laboratory Sessions

During these sessions, you are required to work through practical tutorials and various exercises.
The details of these are provided in this guide.
5.4 Private Study

This Student Guide also contains details of the private study exercises. You are expected to
complete these exercises to improve your understanding. Your tutor will set deadlines for the
completion of this work and go over the suggested answers with you. The deadlines will usually be
before the scheduled tutorials for that topic. Some of the private study tasks may require you to work
in a small group so you will need to plan your time carefully and ensure that you can meet with your
group members to complete the work required before the deadline.
You should also use this time to revise the content of lectures to ensure understanding and conduct
extra reading (using the supplementary textbooks or other materials available in the library or
online). You should bring any questions to the tutorial for additional guidance and support.
6. Further Reading List

You will also be expected to undertake further reading to consolidate and extend your knowledge of
the topics covered in this module. Your Accredited Partner Centre’s library will contain a selection of
useful sources of information and you can also make use of materials available online. The list
below also provides suggestions of suitable reference books you may like to use:
Benyon-Davies, P. (2003). Database Systems, 3rd Revised Edition. Palgrave Macmillan.

ISBN-10: 1403916012
ISBN-13: 978-1403916013
Connolly, T. and Begg, C. (2009). Database Systems: A Practical Approach to Design,

Implementation, and Management, 5h Edition. Pearson Addison Wesley.
ISBN-10: 0321523067
ISBN-13: 978-0321523068
Page 10 of 85
Title Here
Hoffer, J., Ramesh, V. and Toppi, H. (2010). Modern Database Management, 10th Edition. Pearson
Prentice Hall.
ISBN-10: 1408264315
ISBN-13: 978-1408264317
7. Assessment
This module will be assessed by means of an assignment worth 50% of the total mark and an
examination worth 50% of the total mark. These assessments will cover the learning outcomes and
assessment criteria given above.
Page 11 of 85
Title Here
Page 12 of 85
Topic 1
Topic 1: Introduction to the Module and Database

Fundamentals
1.1 Learning Objectives

This topic provides an overview of the module syllabus and a general introduction to databases
On completion of the topic, you will be able to:
 Give a definition of what a database is;

 Give examples of databases in use;
 Distinguish between data and information.
1.2 Timings
Lectures: 2 hours
Laboratory Sessions: 1 hour
Private Study: 7.5 hours
Tutorials: 1 hour
Page 13 of 85
Title Here
The laboratory time allocation for this topic is 1 hour.
This session is about creating some data structures in the form of database tables and populating
them with some data. These tables and data will be used in future exercises.
These exercises are to get you started. The parts of the SQL language you will use will be re-visited
in subsequent sessions.
Exercise 1
The following SQL scripts will create two tables. Create and run them. If possible learn how to save
them in a file and run them from the SQL prompt. The mechanism for doing this will depend upon
the version of SQL you are using.
Create table departments

(dept_no integer not null,
department_name varchar(30),
location varchar2(3)
primary key dept_no);
Create table workers

(emp_no integer not null,
first_name varchar(30),
last_name varchar(30),
job_title varchar(30),
age integer,
dept_no integer,
primary key emp_no,
foreign key (dept_no) references departments)
Exercise 2
Examine the tables you have created. You do this using the desc <table_name> command.
Exercise 3: Insert Statements
To insert data into a table, you need to use an insert statement. The structure of insert statements
is:
Insert into departments values ('1','Packing','Cairo');
Now use similar statements to insert the Accounts department in Lagos with reference number 2
and the Human Resources department in London with reference number 3.
Note: ‘ ‘ are used around text based fields and are not required for numeric fields.
Page 14 of 85
Title Here
Exercise 4
Now use insert statements to create the following workers:
Emp_no First_name Last_name Job_title Age Dept_no
1 Lawrence Surani Manager 56 1
2 Jason Argo Manager 33 2
3 Emily Villa Manager 32 3
4 Ahmed Mukani Packer 23 1
5 Joe Todj Packer 24 1
6 Hattie Smith Accountant 56 2
7 Sally Boorman Admin 34` 3

Assistant
Exercise 5: Looking at the Data
To see the data in your table, you need to use a select statement. The structure of select statements
is:
select <column_name> from <table_name>
To see all the columns:
select * from <table_name>
Use the select command to view the contents of your tables.
Page 15 of 85
Title Here
1.4 Private Study Exercises
You should spend approximately 7.5 hours on the Private Study for this topic. You should use this
time to complete the exercises below as directed by your lecturer and to review the contents of this
topic.
Exercise 1: Databases in Organisations
Consider at least 3 organisations that you are familiar with; for example: your college, place of work,
somewhere you shop or an organisation you are involved with during your leisure time. For each of
them make notes on the following:
 Do they have databases at the moment? If so, what are they used for?
 What sort of data would they be interested in collecting?
 How might they breakdown the data they are interested in into different categories? These
might be types of people, objects for sale, courses etc.
Exercise 2
Many organisations that collect data that will eventually go into a database begin the collection
process with paper forms. Example of this might be a passport or driving license application, a job
application or application to join a library.
Collect some examples of such paper forms and a turn each into a list of the data that could then be
input into a database.
Exercise 3
Revise the topics that were discussed in the lecture. Ensure that you understand examples of
databases in use, the definitions of databases that were given, types of data, and the difference
between data and information.
If anything remains unclear after you have revised the topic, make a list of your questions and bring
it to the tutorial session.
Page 16 of 85
Title Here
1.5 Tutorial Notes
The tutorials for this topic will last for 1 hour. You can expect to spend some of this time discussing
your answers to the Private Study exercises with your lecturer and other students. Your lecturer will
then direct you on completing the tasks below.
Exercise 1: Group Discussion of Private Study Activities
In small groups, discuss your findings to Private Study Exercises 1 and 2. You should collate your
findings and report back to the class.
Exercise 2: Data and Information
Answer the following questions:
1. What is the difference between ‘data’ and ‘information’?
2. Identify in the materials you have collected as part of the Private Study: what is data and what is
information?
Page 17 of 85
Title Here
Page 18 of 85
Topic 2
Topic 2: Databases and Database Management Systems

(DBMS)

This topic provides an overview of databases and database management systems
 Describe the main features of a database system;

 Understand the role of the database management system;
 Describe pre-database information systems;
 Identify some of the commercial products available;
 Understand the importance of the relational model and identify some alternatives to it.
2.2 Timings
Lectures: 2 hours
Tutorials: 1 hour
Page 19 of 85
Title Here
2.3.1 Simple queries
The SELECT statement is used to retrieve data from tables in the database. Whenever you want to
retrieve some data, use the SELECT keyword followed by the name of the columns you want and
the table (or tables) that those columns are in. The form on the SELECT statement is:
Select <column name>, <column name>

From <table name>
Where <Condition>
An example of a SELECT statement that gets the emp_no and the last_name from the workers table
is:
Select emp_no, last_name

From workers
Where dept_no = 1;
Try this and look at the results.
Exercise 1
Select the emp_no, first_name and last name from the workers table.
Exercise 2
Select the emp_no, first_name and last_name from the workers table for all the workers in
department no 1.
Exercise 3
Select the first_name, last_name and job_title for all the managers in the workers table.
Exercise 4
Select the first_name and last_name for all the workers whose first names start with the letter ‘J’.
Exercise 5
Select all the columns from the workers table for workers over the age of 50.
Page 20 of 85
Title Here
Exercise 6
Select the emp_no, first_name and last_name for all the managers who are under the age of 40.
Exercise 7
Select the name and location of all the departments.
Exercise 8
Select all the columns for the department located in Cairo.
2.3.2 Using the COUNT Function with Queries
The COUNT function allows us to count the rows in a table.
Example:
Select Count(emp_no)
From Workers;
Using the primary key, in this case emp_no, in the count function, will give the number of unique
rows that match whatever criteria is used in the WHERE clause.
Exercise 9
Count the number of workers who are under the age of 30.
Exercise 10
Count the number of Managers.
Page 21 of 85
Title Here
You should spend approximately 7 hours on the Private Study for this topic. You should use this
topic.
Exercise 1
Write a report on one of the following topics. Your report should be 600-900 words in length and you
should be prepared to discuss your report in the tutorial session. Your lecturer should allocate the
topics to ensure the different content is covered evenly.
 Prepare a report on three of the commercially available database management systems.

Include at least two of the main vendors (Oracle, MS SQL-Server, MySQL).
Your report should discuss the products and features available for each of the vendors,
their market share and type of market they are most commonly used in, and the ways in
which they differ from one another.
 Prepare a report on three of the alternative data model approaches that have been used
for database systems: network, hierarchical, relational, object-oriented, deductive and
post-relational. Discuss the history, structure and uses of each of your chosen models.
These reports will be used as the basis for the classroom discussion in the tutorial session.
Page 22 of 85
Title Here
2.5 Tutorial Notes
Exercise 1
Work in a small group with other students who have written a report on the same topic during
private study time.
Discuss the information you have found. You should take the opportunity to add any additional
information to your own notes.
Now prepare to present your information to students who have worked on the other report. You
should work together as group to prepare a short (5 minutes), informal presentation which will give
the other students a summary of the main information you have found.
Exercise 2
Join together with another small group who have worked on the other report topic.
Work with your group to present your information to students from the other group. You should also
answer any questions they might have.
Now listen to their presentation and take notes.
Page 23 of 85
Title Here
Page 24 of 85
Topic 3
Topic 3: Entity Relationship (ER) Modelling (I)

This topic provides an overview of Entity Relationship Modelling
 Understand the goal of Entity Relationship (ER) Modelling;

 Recognise different types of notation;
 Understand the concepts of an entity type, relationships and attributes;
 Begin to develop an approach to identifying entities.
3.2 Timings
Lectures: 2 hours
Tutorials: 1 hour
Page 25 of 85
Title Here
3.3.1 Selecting from More Than One Table
When we want to get information from more than one table, this involves what is known as a join
between the two tables. This basically means using the WHERE part of the SELECT statement to
do an equality operation between the columns that join the two tables.
For example, the workers and departments tables are joined in our data model by the fact that
dept_no is a foreign key on workers. This shows in which department each worker is. If we want to
select the department_name from departments and the first_name and last_name of the workers
from the workers table, the SELECT statement will look like this:
Select departments.department_name, workers.first_name, workers.last_name

From departments, workers
Where departments.dept_no = workers.dept_no;
We can make this a bit easier to write out by giving each of our tables an alias, usually a letter so we
do not have to keep writing the whole name for the table each time we refer to it:
Select d.department_name, w.first_name, w.last_name

From departments d, workers w
Where d.dept_no = w.dept_no;
Exercise 1
Make sure you can write and run the above query using the table aliases.
Exercise 2
Try the following exercises that all use similar statements:
1. Select the department_name from departments and the first_name, last_name and job_title
from workers.
2. Select the department_name and location from departments and the first_name and
last_name from workers.
3. Select the department_name and location from departments and the first_name and
last_name from workers. Only select workers who are in the ‘Packing’ department.
4. Select the department_name and location from departments, and the first_name, last_name
and job_title from workers, for just the managers that work in Cairo.
5. Select the job_title, age and location for all the workers who work in London.
6. Using the COUNT function and joining the two tables, count how many workers there are in
Lagos.
Page 26 of 85
Title Here
Exercise 3
The ORDER BY statement is a way of specifying the order in which you want your selected data to
appear. For example, to retrieve the emp_no and first_name of workers, we could order by emp_no
or order by first_name:
To order by emp_no:
Select emp_no, first_name

From workers
Order by emp_no;
To order by first_name
Select emp_no, first_name

From workers
Order by first_name;
1. Select the department_name from departments, the first_name, last_name and age from
workers. Order by the age.
2. Select the first and last names of the workers who work in Cairo and order them by their age.
Page 27 of 85
Title Here
topic.
Exercise 1
In the Private Study session for Topic 1, you were asked to collect paper data input forms from an
organisation, such as a library or government department or any other organisation.
Examine these forms and specify what entities and attributes might be needed in a database to
capture the data that they collect.
Bring both the paper forms and your analysis to the tutorial for discussion.
Exercise 2
Examine the library system diagram on Slide 24. Identify the missing multiplicities for Book to Loan
and Loan to Borrower.
Exercise 3
Revise the topics studied in the lecture. Make notes on the following topics and make sure you
understand the concepts:
 Entity Type
 Entity Occurrence
 Relationship Type
 Relationship Occurrence
 Attributes
 Multiplicity
Page 28 of 85
Title Here
3.5 Tutorial Notes
Exercise 1: Group Discussion of Private Study Activity
In small groups, discuss your findings to Private Study Exercises 1 and 2. You should collate your
findings and report back to class.
Exercise 2
Use this time to raise questions regarding the material. In small groups, discuss the concepts listed
below and report your findings back to the class.
Do you understand the following concepts? :
 Entity Type
 Entity Occurrence
 Relationship Type
 Relationship Occurrence
 Attributes
 Multiplicity
Page 29 of 85
Title Here
Page 30 of 85
Topic 4
Topic 4: Entity Relationship (ER) Modelling (2)

This topic provides an overview of Entity Relationship Modelling
 Construct an ER model from a scenario;

 Understand the purpose of a primary key;
 Understand the role of foreign keys;
 Recognise strong and weak entities;
 Identify and solve problems in ER models.
4.2 Timings
Lectures: 2 hours
Laboratory Sessions: 2 hours
Tutorials: 2 hours
Page 31 of 85
Title Here
The laboratory time allocation for this topic is 2 hours.
Exercise 1: Inserting Data
The INSERT statement allows us to add new data to our tables.
The basic structure of the INSERT statement was introduced in the first laboratory session.
Insert into departments values (1,'Packing','Cairo');
There is some variation on this. For example, to insert into the table but only particular fields, we
specify the columns in the insert statement. So, if we wanted to insert a new row into departments,
but did not yet know where it would be located, we would write:
Insert into departments (dept_no, department_name) values (4, ‘Marketing’);
Run this insert statement.
1. Insert another new department ‘Public Relations’. Its dept_no will be 5. Its location is Madrid.
2. Insert another new department ‘Research and Development’. Its dept_no is 6. Its location is
unknown at the moment.
3. A new employee has joined Research and Development. Their first_name is ‘Jonas’, their
last_name is ‘McKenzie’. They are 38 years old. Their emp_no is 8. They are 38 years old,
but currently their job_title has not been decided. Insert data into the workers table for them.
IMPORTANT: Make sure you commit your work by writing the command: commit;
Exercise 2: Updating Data
The UPDATE statement allows us to change data that already exists.
For example, we might have a script that changes someone’s age.
Update workers
Set age = age + 1
Where emp_no = 1;
This will add one to the age of worker one.
Try this Update script.
1. It has now been decided where the new Research and Development department should be
located. Write an update script that sets the location of this department to ‘Berlin’.
2. Write an update script to set the job_title of the employee McKenzie to ‘Manager’.
Exercise 3: Deleting Data
Page 32 of 85
Title Here
The DELETE statement allows us to get rid of data from our database.
For example, if we wanted to delete all the managers from the database, we would write the
following (do NOT run this; it is an example):
Delete from workers

Where job_title = ‘Manager’;
1. Use the INSERT statement to insert yourself in the workers table with an ID of 10. Then write
a DELETE statement to get rid of yourself from the database.
Page 33 of 85
Title Here
topic.
4.4.1 Review Material on ER Modelling
Please review all the materials for Topics 3 and 4 before going on to the exercises below. You
should make note of anything that you feel requires further clarification and bring your questions to
the tutorial for this topic.
4.4.2 Drawing ER Models
ER diagrams are one of the most important techniques used in database development. You will
need to master this technique in order to complete your assignment. Almost any work in the
database field requires an understanding and ability to construct ER diagrams.
The following are a series of short scenarios. Draw an ER diagram for each.
Exercise 1
A customer records systems for a mail order beauty products company. A customer is assigned to
one and only one geographical region. A customer may be interested in a number of different
product lines. Any particular product line belongs to one product category that may contain many
product lines.
Exercise 2: A Boat Rental System
A boat is rented to a customer for a set period of time. Any damage to the boat is recorded for that
particular rental.
Exercise 3: A Personnel Database
Employees can be members of one or more than one department.
Exercise 4: A Database for a Private Collection of Books
Each author may have written one or more books. A book might have one or more authors. Each
book belongs to one category.
Exercise 5: A Plane Ticket System
Each ticket is for one flight and one customer. A customer may book many flights. A flight has many
customers.
Page 34 of 85
Title Here
Exercise 6: A Film Rental Shop
The shop needs to keep track of rentals. A member can rent films. A film can be rented by many
members. A film can be rented by the same member more than once.
Page 35 of 85
Title Here
4.5 Tutorial Notes
The tutorials for this topic will last for 2 hours. You can expect to spend some of this time discussing
Exercise 1: Review of ER Modelling
Ask your tutor any questions you have with regard to Topic 3 and 4 on Entity Relationship Modelling.
Exercise 2: Review of Private Study Exercises
Work in a small group and review your answers to Private Study Exercises 1-6. Discuss the
decisions you took in drawing each ER diagram.
Exercise 3:
Answer the following questions in your own words.
a. Give an explanation of a fan trap using examples.
b. Give an explanation of a chasm trap using examples.
c. Why is it important to resolve many-to-many relationships into one-to-many relationships?
d. Give examples of entities, from any system or example system, which represent the
following: people, events, concepts, physical objects.
e. Define the following: simple attribute; composite attribute and single-valued attribute.
Page 36 of 85
Topic 5
Topic 5: The Relational Model (I)

This topic provides an overview of the relational model.
 Understand the aims of the relational model;

 Understand the basic concepts of the relational model;
 Define key terms of the relational model.
5.2 Timings
Lectures: 2 hours
Tutorials: 2 hours
Page 37 of 85
5.3.1 More on Creating and Modifying Tables
The basic format for creating tables was shown in Topic 1:

(dept_no integer not null,
location varchar(30)
primary key dept_no);
Be aware of variations depending on which database product you use. The variations are usually to
do with where you specify primary keys and foreign keys.

(dept_no integer not null primary key,
location varchar(3));
In the exercises for this laboratory session, we will look at creating a table from another table and
modifying an already existing table.
If we wanted to create a table which stored the unique names for the different types of jobs that are
available in our firm, we could create it in the conventional way. However, since all the values we
want already exist in the workers table, it would be easier to create the table with the data from the
existing workers table. This is done like this:
Create table job_type as

(Select distinct job_title from workers);
Exercise 1
Run the above script and use a SELECT statement to look at the results.
Exercise 2
Our new table only has one attribute and no primary key. Therefore, we should modify this with the
ALTER TABLE statement as follows:
Add a column for the salary of that job_title:
ALTER TABLE job_type

Add salary FLOAT;
Note that ‘Float’ designates the floating point data type. It is also known as REAL.
Run this script to alter the table.
Page 38 of 85
Exercise 3
We now need to add the primary key for this table. The primary key will be the job_title field.
ALTER TABLE job_type

ADD PRIMARY KEY (job_title);
Run this script.
Exercise 4
We must now enforce the fact that job_title in the workers table is now a foreign key to the job_type
table. We do this in a similar way using the ALTER table statement.
ALTER TABLE workers

ADD FOREIGN KEY (job_title) REFERENCES job_type(job_title);
Run this script.
Be aware that different vendors’ versions of SQL may implement these constraints slightly
differently.
Exercise 5
You will notice that the salary field is blank. Update the job_type table to set the salaries as follows:
 Managers earn 30000

 Packers earn 20000
 Admin Assistants earn 15000
 Accountants earn 28000
Exercise 6
You should now be confident enough to be able to create tables of your own design.
1. Design a table that keeps your personal details. This should include your name, address and
date of birth. Create this table using SQL with an appropriate primary key.
2. Design a table that keeps a list of your qualifications. This will have a foreign key to the table
with your personal details. Create this table using SQL with the appropriate primary and
foreign keys. You should include information about the name of the qualification, the level of
the qualification (e.g. Level 4), the name of the institution the qualification was taken at and
the final grade.
Page 39 of 85
topic.
Exercise 1
Look at the following table of data from a hair-care product supplier:
Customer ID Customer Name Customer Products Product Prices

C1 Manjeet Islam Hair dryer $35
Shampoo $7
Specialist comb set $8

C4 Tolu Amusia Hair net
C2 Sid James Specialist comb set $7
Hair dryer $35
C6 Ambereen Reeza Clippers
1. Identify why you think this table is not a relation.

2. Is the price of the hair net the same as the price of the clippers?
3. What are the prices of the hair net and the clippers?
4. Should the row for customer C2 be put before that of C4?
5. Redraw the table as a single table so that it qualifies as a relation.
Name: Customers
Customer ID Customer Name Customer Products Product Prices

C1 Manjeet Islam Hair dryer $35
C1 Manjeet Islam Shampoo $7

C1 Manjeet Islam Specialist Comb set $8
C4 Tolu Amusia Hair net
C2 Sid James Specialist Comb set $7
C2 Sid James Hair dryer $35

C6 Ambereen Reeza Clippers
Page 40 of 85
Exercise 2
Looking at the single table you have produced for Question 5 of the Exercise 1 above where you
were asked to redraw the table as a single table. There will still be a number of problems with it.
What issues are there with duplication and the primary key?
Exercise 3
Redraw the single table as three separate tables that have less duplication. You should be guided in
this by the example shown in the lecture for this topic.
Exercise 4
Identify the primary and foreign keys for each of your new relations.
Exercise 5
Review the content of this topic and conduct any further reading you need to undertake in order to
ensure that you understand the material. You should make note of anything that you still feel
requires further clarification and bring your questions to the tutorial for this topic.
Page 41 of 85
5.5 Tutorial Notes
Exercise 1: Group Discussion of Private Study Activity
In small groups, discuss your findings to Private Study Exercises 1-4. Your tutor will then lead a
class feedback session, during which you can also raise any questions you have about the material
covered in this topic.
Exercise 2: Questions
Answer these questions in your own words.
a. What is meant by the concept of data independence?
b. What was System R and what was its importance in the development of the relational
model?
c. What is meant by the term NULL and why can’t a primary key contain a NULL value?
d. What are the properties of a relation?
e. What is the purpose of foreign keys in a relational database?
f. Look at the following tables that form part of a database from a library system:
Book
BookID
BookName
AuthorID
BookTypeCode
ISBN
BookType
BookTypeCode
BookTypeDescription
Author
AuthorID
AuthorName
NationalityCode
Country
NationalityCode
CountryName
Borrower
BorrowerID
BorrowerName
Page 42 of 85
Loan
BorrowerID
BookID
LoanStartDate
LoanEnd Date
Idenfity the primary and foreign keys in the above schema.
Page 43 of 85
Page 44 of 85
Topic 6
Topic 6: The Relational Model (2)

This topic provides an overview of further aspects of the relational model.
 Describe the types of relational integrity;

 Describe the concept of functional dependency;
 Recognise anomalies in relations;
 Normalise a paper-type form.
6.2 Timings
Lectures: 2 hours
Tutorials: 2 hours
Page 45 of 85
In this session you should ensure that you have completed the exercises from the previous
laboratory sessions and are confident with SQL. You should ensure that you are confident with the
following:
 Creating tables
 Inserting data
 Updating data
 Deleting data
 Performing queries
If you have completed the previous laboratory exercises or if you complete them before the end of
this session, the following exercises should be attempted:
Exercise 1
Create a table called Student. The table should have the following attributes all of type varchar:
 Student_id
 First_name
 Last_name
 Gender
Student_id is the primary key for the Student table. The Student table is attached to the table
Course in a one-to-many relationship where Student is the many part of the relationship. The
primary key of the Course table is Course_id. You will also need to create the Course table.
Exercise 2
Insert the following person into the Student table created in Exercise 1. The student’s first name is
‘Chris’, his last_name is ‘Peters’, he is an 18 year old male and his student ID will be NCC001. Chris
will be on course DB001 Databases. You will also need to update the Course table.
Exercise 3
Update the Course table to show that the course name for course DB001 has changed from
Databases to Database Systems. Then update the Student table to show that student NCC001 no
longer wishes to take course DB001, they now wish to take course SE001 Software engineering.
You will also need to update the course table.
Page 46 of 85
Exercise 4
Delete the student with the ID NCC001 from the Student table.
Exercise 5
1. Using the COUNT function and joining Student and Course, count how many students there are
on the Software Engineering course.
2. Select the first and last names of the students who are on the Database Systems course and
order them by their gender.
Page 47 of 85
topic.
Exercise 1
Draw the ER diagram for the set of relations produced in Slide 20.
Exercise 2: Normalisation
Product Number: 009

Product Name: Wall Bracket
Product Type Code: HF
Product Type Name: Home Fitting
Supplier Suppliers Supplier’s Price Main Supplier

Number Name Product Ref Y/N ?
No
099 Gibbons WB09 £3 Y
0100 Jarrold’s 98383 £3.50 N
Fittings
0101 H Drammond B010 £3.75 N
098 Crambornes Br 7 £3.99 N
078 Jamison 8383 £3.99 N
Above is a form used by a firm to keep track of the different suppliers that supply them the same
part. Supplier’s Product Ref No is the reference number given to the part by the supplier. Main
Supplier Y/N indicates whether this is their preferred supplier of the part.
Using the techniques discussed in the lecture, break this document down into a set of third normal
form relations.
Exercise 3
Page 48 of 85
6.5 Tutorial Notes
In small groups, discuss your findings to Private Study Exercises 1 & 2. Your tutor will then lead a
class feedback session, during which you can also raise any questions you have about the material
covered in this topic.
Answer these questions in your own words.
a. Give a definition for first, second and third normal form.
b. What is the purpose of normalisation? Why is it necessary to split data into separate tables?
c. Why do you think Entity Diagrams are usually referred to as a ‘top-down’ approach and
normalisation as a ‘bottom-up’ approach?
d. Describe the concept of functional dependency.
e. What role does functional dependency play in the process of normalisation?
Page 49 of 85
Page 50 of 85
Topic 7
Topic 7: SQL (I)

This topic provides an overview of SQL (Structured Query Language).
 Explain the purpose of SQL;

 Outline the basic concepts of SQL;
 Understand that there are different flavours of SQL.
7.2 Timings
Lectures: 2 hours
Tutorials: 1 hour
Page 51 of 85
Exercise 1
In Topic 5, you should have designed and created your own personal details tables. Gather details
of at least 8 of your fellow students. You should get data for both tables. Insert the data into the
tables.
Exercise 2
Write a query that shows all the qualifications for a named person. This could be yourself.
Exercise 3
Write a query that shows which institution each student has attended. Order this by the students’
last name.
Exercise 4
Show all those people who have achieved a Level 3 qualification.
Exercise 5
Write a query that shows how many qualifications you have.
Exercise 6
If there is not one already there then add a column to the personal_details to record a persons’ age.
Exercise 7
Update the personal_details table with each person’s age.
Exercise 8
Write a query to show all first names, last names and the level 2 qualifications for students who are
under the age of 20;
Exercise 9
Create a new table called ‘Qualification_Type’ using the ‘as’ statement that shows all the
qualifications that exist. There should be one row for each qualification without duplications.
Page 52 of 85
Exercise 10
Add a column to the qualification_type table to show the level the qualifications is at.
Exercise 11
Update the qualification_type with the correct level for each qualification.
Exercise 12
Once the qualification_type table is updated with the level then the level can be deleted from the
qualification table. Use the drop column scripts as shown below:
Alter table qualification

Drop column qual_level;
Exercise 13
Make the qualification attribute the primary key of qualification_type;
Exercise 14
Now create a foreign key between qualification and qualification_type using the qualification
attribute.
Exercise 15
Rewrite the query from Exercise 4. Show all those people who have achieved a Level 3 qualification.
You will now need to include all three tables.
Page 53 of 85
topic.
Exercise 1
In a Customer Accounts System, the following tables have been created using SQL DDL
commands.
1. CREATE TABLE Customer_Purchase

(
ItemNo char (4) not null,
CustomerNo char (4) not null,
PurchaseDate date
PaymentTotal decimal
PRIMARY KEY (ItemNo, CustomerNo),
FOREIGN KEY (CustomerNo) REFERENCES Customer(CustomerNo)
FOREIGN KEY (ItemNo) REFERENCES Purchase(ItemNo) );
2. CREATE TABLE Customer

(
CustomerNo char (4) not null,
CustomerName char(10),
City char(20),
PRIMARY KEY (CustomerNo) );
3. CREATE TABLE Item

(
ItemNo char (4) not null,
Item Name char (30) not null,
Item Type Code char(4) not null,
PRIMARY KEY (ItemNo),
FOREIGN KEY (Item Type Code) REFERENCES Item Type (Item Type Code));
4. CREATE TABLE Item Type

(
Item Type Code char (4) not null,
Item Type Name char(30) not null,
PRIMARY KEY (Item Type Code));
A user tried to execute the following commands in the given order to insert values into the created
tables. Find those commands that would result in the return of an error message. Explain why.
1. INSERT INTO Item Type values (‘2345’, ‘Hand Drill’, 25);

2. INSERT INTO Item Type values (‘2344’, ‘Electronic Drill’);
3. INSERT INTO Item Type values (‘2346’, ‘Drill Bit’);
4. INSERT INTO Item values (‘1010’, 2344, ‘2344’);
5. INSERT INTO Item values (‘1005’, ‘Dulux Cordless Electronic Drill’, ‘2344’);
6. INSERT INTO Item values (‘1005’, ‘5mm Ceramic Drill Bit’,‘2354’);
7. INSERT INTO Item values (1005, ‘Standard Long Cord Electronic Drill’,‘2344’);
8. INSERT INTO Customer values (‘5566’, ‘HASNET’, ‘LONDON’);
Page 54 of 85
9. INSERT INTO Customer values (‘5667’, ‘SONGARA’, ‘BIRMINGHAM’);
10. INSERT INTO Customer values (‘5667’, ‘SINGH’, ‘CAIRO’);
11. INSERT INTO CustomerPurchase values (‘1005’, ‘5566’, ’03-FEB-2004’, 20.00);
12. INSERT INTO CustomerPurchase values (‘1007’, ‘5566’, ’04-FEB-2004’, 40.00);
Exercise 2
Using online resources, compare the features of any two implementations of SQL, as provided by a
vendor. For example, you could compare Oracle SQL*Plus with MySQL.
Use your own words to write your answer and make sure you include a reference list of the places
where you found the information.
Page 55 of 85
7.5 Tutorial Notes
The tutorials for this topic will last for 1 hour.
In small groups, discuss your findings to Private Study Exercise 1, asking your tutor for clarification
when needed.
Exercise 2
Work in a small group. Present your findings from Private Study Exercise 2 to the other students and
answer any questions they may have.
Make notes on the findings of the other students to increase your understanding.
Your tutor will then run a whole class feedback session.
Page 56 of 85
Page 57 of 85
Topic 8
Topic 8: SQL (2)

This topic provides an overview of further aspects of SQL.
 Understand the syntax of the create statement;

 Understand the construction of more complex selections;
 Recognise the issues around error messaging and query optimisation.
8.2 Timings
Lectures: 2 hours
Tutorials: 1 hour
Page 58 of 85
8.3.1 Aggregation
Many important queries in a database involve using the aggregation functions COUNT, MIN, MAX,
SUM and AVG.
COUNT counts the number of times something occurs in a database table; the number of rows that
meet a particular condition.
MIN finds the minimum or lowest occurrence of an attribute in a database table.
MAX finds the highest occurrence of an attribute in a database table.
AVG finds the mean average of an attribute in a database table.
SUM finds the totals of all the values of an attribute in a database table.
Example: To find the number of rows in the workers table, we use the primary key, Emp_no, as
there will be exactly one occurrence of this value for each row and no duplicates.
Select count(emp_no)
From Workers;
8.3.2 Part One: Using the Workers, Departments and Job_Types Tables
Exercise 1
Try the above select statement.
Exercise 2
Find the average age of the workers in the workers table.
Exercise 3
Find the average age of the managers in the workers table.
Exercise 4
Find the minimum, maximum, average and the sum of the age all the packers in the workers table.
Exercise 5
Write a query that tells you the age of the youngest employee in Cairo. You will need to use the
joining of tables that you have studied in previous tutorials.
Page 59 of 85
Exercise 6
Write a query that tells you how many employees there are in ‘Lagos’.
Exercise 7
Write a query that finds the job_type with the highest salary. You will need to use the job_type table
you created in Topic 5.
Exercise 8
What is the total of all salaries paid?
Exercise 9
What is the lowest salary paid in Cairo?
8.3.3 Using Personal_details, Qualifications and Qualification_types Tables
The following operations use the personal_details, qualifications and qualification_types tables.
Exercise 10
Select the maximum level of qualification attained overall.
Exercise 11
Select the highest level of qualification attained by you.
Exercise 12
Select the highest level achieved for those students who are over 20.
Exercise 13
How many students have achieved level 2 qualifications?
Exercise 14
What is the average grade for level 3 qualifications?
Exercise 15
What is the average level of qualification achieved by students under 19?
Page 60 of 85
topic.
The following tables are for a garden products database.
Customers
Customer ID Customer Name
C1 Arthur Smith
C4 Samson Odogo
C2 Jagpal Singh
C6 Jenkins Watson
Products
Product Price
Land mower £100
Slug Repellent £5
Trowel £8
Weed killer
Knee rest
Page 61 of 85
Customer Products
Customer ID Product
C1 Lawn Mower
C1 Slug Repellent
C1 Trowel
C4 Weed Killer
C2 Weed Killer
C2 Lawn Mower
C6 Trowel
Use these tables to complete the exercises below.
Exercise 1
Write an SQL statement that returns the names of all the customers.
Exercise 2
Write an SQL statement that returns the names of all the customers who have bought a lawn
mower.
Exercise 3
Write an SQL statement that finds the average price for all the products.
Exercise 4
Write an SQL statement that sets the price of weed killer to £5.
Exercise 5
Write a query that gives the total spent by each customer.
Page 62 of 85
Exercise 6
Review all the material for Topics 7 and 8 (SQL). You should make sure that you understand the
following concepts and be prepared to raise any questions about them in the next tutorial:
 The purpose of SQL

 Data definition language (DDL)
 Date manipulation language (DML)
 How to update data on a database
 How to retrieve data on a database using the select statement
 How to create and modify tables using SQL
 The advantages and disadvantages of SQL
Page 63 of 85
8.5 Tutorial Notes
Work in a small group and review your answers to Part One of the private study exercises. Your
tutor will then lead a whole class feedback session.
Answer the following questions which relate to aspects of SQL.
a. SQL has two major components, DDL and DML. What are these components and what are
their functions?
b. What are the disadvantages of the CHAR data-type and how does the VARCHAR data-type
overcome these?
c. What is the purpose of the ROLLBACK statement?
d. List the advantages and disadvantages of SQL.
e. What are the advantages of using the ALTER TABLE statement as opposed to creating a
new table from scratch when changes are needed?
f. What is the purpose of the GROUP BY clause?
Exercise 3: Review of SQL
Take part in a class discussion around the relevant points of Topics 7 and 8 that are listed in Private
Study Exercise 6. Ask your tutor any questions you have about SQL.
Page 64 of 85
Page 65 of 85
Topic 9
Topic 9: Database Design

This topic provides an overview of database design.
 Understand the process of requirements gathering;

 Design a set of database tables from an entity model;
 Document the tables, columns and domains in a database using a data dictionary;
 Understand the use of Case tools.
9.2 Timings
Lectures: 2 hours
Tutorials: 2 hours
Page 66 of 85
9.3.1 Grouping
In the previous laboratory session we looked at aggregation. You were asked to find the minimum,
maximum, average and the sum of the age all the packers in the workers table.
The suggested solution was like this:
Select min(age), max(age), avg(age), sum(age)

From workers
Where job_type = ‘Packer’;
But what if we want to provide a query that shows us the maximum age for each of the different
types of workers? SQL provides a group by clause that allows us to do this.
Select job_title, max(age)

From Workers
Group by job_title;
Exercise 1
Run the above query and study the results.
Exercise 2
Write a query that finds the average age for the employees in ‘Cairo’. Group this by job_title.
Exercise 3
Write a query that shows the age of the eldest workers in each department. Group this by the
dept_no. You do not have to show the department name.
Exercise 4
Find the departments that have an average age of over 40. You do not need to show the department
name.
Exercise 5
Find the maximum age, the minimum age, the average age and the job title for those jobs where the
average age is above 35. Group this by the job title.
Page 67 of 85
Exercise 6
As part of the laboratory exercises in Topic 5, you created two tables that kept personal information
about yourself and the qualifications that you have. In Topic 7 you should have added some new
rows about your friends and their qualifications to these tables.
Now use the aggregate functions from Topic 8’s Laboratory Session and the ‘Group By’ clause from
Topic 9’s Laboratory Session to create a set of useful queries using these tables.
Page 68 of 85
topic.
This topic’s private study involves practising some database design based on elaboration of a
previous private study exercise.
Exercise 1
In Topic 4, you were asked to draw an ERD for a boat rental system. The requirements were the
following:
 You should be able to record that a boat is rented to a customer for a set period.
 Any damage to the boat is recorded against the particular rental.
 A boat should have a name.
 All boats are of the same type (yacht).
 Damage is classified as being hull, interior or other.
Using the ERD for this system, produce a data dictionary specifying the base relations (tables),
attributes and domains. The data dictionary should be in the format given in the lecture.
Exercise 2
Find some examples of CASE tools online. What are their features? For how much of the database
development process do they cater? What might be their disadvantages?
Prepare a brief written discussion for the tutorial.
Exercise 3
Investigate a systems development methodology such as SSADM. Each stage or step has what is
known as a set of ‘deliverables’. These are the outcomes of that stage which will form the basis of
work in the next stage.
What are the deliverables from analysis, design and implementation stages for the methodology that
you have investigated?
Exercise 4
Page 69 of 85
9.5 Tutorial Notes
In small groups, discuss your findings to the Private Study Exercises, asking your tutor for
clarification when needed.
Exercise 2
Answer the following questions, which relate to approaches to development.
1. What is the difference between analysis and design?
2. Why is the traditional systems development approach called the Waterfall Model?
3. What stages in a traditional waterfall lifecycle do you think overlap with the conceptual,
logical and physical stages of database design?
4. What is prototyping and what are its advantages?
Exercise 3
Outline the difference between Conceptual, Logical and Physical Design.
Page 70 of 85
Page 71 of 85
Topic 10
Topic 10: Supporting Transactions

This topic provides an overview of supporting transactions.
 Identify transactions;
 Understand business rules and their implications;
 Recognise potential performance issues;
 Identify the potential need for de-normalisation.
10.2 Timings
Lectures: 2 hours
Tutorials: 2 hours
Page 72 of 85
10.3.1 Nested Sub-queries
When we need to find the result of some complex enquiry on our database, we can put one query
inside another. This is known as nesting.
Consider the following example:
Select d.department_name, d.location

From departments d, workers w
Where d.dept_no = w.dept_no
And w.age =
(select max(w2.age)
From workers w2);
10.3.2 Use of the Workers, Departments and Job_types Tables
Exercise 1
Run the above query. What information is it telling us?
Exercise 2
Modify the above query to select the department and its location that has the youngest manager.
Exercise 3
Write a query using a nested sub-query to find those department IDs where the average age of the
workers is less than the average age for all the workers in the company.
Exercise 4
Note that the result above produces multiple repeating values. Use the ‘Group By’ clause after the
closing brackets to group by department ID.
Exercise 5
Make this query more user-friendly by changing the department ID to the department name.
Exercise 6
Using a nested sub-query, get the first names of all those workers who have the maximum age for
the whole company. Remember to use Group By if you are getting repeating values.
Page 73 of 85
10.3.3 Using the Personal_details, Qualifications and Qualification_types Tables
The following exercises use the Personal_details, qualifications and qualification_types tables.
Exercise 7
Show the first name and last name of all the people who have qualifications at level 3 and are older
than the average age.
Exercise 8
Show a count of those people who have got a higher grade than the average for their level 2
qualification.
Page 74 of 85
topic.
Exercise 1
Write the SQL for creating the view for the following transaction:
List the details for damage: to which boats, during which hire periods and for which customers.
Exercise 2
Explain what you think the purpose and effect of this view would be for the Boat Hire system.
Exercise 3
Use online resources to look for jobs advertised for database development work. What sorts of skills
are being required for the jobs? What software is involved?
Exercise 4
One of the definitions of a transaction is that it should possess four basic properties, usually
remembered by the abbreviation ACID:
 Atomicity
 Consistency
 Isolation
 Durability
Research and give a definition for each of these properties.
Exercise 5
Review all the material from this topic and prepare any questions for the tutorial session.
Page 75 of 85
10.5 Tutorial Notes
In groups, review the work you carried out during your private study.
Exercise 2
Give an explanation as to your understanding of a business rule. Using a system you are familiar
with, from an example in the course materials or through personal experience, specify business
rules that apply to that system.
Exercise 3
A student record system consists of three tables: Students, Modules, StudentsModule and the ER
shown below:
1 0..* 0..* 1
Student StudentModule Module
Data dictionary
Student
StudentID (PK)
StudentFirstName
StudentLastName
StudentAddress
StudentAge
StudentModule
StudentID(PK)(FK)
ModuleCode(PK)(FK)
Semeseter
Year
Result
Module
ModuleCode(PK)
ModuleName
Complete a CRUD matrix for the following transactions:
a. Insert a new student.
Page 76 of 85
b. List a student’s personal details and results for all of the modules they have taken. Include
the module name.
c. List details of all the modules.
d. Insert a new module.
e. Allocate a student to a module.
f. Assign a result to a student for a module.
Exercise 4
A number of business rules have been defined for this student records system:
1. All students should have an enrolment date recorded for them and a final completion date.
All students should be deleted from the system three years after their completion date.
2. Secondly, students should be classified as being ‘Home’ or ‘Overseas’.
3. Students should be allowed to retake a module that they fail.
Discuss how each of these business rules might be enforced on the system. This might require the
creation of new attributes or other database structures.
Page 77 of 85
Topic 11
Topic 11: Database Implementation

This topic provides an overview of database implementation issues and the implementation
environment.
 Understand how constraints can be enforced;

 Insert multiple rows of data in SQL;
 Explain some of the features of the Oracle RDBMS.
11.2 Timings
Lectures: 2 hours
Tutorials: 2 hours
Page 78 of 85
11.3.1 Views
A view can be thought of as a virtual table. It is the result of an SQL select operation and, to the
user, looks like a table with rows and columns. However, unlike a table, it does not necessarily exist
permanently in the database.
The syntax to create a view is similar to a select statement, but with a ‘Create View’ added. For
example:
Create View WorkersOverThirty

As Select Emp_no, First_name, Last_name
From Workers
Where Age > 30;
Exercise 1
Run the above script and then run a select statement to see all the data from it.
Exercise 2
Create a view that will contain the last name and the job title for all the workers in Cairo.
Exercise 3
Create a summary view that includes the emp_no, first_name, last_name, department_name,
location, the job type and the salary (from the job type table).
Exercise 4
Now recreate the summary view from Exercise 4, but make it only for Workers who earn more than
25000.
Note: you will have to give it a different name from the previous summary.
Page 79 of 85
11.3.2 Indexes
Exercise 5
An index is a structure in a database that helps queries run more quickly. This will be discussed in
more detail in a coming lecture. Indexes can be unique, meaning that they will prevent a duplicate
value from being added to that column, or they can be non-unique.
The syntax to create a unique index for the Workers table column Emp_no is:
Create Unique Index EmpNoIndex on Workers(Emp_No);
Run this script.
If you need to get rid of this index, the syntax is:
Drop Index EmpNoIndex;
11.3.3 Constraints
Exercise 6
As well as having constraints to enforce primary and foreign keys, constraints can also be added to
enforce a business rule. This will be discussed in more detail in a coming lecture. The example
below enforces a rule that all our workers must be 70 or younger.
Alter table Workers

Add Constraint Valid_age
Check (age < 71);
Run this script and then see what happens if you try to update someone’s age to over 70.
Page 80 of 85
topic.
Exercise 1
You should prepare a short presentation on the database architecture of the vendor that you used to
implement your assignment. Focus should be on the logical structure and the physical structure. The
degree of detail that you will need to present should be guided by the lecture slides, i.e. it should be
an overview in your own words rather than a detailed technical paper.
Exercise 2
Your lecturer will assign one of the following topics, concerning bulk loading facilities, to you.
Prepare a short report about the features and the facilities of the tool that you are assigned to
investigate.
Bulk insert in SQL server
http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html
Oracle SQL loader
http://oreilly.com/catalog/orsqlloader/chapter/ch01.html
My-SQL uses something called 'Bulk Insert'
http://mysql.bigresource.com/Bulk-insert-from-text-files-dDPRzHYo.html#2t6P0D5I
Exercise 3: Review of All Topics
Review the materials for all the topics up to this week and prepare questions for the final overview
lecture in Topic 12.
Page 81 of 85
11.5 Tutorial Notes
Exercise 1: Vendor Presentation
Give your presentation on the database architecture of the vendor you have chosen to the rest of
the group.
Takes notes on interesting points while other students are speaking. Your tutor will also lead a
discussion to summarise the findings of the class at the end of the presentations.
Exercise 2
Work in a small group with other students who have written a report on the same topic during
private study time.
Discuss the information you have found. You should take the opportunity to add any additional
information to your own notes.
Now prepare to present your information to students who have worked on the other report. You
should work together as group to prepare a short (5 minutes), informal presentation which will give
the other students a summary of the main information you have found.
Exercise 3
Work with your group to present your information to students from the other groups. You should also
answer any questions they might have.
Now listen to their presentations and take notes.
Page 82 of 85
Page 83 of 85
Topic 12
Topic 12: Summary

This topic provides an overview of the module materials as a whole.
 Recognise the topics you have studied on the module;

 Recognise links to other modules.
12.2 Timings
Lectures: 2 hours
Page 84 of 85
12.3 Private Study
You should spend approximately 7.5 hours on the Private Study for this topic.
Exercise 1
You are now at a point where you should be revising for the examination. The lists below, although
not exhaustive, indicate things that you should understand, be able to describe and be able to
produce, in order to do well in the examination.
Understand
 Metadata
 Fan traps
 Chasm traps
 The concepts associated with SQL
 Constraints on data
In order to make sure that you can show your understanding of the above,read through the lecture
slides and make short notes on each of the points. Revise from these notes. You can also ask your
tutor for guidance.
Describe
 Entity types
 The relational model
 The database development process
 How databases are used
 How databases are deployed
In order to make sure that you can describe the above, read through the lecture slides and make
detailed notes on each of the points. Revise from these notes. You can also ask your tutor for
guidance.
Produce
 An ER Diagram from a scenario

 SQL SELECT statements from information given to you
 SQL CREATE statements from information given to you
 SQL INSERT statements from information given to you
 CRUD Matrices from information given to you
 Normalised tables from information given to you
In order to make sure that you can produce the above, go through the appropriate laboratory, tutorial
and private study exercises and make sure that you can answer the questions. If you are having
difficulties answering the questions, you may need to either revisit the lecture slides or ask your tutor
for guidance.
GOOD LUCK IN YOUR EXAMINATION
Page 85 of 85

DB Student Guide V1.1

Uploaded by

Copyright:

Available Formats

DB Student Guide V1.1

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DB Student Guide V1.1

Uploaded by

Copyright:

Available Formats

LEVEL 4

DB Student Guide V1.1

Version Date Revision Description

© NCC Education Limited, 2011

1. Module Overview and Objectives

2. Learning Outcomes and Assessment Criteria

4 Entity Relationship 1/10  Constructing ER models

11 Database 1/10  The implementation environment

4. Related National Occupational Standards

Related National Occupational Standards (NOS)

5. Teaching and Learning

5.3 Laboratory Sessions

5.4 Private Study

6. Further Reading List

Benyon-Davies, P. (2003). Database Systems, 3rd Revised Edition. Palgrave Macmillan.

Connolly, T. and Begg, C. (2009). Database Systems: A Practical Approach to Design,

Topic 1: Introduction to the Module and Database

1.1 Learning Objectives

On completion of the topic, you will be able to:

 Give a definition of what a database is;

Laboratory Sessions: 1 hour

Private Study: 7.5 hours

Create table departments

Create table workers

Exercise 3: Insert Statements

Insert into departments values ('1','Packing','Cairo');

Now use insert statements to create the following workers:

Emp_no First_name Last_name Job_title Age Dept_no

1 Lawrence Surani Manager 56 1

2 Jason Argo Manager 33 2

3 Emily Villa Manager 32 3

4 Ahmed Mukani Packer 23 1

5 Joe Todj Packer 24 1

6 Hattie Smith Accountant 56 2

7 Sally Boorman Admin 34` 3

Exercise 5: Looking at the Data

select <column_name> from <table_name>

To see all the columns:

select * from <table_name>

Use the select command to view the contents of your tables.

Exercise 1: Databases in Organisations

 What sort of data would they be interested in collecting?

Exercise 1: Group Discussion of Private Study Activities

Exercise 2: Data and Information

Answer the following questions:

1. What is the difference between ‘data’ and ‘information’?

Topic 2: Databases and Database Management Systems

2.1 Learning Objectives

On completion of the topic, you will be able to:

 Describe the main features of a database system;

Laboratory Sessions: 1 hour

Private Study: 7.5 hours

2.3.1 Simple queries

Select <column name>, <column name>

Select emp_no, last_name

Try this and look at the results.

Select the name and location of all the departments.

Select all the columns for the department located in Cairo.

2.3.2 Using the COUNT Function with Queries