Normalization of Product Report Paper

 Normalization of Product Report Paper

ORDER CUSTOM, PLAGIARISM-FREE PAPERS ON Normalization of Product Report Paper

tables are not set up properly, you will have many more challenges when interacting with the database with your application. Multiple steps are involved to normalize the database to third normal form (3NF). Using any simple source document, identify the data fields and go through the normalization process, explaining each step to describe your thought process. Note that a source document may not provide any business rules, so you will need to state any assumptions you make through the normalization steps. Make your initial post by completing the following:

  • Provide the list of data fields from the source document.
  • List the normal forms up to 3NF.
  • Explain the steps and reasoning behind each normal form.
  • Display the entities, attributes and relationships at each normal form.Normalization of Product Report Paper

 

DATA MODELING AND DESIGN 1 Data Modeling and Design Learner’s Name Capella University ITEC5020 Application and Database Development Data Modeling and Design August 2020 Copyright ©2020 Capella University. Copy and distribution of this document are prohibited. DATA MODELING AND DESIGN 2 In this example, we will review various source documents related to an order entry system and identify the data fields that should be present in the order entry system. These data fields can be converted to a database format by eliminating redundant data fields. We will review various source documents such as the “Add Customer” document, “Categories and Product Report” document, “Place Order” document, and “Order Details Report” document. First, let us analyze the “Add Customer” document. The fields present in the document are as follows: • • • • • • • • First Name Last Name Address City State Zip Phone Email Next, we will consolidate these data fields. We can create a database table called “customer,” which will contain the fields present in the “Add Customer” document. This table should have a primary key that uniquely identifies each row in the table “customer.” Among the fields, first name, last name, address, city, state, or zip cannot be considered as the primary key because two customers might have the same first name, last name, address, city, state, or zip. Phone and email are unique to each customer. However, considering the size of these fields, they should not be declared as primary keys. To solve the issue of the primary key, each customer can be assigned a unique id. The “customer” table will look something like the following: id first_name last_name address city state zip phone email 1 Harry Smith Los Angeles California 90086 3578076 harrysmith@abc.com 2 Kane Williams 107 Pega Street 897 Harwin Street Miami Florida 33120 2319065 kanewilliams@abc.com Table “customer” The “customer” table does not contain any redundant data. Hence, this table should not be normalized. The “id” of a customer will be an integer value.
However, we will declare the data type of “id” as “serial.” The advantage of using “serial” data type is that when a new customer is added, his/her “id” will be automatically generated. Suppose there are three customers. When a new customer is added, his/her “id” will be 4. For the rest of the fields, we will declare the data type as “varchar.” The data type “varchar” is used when the size of the data entered in a column is not fixed. Now, let’s see the data types and size of the fields of “customer” table: Copyright ©2020 Capella University. Copy and distribution of this document are prohibited. DATA MODELING AND DESIGN Fields id (primary key) first_name Data Type serial varchar last_name varchar address varchar city varchar state varchar zip varchar phone varchar email varchar 3 Data Size 4 bytes The field can hold a maximum of 20 characters. The field can hold a maximum of 20 characters. The field can hold a maximum of 200 characters. The field can hold a maximum of 50 characters. The field can hold a maximum of 50 characters. The field can hold a maximum of 10 characters. The field can hold a maximum of 12 characters. The field can hold a maximum of 50 characters. Next, let us analyze the “Product Report” document. The fields present in the report are as follows: • • category_name product_name We can create the table “product and category” considering the fields present in the “Product and Categories Report” document as follows: category_name Bedroom Dining product_name Krys solid wood king-size bed, Olivia solid wood queen-size bed Kelly solid wood dining chair, Imara solid wood dining table We can see that one category of furniture can have more than one product. In that case, the table “product and category” has a multi-valued field “product_name.” Hence, this table is not normalized. We will have to convert this table to the first normal form (1NF) so that one field contains a single value. The following table is in 1NF: category_name Bedroom Bedroom Dining Dining product_name Krys solid wood king-size bed Olivia solid wood queen-size bed Kelly solid wood dining chair Imara solid wood dining table The primary key of the “product_and_category” table is a composite key that contains the fields “category_name” and “product_name.” The table also contains redundant data. A database must have a Copyright ©2020 Capella University. Copy and distribution of this document are prohibited.
DATA MODELING AND DESIGN 4 single-column primary key in the second normal form (2NF). Hence, the table “product and category” is not in 2NF. We must split the table into two different tables: “category” and “product.” Let us assign a unique id to each category that will be the primary key of the table “category.” The “category” table will look something like the following: id 1 2 category_name Bedroom Dining Table “category” The “category” does not contain any redundant data; hence, it should not be normalized again. Now, let’s see the data types and size of the fields of “category” table: Field id (primary key) category_name Data Type serial varchar Data Size 4 bytes The field can hold a maximum of 50 characters. We will also assign a unique id to each product that will be the primary key of the table “product.” In the “product” table, “category_key” is a foreign key that references the primary key “id” of the “category table.” The “product” table can also have some extra fields such as “product_description” and “unit_price.” The “product” table will look something like the following: id 1 product_name Krys solid wood kingsize bed 2 Olivia solid wood queen-size bed 3 Kelly solid wood dining chair product_description The dimensions (in inches) of the bed are H 36.5 × W 78 × D 82.5; the color is white; the warranty is 36 months. The dimensions (in inches) of the bed are H 39.2 × W 65.5 × D 85.5; the color is provincial teak; the warranty is 48 months. The dimensions (inches) of the dining chair are H 37 × W 17 × D 18; the color is provincial teak; the unit_price $350 category_key 1 $275 1 $40 2 Copyright ©2020 Capella University. Copy and distribution of this document are prohibited.Normalization of Product Report Paper
DATA MODELING AND DESIGN 4 5 warranty is 24 months. The dimensions (in inches) of the dining are H 45 × W 40 × D 30; the color is provincial teak; the warranty is 36 months. Imara solid wood dining table $300 2 Table “product” Now, let’s see the data types and size of the fields of “product” table: Fields id (primary key) product_name Data Type serial varchar product_description varchar unit_price varchar category_key (foreign key) int Data Size 4 bytes The field can hold a maximum of 200 characters. The field can hold a maximum of 1,000 characters. The field can hold a maximum of 10 characters. 4 bytes Now, we will analyze the “Place Order” document. The data fields present in the “Place Order” document are “customer_id” and “product_id,” and they are already present in the tables “customer” and “product,” respectively. When the “customer_id” and “product_id” are selected and the “Place Order” button is pressed, a new order will be placed. We will create a table named “order” to store information about the orders. The “order” table will have the following fields: “id,” which will uniquely identify an order; “customer_id,” which is a foreign key that references the primary key of the table “customer;” “order_date;” and “status.” The “order” table will look something like the following: id 1 2 customer_id 1 2 order_date 2018-02-15 2018-03-19 status Delivered Pending Table “order” The “order” table does not contain any redundant data. Hence, it should not be normalized. Now, let’s see the data types and size of the fields of “order” table: Field id (primary key) customer_id (foreign key) order_date Data Type serial int date Data Size 4 bytes 4 bytes 3 bytes Copyright ©2020 Capella University. Copy and distribution of this document are prohibited.
DATA MODELING AND DESIGN status 6 varchar The field can hold a maximum of 10 characters. Next, we will analyze the “Order Details Report” document. We can create a table “order_details” based on the Order Details Report with the following fields: • • • • • • • • • • • • • • order_id order_date status customer_id first_name last_name address city state zip phone email product_id quantity However, the fields “order_id, “order_date,” and “status” are already present in the “order” table. Similarly, “customer_id,” “first_name,” “last_name,” “address,” “city,” “state,” “zip,” “phone,” and “email” are already present in the “customer” table. Instead of including the fields that are already present in the “order” and the “customer” table, we can include the following fields in the “order details” table. • • • order_id, which references the primary key “id” of the table “order” product_id, which references the primary key “id” of the table “product” quantity order_id 1 2 product_id 2 4 quantity 3 2 Table “order_detail” The table “order_detail” does not contain any redundant data; hence, it should not be normalized. Now, let’s see the data types and size of the fields of “order details” table: Field order_id product_id quantity Data Type int int int Data Size 4 bytes 4 bytes 4 bytes Copyright ©2020 Capella University. Copy and distribution of this document are prohibited. ..Normalization of Product Report Paper