Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

You are provided an Excel file that contains a partial ERD, suggested table definition, and also 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.

Task 1: Normalizing the Data

+---------------+--------------+------+-----+---------+-------+

| Field         | Type         | Null | Key | Default | Extra |

+---------------+--------------+------+-----+---------+-------+

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

| CompanyName   | varchar(35)  | NO   |     | NULL    |       |

| FirstName     | varchar(25)  | YES  |     | NULL    |       |

| LastName      | varchar(25)  | NO   |     | NULL    |       |

| title         | varchar(5)   | YES  |     | NULL    |       |

| Address       | varchar(50)  | YES  |     | NULL    |       |

| City          | varchar(50)  | YES  |     | NULL    |       |

| State         | varchar(15)  | YES  |     | NULL    |       |

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

| Country       | varchar(35)  | YES  |     | NULL    |       |

| BusinessPhone | varchar(15)  | YES  |     | NULL    |       |

| FaxNumber     | varchar(15)  | YES  |     | NULL    |       |

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

| Email         | varchar(100) | YES  |     | NULL    |       |

| Website       | varchar(150) | YES  |     | NULL    |       |

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

+---------------+--------------+------+-----+---------+-------+

+------------------+-------------+------+-----+---------+----------------+

| Field            | Type        | Null | Key | Default | Extra          |

+------------------+-------------+------+-----+---------+----------------+

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

| AgencyName       | varchar(50) | NO   |     | NULL    |                |

| Prefix           | varchar(5)  | YES  |     | NULL    |                |

| FirstName        | varchar(25) | YES  |     | NULL    |                |

| LastName         | varchar(25) | NO   |     | NULL    |                |

| Address          | varchar(35) | YES  |     | NULL    |                |

| Address2         | varchar(35) | YES  |     | NULL    |                |

| State            | varchar(35) | YES  |     | NULL    |                |

| Code             | varchar(4)  | YES  |     | NULL    |                |

| workPhone        | varchar(25) | YES  |     | NULL    |                |

| EmailAddress     | varchar(50) | YES  |     | NULL    |                |

| AgencyPercentage | text        | NO   |     | NULL    |                |

+------------------+-------------+------+-----+---------+----------------+

+--------------+-------------+------+-----+---------+----------------+

| Field        | Type        | Null | Key | Default | Extra          |

+--------------+-------------+------+-----+---------+----------------+

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

| CopyStatus   | varchar(25) | NO   | UNI | NULL    |                |

| JobReqNo     | tinyint(1)  | NO   |     | NULL    |                |

+--------------+-------------+------+-----+---------+----------------+

+-----------+-------------+------+-----+---------+----------------+

| Field     | Type        | Null | Key | Default | Extra          |

Task 2: Creating an Entity Relationship Diagram

+-----------+-------------+------+-----+---------+----------------+

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

| IssueCode | varchar(50) | NO   | UNI | NULL    |                |

| Extension | text        | NO   |     | NULL    |                |

| Issue     | text        | NO   |     | NULL    |                |

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

+-----------+-------------+------+-----+---------+----------------+ 

+--------------+--------------+------+-----+---------+-------+

| Field        | Type         | Null | Key | Default | Extra |

+--------------+--------------+------+-----+---------+-------+

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

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

| EntryDate    | date         | NO   |     | NULL    |       |

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

| Remarks      | varchar(500) | NO   |     | NULL    |       |

| followUpDate | varchar(15)  | YES  |     | NULL    |       |

| Complete     | tinyint(1)   | NO   |     | NULL    |       |

+--------------+--------------+------+-----+---------+-------+

+------------------+---------------+------+-----+---------+-------+

| Field            | Type          | Null | Key | Default | Extra |

+------------------+---------------+------+-----+---------+-------+

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

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

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

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

| Production       | decimal(6,2)  | YES  |     | NULL    |       |

| ProductionDetail | text          | NO   |     | NULL    |       |

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

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

| Size             | text          | NO   |     | NULL    |       |

| Shape            | text          | NO   |     | NULL    |       |

| Special          | text          | YES  |     | NULL    |       |

| Colour           | text          | YES  |     | NULL    |       |

| PageSize         | decimal(10,0) | NO   |     | NULL    |       |

| CopyDue          | text          | YES  |     | NULL    |       |

| CopyToUse        | text          | YES  |     | NULL    |       |

| statusOfCopy     | varchar(100)  | NO   |     | NULL    |       |

| Position         | text          | YES  |     | NULL    |       |

| Cancelled        | tinyint(1)    | NO   |     | NULL    |       |

+------------------+---------------+------+-----+---------+-------+ 

+--------------------+---------------+------+-----+---------+-------+

| Field              | Type          | Null | Key | Default | Extra |

+--------------------+---------------+------+-----+---------+-------+

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

Conclusion

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

| PoNumber           | varchar(25)   | YES  |     | NULL    |       |

| OrderDate          | date          | YES  |     | NULL    |       |

| AgencyPercentage   | decimal(10,1) | YES  |     | NULL    |       |

| CopyDue            | text          | YES  |     | NULL    |       |

| SpecialInstruction | text          | YES  |     | NULL    |       |

| GST_Exemot         | tinyint(1)    | NO   |     | NULL    |       |

| CopyNotes          | text          | YES  |     | NULL    |       |

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

+--------------------+---------------+------+-----+---------+-------+ 

+-----------+---------------+------+-----+---------+-------+

| Field     | Type          | Null | Key | Default | Extra |

+-----------+---------------+------+-----+---------+-------+

| PageSize  | varchar(25)   | NO   | PRI | NULL    |       |

| PageValue | decimal(10,0) | NO   |     | NULL    |       |

+-----------+---------------+------+-----+---------+-------+ 

+-----------------+-------------+------+-----+---------+----------------+

| Field           | Type        | Null | Key | Default | Extra          |

+-----------------+-------------+------+-----+---------+----------------+

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

| PaymentMethod   | varchar(25) | NO   | UNI | NULL    |                |

| CreditCard      | tinyint(1)  | NO   |     | NULL    |                |

+-----------------+-------------+------+-----+---------+----------------+ 

+-----------------+---------------+------+-----+---------+-------+

| Field           | Type          | Null | Key | Default | Extra |

+-----------------+---------------+------+-----+---------+-------+

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

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

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

| PaymentAmount   | decimal(10,0) | NO   |     | NULL    |       |

| PaymentDate     | date          | NO   |     | NULL    |       |

| CreditCardNo    | text          | YES  |     | NULL    |       |

| CardHolderName  | text          | YES  |     | NULL    |       |

| CardExDate      | text          | YES  |     | NULL    |       |

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

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

| ChequeNo        | text          | YES  |     | NULL    |       |

+-----------------+---------------+------+-----+---------+-------+ 

+----------------+-------------+------+-----+---------+----------------+

| Field          | Type        | Null | Key | Default | Extra          |

+----------------+-------------+------+-----+---------+----------------+

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

| FirstName      | varchar(25) | NO   |     | NULL    |                |

| LastName       | varchar(25) | NO   |     | NULL    |                |

| Title          | varchar(5)  | YES  |     | NULL    |                |

| WorkPhone      | text        | YES  |     | NULL    |                |

| EnrollmentType | varchar(2)  | NO   |     | NULL    |                |

+----------------+-------------+------+-----+---------+----------------+ 

+-------------+---------+------+-----+---------+----------------+

| Field       | Type    | Null | Key | Default | Extra          |

+-------------+---------+------+-----+---------+----------------+

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

| CompanyName | text    | NO   |     | NULL    |                |

| ContactName | text    | YES  |     | NULL    |                |

| Address     | text    | YES  |     | NULL    |                |

| City        | text    | YES  |     | NULL    |                |

| Region      | text    | YES  |     | NULL    |                |

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

| Phone       | text    | YES  |     | NULL    |                |

| Fax         | text    | YES  |     | NULL    |                |

| website     | text    | YES  |     | NULL    |                |

+-------------+---------+------+-----+---------+----------------

+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |

+-----------+--------------+------+-----+---------+----------------+

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

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

| Initals   | varchar(2)   | YES  |     | NULL    |                |

| FName     | text         | NO   |     | NULL    |                |

| SurName   | text         | NO   |     | NULL    |                |

| LoginName | varchar(25)  | NO   | UNI | NULL    |                |

| password  | varchar(125) | NO   |     | NULL    |                |

+-----------+--------------+------+-----+---------+----------------+ 

SELECT * FROM Advertisers limit 5;

+--------------+-------------------------------------+-----------+----------+-------+-----------------------------------------+-----------------------------+-------+------------+---------+-----------------+-----------------+--------------+--------------------------------------+--------------------------------+----------+

| AdvertiserID | CompanyName                         | FirstName | LastName | title | Address                                 | City                        | State | PostalCode | Country | BusinessPhone   | FaxNumber       | MobileNumber | Email                                | Website                        | AgencyId |

+--------------+-------------------------------------+-----------+----------+-------+-----------------------------------------+-----------------------------+-------+------------+---------+-----------------+-----------------+--------------+--------------------------------------+--------------------------------+----------+

|            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 |

|            2 | Freightcorp                         | Glenn     | Stephens |       | 50 Rocklands Road                       | CROWS NEST                  | NSW   |       2065 |         |                 |                 |         NULL |                                      |                                |     NULL |

|            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;

+----------+---------------------------------+--------+-----------+-----------+------------------------------------+----------------------+---------------+------+----------------+--------------------------+------------------+

| AgencyId | AgencyName                      | Prefix | FirstName | LastName  | Address                            | Address2             | State         | Code | workPhone      | EmailAddress             | AgencyPercentage |

+----------+---------------------------------+--------+-----------+-----------+------------------------------------+----------------------+---------------+------+----------------+--------------------------+------------------+

|        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 | Discovered Agencies             | Miss   | Cersei    | Lannister | kings landing high tower           | kings landing street | kings landing | 2322 | (21) 123 123   | [email protected]         | 0.2              |

+----------+---------------------------------+--------+-----------+-----------+------------------------------------+----------------------+---------------+------+----------------+--------------------------+------------------+

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;

+----------+--------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------+

| RemarkID | AdvertiserID | EntryDate  | UserID | Remarks                                                                                                                                                                                                                                            | followUpDate | Complete |

+----------+--------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------+

|        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;

+---------------+---------+---------+-----------+------------+------------------+--------+------+--------------+--------+---------+-------------+----------+---------+-----------+------------------+----------+-----------+

| OrderDetailID | OrderID | IssueId | UnitPrice | Production | ProductionDetail | GST    | Year | Size         | Shape  | Special | Colour      | PageSize | CopyDue | CopyToUse | statusOfCopy     | Position | Cancelled |

+---------------+---------+---------+-----------+------------+------------------+--------+------+--------------+--------+---------+-------------+----------+---------+-----------+------------------+----------+-----------+

|             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;

+---------+----------+----------+------------+------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------+--------+

| OrderId | ClientID | PoNumber | OrderDate  | AgencyPercentage | CopyDue | SpecialInstruction                                                                                                                                   | GST_Exemot | CopyNotes                                                                                                    | UserID |

+---------+----------+----------+------------+------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------+--------+

|       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;

+-----------+---------+---------------+---------------+-------------+--------------+----------------+------------+----------------+-----------------+----------+

| PaymentID | OrderId | OrderDetailId | PaymentAmount | PaymentDate | CreditCardNo | CardHolderName | CardExDate | CardAuthorizNo | PaymentMethodID | ChequeNo |

+-----------+---------+---------------+---------------+-------------+--------------+----------------+------------+----------------+-----------------+----------+

|         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;

+---------+-----------+-----------+-------+-----------+----------------+

| StaffID | FirstName | LastName  | Title | WorkPhone | EnrollmentType |

+---------+-----------+-----------+-------+-----------+----------------+

|       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;

+------------+------------------------+-------------+---------------------+----------------+--------+------------+--------------+-----------+---------------------+

| supplierID | CompanyName            | ContactName | Address             | City           | Region | PostalCode | Phone        | Fax       | website             |

+------------+------------------------+-------------+---------------------+----------------+--------+------------+--------------+-----------+---------------------+

|          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;

+--------+---------+---------+---------+-----------+-----------+----------+

| UserId | StaffID | Initals | FName   | SurName   | LoginName | password |

+--------+---------+---------+---------+-----------+-----------+----------+

|      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;

+--------------+-------------+-----------+----------+-------+---------------------+------------+-------+------------+---------+-----------------+----------------+--------------+----------------+----------+----------+

| AdvertiserID | CompanyName | FirstName | LastName | title | Address             | City       | State | PostalCode | Country | BusinessPhone   | FaxNumber      | MobileNumber | Email          | Website  | AgencyId |

+--------------+-------------+-----------+----------+-------+---------------------+------------+-------+------------+---------+-----------------+----------------+--------------+----------------+----------+----------+

|            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;

+----------+---------------------+--------+-----------+-----------+--------------------------+--------------------------+---------------+------+---------------+------------------+------------------+

| AgencyId | AgencyName          | Prefix | FirstName | LastName  | Address                  | Address2                 | State         | Code | workPhone     | EmailAddress     | AgencyPercentage |

+----------+---------------------+--------+-----------+-----------+--------------------------+--------------------------+---------------+------+---------------+------------------+------------------+

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

|        5 | Discovered Agencies | Miss   | Cersei    | Lannister | kings landing high tower | kings landing street     | kings landing | 2322 | (21) 123 123  | [email protected] | 0.2              |

+----------+---------------------+--------+-----------+-----------+--------------------------+--------------------------+---------------+------+---------------+------------------+------------------+

2 rows in set (0.00 sec)

SELECT * FROM orders order by orderid limit 1;

+---------+----------+----------+------------+------------------+---------+-------------------------------+------------+----------------------------------+--------+

| OrderId | ClientID | PoNumber | OrderDate  | AgencyPercentage | CopyDue | SpecialInstruction            | GST_Exemot | CopyNotes                        | UserID |

+---------+----------+----------+------------+------------------+---------+-------------------------------+------------+----------------------------------+--------+

|      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;

+-----------+---------+---------------+---------------+-------------+--------------+----------------+------------+----------------+-----------------+----------+

| PaymentID | OrderId | OrderDetailId | PaymentAmount | PaymentDate | CreditCardNo | CardHolderName | CardExDate | CardAuthorizNo | PaymentMethodID | ChequeNo |

+-----------+---------+---------------+---------------+-------------+--------------+----------------+------------+----------------+-----------------+----------+

|        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)

  1. 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)

  1. 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)

  1. Query 5

SQL:

select firstname,lastname,mobilenumber,email

from advertisers

where mobilenumber is null;

Output:

+-----------+----------+--------------+--------------------------------------+

| firstname | lastname | mobilenumber | email                                |

+-----------+----------+--------------+--------------------------------------+

| Glenn     | Stephens |         NULL |                                      |

| Warren    | Jennings |         NULL |                                      |

| Elizabeth | Wilson   |         NULL | [email protected] |

| Allan     | Russell  |         NULL | [email protected]                   |

| Stephen   | Morris   |         NULL | [email protected]           |

+-----------+----------+--------------+--------------------------------------+

5 rows in set (0.00 sec)

  1. 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)

  1. 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)

  1. 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:

+----------------------+-----------+----------+----------------+

| agencyname           | firstname | lastname | businessphone  |

+----------------------+-----------+----------+----------------+

| Media Communications | Warren    | Jennings | (03) 9399 8955 |

| Media Communications | Allan     | Russell  | (08) 9410 2300 |

+----------------------+-----------+----------+----------------+

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.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Tasks For Normalizing And Modeling Advertisers_Data.xlsx Essay.. Retrieved from https://myassignmenthelp.com/free-samples/700011-database-design-and-development-for-physical-entity.

"Tasks For Normalizing And Modeling Advertisers_Data.xlsx Essay.." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/700011-database-design-and-development-for-physical-entity.

My Assignment Help (2020) Tasks For Normalizing And Modeling Advertisers_Data.xlsx Essay. [Online]. Available from: https://myassignmenthelp.com/free-samples/700011-database-design-and-development-for-physical-entity
[Accessed 19 July 2024].

My Assignment Help. 'Tasks For Normalizing And Modeling Advertisers_Data.xlsx Essay.' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/700011-database-design-and-development-for-physical-entity> accessed 19 July 2024.

My Assignment Help. Tasks For Normalizing And Modeling Advertisers_Data.xlsx Essay. [Internet]. My Assignment Help. 2020 [cited 19 July 2024]. Available from: https://myassignmenthelp.com/free-samples/700011-database-design-and-development-for-physical-entity.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
Whatsapp
callback
sales
sales chat
Whatsapp
callback
sales chat
close