Assignment  - Database Modelling &Development of an ER Diagram and Schema

Course Objectives:

This assessment task focuses on the following course objective:

Use entity-relationship diagrams to design a relational database.

Project Specification

You have been commissioned by RondafromExpress Mediato design a database to assist them with managing their magazine advertising orders. Express Media is a publishing company that sells advertising space to clients in magazines that are published every two months. You are only required to provide the design of the database at this time.

The Advertisersdatabasewill assist sales representative staff to maintain a record of advertisement order details and bookings that flow through to invoicing. The system will also allow managers to produce reports on sales revenue and sales history.

The main purpose of the database is to keep track oforders for advertising space. For advertising orders they would like to store the order date, purchase order number, initials of the sales representative handling the order, special instructions, and copy notes. For each order Express Media would like to store the order ID, invoice date, magazine issue description, cost price, page size, shape, colour, position, and production notes.

Express Media would also like to identify magazine issues in terms of publication name, issue year, and issue month.

Express Media would like to keep a record of payments for orders. Payments can be made in full or a number of payments may be made against an order.

Payment information should include the following: payment amount, payment date, cheque number, credit card details (which include credit card type (for example Visa, American Express, & Diners Club), credit card number, credit card name, and credit card expiry month and year), payment method (where payment method may be: cash, cheque, direct debit, CC Debit, or Direct Credit, and Other). Express Media would like to maintain a list of payment types to produce a report that summarises payments for each type.

Express Mediawould like to store details related to each advertiser clientAdvertiserclient details should include a company name, website address, business phone number, fax number, advertising contact details (including first name, last name, telephone, & fax number),and address of premises/offices (including street name, city, state, and post code).Express Media would also like to keep a record of the following for each advertiser:

• Enquiry / Editorial Address (including street address, city, state, and postcode)

• Invoicing Address (including street address, city, state, and postcode)

• Design agency (company name, contact name, phone, & email address)

• Advertising agency name

Express Media would like to record the following details forthe advertising agenciesthattake care of advertising needs for some of the advertisers. The agencies are companies or consultants that are hired by the advertisers to design and place advertisements on the magazines that Express Media publishes. For each advertising agency they would like to store their business name, contact name, address details (including their street address, city, state, and postcode), and phone numbers (mobile, business, fax, and other). Express Media would like to be able record the percentage commission that is paid to the advertising agency for those advertisers that are handled by an agency.

Express Media would also like to maintain a list of suppliers which provide services such as print design and production. Supplier details should include the following: company name, contact name & title, address (including street address, city, state, & postcode), phone number, web address, email address, and a comment.

In additionExpress Mediawould also like to store information about their staffin the database. Staff may be employed full-time, part-time or on a casual basis. Express Media needs to store contact information for the staff (address and phone), along with their Tax File Number (TFN). Some staff members are supervisors of other staff members and this also needs to be identified. Express Media would also like to store up to three email addresses for each staff member. The database should be designed so that only HR staff have access to confidential details such as the TFN & salary.

Express Media staff who work in HR and sales have access to this database and they will be uses of the database. Express Media would like to store information regarding their database users. For users they would like to store the userfirst name& last name, login name & password, email address, and user level (which specifies which level of access privileges the user should have).

Express Media understands that they may not have provided you with sufficient information. If you need to make assumptions about their organisation please ensure that you record these.