ITEC 5020 Capella University Data Modeling and Design Paper

attachment_1Data Modeling and Design

Learner’s Name

Capella University

ITEC5020 Application and Database Development

Data Modeling and Design

August 2020

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. ITEC 5020 Capella University Data Modeling and Design Paper

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:

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:

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:

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:

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 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:

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:

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:

Table “product”

Now, let’s see the data types and size of the fields of “product” table:

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:

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:

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

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: