Our company, Sensational Fashion, Limited, which runs an e-commerce business, has decided to use Microsoft Access to create a relational database to keep track of our employees and clients. Here are the data we have collected:

Employees
Last Name	First Name	Employee ID	Department	Salary
Your Last Name	Your First Name	G24252	Accounting	$750,000.00
Presley	Priscilla	M3131	Accounting	$337,000.00
Presley	Elvis	N2929	Sales	$1.00
Washington	Denzel	R2345	Sales	$225,000.00
Douglas	Michael	S4321	IT	$115,200.00
Gutierrez	Carlos	T1234	Sales	$100,500.00
Zeta-Jones	Catherine	T4354	Accounting	$75,000.00
Washington	George	V1212	IT	$350,000.00
Washington	Kerry	V9000	Accounting	$325,000.00

Firm Name	Address	State	Credit
Concord Beverages	4444 Lexington Way	MA	Fair
Fordham U.	441 E. Fordham Road	NY	Very Good
Honickman Beverage Group	77 Sunset Boulevard	CA	Very Good
Keurig Dr. Pepper	5492 Cupboard Street	NJ	Okay
PepsiCo	515 Gatorade Avenue	NY	Excellent
The Coca-Cola Company	6500 Caffeine Drive	GA	Excellent

Each client has one representative in our company. Some of our employees are client representatives, while others are not.

Currently, Elvis Presley has been assigned three clients: Pepsi, Coke, and Honickman. Michael Douglas has been assigned Concord and Keurig; and Priscilla Presley has been assigned Fordham.

Your boss has given you the following assignment. Read the whole assignment before you begin.

1. For Employee G24252 replace the first and last names with YOUR first and last name.

2. Create (that is, define the structure of) the necessary tables to define this database in Access. Give the entire database this name: SENSATIONAL.yourfirstname.yourlastname

Be sure to designate appropriate primary keys and to set appropriate field types and sizes. For Annual Salary, use field type currency.

IMPORTANT HINTS:

- Planning the structure of your database--e.g., sketching it out on paper--would be a good way to start before defining it in Access.

- Ask yourself what type of relationship it is (1-1, 1-many, many-to-many?)

- Make sure that if you use a foreign key, it is of the same data type as the field to which you are

linking.

- Hint: See Microsoft Access Lab Exercises #2 and #3 if you need help.

3. Create any relationships that are needed for this database. Be sure to enforce referential integrity.

4. Enter the data for employees and client firms into the database if you have not done so already.

5. Use the Query-by-Examples capability to answer the following questions. For each question, include only the information requested and show fields in the order they are listed in the question. Save your query in the database. (Name your queries by question--e.g., 5a, 5b, and so forth.).

(Hint: Do not worry about the format of the results, but do make sure that all the data show--i.e., nothing is chopped off. You can just drag the boundary line that divides the columns in datasheet view to change the width of columns in a datasheet.)

IMPORTANT: Your queries should be general enough to answer these questions correctly no matter what data are in the database. For example, even if your data already happen to be in the order requested by a particular question, still perform the sort command. (If this were a large database, you would have no way of knowing that the data were already in the correct order.) Also, the results of your queries should show exactly what is asked for, not more or less.

a. Display, in alphabetical order, the full names and salaries of all employees earning more than $110,000 per year. (Hint: Pay attention to first and last names. Another Hint: Enter it as 110000 without $ or ,)

b. Display the full names and annual salaries of all employees, listing them in order of decreasing salary (i.e., the highest paid person goes first and so on down the line).

c. Display the full names and MONTHLY salaries of all employees, listing them in order of decreasing salary (i.e., the highest paid person goes first and so on down the line). (Hint: See pages AC 3-13 on MINDTAP for calculated fields.)

d. Do not worry if your display for the monthly salary column is not very nice, with lots of decimal places. But if you want to clean this up, right click in this column in Design View and then choose "Properties." You will get a "Property Sheet" pane on the right side of your screen. Select "Currency" as the "Format" and 0 for "Decimal Places."de. Display the full names and the annual salaries of all employees in Sales.

e. Display a list of departments, showing for each department how many employees it has (that is, the count of employees) and their average annual salary. (Hint: You will need to use the Total feature on the ribbon.)

f. For the Accounting department, display (only) the count of employees and their average annual salary only.

g. Display a list of client firms matched with their representatives. (Make sure to display all the fields pertaining to these clients and employees, but do not display any field twice.)

h. Display a list of client firms matched with their representatives and sorted by the representatives' employee IDs. Show only the firm name, the representative's first and last names, and the representative's ID.

i. For those employees who are client representatives, list (in alphabetical order) their full names and how many firms they are assigned.

6. Be sure to close your database when you are finished. Your database must be closed to upload it to Blackboard.