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

Aim: to give you practical experience in using entity-relationship and relational modelling techniques.

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 Ronda from Express Media to 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 Advertisers database will 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 of orders 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 Media would like to store details related to each advertiser client Advertiser client 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)

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 addition Express Media would also like to store information about their staff in 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 user first 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.

Assignment Submission

You should produce a single .pdf file, created using Word, containing all of the requirements as specified within this document. Your lecturer will provide details of how the file to be submitted within lectures.

The design document should contain:

A completed copy of the AIH Assignment Coversheet.

2. An appropriate title page that includes an acknowledgement  of all students you have spoken to about the assignment.

3. A table of contents and automatically generated page numbers.

4. An entity relation diagram that models the solution which includes:
a. all entities, relationships (including names) and attributes;
b. primary (underlined) and foreign (italic) keys identified;
c. cardinality and participation (optional / mandatory) symbols; and
d. assumptions you have made, e.g. how you arrived at the cardinality / participation for those not mentioned or clear in the business description, etc.
The E-R should be completed using the standards of this course (crow's feet or UML).

5. Relational data structures (shown in Standard Notation Format) that translates your E-R diagram which includes:
a. relation names;
b. attribute names;
c. primary and foreign keys identified; and
d. for each relation the level of Normalisation achieved, and for any not to Third Normal Form, explain why.
e. The data structures should be shown using the standards of this course (crow's feet or UML).

6. A relational database schema that translates your relational data structures which includes:
a. table names,
b. column names and data types (for MySQL or Oracle)
c. primary and foreign keys identified

7. Write a page that describes your experience designing the database. You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in the project specification. Include an acknowledgement of all students you have spoken to about the assignment.

8. A bibliography, in Harvard format, containing all resources used to complete the assignment. If no resources have been used please indicate this appropriately.