Part 1: Create a Database (10 points)

Create a folder named XXAssign7 where XX are your initials.

Following the ERD in ERD Commision.docx, create a database in your XXAssign7 folder named Commission using Microsoft Access or LibreOffice Base or OpenOffice Base.

Notes:

• All primary keys should be AutoNumber (in Access) or Integer with AutoValue True (in Base).
• Foreign keys must be Number (in Access) or Integer with AutoValue False (in Base).
• Fields must be appropriately typed and sized.
  • Unless a field is going to be used in arithmetic or is a date or True/False, it should be ShortText (Access) or Text[VARCHAR] (Base). Set FieldSize (Access) or Length (Base) to a reasonable size for text fields.
• Enter relationships from ERD. Be sure they enforce referential integrity (in Access; this is automatic in Base).

Part 2: Fill the Database (5 points)

Fill the database with your own data. You must have at least:

• At least 3 sales persons. One must be yourself.
• All sales persons must have at least 2 clients.
• All clients must have at least 2 sales.

The data may be fictitious, but one salesperson MUST have your first and last name.

Part 3: Create a Commission Report (5 points)

Create a report which contains:

• Sales Person's name
• Client's company name
• Date, sales amount and commission amount (= sales amount * commission rate)
• Total sales and commission amount by sales person and overall

Sample:

Part 4: Data Integrity (5 points)

Using Word or Writer:

• Give an example of a table that lacks entity integrity (2 points).
• Give an example of two tables that lack referential integrity (3 points).

These tables don't have to be big; they only need two or three records apiece. They do need to be your own examples (not copied from the book, the Internet, a classmate, etc). Explain why these tables lack integrity.

Please note: Your examples must have data (records) not just design (field names)

Save this document in XXAssign7 as Integrity.docx

(If you don't know how to create a table in a word processor, Google a tutorial on the subject.)

===========================================

ERD Commision.docx

Commission Amount = Commission Rate * Amount (Dan Doc Commission Rate is 10%; Jule is 20%; Minh is 15%)

Salesperson Client Sales PK Sales PersonlD + PK ClientID + PK Salesid Sales PersonName Company Name SalesDate Office Contact Amount CommissionRate SalesYearTo Date FK ClientID FK Sales YearToDate Salesperson CommissionYearToD ate Commision Report Company Name Sales Date Amount Commission Amount Name Dan Doc Big Box Big Box Ma & Pa Ma & Pa 2/22/2019 2/25/2019 2/19/2019 2/22/2019 $2,000.00 $1,000.00 $3,000.00 $4,000.00 $200.00 $100.00 $300.00 $400.00 $10,000.00 $1,000.00 Jule LaBelle $600.00 $120.00 Center for Centers Center for Centers New Life New Life 2/21/2019 2/21/2019 2/21/2019 $500.00 $400.00 $200.00 $100.00 $80.00 $40.00 $1,700.00 $340.00 Minh Ma $15.00 $15.00 Jaust For You Jaust For You Melon Run Melon Run 2/20/2019 2/22/2019 2/20/2019 2/26/2019 $100.00 $100.00 $3,000.00 $3,000.00 $450.00 $450.00 $6,200.00 $930.00 Grand Total $17,900.00 $2,270.00 Thursday, February 28, 2019 Page 1 of 1 Salesperson Client Sales PK Sales PersonlD + PK ClientID + PK Salesid Sales PersonName Company Name SalesDate Office Contact Amount CommissionRate SalesYearTo Date FK ClientID FK Sales YearToDate Salesperson CommissionYearToD ate Commision Report Company Name Sales Date Amount Commission Amount Name Dan Doc Big Box Big Box Ma & Pa Ma & Pa 2/22/2019 2/25/2019 2/19/2019 2/22/2019 $2,000.00 $1,000.00 $3,000.00 $4,000.00 $200.00 $100.00 $300.00 $400.00 $10,000.00 $1,000.00 Jule LaBelle $600.00 $120.00 Center for Centers Center for Centers New Life New Life 2/21/2019 2/21/2019 2/21/2019 $500.00 $400.00 $200.00 $100.00 $80.00 $40.00 $1,700.00 $340.00 Minh Ma $15.00 $15.00 Jaust For You Jaust For You Melon Run Melon Run 2/20/2019 2/22/2019 2/20/2019 2/26/2019 $100.00 $100.00 $3,000.00 $3,000.00 $450.00 $450.00 $6,200.00 $930.00 Grand Total $17,900.00 $2,270.00 Thursday, February 28, 2019 Page 1 of 1