Speedy Messenger Service is a business that employs messengers who deliver packages in an urban area. The messengers are paid an hourly rate plus a...

Speedy Messenger Service is a business that employs messengers who deliver packages inan urban area. The messengers are paid an hourly rate plus a fixed dollar amount forevery package delivered. The database should track the number of packages deliveredper day by each messenger, and the number and weight of packages delivered forcustomers. Customer bills need to be generated and the data in the database will beimported into a payroll system to generate the employee paychecks.Business Rules1. The Delivery Rates are based upon the RateCategory2. The number of packages delivered per day by each messenger must be calculated3. The number of packages delivered for each customer per day must be calculated.4. For each package the weight, location and RateID (category used to determine what tocharge the customer) must be tracked.5. A Messenger must attend a 2 week unpaid training session BEFORE being assigned adelivery. During this period of time hours do NOT need to be tracked.6. The number of hours a messenger works daily must be calculated.7. All packages must have a Delivery associated with them.The Functional Dependencies result in the following Normalized Entities:MsgrID MsgrName, MsgrAddress, MsgrPhone, MsgrEmail, PayRateCustID CustName, CustAddress, CustPhone, CustEmailRateID RateCategory, PriceDeliveryID DeliveryDate, Weight, DeliveryLocation, CustID, RateID, MsgrIDMsgrID, WorkedDate HoursNote: Cust = Customer Data | Msgr = Messenger Data11/3/2014 Prepared by Valerie Farmer, Assistant Professor CIS Page 2 of 3Using the following as a Guide to Design the E-R Diagram1. Verify that all columns are broken down to the smallest unit of data. Ex. Name (FirstName, LastName)2. Create the resulting Entities based upon the Functional Dependencies. THIS DATABASE IS NORMALIZED!!!a. Indicate Optional Columnsb. Indicate Required Columns3. Indicate which column(s) will serve as the Primary Key.More than one column may be needed to make a Primary Key (Concatenated) – Hint: ONE Entity has this – See Visio Tutorial Handout – Page 124. Identify the Foreign Keys. Remember do NOT Add Foreign Keys during Entity setup. Relationships will add them.a. Two Entities will contain Foreign Keysb. One Entity will contain more than one Foreign Key5. Create the Relationships between Entities.Hint: There should be ONE Entity that has an Indentifying Relationship – See Visio Tutorial Handout page 126. Verify the Cardinalities between Entities, based upon the Business Rules. See Visio Tutorial Handout page 107. Add appropriate Verb Phrases. See Visio TUTORIAL Handout page 15Verb Phrases should describe the actions between the Entities8. Add the Business Rules to the Diagram. (Copy and Paste them)E-R Diagram Requirements (example below) Entities (Named using the Leszynski/Reddick naming convention: tbl_____) Attributes within the Entities Columns Only (no need to show type and size) along with Optionality Primary Keys Relationships Foreign Keys Cardinalities Verb Phrases Business RulesCardinalitiesRelationshipsOptionalityPrimay KeysForeign KeysVerb PhrasesBusiness RulesUse the Business Rules and good Database Design Practicesto determine Optional and Required Columns.11/3/2014 Prepared by Valerie Farmer, Assistant Professor CIS Page 3 of 3BEFORE submitting project make sure you have done the following!!!Activity PossiblePoints Use the IDE1FX Diagram type Converted the Functional Dependencies into 5 EntitiesHint:No additional columns were needed unless they broke an existing column down into the smallestunit of data. The database was already normalized.20 Make sure you have created Relationships between the Entities based upon the Foreign Keys 20 Make sure you have based the Cardinalities on the Business Rules: 10 Column Data should be broken down into the Smallest Unit of DataHint: This applies to 5 columns Optionality shoud allow for columns that might not have data enteredHint: The Business Rules do not require E-mail Addresses10 Primary Keys should be indicated and based upon the Determinants 10 Foriegn Keys should be included in the appropriate Tables and should be RequiredHints: Two Entities will contain Foreign Keys One Entity will contain more than one Foreign Key10 Verb Phrases should be included on Relationships Lines:Hint: Verb Phrases should describe the actions between the Entities.For example: Patients request appointments10 Should include Business Rules 10Total Points 100%