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 Shines 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 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.
Construct a conceptual data model in the form of an enhanced ER (EER) model for the description. 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 modelling tool such as Power Designer or a drawing tool such as draw.io.
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 Power Designer or using a text editor.
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).
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.
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.
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.
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.