PLEASE MAKE SURE TO FOLLOW ALL THE INSTRUCTIONS AND NOT USE ANY RESOURCES OUTSID

PLEASE MAKE SURE TO FOLLOW ALL THE INSTRUCTIONS AND NOT USE ANY RESOURCES OUTSIDE OF THE PROVIDED FILES. DOING SO WILL RESULT IN 0. ALSO, PLEASE COMPLETE ALL THE STEPS PROVIDED.
Use only the database file sent to you via messages/email. Failure to do so will result in a zero and you will be referred to the Deans office.
Student Handout
Your organization is developing and testing a database. You are asked to fix any errors in the test database—provided to you by your instructor- and improve upon it. (Note : Achieve 2nd Normal form. Ignore dependency of State and City on ZIP code). Delete any old macros in the tables that might be interfering with the creation of tables..
*. The database has no relationships defined. Primary keys may be missing or not correct.
*. Please note that all data in the database is test data and hence can be modified/replaced as needed. If a column does not exist, create it.
*. Each item belongs to a Category.
1. Enforce entity integrity for all tables (Hint: Primary Key) (Exceptions: HISTORY tables).
(0.50 pts)
2. Enforce referential integrity for all tables (Hint: Foreign Key) (Exceptions: HISTORY tables).
(0.50 pts)
3. The ORDER table in the database is not normalized and may have to be broken into two tables- ORDER and ORDERDETAILS.
(0.40 pts)
4. Each table must be in a (correct) relationship with at least one other table. (Exceptions: HISTORY tables). ( 0.10 for each relationship to a max of 1 pt)
(0.40 pts
5. Each item can be supplied by many suppliers, and each supplier can supply many items. Therefore, new tables may have to be created (Hint: Bridge tables—also known as associative entity table or junction table).
(0.20 pts)
6. Each employee can be in one or many projects. Each project can be associated with one or many employees. Therefore, new tables may have to be created (Hint: Bridge tables—also known as associative entity table or junction table).
(0.20 pts)
8. All possible values in the State column for CUSTOMERS, EMPLOYEES, and SUPPLIERS will come from the STATES table.
(0.60 pts total – 0.20 points each)
9. To further improve the database, enforce column-level integrity through field properties, input mask, validation rule, and lookup. Enforce the following rules:
a. FName and LName of EMPLOYEES and CUSTOMERS tables are required (Hint: Field properties).
(0.10 pts)
b. HomePhone and Mobile Phone of EMPLOYEES and CUSTOMERS number should be in the phone number format: (Hint: input mask).
(0.10 pts)
c. Employees can only be from the USA, Canada, or Mexico: (Hint: Validation rules).
(0.10 pts)
d. The State column in the EMPLOYEES, CUSTOMERS, and SUPPLIERS tables should contain a drop-down menu showing all states available. (Hint: lookup tab).
( Total .30 pts – 0.10 pts each)
10. Saving past addresses: When employees, customers, and suppliers change addresses, your organization wants to store their previous addresses. For this purpose, whenever there is a change in these tables, the changes need to be transferred into a history table. Use figure 1 below as reference. These history tables don’t have to be part of any relationships.
( Total 0.60 pts – 0.20 pts for each )
Close the database completely before uploading.