Question $1[20$ Marks$]$

Background:

You run a small bakery. You've been using a recipe binder and a notebook to track sales, but it has become cumbersome and confusing. You recently learned about Microsoft $($MS$)$ Access databases and want to showcase your new skills by creating a system to make your bakery more efficient.

You have identified several entities from your research on the operations of the bakery shop and decided to include a customer, product, and sales table in your MS Access database.

Required:

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

Rubric:

Criteria

Requirements

Marks

$1$

The student created appropriate database tables with proper columns.

$6$ marks

$2$

At least five attributes in each table.

$3$ marks

$3$

Provide at least ten items in the inventory table and ten customers in the customer table

$4$ marks

$4$

Add an order column in the customer table and provide all customers with their own orders from the inventory table.

$2$ marks

$5$

In the Design View, ensure that the "Required" property for the order field is set to "Yes".

$1$ marks

$6$

The correct naming convention is used, and consistency is followed.

$4$ marks

Total

$20$ marks

$3$ HCLT$108-1-$July$-$Dec$2024-$FA$3-$LS$-$V$\mathrm{.}3-14072024$

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 briefly the seven $(7)$ steps in the design progress of a database. $(7$ marks$)$

$2\mathrm{.}2$ In Microsoft Access, as a relational database system, tables can be linked together through a shared field. Identify and provide explanations for the three relationships that utilise this linkage. $(6$ marks$)$

$2\mathrm{.}2$ Create a relationship between the Inventory table and the Suppliers' table and produce a screenshot of the relationship diagram. $(3$ 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 customer table $($show all attributes$)$ in MS Access of the store's database, with the order column in descending order, and add a screenshot of the result. $(4$ marks$)$

$2\mathrm{.}4$ Create a tabular form of the entire Inventory table $($show all attributes$).$ Change the Inventory data label font and size to Arial Black, $24.$ Add a screenshot of the result. $(4$ marks$)$

$2\mathrm{.}5$ Create a report of the suppliers' table in the database $($excluding supplierID$),$ sort the report in ascending order of item name, and add a screenshot of the report.