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:

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 customers and at least five items to the database.

$4$ marks

$4$

At least four customers with one item per customer have been added to the Sales Table.

$3$ marks

$5$

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

$4$ marks

Total

$20$ marks

$3$ HCLT$108-1-$Jan$-$June$2024-$FA$3-$IM$-$V$\mathrm{.}3-06022024$

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{.}6$ Identify and explain the relationships between the tblMajor and tblStudent, tblStudent and tblAdvisor. $(6$ marks$)$

$4$ HCLT$108-1-$Jan$-$June$2024-$FA$3-$IM$-$V$\mathrm{.}3-06022024$

The below only applies to students enrolled in the Bachelor of Accounting program.

Aligns to SAICA competencies: W$3$ Data Analytics

d$)$

Interpret the results to solve a defined business or audit problem and suggest further steps to be taken.

f$)$

Data inspection:

$($i$)$ Describe the elements of a specific business process by documenting the workflow

$($ii$)$ Define the problem to be solved and determine clear measurement priorities

$($iii$)$ Identify data sources appropriate to solving the defined problem

$($iv$)$ Evaluate the input controls responsible for ensuring that the data captured and used is valid, accurate, and complete

$($v$)$ Explain the nature, distribution, and limitations of the identified data and the population to be tested.

i$)$

Data modelling

$($i$)$ Examine the key fields using descriptive statistics to determine their characteristics and statistical parameters

$($ii$)$ Evaluate the quality of data to determine how well it supports business analysis and decision$-$making

$($iii$)$ Choose appropriate analytical methods and identify alternative approaches, taking the data characteristics and the specific analytical task into account

$($iv$)$ Apply designated quantitative techniques $($e$.$g$.,$ statistics, time series analysis, optimisation, simulations$)$ in modelling for analysis and prediction

$($v$)$ Identify relationships between data in different forms and different data sets, and build relationship models between data sets to achieve a business or audit objective W$7$ User Competencies

b$)$

Use presentation software in an accounting$/$ business context

d$)$

Use accounting software to create and view financial transactions.