Assignment Content
Resources: “
SQL CREATE DATABASE Statement
,” “
SQL CREATE TABLE Statement
,” “
SQL PRIMARY KEY Constraint
,” “
SQL FOREIGN KEY Constraint
,” and “
SQL INSERT INTO Statement
“
For this assignment, you will create the database you designed in Week 2. (The week 2 assignment is attached.)
Use the Create a Database document that is attached and follow the instructions.
Submit the database containing all the tables you created or a Microsoft® Word file containing all the SQL statements used.
DBM/380 v14
Normalized Database Design
DBM/380 v14
Page 3 of 3
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.
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
Copyright© 2018 by University of Phoenix. All rights reserved.
Copyright© 2018 by University of Phoenix. All rights reserved.
DBM/380v14
Brewton Business Scenario
DBM/380 v14
Page 2 of 2
Brewton Enterprises, Inc. is a clothing retail company located in the city of Eugene, Oregon. The company offers an array of products with a mix of clothing styles, appealing primarily to teens and young adults. Brewton has four satellite offices: Los Angeles, New York, Houston, and Atlanta. These offices, including headquarters in Eugene, serve as distribution centers for products sold by Brewton. The company employs 240 employees as follows:
Eugene 130
Los Angeles 27
New York 30
Houston 25
Atlanta 28
Each of these locations has a warehouse manager, an administrative assistant, a human resources representative, sales and customer service coordinators, shipping and receiving personnel, and facilities maintenance personnel. All locations stock a full complement of Brewton products for delivery to purchasing customers. In addition to these functions, headquarters, located in Eugene, also provides human resources, purchasing analysts, sales and marketing personnel, accountants, systems personnel, and operations personnel.
Mark Brewton, the company’s founder, wants to implement a relational database management system (RDBMS) that would support his long-term vision in implementing comprehensive Enterprise Resource Planning (ERP) application to support the business now and in the future. He believes the business has grown to the point that such a system will streamline business processes, help control business expenditures, and better manage inventory levels, which presently do not seem to reflect accurate numbers for each distribution center. The new system will replace almost all of the computer applications currently in use in the company.
Users in the business functions of the organization use standalone software packages, each with its own database. The standalone software packages include a purchasing system, accounting and payroll applications, and a personnel-tracking system used by Human Resources. Because these applications do not share files, there are frequently situations in which employee and customer data do not match. When this happens, research must be conducted—and data updated in multiple places–to make the data consistent across all the standalone systems. The IT organization runs special programs developed to cross-compare the information and report mismatches among data files, which are then updated to reflect the correct information. The company currently does not have a disaster recovery plan or a business continuity plan to be followed in the event of a contingency situation, such as flooding or an earthquake, and management feels this situation should be addressed. The company needs to back up its databases to prevent data loss in the case of a disaster or other emergency.
Sales Department and Order Processing Form
Below is an example of the order processing form that needs to be entered into the sales system. It currently resides in a spreadsheet and the company is hoping to migrate this system to sales-specific software with an RDBMS backend.
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
Copyright© 2018 by University of Phoenix. All rights reserved.
Copyright© 2018 by University of Phoenix. All rights reserved.
DBM/380 v14
Create a Database
DBM/380 v14
Page 2 of 2
The following assignment is based on the business scenario for which you created both an entity-relationship diagram and a normalized database design in Week 2.
For this assignment, you will create multiple related tables that match your normalized database design. In other words, you will implement a physical design (an actual, usable database) based on a logical design.
Refer to the linked W3Schools.com articles “
SQL CREATE TABLE Statement
,” “
SQL PRIMARY KEY Constraint
,” “
SQL FOREIGN KEY Constraint
,” and “
SQL INSERT INTO Statement
” for help in completing this assignment.
Note: In the industry, even the most carefully thought out database designs can contain mistakes. Feel free to correct in your tables any mistakes you notice in your normalized database design. Also, note that in Microsoft® Access®, you follow the steps below to launch the SQL editor:
Figure 1. To create a SQL query in Microsoft® Access®, begin by clicking the CREATE tab.
To Complete This Assignment:
1. Use the CREATE TABLE statement to create each table in your design. Note that a table in a RDMS corresponds to an entity in an entity-relationship diagram. Recommended tables for this assignment are CUSTOMER, ORDER, ORDER_DETAIL, PRODUCT, EMPLOYEE, and STORE.
2. As part of each CREATE TABLE statement, define all of the columns, or fields, that you want each particular table to contain. Give them short, meaningful names and include constraints; that is, describe what type of data each column (field) is allowed to hold and any other constraints, such as size, range, or uniqueness.
3. Note that any field you marked as a unique identifier in your normalized database design is a key field. Key fields must be described as both UNIQUE and NOT NULL, which means a value must exist for each record and that value must be unique across all records.
4. After you have created all six tables, including relationships between the tables as appropriate (matching the primary key in one table to a foreign key in another table), use the INSERT INTO statement to insert 10 records into each of your tables. You will need to make up the data you insert into your tables. For example, to insert one record into the CUSTOMER table, you will need to invent a customer number, a customer name, and so on—one value for each of the fields you defined for the CUSTOMER table—to insert into the table.
5. To ensure that your INSERT INTO statements succeeded in populating your tables, use the SELECT statement described in Ch. 7, “Introduction to Structured Query Language,” in Database Systems: Design, Implementation, and Management.to retrieve the records you inserted. For example, to see all 10 records you inserted into the CUSTOMER table, you might apply the following SQL statement: SELECT * FROM CUSTOMER;
After you have created all six tables and populated ten records in each table, submit to the Assignment Files tab the database containing all of the tables you created, or a Microsoft® Word document listing all of the SQL statements you used.
Copyright© 2018 by University of Phoenix. All rights reserved.
Copyright© 2018 by University of Phoenix. All rights reserved.
Customer ID | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Order number | Order date | Customer number | Customer name | Customer address | Tel | Sales agent | Sales agent number | Product No | Description | Quantity | Unit Price | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
John D | 1 | 2 | 3 | 4 | 12 | 5 | 18 | 9876 | John Doe | 456 Bishop Street Honolulu, Hawaii 96813 | (808)8650990 | Lloyd Johnson | S99 | Boots | ¤ 6.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
12 | 34 | 12/5/18 | Sunglasses | ¤ 12.00 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
John W | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1326 | 12/8/18 | 5432 | John Walker | 124 Eugene, Oregon | (808)5378942 | James Milner | W8 | Waistcoat | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Peter G | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1578 | 12/9/18 | 1896 | Peter Griffin | 128 Eugene, Orlando | (808)4548415 | Peter Parker | A12 | ¤ 8.00 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sam S | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
5678 | 12/7/18 | 5431 | Sam Smith | 789 Bishop Street Honolulu, Hawaii 96813 | (808)9876543 | Scarf | ¤ 5.00 |
Customer Number |
---|
1234 | |||||
Product number | Unit price | ||
---|---|---|---|
Sales Agent | Sales Agent number |
---|---|
12/5/2018 | |
12/8/2018 | |
12/9/2018 | |
12/7/2018 |
12,18 | Boots, Sunglasses | 2,3 | ¤ 18.00 |
We provide professional writing services to help you score straight A’s by submitting custom written assignments that mirror your guidelines.
Get result-oriented writing and never worry about grades anymore. We follow the highest quality standards to make sure that you get perfect assignments.
Our writers have experience in dealing with papers of every educational level. You can surely rely on the expertise of our qualified professionals.
Your deadline is our threshold for success and we take it very seriously. We make sure you receive your papers before your predefined time.
Someone from our customer support team is always here to respond to your questions. So, hit us up if you have got any ambiguity or concern.
Sit back and relax while we help you out with writing your papers. We have an ultimate policy for keeping your personal and order-related details a secret.
We assure you that your document will be thoroughly checked for plagiarism and grammatical errors as we use highly authentic and licit sources.
Still reluctant about placing an order? Our 100% Moneyback Guarantee backs you up on rare occasions where you aren’t satisfied with the writing.
You don’t have to wait for an update for hours; you can track the progress of your order any time you want. We share the status after each step.
Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.
Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.
From brainstorming your paper's outline to perfecting its grammar, we perform every step carefully to make your paper worthy of A grade.
Hire your preferred writer anytime. Simply specify if you want your preferred expert to write your paper and we’ll make that happen.
Get an elaborate and authentic grammar check report with your work to have the grammar goodness sealed in your document.
You can purchase this feature if you want our writers to sum up your paper in the form of a concise and well-articulated summary.
You don’t have to worry about plagiarism anymore. Get a plagiarism report to certify the uniqueness of your work.
Join us for the best experience while seeking writing assistance in your college life. A good grade is all you need to boost up your academic excellence and we are all about it.
We create perfect papers according to the guidelines.
We seamlessly edit out errors from your papers.
We thoroughly read your final draft to identify errors.
Work with ultimate peace of mind because we ensure that your academic work is our responsibility and your grades are a top concern for us!
Dedication. Quality. Commitment. Punctuality
Here is what we have achieved so far. These numbers are evidence that we go the extra mile to make your college journey successful.
We have the most intuitive and minimalistic process so that you can easily place an order. Just follow a few steps to unlock success.
We understand your guidelines first before delivering any writing service. You can discuss your writing needs and we will have them evaluated by our dedicated team.
We write your papers in a standardized way. We complete your work in such a way that it turns out to be a perfect description of your guidelines.
We promise you excellent grades and academic excellence that you always longed for. Our writers stay in touch with you via email.