DTBS-CWK-GP01
DTBS
Databases Coursework
Instructions
You are to create a database for a user
You should also prepare a report detailing:-
o user requirements,
o the design processes you have used, o evidence of the database created,
o appropriate testing,
o a self-evaluation of your experiences.
1
Detailed Specification
The User
Identify a suitable User whose business requires the use of a database, which must have at least two tables (entities).
Possible users and databases include:-
• A database of information about UK universities useful for a potential student e.g. parts of UCAS to include o Entry requirements
o Subject specifications
o Student population
o International student ratio o etc
• A database of Bellerbys College student populations and subjects e.g. parts of Progresso to include o Comparative data between the colleges
o Subject enrolment
o Student cultural backgrounds
o Academic results o etc
• A database for a Theme Park to include o Opening times and dates
o Details of attractions in the different zones e.g. rides, shows, restuarants o Booking arrangements for parties and events e.g. capacities allowed
o Staff
o etc
• One of your own choice, but check with your teacher first.
Your user can be real or imaginary, but you should be careful about using real business details or people’s
personal details, including yourself.
In your report you should include details of the user requirements e.g.
• What will the database allow the user to do when it is finished?
• What sorts, searches reports etc will the user be able to carry out?
2
DTBS-CWK-GP01
Design
Describe the data needed for your database:
• What data is needed?
• Where will you get the data from (will it be from friends, magazines, shops, own knowledge, Internet etc)?
• List the entities you are going to include in your database.
• Create an ERD diagram for your database
• List the attributes for each entity
• Explain any normalisation needed, any changes you have made after normalisation and why your resulting database is normalised
Describe the tables and the relationships you plan to use.
• How many tables (entities) are you going to include in your database?
• Draw out appropriate tables for each of the entities in your database with the field names including primary keys, data types and field size, validation rules and text etc. State or draw on how these data dictionaries are going to link. An example table:-
Field Name
Data Type
Size
Primary Key
Required
Data Validation
Validation Text
Input Mask
Membership No
Number
Integer
Yes
Name
Text
30
No
3
DTBS-CWK-GP01
Development
Describe how you built the database, including forms and reports:
Tables
• Set up your tables using design view in your RDMS and add all the features included in your data dictionary.
• Set up the relationships between the tables, ensuring each table has an appropriate primary key. Forms
• Set up your forms, including features such as navigation, house style etc
• Use a form to create a main menu. If appropriate for your database create forms using sub forms. Queries/Reports
• Describe what you are going to search for and why this is necessary to meet the user requirements.
• Set up your query in SQL.
• Set up reports using the queries you have recently created.
• Explain what each report shows and why it is necessary to meet the user requirements.
You must show sufficient evidence in your report for each of the stages shown above. This will need you to include all necessary screen shots e.g. a before and after you have made a change, screen shots of all forms, all queries and all SQL you are using. If make changes to the design you must also include appropriate before and after details e.g. screen shots if appropriate.
4
DTBS-CWK-GP01
Testing
Describe how you tested the functionality of the database, including forms and reports:
• Show that you checked the database for errors
• use a table like that below to validate your database is correct:
• If possible, get a second person to read through the data you have entered to ensure there are no errors in the data entry. They could also comment on how easy the system is to use and recommend any improvements.
Field
Test Data
Normal, Abnormal, Extreme
Expected Result
Actual Result
5
DTBS-CWK-GP01
Evaluation
Describe how you tested the functionality of the database, including forms and reports:
• Is the finished Database similar to how you wanted it to be when you first listed the user requirements or have any changes been made along the way?
• Look back at your field and table (entity) names. Did you choose these wisely (have they produced a well- structured database for your user?) or could they have been improved.
• Following your testing how easy was your system/forms/reports to use and/or navigate; state any changes you would recommend. Include any features you think work particularly well.
• What other entities / features would you have liked to add to your database to improve the end result for your user? You may assume you had no time constraints and/or other people to work on the project with you.
6
DTBS-CWK-GP01
Assessment Criteria
User Requirements and Design
Basic
Good
Excellent
1-4
5-7
8-10
Limited User Requirements have been given
Some evidence of database design
A data dictionary has been produced but may have errors
An ERD has been produced but may have errors
A detailed list of User Requirements has been given
A correct data dictionary has been produced
A correct ERD has been produced
There is evidence of normalisation however it contains some errors
User requirements have been used to determine a list of queries and reports that will be run on the database to meet the user needs
A correct and detailed data dictionary has been produced
A correct and efficient ERD has been produced
The database has been correctly normalised to third normal form
7
DTBS-CWK-GP01
Development
Basic
Good
Excellent
1-4
5-7
8-10
Data Entry Forms have been created
Queries based on one table have been created using SQL
A report has been generated from the database
There is some customisation of forms to ease data entry.
Queries based on more than one table have been produced and a reason for each query has been given. The reasons do not relate back to the user requirements.
Reports have been produced and a reason for each report has been given. The reasons do not relate back to the user requirements.
The database makes use of sub forms and main menu forms. The forms are well designed and ease data entry, all follow a house style
Complex queries have been produced and a reason for each query has been given. The reason relates back to the user requirements.
Reports have been produced and a reason for each report given. The reasons relate back to the user requirements.
8
DTBS-CWK-GP01
Testing
Basic
Good
Excellent
1-4
5-7
8-10
Some testing of the database and the results of the testing have been recorded
Most aspects of the database have been tested using a well- designed test plan and the results of the testing have been recorded.
Some improvements have been made as a result of testing and these improvements have been recorded
OR
There is an explanation of why
no improvements are needed
The database has been fully tested.
Most improvements identified from testing have been made and these improvements have been recorded and retested
OR
There is a detailed explanation
of why no improvements are needed
9
DTBS-CWK-GP01
Evaluation
Basic
Good
Excellent
1-4
5-7
8-10
The student has provided some evaluation of the database
The student has described how the database provides a solution to the user requirements.
There is little discussion on how the exercise has improved their understanding of databases
The student has evaluated the database against the user requirements
The student has explained how well the database meets the user requirements.
There is some discussion on how the exercise has improved their understanding of databases
The student has evaluated how well the database meets the user requirements (what does it do well? What could it do better?)
They have explained how any changes they have made to the database have improved the database.
There is detailed discussion on how the exercise has improved their understanding of databases
10
DTBS-CWK-GP01
Submission
Draft Submission: 24th May 2020
You will not be graded on this submission, but will receive general feedback from your tutor.
You should upload to Study Smart a Zipped file containing all relevant files. Your draft submission to Study Smart must be before midnight on the 24th May 2020 (BST) or earlier.
Final Submission: 4th June 2020
You should upload to Study Smart a single Zipped file containing all relevant files, reflecting any further development and feedback received from your tutor.
Your submission to Study Smart must be before midnight on the 4th June 2020 (BST) or earlier.
11
DTBS-CWK-GP01