Database modelling – case study (10 marks)An online advertising agency wants to create a simple database to manage theirclients’ advertising campaigns. Clients’ details need to be recorded. Clients aregrouped into regions where a region can have up to 20 clients. Clients requestadvertising campaigns, and a record is kept of every campaign. Each campaign isbased on a unique theme and has an associated cost as quoted to each client. Eachcampaign is managed by an account manager, an advertising manager and agraphic design manager, who may also manage other campaigns. Payments foreach campaign is recorded, and payment instalment arrangements are available forthose clients who meet some certain criteria. Date and amount of each payment isrecorded.Part 1: GRD (6 marks)Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in theabove, indicating all the primary keys and foreign keys. The GRD should be in aform similar to Figure 17.9 (page 554 or 516 for edition 5) of the textbook, but allthe 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 yourdesigned 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 sufficientrecords into your table/s (at least 4 records in each table) so that your queryreturns at least 2 records – that is yours and your group member’s familyname. (if you are working alone, then you need to have your name and thetutor’s name shown in the result of the query). Screenshots are required forthe 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 thecampaigns by each client on a specific date in the most recent campaignlisted first. (screenshot required).
