ISYS1055/1057 Database Concepts S2/2017

ISYS1055/1057 Database Concepts S2/2017
Assignment 2
This is an individual assignment. Plagiarism in oral, written or visual presentations is the
presentation of the work, idea or creation of another person, without appropriate
referencing, as though it’s one’s own. Plagiarism is not acceptable and may result in
charges of academic misconduct which carries a range of penalties. It is also a
disciplinary offence for students to allow their work to be plagiarised by another student.
For details, please check the course guide.
Due time
Final submission is due at 10:00pm 11 October 2017 Wednesday in Week 12. Submit one
PDF file to the “on-time submission” folder in the Blackboard submission system.
● Make sure you take a screenshot after submission and keep that screenshot as
proof of your submission receipt.
● Do not leave your submission to the last minute as you may experience difficulty
uploading your file.
● You can re-submit multiple times before the due time – any re-submission will
override previous submissions. Do not worry about any system warning message
like “ Originality reports for resubmissions can take up to 24 hours to generate”.
Late submissions of assignments after the due time (even if with approved extensions)
need to be submitted to the “late submission” folder and will be penalised as follows. For
every (up to) 24 hours late, a penalty of 10% (i.e. 10% out of total marks, not 10% out of
your marks) applies. For assignments more than 5*24 hours late, 100% penalty applies.
Assessment
100 marks in total, which is 30% of the overall assessment for ISYS1057 and ISYS1055.
The assessment components and weights are:
Assignment 1 Assignment 2 Exam
20% 30% 50%
There is not a hurdle for any assessment component.

Question 1. The Relational model (30 marks).
Consider the below relational database schema for the MX department store:
Shop(shopNo, street_addr, suburb, postcode, phoneNo)
Item(itemNo, description, price, shopNo, stock)
Order(orderNo, itemNo, quantity, supplier, supplier_contactNo)
MX has many shops throughout Australia. The following Functional Dependencies (FDs)
are collected at the database design stage:
shopNo → street_addr, suburb, postcode, phoneNo
suburb → postcode
itemNo → description, price
shopNo, itemNo → stock, price
orderNo, itemNo → quantity
itemNo → supplier, supplier-contactNo
supplier → supplier_contactNo
Answer questions:
1.1) ( 3 marks) Give {itemNo, shopNo} + .
1.2) (3 * 3 marks = 9 marks) Give the candidate keys for each of the given
relations Shop, Item and Order. Show your workings of using FDs.
1.3) (6 marks) Compute the minimal basis for the given FDs.
1.4) (6 marks) Use the minimal basis for FDs from question 1.3) to explain if
Shop, Item and Order are in BCNF.
1.5) (6 marks) If any of the relations Shop, Item or Order is not in BCNF,
decompose it into BCNF/3NF. Give the relations after decomposition and specify
the primary key and any foreign key for each relation.
Question 2. SQL (30 marks).
In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial
on Blackboard (“Using Oracle → SQL*Plus tutorial”) and other resources for Oracle
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.
● 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 Blackboard
(“Using Oracle”) and run academics.sql in your Oracle account to build the database.
Each question is worth 3 marks. Write ONE SQL query for each of questions 2.1)–2.7).
Each component of an SQL statement must be on a separate line. For example,
SELECT *
FROM Department
WHERE State=’VIC’;
Do not include the result of the query or the script used to create the tables. Your query
should not produce duplicates in output but use DISTINCT only if necessary.
3
2.1) List the deptnum and total number of academics for departments with
postcode in the range 3000..3999, in descending order of total number of
academics for each department.
2.2) Are there any academics who have not written any papers? List the acnum,
givename and famname of these academics. Your query must contain a subquery.
2.3) Find the departments that have more than 10 academics. List the deptnum,
instname and deptname of these departments.
2.4) Find the department in Victoria that is the strongest in “Software Engineering”
(field.title) research, that is the department has the largest of academics interested
in “Software Engineering” research. Output the deptnum, deptname and instname
of the department. You must NOT use the MAX aggregate function.
2.5) Give the total number of academics that do not have any research interests.
2.6) Are there any research fields where less than 10, including zero, academics
are interested in. List the fieldnum, id and number of interested academics for
these research fields.
2.7) Find the papers authored by academics from departments located in Victoria.
List the panum and title of these papers. You must use the NATURAL JOIN and
EXISTS operators.
2.8) Write a CREATE VIEW statement to define a view PAPER_VIEW that has
three columns to keep data for each paper: panum (the paper number), n_author (
the total number of authors) and title (the paper title).
2.9) The ID of research fields has three parts separated by two periods. Consider a
query to find details of research fields where the first two parts of the ID are D
and 2 and the last part is one character (digit). IDs like D.2.1 and D.2.3 are in the
query result whereas IDs like D.2.12 or D.2.15 are not. The SQL query given
below does not return the correct result. Explain the reason why it does not return
the correct result and give the correct SQL query.
select *
from field
where ID like ‘B.1._’;
2.10) The following SQL query is intended to find the departments with the largest
total number of academics and print their department num, deptname and
instname, but it has syntax errors. Identify the syntax errors in the query and give
the correct SQL query.
select deptnum, deptname, instname, count(acnum)
from department, academic
where academic.deptnum =department.deptnum
group by deptnum
having max(count(acnum));
Question 3. ER model (20 marks).
An ER diagram for the Beta Health Physiotherapy Centre is given below. The names of
entities, relationships and attributes express the meanings clearly.
Some limitations of the given ER model are identified and listed below, together with
additional information for refining and extending the given ER diagram.
● In the given ER model, the “See” relationship only contains simple information
on patients seeing doctors. Extend this part to include details on therapist
consultation sessions: A therapist has consultation sessions at specific date and
time and in a room; therapists can have consultation sessions at the same time but
in different rooms. Consultation sessions can be short (15 minutes) or long (30
minutes). A patient can book several consultation sessions with different
therapists but each consultation session is for one patient.
● Treatments like “back massage” are often suggested by therapists after
consultation sessions. Treatments have a unique treatment code and a name, and
comprises multiple treatment sessions (details given next). A therapist
consultation session results in only one treatment and a treatment may be used for
several consultation sessions.
● A treatment comprises multiple treatment sessions numbered sequentially.
Equipments need to be booked for treatment sessions. A treatment session uses
one equipment, and the date, time and duration for using the equipment should be
recorded. An equipment can be used for several treatment sessions.
Give the complete Entity Relationship (ER) diagram for the database of the Beta Health
Physiotherapy Centre, including the original and additional information given above .
● Your ER diagram must use notations from the lecture notes and must not be hand
drawn. ER diagrams using other notations will receive zero mark for this question.
● You can use the ER diagramming tool Dia, which can be downloaded from the course
Blackboard (“Tools → ER Diagram Tools”) and is also available as an App on
mydesktop.rmit.edu.au. 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.
5
Question 4. ER to relational schema mapping. (20 marks).
Consider the Omeria Hotel database ER diagram as shown in Figure 1.
4.1) (10 marks) Give the FDs for the constraints in the ER diagram. You should
not include trivial or redundant FDs.
4.2) (10 marks) Map the ER diagram to a relational database schema following the
ER to a relational database schema mapping rules and indicate the primary key
(underline) and any foreign keys (asterisk) in each relation.
Fig. 1 The Oreria Hotel database ER diagram
6