Answer the following questions based on the database you created in Question $1.$ Submit a PDF with your answers.

Question $1$

$[20$ Marks$]$

Background:

Your friend Nina's baby clothing business is growing, but sales are still recorded in a book. You have decided to showcase acquired skills using Microsoft $($MS$)$ Access databases and develop a database for the clothing shop.

You have identified several entities from your research on the operations of the clothing shop and decided to include a customer, product and sales table in your MS Access database. Your tables should include the below attributes per table:

Customer table $[$first name, last name, address, cell number and city$]$

Product table $[$product name, price, colour, size and category$]$

Sales table $[$This table is derived from the Customer and Product table$]$

Required:

Create a database in MS Access for the sales management system and upload a copy of the database on ColCampus.

Rubric:

$\backslash$table$[[$Criteria$,$Requirements,Marks$],[1,\backslash$table$[[$The student created appropriate$],[$database tables with proper columns.$]],6$ marks$],[2,$At least five attributes in each table.,$3$ marks$],[3,\backslash$table$[[$Provide at least ten customers and at$],[$least five items to the database.$]],4$ marks$],[4,\backslash$table$[[$At least four customers with one item per$],[$customer have been added to the Sales$],[$Table$.]],3$ marks$],[5,\backslash$table$[[$The correct naming convention is used,$],[$and consistency is followed.$]],4$ marks$],[,$Total,$20$ marks$]]$ Question $2$

$[30$ marks$]$

Answer the following questions based on the database you created in Question $1.$ Submit a PDF with

your answers.

$2\mathrm{.}1$ Describe the functions of each table in your database.

$(6$ marks$)$

$2\mathrm{.}2$ Create relationships between the tables in MS Access and produce a screenshot of the relationship

diagram.

$(4$ marks$)$

Please note: The relationship must be created in MS Access and should display referential integrity

between the tables.

$2\mathrm{.}3$ Query the entire customers' table$($show all attributes$)$ in MS Access of the store's database, with

the customers' last names in descending order, and add a screenshot of the result.

$(4$ marks$)$

$2\mathrm{.}4$ Query the full product table $($show all attributes$)$ in MS Access of the store's database in descending

order of product name and add a screenshot of the result.

$(4$ marks$)$

$2\mathrm{.}5$ Create a report of the customers in the database, sort the report in ascending order of first name.

and add a screenshot of the report.

$(6$ marks$)2\mathrm{.}5$ Create a report of the customers in the database, sort the report in ascending order of first name. and add a screenshot of the report.

$(6$ marks$)$

$2\mathrm{.}6$ Identify and explain the relationships between the tbIMajor and tbIStudent, tbIStudent and tblAdvisor.

$(6$ marks$)$

$2\mathrm{.}1$ Describe the functions of each table in your database. $(6$ marks$)$

$2\mathrm{.}2$ Create relationships between the tables in MS Access and produce a screenshot of the relationship diagram. $(4$ marks$)$

Please note: The relationship must be created in MS Access and should display referential integrity between the tables.

$2\mathrm{.}3$ Query the entire customers' table$($show all attributes$)$ in MS Access of the store's database, with the customers' last names in descending order, and add a screenshot of the result. $(4$ marks$)$

$2\mathrm{.}4$ Query the full product table $($show all attributes$)$ in MS Access of the store's database in descending order of product name and add a screenshot of the result. $(4$ marks$)$

$2\mathrm{.}5$ Create a report of the customers in the database, sort the report in ascending order of first name. and add a screenshot of the report. $(6$ marks$)$

$2\mathrm{.}6$ Identify and explain the relationships between the tblMajor and tblStudent, tblStudent and tblAdvisor.