Chapter 4 In Class Project (Problem #6)

Paddys Lawn Care just started operations. Frank is the owner of Paddys and decides he wants to set up his database right from the get go, so he hires his son Charlie to design the database. Charlie has a good heart but may have lied about his Accounting degree (his real degree is in Bird Law). He knows that you are enrolled in an AWESOME accounting systems class and has reached out to you to help him design a relational database. Albeit reluctantly, you decide to help him. You meet Charlie in a sketchy bar to discuss his needs. He tells you the following information:

He wants the database to be able to create invoices for Paddys customers and store all the pertinent related information including information about Paddys employees. Paddys has three different services that they charge different rates for and they pay their employees hourly. Charlie notes that only one employee will work on a job at a time (i.e. one employee per invoice) because the team does not trust each other and would get no work done if working together. The invoices are issued on the date of the service. All three employees can perform each service. Frank will run a report at the end of the month to determine how much to pay each employee. Frank was very explicit that he wanted the hourly pay information stored but no records of paychecks. Frank wants to set up a numbering system for his employees, customers, and services. He said he wants the numbering scheme to be in 4 digits for each grouping because he guarantees this business is going to be huge, and he plans to sell it as soon as its profitable so he can pursue his dreams of selling branded hardboiled eggs. Frank also wants no reference to himself, Charlie, or the office location

Charlie provides the following information for you:

Paddys has three lawn care employees who are paid by the hour as follows:

• Ronald Mac MacDonald - $9/hour
  • Phone: 267-555-2232
  • Address: 777 Knightmun Dr., Philadelphia, PA 19145
  • Employee ID: 1001
• Dee Reynolds - $10/hour
  • Phone: 267-555-2473
  • Address: 1 Chardee MacDennis Ave., , Philadelphia, PA 19145
  • Employee ID: 1002
• Dennis Reynolds - $12/hour
  • Phone: 267-555-1920
  • Address: 900 Golden Rd. , Philadelphia, PA 19145
  • Employee ID: 1003

Paddys offers three different services at the following rates:

• Mowing - $20/hour (Svc # 2001)
• Landscaping - $30/hour (Svc #2002)
• Hedge Sculpting - $50/hour (Svc #2003)

Paddys currently has four customers lined up:

• Lincoln Financial Field (Cust # 3001)
  • Phone 267-555-9980
  • Address 1 Lincoln Financial Field Way, , Philadelphia, PA 19145
• South Philadelphia Community Center (Cust # 3002)
  • Phone 215-555-1957
  • Address 5 Damon Ln, , Philadelphia, PA 19145
• McPoyle Farm (Cust # 3003)
  • Phone 215-555-1357
  • Address 379 Ponderosa Rd., Doylestown, PA 18901
• Mollys Restaurant (Cust #3004)
  • Phone 215-555-2468
  • Address 13 Pub St. , Philadelphia, PA 19145

Services provided during August, (the first month of operations) were as follows:

• 8/2/18 Dee provided 2 hours of mowing and 5 hours of landscaping for Mollys Restaurant.
• 8/7/18 Mac provided 12 hours of mowing and 3 hours of landscaping for Lincoln Financial Field.
• 8/9/18 Mac provided 1 hour of landscaping for the South Philly Community Center
• 8/17/18 Dennis provided 22 hours of hedge sculpting and 4 hours of mowing to the McPoyle Farm.
• 8/23/18 Dee provided 7 hours of hedge sculpting for Lincoln Financial Field.

Please create a relational database using the Semantic Modelling approach. Make sure the database is free from all anomalies and that it follows the four rules of database design. This database should allow Frank to determine the which employees worked for which customers, the services rendered, and the hours worked. (Make sure you label any and all Primary and Foreign Keys)