Database Optimization and Normalization
Budget: $10 – $30 USD
Database
Unlimited uploads possible
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).
*. 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.
Replace Employee Kotas Jan with your name.
1. Enforce entity integrity for all tables (Hint: Primary Key) .
2. Enforce referential integrity for all tables (Hint: Foreign Key) .
3. The ORDER table in the database is not normalized and may have to be broken into two tables- ORDER and ORDERDETAILS.
4. Each table must be in a (correct) relationship with at least one other table.
5. Each item can be supplied by many suppliers, and each supplier can supplymany items. Therefore, new tables may have to be created (Hint: Bridge tables—also known as associative entity table or junction table)
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).
7. All possible values in the State column for CUSTOMERS, EMPLOYEES, and SUPPLIERS will come from the STATES table.
8. 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).
b. HomePhone and Mobile Phone of EMPLOYEES and CUSTOMERS number should be in the phone number format: (Hint: input mask).
c. Employees can only be from the USA, Canada, or Mexico: (Hint: Validation rules).
d. The State column in the EMPLOYEES, CUSTOMERS, and SUPPLIERS tables should contain a drop-down menu showing all states available. (Hint: lookup tab).
RUBRIC
20% of each day late.
Entity integrity : 10 %
Referential integrity : 10%
(correct) relationship with at least one other table: 8*5 = 40%
Order - Order Details connection: 10%
Bridge table 1:: 5%
Bridge table 2: : 5%
8a : 5%
8b : 5%
8c : 5%
8d: 5%
Unlimited uploads possible
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).
*. 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.
Replace Employee Kotas Jan with your name.
1. Enforce entity integrity for all tables (Hint: Primary Key) .
2. Enforce referential integrity for all tables (Hint: Foreign Key) .
3. The ORDER table in the database is not normalized and may have to be broken into two tables- ORDER and ORDERDETAILS.
4. Each table must be in a (correct) relationship with at least one other table.
5. Each item can be supplied by many suppliers, and each supplier can supplymany items. Therefore, new tables may have to be created (Hint: Bridge tables—also known as associative entity table or junction table)
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).
7. All possible values in the State column for CUSTOMERS, EMPLOYEES, and SUPPLIERS will come from the STATES table.
8. 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).
b. HomePhone and Mobile Phone of EMPLOYEES and CUSTOMERS number should be in the phone number format: (Hint: input mask).
c. Employees can only be from the USA, Canada, or Mexico: (Hint: Validation rules).
d. The State column in the EMPLOYEES, CUSTOMERS, and SUPPLIERS tables should contain a drop-down menu showing all states available. (Hint: lookup tab).
RUBRIC
20% of each day late.
Entity integrity : 10 %
Referential integrity : 10%
(correct) relationship with at least one other table: 8*5 = 40%
Order - Order Details connection: 10%
Bridge table 1:: 5%
Bridge table 2: : 5%
8a : 5%
8b : 5%
8c : 5%
8d: 5%