You have decided to open a small restaurant. Due to the pandemic and a limited budget, you will offer delivery service only at this time. You choose to start your business based on a subscription-type model. For select clients, you will offer this subscription service for $50 a week. These clients will get their choice of 2 entrees, 2 side dishes, and 2 drinks for this price.

Using the skills you have learned so far, you have decided to create a simple database to help you keep track of your clients and their orders. As you are starting a new business and are a small company at the present time, you decide to start your database with 2 tables only. You know you can add additional information to your database as you continue to grow. One table will contain the Customers and restaurant offerings, and the other table contains information on the delivery drivers. Ten customers have signed up for your subscription service at this point.

Complete the following tasks:

1. Create a database with the name (your name) Restaurant. For example, my database file will be named Barbs Restaurant.

2 Create a table called Customer Information. This table will consist of 8 fields using the structure shown in Table 1 on the next page:

You can refer to Class lesson 1 for help setting up your tables. For help with Lookup fields, refer to the class lesson for chapter 3 (page 9). Instructions for these fields to follow.

This Project will be marked out of a total of 40 marks.

Structure of the Customer Information table

1

Field Name

Data Type

Field Size

Primary Key?

Description

Customer Number

Short Text

3

Yes

Customer Number ID (Primary Key) uses the format 001, 002 ...

Full Name

Short Text

25

Address

Short Text

25

Entre Order

Lookup field

Allow Multiple values 12 Entre options (see below)

Side Order

Lookup field

Allow Multiple values 8 side dish options (see below)

Drink Order

Lookup field

Allow Multiple values 6 drink options (see below)

Order date

Date/ Time

Delivery Driver ID

Short Text

6

Delivery Driver Information

Table 1 11 Marks The lookup fields are 2 Marks each

For your Entre, side dish and drink fields, you will create a lookup field (Chapter 3 lesson notes) with the number of options specified above. You will create your own options. Example - Entre Orders:

2

Make sure you check this box at the end so you can choose more than 1 option:

Finished field:

3

Once you are done creating your fields your table will look similar to this:

3. Enter Information for 10 Customers that you create using the Customer numbers 001 through 010. Choose 2 entre, 2 side dishes and 2 drink order options for each customer (use your option boxes that you created previously). An example for 1 customer is shown above. 5 Marks

4. Create a new table named Delivery Drivers that will contain information for your employees who deliver for you. Use the information in table 2 below

Structure of the Delivery Drivers table

7 Marks (2 marks for the

Employee Status fi

eld

)

4

Field Name

Data Type

Field Size

Primary Key?

Description

Delivery Driver ID

Short Text

6

Yes

Employee Initials followed by a 2-digit number of your choice

Employee Name

Short Text

50

Address

Short Text

50

City

Short Text

25

Employee Status

Short Text

2

Full or Part Time Employee (FT or PT) This field will have a validation rule attached to it

Picture

OLE Object

Table 2

5

5. For the Employee Status field create a validation rule and text ensuring only the values PT or FT can be entered into this field. (Help for this can be found in class lesson chapter 3 Legal values) You can choose your own text an example is shown below: 1.5 marks

6. Create and fill out information for 4 Delivery Drivers for your restaurant. Once you have created this information return to your Customer Table and update your Delivery Driver ID field with employees from your Delivery

Driver table. Find 4 Pictures (It would be nice if 1 of them are of you )

and insert them in the picture field for each driver. You can use images you find online. 3 Marks

6

Example next page

Example for Delivery Driver Table:

Updated Customer Table:

7. Using the primary key field in the Delivery Driver table specify referential integrity (Relationship) between the Delivery Driver Table (The 1 table) and the Customer Table (the many). We are ensuring no delivery Driver ID can be entered into the Customer table if it is not in the Delivery Driver Table. Click the Cascade Update Related fields but not the Delete. (Chapter 3 Lesson notes) 1 Mark

7

8. Using all the fields in the Customer Information Table create a query returning information for Customer 001. Call your query Customer 001. (Chapter 2 notes)

1 Mark

Example:

9. Create a Crosstab query (Customer Information Table) using Delivery Driver ID as the row heading, Full Name as the column heading, and a count of the Customer ID. Save it as Customer Information_crosstab. (Chapter 2 lesson notes) 2 Marks

Example Output

8

10. Create a Query that counts the number of deliveries each Delivery driver Performed. Include the Driver ID and the Employee Name Field from the Delivery Drivers Table. Assign the Caption Number of Deliveries to the Customer Number field using the Property Sheet. Save Query as Number of Deliveries by driver

(Chapter 2 lesson notes Calculating Statistics) 2 Marks

Hint: Use Query Design Add 2 tables. Use Total : Count from the Customer information table/Customer Number field to get the Number of Deliveries Performed by each Delivery Driver.

Example Output

hint

9

11. Display the top 2 Delivery Drivers from the Number of Deliveries by driver query. Save Query as Number of Deliveries by driver top 2 (Chapter 2 lesson notestop value query) 1.5 Marks

Create a basic form based on your Customer Information Table. Call your Form Customer Order Form. We can use this form in the future (in form view) to change the order options and dates for the Customers. 1.5 Marks

10

11. Create a form in design view based on the Delivery Driver Table. Use all of the fields. The picture below is an example only. Design and format your form as you would like. Call your form Delivery Drivers. You can find help for this in the Chapter 5 class lesson notes 3.5 Marks

12. Submit your completed Database assignment on blackboard. Please double check your work as this project is worth 30% of your grade.

1