Learning Objectives
CLO 1: Describe various data modelling and database system technologies.
CLO 2: Explain the main concepts for data modelling and characteristics of database systems.
CLO 3: Identify issues with and compare, justify relational database design using the functional dependency concepts.
CLO 4: Apply SQL as a programming language to define database schemas and update database contents.
CLO 5: Apply SQL as programming language to extract data from databases for specific users' information needs
CLO 6: Design a database schema using conceptual modeling mechanisms such as entity-relationship diagrams.
Graduate Outcomes:
Enabling Knowledge: You will gain skills as you apply data modelling knowledge effectively in diverse contexts.
Critical Analysis: Analyse and model requirements and constraints for the purpose of designing and implementing software artefacts and IT systems.
Problem solving: Design and implement database solutions that accommodate specified requirements and constraints, based on analysis or modelling or requirements specification.
Question 1. The Relational model
A database is needed to keep data for the booking systems of the ABC Clinic. Consider the below database schema of one relation including
attributes for doctors (doc-), patients (pat-) and appointments (app-).
ABC(doc-firstname, doc-surname, doc-gender, doc-rego, doc-qualification, pat-ID, pat-givename, pat-surname, pat-gender, pat-DOB, pat-addr, pat-phone, app-ID, app-datetime, app-type)
• A doctor has a unique registration number (doc-rego) and is also described by name, gender and qualification.
• A patient is identified by a unique patient ID (pat-ID) and has other information.
• Each appointment by a patient with a doctor is assigned a unique appointment ID (app-ID). An appointment can be of the long or short type.
Answer questions:
1.1) Give likely FDs.
1.2) Give the candidate keys for the ABC relation. In your working, show how you develop the closure for each
1.3) Is the relation ABC in BCNF or 3NF? Explain your answer.
Question 2. Normalisation
Consider the two relations below. They are in BCNF with primary key attributes underlined:
Customer(custlD, firstname, lastname)
Item(itemNo, desc, price)
A Transaction relation as below is proposed to keep data for orders. Each order is by one customer and it can contain multiple items with their quantities.
Transaction(custlD, itemNo, orderlD, quantity, discount, amount_due)
Given the FDs below:
orderlD custlD, amount_due, discount orderlD, itemNo . quantity
custlD, orderlD amount_due, discount
Answer questions.
2.1) Give the minimal basis for the given FDs.
2.2) The Transaction relation is not in BCNF or 3NF. Give the reason.
2.3) Follow the BCNF/3NF
2.3) Follow the BCNF/3NF decomposition algorithm to decompose
Transaction into relations in BCNF or 3NF.
Give the relations after decomposition and specify the primary key and any foreign keys for each relation.
Question 3. SQL
In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial on Canvas (the Oracle section) and other resources for syntax and useful functions.
The relational schema for the Academics database is as follows:
DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)
ACADEMIC(acnum, deptnum*, famname, givename, initials, title)
PAPER(panum, title)
AUTHOR(panum*, acnum*)
FIELD(fieldnum, id, title)
INTEREST(fieldnum*, acnum*, descrip)
Some notes on the Academics database:
• An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department institution (instname) and often has many academics. An academic only works for one department.
• Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR).
• A research field (FIELD) often attracts many academics and an academic can have interest in several research fields (INTEREST).
Primary keys are underlined and foreign keys are marked with *. You should download the SQL script for defining and populating the database academics.sql from Canvas (the Oracle section) and run academics.sql in your Oracle account to build the database.
Write ONE SQL query for each of questions 3.1)- -3.9). Put your answer for Question 3.10) in comments (starting each line with "--").
• Do not include the result of the query or the script used to create the tables.
• Your query should not output duplicates but use DISTINCT only if necessary.
• Queries are marked in terms of both correctness and efficiency. Unnecessary joins will incur deduction.
3.1) List the deptnum and total number of academics for CS departments, in alphabetical order of deptname. CS departments are departments whose deptname contains the phrase "Computer ... Science" or "Computing ... Science" in upper case or lower case letters. You must use the NATURAL JOIN operator.
3.2) List research fields where at least one academic is interested in. List the fieldnum, ID and title of these research fields. You must use a
subquery.
3.3) Find papers that have three or more authors. Give the panum, title and number of authors for these papers.
3.4) For EACH academic, compute the total number of papers s/he has written. Output should include the acnum and total number of papers for each academic. In particular, an academic without any papers should have zero(0) as number of papers in the output. You academic without any papers should nave zero(0) as number of papers in the output. You must use a JOIN operator.
3.5) Give the total number of academics that do not have research interests. You must use the NOT IN operator.
3.6) Are there any research fields where less than 20, including zero, academics are interested in. List the fieldnum, ID, title and number of
interested academics for these research fields.
3.7) Find the papers whose title contain the string 'data' and where at least one author is from the department with deptnum 100. List the
panum and title of these papers. You must use the EXISTS operator. Ensure your query is case-insensitive.
3.8) Return the research interest that has the largest number of interested academics. You must not use MAX. Note: An SQL query that lists all research interests in decreasing order of their total number of interested academics is incorrect.
3.9) The following SQL query is intended to find academics (acnum) who are ONLY interested in "Data" (descrip) fields. But it is incorrect. Give the correct SQL query.
select acnum from interest where upper(descrip) like '%DATA%';
3.10) Consider the SQL query given below, give the English explanation for the output of a) the subquery, and b) the whole SQL query. Literal explanation will receive zero mark.
select distinct AC1.givename, AC1.famname, AC2.givename, AC2.famname
from academic AC1, author AU1, academic
AC2, author AU2
where AC1.acnum=AU1.acnum
and AC2.acnum=AU2.acnum
and AU1.panum=AU2.panum
and AU2.acnum>AU1.acnum
and not exists
(select *
from Interest 11, Interest 12
where 11.acnum =AC1.acnum
and 12.acnum=AC2.acnum
and 11.fieldnum=12.fieldnum);
Question 4. ER model
The A-Star consulting firm has decided to build a database. The company has hired you to design the database. Requirements are as follows.
• Clients are given a unique client-ID. Other client details including name, address and telephone number are also recorded.
• A-Star has several departments. Each department is described by a unique name, address and contact phone number. Each department must have a manager and has many employees. A manager can only manage one department.
• Each employee of A-Star has a unique employee ID, name, and birth date. An employee must belong to a department.
• An employee is assigned as the consultant for a group of clients. But a client has only one consultant.
• Once signed up, clients book consultation sessions with their consultant. All consultation sessions for a client are numbered sequentially as 1, 2, 3, etc. and the details for consultation sessions are also recorded, including date, time and topic.
• A-Star has vehicles for visiting clients. Each vehicle has a registration number, model, and year the vehicle was made. Consultants book
vehicles for consultation sessions with clients.
The database is aimed to:
• Keep track of the consultation sessions for clients and vehicle usage for booking vehicles for consultation.
• Keep track of scheduled consultation sessions for consultants.
According to the requirements and design aim, give an Entity Relationship (ER) diagram for the database, making assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any constraints that can not be expressed in the ER diagram.
• Your ER diagram must only use notations from the lecture notes and must not be hand drawn. ER diagrams using other notations will
receive zero mark.
• You can use the ER diagramming tool Dia, which can be downloaded from Canvas (The • You can use the ER diagramming tool Dia,
which can be downloaded from Canvas (The ER section) and is also available as an App on
When exporting your
ER diagram in Dia to a pdf file, you need to first set "page setup" to "Fit to 1 by 1" so that your pdf diagram scales properly. You can also use any other diagramming tool.
Question 5. ER to relational schema mapping.
Consider the Musician database ER diagram as shown in Figure 1.
5.1) Give the FDs for the constraints in the ER diagram. You should not include trivial or redundant FDs.
5.2) Map the ER diagram to a relational database schema following the ER- to-relational-database-schema mapping rules. Indicate the primary key (underline) and any foreign keys (asterisk) in each relation.
Fig. 1 The Musician database ER diagram