FIT2094 - Databases SAMPLE SOLUTIONS
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FIT2094 - Databases
MOCK SCHEDULED FINAL ASSESSMENT/EXAM
SAMPLE SOLUTIONS
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 |
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 |
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
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.
OR
π
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))
)
(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
⨝
(π author_id (σ authorpaper_position = 1 (AUTHOR_PAPER)))
)
)
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.
Monash Computing Students Society (MCSS) Logical Model
PART C Normalisation [Total: 10 Marks]
Q3 [10 marks]
The Super Electronics Invoice shown below displays the details of an invoice for the client Alice Paul.
Super Electronics INVOICE |
||||||
Client Number: Client Name: Client Address: Client Phone: |
C3178713 Alice Paul 43 High Street, Caulfield, VIC 3162 0411 245 718 |
Invoice No.: 132 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 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]
UNF
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)
1NF
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
2NF
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
3NF
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
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) NOT NULL ,
sallower NUMBER (6,2) NOT NULL ,
salupper NUMBER (6,2) NOT NULL ,
salbonus NUMBER (6,2) NOT NULL ,
CONSTRAINT salgrade_pk PRIMARY KEY (salgrade),
CONSTRAINT salgrade_chk1 CHECK (sallower >= 0),
CONSTRAINT salgrade_chk2 CHECK (sallower <= salupper));
COMMENT ON COLUMN
COMMENT ON COLUMN
COMMENT ON COLUMN
COMMENT ON COLUMN
2022-05-31