Part I - Table Creation (18 marks) 1. Open the Northwind database provided, save as NorthWind_Midterm_YourFirstName.accdb. 2. [2 marks] import the tblPet table from Pet.xlsx. If you cannot import, create the table manually from the data in the spreadsheet. Manually edit any field properties to be appropriate types/sizes. Note that a Pet can exist without an Owner. 3. [4 marks] Create the following tables with the properties shown. Follow the standards outlined in class. Table Name: tblOwner Table Name: tblVaccination 4. [1 mark] Add the following data to test your database. 5. [4 marks] Combo boxes a. Table: tblVaccination Field: petID should be a combo box that shows the petName, TypeOfAnimal of the tblPet concatenated. It should be sorted by petName Descendingly. The petID should be hidden but saved to the table. b. Table: tblPet Field: typeOfAnimal should be a combo box that shows the animalType. The user should be allowed to enter other types as well. The options in the combo box are: 6. [4 marks] Refer to the ER diagram below to complete the following: a. Create the appropriate relationships based on the following i. For queries involving tblowner and tblPet, show Pets whether they have an Owner or not. ii. For queries involving tblPet and tblVaccination, show Pets whether they have had vaccinations or not. b. Use the following descriptors to set referential integrity options: i. When an owner is deleted, their pets remain. ii. When a pet is deleted, its associated vaccinations are removed. iii. ID numbers do not change for these tables. c. Set the Foreign keys as required. 7. [3 marks] Add the following validation to the tblPet table: a. dateSpayedNeutered must have happened after the dateOfBirth. b. dateOfBirth must be greater than 01/01/1980. 2) NorthWind Traders wants to find all products whose CategoryName is "Confections" and their QantityPerUnit is sold as either "pieces" or "glasses". a) Display the CategoryName, Description, ProductName, and QuantityPerUnit. b) Restrict to records as described in the question. c) Save the query as qry2. [4 marks] 3) Northwind wants a list of Shippers and the Companies that they shipped product to. a) Display the ShipperID, Shipper CompanyName, CustomerID and Customer CompanyName. b) Remove duplicates if there are any. c) Save the query as qry3. [ 3 marks] 4) Create a query to calculate the ExtendedCost for each Order Detail item. a) Show the OrderID, ProductID, UnitPrice, Quantity and Discount. b) Add a field that calculates the extended cost Price*Quantity*(1-Discount). Format as currency using the field properties, not a function. c) Save as qry4a. d) Create a second query based on qry4a and any other tables required to calculate the sum of the extended costs for each order. e) Show the CustomerID, CustomerName, OrderID, Sum of ExtendedCost for all companies from Canada. f) Save as qry4b. [6 marks] 5) Create a query to show all products that have never been ordered. a) Show ProductID, ProductName along with its supplier's CompanyName. b) Save as qry 5 [3 marks] Part III - Forms (4 marks): Create a form as follows: 1. The form is based on the Employee table. 2. Note that the Employee Name is a calculated field based on the TitleOCourtesy, the LastName and the FirstName (concatenated together as shown). 3. Add a label as shown in the form header 4. Move labels and textboxes and format exactly as shown. 5. Name all of the textboxes but not the labels. 6. The form should be restricted to not allow editing, additions or deleting of records 7. Save as frmEmployee Relationship Diagram for the NorthWind_Midterm database Shipperle - CompanyName - Phone -Click to Add