Monash University FIT2094 – Databases
MOCK SCHEDULED FINAL ASSESSMENT/EXAM SAMPLE SOLUTIONS
Author: FIT Database Teaching Team
License: Copyright © Monash University, unless otherwise stated. All Rights Reserved.
Copyright By PowCoder代写 加微信 powcoder
COPYRIGHT WARNING
This material is protected by copyright. For use within Monash University only. NOT FOR RESALE. Do not remove this notice.
Page 1 of 19
PART A Relational Model [Total: 10 Marks]
Q1 [3 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
Given name: name: Bloggs
Street: 12 Wide Rd Town: Mytown Postcode: 1234
Bachelor of Commerce MBA
Project Management Hadoop
Given name: name: Xiu
Street: 55 Narrow St Town: Mytown Postcode: 1234
Bachelor of Computer Science Master of IT Doctor of Philosophy
SQL PL/SQL
Given name: name: : 25 High St Rd Town: Mytown Postcode: 1234
Certificate IV in Business Administration
SQL Java Phyton
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.
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 19
Q2 [7 Marks]
The following relations represent a publications database: AUTHOR (author_id, author_firstname, author_lastname) AUTHOR_PAPER (author_id, paper_id, authorpaper_position) PAPER (paper_id, paper_title, journal_id)
JOURNAL (journal_id, journal_title, journal_month, journal_year, journal_editor)
* editor in journal references author(author_id) – this is an author acting as the journal editor
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:
Write the relational algebra for the following queries (your answer must show an understanding of query efficiency):
List of symbols:
project: π, select: σ, join: ⨝, left outer join ⟕, right outer join ⟖, full outer join ⟗, intersect ⋂, union ⋃, minus –
(a) Show the paper title, journal title, and month and year of journal publication for all papers published before 2012. (3 marks)
R1 = π journal_id, journal_title, journal_month, journal_year (σ journal_year < 2012 (JOURNAL)) R2 = π journal_id, paper_title(PAPER)
R3 = R1 ⨝ R2
R = π paper_title, journal_title, journal_month, journal_year (R3)
Here R1 could be done in two steps, a select and then a project.
π paper_title, journal_title, journal_month, journal_ year (
(π journal_id, journal_title, journal_month, journal_year (σ journal_year < 2012 (JOURNAL))
(π journal_id, paper_title(PAPER)) )
Page 3 of 19
(b) Show the names of all authors who have never been listed as first author (authorpaper_position = 1) in any paper. (4 marks)
R1 = π author_id (σ authorpaper_position = 1 (AUTHOR_PAPER)) R2 = AUTHOR ⨝ R1
R3 = π author_firstname, author_lastname(R2)
R4 = π author_firstname, author_lastname(AUTHOR) - R3 OR
π author_firstname, author_lastname(AUTHOR) - ( π author_firstname, author_lastname(
(π author_id (σ authorpaper_position = 1 (AUTHOR_PAPER))) )
Page 4 of 19
PART B Database Design [Total: 20 Marks]
Q3 [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.
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
● In building your model you must conform to FIT2094 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
NOTE: This question has been designed such that the model will fit on a single A4 page. You are allowed to use two blank worksheets to draft your model and then submit your final response on ONE page.
Page 5 of 19
Monash Computing Students Society (MCSS) Logical Model
Page 6 of 19
PART C Normalisation [Total: 10 Marks] Q3 [10 marks]
The Super Electronics Invoice shown below displays the details of an invoice for the client .
Client Number: Client Name: Client Address:
Client Phone:
43 High Street, Caulfield, VIC 3162
0411 245 718
Invoice No.: 132
Super Electronics INVOICE
Invoice Date:
02/11/2018
Purchase Price
Expected Delivery Date
Soniq S55UV16B 55"
Microsoft Surface Pro
Delonghi Digital Coffee
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 unnormalised form (UNF) to the third normal form (3NF). Clearly, indicate primary keys on all relations from 1NF onwards.
[10 marks]
Page 7 of 19
INVOICE (inv_nbr, inv_date, client_number, client_name, client_address, client_phone, (item_id, item_name, invline_purchaseprice, invline_deliverytime, invline_qtyordered, invline_linecost), inv_subtotal, inv_deliveryfee, inv_ordertotal)
INVOICE (inv_nbr, inv_date, client_number, client_name, client_address, client_phone, inv_subtotal, inv_deliveryfee, inv_ordertotal)
INVOICE_LINE (inv_nbr, item_id, item_name, invline_purchaseprice, invline_deliverytime, invline_qtyordered, invline_linecost)
Partial Dependencies: item_id -> item_name
INVOICE (inv_nbr, inv_date, client_number, client_name, client_address, client_phone, inv_subtotal, inv_deliveryfee, inv_ordertotal)
INVOICE_LINE (inv_nbr, item_id, invline_purchaseprice, invline_deliverytime, invline_qtyordered, invline_linecost)
ITEM (item_id, item_name)
Transitive Dependencies:
client_number -> client_name, client_address, client_phone
INVOICE (inv_nbr, inv_date, client_number, inv_subtotal, inv_deliveryfee, inv_ordertotal)
CLIENT (client_number, client_name, client_address, client_phone)
INVOICE_LINE (inv_nbr, item_id, invline_purchaseprice, invline_deliverytime, invline_qtyordered, invline_linecost)
ITEM (item_id, item_name)
Full Dependencies:
inv_nbr -> inv_date, client_number, inv_subtotal, inv_deliveryfee, inv_ordertotal
client_number -> client_name, client_address, client_phone
inv_nbr, item_id -> invline_purchaseprice, invline_deliverytime, invline_qtyordered, invline_linecost
item_id -> item_name
Page 8 of 19
PART D SQL [Total: 50 Marks]
Employee System Model and Schema File for Part D The following relational model depicts an employee system:
The schema file to create these tables is:
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 ,
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';
Page 9 of 19
CREATE TABLE course ( crscode VARCHAR(6) crsdesc VARCHAR(30) crscategory CHAR(3) crsduration NUMBER(2)
NOT NULL , NOT NULL , NOT NULL ,
NOT NULL , CONSTRAINT course_pk PRIMARY KEY (crscode),
CONSTRAINT course_chk1 CHECK (crscode = upper(crscode)),
CONSTRAINT course_chk2 CHECK (crscategory in ('GEN','BLD','DSG')));
COMMENT ON COLUMN course.crscode COMMENT ON COLUMN course.crsdesc COMMENT ON COLUMN course.crscategory COMMENT ON COLUMN course.crsduration
CREATE TABLE DEPARTMENT (
deptno NUMBER(2) NOT NULL , deptname VARCHAR(10) NOT NULL , deptlocation VARCHAR(8) NOT NULL empno NUMBER(4) ,
CONSTRAINT department_pk PRIMARY KEY
CONSTRAINT department_un UNIQUE (deptname),
CONSTRAINT department_chk1 CHECK (deptname = upper(deptname)), CONSTRAINT department_chk2 CHECK (deptlocation = upper(deptlocation)));
COMMENT ON COLUMN department.deptno IS COMMENT ON COLUMN department.deptname IS COMMENT ON COLUMN department.deptlocation IS
'Department Number'; 'Department Name'; 'Location of department'; 'Employee who manages
COMMENT ON COLUMN department.empno department';
CREATE TABLE EMPLOYEE (
empno NUMBER(4)
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) ,
NOT NULL , NOT NULL ,
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));
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 COMMENT ON COLUMN employee.empmsal COMMENT ON COLUMN employee.empcomm
NOT NULL , ,
IS 'Course Code';
IS 'Course Description'; IS 'Course Category';
IS 'Course Duration';
IS 'Employee number'; IS 'Employee name';
IS 'Employee initials'; IS 'Employee job';
IS 'Employee birthdate';
IS 'Employee monthly salary'; IS 'Employee commission';
Page 10 of 19
COMMENT ON COLUMN employee.deptno IS 'Department Number';
COMMENT ON COLUMN employee.mgrno IS 'Employees manager (empno of manager)';
ALTER TABLE DEPARTMENT
ADD (CONSTRAINT department_fk FOREIGN KEY (empno)
REFERENCES employee (empno));
CREATE TABLE HISTORY (
empno NUMBER(4) NOT NULL , histbegindate DATE NOT NULL , histbeginyear NUMBER(4) NOT NULL , histenddate DATE ,
histmsal NUMBER(6,2)
histcomments VARCHAR(60) ,
deptno NUMBER(2) NOT NULL
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 COMMENT ON COLUMN history.histbegindate COMMENT ON COLUMN history.histbeginyear COMMENT ON COLUMN history.histenddate COMMENT ON COLUMN history.histmsal history record';
COMMENT ON COLUMN history.histcomments record';
COMMENT ON COLUMN history.empno
CREATE TABLE OFFERING ( offbegindate DATE NOT NULL , crscode VARCHAR(6) NOT NULL , offlocation VARCHAR(8) ,
empno NUMBER(4) ,
IS 'Department Number';
IS 'Date history record begins'; IS 'Year history record begins'; IS 'Date history record ends'; IS 'Monthly Salary for this
IS 'Comments for this history IS 'Employee number';
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 COMMENT ON COLUMN offering.crscode COMMENT ON COLUMN offering.offlocation COMMENT ON COLUMN offering.empno running offering';
IS 'Begin date for offering';
IS 'Course Code';
IS 'Location for offering';
IS 'Employee number for employee
Page 11 of 19
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';
Note in coding your SQL each SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY clause must start on a new line.
Page 12 of 19
Q5 [10 marks]
The company needs to record a new department. This new department's number will be 10 higher than the highest current department number and will be called EXAM and is located in BOSTON. The employee named KING who has a job as the only company DIRECTOR has been assigned to manage the new EXAM department.
The company has also decided that they wish to record, for each department, the number of employees currently working in the department (the employee count). For new departments the number of employees in the department should be set to 0. For those departments which currently have employees, the employee count should correctly reflect the current number of employees in the department.
Code the SQL statements to modify the database to meet these requirements.
INSERT INTO department VALUES (
MAX(deptno)
department
SELECT empno
empname = 'KING'
AND empjob = 'DIRECTOR'
ALTER TABLE department ADD deptcount NUMBER(3, 0) DEFAULT 0 NOT NULL;
UPDATE department d
deptcount = (
COUNT(empno)
employee e
); COMMIT;
e.deptno = d.deptno
Page 13 of 19
Q6 [6 marks]
List the employee number, the employee name, the employee job and the yearly salary of all employees that belong to the ‘Sales’ department. The name of the employee must be shown in a column called “Employee Name” and the yearly salary must be shown in the form of $34,200 in a column called “Yearly Salary”. Show the employee with the highest salary first, if two employees have the same salary, order it based on the employee number.
Code the SQL SELECT statement.
to_char(empmsal * 12, '$99,990')
employee e
JOIN department d
ON e.deptno = d.deptno
upper(deptname) = upper('Sales')
empmsal DESC,
Q7 [9 marks]
AS "Employee Name",
AS "Yearly Salary"
For each course which has been completed by at least 5 employees, list the course code, the course description and the course duration. The course duration must be shown in a column called “Course Duration” and include the word ‘days’ (e.g. 4 days). Order the output by the course code. Code the SQL SELECT statement.
c.crscode,
crsduration || ' days' as "Course Duration"
registration r
JOIN course c
ON r.crscode = c.crscode
r.regevaluation IS NOT NULL
c.crscode,
crsduration
COUNT(empno) >= 5
c.crscode;
Page 14 of 19
Q8 [15 marks]
List ALL employees whose total course registrations are less than the average number of registrations for employees who have registered for a course. Note that some employees may repeat a course, this repeat does not count as a different course. In the list, include the employee number, name, date of birth and the number of different courses they have registered for. Order the output by employee number.
Code the SQL SELECT statement.
to_char(empbdate, ‘dd-Mon-yyyy’) AS dob,
COUNT(DISTINCT crscode) AS crscount
employee e
LEFT JOIN registration
r ON e.empno = r.empno
to_char(empbdate, ‘dd-Mon-yyyy’)
COUNT(DISTINCT crscode) < (
AVG(COUNT(DISTINCT crscode))
registration
Page 15 of 19
Q9 [10 marks]
We wish to develop a php based web page which shows all departments, and their manager's name and monthly salary as shown below:
Here is the incomplete PHP code for the page:
| Department Number | Department Name | Department Location | Manager | Manager’s Monthly Salary |
|---|