Create and Populate a Database

  

Assignment Content

Don't use plagiarized sources. Get Your Custom Essay on
Create and Populate a Database
Just from $13/Page
Order Essay

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

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.

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 Business Scenario

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.

The Current System Environment

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

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

Create a Database

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.

/

/

12

2

John D

9876 John Doe 456 Bishop Street Honolulu, Hawaii 96813 (808)8650990 Lloyd Johnson S99 18

3

5

4 ¤ 12.00

18 Sunglasses 2

Peter Parker A12 34

2

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 ID

Customer name Customer address Tel

John D 9876 John Doe 456 Bishop Street Honolulu, Hawaii 96813 (808)8650990
John W 5432 John Walker 124 Eugene, Oregon (808)5378942
Peter G 1896 Peter Griffin 128 Eugene, Orlando (808)4548415
Sam S 5431 Sam Smith 789 Bishop Street Honolulu, Hawaii 96813 (808)9876543
Customer Number
Customer ID Order number
John D

John W 1326
Peter G 1578
Sam S 5678
1234
Order number Order date Sales agent Sales agent number

Description Quantity

1234 12/5/18 Lloyd Johnson S99 12 Boots 2 ¤ 6.00
1234 12/5/18 Lloyd Johnson S99 18 Sunglasses 3 ¤ 12.00
1326 12/8/18 James Milner W8 5 Waistcoat 4 ¤ 12.00
1578 12/9/18 Peter Parker A12 18 Sunglasses 2 ¤ 8.00
5678 12/7/18 Peter Parker A12 34 Scarf 2 ¤ 5.00
Product number Unit price
James Milner W8
Lloyd Johnson S99
Peter Parker A12
Sales Agent Sales Agent number
Order number Order date Sales agent Product number Description Quantity Unit price
1234

Lloyd Johnson 12 Boots 2 ¤ 6.00

1234 12/5/2018 Lloyd Johnson 18 Sunglasses 3 ¤ 12.00
1326

James Milner 5 Waistcoat 4 ¤ 12.00

1578

Peter Parker 18 Sunglasses 2 ¤ 8.00

5678

Peter Parker 34 Scarf 2 ¤ 5.00

12/5/2018
12/8/2018
12/9/2018
12/7/2018
Order number Order date Customer number Customer name Customer address Tel Sales agent Sales agent number Product No Description Quantity Unit Price 1234 12/5/18 9876 John Doe 456 Bishop Street Honolulu, Hawaii 96813 (808)8650990 Lloyd Johnson S99

1326 12/8/18 5432 John Walker 124 Eugene, Oregon (808)5378942 James Milner W8 5 Waistcoat 4 ¤ 12.00
1578 12/9/18 1896 Peter Griffin 128 Eugene, Orlando (808)4548415 Peter Parker A12 18 Sunglasses 2 ¤ 8.00
5678 12/7/18 5431 Sam Smith 789 Bishop Street Honolulu, Hawaii 96813 (808)9876543 Peter Parker A12 34 Scarf 2 ¤ 5.00

12,18 Boots, Sunglasses 2,3 ¤ 18.00

What Will You Get?

We provide professional writing services to help you score straight A’s by submitting custom written assignments that mirror your guidelines.

Premium Quality

Get result-oriented writing and never worry about grades anymore. We follow the highest quality standards to make sure that you get perfect assignments.

Experienced Writers

Our writers have experience in dealing with papers of every educational level. You can surely rely on the expertise of our qualified professionals.

On-Time Delivery

Your deadline is our threshold for success and we take it very seriously. We make sure you receive your papers before your predefined time.

24/7 Customer Support

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.

Complete Confidentiality

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.

Authentic Sources

We assure you that your document will be thoroughly checked for plagiarism and grammatical errors as we use highly authentic and licit sources.

Moneyback Guarantee

Still reluctant about placing an order? Our 100% Moneyback Guarantee backs you up on rare occasions where you aren’t satisfied with the writing.

Order Tracking

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.

image

Areas of Expertise

Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.

Areas of Expertise

Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.

image

Trusted Partner of 9650+ Students for Writing

From brainstorming your paper's outline to perfecting its grammar, we perform every step carefully to make your paper worthy of A grade.

Preferred Writer

Hire your preferred writer anytime. Simply specify if you want your preferred expert to write your paper and we’ll make that happen.

Grammar Check Report

Get an elaborate and authentic grammar check report with your work to have the grammar goodness sealed in your document.

One Page Summary

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.

Plagiarism Report

You don’t have to worry about plagiarism anymore. Get a plagiarism report to certify the uniqueness of your work.

Free Features $66FREE

  • Most Qualified Writer $10FREE
  • Plagiarism Scan Report $10FREE
  • Unlimited Revisions $08FREE
  • Paper Formatting $05FREE
  • Cover Page $05FREE
  • Referencing & Bibliography $10FREE
  • Dedicated User Area $08FREE
  • 24/7 Order Tracking $05FREE
  • Periodic Email Alerts $05FREE
image

Our Services

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.

  • On-time Delivery
  • 24/7 Order Tracking
  • Access to Authentic Sources
Academic Writing

We create perfect papers according to the guidelines.

Professional Editing

We seamlessly edit out errors from your papers.

Thorough Proofreading

We thoroughly read your final draft to identify errors.

image

Delegate Your Challenging Writing Tasks to Experienced Professionals

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!

Check Out Our Sample Work

Dedication. Quality. Commitment. Punctuality

Categories
All samples
Essay (any type)
Essay (any type)
The Value of a Nursing Degree
Undergrad. (yrs 3-4)
Nursing
2
View this sample

It May Not Be Much, but It’s Honest Work!

Here is what we have achieved so far. These numbers are evidence that we go the extra mile to make your college journey successful.

0+

Happy Clients

0+

Words Written This Week

0+

Ongoing Orders

0%

Customer Satisfaction Rate
image

Process as Fine as Brewed Coffee

We have the most intuitive and minimalistic process so that you can easily place an order. Just follow a few steps to unlock success.

See How We Helped 9000+ Students Achieve Success

image

We Analyze Your Problem and Offer Customized Writing

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.

  • Clear elicitation of your requirements.
  • Customized writing as per your needs.

We Mirror Your Guidelines to Deliver Quality Services

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.

  • Proactive analysis of your writing.
  • Active communication to understand requirements.
image
image

We Handle Your Writing Tasks to Ensure Excellent Grades

We promise you excellent grades and academic excellence that you always longed for. Our writers stay in touch with you via email.

  • Thorough research and analysis for every order.
  • Deliverance of reliable writing service to improve your grades.
Place an Order Start Chat Now
image

Order your essay today and save 30% with the discount code Happy