You are to design a database system for “The Bonsai Pot Ltd” and implement this within a Microsoft SQL Server 2012 database.
Complete the assignment tasks and present them within a written report.
1. Use normalisation techniques to normalise the data given within the ‘Invoices by Customer’ report. You must use dependency diagrams with relational schema notation to show the transformation through the normal forms.
2. Present the initial Entity-Relationship Diagram (ERD) resulting from Task 1.
3. Using the ‘Invoices by Customer’ report and the narrative below, identify the business rules and constraints for the system.
“The Bonsai Pot Ltd have one shop based in Bury St Edmunds from which it supplies Bonsai trees and accessories to its customers. They produce an invoice for each sale regardless of whether the customer purchases at the shop or requires the order to be sent by courier. The company cultivates trees on-site and a regular check is made on the number of healthy trees of each species are currently available for purchase.”
4. Present a final relational schema.
5. Present a final ERD. This diagram should take into account the business rules from Task 3, including any additional entities, attributes and relationships that have been identified. You should also make improvements to your design as required by considering elements of good database design practice (as presented in the recommended course text).
6. Write a script file that contains SQL commands to:
• Create a database that implements your design.
• Populate tables with reference and example data.
• Provide views that will prove useful to the staff when using the database.
• Include stored procedures and triggers (if required).
• Insert the completed script into your report.
The assessment is designed to assess the following learning outcomes:
LO1. Demonstrate an understanding of the core theories and concepts relating to database systems.
LO2. Demonstrate the skills required to design a database system.
LO3. Implement a database conforming to a given design.
LO4. Extract information from a database using queries in at least one commonly used query language.
LO5. Demonstrate a practical awareness of database maintenance tools and techniques.
The assessment is designed against following assessment criteria:
AC1. A range of core database theories and concepts are described and explained.
AC2. Database design skills have been correctly applied and used to produce a solution for a given scenario.
AC3. An appropriate database has been produced which implements a design.
AC4. A range of queries have been created which extract information to satisfy given requirements.
AC5. Maintenance tools and techniques have been appropriately selected and applied to a given database.
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.