This is an individual assignment. (4% of the course total mark)

Part I

1. Draw an E-R Diagram. (20%)

Draw an E-R diagram based on the description below (Use the notation learned in the lecture).

A Database is being constructed to keep track of data about UNB students’ status of job applications. A student can apply to more than one company and a company has many students who applied to the company. The UNB also keeps track of an advisor who helps each student to practice for job interviews. Each student is assigned to only one advisor and each advisor helps at least one student. For each student, the database is to hold the students’ ID (primary key), name, and an e-mail address. For each company, the database is to hold the company ID (primary key), name and address. For each advisor, the database is to hold the tutor ID (primary key), and a telephone number.

2. Convert the E-R diagram below into the corresponding relations (tables). Show the primary keys and other fields in the relation. Underline the primary keys, dotted underline the foreign key, and use arrows to indicate the foreign keys and the corresponding primary keys. (20%)

E-R Diagram

Deliverable:

Hand written paper for the part I.

BA 3672

Assignment 4: E-R and Database Assignment

Part II

Instruction:

To answer questions below, you must use the database, which can be downloaded from the D2L (DB Name: BA3672 Assignment 4 DB W 2015.mdb).

3. Queries

a) Create a query to find the customer(s) whose last name is Smith. Show the last name, first name, and customer ID. Save your query file as “query1” (10%).

b) Create a query to find the customer(s) who purchased before Jan. 15, 2003. Show the customer ID, last name, first name, and order date. Save your query file as “query2” (10%).

c) Create a query to find product(s) that have less than 3,000 units in stock. Show the product name, product ID, units in stock, and supplier company name. The list should be sorted by the units in stock in ascending order (small quantity first and large quantity last). Save your query file as “query3” (10%).

d) Create a query to find the customer(s) who have purchased a Viewsonic 17 Monitor since Dec. 30, 2002. Show the customer ID, last name, and first name, (but do not show order date and product name). The same customer name should appear only once. Save your query file as “query4” (10%).

e) Create a query that allows you to input the type of credit card of customers, and which will then give you an output list of the product names and total quantities of those products that were ordered by customers with that credit card type since Jan. 1, 2005. For example, when you run the query, the computer should ask you which type of credit card you wish to inquire about. If you input “M” for this assignment, then the computer will show you the orders that were placed by customers using the credit card type “M” since Jan. 1, 2005. The query should display the summary results by including the following two fields only: product name and quantity (i.e. total quantity of the product ordered using the credit card type “M”). The quantity should be sorted in descending order (large quantity first and small quantity last). The same product name should appear only once. Save your query file as “query5” (20%).

Deliverable:

Please submit the database file for the part 2 of the assignment 2 as an attachment via the D2L Drop Box .When saving your work, you should give a name to your Access file using your student no. and last name (e.g., 1234567_smith). You must also keep a backup copy of your file in your hard drive, USB, or a CD rom (DO NOT OPEN THE BACKUP FILE ONCE YOU HANDED IN THE ASSIGNMENT). Please make sure that your file is virus-free before you hand it in! Late submissions will receive a penalty of 5 % per day (to a maximum of zero mark for this assignment)