SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    0 Post(s)
    0 Thread(s)

    Going MAD someone HELP Please

    My project is a bit hard to explain...i have attched a copy of it.

    I need to create a EAR model of a company!

    the specification has been stated in the document.

    Any help is very greatful

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

    It is important for Whitespace to keep track of the campaigns they are running for clients. A campaign will involve a number of costs in its production. Without accurate information of these activities, it is difficult for Whitespace to invoice clients effectively. In particular Whitespace has found that it is not keeping accurate track of a campaign and consequently it is not confident about the accuracy of its invoicing of 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.

    Recently Whitespace invested in a Local Area Network and each member of staff now has a PC on their desk. The network is primarily used in development work on campaigns. Software tools such as Dreamweaver and Flash are used to prototype a client’s requirements. However the management realise that the network would also provide the company with the opportunity to do something about its record keeping and would like a database system developed to keep track of Whitespace’s campaigns.

    Information Requirements
    As previously mentioned, Whitespace 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 client’s 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 and telephone number. Whitespace 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 Whitespace.

    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 Whitespace staff. Staff may work on more than one campaign at the same time.

    Staff complete daily timesheets which show, in 15 minute intervals, how their time is applied. Each staff member decides if the activity they record is chargeable. Any chargeable time must be charged to a specific campaign. This enables accurate costing of staff time which can be charged to campaigns. A weekly summary of the information submitted on the timesheets is shown in Figure 1.

    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 record their time spent on the campaign on the timesheet so that the client can be invoiced. 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 2.

    All campaigns consist of adverts. Each advert within a campaign has an advert code (e.g. A111) for filing and record-keeping purposes. These adverts are either newspaper, magazine, radio, television, cinema, poster or leaflet adverts. 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 type is made up of a range of components which can be classified by type, such as photographs, text, and so on. Figure 3 provides a breakdown of component types required for the production of each advert type. Obviously not every advert of a particular type will contain every component for that type. 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 component types for all the adverts are recorded as complete.

    In the production stage the actual adverts, poster proofs, video footage, TV adverts, newspaper proofs, leaflets, and so on that have been agreed with the client in the design stage can be created. The cost details recorded in the database will be the total cost of each component type used in each advert. So, the costs of the components of a specific radio advert, A13, might be recorded in the database as £375 music, £900 voice actors, £600 sound recording. This cost information is entered when the component production work is finished and the completion date is noted.

    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 4. 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 as the costs will differ for each.

    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. A client is only billed for a placement if the purchasing assistant has signed it off to say that it appeared.

    At the end of a campaign an internal report showing all costs incurred for the campaign, staffing, resources, outside agencies, and so on is produced for the campaign manager and account managers. An additional report showing initial cost estimates, campaign details, deadlines and sign-off related to campaign progress is produced for the account manager.

    The billing of clients and the management of their payments is handled by a separate record-keeping system. However, the campaign records should include details of when an invoice was issued and when the final payment has been made. The invoice details should include the invoice number, date issued, amount and who authorised the issue of the invoice (usually the account manager for the campaign or a director).

    Once payment has been received for a campaign, the campaign is recorded as being closed and is signed off by the campaign manager. Six months after the campaign is closed, the librarian can archive the associated materials.

    System Requirements
    Whitespace require a database to support their key activities in a campaign, this 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 spend on these activities and whether it is chargeable to a particular campaign
    • Client details including contacts for particular campaigns
    • Campaign details
    • Advert and placement details
    • Invoice information

    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 costs and dates associated with a campaign.

    Coursework Requirements
    Design Stage
    Design an EAR model that will capture the data, relationships and constraints 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. You are not required to reproduce any particular formatting of the figures or to consider the implementation aspects of EDI.
    You need to prepare an EAR Data Model including the E-R Model, Entity Definitions, Constraints and Assumptions.

    End of Design Stage
    Your model should be ready for the workshops in the week beginning February 28th. Your work will be peer marked and you will receive feedback from this process that you can choose to incorporate into your model.

    Implementation Stage
    • Create a set of relational headings from your EAR model, i.e. the entity definitions with foreign keys added to replace the relationships from the EAR model together with any design changes that were made during the transition from EAR to the relational headings.
    • Use the relation headings to create an SQL database using the notes from the workshop (posted on Blackboard as Database Implementation in the Workshops folder).
    • 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. You need to create and run SQL queries that produce the information required. You do not need to consider the formatting of the data.

    Demonstration Deadline
    The database should be ready for demonstration in the week beginning April 25th although there will be some optional dates for demonstration available in the weeks beginning April 11th and April 18th.

    The Final Report
    The design and implementation stages will be undertaken as a team exercise where you will be working in pairs. The final report will be an individual piece of work and will document the activities you have undertaken. It requires the following chapters:

    Chapter 1: Introduction – one page description of the aims and objectives of the report
    Chapter 2: The EAR Data Model. The E-R model, entity definitions, constraints and assumptions plus a one-page discussion on the major design decisions taken in the construction of the model.
    Chapter 3: Implementation. Provide a listing of the relation headings and a listing of the SQL table definitions and a one-page discussion of the design decisions taken in implementing the database.
    Chapter 4: Conclusion. A critical evaluation of your final product and a review of the entire exercise.

    Figure 1: Timesheet Weekly Summary

    Week Beginning: 02/08/2004 Name: Robert Silverside
    Submitted: 09/08/2004 Employee Reference No: 00312
    Activity Description Campaign Code Chargeable (Y/N) Mon Tue Wed Thur Fri Total
    Holiday N 8.00 8.00
    Photo Shoot C105 Y 0.45 0.45
    Internal campaign review C016 Y 3.00 3.00
    Design confirmation meeting C109 Y 2.00 2.00
    Design final meeting C110 Y 2.15
    Script development C111 Y 1.15
    Script development C111 Y
    Client meeting C111 Y
    Proof reading C111 Y 2.15
    Client discussion (phone) C120 Y
    Client discussion (meeting) C120 Y
    Portfolio presentation C120 Y
    Preliminary meetings C125 N 2.00 1.30
    Preliminary draft report C125 N 2.00
    Campaign outline development C125 N 2.30

    Figure 2: Campaign Summary
    (summary form used to confirm the details of the campaign to be developed)

    Figure 3: Advertisement Components

    Newspaper Magazine Radio Television Cinema Poster Leaflet
    Copyright text (inc scripts) √ √ √ √ √ √ √
    √ √ √ √ √ √
    √ √ √ √ √ √
    √ √ √
    √ √
    √ √ √ √ √ √
    ‘Voice’ actors
    √ √ √
    Sound recordings √ √ √

    Figure 4: Placement-Location Record Card

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    New York City
    0 Post(s)
    0 Thread(s)
    What's your biggest problem? You dont' know what a database is? You don't know what modeling is all about? You need help with defining relationships?

    Nobody, and I mean nobody, is going to read that huge document and give you a project to turn in to your boss.


  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Toronto, Canada
    63 Post(s)
    3 Thread(s)
    dan, that's not for a boss

    that's a homework assignment | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts