Custom essays, dissertations, coursework & essay help from the UK's original custom essay writing services provider
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 of a relational database for Whitespace Promotions. The latter includes the recording of:
. Staff details and their involvement in campaigns including the recording of the activities that staff have been involved in, the amount of time spent on these activities and whether it is chargeable to the particular campaign
. Client details including contacts for particular campaigns
. Campaign details
. Advert and placement details
. Invoice information
The first chapter of this report is the introduction. The second chapter will describe the creation of the EAR model. That is, the E-R model, entity definitions, constraints and assumptions. The latter will be based on the information requirements analysis. The second chapter will describe the relational model which will be derived from the E-R model. Chapter four will focus on the implementation and will provide a listing of the SQL table definitions. Finally, chapter five will conclude by providing a critical evaluation of the final product and a review of the entire exercise.
Chapter 2: The EAR Data Model
In order to create the EAR model, first of all we have to identify the entities that will take part in the model. According to the information requirements, Whitespace Promotions requires a database to support their key activities in an advertising campaign. The purpose of the requested data is essentially the recording of the information necessary to track costs and dates associated with a campaign. According to the requirements, a list of entities is extracted:
. STAFF
. CLIENT
. CAMPAIGN
. ADVERT
. ACTIVITY
. PLACEMENT
. COMPONENT
. FOLDER
. INVOICE
Assumptions
In order to create the EAR diagram, a number of assumptions have to be made. These assumptions are very important in defining the relationships between the entities that will comprise the database model. Below, we list the relationships between the entities based on assumptions.
CLIENT-CAMPAIGN: Each client can be involved in a number of campaigns. Each campaign is unique for the particular customer. For Whitespace productions each client is involved in at least one campaign. Therefore, the relationship between the client and the campaign entity is one-to-many.
STAFF-CAMPAIGN: Members of staff can participate in zero, one or many campaigns. A campaign involves at least one member of staff. Therefore the relationship between the entities staff and campaign is many-to-many. In this relationship the participation of the entity staff is mandatory whereas the participation of the entity campaign is optional. In order to convert this relationship into 2 1:N relationships, we connect 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 staff is involved in one or more activities. Each activity is performed by a specific member of staff. Therefore the relationship between the entities staff and campaign is one-to-many.
ACTIVITY-CAMPAIGN: Each activity can be a part of one or many campaigns. A campaign consists of many activities. Therefore the relationship between the entities activities and campaign is many-to-many. In this relationship the participation of both entities is mandatory. A campaign consists of at least one activity and an activity has to belong to a certain campaign. In order to convert this relationship into 2 1:N relationships, we connect each entity with a new entity that represents the 1:N-N:1 relationship. The new entity is called TIMESHEET.
CAMPAIGN-ADVERT: Each campaign consists of adverts. Each advert is unique for a particular campaign, since its content is associated with a specific product and thus with a particular campaign. In addition to that, each campaign must consist of at least one advert. Therefore, the relationship between the entities campaign and advert is many-to- one with a mandatory participation of the entity advert.
ADVERT-COMPONENT: Each advert is made up of a number of components. Not every advert of a particular type will contain every component of that type. Each component may participate in many adverts and there 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 with a mandatory participation of the entity component.
ADVERT-PLACEMENT: Each advert may have more than one placement. Each placement is unique for the particular advert. Therefore the relationship between the entities advert and placement is one-to-many with the mandatory participation of placement since an advert has to be displayed at least in one placement.
COMPONENT-FOLDER: Each folder contains many components. Each component is a part of a specific folder. The entity folder represents the work in progress. The relationship between the entities folder and component is one-to-many. The participation of both entities in the relationship is mandatory. A folder must contain components and each component must belong to a folder.
CAMPAIGN-INVOICE: Each campaign is assigned a unique invoice. Therefore the relationship between these two entities 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 relationships was not supported. Therefore, the entity which has a mandatory participation in the 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, sports events}
AdvertSize {full page, half page, A4 leaflet, A5 leaflet, 30 sec for media}
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 that many-to-many relationships are decomposed into two one-to-many relationships.
Chapter 3: The Relational 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
Computer Science Essays - Find your free computer science essays...
We have a large assortment of free essays available to use as research material. Visit our computer science essays from our free essays section.
UK Essays Sitemaps
UKEssays.com RSS Feed
