Assignment 3 - Build a Relational Database in Access
Objective:
You are required to develop a small-scale relational database for a catering or food industry related company or organisation. The database should be well-designed, easy to use, and able to handle basic data management tasks.
Task:
1. Entity Relationship Diagram (ERD)
A. Identify the purpose of the database. Explain the table design in the context of your business. This should not be more than 200 words.
B. Create an ERD with the below details:
o Minimum 4 tables
o Each table has at least 4 fields, unless it is a junction table.
o Database includes at least 3 different data type
o Define relationships between tables
o Define Primary and Foreign Keys for each table
2. Create Data for Database
You may use a generative Al tool to create your data for your chosen business.
As part of using a generative Al tool, you must include the prompt, system used and results and attach it to your submission via this template. Data Task- Declaration of usage of Generative AL(Temolate) docx
This is an example of how to use the template. Data Task- Declaration of usage of Generative Al (Examolel docx
3. Create the Database
Tables Tables are the primary means of organizing and storing data in a database. You will need to create tables to hold the data you want to store in your database. Tables should be organized in a logical manner that reflects the relationships between the data as defined in the ERD.
Each table should include fields (at least 4, unless it is a junction table) that define the type of data to be stored in that table. Fields should be defined with appropriate data types, such as text, number, date/time, etc. Each field should have a unique name that clearly identifies the data it will store.
o Create a minimum of 4 tables provide relevant names to the tables, add at least 4 fields to each table unless It is a junction table.
o Ensure Database has at least 3 different data types.
o At least one table must have more than 15 records
For example, if you are creating a database to manage products of a business, you might create tables for customers, orders, products and order details.
4. Main Form. Mock Up
Mock-up a design for the main form. (do not create it in MS Access yet). The final Main form. must allow Users tol access all the reports and formms in the database.
5. Forms
Design and create two forms either built on or embedded with queries. Forms built on queries retrieve and input data from across multiple tables.
o Create the main form. with four functional buttons (using Macros created using the macro builder) for other forms and reports.
o Form. 1-allows user to input all fields in for a new record (except ID) and save. When an entry is added, al new ID is created. Sub-form. showing link to other tables is shown.
o Form. 2-allows users to select a record from a drop-down menu and displays all the record's deta'ls.l allowing thern to be updated or deleted. The correct total number of records is always shown in the forn.
6. Reports
Create two reports built on or embedded with queries. The queries must collate information from across multiple tables. The reports should be formatted and follow good design principles.
o Report 1 must include a sub-form. and a calculated field. Accurate data is collected and displayed with totals.
o Report 2 should include a data visualisation - Logo, title, trendline and legend displaying a chart with accurate data frorn the query