Project Normalization 1. Entity Relationship Diagram of the Database. 2. SQL file which contains the script to create tables, set constraints and insertion of data. 3. Word document with screen shots of validity check of constraints. ABC Fruits is a specialty grocery store supplying a variety of boxed fruits to its customers. The entities represented with database tables are P_Customers, Porders, P stock, and P vendors. The stock table keeps track of current inventory, including quantity on-hand and current price. The vendor's table lists the wholesalers who supply ABC Fruits with items. It includes both the cost of the item to ABC Fruits (cost) and the geographical region where the vendor is located. The customers table also keeps track of the location (region) of each customer, as well as cust name and phone. 4. Team Work Question's answer. Marking Scheme: Activity Level Range Maximum Points Range 0-2 Note that in the world of ABC Fruits, a given item id may be available from more than one vendor, but each particular vendor supplies only one item-id. Although this is not usually the case in real life, we assume that ABC Fruits vendor only supplies one item id. 1. Entity Relationship Diagram Due Week 6 Monday Feb. 22. Incorrect to correct 0-2 2. Write a script to create all the tables and set up constraints based on the E-R Diagram provided in class. Errors to error free Un-normalised table with the following columns is used to store the information. (cust id, cust_name, cust_region, phone, order no_item id, quantity, descript, price, on hand, vendor id, cost, vendor region) 3. 0-2 simple to advanced ABC Fruits has set some rules that data to be entered must meet. These are as follows: Generate data and Insert it into the tables, minimum 5 rows in each table. 1st and 2nd rows of P_CUSTOMERS and P_VENDOR table must have your and your partner's name. 1. Cust name, descript cannot be blank. 2. Quantity, price and cost cannot be 0 or less 3. On hand cannot be negative. 0-2 Simple to complex Create the tables into 3NF to maintain this information as well as the Entity relationship diagram and other required tasks listed in the table below. a. Screen captures of data Integrity a. Entity integrity issues at least 2. b. Referential Integrity issues at least 2. c. Other constraints' issues like NOT NULL, CHECK, UNIQUE. Though you may find it useful to work through the normalization steps one at a time, you are only required to show your results at the end of 3NF, so only your 3NF design will be marked 0-2 No-excellent team work 1NE: (optional) 4. Team work: answer the following question: a. What issues you had to face between team members during the project? How did you resolve them? Or b. If you did not have any issue, how did you manage the team so there will be no issues? 2NF: (optional) 3NF: (Mandatory) ERD (Mandatory) Script (Mandatory) Integrity Issues' Screen captures (Mandatory) What you are submitting: Project Normalization 1. Entity Relationship Diagram of the Database. 2. SQL file which contains the script to create tables, set constraints and insertion of data. 3. Word document with screen shots of validity check of constraints. ABC Fruits is a specialty grocery store supplying a variety of boxed fruits to its customers. The entities represented with database tables are P_Customers, Porders, P stock, and P vendors. The stock table keeps track of current inventory, including quantity on-hand and current price. The vendor's table lists the wholesalers who supply ABC Fruits with items. It includes both the cost of the item to ABC Fruits (cost) and the geographical region where the vendor is located. The customers table also keeps track of the location (region) of each customer, as well as cust name and phone. 4. Team Work Question's answer. Marking Scheme: Activity Level Range Maximum Points Range 0-2 Note that in the world of ABC Fruits, a given item id may be available from more than one vendor, but each particular vendor supplies only one item-id. Although this is not usually the case in real life, we assume that ABC Fruits vendor only supplies one item id. 1. Entity Relationship Diagram Due Week 6 Monday Feb. 22. Incorrect to correct 0-2 2. Write a script to create all the tables and set up constraints based on the E-R Diagram provided in class. Errors to error free Un-normalised table with the following columns is used to store the information. (cust id, cust_name, cust_region, phone, order no_item id, quantity, descript, price, on hand, vendor id, cost, vendor region) 3. 0-2 simple to advanced ABC Fruits has set some rules that data to be entered must meet. These are as follows: Generate data and Insert it into the tables, minimum 5 rows in each table. 1st and 2nd rows of P_CUSTOMERS and P_VENDOR table must have your and your partner's name. 1. Cust name, descript cannot be blank. 2. Quantity, price and cost cannot be 0 or less 3. On hand cannot be negative. 0-2 Simple to complex Create the tables into 3NF to maintain this information as well as the Entity relationship diagram and other required tasks listed in the table below. a. Screen captures of data Integrity a. Entity integrity issues at least 2. b. Referential Integrity issues at least 2. c. Other constraints' issues like NOT NULL, CHECK, UNIQUE. Though you may find it useful to work through the normalization steps one at a time, you are only required to show your results at the end of 3NF, so only your 3NF design will be marked 0-2 No-excellent team work 1NE: (optional) 4. Team work: answer the following question: a. What issues you had to face between team members during the project? How did you resolve them? Or b. If you did not have any issue, how did you manage the team so there will be no issues? 2NF: (optional) 3NF: (Mandatory) ERD (Mandatory) Script (Mandatory) Integrity Issues' Screen captures (Mandatory) What you are submitting