Develop Database System For I Design Advertising Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

I-Design Advertising Company arranges advertising campaigns which deal with designing, printing and publishing different advertising types such as video, audio, text, graphics and animation.

The Company currently stores all the data of the campaigns on a spreadsheet, which are huge, difficult to manage and share between the company employees. As a result, too many errors and mistakes appeared in the company work. The employees reported these issues to IT department to get a solution.

As you are working in the IT department your manager assigned you to investigate these issues and identify he company requirements. After completing your investigation you found that the company requires a database system to keep track of their current and future advertising campaigns. For each campaign the company needs the following details:

1. The employee's record:

Employee Names

Employee Number (in the range E000 to E999).

Job Title (job title is one of project manager, secretary, audio developer, video developer, banner designer, poster &rollup stand designer or brochure &flyer designer).

Employee Salary (in the range 7000 AED to 40000 AED).

2. Campaign record includes:

Campaign Title

Campaign Code (with values in the range C1000 to C9000) to uniquely identify the project.

Official Campaign Start Date.

Each Campaign must have one employee who is the leader (who must have the Job Title "Project Manager) and can have several employees who work on the project;

The leader must be recorded. An employee who leads a campaign cannot also be recorded as working on the campaign, and cannot lead on another campaign.

An employee can work on several campaigns at the same time.

3. Each campaign consists of a number of activities. Each activity record includes:

activity identification

A combination of the Campaign Code, and the activity Code within the campaign.

Activity Type

Banner Design

Banner Printing

Rollup stand design and printing

Brochure Design and Printing

Flyer Design and Printing

Poster design

Poster Printing

Radio Advertisement

TV Advertisement

Newspaper Advertisement

The company classifies each activity with a set of common activity types. An activity type is given a classification of casual, critical, optional or essential. Each classification of activity type has a minimum and maximum number of person hours and a quality level (values range from 1 to 4 ) that applies to all activities given that activity type as follow:

Classification type

Minimum number of hours

Maximum number of hours

Quality level

















Each activity must be part of a single campaign.

Each activity is given an activity type at the campaign start.

The Expectation Start Date for that activity.

The Expectation Finish Date for that activity validated as being on or after Expectation Start Date for that activity.

The Actual Start Date for that activity validated as being on or after Expectation Start Date for that activity.

The Actual Finish Date for that activity validated as being on after Actual Start Date for that activity.

4. The Company requires a database system that has the ability to:

Permit new campaign to be added and existing ones to be amended if required.

Calculate the number of days per activity (both maximum and minimum)

The number of people per campaign at a time.

The system should produce a report showing the campaign plan with all relevant dates, classifications and the type of employee per activity.

Task 1(P1, M1, D1)

Write a Memo to your manager describing the purpose of relational database.(P1-a)

Draw the ERD (Entity Relationship Diagram) to accurately represent the set of company requirements and carry out normalisation on these requirements. (P1-b)

Determine the type and size and properties of all attributes (fields).(P1-c)

Identify keys field on the ERD, then explain the purpose of Primary and Foreign keys and how they are used to build the relationships between tables.(M1-a)

Explain referential integrity and show print screen of the mechanisms that exist to support the enforcement of referential integrity within a database package software.( M1-b)

Identify and explain potential errors in database design and construction. Then explain how these types of errors can be avoided. (D1)

Task 2 (P2, P3, M2, M4)

Create a relational database which meets the company needs.(P2)


Your solution should have at least five tables.

The database must have working relationships which will allow queries to be run using multiple tables and criteria.

Provide print screen for all your work.

Design and implement employee and activity forms and add validation rules where it is needed according to the company requirements, in order to reduce user error, and to speed up data input and retrieval.(P3)


Your solution should have data dictionary.

Provide print screen for all your work.

Implement consistent and appropriate styling in the design and construction of a database reflecting the specific company requirements by using: (M2)

Company logo on all the forms

Same background colour with black text displays

Text size will be easy readable

Validation rules where needs according to the company requirements

Input mask for these fields Employee Number, Employee Salary and, Campaign Code

Drop down menu for these fields Job Title, Activity Type and Quality level.

Add suitable message boxes to prompt users when actions are required.


Provide print screen for all your work.

Explain how records and related records are deleted to ensure the integrity and consistency of the database is maintained by demonstrate the positive and negative outcomes of enforcing and relaxing referential integrity. Then add a delete tab which enables the user to delete specific campaign details. (M4)


Provide print screen for all your work.

Task 3 (P4, P5, P6, M3)

Guidance: Provide print screen for all your work of this task.

You have presented your work to your manager, who found the database system needs to store information about clients. You are asked to import this data into an additional table. The data is currently held on a separate spreadsheet (P4-a)

Client's details







AL Ahlam Land Trade company




[email protected]

Dubai 46958

Al Raway Furniture Company




[email protected]

Sharjah 21479

Thinc College




[email protected]

Dubai 36885

White Teeth Clinic




[email protected]

Dubai 38781

You have identified from the user requirements to generate a report to show activity records to be sent to a word-processed document to form part of a company report.(P4-b)

Modify your database to include this additional information. You must ensure that you choose a suitable primary key and link this new table to an appropriate table within your current system. Then test this new addition to ensure that the integrity of your database has not been compromised. (P5)

The company needs to generate a report to see which campaign will start between 1 and 30 August 2009.The report also show the classification and quality codes for the specified Campaign. They will also need a query to identify which member of the team works on which campaign. This will be a separate query using different tables from within the database. (P6)

The company wants to have a report that allows employees to select a particular client and highlight all the campaign details he/she had with the company.(M3)

Task 4 (D2, D3)

Evaluate the relational database system you developed against the list of the company requirements given to you. (D2)


Evaluate the database system against its success in meeting user needs by discuss strengths, weaknesses and also should give an overall conclusion on whether the database has met user requirements. .

You have been informed that at least one member of the company team has a visual impairment and will need to have custom forms and toolbars created to allow them to use the database as effectively as the other team members. Write a report to identify and explain what will you change in the database system to enable all the staff to make use of it.(D3)


Focus on the practical aspects of streamlining an interface so that navigation and control of the database is as simple and robust as possible for the impaired user.

Need to include evidence of macros and customised tool bars which could be used to meet the requirements of a visually impaired user.