Download Database Design Assignment for BTEC Level 3 Extended Diploma in IT - Unit 18 and more Schemes and Mind Maps Design in PDF only on Docsity!
Assignment brief – QCF BTEC
Qualification Unit number and title
BTEC Level 3 Extended Diploma for IT
Practitioners
Unit 18 Database Design
Student name Assessor name
Date issued Hand in deadline Submitted on
23 rd^ September 2016 18 th^ November 2016
Assignment number and title Database Design and Implementation
In this assessment you will have opportunities to provide evidence against the following criteria. Indicate the page numbers where the evidence can be found.
Criteria reference
To achieve the criteria, the evidence must show that the student is
able to: Task number
P1 explain the features of a relational database
P design a relational database for a specified user need
P create and populate a database
P4 create features in data entry forms to ensure validity and integrity of data
P perform queries using multiple tables and multiple criteria
P include an advanced feature in a database design
P7 test a relational database
M
explain referential integrity and the purpose of primary keys in building the relationships between tables
M2 import data from an external source
M3 export data to an external source
M implement an automated function.
D discuss how potential errors in the design and construction of a database can be avoided
D2 evaluate a database against the specified user need
ASSESSMENT RECORD SHEET (QCF Level 3)Programme
BTEC Level 3 Extended Diploma in IT
Student name
Assignment number & title
Database Design and Implementation
Assessor name
Raj Singh
Unit number & title
Unit 18 Database Design
Target learningoutcomes
Issue date
rd
September 2016
Deadline date
th
November
Submission date
First Submission
TargetcriteriaRef.
Criteria
Criteriaachieved? (Yes / No)
Assessment comments
P
Explain the features of a relational database
P
Design a relational database for a specified user need
P
Create and populate a database
P
create features in data entry forms to ensure validity and integrity ofdata
P
Perform queries using multiple tables and multiple criteria
Assessor declaration
I certify that the evidence submitted for this assignment is the student’s own. The student has clearly referenced anysources used in the work. I understand that false declaration is a form of malpractice.
Assessor signature
Date
Student comments Student signature
Date
After a meeting with Mia the following requirements have been outlined:
Input requirements
The following forms will be required:
Customer form Order form Shop form Sandwich form Switchboard (This should be used to enable the user to open all forms, reports and queries that have been created)
It is important that the details are captured accurately and that good use is made of validation on all forms.
Output requirements
The following reports will be required:
Outstanding payments – this report displays all orders that still require payment
This will display all order details including: company name, address, contact number, order ID, order date, order paid, sandwich description, sandwich price and total cost
Invoice – this report displays an invoice for a particular customer
This will display all order details including: company name, address, contact number, order ID, order date, sandwich ID, sandwich description, sandwich price, discount and total cost.
NOTE: 10% discount is given to the customers when they spend over £
Both of these reports must be designed in accordance with the requirements.
Design requirements
A consistency of styling must be employed in order to create a professional image and to help users interact with the system. Consistency must extend to at least the following:
Layout inc. forms, reports and use of logos etc. Colours Naming of tables and fields
Task 1 (P1, M1, D1) In order to help Mia understand the purpose and the benefits of relational databases, write a report that will contain the following:
An explanation of the main features of relational databases and the benefits they bring to an organisation. You will need to include an explanation of how the structure of relational databases can provide efficiencies in data storage, the elimination of data redundancy and the ease by which information can be extracted from the underlying data. (P1)
Explain with examples, the concept of Referential Integrity as it applies to relational databases and how, by using primary and foreign key relationships, the integrity of a database can be maintained. (M1)
A discussion into a range of common errors that may occur in the design and implementation of a relational database. Within this discussion, you will need to explain the impact that these errors could have on the integrity of data and the users. You will also need to state how these errors can be avoided. (D1)
Task 2a (P2)
Using the information supplied in the scenario and functional requirements design a database containing at least 5 tables. You will need to provide documentary evidence that demonstrates that you have:
Carried out the normalisation process to 3rd^ Normal form and have identified at least 5 entities. Produced an Entity Relationship Diagram (ERD) Created a data dictionary that contains information about all the fields required for each table. The data dictionary should identify the data types and sizes for each field, the primary and foreign identified during the normalisation and any input validation required i.e. input masks, validation rules etc
Task 2b (P3)
Implement your designs using MS Access by creating a set of tables (min 5 tables) that have referential integrity enforced through primary/foreign key relationships to allow cascade updating and deletion of linked records. Evidence this task by taking screen shots of the design interfaces of the completed database tables and the Entity Relationship Diagram supplied by MS Access
Task 2c (M2)
Using Mia’s spreadsheet, import all the data from the spreadsheet into the relevant tables. You will need to document each step of the importation process
Task 3a (P4)
Design and implement all the forms that have been outlined in the requirements taking care to ensure consistency. Show how you have implemented any validation rules, input masks and dropdown boxes you identified in the design stage. You will also need to create an input for customer orders that contains at least one sub-form (ideally 2 sub-forms)
Task 3b (M4)
To avoid users leaving records incomplete, use VBA code on at least one of the forms to perform a presence check on all the fields to ensure that they are not left blank. If a field is left blank you will need to draw attention to this field by highlighting it and displaying an error message. Evidence this task by taking screen shots of the VBA code and interfaces, with annotations.
Task 4 (P5)
Create all queries that are required from the output specification. In addition, design and implement the queries below to enable the user to retrieve the following information:
Customer Records Customer Orders
Most popular sandwich this year Least popular sandwich
Task 5a (P2), (P6)
Design and implement all the reports that have been outlined in the requirements taking care to ensure consistency in line with your form designs.
Task 5b (P6)
Ensure consistency in design requirements as per the scenario Layout of forms and reports Use of logos (forms and reports)
Sources of information www.databasedev.co.uk
Hernandez M –Database Design for Mere Mortals: A Hands-on Guide to Relational Database Design, 2nd
Edition (Addison Wesley, 2003) ISBN 0201752840
Kroenke D –Database Concepts, 2nd Edition (Prentice Hall, 2004) ISBN 0131451413
Ritchie C –Relational Database Principles (Thomson Learning, 2002) ISBN 0826457134
Please ensure all evidence is printed and submitted,
