tim major database Assignment

Winter 2017, TIM 50, Database Assignment 2

Instructions:

Set Up Database

1. Create an empty database.

2. Click External Data -> XML File -> Browse -> Select Files, to import the xml data files one by one.

3. Rename each table after importing. Name the tables like: customer, orders, nation, region, supplier.

4. Change the datatype of each table to correct format. For example, right click the orders table, and then click Design View. In the opened view, modify data types as shown in the following screen shot. (Use datatypes: Number, Text and Date/Time)

5. Make sure all the data types are correct. If there are mistakes, you will encounter errors like “Typeerrors” later.

Questions 1. Add proper relationships among the tables. Make a screen shot of your relationship panel, and convert it to a PDF file. You must find ALL the proper relationships. (10 points)

Hint: look at the primary KEY field in each table, find the matching fields. For example, both “O_CUSTKEY” and “C_CUSTKEY” denote customers’ id. Find all similar pairs and create relationships accordingly. However, the relationship must have semantic meaning. For example, there is no relationship between “C_CUSTKEY” and “O_ORDERKEY”, because customer key and order key does not mean the same thing.

Note: when creating a relationship, please use the first joint type (the default option).

Convert the captured picture to a PDF file. You can do it by first pasting the picture file into MS Word, and then saving it as a PDF file.)

Finally, you should get something like the following:

2. Generate a report to show the “O_TOTALPRICE, C_CUSTKEY, C_NAME’ of the top 5 highest orders in total price. (10 points) (No need to distinguish different order status)

3. Generate a report to show the total number of customers by country. You need to add “C_NAME, C_NATIONKEY and N_NAME” in your query, and if you did everything correctly, you should see “C_NAMEOfCount, C_NATIONKEY and N_NAME” in your query result. (10 points)

4. Generate a report to show the total number of suppliers by country. Requirements are similar to Problem 3. (10 points)

5. Generate a report to show all the nations ranked by the total price orders (From highest to lowest). (15 points)

Notes: a) Total price here means the sum of order’s O_TOTALPRICE placed in a nation, e.g. Germany’s sum of O_TOTALPRICE

b) You may need to use SUM in query design form.