Download Database Design and Implementation using ER Model and Microsoft SQL Server 2012 and more High school final essays Computer science in PDF only on Docsity!
Higher Nationals
Internal verification of assessment decisions – BTEC (RQF)
INTERNAL VERIFICATION – ASSESSMENT DECISIONS
Programme title Assessor Internal Verifier Unit(s) Unit 04: Database Design & Development Assignment title Data base system for the SmartMovers Transport Company 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 been assessed accurately? Y/N Is the feedback to the student: Give details:
- Constructive?
- Linked to relevant assessment criteria?
- Identifying opportunities for improved performance?
- Agreeing actions? Y/N Y/N Y/N Y/N Does the assessment decision need amending? 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
Higher Nationals - Summative Assignment Feedback Form
Student Name/ID Unit Title Unit 04:^ Database Design & Development Assignment Number 1 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 Descripts
P1 M1 D
LO2 Develop a fully functional relational database system, based on an existing system design Pass, Merit & Distinction Descripts
P 2 P3 M3 M3 D
LO3 Test the system against user and system requirements. Pass, Merit & Distinction Descripts
P 4 M 4 D 2
LO4 Produce technical and user documentation. Pass, Merit & Distinction Descripts
P 5 M 5 D
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. Programme Leader signature (if required) Date
Date Date
Pearson Higher Nationals in
Computing
Unit 04: Database Design & Development
Assignment 01
Page 5 of 102 General Guidelines
- 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.
- The entire assignment brief should be attached as the first section of your assignment.
- The assignment should be prepared using a word processing software.
- The assignment should be printed single sided in an A4 sized paper.
- Allow 1” margin on top, bottom and right sides of the paper and 1.25” on the left side (for binding). Word Processing Rules
- The font size should be 12 point, and should be in the style of Time New Roman.
- Set line spacing to 1.5. Justify all paragraphs.
- Ensure that all headings are consistent in terms of size and font style.
- Use footer function on the word processor to insert your name, unit, assignment no, and page number on each pag e. This is useful if individual sheets get detached from the submission.
- Use the spell check and grammar check function of the word processing application to review the use of language on your assignment.
Page 7 of 102 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.
- I know that plagiarism is a punishable offence because it constitutes theft.
- I understand the plagiarism and copying policy of Edexcel UK.
- I know what the consequences will be if I plagiarise or copy another’s work in any of the assignments for this program.
- 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.
- I acknowledge that the attachment of this document signed or not, constitutes a binding agreement between myself and Edexcel UK.
- I understand that my assignment will not be considered as submitted if this document is not attached to the assignment. Student’s Signature: Date: ( Provide E-mail ID ) ( Provide Submission Date )
Page 8 of 102
Higher National Diploma in Computing
Assignment Brief
Student Name /ID Number Unit Number and Title Unit 4: Database Design & Development
Academic Year 2017/
Unit Tutor Assignment Title Data base system for the Smart^ Movers 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.
Page 10 of 102
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.
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.
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.
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.
Page 11 of 102
Activity 2
Activity 2.
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.
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.
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.
Page 13 of 102
Activity 4
Activity 4.
a. Prepare a simple users’ guide and a technical documentation for the
support and maintenance of the software.
Activity 4.
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.
a. Suggest the future improvements that may be required to ensure the
continued effectiveness of the database system.
Page 14 of 102
Grading Criteria Achieved Feedback
LO1 Use an appropriate design tool to design a relational
database system for a substantial problem
P1 Design a relational database system using appropriate
design tools and techniques, containing at least four
interrelated tables, with clear statements of user and system
requirements.
M1 Produce a comprehensive design for a fully functional
system that includes interface and output designs, data
validations and data normalization.
D1 Assess the effectiveness of the design in relation to user
and system requirements.
LO2 Develop a fully functional relational database system,
based on an existing system design
P2 Develop the database system with evidence of user
interface, output and data validations, and querying across
multiple tables.
P3 Implement a query language into the relational database
system
Page 16 of 102
Acknowledgement
Acknowledgement is not only a formal, so far additionally an obligation to every one of the
persons who have helped in the finishing procedure of the venture. One of the most wonderful
angles in meeting the important and critical data and assembling it is the chance to thank every
one of the persons who effectively add to it
First of all, I Would Thank our ESOFT Manager Mr. V. Niruban, Senior Lecturer of ESOFT
Metro Campus and Mr. Piragan ESOFT Metro Campus who gave the specialized way and
consistent management all the same his busy timetable
Finally, I would like to thank ESOFT Metro Campus and its decent showing board for giving
important information and help all through the HND Computing and Systems Improvement
program. I am additionally grateful to my family mostly to my parents, for supporting also,
promising me to finish this project effectively.
Page 19 of 102
Table of Contents
Acknowledgement ............................................................................................................................... 16 List of Figures ...................................................................................................................................... 21 List of Tables ....................................................................................................................................... 23 Introduction ......................................................................................................................................... 24 Activity 01 ............................................................................................................................................ 25 Activity 1.1 ....................................................................................................................................... 25 A Requirement Analysis ............................................................................................................. 25 ER Diagram for Smart Movers ................................................................................................. 27 Logical Schema for Smart Movers ............................................................................................ 31 Activity 1.2 ....................................................................................................................................... 32 Data normalization ..................................................................................................................... 32 Purpose of the Data Normalization ........................................................................................... 32 Process of Normalization ............................................................................................................ 32 Design set of simple interfaces to input and output for the above scenario using Wireframe or any interface-designing tool. .................................................................................................. 36 Explain the validation mechanisms to validate data in the tables with examples. ................ 43 Activity 1.3 ....................................................................................................................................... 45 Check whether the given design (ERD and Logical design) is representing the identified user and system requirements to access the effectiveness of the design. ................................ 45 Activity 02 ............................................................................................................................................ 47 Activity 2.1 ....................................................................................................................................... 47 Develop a relational database system according to the ER diagram you have created (Use SQL DDL statements). ................................................................................................................ 47 Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and delete data in the database .................................................................................................. 53 Implement the validation methods explained in task 1.2-part c. ............................................ 60 Display the details payment with the job details and the customer details using Join queries ...................................................................................................................................................... 62 Activity 2.2 ....................................................................................................................................... 63 Explain the usage of DML with below mentioned queries by giving at least one single example per each case from the developed database. .............................................................. 63 Activity 2.3 ....................................................................................................................................... 66 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. ........................................................................ 66 Activity 2.4 ....................................................................................................................................... 69
Page 20 of 102 Explain the usage of the below SQL statements with the examples from the developed database ....................................................................................................................................... 69 Activity 03 ............................................................................................................................................ 72 Activity 3.1 ....................................................................................................................................... 72 Provide a suitable test plan to test the system against user and system requirements. ........ 72 Provide relevant test cases for the database you have implemented ...................................... 76 Activity 3.2 ....................................................................................................................................... 84 Explain how the selected test data in task 3.1 b) can be used improve the effectiveness of testing. .......................................................................................................................................... 84 Activity 3.3 ....................................................................................................................................... 85 Get independent feedback on your database solution from the non-technical 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. ....................................................................................... 85 Recommendations and suggestions for improvements in a separate conclusion / recommendations section. .......................................................................................................... 87 Activity 04 ............................................................................................................................................ 88 Activity 4.1 ....................................................................................................................................... 88 Prepare a simple users’ guide and a technical documentation for the support and maintenance of the software. ...................................................................................................... 88 Activity 4.2 ....................................................................................................................................... 95 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. ......................................................................................................... 95 Activity 4.3 ....................................................................................................................................... 98 Suggest the future improvements that may be required to ensure the continued effectiveness of the database system. ......................................................................................... 98 Self-Criticism ....................................................................................................................................... 99 Conclusion ......................................................................................................................................... 100 References .......................................................................................................................................... 101
