DBM380 V14 Normalized Database Design

DBM380 V14 normalized Database Designdbm380 V14page 2 Of 3normalized

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.

a. 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 do you need to identify, store, and track in this particular business scenario? The nouns you identify are what you will list in the UNF column.
• 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.
• Also in the UNF column, designate any field names that seem to represent repeating values. Use a closing parenthesis “)” to designate repeating values.
• 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.

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.

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.

d. Apply the third normal form rule. 3NF requires that there be no transitive dependencies; non-key fields should depend only on the primary key.

e. Identify sensible entity names for each group of fields. Each group of related field names in the 3NF column should correspond to one entity name, such as Customer, Order, Product, etc. Double-check that each entity contains one primary key, and define relationships among entities clearly, including foreign keys where applicable.

Use your findings to develop an ERD that visually reflects these entities, attributes, and relationships, accurately representing the business scenario provided on page 3.

Sample Paper For Above instruction

In designing a normalized database for Brewton Enterprises, Inc., based on the business scenario provided, it is essential to follow a structured normalization process—from un-normalized form to third normal form—while translating the scenario data into an Entity-Relationship Diagram (ERD). This process ensures that data redundancy is minimized, dependencies are properly managed, and the integrity of the data is maintained.

Initially, we analyze all the nouns in the scenario, such as order number, order date, customer number, customer name, customer address, telephone, sales agent, product number, description, and quantity. Some of these nouns serve as potential unique identifiers, such as order number—in particular, the order number uniquely identifies each order. Therefore, in the UNF (Unnormalized Form), attributes like order number, order date, customer number, customer name, customer address, telephone, sales agent, product number, description, and quantity are listed. For example, the order number appears to be a candidate for a primary key as it uniquely distinguishes each order (Elmasri & Navathe, 2015).

In the UNF, repeating groups are identified. For instance, a single order can contain multiple products, each with its description and quantity. These multiple products associated with an order constitute a repeating group under the order entity, with 'product' attributes repeated for each product in the order. The key for this group is the 'order number,' to which dependencies relate. The designation of repeatable fields, such as multiple products per order, helps determine the normalization process. These repeating data elements are marked with parentheses, and their own dependencies are examined carefully.

Applying the first normal form (1NF) involves eliminating these repeating groups by creating separate records for each product in an order. This process may involve creating a line item table with a composite primary key consisting of order number and product number, ensuring each record is atomic and uniquely identified. Moving from UNF to 1NF creates a structure where each record contains one product per row, with foreign keys linking to the main order entity.

Next, the second normal form (2NF) involves removing partial dependencies by ensuring all non-key attributes depend on the entire primary key. If, for example, product description depends only on product number, it belongs to the product entity, separate from the order entity. Thus, the data is split into smaller tables: Customer, Order, Product, and OrderLine (or OrderItem), each with its own primary key. The primary key of OrderLine is a combined key of order number and product number, enforcing dependency on both attributes. Such splitting improves data consistency, avoids redundancy, and facilitates accurate updates (Rob & Coronel, 2009).

Advancing to third normal form (3NF) necessitates removing transitive dependencies. For instance, customer address and telephone depend solely on customer number; but if customer name depends on customer number and customer address, but these dependences are direct, the entity is well-structured. Any dependent attributes not directly related to the primary key are relocated to their entities. For example, in our scenario, the customer data becomes its entity, linked to orders via a foreign key, and product data is stored in its own entity, linked via foreign keys in the order line table.

After normalization, the logical structure is translated into an ERD with entities such as Customer, Order, Product, and OrderLine. Relationships are depicted, e.g., a Customer places many Orders, an Order contains many Products through OrderLine, and Products can be in many Orders (many-to-many relationship needing a junction table). Foreign keys are added to connect these entities, enforcing referential integrity. Properly defining these relationships ensures an efficient, normalized database design that accurately models the business operations, minimizes redundancy, and maintains data integrity.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of database systems (7th ed.). Pearson.
• Rob, P., & Coronel, C. (2009). Database systems: Design, implementation, and management (9th ed.). Cengage Learning.