question for ITIS

This is a group assignment. Groups must be between 4 and ve students. Choose a team member for uploading the files. Use the following convention to name the files that you submit (GroupName_Database). You can have any name for your group. Submission should be done before the deadline using the DatabaseAsst link on Sakai. You are required to submit one MS Access database file gr group through the course website. Verify that your assignment file is submitted properly. Late submissions and wrong files will automatically receive a grade of zero. For this group assignment, please form a group and email the section number, name and ID number of each group member to Jiqing (Jessie) Bian (at JBian@brocku.ca) with other group members copied by October 20, 2020. Those of you who have not been part of any group by this date will be assigned to a group randomly. A group must have between 4 and five students. Please read the course outline for additional information about group composition. For this assignment, you are responsible for creating a fully functional database system in Microsoft ACCESS. Your submission will be graded based on the accuracy, feature and functions of the database and completion of the allocated tasks. You may add features on the tables, forms, or reports, or add navigation features to the database. You may make assumptions, if necessary. If you assume, you are required to write down all the assumptions as we\" as the feature(s) and function(s) you have added as a note in your final work. There are lots of materials on Database Management in general, and on MS Access specifically, on the Internet that help you complete the required tasks. Here are links to a tutorial and an ebook, for example, Access video training, MS Access Tutorial, Microsoft Access Tutorial, MS-Access Access 2013 videos and tutorials, Microsoft Access 2013 Step by Step ebook. You can get access to the Brock University Virtual labs in case you don't have MS Access installed on your machine Page 1 of 6 About the Database A company resells paints to customers both individual and company customers, but same data is recorded for both. The company tracks the sale of products to customers and the purchase of paints from vendors. The database keeps data on customers (CUSTOMER), sales (INVOICE), products (PRODUCTS), which products are on which invoice (LINEITEM), employees (EMPLOYEE), the salary history of each employee (SALARY_HISTORY), departments (DEPARTMENT), product brands (BRAND), vendors (VENDOR), and which vendors supply each product (SUPPLIES). Refer to the ERD diagram shown below for further information on the entities and relationships. Based on the above description and the ERD, create a database using Microsoft Access and answer the following questions: 1. Using Microsoft Access, create all the required tables. Choose appropriate datatype and size for each of the attributes. (9 marks) 2. Create relationships among the tables as indicated in the ERD. (4 Marks) 3. Populate the database as follows: (Hint: Before populating CHILD tables with data, Populate PARENT tables) (3 Marks) - Create 10 records each in the CUSTOMER (including your instructor and each member of your team), INVOICE, PRODUCTS, VENDOR, LINEITEM, SUPPLIES tables. I Create 5 employees (EMPLOYEE table) each of whom has at least two records in the SALARY_HISTORY table o Create 3 records each in the DEPARTMENT and BRAND tables 4. Create forms to simplify data entry into each of the tables. (See Access Help for directions on creating a form with a subform.) (4 marks) 5. Create QJeries that to do the following: (10 marks). Your queries will be marked based on whether the queries are formulated correct or not, not based on the records that are retrieved by the queries. 5.1.Write a query to display the first name, last name, and email address of employees hired from January 1, 2001, to December 31, 2010. Sort the output by last name and then by first name. 5.2.Write a query to display the first name, last name, phone number, title, and department number of employees who work in department number 300 or have the title \"CLERK I". Sort the output by last name and then by first name. 5.3.Write a query to display the number of products in each category that have a water base. Page 2 of 6 5.4. Write a query to display the department number and most recent employee hire date for each department. Sort the output by department number. Sample output DEPT_NUM . MOSTRECENT_HIRE 200 6/8/2001 250 12/15/2011 280 4/16/2010 300 12/12/2010 400 1/26/2011 500 4/26/2011 550 10/22/2011 600 10/2/2011 5.5. Write a query to display the SKU (stock keeping unit), description, type, base, category, and price for all products that have a PROD_BASE of water and PROD_CATEGORY of sealer. Sample output PROD_SKU . PROD_DESCRIPT PROD_TYPE . PROD_BASE . PROD_CATEGORY . PROD_PRICE . 1403-TUY Sealer, Water Based, for Concrete Floors Interior Water Sealer 42.99 5.6. Write a query to display the employee number, last name, first name, salary "from" date, salary end date, and salary amount for emplyees 83731, 83745, and 84039. Sort the output by employee number and salary "from" date. Sample output EMP_NUM . EMP_LNAME . EMP_FNAME . SAL_FROM . SAL_END . SAL_AMOUNT 83731 VARGAS SHERON 7/15/2008 7/14/2009 43740 83731 VARGAS SHERON 7/14/2009 7/13/2010 48110 83731 VARGAS SHERON 7/14/2010 7/14/2011 49550 83731 VARGAS SHERON 7/15/2011 51040 83745 SPICER DWAIN 8/2/2005 8/1/2006 56020 83745 SPICER DWAIN 8/2/2006 8/2/2007 $770 83745 SPICER DWAIN 8/3/2007 8/1/2008 63470 83745 SPICER DWAIN 8/2/2008 8/1/2009 68550 83745 SPICER DWAIN 8/1/2009 7/31/2010 71980 83745 SPICER DWAIN 8/1/2010 8/1/2011 74140 83745 SPICER DWAIN 8/2/2011 76360 84039 COLEMAN HANNAH 6/28/2008 6/27/2009 47380 84039 COLEMAN HANNAH 6/27/2009 6/26/2010 51170 84039 COLEMAN HANNAH 6/27/2010 6/27/2011 5270 84039 COLEMAN HANNAH 6/28/2011 54280 5.7. Write a query to display all products supplied by a vendor named "Leal Decor Workshop" 5.8. Write a query to display all products purchased by a customer named "JONAS HOSKINS" Page 3 of 65.9.Write a query to display the number (Dept_Nun) and name (Dept_Name) of all departments along with their manager's employee number, first name, last name and email address. 5.10. Write a query to display the details of customers (cust_code, cust_fname, and cust_lname) served by an employee named FRANKLYN STOVER. Display the first and last name of the employee in the result. . Create additional Queries (a minimum of three) which you think are relevant. (3 marks) . Create at least three reports which you think are important. (3 marks) . Overall database design features such as appearance and ease of navigation. (4 marks) CUSTOMER cust_code cust_fname BRAND cust_Iname brand _id cust_street cust_city brand_name cust_state brand_type cust_zip + cust_balance includes places INVOICE PRODUCT LINEITEM inv_num prod_sku Binv_num (FK) inv_date prod_descript cust_code (FK) contains line_num inv_total prod_sku (FK) appears on + prod_type prod_base employee_id line_qty prod_category emp_num (FK) line_price prod_price prod_qoh completes prod_min brand_id (FK) EMPLOYEE provided by Temp_num emp_fname SALARY_HISTORY SUPPLIES emp_lame Bemp_num (FK) emp_email has sal from prod_sku (FK) emp_phone vend_id (FK) emp_hiredate salend emp_title salamount emp_comm dept_num (FK) provides employs manages VENDOR DEPARTMENT vend_id dept_num vend_name dept_name vend_street dept_mail_box vend_city dept_phone vend_province emp_num (FK) vend_postcode Page 5 of 6