1st Scenario

You have recently been appointed as database designer for HoneyBee in Stafford.  HoneyBee Stafford is a sole trader, which has setup and is selling honey to customers in the local area. The business has been doing really well and until now it has recorded sales and customer information by hand. However the sales are increasing and the owner now needs to start tracking customer details, sales, bees, hives and batches of honey. The owner of HoneyBee has asked you to design a relational database that will store and record the business information.

-Activity 1

Using the scenario, produce design documentation for a relational database for HoneyBee Stafford. Your design documentation should include ERD, Normalisation and data dictionaries for each of the tables you feel should be created in the database. Include security features to protect the data and include data integrity features to help users with data entry, including database administration.

Help: The learners need to produce design documentation for HoneyBee Stafford. As a minimum they should include ERD, normalisation, data dictionaries and logical vsphysical design documentation. They should consider what the business will need in terms of data requirements and the different types of data that will be required within the different tables to ensure effective data management.  Normalisation should be carried out to at least 3NF and ERD documents should be annotated correctly considering cardinality and ordinality. The learners will use these designs to support their arguments in their report in activity 2 and therefore they need to be complex enough to meet the requirements

-Activity 2

Produce a report to the owner of HoneyBee Stafford which explains:

• the relationships within the database
• normalisation and functional dependency within the database
• the data integrity and security controls you have designed
• integrity constraints within relational models
• how the design documents meet the design brief

In addition your report should evaluate how the database design meets the brief, following the feedback you have received from activity 1

Help: The learners will produce a comprehensive report which meets the standards set by the criteria for this task. Using the design documentation from activity 2, learners will explain their design documentation in detail throughout the report. They will use their design documentation to support their report and this can be included as screen shots or appendices.

The learner will explain the relationship within the database and refer to their ERD. They will explain why they have made certain joins/relationships and the keys they have used.

The learner will explain the integrity constraints they have considered throughout the design process and they will show the normalisation process from UNF to 3NF fully and correctly. The student will discuss the normalisation process they have been through and normal forms.

The learner will explain the security controls they have considered and included in their design documentation along with the database administration that would be implemented if the database is to be created.

The learner will explain how their design documentation meets the owner’s requirements and they will include an evaluation of their design documentation.

2nd 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 3

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. Normalise 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

Help:

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 normalise the data to 3rd 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 normalisation 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.

-Activity 4

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

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.

-Activity 5

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 organisations 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

Help: 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.

Activity 6

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 organisations 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

Help: 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 organisations. 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 organisations 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.