LSBU Garden Design Shop: Prototype Database using MS SQL Server SQL and T-SQL

This coursework will fully or partially assess the following learning outcomes for this module.

On completion of the module you will be able to:

• Critically discuss and evaluate a broad range of data management & big data issues including data

• administration, integrity and security.

• Discuss the continuing development of big data - database technologies/ applications and the need for continued study, reflection, and development throughout a career as a database professional.

• Design, implement, query and secure database applications and Internetworking for database systems
  
  
• Develop skills for both autonomous and independent practice, including oral and written communication skills

LSBU runs a small firm that designs and landscapes gardens for its clients. Each client may own several gardens, with each garden belonging to a single client. LSBU offers a design service to its customers where it plans the layout of plants and other special features that it can incorporate into a garden. Each design is unique and is never duplicated. Not every garden owned by a client is of LSBU design, as clients have.

LSBU needs you to design, implement and demonstrate a prototype database using MS SQL Server SQL and T-SQL. Finally a dashboard based Graphical User Interface (GUI) is also requested this can be built using 3^rd party software e.g Tableau/Power BI to visualise the database content effectively.

1. Draw a complete Entity Relationship Model for the case study. Include a complete list of all entities & attributes (ensure that where appropriate you add relational features) and the attributes including all primary/foreign keys as part of your answer. (Consider carefully the attributes required to complete task 3 when you are choosing appropriate attributes for each entity).

2. Using suitable relational database software (MS SQL Server), write SQL & T-SQL scripts to set-up all tables/objects/types, including your own, well-designed test data (minimum 10 records per table), to implement your ER-Model as a relational database. You must provide printouts of all SQL for creation of tables/ objects/ types & test data.

3. Set-up and test all of the following tasks using Structured Query Language (SQL) or Procedural Language SQL (T- SQL). You must provide printouts of all SQL and T-SQL for each task AND the task output:

1. Show employee details of all sales reps including the total number of orders placed by each rep.
2. How many orders (the total number) and what total value of orders have been made by wholesale customers vs retail customers?
3. Write a trigger that automatically re-orders any plants whose quantity-in-stock level is less than 5.
4. Write a Stored Procedure that can generate a complete invoice for any customer. The invoice must include customer details, plant details & quantities purchased, labour costs, invoice totals before and after VAT has been added.

4. Create a suitable Dashboard visualisation using any 3 queries above combined into a single screen.  The dashboard demonstrates aspects of your prototype’s functionality using software e.g. Tableau / Power BI 5 Use MS Teams to record a video demo (10 mins max) of the tasks 3 and 4. During the video you demonstrate your live execution of Task 3 in SQL Server and Task 4in either Tableau or PowerBI. Supply a working link (URL) to the video in your document.