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 3rdÂ 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:
- Show employee details of all sales reps including the total number of orders placed by each rep.
- How many orders (the total number) and what total value of orders have been made by wholesale customers vs retail customers?
- Write a trigger that automatically re-orders any plants whose quantity-in-stock level is less than 5.
- 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.