首页 > > 详细

讲解FIT9132 调试Java编程

Monash University 
 
FIT9132 
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) 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] 
 
 
Simple - an attribute which cannot be subdivided eg. employeeid, department 
number 
 
 
Composite - an attribute which can be subdivided into additional attributes eg. 
employee name, home address 
 
 
Multivalued - an attribute which has many potential values eg. qualification, skill 
Page ​2​ of ​15 
 
(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​, ​paper_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] 
π ​journal_title​ (JOURNAL) 
 
(ii) Show the paper title, journal title and month and year of publication for all papers 
published before 2012 
​[3 marks] 
π ​paper_title, journal_title, month, year 
(π ​journal_id, journal_title, month, year​ (σ ​year < 2012 ​(JOURNAL)) 
⨝ 
(π ​journal_id, paper_title​(PAPER)) 
OR 
ANSWER1 = π ​journal_id, journal_title, month, year​ (σ ​year < 2012 ​(JOURNAL)) 
ANSWER2 = π ​journal_id, paper_title​(PAPER) 
ANSWER3 = ANSWER1 ​⨝ ANSWER2 
ANSWER4 = π ​paper_title, journal_title, month, year​ (ANSWER3) 
Here ANSWER1 could be done in two steps, a select and then a project. 
Page ​3​ of ​15 
 
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​, ​General 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 
● Surrogate keys must not be added 
● In building your model you must conform to FIT9132 modelling requirements 
● The following are​ NOT required​ on your diagram 
● verbs/names on relationship lines 
● indicators (*) to show if an attribute is required or not 
● data types for the attributes 
Page ​4​ of ​15 
 
 
Monash Computing Students Society (MCSS) Logical Model 
 
 
 
 
Page ​5​ of ​15 
 
Q3. Normalisation (10 marks) 
 
 
The Super Electronics Invoice shown below displays the details of an invoice for the client 
Alice Paul. 
 
Super Electronics 
INVOICE 
 
Client Number: C3178713 ​Invoice No.: ​132 
Client Name: Alice Paul ​Invoice​ ​Date:​ 02/11/2018 
Client Address:​ 43 High Street, 
Caulfield, VIC 3162 
Client Phone:​ 0411 245 718 
 
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 and address 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 ​6​ of ​15 
 
 
UNF 
INVOICE (invoice_nbr, inv_date, client_number, client_name, client_address, client_phone, 
(item_id, item_name, item_purchase_price, item_delivery_time, qty_ordered, line_cost) 
sub_total, delivery_fee, order_total) 
 
ii) Remove repeating groups and identify the primary key for each relation 
1NF 
INVOICE (​invoice_nbr​, inv_date, client_number, client_name, client_address, client_phone, 
sub_total, delivery_fee, order_total) 
INVOICE_LINE (​invoice_nbr​, ​item_id​, item_name, item_purchase_price, item_delivery_time, 
qty_ordered, line_cost) 
 
Partial Dependencies: 
item_id -> item_name 
 
iii) Remove partial dependency and identify the primary key for each relation 
2NF 
INVOICE (​invoice_nbr​, inv_date, client_number, client_name, client_address, client_phone, 
sub_total, delivery_fee, order_total) 
INVOICE_LINE (​invoice_nbr​, ​item_id​, item_purchase_price, item_delivery_time, qty_ordered, 
line_cost) 
ITEM (​item_id​, item_name) 
 
Transitive Dependencies: 
client_number -> client_name, client_address, client_phone 
 
iv) Remove transitive dependency and identify the primary key for each relation 
3NF 
INVOICE (​invoice_nbr​, inv_date, client_number, sub_total, delivery_fee, order_total) 
CLIENT (​client_number​, client_name, client_address, client_phone) 
INVOICE_LINE (​invoice_nbr​, ​item_id​, item_purchase_price, item_delivery_time, qty_ordered, 
line_cost) 
ITEM (​item_id​, item_name) 
Page ​7​ of ​15 
 
 
Full Dependencies: 
invoice_nbr -> inv_date, client_number, sub_total, delivery_fee, total_cost 
client_number -> client_name, client_address, client_phone 
invoice_nbr, item_id -> item_purchase_price, item_delivery_time, qty_ordered, line_cost 
item_id -> item_name 
Page ​8​ of ​15 
 
 
Q4. SQL ( 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​. 
 
 
 
Page ​9​ of ​15 
 
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​] 
SELECT 
crscode, 
crsdesc, 
crsduration 
FROM 
course 
WHERE 
crscategory = 'GEN' 
OR crscategory = 'BLD' 
ORDER BY 
crsduration DESC, 
crscode; 
 
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​] 
SELECT 
deptname, 
deptlocation, 
e1.empname AS "MANAGERS NAME", 
COUNT(*) AS "TOTAL EMPLOYEES" 
FROM 
( department d 
JOIN employee e1 
ON d.empno = e1.empno ) 
JOIN employee e2 
ON d.deptno = e2.deptno 
GROUP BY 
deptname, 
deptlocation, 
e1.empname 
ORDER BY 
COUNT(*); 
Page ​10​ of ​15 
 
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​] 
 
SELECT 
e.empno, 
empname, 
TO_CHAR(empbdate, 'dd-Mon-yyyy') AS dob, 
COUNT(DISTINCT r.crscode) AS crscount 
FROM 
employee e 
LEFT JOIN registration r 
ON e.empno = r.empno 
GROUP BY 
e.empno, 
empname, 
empbdate 
ORDER BY 
e.empno; 
 
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. 
[​4marks​] 
INSERT INTO department VALUES ( 
SELECT 
MAX(deptno) 
FROM 
department 
) + 10, 
'EXAM', 
'BOSTON', 
NULL 
); 
 
COMMIT; 
 
 
Page ​11​ of ​15 
 
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​] 
 
UPDATE department 
SET 
empno = ( 
SELECT 
empno 
FROM 
employee 
WHERE 
empname = 'KING' 
AND empjob = 'DIRECTOR' 
WHERE 
deptname = 'EXAM'; 
 
COMMIT; 
 
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​] 
 
ALTER TABLE department ADD deptcount NUMBER(3, 0) DEFAULT 0 NOT NULL; 
 
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 
Page ​12​ of ​15 
 
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. 
 
"_id": 7876, 
"name": { 
"initial": "AA", 
"familyName": "ADAMS" 
}, 
"position": "TRAINER", 
"birthDate": "30-12-1983", 
"courseInfo": [ 
"code": "SQL", 
"date": "12-04-2016", 
"evaluation": 2 
}, 
"code": "PLS", 
"date": "11-09-2017", 
"evaluation": null 
}, 
"code": "JAV", 
"date": "13-12-2016", 
"evaluation": 5 
[​4 marks​] 
 
(ii) Assume that the collection name is employees, write the MongoDB command to show all 
employees who have a job as ‘MANAGER’ 
 
db.collection.find({ 
"position": "MANAGER" 
}) 
[​2 marks​] 
 
(iii) Write the MongoDB command to show all employees who have a surname of ‘JONES’ or 
‘SCOTT’ 
 
db.employees.find({ 
$or:[{"name.familyName": "JONES"},{"name.familyName":"SCOTT"}] 
}) 
[​4 marks​] 
Page ​13​ of ​15 
 
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. 
[ ​4+ 1​ = ​5 marks​] 
 
 
i. 
 
Serial – all of one transaction followed by all of the other 
 
T1 R(X), T1 W(X), T2 R(Y), T2 W(Y), T2 R(X), T2 W(X) 
 
Non-Serial – interleaving of the transactions 
 
T1 R(X), T2 R(Y), T2 W(Y), T1 W(X), T2 R(X), T2 W(X) 
 
 
ii. 
 
Isolation or Consistency 
 
 
 
Page ​14​ of ​15 
 
b. 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. 
[​5 marks​] 
 
T1 – nothing required, committed before checkpoint 
 
T2 – ROLL FORWARD, committed after checkpoint and before fail 
 
T3 – ROLL BACK, never reached commit 
 
T4 – ROLL FORWARD, started after checkpoint committed before fail 
 
T5 - ROLL BACK, never reached commit 
Page ​15​ of ​15 
 
联系我们
  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-21:00
  • 微信:codinghelp
热点标签

联系我们 - QQ: 99515681 微信:codinghelp
程序辅导网!