ISYS224 2019-Assignment 1

ISYS224 2019-Assignment 1
DEPARTMENT OF COMPUTING
ISYS224 2019 S2 – ASSIGNMENT ONE
(25%)
Due: 6pm Friday 13 September 2019 (Week
7)
Database Design & Implementation
Please Print Clearly In CAPITALS
Surname HASAN
First Name ASEF
Student ID 44182341
Signature
Student Code of Conduct
Macquarie University students have a responsibility to be familiar with
the Student Code of Conduct: https://students.mq.edu.au/study/getting-
started/student-conduct
Student Support
1

ISYS224 2019-Assignment 1
Macquarie University provides a range of support services for students.
For details, visit http://students.mq.edu.au/support/
2

ISYS224 2019-Assignment 1
Task 1: Conceptual data model
--Add a screenshot of your conceptual data model
in the form of an enhanced ER (EER) model (the
model must be legible, when zoomed in). Note
that you should not show the foreign keys in the
EER model.
-- Identify and justify the use of, if any,
generalization /specialisation, weak entity types,
and attributes on relationships.
The Full time and part time staffs can be generalized in to the staffs
table. The weak entity identified in this ER Diagram is booking table. This
is because the booking table depends on a number of other entities.
Hence the selection.
-- List your assumptions (if any) over and above
what was provided in the problem domain
description.
A number of assumptions have been done during the development
of the ER Diagram model.
3

ISYS224 2019-Assignment 1
 Firstly, it has been assumed that each of the campaigns would have
only a single manager.
 In addition to this, the staff would have only a single supervisor and
hence, they would not be having any unique keys their name itself
would be unique.
 Additionally, the cost for the campaign has been assumed and not
calculated.
4

ISYS224 2019-Assignment 1
Task 2: Logical data model
--Add a screenshot of your logical data model (the
model must be legible, when zoomed in).
Staff (StaffID, StaffName,
Supervisor, StaffType,
StaffCategory, SalaryGrade)
Primary Key: StaffID
Alternate Key: Supervisor
Full_Time (FTID, StaffID, Salary)
Primary Key: FTID
Foreign key : StaffID reference
Staff
Part_Time (PTID, StaffID, Salary)
Primary Key: PTID
Foreign key : StaffID reference
Staff
Studio (StudioNumber)
Primary Key: StudioNumber
Campaign (CampaignID, Title,
Theme, EstimatedCost,
ActualCost, Managers)
Primary Key: CampaignID
Alternate Key: Managers
Adverts (AdvertID, CampaignID,
TargetDate, DateOfCompeltion)
Primary Key: AdvertID
Alternate Key: CampaignID
Foreign Key: CampaignID
reference Campaign
Bookings (BookingID, AdvertID,
StudioNumber, BookingDate,
Staff_Time (TimeID, StaffID,
5

ISYS224 2019-Assignment 1
NoOfHours)
Primary Key: BookingID
Foreign Key: AdvertID reference
Adverts, StudioNumber reference
Studio
CampaignID, TimeSpent)
Primary Key: TimeID
Foreign Key: CampaignID
reference Campaign, StaffID
reference Staff
Clients (ClientID, ClientName,
ContactPerson)
Primary Key: ClientID
Alternate Key: ClientName
Invoice (InvoiceID, ClientID,
CampaignID)
Primary Key: InvoiceID
Foreign Key: CampaignID
reference Campaign, ClientID
reference Client
-- Discuss how you translated the conceptual data
model into the logical data model – briefly.
The conceptual data model was transformed by converting all the
entities into tables for modelling the database. In addition to this, the
primary key values for all the tables have been identified and the foreign
keys have been identified for establishing the relationship in between the
tables.
-- You may refer to specific translation /
conversion rules discussed in the lectures (Week
3 slides, Chapter 17 of the textbook)
6

ISYS224 2019-Assignment 1
Task 3: Logical data model
-- List the functional dependencies (after making
necessary assumptions) for each relation in the
logical data model of Task 2.
-- Identify the normal form each relation is in and
justify it according to the definition of the
corresponding normal form (e.g., if a relation is
already in 2NF and doesn’t have any transitive
dependencies, it is in 3NF).
-- You may refer to the process of normalization
discussed in the lectures (Week 4 slides,
Chapters 14&15 of the textbook)
Staff (StaffID, StaffName, Supervisor, StaffType, StaffCategory,
SalaryGrade)
It has been assumed that the different staffs would be
having different Supervisors and hence, the supervisor’s names
have been stored with that of the staffs.
Nontrivial FDs:
StaffID -> StaffName, Supervisor, StaffType, StaffCategory,
SalaryGrade
1NF – no repeating groups (only one value per each column & row
intersection)
2NF – 1NF and every non-primary-key attribute is fully
functionally dependent on any candidate key.
3NF -- 1NF and 2NF and no non-primary-key attribute is
transitively dependent on any candidate key
BCNF – 3NF and every determinant is a candidate key
Full_Time (FTID, StaffID, Salary)
7

ISYS224 2019-Assignment 1
The full time staff table helps in the specialization of the
staffs of the organization and the salary depends on the full time
staff ID.
Nontrivial FDs:
FTID -> StaffID, Salary
1NF – no repeating groups (only one value per each column & row
intersection)
2NF – 1NF and every non-primary-key attribute is fully
functionally dependent on any candidate key.
3NF -- 1NF and 2NF and no non-primary-key attribute is
transitively dependent on any candidate key
BCNF – 3NF and every determinant is a candidate key
Part_Time (PTID, StaffID, Salary)
The part time staff table helps in the specialization of the
staffs of the organization and the salary depends on the part time
staff ID.
Nontrivial FDs:
PTID -> StaffID, Salary
1NF – no repeating groups (only one value per each column & row
intersection)
2NF – 1NF and every non-primary-key attribute is fully
functionally dependent on any candidate key.
3NF -- 1NF and 2NF and no non-primary-key attribute is
transitively dependent on any candidate key
BCNF – 3NF and every determinant is a candidate key
Studio (StudioNumber)
1NF – no repeating groups (only one value per each column & row
intersection)
8