$4.$ Add data to the tables with at least: $(4$ marks$)$

$10$ products

$3$ suppliers

$6$ Sales Reps $($including your instructor and each member of your team$)$

$10$ customers

$5.$ Create forms and sub$-$forms to simplify input for the PRODUCT, SUPPLIER, CUSTOMER,

SALES REP tables, and other tables that you identified in step $1.($See Access Help for

directions on creating a form with a subform.$)(4$ marks$)$

$6.$ Create at least one RENTS for each customer. Include more than one product on at least

half of these rents. $(2$ marks$)$

$7.$ Create Queries and Reports to do the following: $(8$ marks$)$

$7\mathrm{.}1.$ Provide a list of suppliers, showing the products they supply.

$7\mathrm{.}2.$ Provide a list of customers $($sorted alphabetically$)$ showing all the products leased by

that customer and the details of vendors who supplied the products.

$7\mathrm{.}3.$ Provide a list of suppliers and the number of customers who leased products supplied

by each supplier.

$7\mathrm{.}4.$ Provide a list of customers and the number of products they leased in descending

order by the number of products.

Format your Reports for these queries to give an attractive and professional look to your

business.

$8.$ Create $2$ additional queries and $2$ reports. $($These will be graded based on their complexity

and usefulness.$)(4$ marks$)$

$9.$ There are additional marks for added features and functionality $($such as input masks,

navigation buttons, summary calculations in reports, switchboard etc.$)$ to improve the

usability of your database. In an MS Word file, please provide a list of these added

features and explain how they improve the usability of your database. $(2$ marks$)$You have just been contacted by a small business called EasyRentals, which leases customers

handheld tools and small machinery for small and medium$-$sized projects such as home

renovation, basement construction, fencing, landscaping, etc. The business wants to track

products, customers, items customers borrow, vendors, products each supplier supplies, and

sales reps that rent products to customers. A supplier may supply many items, and an item may

be supplied by many suppliers. A customer may rent many products, and a rent is made by one

customer. A rent includes one or many products, a product may be included in many rents. The

business maintains several items of the same type $($make and model$)($for example, many

chainsaws of the same make and model$).$

EasyRentals maintains the following data:

SUPPLIER $($Supplier code $($unique for each vendor$),$ name, street number, street name, city,

region, postcode, email address, phone number$).$

PRODUCT $($SKU $($unique for each product$),$ name of the product, description, price, quantity on

hold, make, model, vendor code$)$

SALES REP $($sales rep number $($unique for each sales rep$),$ first name, last name, email, phone,

hired date$)$

CUSTOMER $($customer code $(($unique for each customer$),$ first name, last name, street number,

street name, city, region, postcode$)$

RENTS $($add the necessary detail based on the business rules indicated in the narration$)$

Using Microsoft Access, create the following and the additional required tables. There is a

many$-$to$-$many relationship, and you are required to break it down into two one$-$to$-$many

relationships before you create the tables in MS Access. You are also required to choose

the appropriate data type for the attributes. $(10$ marks$)($Hint: create tables in a

sequence $-$ first parents and then child tables$)$

$>$ SUPPLIER

$>$ PRODUCT

$>$ CUSTOMER

$>$ SALES REP

$>$ RENTS

Additional tables created to break the many$-$to$-$many relationships $(2$ marks$)$

Create the appropriate relationships between tables. $($Hint: $(1)$ Do not add data to the

tables before you create the relationships. $(2)$ the data type of the PK in the parent table

must be identical to the FK in the child table. For example, the supplier code in SUPPLIER,

the PK$,$ must be identical to the supplier code, which is FK$,$ in the PRODUCT table.$)(4$

marks$)$