Analyze an extraction from an operational database

Assignment Help Database Management System

Reference no: EM131861670

Objectives:

• Analyze and clearly describe the supplied dataset within an Excel workbook

• Design a possible solution (database portion only)

• Explain your analysis and database requirements

Business situation

You are responsible for the design of a database for use in a future study of customer purchases and demographics.

You will analyze an extraction from an operational database, clean the extracted data and design a database.

The extracted data is given to you in an Excel workbook. The name of the worksheet containing the data is orders.

As you complete each task below, answer the questions that follow. Write all answers to the questions on this document.

1. Connect to the Oracle database using SQL Developer. Create a new user named Week or use existing.

2. Import the data from the Excel workbook into SQL Developer using the connection.

a. Expend the connection, click on Tables.

b. Right-click Tables and select Import Data. Browse to the Excel workbook. Name the table SUPERSTORE. Adjust column names, data types and size/precision as necessary.

c. After creating and populating the table, insert a screen clipping below that shows the result of the QUICK DDL. (Right-click the table name).

3. Begin your analysis of the dataset by executing the following commands:

a. Explain the construction of the commands, their purpose. How many rows are in each of the result sets?

4. Continue your analysis of the data set by investigating the remaining columns of the dataset. Save the sql commands that you used in your analysis in a sql file named analysis.sql. Use comments that explain the purpose of the commands.

a. Describe your analysis. Include a description of any assumptions that you make.

5. Summarize your findings in a table ... Something like this:

Attribute name	Data type	Number of distinct values if categorical	Max value if numeric	Min Value if numeric

6. Based on your analysis propose a database design.

a. List the tables in your proposed design.

b. Compare your design to some other possible relational models listed below.

For example, databases 1, 2, and 3 consist of six, five and nine tables, respectively.

Database 1: ORDERS, ORDERLINE, CUSTOMER, ADDRESS, PRODUCT, SALES

Database 2: CUSTOMER, REGION, ORDERS, TRANSACTION HISTORY, PRODUCT

Database 3: CUSTOMER, ORDERS, PRODUCT, CATEGORY, SUB_CATEGORY, SEGMENT, ADDRESS, SHIPPING, CUSTOMER_SHIPMENT

After considering alternative designs, did you modify your initial design? Why or why not?

7. Finalize your design. Create and populate the tables that you include in your final design.
It is required that one table is named ORDERS.
a. Is it necessary to name a table ORDERS instead of ORDER? Why?

b. List the tables in your database design in a table ... Something like this.8. Export the DDL of your design.

Table name	Primary key	Foreign Key(s)	Attributes
ORDERS	ORDER_ID	CUSTOMER_ID . . .	ORDER_ID CUSTOMER_id . . .

a. What is the name of the file containing the DDL?

9. Import the DDL into Data Modeler and review the resulting relational diagram.

a. Place a screen clipping of your relational diagram in the space below.

10. List at least three resources in APA style that you used to complete the tasks in this assignment.

11. List three things that you learned from this?

Attachment:- Order Data.rar

Reference no: EM131861670

Questions Cloud

Managers and for policymakers : Explain why a crisp definition of a "barrier to entry" is useful for managers and for policymakers.

Impose a martial law and to restrict movement : Indicate if the long run growth of real GDP is likely to increase or decrease. Ceteris Paribus means all else constant.

How international trade would affect industries : Summarize how international trade would affect industries in the different market structures? Be 6 pages in length, not including the title or reference pages.

Ethics and morality in the marketplace : Or, should nation-states enforce ethics and morality in the marketplace and insist that any country seeking to export its goods have in place laws

Analyze an extraction from an operational database : You will analyze an extraction from an operational database, clean the extracted data and design a database.

Calculate the equilibrium number of firms : a. Calculate the equilibrium number of firms in the U.S. and European automobile markets without trade.

Fair insurance premium : What is the actuarially fair insurance premium to cover? Genentech's loss $_______ in millions

Build an HTML page that takes in an initial investment : Build an HTML page that takes in an initial investment, interest rate, and number of periods. Interest Rate should be a pulldown with four choices

The Russian crisis-What is the firm WACC : The Russian crisis of 1998. What is the firm's WACC?

User Account

All Pages