Unit 17 Database Design Concept
Assignment front sheet
|Qualification||Unit number and title|
|BTEC HND Computing and Systems Development||Unit 17: Database Design Concepts (DDC)|
|Student name and number||Assessor name|
|Date issued||Completion date||Submission details|
|6March 2017||Part 1 –Via Moodle Upload 4 May 2017 23:55|
Part 2 – At Presentation 15 May 2017 Room 421 (times TBC) & via Moodle upload 15 May 23:55
|Report for Part 1 uploaded to Moodle.|
Database& database design documents to be submitted at the presentation.
|Assignment title||Manningham Car Rentals|
|Learning Outcome|| |
|Assessment Criteria||In this assessment you will have the opportunity to present evidence that shows you are able to:||Task no.|| |
|LO1||Understand databases and data management systems||1.1||Analyse the key issues and application of databases within organisational environments||Part 1|
|1.2||Critically evaluate the features and advantages of database management systems||Part 1|
|LO2||Understand database design techniques||2.1||Analyse a database developmental methodology||Part 2|
|2.2||Discuss entity-relationship modelling and normalisation||Part 2|
|LO3||Be able to design, create and document databases||3.1||Apply the database developmental cycle to a given data set||Part 2|
|3.2||Design a fully functional database (containing at least four inter-relational tables) including user interface||Part 2|
|3.3||Evaluate the effectiveness of the database solution and suggest methods of improvement||Part 2|
|3.4||Provide supporting user and technical documentation.||Part 2|
In addition to the above pass criteria, this assignment gives you the opportunity to submit evidence in order to achieve the following Merit and Distinction grades:
|Grade Descriptor||Indicative Characteristics||Contextualisation|
|M1||Identify and apply strategies to find appropriate solutions||· Effective judgements have been made|
· an effective approach to study and research has been applied
|To achieve M1, you must demonstrate effective time management strategies for completing this assignment. This includes submitting your work for this assignment in accordance with the completion date and submission details. Your completed report should make correct use of Harvard referencing in line with the College’s published guidelines.|
|M2||Select/design and apply appropriate methods/techniques||· A range of methods and techniques have been applied|
· The selection of methods and techniques/sources has been justified
|To achieve M2, a range of methods and techniques have been applied and justified in the design and build of your proposed network solution|
|M3||Present and communicate appropriate findings||· The appropriate structure and approach has been used|
· a range of methods of presentation have been used and technical language has been accurately used
|To achieve M3 you should produce a report that follows the presentation and layout guidelines given in the assignment brief. Your report should be written using the correct technical language that can be easily understood by an IT Professional.|
|D1||Use critical reflection to evaluate own work and justify valid conclusions||· Realistic improvements have been proposed against defined characteristics for success||To achieve D1, you should provide a constructive evaluation considering the strength, limitations and improvements of your database solution.|
|D2||Take responsibility for managing and organising|
|· Substantial activities, projects or investigations have been planned, managed and organised|
· The unforeseen has been accommodated
|To achieve D2 your completed database should be both original and independently produced. Your Database design and associated documentation should identify solutions to challenging input validation issues.|
|D3||Demonstrate convergent/lateral creative thinking||· Innovation and creative thought have been applied|
· Problems have been solved
· convergent and lateral thinking have been applied
|To achieve D3, the design and development of your database switchboard and forms should show evidence of creativity and innovation. Your database should use a range of challenging queries that produce key business information|
|Unit number and title||Unit 17: Database Design Concepts|
|Qualification||BTEC HND Computing and Systems Development|
|Start date||6 March 2017|
|Deadline/hand-in||Part 1 – Via Moodle Upload 4 May 2017 23:55|
Part 2 – At Presentation 15 May 2017 (times TBC) & via Moodle upload 15 May 23:55
|Assignment title||Manningham Car Rentals|
|Purpose of this assignment |
The aim of this assignment is to enable you to evidence an understanding of the database development lifecycle focusing on analysis, application of database design concepts in the design, build, testing and documenting of a fully functional database. You are also expected to understand and demonstrate awareness of the theoretical and background aspects of databases and database development.
|Scenario –Manningham Car Rentals|
Manningham Car Rental (MCR) currently use a paper-based car rental booking system. But they now want a computer based database system.
MCRrent out various different types of cars (Small, Family, MPV, 4×4, Crossover, Hot Hatch/Sports) produced by various manufacturers (Ford, Vauxhall, Renault, Hyundai, VW, Honda, Audi).
Rentals are performed by salestaff, MCRrequire salestaff to input all details of each rental in to the system via a rental form. MCRalso require forms for entering new customer details and entering details of when the company purchases a new car for rental. MCRalso require a form to add new salestaff.MCRalso require the system to be able to update customer details. The system also needs to be able to recognise that a rental car is no longer available for rental due to being sold or scrapped.
MCR rental price list is as follows:
Small Cars: £20/day
Family Cars: £30/day
Hot Hatch/Sport Cars: £60/day
Do not allow customers who are under the age of 25 rent cars with an engine capacity over 2.0L
A discount of 10% is to be given on rentals of longer than 6 days
A discount of 25% is to be given on rentals to staff.
There is an insurance excess of £50 to pay on all rentals that is refunded on return of all undamaged rental cars.
The new system should also be able to check availability of rental cars on particular days or between particular date ranges, and provide relevant business information to the sales manager by querying the data and producing reports from those queries.
The system will need to have a user friendly switchboard from which users can access all the system’s functions from a central point that they want to use; new customer, new rental, new car, update customer, update car, new salestaff, and all queries and reports.
|PART 1 –Report|
|Task 1 (Learning outcome1) |
Undertake Research and write a report discussing the different types of databases and their strengths and weaknesses. Critically evaluate the features and advantages of modern database management systems. Describe how the three-layer model can be used to provide solutions for key organisational issues, including integrity, security, recovery, and concurrency.
|PART 2 – Build |
The database build will be uploaded to Moodle and presented to your tutor.
|Task 2 (Learning outcomes 2 and 3) |
Using the case study provided
a) Explain the purpose of entity-relationship modelling.
b) Undertake analysis of the case study, and thus identify all the entities, attributes, relationships and create an entity relationship diagram (ERD). Remember to include cardinalities and relationship names.
c) Perform the normalisation process up to 3NF. You must show your stages from un-normalised to 1NF to 2NF and to 3NF.
d) Write a brief explanation of how you carried outthe normalisation process and explain why it is important.
e) Create a data dictionary detailing all the formats for all your data items used in the database. (Use the data dictionary form attached)
Provide a series of sketches of your user interface for Manningham Car Hire system.
|Task 3 (Learning outcomes 3)|
Create a database (containing at least four inter-relational tables) based upon your design documentation. Ensure all relationships are included. Ensure the necessary validation rules and input masks have been included in the tables.Populate your database and test that the database works. Please document these tests in a test plan (to be used in task 7).
|Task 4 (Learning outcomes 3)|
Create a range of forms for the system. Create a switchboard for your database. Include as part of theuser interface a range of features such as input masks, drop-down lists, option buttons, command buttons, check boxes, macros and other user interaction objects.
|Task 5 (Learning outcomes 3)|
Prepare queries and reports for the client.
Demonstrate a minimum of three of the following queries:
a) Which is the most popular rental car type? (MPV etc)
b) Which is the most popular rental car manufacturer?
c) Who is the best performingSalestaff? (during a particular timeframe, perhaps)
d) Total cash sales (during a particular timeframe, perhaps)
e) Which cars are available in a particular date range? (or day)
f) What were the ages (age grouping per 5 years) of customers who returned damaged cars?
You may produce other relevant queries and reports as you see fit for the business.
|Task 6 (Learning outcomes 3) |
Evaluate the effectiveness of the database solution in terms of the value (benefit) the database will provide the business. Provide a set of enhancements to your current system for future development.
|Task 7 (Learning outcomes 3) |
a) Produce a technical documentation report for the client.
This should include:
i. An overview
ii. Screen shots/hard copies attached of table data, forms, queries, reports
iii. Entity Relationships diagram
iv. Results from your tests of the entire database recorded in your test plan (make use of effective screen shots)
v. Troubleshooting guide
vi. Any other facilities used
b) Produce a user guide for the client of the Manningham Care Hire hiring system.
This should include:
i. An overview
ii. Getting started guide
|BTEC HND Computing and Systems Development|| |
Unit Number and title
|Unit 17: Database Design Concepts|| |
Student name and number
|Criteria Reference||To achieve the criteria the evidence must show that the student is able to:||Achieved?|
|LO1||Understand databases and data management systems|
|1.1||Analyse the key issues and application of databases within organisational environments|
|1.2||Critically evaluate the features and advantages of database management systems|
|LO2||Understand database design techniques|
|2.1||Analyse a database developmental methodology|
|2.2||Discuss entity-relationship modelling and normalisation|
|LO3||Be able to test Be able to design, create and document databases|
|3.1||Apply the database developmental cycle to a given data set|
|3.2||Design a fully functional database (containing at least four inter-relational tables) including user interface|
|3.3||Evaluate the effectiveness of the database solution and suggest methods of improvement|
|3.4||Provide supporting user and technical documentation.|
|Higher Grade achievements (where applicable)|
| Grade descriptor|
|Grade descriptor|| |
|M1: Identify and apply strategies to find appropriate solutions|
|D1: Use critical reflection to evaluate own work and justify valid conclusions|
|M2: Select / design and apply appropriate methods / techniques|
|D2: Take responsibility for managing and organising activities|
|M3: Present and communicate appropriate findings|
|D3: Demonstrate convergent/lateral/creative thinking|
|Formative Feedback: Assessor to Student|
|Feedback: Student to Assessor|
| Assessor signature |
| Student signature |