Monash University
FIT9132 SAMPLE EXAM PAPER
● IMPORTANT NOTE: This Sample Exam serves to provide a general overview of the general structure of the exam paper only.
● To protect the integrity of the exam: NO ACTUAL EXAM QUESTIONS are included; and the COMPOSITION OF THE SUB QUESTIONS are SUBJECT TO CHANGE.
● Students are reminded that all content specified by the Unit Guide is examinable, including but not limited to Pre-reading (weekly Coronel & Morris chapters) + Workshop Slides and Videos + Tutorial Notes + all other Moodle Materials (except where explicitly stated).
ALL ANSWERS MUST BE ENTERED INTO THE EXAM ANSWER PAPER using Microsoft Word (install available here – DO NOT use MoVE) https://www.monash.edu/esolutions/software/microsoft-office-365
NOTE
● forthissampleexam,youmustnotusescannedhandwrittenmaterials ● fortheactualexam,noscannedhandwrittenmaterialwillbe
accepted/marked
Page 1 of 13
Q1. Relational Model (6 + 1 + 3 = 10 marks)
A company wishes to record the following attributes about their employees: employee ID, department number, name, home address, education qualifications and skills which the employee has.
A small sample of data is show below:
Employee ID
Department Number
Employee Name
Home Address
Qualification
Skill
101
21
Given name: Joe
Family name: Bloggs
Street: 12 Wide Rd Town: Mytown Postcode: 1234
Bachelor of Commerce MBA
Project Management Hadoop
R
102
13
Given name: Wendy Family name: Xiu
Street: 55 Narrow St Town: Mytown Postcode: 1234
Bachelor of Computer Science Master of IT Doctor of Philosophy
SQL PL/SQL
103
13
Given name: Sarah
Family name: Green
Street: 25 High St Rd Town: Mytown Postcode: 1234
Certificate IV in Business Administration
SQL Java Phyton
(a) Use this data to explain the difference between a simple attribute, a composite attribute and a multivalued attribute. Your answer must include examples drawn from this data.
[6 marks]
Page 2 of 13
(b) The following relations represent a publications database.
Authors write papers which are published in an edition of a journal.
Each edition of a journal is assigned a journal id and appoints an editor.
A given paper may be authored by several authors, in such cases each author is assigned a position representing their contribution to the paper:
author (author_id, first_name, last_name) author_paper (author_id, p aper_id, author_position) paper (paper_id, paper_title, journal_id)
journal (journal_id, journal_title, month, year, editor)
● Primary keys are underlined
● editor in journal references author(author_id) – this is an author acting as the
journal editor
Write the relational algebra for the following queries (your answer must show an
understanding of query efficiency):
(i) Show all the journal titles.
[1 mark]
(ii) Show the paper title, journal title and month and year of publication for all papers published before 2012
[3 marks]
Page 3 of 13
Q2 Database Design (20 marks)
Monash Computing Students Society (MCSS) is one of the student clubs at Monash University.
Students are welcome to join as a member. When a student joins MCSS, a member id is assigned, and the students first name, last name, date of birth, email and phone number will be recorded. This club has an annual membership fee. When a member has paid the membership fee for the current year, the current year is recorded against the year of membership as part of their membership details.
MCSS hosts several events throughout the year. The events are currently categorised into Professional Events, G eneral Events, and Social Events. MCSS would like to be able to add further categories as they develop new events, When an event is scheduled, MCSS assigns an event id to the event. The event date and time, description, location, allocated budget, the ticket price and the discount rate (eg 5%) for members. Some events are organised as free events for members. In this situation, the discount rate is recorded as 100% for members. For all events, only members can purchase the tickets. However, members can buy additional tickets for their friends or family at full price. For each of the sales, the receipt number, number of tickets sold, total amount paid and the member id are recorded.
Some events attract some sponsorships. The sponsor may be an organisation or an individual. The sponsors provide financial support to the event. Some events may have several sponsors. The amount of financial support provided by each sponsor is recorded for the event. Each sponsor is identified by a sponsor id. The name, contact email and sponsor type are also recorded. A sponsor may support several events throughout the year.
For some events such as career night, MCSS may also invite some guest speakers to share their experience. The database records all guests’ information, the guests full name, email and phone number are recorded. If a guest comes from an organisation or an individual that provides a sponsorship to any of the MCSS events (does not have to be at the event where the guest speaks), this fact will also be recorded. A guest may be invited to several events.
Create a logical level diagram using Crow’s foot notations to represent the “Monash Computing Students Society” data requirements described above. Clearly state any assumptions you make when creating the model.
This model must be created using LucidChart (you MUST NOT use SQL Developer Data Modeller). After you have built your model in LucidChart, export it as a png image and add it to the answer paper via the MS Word Insert – Picture menu.
Please note the following points:
● Be sure to include all relations, attributes and relationships (unnecessary relationships must not be included)
● Identify clearly the Primary Keys (P) and Foreign Keys (F), as part of your design
● Surrogatekeysmustnotbeadded
● In building your model you must conform to FIT9132 modelling requirements
● The following are NOT required on your diagram
● verbs/namesonrelationshiplines
● indicators(*)toshowifanattributeisrequiredornot ● datatypesfortheattributes
Page 4 of 13
Q3. Normalisation (10 marks)
The Super Electronics Invoice shown below displays the details of an invoice for the client Alice Paul.
Client Number: Client Name: Client Address:
Client Phone:
C3178713
Alice Paul
43 High Street, Caulfield, VIC 3162
0411 245 718
Invoice No.: 132
Super Electronics INVOICE
Invoice Date:
02/11/2018
ItemID
Item Name
Purchase Price
Expected Delivery Date
Quantity
Cost
316772
Soniq S55UV16B 55″
499.00
2 weeks
1
499.00
452550
Microsoft Surface Pro
1198.00
1-3 weeks
1
1198.00
483041
Delonghi Digital Coffee
299.00
Same Day
2
598.00
SUB TOTAL: $ 2295.00
DELIVERY: $145.00
ORDER TOTAL: $2440.00
Represent this form in UNF. In creating your representation you should note that Super Electronics wish to treat the client name, address and phone as simple attributes.
Convert your UNF to first normal form (1NF) and then continue the normalisation to third normal form (3NF). At each normal form show the appropriate dependencies for that normal form, if there are none write “No Dependencies”
Do not add new attributes during the normalisation. Clearly write the relations in each step from the unormalised form (UNF) to the third normal form (3NF). Clearly, indicate primary keys on all relations from 1NF onwards.
[10 marks]
Page 5 of 13
Q4. SQL/NOSQL ( 6 + 10 + 10 + 4 + 4 + 6 + 10 = 50 marks) A. SQL: 40 marks
The following relational model depicts an Employee System:
The schema file to create these tables is listed in Appendix A.
Given this model and assuming the tables have been created and populated in an Oracle database, provide the SQL statements for the following requirements.
When coding SQL you must ensure each clause you use, such as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, CREATE, ALTER etc starts on a new line.
i. Display the course code, course name and duration for all those courses which are from the course category “GEN” or “BLD”, order the output with the course with the longest duration first. Where two courses have the same duration, order their output by the course code.
[6 marks] Page 6 of 13
ii. For each department list the department name, the department location, the name of the manager and the number of employees in that department. The name of the manager must be output in a column called “MANAGERS NAME” and the number of employees must be output in a column called “TOTAL EMPLOYEES”. Order the output by the number of employees in the department.
[10 marks]
iii. List for all employees, the employee number, name, birthdate and the number of different courses they have registered for. Note that some employees may repeat a course, this repeat does not count as a different course. Order the output by employee number. Sample output will have the form (only partial shown):
[10 marks]
iv. Add a new department to the DEPARTMENT table, this department’s number will be 10 higher than the highest current department number and will be called EXAM and is located in BOSTON, the department does not currently have a manager assigned. No sequences are available or may be created.
[4 marks] v. The employee named KING who has a job as the only company DIRECTOR has been assigned
to manage the new EXAM department. Record this in the database.
[4 marks]
vi. The company has decided that they wish to record, for each department, the number of employees currently working in the department. Modify the database structure to allow this data to be recorded. Initially, following your modification, the number of employees in each department should be set to 0 – this will be updated at a later stage, you do not need to code this later update.
[6 marks]
Page 7 of 13
B. NOSQL: 10 marks
(i) Given this sample data:
and this select statement
SELECT
JSON_OBJECT( ‘_id’ VALUE empno,
‘name’ VALUE JSON_OBJECT(
‘initial’ VALUE empinit,
‘familyName’ VALUE empname
),
‘position’ VALUE empjob,
‘birthDate’ VALUE to_char(empbdate,’dd-mm-yyyy’),
‘courseInfo’ VALUE JSON_ARRAYAGG(
JSON_OBJECT(
‘code’ VALUE crscode,
‘date’ VALUE to_char(offbegindate,’dd-mm-yyyy’),
‘evaluation’ VALUE regevaluation
)
)
FORMAT JSON )
|| ‘,’
FROM PAYROLL.employee NATURAL JOIN payroll.registration
GROUP BY empno, empinit, empname, empjob,empbdate
ORDER BY empname;
Write the JSON formatted text for one of the employees listed in the table.
[4 marks] (ii) Assume that the collection name is employees, write the MongoDB command to show all
employees who have a job as ‘MANAGER’
[2 marks]
(iii) Write the MongoDB command to show all employees who have a surname of ‘JONES’ or ‘SCOTT’
[4 marks]
Page 8 of 13
Q5. Transaction Management (5 + 5 = 10 marks)
a.
Given two transactions:
T1 – R(X), W(X)
T2 – R(Y), W(Y), R(X), W (X)
Where R(X) means Read(X) and W(X) means Write(X).
i. If we wish to complete both of these transactions, explain the difference between a serial and non-serial ordering of these two transactions. Provide an example of each as part of your answer.
ii. What transaction ACID property does a non-serial ordering of these two transactions potentially violate.
[5 marks]
A write through database has five transactions running as listed below (the time
is shown horizontally from left to right):
At time tc a checkpoint is taken, at time tf the database fails due to a power outage.
Explain for each transaction what recovery operations will be needed when the database is restarted and why.
b.
END OF EXAMINATION
[5 marks]
Page 9 of 13
—
— Create Table
— salgrade
— sallower
— salupper
— salbonus
—
CREATE TABLE SALGRADE (
salgrade NUMBER(2)
sallower NUMBER(6,2)
salupper NUMBER(6,2)
salbonus NUMBER(6,2)
NOT NULL ,
NOT NULL ,
NOT NULL ,
NOT NULL ,
crscode VARCHAR(6)
crsdesc VARCHAR(30)
crscategory CHAR(3)
crsduration NUMBER(2)
APPENDIX – Schema for Employee System
: ‘SALGRADE’
: Salary Grade
: Salary lower limit
: Salary upper limit
: Salary bonus
CONSTRAINT salgrade_pk PRIMARY KEY (salgrade),
CONSTRAINT salgrade_chk1 CHECK (sallower >= 0),
CONSTRAINT salgrade_chk2 CHECK (sallower <= salupper));
COMMENT ON COLUMN salgrade.salgrade IS 'Salary Grade';
COMMENT ON COLUMN salgrade.sallower IS 'Salary Lower Limit';
COMMENT ON COLUMN salgrade.salupper IS 'Salary Upper Limit';
COMMENT ON COLUMN salgrade.salbonus IS 'Salary Bonus';
--
-- Create Table
-- crscode
-- crsdesc
-- crscategory
-- crsduration
--
CREATE TABLE course (
: 'COURSE'
: Course Code
: Course Description
: Course Category
: Course Duration
NOT NULL ,
CONSTRAINT course_pk PRIMARY KEY (crscode),
--
-- Create Table
-- deptno
-- deptname
-- deptlocation
-- empno
--
--
CREATE TABLE DEPARTMENT (
NOT NULL ,
NOT NULL ,
NOT NULL ,
CONSTRAINT course_chk1 CHECK (crscode = upper(crscode)),
CONSTRAINT course_chk2 CHECK (crscategory in ('GEN','BLD','DSG')));
COMMENT ON COLUMN course.crscode IS 'Course Code';
COMMENT ON COLUMN course.crsdesc IS 'Course Description';
COMMENT ON COLUMN course.crscategory IS 'Course Category';
COMMENT ON COLUMN course.crsduration IS 'Course Duration';
deptno NUMBER(2) NOT NULL ,
deptname VARCHAR(10) NOT NULL ,
deptlocation VARCHAR(8) NOT NULL ,
empno NUMBER(4) ,
CONSTRAINT department_pk PRIMARY KEY (deptno),
CONSTRAINT department_un UNIQUE (deptname),
: 'DEPARTMENT'
: Department Number
: Department Name
: Location of department
: Employee who manages department (references EMPLOYEE.empno)
FK constraint cannot be added until employee table is created
Page 10 of 13
CONSTRAINT department_chk1 CHECK (deptname = upper(deptname)),
CONSTRAINT department_chk2 CHECK (deptlocation = upper(deptlocation)));
COMMENT ON COLUMN department.deptno IS 'Department Number';
COMMENT ON COLUMN department.deptname IS 'Department Name';
COMMENT ON COLUMN department.deptlocation IS 'Location of department';
COMMENT ON COLUMN department.empno
IS 'Employee who manages department';
--
-- Create Table
-- empno
-- empname
-- empinit
-- empjob
-- empbdate
-- empmsal
-- empcomm
-- deptno
-- mgrno
--
CREATE TABLE EMPLOYEE (
empno NUMBER(4) NOT NULL ,
COMMENT ON COLUMN employee.empmsal
COMMENT ON COLUMN employee.empcomm
COMMENT ON COLUMN employee.deptno
COMMENT ON COLUMN employee.mgrno
IS 'Employee monthly salary';
IS 'Employee commission';
IS 'Department Number';
IS 'Employees manager (empno of manager)';
: 'EMPLOYEE'
: Employee number
: Employee name
: Employee initials
: Employee job
: Employee birthdate
: Employee monthly salary
: Employee commission
: Department Number (references DEPARTMENT.deptno)
: Employees manager (empno of manager) (references EMPLOYEE.empno)
empname VARCHAR(8)
empinit VARCHAR(5)
empjob VARCHAR(8)
empbdate DATE NOT NULL ,
empmsal NUMBER(6,2) NOT NULL ,
empcomm NUMBER(6,2) ,
deptno NUMBER(2) ,
mgrno NUMBER(4) ,
CONSTRAINT employee_pk PRIMARY KEY (empno),
CONSTRAINT employee_fk1 FOREIGN KEY (mgrno)
REFERENCES EMPLOYEE (empno),
CONSTRAINT employee_fk2 FOREIGN KEY (deptno)
REFERENCES DEPARTMENT (deptno));
NOT NULL ,
NOT NULL ,
,
COMMENT ON COLUMN employee.empno
COMMENT ON COLUMN employee.empname
COMMENT ON COLUMN employee.empinit
COMMENT ON COLUMN employee.empjob
COMMENT ON COLUMN employee.empbdate IS 'Employee birthdate';
IS 'Employee number';
IS 'Employee name';
IS 'Employee initials';
IS 'Employee job';
-- Alter Table : 'DEPARTMENT'
-- empno : Employee who manages department (references EMPLOYEE.empno)
-- Add constraint for Department table now employee exists:
ALTER TABLE DEPARTMENT
ADD (CONSTRAINT department_fk FOREIGN KEY (empno)
REFERENCES employee (empno));
Page 11 of 13
--
-- Create Table
-- deptno
-- histbegindate
-- histbeginyear
-- histenddate
-- histmsal
-- histcomments
-- empno
--
CREATE TABLE HISTORY (
--
-- Create Table
-- offbegindate
-- crscode
-- offlocation
-- empno
EMPLOYEE.empno)
--
CREATE TABLE OFFERING (
: 'HISTORY'
: Department Number (references DEPARTMENT.deptno)
: Date history record begins
: Year history record begins
: Date history record ends
: Monthly Salary for this history record
: Comments for this history record
: Employee number (references EMPLOYEE.empno)
empno NUMBER(4) NOT NULL ,
histbegindate DATE NOT NULL ,
histbeginyear NUMBER(4) NOT NULL ,
histenddate DATE ,
histmsal NUMBER(6,2) NOT NULL ,
histcomments VARCHAR(60) ,
deptno NUMBER(2) NOT NULL
CONSTRAINT history_pk PRIMARY KEY (empno, histbegindate),
CONSTRAINT history_chk CHECK (histbegindate < histenddate),
CONSTRAINT history_fk1 FOREIGN KEY (empno)
REFERENCES EMPLOYEE (empno)
ON DELETE CASCADE,
CONSTRAINT history_fk2 FOREIGN KEY (deptno)
REFERENCES DEPARTMENT (deptno));
COMMENT ON COLUMN history.deptno IS 'Department Number';
COMMENT ON COLUMN history.histbegindate IS 'Date history record begins';
COMMENT ON COLUMN history.histbeginyear IS 'Year history record begins';
COMMENT ON COLUMN history.histenddate IS 'Date history record ends';
COMMENT ON COLUMN history.histmsal IS 'Monthly Salary for this history record';
COMMENT ON COLUMN history.histcomments IS 'Comments for this history record';
COMMENT ON COLUMN history.empno IS 'Employee number';
,
: 'OFFERING'
: Begin date for offering
: Course Code (references COURSE.crscode)
: Location for offering
: Employee number for employee running offering (references
offbegindate DATE NOT NULL ,
crscode VARCHAR(6) NOT NULL ,
offlocation VARCHAR(8) ,
empno NUMBER(4) ,
CONSTRAINT offering_pk PRIMARY KEY (offbegindate, crscode),
CONSTRAINT offering_fk1 FOREIGN KEY (crscode)
REFERENCES course(crscode),
CONSTRAINT offering_fk2 FOREIGN KEY (empno)
REFERENCES EMPLOYEE (empno));
COMMENT ON COLUMN offering.offbegindate IS 'Begin date for offering';
COMMENT ON COLUMN offering.crscode IS 'Course Code';
COMMENT ON COLUMN offering.offlocation IS 'Location for offering';
COMMENT ON COLUMN offering.empno IS 'Employee number for employee running
offering';
--
Page 12 of 13
-- Create Table
-- offbegindate
-- crscode
-- regevaluation
-- empno
EMPLOYEE.empno)
--
: 'REGISTRATION'
: Begin date for offering (references OFFERING.offbegindate)
: Course Code (references OFFERING.crscode)
: Grade for course completed
: Employee number of employee completing course (references
CREATE TABLE REGISTRATION (
offbegindate DATE NOT NULL ,
crscode VARCHAR(6) NOT NULL ,
empno NUMBER(4) NOT NULL,
regevaluation NUMBER(1) ,
CONSTRAINT registration_pk PRIMARY KEY (offbegindate, crscode, empno),
CONSTRAINT resgitration_chk CHECK (regevaluation in (1,2,3,4,5)),
CONSTRAINT registration_fk1 FOREIGN KEY (empno)
REFERENCES EMPLOYEE (empno),
CONSTRAINT registration_fk2 FOREIGN KEY (offbegindate, crscode)
REFERENCES OFFERING (offbegindate, crscode));
COMMENT ON COLUMN registration.offbegindate IS 'Begin date for offering';
COMMENT ON COLUMN registration.crscode IS 'Course Code';
COMMENT ON COLUMN registration.regevaluation IS 'Grade for course completed';
COMMENT ON COLUMN registration.empno IS 'Employee number of employee completing
course';
Page 13 of 13