Question 2: relational databasestotal marks50

You should download the excel file (filename: BCL raw data.xls) to complete the requirements of this question. For questions 1 and 2, please include examples cited from the Balance Consultancy's Invoices' data to validate the points in your discussions. You may present your answers through MS Word.

Required:

1.Discuss the problems that may occur if all invoices from Balance Consultancy will be stored in one Uniform data table? How about problems if the number of Columns changes? (5 marks)

2.Evaluate each basic requirements in designing Balance Consultancy's relational database and discuss how each requirements can eliminate the identified problems in Question 1. (5 marks)

3.Using the normalization approach in database design, work with the provided unnormalized data (excel file you have downloaded) to create a Third Normal Form (3NF) for your relational database for Balance Consultancy Ltd. (Please provide a screen shot for each form) (3 marks)

4.Develop queries and report using MS Access based for the following requirements (you are required to take a screen shot of your query showing the relational database and the report)

a.How many sales were made for the entire year of 2020? (2 marks)

b.Who are the customers that don't need to pay GST? (2 marks)

c.How many hours of each services were rendered from 2019 to 2020?

(2 marks)

d.What are the services offered by the BCL and which service was offered most frequently? (2 marks)

e.Who are the top clients that make most use of the each services of BCL?

(3 marks)

f.Present each consultant's sales amount before tax for 2019 in descending order? (4 marks)

g.What were the total sales after tax in the year 2020? (5 marks)

h.What was the average amount of sales after tax transaction for the year 2020?(5 marks)

i.Which customers (show their names) has spent hours with V. Wilson, stating how many hours, type of services, and the total amount before and after tax they spent? (6 marks)

j.To whom and what were the services and hours that was provided by BLT under invoice INV0069? How much did the client pay after tax for each services supplied?(6 marks)