Delphi Campaign Management System: Staff, Client, and Campaign Details

Campaign Contacts and Account Manager

Each client company is asked to nominate two members of staff to act as contacts for each campaign.  The information requested is the person’s name, position, email address and telephone number.  Delphi allocate an account manager to each client no matter how many campaigns the client is involved in.  The account manager is responsible for maintaining contact with the client after campaigns have finished as part of the marketing activities for Delphi.

A campaign is conducted to promote a particular product for the client.  When a campaign is first started, it will be given a unique campaign code, for example C111, and a campaign name.  Each campaign has a campaign manager who is responsible for the day-to-day running of the campaign.  Once a campaign reaches the development stage a campaign team is formed by the campaign manager from Delphi staff.  Staff may work on more than one campaign at the same time.

The initial stages of a campaign will involve a number of meetings with the client and the design of the campaign.  These processes will involve staff members who will be involved in the campaign.  The system needs to keep a record of all meetings held for a campaign.  The date, time and duration of each meeting should be recorded along its purpose and the internal staff attending.  Whilst specific details of the room used for the meeting are not required, a record of where the meeting was held is necessary.  Meetings will either be held at Delphi or at the client’s offices.

Once a campaign starts, its first day is known as rollout day, the purchasing 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.  

System Requirements

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 and Development Requirements

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.