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