Unit 4 Database Design and Development ICON

December 7, 2021 admin 0 Comments

Session: September 2021

Programme title	BTEC HND in Computing
Unit number and title	4	Database Design and Development (L4)
Assignment number & title	1 of 1	Database Design and Development (L4)
Unit Leader	–
Assessor(s)	Dr Abdul Adamu
Issue Date	7/10/2021
Final assignment submission deadline	10-15 January 2022
Late submission deadline	17-22 January 2022 The learners are required to follow the strict deadline set by the College for submissions of assignments in accordance with the BTEC level 4 – 7 submission guidelines and College policy on submissions.
Resubmission deadline	TBA
Feedback	Formative feedback will be available in class during the semester. Final feedback will be available within 2 – 3 weeks of the assignment submission date.

General Guidelines

The work you submit must be in your own words. If you use a quote or an illustration from somewhere you must give the source.Include a list of references at the end of your document. You must give all your sources of information.Make sure your work is clearly presented and that you use readily understandable English.Wherever possible use a word processor and its “spell-checker”.

Internal verifier	Mohammed Hasan
Signature (IV of the brief) *	MJ	Date	06/10/2021

This Unit will be assessed by an Assignment.

You are strongly advised to read “Preparation guidelines of the Coursework Document” before answering your assignment.

ASSIGNMENT

Aim & Objective

This coursework is designed to demonstrate the broad understanding and knowledge of the module, assessing and evaluating the student’s strength and level of analysis; divided into four learning outcomes. The coursework should be submitted as one document in a report format in final submission.

Assignment Scenario

“ULKOM LTD” is a company that hire bicycles to students, residents, and tourists in Cambridge for many years. “ULKOM LTD” has decided to launch a new scheme dealing with electronic bicycles and electronic scooters due to recent high demands. The scheme will provide short-term and long-term access to electronic bicycles and electronic scooters through self-service terminals placed at convenient 5 locations across the city. For tracking purposes the bikes and the scooters are fitted with a GPS tracking device to identify the locations of the devices and the staff are alarmed if the device is not in any of the 5 locations. The company want to offer subscriptions to members who are 18 years or over, will offer weekly, monthly, and annual memberships. Membership costs £10 a week, £20 a month and

£70 for an annual membership.

Information stored for each member includes full name, email, contact phone numbers, residential address, driver’s license number, and membership status. A subscriber may only have one active membership at any given point in time. In addition to memberships, visitors or casual users can access the system by purchasing a £5 one-day pass using a credit card.

You have graduated from BTEC L4 College in London, and recently hired by a software company in London. The project manager has asked you to help provide suitable solution using relational database management system to solve the above problems for the new scheme. During the first consultation with the owner of the company, you discussed about the software development life cycle and how to implement the process effectively. During the requirement specification stage of the development, you visited “ULKOM LTD” and had the opportunity discuss users and systems requirements. During the interview you noted that the database system requires different users including customers, managers, and administrative staffs.

Below highlights information obtained during the requirement specification

The company has 100 bikes and 300 scooters location at 5 different locations in the city.
Efficient method of storing and retrieving records on bikes and scooters, the customers, and the employees.
The locations at which the bikes and scooters were hired from and returned to.

A customer details should include name, age, phone number and address (street, city, and postcode).
Customers can hire and return electronic bikes or electronic scooters in any of the 5 different locations. Each location has unique code, address (street, city, postcode), date of hire and return date.
The system should be automated keeping track of the availability, location and conditions of the bikes and scooters.
The company wants to keep records the unique ID of each bike or scooter, along with its make, model, colour, and size.
The company keeps record of the hired date, return date and the number of bicycles in stock.
A user may not borrow more than one bicycle at any given point in time. And each rental transaction is fully automated and records details of the user, bicycle or scooter, pick-up time and location, drop-off time and location, total rental length and the total cost.
The company has 50 employees at their local depot to manage supply and respond to any issues at the 5 locations in the city.
The employee on duty should be able track the locations the bikes and scooters. Also, if they have left outside the 5 locations.

The project manager, the database development team and the management team of ULKOM LTD have had a number discussion in order to effectively address the user and system requirements. The project manager has asked you to support the database development team by developing the database management system for ULKOM LTD by following the tasks below.

LO1

Provide clear statement for user and system requirements of the relational database system design. Use appropriate tool (i.e. draw.io or Lucidchart) to produce ERD diagram consisting of at least four interrelated entities. Use any suitable notation (Crow foot or Chen or UML) and show the attributes, primary keys, foreign keys and the relationships between the entities. Produce explanation and illustrations of the normalisations process including 1NF, 2NF and 3NF. Produce the data types of the attributes, including data validation. After producing the user interface statements and ERD diagram, your manager has asked you to produce a diagram of the user interface and explain relationships between the input/output of user interface diagram and the attributes of the entities (i.e. tables). Produce appropriate screenshots and annotations.

Finally, your manager then asked you to produce a report by assessing the effectiveness of the design in relation to user and system requirements.

LO2

After a successful database design, the next step is to develop the database using the structured query language. Using your design as a guide, use a suitable DBMS, for example Microsoft SQL Server 2012 (or above) to develop your database system. The tables created must be populated with records of at least five (5) entries for each table. Produce screenshots of the tables you have created and data you have inserted in the tables. To reflect your understanding of database security and maintenance, you are required to assess how these are ensured in your implementation of the fully functional database system in accordance with users and system’s requirements. Produce appropriate screenshots and annotations.

Using Structured Query Language produce query report and ensure your implementation is justified to meet user requirements. Produce at least 8 query reports from a single table and

multiple tables. Produce screenshots of the SQL statements and the query reports, including suitable annotations. To ensure usefulness of the report, you should assess whether a meaningful data were extracted from the query report. Produce appropriate screenshots and annotations.

You should discuss the effectiveness of the DBMS you have developed, the query reports and the tools have used in developing the DBMS in relation to user and system requirements, and suggesting improvements.

LO3

It is necessary to test database and in the process of successfully carrying out testing, a test plan suffice. In your report, outline how the system has been tested against users and system’s requirements. This test plan preferably to be in a table format illustrating at least six

(6) records tested. Ensure to have “Test Description”, “Expected Outcome”, “Actual Outcome” as headings. The “Actual Outcome” heading should include a visual representation such as screenshots of results and annotations. Produce appropriate screenshots and annotations of the results of the testing in “Actual Outcome” column.

From the test plan created, you are to explain the different database testing techniques and assess with evidence, one of the testing techniques implemented on your database development. You are required to implement and test the verification and validation process with above query transaction from the database illustrating the understanding of the various features of SQL (update, sorting, joining tables, conditions using the where clause, grouping, set functions, sub-queries etc.). In your report, include recommendations on how you can improve your database development.

LO4

Documentation helps in understanding the concept of database development. To reflect your understanding of technical and user documentation, you are required to produce a fully technical and user documentation for your designed database for the college. Your documentation should include diagrams showing movement of data through the system, and flowcharts describing how the system works. Produce appropriate screenshots and annotations.

Enhancing database development is paramount in completing the development cycle. You are required to assess any future improvements that may be required to ensure the continued effectiveness of the database system.

Relevant Information

To gain a Pass in a BTEC HND Unit, you must meet ALL the Pass criteria; to gain a Merit, you must meet ALL the Merit and Pass criteria; and to gain a Distinction, you must meet ALL the Distinction, Merit and Pass criteria.

1. Learning Outcomes and Assessment Criteria

Pass	Merit	Distinction
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 which includes interface and output designs, data validations and data normalisation.	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.	M2 Implement a fully functional database system which includes system security and database maintenance. M3 Assess whether meaningful data has been extracted through the use of query tools to produce appropriate management information.	LO2 & 3 D2 Evaluate the effectiveness of the database solution in relation to user and system requirements, and suggest improvements.
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.
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.

Preparation guidelines of the Coursework Document
- All coursework must be word processed.
- Document margins must not be more than 2.54 cm (1 inch) or less than 1.9cm (3/4

inch).

Font size must be within the range of 10 point to 14 points including the headings and body text (preferred font size is 12).
- Standard and commonly used type face, such as Arial and Times New Roman, should be used.
- All figures, graphs and tables must be numbered.
- Material taken from external sources must be properly referred and cited within the text using Harvard system
- Do not use Wikipedia as a reference.

3. Plagiarism and Collusion

Any act of plagiarism or collusion will be seriously dealt with according to the College regulations. In this context the definitions and scope of plagiarism and collusion are presented below:

Plagiarism is presenting somebody else’s work as your own. It includes copying information directly from the Web or books without referencing the material; submitting joint coursework as an individual effort.

Collusion is copying another student’s coursework; stealing coursework from another student and submitting it as your own work.

Suspected plagiarism or collusion will be investigated and if found to have occurred will be dealt with according to the College procedure (For details on Plagiarism & Collusion please see the Student Handbook).

4. Submission

Initial submission of coursework to the tutors is compulsory in each unit of the course.
1. The student must check their assignments on ICON VLE with plagiarism software Turnitin to make sure the similarity index for their assignment stays within the College approved level. A student can check the similarity index of their assignment up to five times in the Draft Assignment submission point located in the home page of the ICON VLE.
1. All Final coursework must be submitted to the Final submission point into the Unit (not to the Tutor). The student would be allowed to submit only once and that is the final submission.
1. Any computer files generated such as program code (software), graphic files that form part of the coursework must be submitted as an attachment to the assignment with all documentation.
1. Any portfolio for a Unit must be submitted as an attachment in the assignment

5. Good practice

Make backup of your work in different media (hard disk, memory stick, etc.) to avoid distress due to loss or damage of your original copy.

6. Extension and Late Submission

If you need an extension for a valid reason, you must request one using an Exceptional Extenuating Circumstances (EEC) form available from the Examination Office and ICON VLE. Please note that the tutors do not have the authority to extend the coursework deadlines and therefore do not ask them to award a coursework extension. The completed form must be accompanied by evidence such as a medical certificate in the event of you being sick, and should be submitted to the Examination Office.

Late submission will be accepted and marked according to the College procedure. It should be noted that late submission may not be graded for Merit and Distinction.
- All late coursework must be submitted to the Late submission point into the unit (not to the Tutor) in the ICON VLE. A student is allowed to submit only once and that is also treated as the final submission.
- If you fail in the Final or Late submission, you can resubmit in the Resubmission window.

7. Submission deadlines

Formative feedback	Week 12
Final Submission	10-15 January 2022
Late submission	17-22 January 2022

Submit to: Online to the ICON VLE only Glossary:

Analyse: Break an issue or topic into smaller parts by looking in depth at each part. Support each part with arguments and evidence for and against (Pros and cons)

Critically Evaluate/Analyse: When you critically evaluate you look at the arguments for and against an issue. You look at the strengths and weaknesses of the arguments. This could be from an article you read in a journal or from a text book.

Discuss: When you discuss you look at both sides of a discussion. You look at both sides of the arguments. Then you look at the reason why it is important (for) then you look at the reason why it is important (against).

Explain: When you explain you must say why it is important or not important. Evaluate: When you evaluate you look at the arguments for and against an issue. Describe: When you give an account or representation of in words.

Identify: When you identify you look at the most important points.

Define: State or describe the nature, scope or meaning.

Implement: Put into action/use/effect Compare: Identify similarities and differences Explore: To find out about

Recommend: Suggest/put forward as being appropriate, with reasons why

www.hndassignmenthelp.com