CI7300 Data Management and Governance

Assignmentwork : Database Design & Development

Assignment Question

Background

Delphi Promotions is a promotions company that handles advertising campaigns for its clients. Clients approach the company with the product or product range that they wish to promote and Delphi develop an initial proposal for a range of possible campaigns. When the client agrees to a specific campaign from the portfolio, a contract is signed and Delphi produce the required adverts and arrange for the relevant press and magazine advertising space, web space, radio or TV air-time or poster locations.

It is important for Delphi to keep track of the campaigns they are running for clients. At present all documentation for a campaign is held on paper. This system worked well when the number of clients was small but the company now employs over 40 staff and have around 50 clients with approximately 30 campaigns running at any one time. The company has simply become too large to rely on paper records. Delphi has found that it is not keeping accurate track of a campaign and there is concern that the company is losing business through poor management of campaigns and have decided that they need a database system to keep track of Delphi’s campaigns.

Information Requirements

Staff

As previously mentioned, Delphi have a permanent staff of over 40 people. Every member of staff has a role (for example, account manager, campaign manager, graphic designer, etc), a salary grade and a corresponding charge-out rate at which clients are charged. Staff names should also be recorded together with the telephone number, fax number and email. There are members of staff with the same name therefore a staff number will also be required for identification purposes.

Clients and Campaigns

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 Campaign Process

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.

A campaign summary sheet is created at the start of a campaign and maintained throughout to summarise the details of the campaign. An example of a summary sheet can be found in Figure 1.

All campaigns consist of adverts. Each advert within a campaign has an advert code (e.g. A111) for filing and record-keeping purposes. Types of advert can be either newspaper, magazine, radio, television, cinema, poster, web or leaflet. Along with each advert is a brief description of its content, a description of the intended audience (e.g. financial newspapers, children’s TV, sports events) and details of its intended size (e.g. fullpage or ½ page for print, A4 or A5 leaflet or 30 seconds for broadcast media), so that the purchasing assistant can identify the right location for the advert. Each advert is made up of a range of components, such as photographs, text, graphics, actors, sound recordings, and so on. As the design of the components proceeds, a folder of work in progress is kept referenced by campaign number, advert number and component type (for example, C111, A19, photographs which will contain all the detailed requirements for the photographs for that advert). When a folder is complete and signed off by the advertising executive, the developed component is recorded as being ready for production and the design completion date is recorded. The campaign manager can judge progress of an advert, or a campaign by looking for incomplete components. The campaign manager knows a campaign design is complete when all the required components for all the adverts are recorded as complete.

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.

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.

Figure 1: Campaign Summary

(summary form used to confirm the details of the campaign to be developed)

Figure 2: Placement-Location Record Card

Design and Development Requirements

Conceptual Design Stage

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.

Implementation Stage

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.

Report Mark Sheet

---

Assignment Answer

Chapter1:

In this chapter we have describe the all introduction about this project as well as we have cover the aim and object of this project. This assignment is under the Delphi promotion company who is only for advertisement campaigns. In this company all data is held by supervisor which handle the data on pen and paper base. In this case study there are clients and staffs which managed by this Delphi promotion company. This company needs a database management system which manages the all data about clients and staff. There is campaign management system in this company which manages the all campaigns. Now this company is work fine till when clients are few. When clients are increase then this is hard to manage using pen and paper. So this company needs database management system. So main aim of this case study is that to make a database management system which stores all data of company and all data. This is main aim of this project. The objective of this project is to calculate all data which is assumed by us using sql queries. There are six queries in this assignment which we have to run on oracle database. Oracle database is used for manage the stored the data inside a tables. Delphi promotion company will be work fine when it have a database management system. This system will work fine whatever quantity of data will be inserted into tables. So this is all about the task and introduction of this assignment.

Chapter 2:

This is a class diagram of this case study.

There are six classes in this diagram. In staff class there are some fields which are class variables like name, phone number fax etc. In Client table some class variables are showing in this diagram like name, emails, manager name and phone number etc. campaign class is stored the all data of campaign and there are many relation to this table to advertisement as well as staff. The meeting class is used to store the all data of meeting like meeting time, meeting date etc.

Constraints:

Foreign key and primary key are used in this database management system. Foreign key concept is main concept of this diagram. This is fully about connectivity of data. The primary key never be null and it will always unique. But in foreign key this can be null.

Assumption:

We have assumed that there are six table will be used for this case study. These tables are used to manage the all data. We things that this is enough for all functional and non functional requirement. These is a auto generated fields which is used for make table automatically put a new id which is unique.

Chapter3:

In this chapter we have implement the all create table command code—

1. Create command for table client_data.

create table client_data(
idclient varchar2(500) primary key,
nameclient varchar2(500),
positionclient varchar2(500),
emailclient varchar2(500),
numberclient varchar2(500),
managerName varchar2(500));

Create command is used for creating a table in oracle database. We have to define all type of columns data which we are creating. We have give the length of data 500 to all. There is a primary key used in this table which is idclient.

2. Create command for table Meeting_data.

create table meeting_data(
meet_id varchar2(500) primary key,
meet_location varchar2(500),
meet_date varchar2(500),
meet_time varchar2(500),
meet_duration varchar2(500),
numberstaff varchar2(500),
foreign key (numberstaff) references staff_data(numberstaff));

Create command is used for creating a table in oracle database. We have to define all type of columns data which we are creating. We have give the length of data 500 to all. There is a primary key used in this table which is meet_id. The foreign key is used in this table which Is numerstaff.

3. Create command for table Campaign_data.

create table campaign_data(
c_code varchar2(500) primary key,
c_name varchar2(500),
c_manager_name varchar2(500),
idclient varchar(500),
foreign key (idclient) references client_data(idclient));

Create command is used for creating a table in oracle database. We have to define all type of columns data which we are creating. We have given the length of data 500 to all. There is a primary key used in this table which is c_code. The foreign key is used in this table which Is idclient.

4. Create command for table staff_data.

create table Staff_data(
numberstaff varchar2(500) primary key,
namestaff varchar2(500),
telephonestaff long,
faxstaff varchar2(500),
email varchar2(500));

Create command is used for creating a table in oracle database. We have to define all type of columns data which we are creating. We have give the length of data 500 to all. There is a primary key used in this table which is numberstaff.

5. Create command for table placement_data.

create table placement_data(
placement_id varchar2(500) primary key,
placement_type varchar2(500),
advert_code varchar2(500),
foreign key (advert_code) references advert_data(advert_code));

Create command is used for creating a table in oracle database. We have to define all type of columns data which we are creating. We have given the length of data 500 to all. There is a primary key used in this table which is placement_id. The foreign key is used in this table which is advert_code.

6. Create command for table advert_data.

create table advert_data(
advert_code varchar2(500) primary key,
advert_type varchar2(500),
description_advert varchar2(500),
advert_component varchar2(500),
c_code varchar2(500),
foreign key (c_code) references campaign_data(c_code));

Create command is used for creating a table in oracle database. We have to define all type of columns data which we are creating. We have given the length of data 500 to all. There is a primary key used in this table which is advert_code. The foreign key is used in this table which is c_code.

Chapter 4:

Six queries for this case study:

1. Show name of all campaign and there details with advertisement which has advertisement type are web and radio

select c_name as CampaignName, c_manager_name as ManagerName ,advert_type as AdvertisementType from campaign_data c1 inner join advert_data a1 on c1.c_code =a1.c_code where advert_type in ('radio','web');

Description:

Inner join is used in this query. Select command is used for select three columns showing in this output. Here web and radio is put inside a ‘in’ keyword. Now this is used in for filtering. So this will use the concept of foreign key for joining a table to each other.

2. Show the few details of Staff that have meeting on date ‘21/6/2020’.

select namestaff as staffname, email, meet_location from staff_data c1 inner join meeting_data c2 on c1.numberstaff =c2.numberstaff where meet_date ='21/6/2020';

This is used where clause and get all data which is related to given date. The foreign key concept is used in this query.

3. Give name of staff and staff id which has meeting client office for 40 min.

select c1.numberstaff as staffid, namestaff as staffname from staff_data c1 inner join meeting_data c2 on c1.numberstaff =c2.numberstaff where meet_location='Client office' and meet_duration = '40 min';

There is a connection using foreign key. Where clause is used in this query.

4. Show the name of advert and placement details which has advert type is newspaper.

select advert_type , placement_type from advert_data c1 inner join placement_data c2 on c1.advert_code =c2.advert_code where advert_type='newspaper';

There is a connection using foreign key. Where clause is used in this query.

5. Show all details of client which have campaign name Froggies.

select nameclient as clientname, positionclient, emailclient,numberclient from client_data c1 inner join campaign_data c2 on c1.idclient =c2.idclient where c_name ='Froggies';

6. Show total number of placement which is associated with advert_code.

select p1.advert_code, count(*) from advert_data p1 inner join placement_data p2 on p1.advert_code =p2.advert_code group by p1.advert_code;

The group by command is used in this table which is select the data with corresponding group of data. Inner join is used in this query.

Chapter 5:

In this chapter we have defined the all conclusion of this report. We have successfully design the database management system for Delphi Promotion Company. In this database management system, it is not worry about how many clients are ready to get touch with. This will stored the all data of this company inside tables. This database manages the all data of clients, campaigns and meetings data of staff. There are six tables we have considered. We have cleared the all objective of these assignments. We have successfully attached the all screenshots of this output. We have successfully design the Class diagram showing above. There are six queries related to this database. So we have designed this database queries. Each query has description and screenshots. So this is the conclusion of this case study. So Delphi company is ready to get into the database and have no issue related to number of clients exceeds. Delphi Promotion Company has benefit for making a database. In this assignment main conclusion we have successfully design the database which manage the clients, campaigns process and staff meeting as well. Oracle database is used for whole project. Now Delphi promotion company can added more data as required. It has no problem for exceeding clients and other details.

References:

Loney, K., 2004. Oracle database 10g: the complete reference. London: McGraw-Hill/Osborne.

Kothuri, R., Godfrind, A. and Beinat, E., 2008. Pro oracle spatial for oracle database 11g. Dreamtech Press.

Greenwald, R., Stackowiak, R. and Stern, J., 2013. Oracle essentials: Oracle database 12c. " O'Reilly Media, Inc.".

Price, J., 2004. Oracle database 10g SQL. Tata McGraw-Hill Education.