Download database assignment fully complete and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!
Higher Nationals
Internal verification of assessment decisions – BTEC (RQF)
INTERNAL VERIFICATION – ASSESSMENT DECISIONS
Programme title HND in Computing
Assessor Internal Verifier Unit(s) Unit 04 : Database Design & Development Assignment title Data base system for the SmartMovers Transport Company Student’s name K.N.Tharusha Fernando 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 Programme Leader signature (if required) Date
Higher Nationals - Summative Assignment Feedback Form
Student Name/ID COL/E-^009400 Unit Title Unit^04 :^ Database^ Design^ &^ Development Assignment Number 1 Assessor Submission Date 1/27/2021 (^) 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 D
Descripts LO2 Develop a fully functional relational database system, based on an existing system design
Pass, Merit & Distinction P2 P3 M3 M3 D
Descripts LO3 Test the system against user and system requirements. Pass, Merit & Distinction P4 M4 D Descripts LO4 Produce technical and user documentation. Pass, Merit & Distinction P5 M5 D 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
- A Cover page or title page – You should always attach a title page to your assignment. Use previous page as your cover sheet and make sure all the details are accurately filled.
- Attach this brief as the first section of your assignment.
- All the assignments should be prepared using a word processing software.
- All the assignments should be printed on A4 sized papers. Use single side printing.
- Allow 1” for top, bottom, right margins and 1.25” for the left margin of each page.
Word Processing Rules
- The font size should be 12 point, and should be in the style of Time New Roman.
- Use 1.5 line spacing. Left justify all paragraphs.
- Ensure that all the headings are consistent in terms of the font size and font style.
- Use footer function in the word processor to insert Your Name, Subject, Assignment No, and Page Number on each pag e. This is useful if individual sheets become detached for any reason.
- Use word processing application spell check and grammar check function to help editing your assignment.
Important Points:
- It is strictly prohibited to use textboxes to add texts in the assignments, except for the compulsory information. eg: Figures, tables of comparison etc. Adding text boxes in the body except for the before mentioned compulsory information will result in rejection of your work.
- Carefully check the hand in date and the instructions given in the assignment. Late submissions will not be accepted.
- Ensure that you give yourself enough time to complete the assignment by the due date.
- Excuses of any nature will not be accepted for failure to hand in the work on time.
- You must take responsibility for managing your own time effectively.
- 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.
- Failure to achieve at least PASS criteria will result in a REFERRAL grade.
- Non-submission of work without valid reasons will lead to an automatic RE FERRAL. You will then be asked to complete an alternative assignment.
- If you use other people’s work or ideas in your assignment, reference them properly using HARVARD referencing system to avoid plagiarism. You have to provide both in-text citation and a reference list.
10. If you are proven to be guilty of plagiarism or any academic misconduct, your grade could
be reduced to A REFERRAL or at worst you could be expelled from the course
Higher National Diploma in Computing
Assignment Brief
Student Name /ID Number K.N.Tharusha Fernando Unit Number and Title Unit 4: Database Design & Development
Academic Year 2020/
Unit Tutor Assignment Title Data base system for the SmartMovers^ Transport Company Issue Date Submission Date IV Name & Date
Submission format
Part 1: The submission should be in the form of an individual written report 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 Harvard referencing system. Please also
provide in-text citation and bibliography using Harvard referencing system. The
recommended word limit is 3,000–3,500 words, although you will not be penalised for
exceeding the total word limit.
Part 2: The submission should be 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 should be 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:
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 they 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
Activity 1
1) Design and produce a comprehensive database design document for the above
scenario and it should cover the below mentioned points.
- User and system requirements
- Complete normalized (up to 3rd normal form) ER Diagram
- Complete Logical database design.
- Validations that used to validate data in the tables
- Simple interfaces to input and output data to the tables
- Critical evaluation and access the effectiveness of the user and system requirements
with the database design
Activity 2
2) Develop a comprehensive database system based on the database design
document which is provided under Activity 1
- Implement the database according to the provided ER diagram and use DDL
statements.
- Implement a GUI based system to view, insert, update, delete data in the tables.
- Implement validation methods explained in the Activity 1.
- Implement join queries display the details payment with the job details and the
customer details
- Implement and access DML (select, update, delete) by giving meaningful examples from
the developed database
- Explain and implement the security mechanisms in the developed database (EX: - User
groups, access permissions).
- Explain and implement the below mentioned SQL statements from the developed
database by giving meaningful examples.
(Group by, Order by, Having, Between, Where)
Activity 3
3) Test the developed database system which has created in the Activity 2.
- Create a test plan to test the system against user and system requirements.
- Create test cases to test the developed database.
- Explain and access how the selected test data can be used improve the effectiveness
of testing.
- Get independent feedback and evaluate the effectiveness on the developed
database solution from the non-technical users and some developers.
Activity 4
Produce a technical documentation for the developed database system that contains a user
guide, UML diagrams (Use case diagram, class diagram, flow charts, DFD level 0 and 1) and
evaluate the database suggesting future enhancements to be implemented to ensure
continuous effectiveness of the system.
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
M2 Implement a fully functional database
system that includes system security and
database maintenance.
M3 Assess whether meaningful data has
been extracted using query tools to
produce appropriate management
information.
LO3 Test the systems against user and
system requirements
P4 Test the system against user and system
requirements.
M4 Assess the effectiveness of the testing,
including an
explanation of the choice of test data used.
LO2 & LO
D2 Evaluate the effectiveness of the
database solution in relation to user and
system requirements, and suggest
improvements.
LO4 Produce technical and user
documentation
P5 Produce technical and user
documentation.
M5 Produce technical and user
documentation for a fully functional
system, including diagrams showing
movement of data through the system, and
flowcharts describing how the system
works.
D3 Assess any future improvements that
may be required to ensure the continued
effectiveness of the database system.
- Internal verification of assessment decisions – BTEC (RQF) Contents
- General Guidelines
- Word Processing Rules..........................................................................................................................
- Important Points:
- Student Declaration
- Student’s Signature: Date:
- Higher National Diploma in Computing
- Logical Database Design
- What is logical database design?
- Validation
- What is validation in database and its types
- Data type
- Presence
- Unique Identifier
- Range check
- Format
- Restricted choice
- Creating simple interfaces to the database
- Login page
- Main menu
- Customer menu
- Product menu
- Payment Menu
- Transport Unit Menu
- Depot Menu
- Developing SQL Database
- What are DDL statements?
- Login Table
- Job Table
- Load Table
- Transport Unit Table
- Lorry Table
- Driver Table
- Assistant Table
- Container Table
- Payment Table
- Product Table
- Depots Table
- GUI based system to Insert, Update, and Delete
- Insert
- C# coding for Inserting the data
- Update
- C# coding for Updating the data
- Delete
- C# coding for Deleting the data
- Inserting Data using SQL
- Updating Data using SQL
- Deleting Data using SQL
- Implementing validation methods to the database
- Data type checking
- Presence Check
- Range Check
- Format
- Implementing Joining Queries
- What is SQL Join statement?
- Implementing DML
- What is DML?
- INSERT Command
- SELECT Command
- UPDATE Command
- DELETE Command
- SQL statements for Group by, Order by, Having, Between and Where
- Group by
- Order by
- Having clause
- Between clause
- Where clause
- Creating Test plans
- What is a test plan?
- Test plan elements
- How to create a test plan to our system?
- Test plan for Smart Mover database
- Creating test cases
- What are test cases?
- Enhancing the effectiveness of testing
- Creating feedback forms technical and non-technical users
- What is Technical documentation?
- Technical documentation
- User documentation
- User guide
- Reference list
- Figure 1.ER Diagram Figures
- Figure 2.Relational Database
- Figure 3.Login page
- Figure 4.Login Success
- Figure 5.Login unsuccessful
- Figure 6.Main menu Smart Movers
- Figure 7.Customer Menu
- Figure 8.Product Menu
- Figure 9.Payment Menu..............................................................................................................................
- Figure 10.Transport Unit Menu
- Figure 11.Depot Menu
- Figure 12.Login Table SQL Statement
- Figure 13.Job Table SQL statement
- Figure 14.Load Table SQL Statement
- Figure 15.Transport Unit Table SQL Statement
- Figure 16.Lorry Table SQL Statement
- Figure 17.Driver Table SQL statement
- Figure 18.Assistant Table SQL Statements..................................................................................................
- Figure 19.Container Table SQL Statements
- Figure 20.Payment Table SQL Statements
- Figure 21.Product Table SQL Statements
- Figure 22.Depots Table SQL Statements.....................................................................................................
- Figure 23.Inserting Products
- Figure 24.Coding for Insert Button
- Figure 25.Successfully Updated table
- Figure 26.Successfully Updated
- Figure 27.Coding for Updating
- Figure 28.Successfully Deleted Record
- Figure 29.Successfully Deleted record table
- Figure 30.Coding for Deleting
- Figure 31.Inserting Data Using SQL
- Figure 32.Output of insertion
- Figure 33.Update using SQL
- Figure 34. Output of Updating
- Figure 35.Delete using SQL
- Figure 36.Output of Delete record
- Figure 37.Data Type Checking.....................................................................................................................
- Figure 38.Correct Table values
- Figure 39.Presence Checking
- Figure 40.Correct Table fields
- Figure 41.Range Checking
- Figure 42.Customer Table
- Figure 43.Format Checking
- Figure 44.Payments Table
- Figure 45.Join Query
- Figure 46.INSERT INTO command
- Figure 47.SELECT command
- Figure 48.UPDATE command
- Figure 49.DELETE Command
- Figure 50.Group by
- Figure 51.Order by
- Figure 52.Having Clause
- Figure 53.Between clause
- Figure 54.WHERE cause
- Figure 55.Google Form 01...........................................................................................................................
- Figure 56.Google form
- Figure 57.Google form
- Figure 58.Pie chart
- Figure 59.Pie chart
- Figure 60.Pie chart
- Figure 61.Login for user
- Figure 62.Main menu for user
- Figure 63.Product menu for user
- Table 1.Unnormalized Table Tables
- Table 2.1NF
- Table 3.2NF table
- Table 4.2NF table
- Table 5.3NF table
- Table 6.3NF table
- Table 7.3NF table
- Table 8.Test Plan
- Table 9.Test case table
- Table 10.Test case table
- Table 11.Test case Table
- Table 12.Test case Table
- Table 13.Test case Table
- Table 14.Maintenance Schedule
- Table 15.ER diagram for technical users
- Table 16.Relation database for technical users
Activity 01
1) Design and produce a comprehensive database design document for the above scenario
and it should cover the below mentioned points.
- User and system requirements
- Complete normalized (up to 3rd normal form) ER Diagram
- Complete Logical database design.
- Validations that used to validate data in the tables
- Simple interfaces to input and output data to the tables
- Critical evaluation and access the effectiveness of the user and system requirements
with the database design
User and the system requirements
System requirements
Hardware and software that is essentially required to run in a computer is known as system
requirements. (Christensson, P. (2020). For example, our operating system needs hardware
requirements to run in the computer by having a minimum amount of memory needed and
storage capacity.
When we implement or going to purchase any kind of software, we need to check its
compatibility to our own system. Below are some system requirements we need to check before
purchase or implement a software.
- Operating system (OS) version
- Minimum processor and its speed
- Minimum RAM (Random Access Memory)
- Minimum HDD (Hard Disk) space or free storage space
- Minimum GPU (Graphics Process Unit)
So, for our database, system requirements are important to be identified when implementing.
Because the whole database is relied on it. Below are some hardware requirements that will be
needed when we will implement the database system.
- Processor- Dual Core Intel Pentium (Xeon) or higher
- RAM- 4GB to 8GB recommended
- Hard disk- 40 GB recommended + 500 MB for the application
There are some languages that is useful to implement the database such as MySQL, C++,
python, C# etc. For the Smart movers I will be using MySQL and C# as the languages to
implement the database.
