DatabaseAssignment:

Scenario:ECOWear is a local company that designs and manufactures eco-friendly casual wear. All clothes are made from earth-friendly fabrics, such as bamboo, hemp, organic cotton, and natural silk. The company recently decided to store its customer and sales rep data in a database. Each customer is assigned to a single sales rep, but each sales rep may be assigned many customers.

1. Go to a https://airtable.com/ and Login

2. First Task (Setup the Database, Tables, Fields)

a. Add a new base named from scratch named:ECOWear.

b.

c. RenameTable1 to SalesRep with the little down arrow

d.

e. Customize and Add fields (with the following field types) to the SalesRep table:

i. SalesRepID (number, integer),

1. Set the SalesRepID as the primary key.(First Column)

ii. FirstName (single line text),

iii. MiddleName (single line text),

iv. LastName (single line text),

v. Address (single line text),

vi. City (single line text),

vii. State (single line text),

viii. Zip (number, integer),

ix. EmailAddress (single line text),

x. BasePay (currency),

xi. CommissionRate (percentage).

3. Second Task (ENTER the records of data in SalesRepTable)

a. Enter the following 3 records of data into the SalesRep table:

ID FirstMiddleLast AddressCity State Zip Email BasePayCommission

1 Vivien Mary Leigh100 W. Tara Lane Windy Hills GA63102 vivien@gmail.com12000 20%
2 William Clark Gable 102 N. Frank Drive Hollywood CA 90211 rhett@yahoo.com15000 18%
3 Leslie WilkesHoward#2 Twin Oaks Drive Plaines GA 62146 ashley@gmail.com11000 19%

4. Third Task (Add the Second table: Customer)

a. Add a new table named Customer

i.

b. Add the field CustomerID of field type number (integer) to the Customer table

i. Set the CustomerID as the primary key. (First Column)

c. Add 5 additional fields of your choice to the Customer table. Select the appropriate data types for your fields

d. Using the CustomerIDs of 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, enter 10 records of data of your choice into the Customer table.

e.

5. Forth Task (One-To-Many Relationship)

One-To-Many Relationship – Each customer is assigned to a single sales rep, but each sales rep may be assigned many customers.

a. In the Customer table,

i. Add a new field named SalesRepID field.

(This field will be used to link the Customer table with the SalesRep table. )

ii. Customize the field type to link to another record in the SalesRep table.

1. Check the box to allow linking to multiple record so the one-to-many relationship is established.

2.

iii. In the 10 records of data that you added to the Customer table, set records 101, 103, and 105 to a SalesRepID of 1.

(This assigns these 3 customers to the Sales Rep of Vivien Leigh in the SalesRep table.)

iv.

b. In the Customer table,

i. set records 102 and 106 to a SalesRepID of 2.

(This assigns these 2 customers to the Sales Rep of William Gable)

c. In the Customer table,

i. set the remaining records to a SalesRepID of 3.

(This assigns these remaining customers to the Sales Rep of Leslie Howard.)

d. View the SalesRep table to see how the relationships are stored in table.

i. Notice the Customer column and multiple CustomerIDs listed.

ii.

6. Fifth Task (Queries) In Airtable, a query is call a view.

a. The View Option is found with the little down arrow

b.

c. Create a view named “Georgia” that filters for sales reps who live in Georgia.

i. Show their first name, last name, state, and commission rate.

d. Create a view named “Salary” that filters for sales reps who have a base pay > 14000.

i. Show their last name, email address, and base salary.

e. Create a view named “Vivien” that filters for Vivien Leigh’s customers.

i. Show 5 fields of your choice from the customer table.

7. Sixth Task (Share your Database)

a. In the final step, you will share your database, creating a link to submit to your instructor. Also, create screenshots of your tables and queries.

b. Share Your Databases via for any email link

i. In your ECOWare Base, on the right-side of the screen, click on Share.

ii. Click on Invite By Link. >> Click on Create Link.

iii. Under “Creator invite link for any email” click on Copy Link.

iv. Paste this link into a Word document.

v.

c. Create Screenshots

i. Take screenshots of your two tables.

1. Be sure to include the fields that demonstrate the relationships.

2. Paste the screenshots into the Word document.

ii. Take screenshots of your three views (queries).

1. Paste the screenshots into the Word document.