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:
Express Media would like to record the following details for the advertising agencies that take 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 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.
The design document should contain:
1.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).
2.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).
3.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
4.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.