Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

MIS310  Assignment 2: Database Assignment --- 15 points

Get started ASAP, it will take longer than you expect.   

Due Date: As posted on Canvas,  Points will be deducted for late submission.   

#1 Access software: We need to use Microsoft Access 2007 or higher version to complete this project.  Access is installed on most of computers at CI computer labs and you can also borrow one from the CI  Library.     It can also be accessed via Virtual Lab (Please refer to a separate posted page “Using Microsoft Access via Virtual Lab” in this Module regarding how to use Virtual Lab to use Microsoft Access for both Mac and Windows users.)

#2 How to get started.  Check out the video posted on Canvas, it will help tremendously.

Get access to the assignment under Assignments   

 

1. Download the Northwind database (listed as an item under the Database and SQL Module) and rename it with the following naming convention:
Rename your database Northwind_LastName_FirstName.mdb

By repalcing  LastName and FirstName with your own last name and first name such as Northwind_Chen_Minder.mdb (don’t change the file extension .mdb  If you save the database in new Access format .accdb after working on it, it is acceptable.) 

2. Open the database, under SECURITY WARNING (see figure below).  Click Enable Content to get started.  

 

 Start working on the predefined dummy queries by modifying them with real queries.  Save these queries after you are done under the same query name.  Q14 is not a query, you need to perform 4 tasks: (1) create a department table, (2) a simple data entry form, (3) a relationship, and (4) a master-detail form.   Submit the completed database by first close the database (no need to Zip it) via Canvas system's: Assignment 2: Database Assignment under Assignments area.

You should be seeing Q01… to Q13, and Your Final Score queries under Queries.
Don't forget there is Q14 which is not a query but needs to be done.

1. All the queries have been created for you with a dummy SELECT statement.  These queries have the following naming convention:

QNN_MeaningfulNameOfYourQuery where NN is the question number, 01, 02, 03, 04, etc.   Such as

Q01_TotalNumberOf_USA_Customers
Q02_MostExpensiveProducts

2. Double click on an existing query that you are working on.   You will see the default “dummy” result of the query showing up.   

   

3. Fist make sure that you select the Home Tab (The first tab).  Click on the View Icon at the top-right corner to select either SQL View and Design View to modify the query such that it will generate the correct query result.   Remember to Save the query under the same name (use Save, instead of Save As) when you are done.

When you close the Query window, if you have not saved your changes, the following dialog box show up.  Click Yes to save your asnwer.

 

4. Submit the completed northwind database via Canvas under Assignments area under Assignment 2: Database Assignment,

5. You will receive 0.8  for each question from Q1~Q13  if you have the correct answer.  Question 14 counts for 2.6 points.  Q12 is an UPDATE statement, not a typical query.  

6. Grading: You will receive the graded database back to your assignment and then you can run the “Your Final Score” query in the database to find out your final score.  The grading result and instructor’s comment can be found in the Grading table. 

  NorthWind Scenerio:

The database contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from all around the world.

· Suppliers: Suppliers' names, addresses, phone numbers and hyperlinks to home pages.

· Products: Product names, suppliers, prices and units in stock.

· Categories: Categories of Northwind products.

· Orders: Customer name, order date and freight charge for each order.

· Order Details: Details on products, quantities and prices for each order in the Orders table.

· Employees: Employees' names, titles and personal information.

· Customers: Customers' names, addresses and phone numbers.

· Shippers: Shippers' names and phone numbers.

 

===   Beginning of the Assignment Questions ====

Q1 to Q13: 0.8 point per Question 0.9*13=11.7 points
=== Read the requirements and Hint for each query/question carefully.

Q1: List all the customers from United Kingdom with Company Name and Country columns.   Sorted by Company Name in ascending order.

Hint: You need to find out the country code for United Kingdom by studying the country coulumn of the Customers table.

Q2. How many customers are located in France? Your query should return just a number representing the number of customers who are located in France. Define a meaningful alias for the resulting column so that the heading says “The total number of customers in “France”.

Q3: How many unique titles do we have in the employee table?

Hint: You need to create TWO queries,

1. The first query returned all the unique titles, i.e.,  Q03_UniqueTitles (use the Distinct keyword and use the SQL View to type in the SQL statement directly (Design View will not work).   Please refer to SQL_MIS310 slide#105~107 for guidance.  You cannot use Distinct and Count together in Access and this is why you have to do it in two related two queries.).  The second query is a query against the first query (i.e., Q03_UniqueTitles) and count the number of unique titles (Use the aggregate function Count) and the query should return a number.

2. The second query is named Q03_UniqueTitlles_Count and it has been created for you already.  You should use Q03_UniqueTitles (not a table) to create this query. This query should return a result that is a simple number.    

Hint: See SQL slide#105~107

Q4: List all current products (the products that are not discontinued – Discontinued is False to exclude discontinued products) from the most expensive products to the least expensive ones.  

Requirements: Your query should return all products that are not discontinued and sorted by price from highest  to the lowest.   The query result should contain Product id, product name, UnitPrice, and discontinued.

 

To select only active products, you need to set the criteria under Discontinued column to False (no quotation) – Discontinued is a Boolean data type.  This will allow you to exclude all discontinued products)   

Q5: List all the products by its product ID, Product Name, and UnitPrice (has to be in this sequence).  Sort the query result first by UnitPrice in DESCENDING order, and then by Product Name in ASCENDING order.

Hint: See SQL slide#83 & 84

Q6: List all products with the price range between 30 to 50 (>= 30 and <= 50), and Price (sorted by price in Ascending order).        List productID, ProductName, UnitPrice, and CategoryName of the product.

Hint: You need to JOIN the Product table with the Category table using the cetagroyID in both tables in order to display categoryName (Not CatgoryID) in the query result.

Q7: List only those products with their inventory below the reordering level.  Please list the product ID, Product Name, UnitsInStock, ReorderLevel, and the shortage amount (sorted by Shortage amount in Descending  order).  

ShoratgeAmount is a calculated column and it is defined as   

              ShortageAmount: ReorderLevel - UnitsInStock

 Hint: Only list products that that have    ShortageAmount > 0

Q8:  Please find out all the products with product name start with “Ch”  List the query result with  Product Name, UnitPrice, and UnitsInStock and Sorted by ProductName.  

Hint: See SQL slide#76 è use Ch*

Q9. Total Sales for each customer in October 1996 (based on OrderDate).   Show the result in CustomerID, CompanyName, and [total sales], sorted in [total sales] in Decending order.

Hint: [total sales] is a calculated field.   Limit the query results to  order date that is during October 1996.  You need to JOIN customers, order, and [order details]  tables.  Use the following critera to limit OrderDate

between #10/01/1996# and #10/31/1996#    

ç  Please [Choose WHERE at the TOTAL Row under OrderDate] and please note "between" is a keywword and is part of the criteria.

for October  1996 sales data. [chooset WHERE at the TOTAL Row]

[Total Sales] is a calculated field defined as the following (copy and paste it to prevent typing error):

Total Sales:  CCur(Sum(([UnitPrice]*[Quantity])*(1-[discount])))

è [choose EXPRESSION at the TOTAL Row] for this calculated column

If you use the following alternative formula

Total Sales: CCur(([UnitPrice]*[Quantity])*(1-[discount]))

è [choose SUM at the TOTAL Row]

Q10: List all the products total sales amount from 1997?

List the query result by ProductID, ProductName, and Annual Sales Amount for 1997 and sorted it by Annual Sales in Descending order (the annual sales is a calculated column).

Hint:  

1. You need to join several tables to answer this question. (Products, Orders, and [Order Details])

2. Use the critera for OrderDate

between #01/01/1997# and #12/31/1997#

to get the annual sales figure from order table and [order details] table.  

3. [Annual Sales] is a calculated column.  You need to use the aggregate function Sum( ) to calculate the annual sales for each product, i.e., the sum of the subtotal (extended price) of an order item for a product ordered in an order.  The calculated column is defined as:

CCur(Sum([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])))

*** You have to use [Order Details].[UnitPrice] instead of [UnitPrice] because there are two UnitPrice colum in Products table and  [Order Details] table.  Please note that there is a space character in the  [Order Details]

           The result will be displayed in currency format and to 2 decimal places.  

You need to use Group By at the TOTAL Row for ProductID and ProductName  

Q11: Write a SQL Update Statement to change the Compnay Name of the shipper #2 (ShipperID = 2) from 'United Package' to 'DHL'  You need to save the query as Q11_UpdateShipperName.   The best way to do this is to use SQL View of the query to write the SQL Update statement directly.   When you run it, you are going to be prompted with messageBox telling you a record will be changed.  Say YES, and no query result will be retruned because this is not a read only query.

Hint: See SQL slide#92

Q12: Show the two most expensive products that are less than 90 dollars with ProductID, Product Name, and Unit Price and remerber to sort the result by Unit Price in Decending order.

Hint: Use Select Top 2  ……  to get the top 2 records.   (Do this under SQL View) See SQL slide#82   You need to type Top 2 in the Select statement

Q13: What is the average product price for products in each product category? Display category ID, Category name, and average price in each category.     Sort by the average price in Decending order.

Hint: Join the Products and Categories tables and GROUP BY Category ID and Categories Name and use the aggregate function AVG for average

== The last Question Q14 is not a query, but has to be done ==

Q14: (3.3 points)   There are 4 questions that are not queries.  You need to create a table, enter some data for the table, create a relationship, and create two forms.  

Hint: Watch Create Tables and Forms http://www.youtube.com/watch?v=AH3ilFm_C88

a. (Add a new table called Department based on the following information (1 point):

Table name: Department

Columns:

DepartmentID: Text data type with 5 characters in length

Name:  Text data type with 50 characters in length

Define DepartmentID as the primary key of this table.  Please enter the following three records into the newly created database table.

DepartmentID	Name
ECON	Economics
MIS	Management information system
BUS	Business

b. (1) Create a new form called Department and then (2) enter Department information into the Department table.   Using the columnar format for the form.  The form should look like the following, and please enter three department  names (make up these Department’s ID and names by yourself) into the table.  (1 points)

 

c. Create a relastionship:  You need to add a new column called DeptID  in the Employee table, it will be the foreign key in this table, it should be text data type and 5 characters in length.  Make sure you enter one of the corresponding departmentID's values (i.e., ECON, MIS, and BUS) for this column in the Employee table for this newly created DeptId column that you have entered in the Department table previously.  Then, use the Relationships tool under Database Tools to create a relationship between the Department Table and the Employee table, i.e., connecting the two tables together via the primary key (DepartmentID in Department Table) and foreign key pair (DeptID in the Employee Table and choose Enforce Referential Integrity to set the integrity contraint between the foreign key and primary key.  (0.7 point)

d. Create a master-detail form called DepartmentEmployeeForm  to view and enter department’s employee data. The form should look like the following one (0.6  point).