Tasks For Normalizing And Modeling Advertisers_Data.xlsx Database

Task 1: Normalizing the Data

+—————+————–+——+—–+———+——-+

+—————+————–+——+—–+———+——-+

| AdvertiserID | int(11) | NO | PRI | NULL | |

| PostalCode | int(4) | NO | | NULL | |

| MobileNumber | int(15) | YES | | NULL | |

| AgencyId | int(11) | YES | MUL | NULL | |

+—————+————–+——+—–+———+——-+

+——————+————-+——+—–+———+—————-+

+——————+————-+——+—–+———+—————-+

| AgencyId | int(11) | NO | PRI | NULL | auto_increment |

+——————+————-+——+—–+———+—————-+

+————–+————-+——+—–+———+—————-+

+————–+————-+——+—–+———+—————-+

| CopyStatusId | int(11) | NO | PRI | NULL | auto_increment |

+————–+————-+——+—–+———+—————-+

+———–+————-+——+—–+———+—————-+

Task 2: Creating an Entity Relationship Diagram

+———–+————-+——+—–+———+—————-+

| IssueId | int(11) | NO | PRI | NULL | auto_increment |

| Year | int(11) | NO | | NULL | |

+———–+————-+——+—–+———+—————-+

+————–+————–+——+—–+———+——-+

+————–+————–+——+—–+———+——-+

| RemarkID | int(11) | NO | PRI | NULL | |

| AdvertiserID | int(11) | NO | MUL | NULL | |

| UserID | int(11) | NO | MUL | NULL | |

+————–+————–+——+—–+———+——-+

+——————+—————+——+—–+———+——-+

+——————+—————+——+—–+———+——-+

| OrderDetailID | int(11) | NO | PRI | NULL | |

| OrderID | int(11) | NO | MUL | NULL | |

| IssueId | int(11) | NO | MUL | NULL | |

| UnitPrice | int(11) | NO | | NULL | |

| GST | decimal(5,2) | NO | | NULL | |

| Year | int(11) | NO | | NULL | |

+——————+—————+——+—–+———+——-+

+——————–+—————+——+—–+———+——-+

+——————–+—————+——+—–+———+——-+

| OrderId | int(11) | NO | PRI | NULL | |

Conclusion

| ClientID | int(11) | NO | MUL | NULL | |

| UserID | int(11) | NO | MUL | NULL | |

+——————–+—————+——+—–+———+——-+

+———–+—————+——+—–+———+——-+

+———–+—————+——+—–+———+——-+

+———–+—————+——+—–+———+——-+

+—————–+————-+——+—–+———+—————-+

+—————–+————-+——+—–+———+—————-+

| PaymentMethodId | int(11) | NO | PRI | NULL | auto_increment |

+—————–+————-+——+—–+———+—————-+

+—————–+—————+——+—–+———+——-+

+—————–+—————+——+—–+———+——-+

| PaymentID | int(11) | NO | PRI | NULL | |

| OrderId | int(11) | NO | MUL | NULL | |

| OrderDetailId | int(11) | NO | MUL | NULL | |

| CardAuthorizNo | int(11) | YES | | NULL | |

| PaymentMethodID | int(11) | NO | MUL | NULL | |

+—————–+—————+——+—–+———+——-+

+—————-+————-+——+—–+———+—————-+

+—————-+————-+——+—–+———+—————-+

| StaffID | int(11) | NO | PRI | NULL | auto_increment |

+—————-+————-+——+—–+———+—————-+

+————-+———+——+—–+———+—————-+

+————-+———+——+—–+———+—————-+

| supplierID | int(11) | NO | PRI | NULL | auto_increment |

| PostalCode | int(4) | YES | | NULL | |

| Fax | text | YES | | NULL | |

+————-+———+——+—–+———+—————-

+———–+————–+——+—–+———+—————-+

+———–+————–+——+—–+———+—————-+

| UserId | int(11) | NO | PRI | NULL | auto_increment |

| StaffID | int(11) | NO | MUL | NULL | |

+———–+————–+——+—–+———+—————-+

SELECT * FROM Advertisers limit 5;

+————–+————————————-+———–+———-+——-+—————————————–+—————————–+——-+————+———+—————–+—————–+————–+————————————–+——————————–+———-+

| 1 | 3M Tapes & Adhesive Division | Glenn | Mortimer | | 3M Industrial & Transportation Business | 950 Pacific Highway Pymble | NSW | 2073 | | (02) 9498 9225 | (02) 9498 9247 | 402 | [email protected] | | 1 |

| 3 | Industrial Packaging Systems Pty Lt | Warren | Jennings | | Unit 1/ 10 Southey St | Williamstown | VIC | 3004 | | (03) 9399 8955 | (03) 9399 8655 | NULL | | www.industrialpackaging.com.au | 3 |

| 4 | Siemens Ltd – Industrial Automatio | Garry | Russell | | 885 Mountain Highway | Bayswater | Vic | 3153 | | (03) 9721 7230 | | 419 | | www.siemens.com.au | NULL |

| 5 | Visy Industrial Packaging | Elizabeth | Wilson | | 6 Koornag Rd | Scoresby | Vic | 3179 | | (03) 9765 2690 | (03) 9763 4914 | NULL | [email protected] | ww.visy.com.au | 4 |

SELECT * FROM agencies limit 5;

+———-+———————————+——–+———–+———–+————————————+———————-+—————+——+—————-+————————–+——————+

| 1 | Adcorp Marketing Communications | Mr | Annette | Skinner | Level 16 77 Eagle St | Brisbane | QLD | 4000 | (07) 3229 6111 | [email protected] | 0.1 |

| 2 | Tradewinds Advertising | Mr | Myles | Connell | Level 1, 240 Chapel St | PRAHRAN | VIC | 3181 | (03) 9529 4744 | | 0.1 |

| 3 | Media Communications | Mrs | Rhonda | Dunlop | Level 1, 195 Little Collins Street | Melbourne | Vic | 3806 | (03) 9769-4516 | [email protected] | 0.1 |

| 4 | Breakthrough Creative | Mr | Andy | Jones | 96 Hay Street | Subiaco | WA | 6008 | | [email protected] | 0.1 |

5 rows in set (0.00 sec)

SELECT * FROM copy status limit 5;

+————–+————————+———-+

| CopyStatusId | CopyStatus | JobReqNo |

+————–+————————+———-+

| 1 | Repeat | 1 |

| 2 | Repeat & changes | 1 |

| 3 | New complete on disk | 1 |

| 4 | New complete via email | 1 |

| 5 | New complete + changes | 1 |

+————–+————————+———-+

SELECT * FROM issues limit 5;

+————–+————————+———-+

| CopyStatusId | CopyStatus | JobReqNo |

+————–+————————+———-+

| 1 | Repeat | 1 |

| 2 | Repeat & changes | 1 |

| 3 | New complete on disk | 1 |

| 4 | New complete via email | 1 |

| 5 | New complete + changes | 1 |

+————–+————————+———-+

SELECT * FROM notes limit 5;

+———-+————–+————+——–+—————————————————————————————————————————————————————————————————————————————————-+————–+———-+

| 1 | 1 | 2016-04-28 | 1 | still no answer on advertising. Will discuss at mgmt 3.12.14 Gave me a lead for Keeble Australia—can’t locate.2.11.14 PS says don?t call us we will call you.. Will email my details. Possible meeting next Friday.PD Note: Used to be regular b | | 1 |

| 2 | 1 | 2016-04-28 | 1 | Paul Schulz not optimistic for advertising. Has new system up for patent approval in new fin year.fu late july. | | 1 |

| 3 | 1 | 2016-11-24 | 2 | Update contact details – company has moved. Paul’s mobile is 0400-019 111 | | 1 |

| 4 | 1 | 2016-11-24 | 2 | Paul Schultz decline el for 06 EG – company direction is changing, not worthwhile to spend money until this is clarified. | | 1 |

| 5 | 1 | 2017-08-30 | 3 | Paul Schultz now works for Birrus International. New phone no is 03 9769 2646, Left a message to ring back cl or pd re advertising. Will F/U next week. | 2017-09-06 | 0 |

5 rows in set (0.00 sec)

SELECT * FROM orderdetails limit 5;

+—————+———+———+———–+————+——————+——–+——+————–+——–+———+————-+———-+———+———–+——————+———-+———–+

| 1 | 1 | 5 | 662 | 95.00 | Production Cost | 75.70 | 2017 | Quarter page | Normal | | Full Colour | 0 | | | New to make up | Pag 90 | 0 |

| 2 | 1 | 5 | 662 | 95.00 | Production Cost | 75.70 | 2017 | Quarter page | Normal | | Full Colour | 0 | | | New to make up | Pag 107 | 0 |

| 3 | 2 | 6 | 800 | 0.00 | | 80.00 | 2017 | Quarter page | Normal | | Full Colour | 0 | | | Repeat | Pag 12 | 0 |

| 4 | 4 | 8 | 0 | 0.00 | | 0.00 | 2017 | Enhanced | | | Full Colour | 0 | | | Repeat & changes | | 0 |

| 5 | 5 | 6 | 1480 | 0.00 | | 148.00 | 2017 | Quarter page | Normal | | Full Colour | 0 | | | Repeat | Pag 113 | 0 |

SELECT * FROM orders limit 5;

+———+———-+———-+————+——————+———+——————————————————————————————————————————————————+————+————————————————————————————————————–+——–+

| 1 | 1 | | 2017-07-01 | 0.0 | July 3 | This is a booking for 2 x 1/4 page course advertisements | 0 | Danielle Harris – 02 4921 6067 – [email protected] | 1 |

| 2 | 3 | | 2017-07-02 | 0.0 | Repeat | Tim has agreed to repeat his advert in the July/August edition at the discounted and protected rate of $800 (plus gst). | 0 | Tim Roberts – [email protected] – 03 5521 7699 | 3 |

| 3 | 3 | | 2017-07-08 | 0.0 | | | 0 | | 2 |

| 4 | 1 | | 2017-07-09 | 0.0 | Dec 15 | This is a free enhanced listing in the 2017 edition of Services Guide booked as a make good for miissing picture in Full page ad in Winners booklet. | 0 | Lee-Anne Ryan ph 03 9237 0752, fax 03 9237 0710 – [email protected] | 1 |

| 5 | 5 | | 2017-07-09 | 0.0 | Repeat | Melissa has agreed to repeat her quarter-page advert in the Sept/Oct edition at the protected and discounted rate of $1480 (plus gst). | 0 | [email protected] melissa barrett 03 9720 4235 | 3 |

5 rows in set (0.00 sec)

SELECT * FROM pagesize limit 5;

+————-+———–+

| PageSize | PageValue |

+————-+———–+

| Enhanced | 0 |

| Front Cover | 1 |

| Full page | 1 |

| Half page | 1 |

| Insert | 0 |

+————-+———–+

SELECT * FROM paymentmethods limit 5;

+—————–+——————+————+

| PaymentMethodId | PaymentMethod | CreditCard |

+—————–+——————+————+

| 1 | Cheque | 0 |

| 2 | Direct Debit | 0 |

| 3 | Visa | 0 |

| 4 | American Express | 1 |

| 5 | Mastercard | 2 |

+—————–+——————+————+

5 rows in set (0.00 sec)

SELECT * FROM payments limit 5;

+———–+———+—————+—————+————-+————–+—————-+————+—————-+—————–+———-+

+———–+———+—————+—————+————-+————–+—————-+————+—————-+—————–+———-+

| 1 | 1 | 2 | 833 | 2017-08-20 | | | | 0 | 2 | |

| 2 | 1 | 2 | 833 | 2017-08-28 | | INV 5777 | | 0 | 4 | |

| 3 | 5 | 5 | 1628 | 2017-10-29 | | SO08-5780 | | 0 | 2 | |

| 4 | 7 | 8 | 1210 | 2017-12-03 | | INV SO08-5783 | | 243 | 1 | |

| 5 | 8 | 16 | 3110 | 2017-11-17 | | INV SO08-5791 | | 0 | 2 | |

+———–+———+—————+—————+————-+————–+—————-+————+—————-+—————–+———-+

SELECT * FROM staff limit 5;

+———+———–+———–+——-+———–+—————-+

+———+———–+———–+——-+———–+—————-+

| 1 | Peter | Lagios | Mr | | FT |

| 2 | Charles | Macdonald | | | FT |

| 3 | Cindy | Lynch | | | FT |

| 4 | Jessie | Liu | | | FT |

| 5 | Sandy | Perazza | Ms | | PT |

+———+———–+———–+——-+———–+—————-+

5 rows in set (0.00 sec)

SELECT * FROM suppliers limit 5;

+————+————————+————-+———————+—————-+——–+————+————–+———–+———————+

+————+————————+————-+———————+—————-+——–+————+————–+———–+———————+

| 1 | Kwik Kopy | Anyone | 28-30 Market Street | Sydney | NSW | 2000 | 9290 2566 | 9299 8143 | www.kwikkopy.com.au |

| 2 | Crisis Couriers | Anyone | | | | 0 | 02 9964 0000 | | www.crisis.com.au |

| 3 | D & D Mailing Services | Glenn Reidy | 21 Ormby Place | Wetherill Park | NSW | 2164 | | | |

| 4 | Couriers to You | Sam | 1 Bridge Street | Hornsby | NSW | 2077 | | | |

+————+————————+————-+———————+—————-+——–+————+————–+———–+———————+

4 rows in set (0.00 sec)

SELECT * FROM users limit 5;

+——–+———+———+———+———–+———–+———-+

+——–+———+———+———+———–+———–+———-+

| 1 | 1 | CM | Peter | Lagios | PL | PL |

| 2 | 2 | CM | Charles | Macdonald | CM | CM |

| 3 | 3 | CL | Cindy | Lynch | CL | CL |

| 4 | 5 | JL | Jessie | Liu | JL | JL |

| 5 | 6 | SP | Sandy | Perazza | SP | SP |

+——–+———+———+———+———–+———–+———-+

5 rows in set (0.00 sec)

SELECT * FROM adverisers order by advertiserid limit 2;

+————–+————-+———–+———-+——-+———————+————+——-+————+———+—————–+—————-+————–+—————-+———-+———-+

| 9 | Apple | Khal | Drogo | Mr | 4th lane westeros | westeros | West | 4321 | Thrones | (123) 2323 2332 | (34) 34 324324 | 4123123 | [email protected] | khal.com | 2 |

| 8 | Google | John | Snow | Mr | 3rd lane winterfell | Winterfell | North | 1234 | GOT | (120) 23432 324 | (32)124 141 | 123213213 | [email protected] | snow.com | 1 |

2 rows in set (0.00 sec)

SELECT * FROM agencies order by agencyid limit 2;

+———-+———————+——–+———–+———–+————————–+————————–+—————+——+—————+——————+——————+

| 6 | Well Known Agencies | Mr | Stanley | Baratheon | 123 kings landing | 321 kings landing harbor | south | 3212 | (232) 324 234 | [email protected] | 0.2 |

2 rows in set (0.00 sec)

SELECT * FROM orders order by orderid limit 1;

+———+———-+———-+————+——————+———+——————————-+————+———————————-+——–+

+———+———-+———-+————+——————+———+——————————-+————+———————————-+——–+

| 18 | 9 | NULL | 2018-01-10 | 0.1 | April | Make a good attractive design | 0 | Stan contact details (32) 323 32 | 2 |

+———+———-+———-+————+——————+———+——————————-+————+———————————-+——–+

1 row in set (0.00 sec)

SELECT * FROM orderdetails order by orderdetailid limit 1;

+———–+———+—————+—————+————-+————–+—————-+————+—————-+—————–+———-+

+———–+———+—————+—————+————-+————–+—————-+————+—————-+—————–+———-+

| 19 | 18 | 19 | 323 | 2018-01-23 | NULL | NULL | NULL | NULL | 1 | NULL |

| 9 | 17 | 28 | 3223 | 2018-01-17 | NULL | NULL | NULL | NULL | 4 | NULL |

+———–+———+—————+—————+————-+————–+—————-+————+—————-+—————–+———-+

2 rows in set (0.00 sec)

SQL:

select companyName,concat (firstname,’ ‘,lastname) as “Advertiser contact name” from advertisers

where agencyid is not null;

Output:

+————————————-+————————-+

| companyName | Advertiser contact name |

+————————————-+————————-+

| 3M Tapes & Adhesive Division | Glenn Mortimer |

| Industrial Packaging Systems Pty Lt | Warren Jennings |

| Visy Industrial Packaging | Elizabeth Wilson |

| AMTAS Pty Ltd | Allan Russell |

| Google | John Snow |

| Apple | Khal Drogo |

+————————————-+————————-+

6 rows in set (0.00 sec)

Query 2

SQL:

select orderid,unitprice,production

from orderdetails

where size=’Full page’ AND unitprice>0;

Output:

+———+———–+————+

| orderid | unitprice | production |

+———+———–+————+

| 8 | 3141 | 0.00 |

+———+———–+————+

1 row in set (0.00 sec)

Query 3

SQL:

select SUM(od.unitPrice+od.production+od.GST) AS “Amount owing”

FROM orderdetails od

join orders o on o.orderid=od.orderid

join advertisers a on a.advertiserid=o.clientid

group by a.companyName

order by “Amount owing” desc;

Output:

+————–+

| Amount owing |

+————–+

| 1665.40 |

| 7022.40 |

| 2854.50 |

| 11858.00 |

| 9384.10 |

| 8457.90 |

| 737.00 |

+————–+

7 rows in set (0.00 sec)

Query 4

SQL:

select pm.paymentmethod,sum(p.paymentamount) As “Total”

from payments p

join paymentmethods pm on p.paymentmethodid=pm.paymentmethodid

where pm.paymentmethod=’Visa’ OR pm.paymentmethod=’Mastercard’

group by pm.paymentmethod;

Output:

+—————+——-+

| paymentmethod | Total |

+—————+——-+

| Mastercard | 2242 |

| Visa | 1975 |

+—————+——-+

2 rows in set (0.00 sec)

Query 5

SQL:

select firstname,lastname,mobilenumber,email

from advertisers

where mobilenumber is null;

Output:

+———–+———-+————–+————————————–+

+———–+———-+————–+————————————–+

+———–+———-+————–+————————————–+

5 rows in set (0.00 sec)

Query 6

SQL:

select companyName

from suppliers

where companyName like ‘%courier%’;

Output:

+—————–+

| companyName |

+—————–+

| Crisis Couriers |

| Couriers to You |

+—————–+

2 rows in set (0.00 sec)

Query 7

SQL:

select u.fname,u.surname,n.remarks

from notes n

join users u on u.userid=n.UserID

where n.followupdate<now() and n.complete=0;

Output:

———————–+

| fname | surname | remarks |

+——-+———+————————————————————————————————————————————————————————————————————————————————-+

| Peter | Lagios | Peter says has cut all Nov/Dec advertising. Has plan for New Year – not yet decided. Will send copies of payments for Nov/Dec 17 n07-5017 $1751.30. |

| Peter | Lagios | Peter Turner booked 1/2 page in Mar/Apr 2017 edition booked at 2016 6x rate of $2038 – should be 2017 casual rate of $2283 – saving is $245. Agency to provide new material or repeat material instructions. |

| Peter | Lagios | SB booked full page $2651 [should be $3079] plus enhanced listing $399 [should be $460] for Review 2017. Also booked Jan/Feb Mar/Apr May/June July/Aug Sept/Oct 2017 @ $2651 [should be $3080] OFC $3255 [should be $3735] Total saving $2362 |

| Cindy | Lynch | Paul Schultz now works for Birrus International. New phone no is 03 9769 2646, Left a message to ring back cl or pd re advertising. Will F/U next week. |

| Cindy | Lynch | From Renee, supplied pr on expansion. Passed to ed. – We are hoping to do some advertising in June this year for Bisalloy’s 25 year anniversary but nothing definite has been decided upon as yet. F/U Michael Sampson |

5 rows in set (0.00 sec)

Query 8

SQL:

select a.companyName,sum(od.pagesize)

FROM orderdetails od

join orders o on o.orderid=od.orderid

join advertisers a on a.advertiserid=o.clientid

where o.orderdate between ‘2017-07-01’ and ‘2017-07-15’

group by a.companyName

order by sum(od.pagesize) desc;

Output:

+————————————-+——————+

| companyName | sum(od.pagesize) |

+————————————-+——————+

| AMTAS Pty Ltd | 1 |

| Visy Industrial Packaging | 0 |

| 3M Tapes & Adhesive Division | 0 |

| Industrial Packaging Systems Pty Lt | 0 |

| Siemens Ltd – Industrial Automatio | 0 |

+————————————-+——————+

5 rows in set (0.00 sec)

Query 9

SQL:

select enrollmenttype, count(staffid) as “No of Staff”

from staff

group by enrollmenttype;

Output:

+—————-+————-+

| enrollmenttype | No of Staff |

+—————-+————-+

| C | 1 |

| FT | 5 |

| PT | 4 |

+—————-+————-+

3 rows in set (0.02 sec)

Query 10

SQL:

select ag.agencyname,ad.firstname,ad.lastname,ad.businessphone

from advertisers ad

join agencies ag on ag.agencyid=ad.agencyid

where ag.agencyname=’Media Communications’;

Output:

+———————-+———–+———-+—————-+

+———————-+———–+———-+—————-+

+———————-+———–+———-+—————-+

2 rows in set (0.00 sec)

Query 11

SQL:

select concat(companyname, ‘ – ‘, firstname,’ ‘ ,lastname) AS “Contacts name”

from advertisers

where advertiserid not in(select clientid

from orders);

Output:

+————————————–+

| Contacts name |

+————————————–+

| ActionLaser Pty Ltd – Stephen Morris |

+————————————–+

1 row in set (0.00 sec)

Query 12

SQL:

select a.companyName,(SUM(od.unitPrice+od.production+od.GST)-p.paymentAmount) AS “Amount Outstanding”

FROM orderdetails od

join orders o on o.orderid=od.orderid

join advertisers a on a.advertiserid=o.clientid

join payments p on p.orderid=o.orderid

where a.advertiserid in (select clientid from orders)

and

(select (SUM(od.unitPrice+od.production+od.GST)-p.paymentAmount)

FROM orderdetails od

join orders o on o.orderid=od.orderid

join advertisers a on a.advertiserid=o.clientid

join payments p on p.orderid=o.orderid)>0

group by a.companyName;

Output:

+————————————-+——————–+

| companyName | Amount Outstanding |

+————————————-+——————–+

| 3M Tapes & Adhesive Division | 2497.80 |

| AMTAS Pty Ltd | 2956.50 |

| Apple | 414.00 |

| Freightcorp | -0.20 |

| Industrial Packaging Systems Pty Lt | -0.50 |

| Siemens Ltd – Industrial Automatio | 6633.00 |

| Visy Industrial Packaging | 0.00 |

+————————————-+——————–+

7 rows in set (0.00 sec)

The task was fairly difficult although very informative. The whole process of modelling a database based on a case study is informative as it enlightened me on the steps necessary to come up with a properly designed database. The steps followed including studying a case study to come up with the entities and their attributes and finding any relationships that exist between the entities are now clear to me. Modelling using logical and physical entity relationship diagrams made it easier for me to understand the ELP database.

The most challenging part of the whole task was importing the data from excel to MySQL tables. A lot of errors were encountered mainly because of incompatible data types. Identifying the errors and changing the data types for the imports to work was very challenging and tedious. Apart from the imports, coming up with the queries was also challenging.

Although there were many challenges, the knowledge acquired from this task is very important as it will help me in future if I encounter a task that requires me to come up with a database from scratch.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Tasks For Normalizing And Modeling Advertisers_Data.xlsx Database ”

Get high-quality paper

NEW! AI matching with writer