Database Design and Development Assignment

Scenario

You have been appointed as a database developer for Mahrud Bees. The company have recently set up a new business and it currently keeps bees. The bees produce honey,

pollen and royal jelly and the bee keeper also raises “queen bees” to sell to other bee farms. Mahrud Bees produces honey to sell, alongside facial products made from the royal jelly that is produced by the bees. The owner has asked you to design and build an advanced relational database that will allow the bee keeper to record details of:

the bees
the queens
customers
staff
orders
payments
products
suppliers

Activity 1

Using the information you have been provided with and some additional external research, plan and produce a design for a complex relational database based on a response to the client brief. Normalize the data to 3rd normal form.

Produce design documentation in response to the client brief ensuring this shows the design of the tables, indexes and constraints, SQL queries, forms, macro and security measures.

LO2 Assessment criteria, 2.1, 2.2, 2.3

Activity 2

Your designs have been approved by the owner of Mahrud Bees. Implement the database using the designs you have created in activity 1. Ensure that your database includes all the features you designed in activity one plus a suitable graphical user interface.

LO3 Assessment criteria 3.1, 3.2

Activity 3

Now that the database has been implemented it needs to be tested. Explain the different testing methods that could be used to test the database and produce a test plan which identifies the tests to be carried out. This should include testing of extreme data and error handling.

Use the test plan and carry out the tests. Produce screen shot evidence to show the tests that have been carried out and explain any errors that need to be resolved.

Amend the database based on the testing.

LO3 Assessment criteria 3.3

Activity 4

Now that the database has been designed, implemented and tested reflect on the process. Produce a report which:

critically evaluates the design process and documentation that was produced for Mahrud Bees database identifying improvements with the design documentation where appropriate
reviews the database implementation process and explain what was successful and what you would do differently if you were to do this again
evaluates the role of databases within Mahrud Bees. Compare this with how larger organizations use databases
explains how Mahrud Bees can connect the database to other applications that it may use within the business
recommends to Mahrud Bees a database management system they can use to maintain the database you have created

LO4 Assessment criteria 4.1, 4.2

LO1 Assessment criteria 1.1, 1.2, 1.3, 1.4

Guidelines for assessors

The assignments submitted by learners must achieve the learning outcomes and meet the standards specified by the assessment criteria for the unit. The suggested evidence below is how learners can demonstrate that they have met the required standard.

Activity Number	Activity Criteria	Suggested Evidence
1	LO2 AC 2.1, 2.2, 2.3	The learner must develop the database design documentation for an advanced relational database for Mahrud Bees. The learner will need to review the client brief and the information provided and decide on the tables to be designed and they will normalize the data to 3^rd normal form. The design documentation should include ERDs, DFDs and data dictionaries. The learner should ensure that data dictionaries include relationships, attributes, keys, data types. The ERD will show the normalization and relationships to be created when the database is implemented. The learner will need to consider the forms, queries; reports and macros to be created to make the system user friendly and these should be designed before the database is implemented. The learner should consider house style in their design. The design should also identify the security features to be implemented when the design is created.
2	LO3 AC3.1, 3.2	The learner will create a complex database, based on the design documentation for Mahrud Bees, created in activity one. The database needs to include advanced features including database links and a graphical user interface and advanced SQL queries. The learner should submit the database for assessment. This could be supported with screen shots to explain the features used during the implementation process.
3	LO3 AC3.3	The learner will create a test plan which identifies the test to be carried out. This will cover error handling and extreme data. The learner will use the test plan to test the database. They will produce screen shots to show the tests that have been carried out and they will explain any errors that have been identified during the testing process. They will amend their database based on the testing and they will evidence this in their reflection.

LO4

AC 4.1,

4.2
LO1
AC 1.1,
1.2, 1.3,
1.4

The learner will produce a reflection report based on the design, implementation and testing of the database. They will bring together evidence from tasks one, two and three and explain how their implemented database meets the client brief.

The learner will produce a critical evaluation of the design documentation they have created, however they will also evaluate the database design process and identify areas for improvement following their evaluation.

The learner will evaluate the role of databases in organizations. They will firstly evaluate the role of the database for Mahrud Bees but they will then use wider reading and research to identify how larger organizations use databases and compare their findings.

The learner will explain how Mahrud Bees can connect their database to other applications that they may use within their business. They must use examples from other businesses to support their explanation.

The learner will research database management systems and based on their research they will recommend a database management system that would be suitable for Mahrud Bees to use to maintain their database.