You are here: UK Essays » Essay Examples » Computer Science Essay Examples » Design Creation Relational Database

Cookie Information

Privacy Information

Example Computer Science Essay

Describe the design and the creation of a relational database.

Design Exercise: Whitespace Promotions

The aim of this report is to describe the design and the creation ofa relational database for Whitespace Promotions. The latter includes therecording of:

.        Staff details and their involvement in campaignsincluding the recording of the activities that staff have been involved in, theamount of time spent on these activities and whether it is chargeable to theparticular campaign

.        Client details including contacts for particularcampaigns

.        Campaign details

.        Advert and placement details

.        Invoice information

The first chapter of this report is the introduction. The secondchapter will describe the creation of the EAR model. That is, the E-R model,entity definitions, constraints and assumptions. The latter will be based onthe information requirements analysis. The second chapter will describe therelational model which will be derived from the E-R model. Chapter four willfocus on the implementation and will provide a listing of the SQL tabledefinitions. Finally, chapter five will conclude by providing a criticalevaluation of the final product and a review of the entire exercise.

Chapter 2: TheEAR Data Model

In order to create the EAR model, first of all we have to identifythe entities that will take part in the model. According to the informationrequirements, Whitespace Promotions requires a database to support their keyactivities in an advertising campaign. The purpose of the requested data isessentially the recording of the information necessary to track costs and datesassociated with a campaign. According to the requirements, a list of entitiesis extracted:

.        STAFF

.        CLIENT

.        CAMPAIGN

.        ADVERT

.        ACTIVITY

.        PLACEMENT

.        COMPONENT

.        FOLDER

.        INVOICE

Assumptions

In order to create the EAR diagram, a number of assumptions have tobe made. These assumptions are very important in defining the relationshipsbetween the entities that will comprise the database model. Below, we list therelationships between the entities based on assumptions.

CLIENT-CAMPAIGN: Each client can beinvolved in a number of campaigns. Each campaign is unique for the particularcustomer. For Whitespace productions each client is involved in at least onecampaign. Therefore, the relationship between the client and the campaignentity is one-to-many.

STAFF-CAMPAIGN: Members of staff canparticipate in zero, one or many campaigns. A campaign involves at least onemember of staff. Therefore the relationship between the entities staff andcampaign is many-to-many. In this relationship the participation of the entitystaff is mandatory whereas the participation of the entity campaign isoptional. In order to convert this relationship into 2 1:N relationships, weconnect each entity with a new entity that represents the 1:N-N:1 relationship.The new entity is called TEAM.

STAFF-ACTIVITY: Each member of the staffis involved in one or more activities. Each activity is performed by a specificmember of staff. Therefore the relationship between the entities staff andcampaign is one-to-many.

ACTIVITY-CAMPAIGN: Each activity can bea part of one or many campaigns. A campaign consists of many activities.Therefore the relationship between the entities activities and campaign ismany-to-many. In this relationship the participation of both entities ismandatory. A campaign consists of at least one activity and an activity has tobelong to a certain campaign. In order to convert this relationship into 2 1:Nrelationships, we connect each entity with a new entity that represents the1:N-N:1 relationship. The new entity is called TIMESHEET.

CAMPAIGN-ADVERT: Each campaign consistsof adverts. Each advert is unique for a particular campaign, since its contentis associated with a specific product and thus with a particular campaign. Inaddition to that, each campaign must consist of at least one advert. Therefore,the relationship between the entities campaign and advert is many-to- one witha mandatory participation of the entity advert.

ADVERT-COMPONENT: Each advert is made upof a number of components. Not every advert of a particular type will contain everycomponent of that type. Each component may participate in many adverts andthere has to be at least one component that belongs to the advert. Therefore,the relationship between the entities advert and component is many-to-many witha mandatory participation of the entity component.

ADVERT-PLACEMENT: Each advert may havemore than one placement. Each placement is unique for the particular advert.Therefore the relationship between the entities advert and placement is one-to-manywith the mandatory participation of placement since an advert has to bedisplayed at least in one placement.

COMPONENT-FOLDER: Each folder containsmany components. Each component is a part of a specific folder. The entityfolder represents the work in progress. The relationship between the entitiesfolder and component is one-to-many. The participation of both entities in therelationship is mandatory. A folder must contain components and each componentmust belong to a folder.

CAMPAIGN-INVOICE: Each campaign isassigned a unique invoice. Therefore the relationship between these twoentities is one-to-one with a mandatory participation of the entity invoice.

The EAR diagram was created using the DDS (Database Design Studio)Lite program. In this software a symbol representing mandatory relationshipswas not supported. Therefore, the entity which has a mandatory participation inthe relationship is colored grey.

Entity attributes

STAFF (StaffNumber, StaffName, Role,SalaryGrade, ChargeOutRate, TelephoneNumber, FaxNumber, Email)

Domains

Role {account manager, campaign manager, graphic designer }

CLIENT (ClientCode, ClientName)

CAMPAIGN (CampaignCode, CampaignName,CampaignManager, Contact1, Contact2, InvoiceNumber)

Domains

ComponentType {photographs, texts, music, actors }

ADVERT (AdvertCode, AdvertType,AdvertDescr, AdvertTargetAudience, AdvertSize)

Domains

AdvertType {newspaper, magazine, radio, television, cinema, poster,leaflet adverts}

AdvertTargetAudience {financial newspapers, children's TV, sportsevents}

AdvertSize {full page, half page, A4 leaflet, A5 leaflet, 30 sec formedia}

COMPONENT (ComponentCode, ComponentType,ComponentCost)

Domains

ComponentType {photographs, texts, music, actors }

FOLDER (FolderReference,CampaignNumber, AdvertCode, ComponentCode, CompletionDate, Cost)

PLACEMENT (PlacementCode,PlacementType, ComponentCost)

Domains

PlacementType {TV time, radio time, newspaper, magazine }

INVOICE (InvoiceNumber,DateIssued, InvoiceAmount, AuthorisedBy, PaymentDate)

E-R Diagram

Figure 1 shows the diagram for the Whitespace Promotions Database.In this figure, many-to-many relationships are also displayed.

Figure 2 presents the same diagram with the only difference thatmany-to-many relationships are decomposed into two one-to-many relationships.

Chapter 3: TheRelational Model

The relational model derived from the above E-R model is as follows:

STAFF

StaffNumber numeric

StaffName string

Role string

SalaryGrade numeric

ChargeOutRate numeric

TelephoneNumber numeric

FaxNumber string

Email string

CLIENT

ClientCode numeric

ClientName string

CAMPAIGN

CampaignCode numeric

CampaignName string

CampaignManager string

Contact1 string

Contact2 string

InvoiceNumber string

ADVERT

AdvertCode

AdvertType

AdvertDescr

AdvertTargetAudience

AdvertSize

COMPONENT

ComponentCode

ComponentType

ComponentCost

FOLDER

FolderReference

CampaignNumber

AdvertCode

ComponentCode

CompletionDate

Cost

PLACEMENT

PlacementCode

PlacementType

ComponentCost

INVOICE

InvoiceNumber

DateIssued

InvoiceAmount

AuthorisedBy

PaymentDate

Chapter 4: Implementation

CREATE TABLE STAFF

CREATE TABLE INVOICE

CREATE TABLE CAMPAIGN

CREATE TABLE ADVERT

CREATE TABLE ACTIVITY

CREATE TABLE COMPONENT

CREATE TABLE PLACEMENT

>> Back to the custom essays section...

Sign up and be the first to receive our latest offers: