CO2209 Database Systems
Your coursework assignment should be submitted as a single PDF file, using the following
file-naming conventions:
YourName_SRN_COxxxxcw#.pdf (e.g. MarkZuckerberg_920000000_CO2209cw1.pdf)
• YourName is your full name as it appears in your student record (check your student
portal);
• SRN is your Student Reference Number, for example 920000000;
• COXXXX is the course number, for example CO2209; and
• cw# is either cw1 (coursework 1) or cw2 (coursework 2).
If you’re not sure how to do this, come to the course discussion board and ask.
It should take between 20 and 40 hours to complete, depending on how much you already
know about the topics. There are some easy parts, and some which are more challenging.
Each part of the coursework assignment has been chosen to help you understand some key
issue in the subject of databases. It should be undertaken with the subject guide, Volume I, at
hand. There are four Appendices at the back to supplement the information in the subject
guide.
The best way to approach this coursework assignment is to look over the whole thing first,
and get an idea of what you will want to concentrate on as you read the subject guide and
other materials such as your textbook. If you have never encountered relational database
ideas before, the terms will be unfamiliar, and it will take some time for them to become part
of your everyday working inventory of ideas – learning these definitions by heart might be a
good strategy to start with, because this will help you gain a deeper conceptual understanding
of them as you do the coursework assignment.
Background
If you look closely at almost any online enterprise, public or private, you will find a database
system behind the public face. Therefore, the more knowledge and experience you have
with database systems, the better your chances are of finding a good job. The aim of this
coursework assignment is to help you gain some of that knowledge and experience.
This coursework assignment (and coursework assignment 2) will introduce you to the basic
concepts of the most common data model (the relational model), around which most
databases are constructed. It will give you some practical experience in designing,
implementing and using a database management system, and it will acquaint you with some
of the issues currently of concern in the database world. To put it another way, if you do this
coursework assignment conscientiously, then you should to be able to talk confidently about
databases in a job interview, as well as in the examination.
This course can only introduce you to the basics. To start to become a professional in the
field, you will need to gain experience with a real database, which will be much more
2
complex in every way than the simple examples we will look at here. You will also need to
learn how to stay up to date in this area, and how to keep educating yourself about
developments in it long after you have finished this course. This coursework assignment
aims to help you understand how to do this.
Why it is important for you to complete this coursework assignment
The coursework assignment has two practical aims: first, to provide you with a ‘rehearsal’ for
the examination. Second, more importantly, the coursework assignment reaches areas that
are not covered in the subject guide. If you do the coursework assignment, you will be up-to-
date with respect to recent developments in the database field. Several of the questions
have been written with an eye to the questions you might be asked during a job interview,
and they are designed to allow you to give a competent answer to them.
Suggested sources: this coursework assignment has been designed around the subject
guide, but in addition to this, and the recommended reading, you will want to consult the
wealth of information available via the internet. Appendix I, we have provided some links
relating to MySQL, but you should not confine yourself to them. Becoming familiar with
reliable sources of information about current database systems, and using them to keep your
knowledge up to date, is part of becoming a database professional.
There are also discussions about dealing with the practical issues involved in database
design in the other appendices to this coursework assignment. Be sure to look at the
Appendices to become familiar with what they contain. You are strongly advised to consult
them as you do these coursework assignments, as well as when you revise for the
examination.
A note on Wikipedia: Wikipedia is the place where most people begin their online searches.
This coursework assignment will frequently direct you to Wikipedia articles. Wikipedia
articles often provide a good introduction to a topic (although occasionally they are over-
technical and not useful for beginners). However, Wikipedia is not an unquestionable
authority. (No authority is unquestionable, of course.)
You cannot treat Wikipedia the same way that you treat ordinary references because the
articles can be written by anyone, can have many authors (who are usually anonymous) and
the contents can be changed at any time. Remember that the author(s) of these articles may
only have a partial knowledge of the subject, may be overly partisan and/or have a material
interest in convincing readers of a particular point of view.
For example, the popular DBMS MySQL can be used with several different software
systems (or ‘engines’) for physical layout in secondary storage and indexing; one is called
MyISAM and another is called InnoDB. If you read the Wikipedia article [accessed during
summer 2016] comparing the two,
[https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines] you will see that it
clearly has been written by someone who is an InnoDB enthusiast. It would be very
dangerous to make a decision about the relative merits of these two alternative database
engines based solely on that article, which is highly biased.
Another example is the Wikipedia article on Data Integrity
[https://en.wikipedia.org/wiki/Data_integrity]. The author of this article has only a middling
grasp of English. The contents are useful, but you would definitely not want to quote from
this article due to its poor grammar.
Therefore, you should never rely only on Wikipedia as a source of information. When you
use Wikipedia, you should check the warnings at the top of the page to see if ‘the neutrality
3
of this article is disputed’ or if there are any other listed concerns about it. It is good practice
to consult the ‘Talk’ page for the article and see if there are disputes among the contributors.
Use Wikipedia, if necessary, as a starting place and as a source of links to follow, but do not
quote Wikipedia articles as authoritative sources. In fact, in a formal report that requires a list
of references, you are better off not listing Wikipedia at all. If you’re not sure about
something regarding databases that you have found in Wikipedia, or anywhere else online,
please ask about it via the course discussion board.
Coursework assignment and the examination
As mentioned earlier, the coursework assignments are also designed to help you prepare for
the examination, so you will doubly gain from making the most of them. You will notice that
both coursework assignments include some very simple initial assignments, which consist
essentially of having you pay close, systematic attention to the subject guides and making
notes about the most important parts. Copy these notes onto separate sheets of paper (and
perhaps use them to make ‘flash cards’), and you will then have a ready-made set of
revision materials. However, please note, your revision should start in November or
December at the latest, not in April.
Coursework assignment and real life
This coursework assignment has also been prepared to give you a solid footing should you
face questions on practical database subjects during a job interview. You will be able to
honestly say that you have had experience of implementing a database, albeit a ‘toy’ one, of
making relational designs, and, upon completion of the coursework assignment 2, of using a
genuine database. In addition, some of the questions help you to engage you with current
developments in this fast-moving field.
IMPORTANT NOTE:
It is important that your submitted assignment is your own individual work and, for the most
part, written in your own words. You must provide appropriate in-text citation for both
paraphrase and quotation, with a detailed reference section at the end of your assignment. It
is important that your submitted assignment is your own individual work and, for the most
part, written in your own words. Copying, plagiarism and unaccredited and/or wholesale
reproduction of material from books online sources, etc. is unacceptable, and will be
penalised (see How to avoid plagiarism).
4
Coursework assignment 1
The coursework assignment this year has been designed to address some of the typical
errors and confusion with respect to relational databases shown in previous years’
coursework assignments. Sometimes even mature students who have worked with
databases for many years have revealed gaps in their knowledge, especially about certain
concepts of basic design.
Do not hesitate to use the course discussion board if you have questions – it’s one of the
great advantages of an online course that it’s actually easier to get help than in many
traditional courses, if you will take advantage of the facilities offered.
This coursework assignment has eight basic tasks: (A) downloading and setting up the
software for managing a database, (B) becoming familiar with the manual, (C) drawing up a
visual model of the properties and inter-relationships of the related groups of data we will
incorporate in a database, (D) showing certain key facts about how the data within these
groups is related, (E) drawing up an abstract ‘schema’ of our database using the previous
two tools, then (F) implementing a ‘toy’ database, (G) seeing what should happen if we
violate some of the rules governing what data can, and cannot, be in the database, (H)
finally running some queries on the database itself.
If you complete these tasks, you should receive a good introduction to the fundamental ideas
of database. Do not hesitate to use the course discussion board if you have questions. One
of the great advantages of an online course, if you take advantage of the facilities offered, is
that it is actually easier to get help than in many traditional courses. However, please be
careful not to post anything that will form. part of your submission. In addition to the
help from the VLE tutor, you will find that many of your fellow students are experienced
database users already and will be more than willing to discuss issues.
A. Downloading a DBMS
You can – and should – get started on this part of the coursework assignment immediately,
even if you know nothing about databases, just in case you have some problems setting this
system up.
Download and install the MySQL database package on your own computer.
You can download MySQL from here: http://dev.mysql.com/downloads/mysql
(Get the 5.7.9 version for whatever Operating System you have. If you already have an
earlier version of MySQL, it is not necessary to download a later version.) You will have to
create an Oracle Account if you do not already have one, but this only takes a few minutes.
Note: Depending on how fast your connection to the internet is, this download may take a
long time. It’s a large (320 MB) package.
Note: Most people have no trouble downloading and installing the MySQL package.
However, problems can occur. If they do, and you cannot solve them quickly by yourself, you
must connect with the course discussion board for this course straightaway and get help.
Further helpful links to sources of information and help with MySQL can be found in
Appendix I.
If for some reason you cannot get a working version of MySQL installed on your computer,
download and install MariaDB from http://mariadb.org/en/. This is a ‘drop-in’ equivalent of
MySQL, and it was started by people concerned about MySQL’s public status, after the
5
Oracle Corporation bought it. They fear that it may eventually be left to wither on the vine.
You can read about MariaDB here: https://en.wikipedia.org/wiki/MariaDB
What to submit: write a short report describing your own experience of databases and/or
database system software. If you have had no experience, describe any issues/problems
you had downloading and installing MySQL. If you have had no experience of databases,
and also had no problems downloading and installing MySQL (or you already had it on
your computer), you should write a short report (no more than one page) summarising the
contents of the MySQL Manual. You can do this by listing (but not copying) the most
important items in the Table of Contents.
[2 hours. 5 marks]
B. Learning about MySQL
Look at the tutorial on using MySQL, which you can find at:
https://dev.mysql.com/doc/refman/5.7/en/tutorial.html (Note: the tutorial refers to tables,
rows and columns, which in this context mean relations, tuples and attributes.)
Read through this part of the MySQL manual and answer the following questions.
B(1) What is the URL of the section entitled ‘B.5.2 Common Errors When Using MySQL
Programs’?
B(2) The tutorial tells us that after a query that you enter is executed, the MySQL server
‘shows how many rows were returned and how long the query took to execute… ’ Is the
server performance time thus presented precise, and if not, why not?
B(3) What is the query that would have MySQL tell you your user name, its version number,
and the current date?
B(4)(a) What is the meaning of each of the following MySQL prompts?
Prompt Meaning
mysql>
->
‘>
‘>
`>
/*>
B(4)(b) Suppose you enter a query, but nothing happens. You see that the MySQL prompt
now looks like this: -> What have you forgotten to do? What must you add for the query to
complete?
B(5) [Section 3.3] What query will tell us what databases currently exist?
B(6) Suppose we know that a database called ‘SchoolData’ currently exists. How do we
access it so that we can modify it or query it?
B(7) What is the command to create a database called ‘SchoolData’ if one does not exist?
Subject guide reference: pages 34-55 of Database Systems, Volume 1.
6
B(8) Suppose you create a database called ‘SCHOOLDATA’, and later try to access it by
typing USE Schooldata. Does it make a difference (a) under Windows, and (b) under UNIX?
B(9) Once you are using a particular database, how can you see what tables it consists of?
B(10) If you know an existing table’s name – let’s say it’s called STUDENTDATA – how can
you get MySQL to show you its structure (the name of each of its columns, their datatype,
whether this column can have null values, whether it’s part of the Primary Key, its default
value...)?
B(11) Suppose you want to create a table called STUDENTDATA. You want it to have
columns called SNum, FName, LName, BirthDate, and Sex. You want SNum to hold
integers, FName and Lname to hold strings of characters, each of which can be up to 36
characters long, Birthdate to be a date, and Sex to be a single character, either ‘M’ or ‘F’.
What command will let you create such a table?
B(12) Suppose you now wish to put data into the table you have created, and you already
have the data in a .txt file called Studentdata.txt – one row of data per line.
(a) What command can copy the data from the .txt file into your table?
(b) How should each data item on a line in the .txt file be separated from the next one?
(c) If you do not know a certain value, and want to insert the value NULL in its place, how
should this be represented in the .txt file?
B(13) Suppose you want to add a row of data directly to Studentdata… how would you
add: 314159 Kapoor Sahib 2002-02-14 M?
B(14) Suppose, after creating the relation Studentdata, you decided to add another
column (attribute), called ‘MI’ of type Char[1] between the columns ‘FName’ and ‘LName’.
What command would let you do this?
(See: https://dev.mysql.com/doc/refman/5.7/en/alter-table.html).
B(15) Suppose you wanted to change Sahib’s BirthDate to ‘2002-02-15’. What statement
would allow you to do this?
B(16) Suppose you wanted to see all of the columns of all of the rows of STUDENTDATA.
What command would allow you to do this?
B(17) Suppose you wanted to see only the first names of all the students. What command
would allow you to do this?
B(18) Suppose you wanted to see all the first names of all the female students only. What
command would allow you to do this?
B(19) Suppose you wanted to see all the Firstnames of all the female students who were
born before January 1, 1999. What command would allow you to do this?
B(20) Suppose you wanted to see the first names of all the female students who were born
before January 1, 1999, sorted on their birthdates with the youngest first. What command
would allow you to do this?
[4 hours. 10 marks]
B. What to submit: Answers to questions B(1)–B(20). Be sure to number your
answers.
7
C. Creating a visual model of related data
In this section of the coursework assignment we will take an imaginary business which wants
to create a database, and draw up a visual model of how its data is related, called an ‘Entity-
Relationship Diagram’.
The background
A manufacturer of SatNav devices gets its electronic parts from a number of different
suppliers, and assembles various models of SatNav device from them. These SatNav devices
are then sold to high street distributors. The manufacturer will hold information on suppliers
even if they are not currently supplying any electronic parts.
In order not to become dependent on any one supplier, the manufacturer has arranged it so
that any given electronic part-type is supplied by at least two separate suppliers.
A SatNav model-type, is identified by a (unique) ‘Model Number’ (MNUM). Each model is
made up of several different types of electronic part-types, each of which is identified by a
‘Part Code’ (PCODE). Each electronic part-type can be used in the assembly of several
different models of SatNav models. A SatNav model can exist which is not being distributed by
anyone at the moment. A SatNav model must be made of one or more electronic parts, but
an electronic part-type could exist which is not currently being used in making any of our
SatNav models.
A given SatNav device distributor, identified by a (unique) ‘Distributor ID’ (D-ID) can receive
and re-sell many different models of SatNav device. No SatNav device distributor has a
monopoly on re-selling any given SatNav device type. A distributor must be distributing at least
one model of SatNav device in order to be considered a distributor. We do not allow a
distributor to distribute a particular model until we are sure that they can install and service it.
Each electronic part-type has a name, not necessary unique, and a weight. Each supplier is
identified by a (unique) Supplier Number (SNO), and has a name and country. Each model of
a SatNav device has a name, and a wholesale base price. Each distributor of the devices has
a name.
A supplier supplies us with a fixed number of electronic part types per month – which we will
identify as a QTY (for ‘quantity’). A distributor of the manufacturer’s SatNav devices places
orders for them: an order is identified by a unique Order Number (ORDNO), which has a date,
and which consists of one or more Order Lines. An Order Line consists of a Model Number and
a quantity.
Subject guide reference: pages 103-123 of the subject guide, Database
Systems, Volume 1, and the section on ‘Types and Instances’ in Appendix II of
this coursework assignment. Note that in ordinary language, as used in this
coursework assignment, the distinction between ‘type’ and ‘instance of a type’ is
not always reflected in language used. You must understand the distinction,
where it is important, from context.