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
First Name
Student ID
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
The main points of generalization used in the assignment are the
staff generalization. The generalization of the full time and the part time
staffs to the staff table has been done here. A potential weak entity in this
model is invoice.
3

ISYS224 2019-Assignment 1
Task 2: Logical data model
Staff (StaffID, StaffName,
Supervisor, StaffType,
StaffCategory, SalaryGrade)
Primary Key: StaffID
Alternate Key: Supervisor
Part_Time (PTID, StaffID, Salary)
Primary Key: PTID
Foreign key : StaffID reference
Staff
Full_Time (FTID, StaffID, Salary)
Primary Key: FTID
Foreign key : StaffID reference
Staff
Campaign (CampaignID, Title,
Theme, EstimatedCost,
ActualCost, Managers)
Primary Key: CampaignID
Alternate Key: Managers
Advertisement (AdvertisementID,
CampaignID, TargetDate,
DateOfCompeltion)
Primary Key: AdvertisementID
Alternate Key: CampaignID
Foreign Key: CampaignID
reference Campaign
Bookings (BookingID, AdvertID,
StudioNumber, BookingDate,
NoOfHours)
Primary Key: BookingID
Foreign Key: AdvertID reference
Adverts, StudioNumber reference
Studio
Clients (ClientID, ClientName,
ContactPerson)
Primary Key: ClientID
Alternate Key: ClientName
Staff_Time (TimeID, StaffID,
CampaignID, TimeSpent)
Primary Key: TimeID
4

ISYS224 2019-Assignment 1
Invoice (InvoiceID, ClientID,
CampaignID)
Primary Key: InvoiceID
Foreign Key: CampaignID
reference Campaign, ClientID
reference Client
For the conversion of the models the foreign keys have been
introduced into the logical model and the relationships introduced in the
assignment have been references properly in the model.
5

ISYS224 2019-Assignment 1
Task 3: Logical data model
Staff (StaffID, StaffName, Supervisor, StaffType, StaffCategory,
SalaryGrade)
Nontrivial FDs:
StaffID -> StaffName, Supervisor, StaffType, StaffCategory,
SalaryGrade
Full_Time (FTID, StaffID, Salary)
Nontrivial FDs:
FTID -> StaffID, Salary
Part_Time (PTID, StaffID, Salary)
Nontrivial FDs:
PTID -> StaffID, Salary
Campaign (CampaignID, Title, Theme, EstimatedCost, ActualCost,
Managers)
Nontrivial FDs:
CampaignID -> Title, Theme, EstimatedCost, ActualCost, Managers
Advertisement (AdvertimentID, CampaignID, TargetDate,
DateOfCompletion)
Nontrivial FDs:
AdvertisementID -> TargetDate, Date, StaffCategory, SalaryGradeā
Bookings (BookingID, AdvertID, StudioNumber, BookingDate,
NoOfHours)
Nontrivial FDs:
BookingID -> BookingDate, NoOfHours
6