Title | Unit 04 Database Sandun Poulier |
---|---|
Author | Sandun Poulier |
Course | HND in Computing |
Institution | ESOFT Metro Campus |
Pages | 95 |
File Size | 5.5 MB |
File Type | |
Total Downloads | 148 |
Total Views | 511 |
Higher NationalsInternal verification of assessment decisions – BTEC (RQF)INTERNAL VERIFICATION – ASSESSMENT DECISIONSProgramme title Data base system for the SmartMovers Transport Company Assessor Mrs Ishani Jayasuriya Internal VerifierUnit(s) Unit 04: Database Design & Development Assignment t...
Higher Nationals Internal verification of assessment decisions – BTEC (RQF)
INTERNAL VERIFICATION – ASSESSMENT DECISIONS Data base system for the SmartMovers Transport Company
Programme title
Mrs Ishani Jayasuriya
Assessor
Internal Verifier Unit 04: Database Design & Development
Unit(s)
Data base system for the SmartMovers Transport Company
Assignment title
Sandun Poulier
Student’s name List which assessment criteria the Assessor has awarded.
Pass
Merit
Distinction
INTERNAL VERIFIER CHECKLIST Do the assessment criteria awarded match those shown in the assignment brief?
Y/N
Is the Pass/Merit/Distinction grade awarded justified by the assessor’s comments on the student work?
Y/N
Has the work accurately?
Y/N
been
assessed
Is the feedback to the student: Give details: • Constructive? • Linked to relevant assessment criteria? • Identifying opportunities for improved performance? • Agreeing actions? Does the amending?
assessment
decision
Y/N Y/N Y/N Y/N need
Y/N
Assessor signature
Date
Internal Verifier signature
Date
Programme Leader signature (if required)
Date
Confirm action completed Remedial action taken Give details: Assessor signature
Date
Internal Verifier signature
Date
Programme Leader signature (if required)
Date
Higher Nationals - Summative Assignment Feedback Form Student Name/ID
KAN/A-011362
Unit Title
Unit 04:
Assignment Number
1
Database Design & Development
Mrs Ishani Jayasuriya
Assessor
Submission Date
Date Received 1st submission
Re-submission Date
Date Received 2nd submission
Assessor Feedback: LO1 Use an appropriate design tool to design a relational database system for a substantial problem Pass, Merit & Distinction
P1
M1
D1
Descripts
LO2 Develop a fully functional relational database system, based on an existing system design Pass, Merit & Distinction
P2
M2
M3
D2
Descripts
LO3 Test the system against user and system requirements. Pass, Merit & Distinction
P3
M4
D3
Descripts
LO4 Produce technical and user documentation. Pass, Merit & Distinction
P4
P5
D4
M5
Descripts
Grade:
Assessor Signature:
Date:
Resubmission Feedback:
Grade:
Assessor Signature:
Date:
Internal Verifier’s Comments:
Signature & Date: *
Please note that grade decisions are provisional. They are only confirmed once internal and external moderation has taken place and
grades decisions have been agreed at the assessment board.
Pearson Higher Nationals in
Computing Unit 04: Database Design & Development Assignment 01
General Guidelines
1. A cover page or title page should be attached to your assignment. Use page 1 of this assignment brief as your cover page and make sure all details are accurately filled. 2. The entire assignment brief should be attached as the first section of your assignment. 3. The assignment should be prepared using a word processing software. 4. The assignment should be printed single sided in an A4 sized paper. 5. Allow 1” margin on top, bottom and right sides of the paper and 1.25” on the left side (for binding).
Word Processing Rules
1. The font size should be 12 point, and should be in the style of Time New Roman. 2. Set line spacing to 1.5. Justify all paragraphs. 3. Ensure that all headings are consistent in terms of size and font style. 4. Use footer function on the word processor to insert your name, unit, assignment no, and page number on each page. This is useful if individual sheets get detached from the submission. 5. Use the spell check and grammar check function of the word processing application to review the use of language on your assignment.
Important Points:
1. Carefully check carefully the hand in date and the instructions given with the assignment. Late submissions will not be accepted. 2. Ensure that sufficient time is spent to complete the assignment by the due date. 3. Do not wait till the last minute to print or bind the assignment. Such excuses will not be accepted for late submissions. 4. You must be responsible for efficient management of your time. 5. If you are unable to hand in your assignment on time and have valid reasons such as illness, you may apply (in writing) for an extension. 6. Failure to achieve at least a PASS grade will result in a REFERRAL grade. 7. Non-submission of work without valid reasons will lead to an automatic REFERRAL. You will then be asked to complete an alternative assignment. 8. If you use other people’s work or ideas in your assignment, it must be properly referenced, using the HARVARD referencing system, in your text or any bibliography. Otherwise, you’ll be found guilty of committing plagiarism. 9. If you are caught plagiarising, your grade will be reduced to a REFERRAL or at worst, you could be excluded from the course.
Student Declaration
I hereby, declare that I know what plagiarism entails, namely to use another’s work and to present it as my own without attributing the sources in the correct form. I further understand what it means to copy another’s work.
1. I know that plagiarism is a punishable offence because it constitutes theft. 2. I understand the plagiarism and copying policy of Edexcel UK. 3. I know what the consequences will be if I plagiarise or copy another’s work in any of the assignments for this program. 4. I declare therefore that all work presented by me for every aspect of my program, will be my own, and where I have made use of another’s work, I will attribute the source in the correct way. 5. I acknowledge that the attachment of this document signed or not, constitutes a binding agreement between myself and Edexcel UK. 6. I understand that my assignment will not be considered as submitted if this document is not attached to the assignment.
Student’s Signature: (Provide E-mail ID)
Date: (Provide Submission Date)
Higher National Diploma in Computing Assignment Brief Student Name /ID Number
KAN/A-011362
Unit Number and Title
Unit 4: Database Design & Development
Academic Year
2017/18
Unit Tutor
Mrs. Ishani Jayasuriya
Assignment Title
Data base system for the SmartMovers Transport Company
Issue Date Submission Date IV Name & Date
Submission format
Part 1: The submission is in the form of an individual written report. This should be written in a concise, formal business style using single spacing and font size 12. You are required to make use of headings, paragraphs and subsections as appropriate, and all work must be supported with research and referenced using the Harvard referencing system. Please also provide a bibliography using the Harvard referencing system. The recommended word limit is 2,000–2,500 words, although you will not be penalised for exceeding the total word limit. Part 2: The submission is in the form of a fully functional relational database system demonstrated to the Tutor; and an individual written report (please see details in Part 1 above). Part 3: The submission is in the form of a witness statement of the testing completed by the Tutor; technical documentation; and a written report (please see details in Part 1 above). Unit Learning Outcomes:
LO1 Use an appropriate design tool to design a relational database system for a substantial problem. LO2 Develop a fully functional relational database system, based on an existing system design. LO3 Test the system against user and system requirements. LO4 Produce technical and user documentation. Assignment Brief and Guidance:
*Please note that assignment guidance is for reference only and should be more specific in detail to meet customized needs. Assignment brief Case study You are employed as a Database Developer for a large IT development company. The company has been approached by a transport company called SmartMovers which has grown to a level where need to have an automated system to handle day today operational activities to meet customer demands. You are given the job to design and develop a database solution for SmartMovers to meet their business requirements. The company transports chemical products from one location to another on behalf of registered customers. Customers are of three types namely Catogory1, 2 and 3. Each customer is given a unique customer number and his or her details have to be recorded in the system. Each transport operation is called a job, which involves picking up one or more loads of the products from a customer requested start location and delivers it to a customer requested destination. A unique number is given for each job and for each load when they are created. A Load is transported using a particular transport unit, which consists of a lorry, a driver, assistant, and a container (for carrying the product) A container is fixed to a trailer so it needs to be coupled to a lorry at the start of a Job. (Single container can transport only one chemical product due to safety requirements) Payments for a job depends on customer type, product type (No Risk, High risk) and the load type (small, medium and large). Transport operations are run from
9 regional depots. Jobs are allocated to individual depots to service. Depots hold, manage and maintain their own transport units which are permanently allocated to them to service Jobs. Normally transport units are held at the base Depot they belong. The final system should be able to provide the required information for management decision making and to handle daily operations efficiently and effectively.
Activity 01 Activity 1.1 a. Identify the user and system requirements to design a database for the above scenario. b. Identify entities and attributes of the given scenario and design a relational database system using conceptual design (ER Model) by including identifiers (primary Key) of entities and cardinalities, participations of relationships. c. Convert the ER Model into logical database design using relational database model including primary keys foreign keys and referential Integrities. It should contain at least five interrelated tables. Note:-It is allowed to have your own assumptions and related attributes within the scope of the case study given. Activity 1.2 a. Explain data normalization with examples. Check whether the provided logical design in task 1.1 is normalised. If not, normalize the database by removing the anomalies. b. Design set of simple interfaces to input and output for the above scenario using Wireframe or any interface-designing tool. c. Explain the validation mechanisms to validate data in the tables with examples. Activity 1.3 a. Check whether the given design (ERD and Logical design) is representing the identified user and system requirements to access the effectiveness of the design.
Activity 2 Activity 2.1 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables. a. Develop a relational database system according to the ER diagram you have created (Use SQL DDL statements). b. Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and delete data in the database c. Implement the validation methods explained in task 1.2-part c. d. Display the details payment with the job details and the customer details using Join queries Activity 2.2 a. Explain the usage of DML with below mentioned queries by giving at least one single example per each case from the developed database.
Select Update Delete
Note: - The queries you have selected should be meaningful and relevant to the given scenario.
Activity 2.3 a. Explain how security mechanisms have been used and the importance of these mechanisms for the security of the database. Implement proper security mechanisms (EX: -User groups, access permissions) in the developed database.
Activity 2.4 a. Explain the usage of the below SQL statements with the examples from the developed database Group by Order by Having Between Where
Activity 3 Activity 3.1 a. Provide a suitable test plan to test the system against user and system requirements b. Provide relevant test cases for the database you have implemented Note:- Learner needs to give expected results in a tabular format and screenshots of the actual results with the conclusion
Activity 3.2 a. Explain how the selected test data in task 3.1 b) can be used improve the effectiveness of testing. Activity 3.3 a. Get independent feedback on your database solution from the nontechnical users and some developers (use surveys, questioners, interviews or any other feedback collecting method) and make recommendations and suggestions for improvements in a separate conclusion/recommendations section.
Activity 4
Activity 4.1 a. Prepare a simple users’ guide and a technical documentation for the support and maintenance of the software. Activity 4.2 a. Your technical documentation should include some of the UML diagrams (Use case diagram Class diagram, etc.), flow charts for the important functionalities, context level DFD and the Level 1 DFD Activity 4.3 a. Suggest the future improvements that may be required to ensure the continued effectiveness of the database system.
Table of Contents 1
Task 01 .................................................................................................................... 1 1.1
a. Identify the user and system requirements to design a database for the above
scenario. ...................................................................................................................... 1 User Requirements .................................................................................................. 1 System requirements ................................................................................................ 1 b.
Identify entities and attributes of the given scenario and design a relational
database system using conceptual design (ER Model) by including identifiers (primary Key) of entities and cardinalities, participations of relationships. ............................. 3 Entities and Attributes for SmartMovers .................................................................. 3 c. Convert the ER Model into logical database design using relational database model including primary keys foreign keys and referential Integrities. It should contain at least five interrelated tables. ..................................................................................... 2 1.2
Explain data normalization with examples. Check whether the provided logical
design in task 1.1 is normalized. If not, normalize the database by removing the anomalies. ................................................................................................................... 3 b.
Design set of simple interfaces to input and output for the above scenario using
Wireframe or any interface-designing tool. .............................................................. 5 c. Explain the validation mechanisms to validate data in the tables with examples. 12 1.3
Check whether the given design (ERD and Logical design) is representing the
identified user and system requirements to access the effectiveness of the design. ..... 16 2
Task 02 .................................................................................................................. 17 2.1
a. Develop a relational database system according to the ER diagram you have
created (Use SQL DDL statements). .......................................................................... 17 Use of SQL DDL Statements for creating tables in SmartMovers Database ........... 20 Implemented System Security ................................................................................ 24
Sandun Blazon Lanka Poulier
Unit No:- 04
Assignment No:- 01
2.2
Explain the usage of DML with below mentioned queries by giving at least one
single example per each case from the developed database. ....................................... 25 Viewing records in Customers table ....................................................................... 26 Deleting Records from the Loads Table ................................................................. 26 Updating records from Products Table ................................................................... 27 2.3
a. Explain how security mechanisms have been used and the importance of these
mechanisms for the security of the database. Implement proper security mechanisms (EX: -User groups, access permissions) in the developed database............................. 27 Privileges ............................................................................................................... 27 Roles ..................................................................................................................... 28 System Security Policy .......................................................................................... 28 2.4
a. Explain the usage of the below SQL statements with the examples from the
developed database .................................................................................................... 31 GROUP BY Function ............................................................................................ 31 ORDER BY Function ............................................................................................ 32 HAVING Function ................................................................................................ 32 BETWEEN Function ............................................................................................. 33 WHERE Function .................................................................................................. 33 3
Task 3.................................................................................................................... 34 3.1
Provide a suitable test plan to test the system against user and system requirements 34
Customer Table Test Plan ...................................................................................... 34 Product Table Test Plan ......................................................................................... 34 Load Table Test Plan ............................................................................................. 35 Transport Unit Test Plan .......................................... Error! Bookmark not defined. Payment Table Test Plan ......................................... Error! Bookmark not defined. 3.2
Provide relevant test cases for the database you have implemented .................. 35
Test Cases ............................................................................................................. 35
Sandun Blazon Lanka Poulier<...