Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
FIT9132
SAMPLE EXAM PAPER
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]
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.
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 13
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, 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.