Normalized Database Design Assignment
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.
Follow the instructions below on the Normalized Database Design document. NOTE: See Instructor's Notes at the bottom of this section for clarification of the University's requirements for this assignment!
Here's the crux of the assignment.
Fill out the last table on page 3 (the one with the UNF, 1NF, 2NF, 3NF headers) by doing the following:
1. Create an un-normalized list of entities
1. Identify nouns, which will be potential keys. Any ID numbers that uniquely identify something are potential keys.
2. Identify repeating values. A class name, for example, may appear numerous times.
3. Identify unique identifiers for the repeating values.
2. Convert to 1NF
3. Convert to 2NF
4. Convert to 3NF
5. Identify an appropriate name for the entity that will identify the 3NF column.
Normalized Database Design
Instructions:
1. Read the business scenario on page 3. The scenario is communicated in the form of an order form from Brewton Enterprises, Inc.
2. 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.
3. Fill out the normalization table on page 4.
a. Begin by defining un-normalized form (UNF).
i. 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.
ii. 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.
iii. 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.
iv. 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.
b. 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.
c. 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.
d. 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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.