Q1. Database modelling – case study (10 marks)
An online advertising agency wants to create a simple database to manage their
clients’ advertising campaigns. Clients’ details need to be recorded. Clients are
grouped into regions where a region can have up to 20 clients. Clients request
advertising campaigns, and a record is kept of every campaign. Each campaign is
based on a unique theme and has an associated cost as quoted to each client. Each
campaign is managed by an account manager, an advertising manager and a
graphic design manager, who may also manage other campaigns. Payments for
each campaign is recorded, and payment instalment arrangements are available for
those clients who meet some certain criteria. Date and amount of each payment is
recorded.
Part 1: GRD (6 marks)
Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in the
above, indicating all the primary keys and foreign keys. The GRD should be in a
form similar to Figure 17.9 (page 554 or 516 for edition 5) of the textbook, but all
the attributes should be kept there too.
Part 2: Create tables – SQL (4 marks)
i. Write an SQL script (of statements) that generates all the tables for your
designed database with at least 3 appropriate attributes in each table.
Screenshots are required for the records of all the created tables.
ii. Write an SQL script to insert records into your tables. Insert sufficient
records into your table/s (at least 4 records in each table) so that your query
returns at least 2 records – that is yours and your group member’s family
name. (if you are working alone, then you need to have your name and the
tutor’s name shown in the result of the query). Screenshots are required for
the records of all the tables.
iii. List all the clients whose name contains your own (you and your partner’s)
first and last name and a contact number.
iv. For a given campaign (number), write an SQL statement to list all the
campaigns by each client on a specific date in the most recent campaign
listed first. (screenshot required).