ISYS224留学生代做、SQL程序设计调试、SQL语言代做、代写ISYS114

日期：2019-08-29 10:29

ISYS224 2019-Assignment 1

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/

ISYS224 2019-Assignment 1

2

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

ISYS224 2019-Assignment 1

3

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, weak entity types, and attributes on relationships. 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.

ISYS224 2019-Assignment 1

4

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.

ISYS224 2019-Assignment 1

5

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.

ISYS224 2019-Assignment 1

6

Appendix

Sample CDM for Task 1 (in the book notation)

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

ISYS224 2019-Assignment 1

7

Sample relational schema for Task 2 (in the book notation)

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

ISYS224 2019-Assignment 1

8

Sample relation diagram for Task 2 (in the book notation)

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