Design and Implementation of Campaign Management Database

Purchasing assistants are responsible for finding and buying the required location space for adverts in the campaigns, referred to as ‘placements’.  This includes TV and radio time, newspaper and magazine space and negotiating with agencies who manage advertising hoarding space and leafleting campaigns.  Each placement is given a unique code within the campaign, e.g. for campaign C145, advert A18 might have three placements, PL1, PL2, PL3. Advert A19 may have two – PL4, PL5 and so on.  The various types of placement have different information recorded against them – there is a standard record card in use so that the purchasing assistant can record these details ‘on-the-fly’.  An example record card is shown in Figure 2.  A single advert may get placed in several locations, e.g. all Sunday newspapers, in which case each newspaper is treated as a separate placement.assistants are responsible for checking that the adverts do appear as planned – for each placement they check to see that the advert was presented in the place and on the dates expected.  A note is made of any placements that do not occur as planned.  

Delphi require a database to support their key activities in a campaign, this includes the recording of:

·Staff details and their involvement in campaigns

·Client details including contacts for particular campaigns

·Campaign details

·Meeting details

·Advert and placement details

Details of the contents of actual documentation produced during a campaign is part of the campaign documentation and is not regarded as relevant information for the database which is essentially concerned with recording the information necessary to track the progress of a campaign.

Design a class diagram that will capture the data and links capable of supporting the requirements outlined above.  The figures provide some sample data to give you additional information on the kind of data you will need to store.   The model needs to capture the data requirements in order for the system to work.  You also need to develop a list of constraints and a list of assumptions.

This stage will be to implement your design, using Oracle.

·Convert your model into an SQL database.

·Populate your database with some sample data

·Test your database.  You will need to consider testing the database to ensure that the database meets the information requirements of the system.  You need to create and run SQL queries that produce the information required.  

The report will require the following chapters:

Chapter 1: Introduction – one page description of the aims and objectives of the report.

Chapter 2: The Class Diagram with constraints and assumptions.

Chapter 3: Implementation.  Provide a listing of the SQL table definitions.

Chapter  4: Six queries that demonstrate that your database meets the requirements of the system.

Chapter 5: Conclusion.  A critical evaluation of your final product and a review of the entire exercise.