这个数据库设计涉及一个名为Shine的广告公司用来管理客户广告活动的系统

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
Macquarie University provides a range of support services for students. For details,
visit http://students.mq.edu.au/support/
1

ISYS224 2019-Assignment 1
The background knowledge for the assignments is given in the textbook(s), lectures, any other components of the unit, in the prerequisite unit ISYS114, and in the readings provided on ilearn. However, some parts of the assignments may not be answered without prior independent research and/or searching for other sources of information.
This assignment concerns database conceptual modelling, logical design and implementation. It will be marked out of 100 and will contribute 25% towards your final grade. It consists of developing an enhanced ER (EER) model (Conceptual data model – CDM), transforming the EER model into a relational data model (logical data model – LDM), forward engineering the relational model to produce a DDL script, creating and populating the database tables, and running SQL queries against the tables. The description of the Problem domain is given below.
1 Problem Domain
This question deals with a system used by an advertising agency called Shine to manage clients’ advertising campaigns. Shine has a number of full-time staff members (such as directors, administrative personnel, accountants, account managers, technical personnel) and also a number of casual staff members (such as actors, models, graphic designers) who can offer specialized skills for each advertising campaign for its clients. Casual staff members do not have a permanent office in the agency and are contacted on their mobile phone. Shine has a hierarchical management structure where each staff member (except for the managing director) reports to a single supervisor within the company.
Shine deals with other companies as its clients. A record is kept of each client company, and each client company has one main contact person with Shine. Similarly, Shine nominates a full-time member of its staff – a director, or an account manager to be the contact for each client. Casual staff members are not eligible to be contact persons.
Shine runs advertising campaigns for its clients, and a record is kept of every campaign. Each campaign is based on a unique theme. One full-time member of Shine’s staff, again either a director or an account manager, manages each campaign. Shine staff may work on zero, or one or more campaigns at a time. For each campaign they work on, they are answerable to the manager of that campaign, who may or may not be their own supervisor.
When a campaign starts, an estimate of the cost and finish date needs to be set and agreed upon. Each campaign includes one or more adverts. Adverts can be one of several types: websites, newspapers, magazines, TV, etc. Shine currently operates five studios and each advert may require a few bookings of studios for a number of hours on specific times and dates. Shine charges an hourly rate for studio bookings. The actual cost of a campaign is calculated from a range of information such as: cost of staff time, cost of studio time, cost of consumables etc.
The system also holds a number of fixed salary grades and annual and hourly pay rates of each grade, so that the cost of staff time on a campaign can be calculated from the timesheets that they fill out. Shine pays annual salaries to full-time staff but it charges hourly pay rates to its clients. Casual staff members are also graded and they are paid based
2

ISYS224 2019-Assignment 1
on an hourly pay rate charged directly to the clients. Please note that (both full-time and casual) staff members can have different salary grades during the campaign period (for instance, when a staff member is given a pay rise right in the middle of a campaign).
When the campaign is completed, an actual completion date and the actual cost are recorded and a single invoice for the whole campaign is sent to the client. The issue date and payment status of the invoice are also recorded. When the client pays, the date paid is recorded.
2 Task Specifications
Task 1 (25 marks)
Construct a conceptual data model in the form of an enhanced ER (EER) model for the above problem domain (see the Appendix for a sample). Identify and justify the use of, if any,generalization/specialisation,weakentitytypes,andattributesonrelationships. This model should include entities, attributes, primary keys, the relationships among entities with cardinality and constraints. You can make any reasonable assumptions if there is a lack of information on particular aspects and/or you think there is ambiguity. If necessary, you may also introduce additional entities to ensure that every data requirement is captured. Note that you should not show the foreign keys in the EER model.
You can do this task through a data modeling tool such as PowerDesigner or a drawing tool such as draw.io. Add/paste the EER model into the Assignment1.doc file.
Task 2 (15 marks)
Construct a logical data model (a relational data model) by transforming the conceptual data model of Task 1, showing the relations, their attributes, and foreign and primary keys. The relational data model can be presented either as a relation diagram or a relational schema (see the Appendix for samples).
You can choose to construct the relational data model either through MySQL Workbench or another tool such as PowerDesigner, or using a text editor. Add/paste the model into the Assignment1.doc file.
Task 3 (15 marks)
List the functional dependencies (make necessary assumptions) for each relation in the relational 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).
Add/paste your answers into the Assignment1.doc file. Task 4 (20 marks)
Create and run the DDL scripts to implement the relevant tables in the relational data model in MySQL. You can choose to do this either through MySQL Workbench by forward engineering the model or manually using a text editor.
The DDL scripts will be saved in your Assignment1.sql file. 3

ISYS224 2019-Assignment 1
Create and run the DML scripts to insert records into each of your tables (there should be a minimum of 5 records per table). When inserting records into the tables, you need to make sure that the result of each SQL query (for Task 5 given below) will return at least one record.
The DML scripts will also be saved in your Assignment1.sql file. Take snapshots of the tables populated with data and add them to the Assignment1.doc file.
Task 5 (25 marks)
Write and run the DML scripts for the following queries. Note that these SQL queries must be tested using the data you inserted in Task 4.
The DML scripts will be saved in your Assignment1.sql file. Take snapshots of the tables of the query execution (query and result) and add it to the Assignment1.doc file.
Q1. List the titles and themes of the Campaigns’ whose actual costs are below the average estimated cost of all the campaigns.
Q2. List the campaign titles and the number of their advertisements whose completion dates were earlier than their target dates.
Q3. List all the names of full-time staff members whose supervisor(s) were not the managers of the campaigns they worked on.
Q4. For campaigns with more than two staff members working on them, list the campaign title and the number of staff members who had salary grade greater than 2.
Q5. List all the full-time staff members who do not manage any campaigns.
3 Submission
You must submit all the work in two documents named Assignment1.doc and Assignment1.sql through the electronic submission site on ilearn. If you modify your file(s) after submission, you may re-submit your assignment again before the due date.
Notes
• The Assignment1.doc file will be based on the template word file provided on ilearn. Fill out the details provided on the first page of the template. For each task, follow the instructions given and add your answers in.
• You are not allowed to submit hand drawn images for the models/diagrams in Tasks 1 & 2; you should use a modelling or drawing tool as mentioned above. If you happen to submit hand-drawn images, you will NOT receive any marks for it.
• If your images for any task lack clarity and /or they are not legible, you will not be given any marks. Tutors can zoom in to check the diagrams. But we will not be able to increase the quality of the image. Whatever is submitted is the final submission. So, please make sure your images are legible.
4

ISYS224 2019-Assignment 1
• The Assignment1.sql file may be executed in the marking process and also manually marked for clarity. It is your responsibility to ensure the correctness of your DDL and DML scripts and they run perfectly under MySQL installed in the labs. The results of the execution of the scripts should also match those given in the Assignment1.doc file.
• SQL statements containing syntax errors are NOT acceptable. If you make syntax errors in your SQL statements, you may get zero marks.
• PowerDesigner also offers a 30-day free trial period should you wish to install it on your personal computer to use it outside the opening hours of the labs in 9WW Level 1.
Late Submission Policy
No extensions on assignments will be granted without an approved application for Special Consideration.
Late submissions will be accepted but there will be a deduction of 10% of the total available marks made from the total awarded mark for each 24 hour period or part thereof that the submission is late. For example, 25 hours late in submission for an assignment worth 10 marks – 20% penalty or 2 marks deducted from the total.
No submission will be accepted after solutions have been posted.
5

Appendix
Sample CDM for Task 1 (in the book notation)
ISYS224 2019-Assignment 1
Page 529, Thomas Connolly and Carolyn Begg. Database Systems. A Practical Approach to Design, Implementation, and Management, Sixth Edition, Pearson, 2014, ISBN-9781- 2920-6183-2
6

Sample relational schema for Task 2 (in the book notation)
ISYS224 2019-Assignment 1
Page 553, Thomas Connolly and Carolyn Begg. Database Systems. A Practical Approach to Design, Implementation, and Management, Sixth Edition, Pearson, 2014, ISBN-9781- 2920-6183-2
7

Sample relation diagram for Task 2 (in the book notation)
ISYS224 2019-Assignment 1
Page 587, Thomas Connolly and Carolyn Begg. Database Systems. A Practical Approach to Design, Implementation, and Management, Sixth Edition, Pearson, 2014, ISBN-9781- 2920-6183-2
8