Question 4 (25 marks) Case: Retail Database Design You have engaged a software vendor to design a database to capture both retail outlets essential data. The key areas of your concerns are sales records, inventory update and the sales staff involved. Such database will be hosted at the Application Server at the head office where Jenny may make use of the data for business analysis from time to time. As discussed with the software vendor, you are required to provide the following details when designing the database tables: Table showing Field Names and Sample data Table showing Field Names and Data Type Primary Key and Foreign Key (if applicable otherwise specified as None) It is important that you think of good sample data to illustrate how the tables are related. The following are the tables you will need to construct and show in your Microsoft Word answer file. (Do not construct the tables using Microsoft Access; use the Insert Table function in Microsoft Word instead.) (a) Sales Details Table The purpose of this table is to record all the items purchased in each sale. The key fields are Sales Record Number, Receipt Number, Item Code, Sales Price and Quantity. Create at least FOUR (4) records for this table by entering your own data. This table should be linked to another two tables. (7 marks) (b) Sales Receipt Table The purpose of this table is to record the receipts summary of each sale. The key fields are Receipt Number, Sales Date/Time, Total Charge and Sales Staff Code. Create at least THREE (3) records for this table by entering your own data. This table should be linked to another table. (6 marks) (c) Sales Staff Table The purpose of this table is to record the sales staff involved in each sale. The key fields are Sales Staff Code, Staff Name, Contact Number, Position Type (Part Time or Full Time) and Position (Manager or Assistant). Create at least THREE (3) records for this table by entering your own data. (5 marks) (d) Inventory Table The purpose of this table is to record the items available for sales and indicate if promotional discount will be applied. The key fields are Item Code, Description, Sales Price, and Promotional Discount (if any). Create at least THREE (3) records for this table by entering your own data. (5 marks) DIPL/BUS0117/Jan2022/MainEQP Page 8 of 8 (e) Refer to the above tables designed, provide an example and explain how a table can be related to another table in a relational database design.

Please complete all question ASAP