Aim: To give you practical experience in with database modelling, development and writing SQL statements.
Background Information
Express Media will be starting operations in 2018. They require a database system to cater for their magazine advertising orders.
The database will assist sales staff with maintaining a record of advertisement order details and bookings that flows through to invoicing and allow managers to produce reports on sales revenue and sales history.
The database will store contact details for advertisers including the 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).
The advertiser's database will store the issue order date, purchase order number, initials of the sales representative handling the order, special instructions, and copy notes. For each order, the database will store order details including order ID, invoice date, magazine issue description, cost price, page size, shape, colour, position, and production details.
Payment information for advertising orders will 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), navment method (where Payment method may he cash cheaue.
The database system also needs to keep a record of database users (note that not all staff are database users), advertising agencies, and suppliers.
Assumptions
Some advertisers engage the services of an advertising agency that handles advertising on behalf of the advertiser and charges a percentage commission fee, which is usually set at 10%.
System Requirements
The system is a prototype system and as such is not a full production version. You will be required to enter a representative sample data into your tables in order to test the design and operation of year database. You are required to import the sample data provided in the excel file into your tables and you are required to enter at least two new records of your own in some of the tables.
Project Specification 1. Part A
You are provided an Excel file that contains a partial ERD, suggested table definition, and some sample data. See Advertisers_Data.xlsx
Use the Excel workbook file Advertisers _Data.xlsx to perform the following tasks.
1. Your first task is to study the sample data and determine appropriate data definitions. Check that the spreadsheet data has been normalized to third normal form (3NF).
Study the partial ERD on the first sheet that provides a suggested schema.
The file has various other worksheets including:
- Advertisers
- Agencies
- Orders
- Order Details
- Page Size
- Payments
- Payment Methods
- Remarks
- Suppliers
- Staff
- Users
2. Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.
Your entity relation diagram that models your database design should:
a. Include all entities, relationships (including names) and attributes.
b. Identify primary and foreign keys.
c. Include cardinality/ multiplicity and show using crow's feet or UML notation.
d. Include participation (optional / mandatory) symbols if applicable.
The E-R should be created as part of a Microsoft Word document. Hand-drawn diagrams will not be accepted. It is recommended that you complete your ERD using Visio or (Search for ERD glitfy to get started.).
3. Using MySQL, you are required to develop a demonstration prototype system that handles loan servicing. Use MySQL to create a new database called ELP. Create tables according to your ERD. Follow a standard naming convention for table names and field names. Avoid using spaces and any special characters in table and field names. Use underscore_case or use camelCase to separate parts of a name.
a. Create relationships between tables and enforce the referential integrity as shown below.
Relationships:
- Advertisers can have one or more orders.
- Advertisers can nominate an advertising agency that handles orders on behalf of the advertiser.
- An order can include advertising order details for multiple publications.
- Each advertiser record may require one or more notes so as to keep a history of information related to communication with the advertiser.
- Notes may be assigned to a particular staff person (or database user) to follow up.
- An order may have one or many payments and each payment is identified as to the payment method.
b. The database should include suitable validation and integrity checks as well as appropriate referential integrity checks. That is, AS A MINIMUM, your system should ensure that the following events cannot occur:
Referential Integrity Constraints:
- An order record cannot be entered for an advertiser that does not exist.
- An advertiser cannot be deleted for which an order has been recorded. Similarly, an advertiser cannot be deleted once remarks have been entered for the advertiser record. Likewise, staff (users) cannot be deleted once staff persons have been assigned to follow up a note.
- An order cannot be deleted once the order has order details associated with it.
- Payment methods cannot be deleted once payment methods have been recorded against payments and orders that have matching payment details cannot be deleted once payment records have been entered.
c. Save the data in the Excel file provided in a CSV file format and import the data into your tables in MySQL.
i. Save a copy of Advertisers_Data.xlsx as Advertisers_ERD.xlsx and on each sheet, delete the definition and arrange the data so that the sample data appears immediately below the column headings. Position the data for each table starting from cell At.
ii. Import your normalised data from Excel into your tables. Save your data in Excel in a CSV file format. Select your table in MySQL, click the Operations tab and then import the data from the CSV file. Refer to the document titled Import CSV into MySQL to learn how to save in a CSV format and import into MySQL.
d. Add at least two new records into the appropriate tables to include your details as a customer, rental details of your own, and notes details related to your customer record.
2. Part B
Use the Express Media (EM) database that you created in MySQL to design and execute SQL queries that answer the following questions.
Number your answers to each question clearly. The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database. The output includes the records that are listed and also the message that appears when you run the SQL statement.
1. List the company name, first name, last name (join contact first and last name with a space in between and use the alias Advertiser Contact Name for the column heading). Filter the output to include only those advertisers that have an advertising agent that handles advertising on behalf of the advertiser. Sort the output in ascending order by the advertiser last name.
2. List the Order ID, Unit Price, and Production for all order details where the page size is Full Page and the Unit Price is greater than zero.
3. List the total amount owing (which is the sum of the unit price, production, and GST) for each advertiser grouped by the advertiser's company name. Use the alias "Total Amount" for the sum of the amount owed. Sort the output in descending order by the total amount owning. Note that this query does not need to take payments into account.
4. List the total payment amount grouped by payment method for payments made by Visa or MasterCard.
5. List the advertiser contact last name, first name, mobile, and email for all advertisers that do not have a mobile phone number recorded in the advertisers table. Sort the output in ascending order by the last name, and then first name.
6. List the supplier name for all suppliers that have a supplier name that has the word 'courier' anywhere in the supplier company name. Sort the output ascending order by the supplier name.
7. List the user first name, surname, and remarks from the notes table for all notes that have a follow up date before today's date and where the complete field has a value of 'False'.
8. Sum the page size for all advertising placed between 1-Jul¬2017 and I5-Jul-2017. Output should include the company name labelled as "Advertiser Name" and the total page size which is labelled "Total Page Size". Sort in descending order by the Total Page Size.
9. Count the number of staff grouped by employment type. Use the alias "No of staff" for the count
10. List the agency name, advertiser name, first name, last name, and business phone for all advertisers that are managed by the agency named Media Communications. Sort in order of the advertiser name.
11. List the advertiser company name, and first & last name combined as "Contact Name" for all advertisers that do not have any advertising orders entered in the database.
12. List the company name for all advertisers who have placed orders for advertising but not paid in full. Calculate the amount owing. Use the alias "Amount Outstanding" for the amount owing. Hint You will have to create a number of queries to calculate (i) the amount owning, (ii) the amount paid, and (iii) the amount owing. Use the first two queries as inputs for the third query. Your first query that sums the amount paid can be based on the payments table only and grouped on the Order ID and the second query that sums the amount owning (which includes the sum of the Unit Price, Production, and GST) can be based on the order details table and grouped on the order ID. The final query that includes the first two queries will also need to include the advertiser table.
3. Part C
1. Write a page that describes your experience building 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 Part A Question 4. Include an acknowledgement of all students you have spoken to about the assignment.
4. Part D
1. Deliverables for Parts A, B, & C must be printed as a report with a cover sheet attached. Your report must include headers and footers that include your name, student number, unit name, assignment name, and page numbers. Your report must be checked for spelling and grammar. Your report must also be formatted so that it is well set out and easy to read.
a) A soft copy of your assignment documentation report must be zipped and uploaded to Moodle.
b) The SQL that can be used to restore your database should also be uploaded to Moodie. You can create the SQL for your database as follows:
Use the mysqldump command to create a text version of the database. Use mysqldump to create SQL file that contains a list of SQL statements which can be used to restore/recreate the original database.
Attachment:- Submission and CSV in Excel.rar