ITIS$2$P$91$ SPRING $2024$

Page $1$ of $5$

Group Assignment: Database Application Development

$(40$ points and $18\%$ of Final Grade$)$

Due date: June $14,2024,$ at $11$:$55$ PM via Brightspace

This is a group assignment. Individual submission is neither accepted nor credited. Choose

a group member to upload the files. Use the following convention to name the files you submit

$($GroupName$\_$Database$).$ You can have any name for your group. Submission should be made

before the deadline using the Database link on Brightspace. You must submit one MS Access

database file. Verify that your assignment file is submitted properly. A $5\%-$mark deduction

applies to late submissions for the first three days. Submissions overdue by more than three

days will not be accepted. Wrong files will automatically receive a grade of zero.

For this assignment, you are responsible for creating a fully functional database system using

Microsoft Access. Your submission will be graded based on the database's accuracy, features,

functions, and the allocated tasks' completion. You may add features to the tables, forms,

reports, or navigation features in the database. You may make assumptions if necessary. If you

assume, you must write down all the assumptions, feature $($s$),$ and function$($s$)$ you have added

as a note in your final work.

You can use MyLab IT $($from ITIS $1$P$97)$ as a resource to learn how to develop databases

using Microsoft Access. Many materials on the Internet can also 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, and MS$-$Access $2013$ videos and tutorials.

If you do not have Microsoft Access installed on your computer, you can access MS Access

using the Brock University Virtual Labs.

About the Database

A company resells paints to individual and company customers, but the 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 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 the appropriate

datatype and size for each of the attributes. $(9$ marks$)$

ITIS$2$P$91$ SPRING $2024$

Page $2$ of $5$

$2.$ Create relationships among the tables as indicated in the ERD. $(4$ Marks$)$

$3.$ Populate the database with data 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

team member$),$ INVOICE, PRODUCTS, VENDOR, LINEITEM, and SUPPLIES tables.

$$ Create $5$ employees $($EMPLOYEE table$),$ each with at least two records in the

SALARY$\_$HISTORY table.

$$ 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 Queries that do the following: $(10$ marks$).$ Your queries will be marked

based on whether they are formulated correctly, not on the records they retrieve.

$5\mathrm{.}1.$Write a query to display the first name, last name, and email address of

employees hired from January $1,2010,$ to December $31,2020.$ Sort the output

by last name and then by first name.

$5\mathrm{.}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\mathrm{.}3.$Write a query to display the number of products in each category with a water

base.

$5\mathrm{.}4.$Write a query to display each department's department number and most

recent employee hire date. Sort the output by department number. Sample

output

ITIS$2$P$91$ SPRING $2024$

Page $3$ of $5$

$5\mathrm{.}5.$Write a query to display the SKU $($stock keeping unit$),$ description, type, base,

category, and price for all products with a PROD$\_$BASE of water and

PROD$\_$CATEGORY of sealer. Sample output

$5\mathrm{.}6.$Write a query to display the employee number, last name, first name, salary

$$from$$ date, salary end date, and salary amount for employees $83731,83745,$

and $84039.$ Sort the output by employee number and salary $$from$$ date.

Sample output

$5\mathrm{.}7.$Write a query to display all products supplied by a vendor named $$Leal Decor

Workshop$$

$5\mathrm{.}8.$Write a query to display all products purchased by a customer named $$JONAS

HOSKINS$$