CO526 Databases Course Work 1: SQL
Due in noon Friday 16th February 2018
The tables below gives details of a family history database. In the person table, people are
identified by their name, and always have their gender, date of birth (dob) and place of birth
(born in) recorded. In addition, each person may optionally have recorded the name of their
father, and the name of their mother. If the person has died, then the date of death dod must be
present. Note that only a fragment of the data held in the database is listed below.
person
name gender dob dod? father? mother? born in
Alice F 1885-02-25 1969-12-05 null null Windsor
Andrew M 1960-02-19 null Philip Elizabeth II London
Andrew of Greece M 1882-02-02 1944-12-03 George I of Greece null Athens
Anne (Princess) F 1950-08-15 null Philip Elizabeth II London
Charles M 1948-11-14 null Philip Elizabeth II London
.
..
person(father)
fk
⇒ person(name) person(mother)
fk
⇒ person(name)
In addition, there is a table monarch which contains the English head of state — normally a
monarch (i.e. a King or Queen) — where the house of each monarch indicates which royal house
the monarch belongs to, accession indicates the date the person came to the throne, and coronation
the date any coronation of the monarch. If null appears in coronation then the person had no
coronation. Each monarch remains head of state until the succession of the next monarch. Note
that the value of null appearing in house indicates not a King or Queen, but a head of state (for
example Oliver Cromwell) who filled the role of Protector during the Commonwealth period in
the 17th Centuary.
monarch
name house? accession coronation?
James I Stuart 1603-03-24 1603-07-25
Charles I Stuart 1625-03-27 1626-02-02
Oliver Cromwell null 1649-01-30 null
Richard Cromwell null 1658-09-03 null
Charles II Stuart 1659-05-25 1626-02-02
James II Stuart 1685-02-06 1685-04-23
…
monarch(name)
fk
⇒ person(name)
Finally, there is a table prime minister, recording the party the person led whilst Prime Minister,
and the date of entry into office. A person remains Prime Minister until the date of entry to office
of the next Prime Minister. Note a person may have more than one period in office.
prime minister
name party entry
David Cameron Conservative 2010-05-11
Gordon Brown Labour 2007-06-27
Tony Blair Labour 1997-05-02
John Major Conservative 1990-11-28
Margaret Thatcher Conservative 1979-05-04
James Callaghan Labour 1976-04-05
Harold Wilson Labour 1974-03-04
Edward Heath Conservative 1970-06-19
…
prime minister(name)
fk
⇒ person(name)
Submission
To gain full marks, answers to the following questions should make full use of ANSI SQL commands
to write compact and efficient queries, and be laid out such that structure of the query is clear.
The queries must also run correctly on the Postgres version of the database, and be submitted
electronically to CATE as single batch file db 2018 cw1.sql by the coursework deadline. A template
version of the file is available on CATE for download. The queries in the file must be given in the
order of the questions below, with the comment present in the template file left unchanged, and
the query terminated by a single semi-colon.
To test your answer against the Postgres version of the database, you should run the command:
p s q l −h db . doc . i c . ac . uk −d f am i l y h i s t o r y −U l ab −W −f db 2018 cw1 . s q l
Note that 60% of the marks will be awarded for correctness, and 40% of the marks for style,
including efficiency, how concise the queries are, appropriate use of indentation, use of Capital
letters for keywords, and expressing join conditions by use of JOIN statements in the FROM
clause as opposed to using equals in the WHERE clause.
Questions
The first four questions test knowledge of SQL as an implementation of the Relational Algebra,
and the last four questions test knowledge of SQL as a Programming Language.
1. Write an SQL query that returns the scheme (name,father,mother) ordered by name contain-
ing the name of all people known to have died before both their father and mother, together
with the name of the mother and the name of the father.
2. Write an SQL query returning the scheme (name) ordered by name that lists all people that
have either been a King, Queen or Prime Minister.
3. A King or Queen is said to abdicate if their reign ceases before their death. Write an an
SQL query returning the scheme (name) ordered by name that lists the name of all Kings or
Queens that have abdicated
4. Write a query that returns the scheme (house,name,accession) ordered by accession that lists
house and name of monarchs who were the first of a house to accede to the throne. Maximum
marks will be given only to answers that use either the ALL or SOME operators.
5. Write an SQL query that returns the scheme (first name,popularity) ordered in descending
order of popularity, and then alphabetical order of first name. Your answer should also
exclude first names that only occur once in the database. A first name is taken to mean the
first word appearing the name column of person.
6. Write an SQL query that returns the scheme (house,seventeenth,eighteenth,nineteenth,twentieth)
ordered by house listing the number of monarchs of each royal house that acceded to the
throne in the 17th, 18th, 19th and 20th centuries.
7. Write an SQL query returning the scheme (father,child,born) ordered by father,born that lists
as father the name of all men in the database, together with the name of each child, with
born being the number of the child of the father (i.e. returning 1 for the first born, 2 for the
second born, etc). For men with no children, the man should be listed with null for both
child and born.
8. Write an SQL query that returns the scheme (monarch,prime minister), ordered by monarch
and prime minister, that lists prime ministers that held office during the reign of the monarch.