A relational database must be designed and normalized before it can be created, populated with data, and used. Database design depends on the business scenario(s) a database architect needs to model, but all relational database designs benefit from being normalized.

Download and follow the instructions on the Normalized Database Design document.

Note: For this assignment, you will be given a business scenario. If you make changes during the normalization process (and usually this is the case) you will update your ERD to reflect the changes. Later in this course, you will be asked to implement the ERD from this week.

Normalized Database Design

Instructions:

Read the business scenario on page 3. The scenario is communicated in the form of an order form from Brewton Enterprises, Inc. Create an ERD that represents the data and data relationships associated with the business scenario. Strongly consider sketching a first draft of the ERD on paper first; following the instructions that begin with step 3 below to normalize the design; making any necessary changes to your on-paper ERD; and then creating your final ERD in a software application such as Microsoft Visio, Lucidchart, Microsoft PowerPoint, or another software tool of your choice. Fill out the normalization table on page 4.

Begin by defining un-normalized form (UNF).

Consider all of the nouns that appear in the business scenario. Decide which of these nouns should be fields in the database you will be designing. What nouns need to be identified, stored, and tracked in this particular business scenario? The nouns you identify are what you will list in the UNF column. The nouns you list in this column are all the potential field names you are considering to include in the yet-to-be-identified entity/entities.

For example, in an educational scenario, some things (nouns) that typically need to be identified/stored/tracked include student name, student ID, student standing, all of the classes a student takes, all of the teachers that teach the classes, and so on.

In the UNF column, designate any field names that seem to uniquely identify a person, place, or thing (noun) by typing an asterisk next to that field name. These are your potential keys. ID numbers (such as student IDs, order IDs, and social security numbers) that are unique to a specific individual, transaction, or other noun make good keys.

For example, field names that can hold the same value in different situations (such as John Smith or freshman) are not unique and therefore not suitable for keys.

Also in the UNF column, designate any field names that seem to represent repeating values. Use a closing parenthesis ) to designate repeating values.

For example, students typically take many classes, so in an educational scenario where student ID is a key, class name is an example of a repeating value. Because one customer can place many orders, in a business scenario where customer ID is a key, order is an example of a repeating value.

Also in the UNF column, for any group of repeating values you have identified (the values you marked with a parenthesis), identify the key, or unique identifier, for that group of repeating values. Type an asterisk next to the key associated with each group of repeating values.

For example, in an educational setting that has repeating values of class name, class ID, and class instructor, class ID would be a suitable unique identifier/key. In a business scenario that has repeating values of order, order ID would be a suitable unique identifier/key.

Apply the first normal form rule. 1NF states that each group of related field names should have a unique identifier (primary key), and each field name should represent one and only one value and contain no repeating groups. The result of applying 1NF to the field names in the UNF column will be a grouping of related fields and a repetition of one or more keys.

For example, you may find you will move all of the fields designated with a parenthesis in the UNF column to the 1NF column and then repeat the key in both columns. The key you repeat will be considered the primary key in the UNF column and the foreign key in the 1NF column. In an educational scenario, you might retain the primary key class ID in the UNF column; move all of the fields marked with a parenthesis that have to do with classes to the 1NF column; and repeat the class ID field in the 1NF column. This repetition allows you to match the value of a primary key to the value of a foreign key and relate the eventual data groups/entities/tables.

Apply the second normal form rule. 2NF states that any non-key field must be dependent on the entire primary key. Conceptually, this means that any non-key field must be able to be located uniquely, based on concatenated or compound keys if necessary. The result of applying 2NF to the field names in the 1NF column is typically groups split apart into smaller groups with additional (and repeated) keys.

Apply the second normal form rule. 2NF states that any non-key field must be dependent on the entire primary key. Conceptually, this means that any non-key field must be able to be located uniquely, based on concatenated or compound keys if necessary. The result of applying 2NF to the field names in the 1NF column is typically groups split apart into smaller groups with additional (and repeated) keys.

Identify sensible entity names for each group of fields. The result of applying this last step to the groups of related field names in the 3NF column is one entity name listed in the Entity Name field for each group.

For example, if the 3NF column contains groups of fields related to customers, orders, and sales reps, sensible entity names may be Customer, Order, and Sales Representative, respectively. If the 3NF column contains groups of fields related to students, classes, and teachers, sensible entity names might be Student, Class, and Teacher. Because each entity will eventually be implemented as a relational table, take a moment at this point to double-check that each group in the 3NF form that corresponds to an entity name (that is, each eventual table) contains one primary key. If tables are related, they must have a second foreign key that matches the primary key of the table(s) to which they relate

Brewton Enterprises, Inc. Order Form

Order number: 1234

Order Date: 5/12/18

Customer number: 9876

Customer name: John Doe

Customer address: 456 Bishop Street Honolulu, Hawaii 96813

Tel: (808) 8650990

Sales agent: Lloyd Johnson

Sales agent number: S99

Product No.

Description

Quantity

Unit Price

Normalization of Brewton Enterprises, Inc. Order Form (Brewton Business Scenario)

UNF

1NF

2NF

3NF

Entity Name