E116765-1634752502190-110100-Unit 04 - Database design and Development - Pamudi PDF

Preview

Summary

Download E116765-1634752502190-110100-Unit 04 - Database design and Development - Pamudi PDF

Description

Higher Nationals Internal verification of assessment decisions – BTEC (RQF) INTERNAL VERIFICATION – ASSESSMENT DECISIONS Programme title

BTEC HND in Computing

Assessor

Dileepa Mihiranga

Internal Verifier

Unit 04: Database Design & Development

Unit(s)

Database Solution for Polly Pipe

Assignment title

L.B Pamudi Manushika

Student’s name List which assessment criteria the Assessor has awarded.

Pass

Merit

P1 P2 P3 P4 P5

Distinction

M1 M2

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

Does the assessment decision need amending?

Y/N

Y/N

[email protected]

Assessor signature Internal Verifier signature

Date

19/01/2022

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

Page | 1 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Higher Nationals - Summative Assignment Feedback Form Student Name/ID Unit Title

Unit 04:

Assignment Number

1

Database Design & Development

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 Descripts

M1

D1

LO2 Develop a fully functional relational database system, based on an existing system design Pass, Merit & Distinction P2 Descripts

P3

M2

M3

D2

LO3 Test the system against user and system requirements. Pass, Merit & Distinction P4 Descripts

M4

D2

LO4 Produce technical and user documentation. Pass, Merit & Distinction P5 Descripts Grade: Pass

M5

D3

Assessor Signature: [email protected]

Date: 19/01/2022

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.

Page | 2 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Assignment Feedback Formative Feedback: Assessor to Student Learner has shown that he is clearly engaging with the subject material, even though it shows overall lack of analysis. Learner shows clear understanding on database concepts. Learner is successful in designing the database, implementing functional database according to the given scenario. In future you would develop well if you you could be more analystical and critical in your evaluations.

Action Plan

Summative feedback

Feedback: Student to Assessor

Assessor signature

[email protected]

Student signature

Date

19/01/2022

Date

Page | 3 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Pearson Higher Nationals in

Computing Unit 04: Database Design & Development Assignment 01

Page | 4 L.B Pamudi Manushika

Unit 04 – Database Design & Development

General Guidelines 1. 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. 2. Attach this brief as the first section of your assignment. 3. All the assignments should be prepared using a word processing software. 4. All the assignments should be printed on A4 sized papers. Use single side printing. 5. 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 page. This is useful if individual sheets become detached for any reason. 5. Use word processing application spell check and grammar check function to help editing your assignment.

1. 2. 3. 4.

Important Points: 1. 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. 2. Carefully check the hand in date and the instructions given in the assignment. Late submissions will not be accepted. 3. Ensure that you give yourself enough time to complete the assignment by the due date. 4. Excuses of any nature will not be accepted for failure to hand in the work on time. 5. You must take responsibility for managing your own time effectively. 6. 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. 7. Failure to achieve at least PASS criteria will result in a REFERRAL grade . 8. Non-submission of work without valid reasons will lead to an automatic RE FERRAL. You will then be asked to complete an alternative assignment. 9. 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

Page | 5 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Unit 04: Database Design & Development Assignment 01

L.B Pamudi Manushika HND BATCH 23

Page | 6 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Table of Contents Figure numbers .................................................................................................................... 7 Activity 1.............................................................................................................................. 9 Activity 1.1..................................................................................................................... 10 1.1.1

Introduction .................................................................................................. 10

1.1.2

The user and system requirements of Polly pipe ......................................... 11

Functional requirements ............................................................................................. 11 Non – functional requirements ................................................................................... 12 System requirements .................................................................................................. 12 1.1.3 Explanation of Polly pipe’s ER Diagram .......................................................... 13 1.1.4 Er diagram for Polly Pipe...................................................................................... 14 1.1.5 Logical Schema ..................................................................................................... 15 1.1.6 Data normalization ................................................................................................ 16 (Watt, 2021) ................................................................................................................. 17 1.1.7 Provided logical schema normalization ................................................................ 18

Figure numbers Figure 1 DBMS .................................................................................................................. 10 Page | 7 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Figure 2 ER Diagram for Polly pipe (Author 2021) .......................................................... 14 Figure 3 logical schema for Polly Pipe (Author 2021) ...................................................... 15 Figure 4 (Author 2021) ...................................................................................................... 20 Figure 5 (Author 2021) ...................................................................................................... 20 Figure 6(Author 2021) ....................................................................................................... 21 Figure 7 Login form (2021 Author) ................................................................................... 23 Figure 8 Main form (Author 2021) .................................................................................... 24 Figure 9 Employee form (Author 2021) ............................................................................ 25 Figure 10 Customer form (Author 2021) ........................................................................... 26 Figure 11 Installation form (Author 2021) ........................................................................ 27 Figure 12 Equipment form (Author 2021) ......................................................................... 28 Figure 13 Payment form (Author 2021) ............................................................................ 29 Figure 14(Author 2021) ..................................................................................................... 36 Figure 15(Author 2021) ..................................................................................................... 36 Figure 16(Author 2021) ..................................................................................................... 37 Figure 17(Author 2021) ..................................................................................................... 37 Figure 18(Author 2021) ..................................................................................................... 38 Figure 19 Login form (Author 2021) ................................................................................. 39 Figure 20 Employee form(Author 2021) ........................................................................... 40 Figure 21 Customer form (Author 2021) ........................................................................... 41 Figure 22 Equipment form (Author 2021) ......................................................................... 42 Figure 23 Installation form (Author 2021) ........................................................................ 43 Figure 24 Payment form (Author 2021) ............................................................................ 44 Figure 25 Select statement (Author 2021) ......................................................................... 46 Figure 26 Select form (Author 2021) ................................................................................. 47 Figure 27 Where form (Author 2021) ................................................................................ 47 Figure 28 Select form (Author 2021) ................................................................................. 48 Figure 29 Update statement (Author 2021) ....................................................................... 48 Figure 30 Update statement (Author 2021) ....................................................................... 49 Figure 31 Between statement (Author 2021) ..................................................................... 49 Figure 32 In statement (Author 2021)................................................................................ 50 Figure 33 Group statement (Author 2021) ......................................................................... 51 Figure 34 Order statement (Author 2021).......................................................................... 52 Figure 35 User guide (Author 2021) .................................................................................. 61 Figure 36 User guide (Author 2021) .................................................................................. 62 Figure 37 User guide (Author 2021) .................................................................................. 63 Table 1Normalisation (Author 2021) ................................................................................. 18 Table 2Normalisation (Author 2021) ................................................................................. 18 Table 3 Test case01 (Author 2021) ................................................................................... 53 Table 4 Test case 02 (Author 2021) ................................................................................... 54 Table 5 Test case 03 (Author 2021) ................................................................................... 55 Table 6 (Author 2021)........................................................................................................ 59 Table 7 (Author 2021)........................................................................................................ 60

Page | 8 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Activity 1 LO1 Use an appropriate design tool to design a relational database system for a substantial problem

Page | 9 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Activity 1.1 What is database? A database is a collection of information or data stored electronically in a computer system. Such databases are managed by a database management system (DBMS). Processing of data in a database is done by using rows and columns in tabular form to make it easier to check the data. That way the data can be easily managed. Today, SQL (structured query language) is the most used query for writing and querying data.

Figure 1 DBMS (What is a dat database? abase? 2021)

1.1.1 Introduction

Polly pipe is a water sports provider and installer based in Braintree, England. Its manual database system wants to design and implement a database that meets the data requirements as computerized system. Therefore, this system was designed and developed the solution for water sports provider and installer to meet their business customers.

Polly pipe is maintaining their data in manual system. Manual system is a paper record.

Page | 10 L.B Pamudi Manushika

Unit 04 – Database Design & Development

In computerized system control the company’s data of maintainable assets through an asset register, easy to control Customer’s request of several installations, but each installation is tailor-made for a specific customer can include carpenters and masons as well as water installers, use equipment such as aquariums, air pumps and thermostats etc. Easy to control the issues and documentation of several installations. Database design process is,

1. Requirement’s collection and analysis 2. Conceptual design 3. Logical design/ data model mapping 4. Physical design

1.1.2 The user and system requirements of Polly pipe When defining requirements, two types of requirements are served different purposes. When identify the user requirements of the above scenario. They are,

1. Functional requirements 2. Non – functional requirements

Functional requirements

All the tasks required to upgrade the database are called functional requirements. Updating the data required to add to the system is one of the main requirements of the user. The customer's database should be able to enter the customer's data from the Poly Pipe company's database. Ability to enter information about employees, what duties they are assigned, etc. Provide the equipment needed for an installation or order, what type of equipment the organization needs, and the user needs to have information in the Page | 11 L.B Pamudi Manushika

Unit 04 – Database Design & Development

system. (Polly Pipe system should ability to give customer details, employee details, payments, installation, equipment details.) It should have creative interface.

Non – functional requirements

Non – functional requirements specify for not covered all the areas by functional requirements. The details in system design were implement by functional requirements. Various types of non – functional requirements are accessibility, audit, efficiency, security, accuracy, availability, backup and restore, capacity, certification, compliance, reliability, responsibility, virus guard, compatibility of software.

System requirements

System requirements are the building blocks developers use to develop the system. System requirements are categorized as either functional or non – functional requirements. Functional requirements differentiate something that a user needs to perform their work very well. For example, in the above scenario system should be able to provide the required information for customers several installations and handle daily operations that is functional requirements.

1. Hardware requirements and Software requirements

8GB Ram, HDD, SSD, Frequency 3-3.5 GHZ and more, Network interface bandwidth 100Mbps, SSD for SQL 200Gb, Operating system – windows 10,

Page | 12 L.B Pamudi Manushika

Unit 04 – Database Design & Development

Hard disk space 250GB+, intel i7 dual core processor, Printer, Fax, scanner, Restore and backup options, Anti-virus software.

1.1.3 Explanation of Polly pipe’s ER Diagram In Polly pipe company’s customers when ordered installation it will be installation by employees and customers pay payment. Company’s ER Diagram and Attributes are,

1. Customer - cus_id, cus_name, cus_address, cus_contact no: 2. Employee - emp_id, emp_name, emp_address, emp_contact no: emp_types, salary 3. Equipment – equi_id, equi_type, equi_name, equi_price, quantity 4. Installation – inst.id, inst. type, start job, end job, address 5. Payment - payment _id, paym_type, paym_date

Learner has identified some of the functional and non-functional requirements of the system.

Page | 13 L.B Pamudi Manushika

Unit 04 – Database Design & Development

1.1.4 Er diagram for Polly Pipe

Learner has identified the some main entities, attributes and relationships and have drawn the ER diagram

Figure 2 ER Diagram for Polly pipe (Author 2021)

Page | 14 L.B Pamudi Manushika

Unit 04 – Database Design & Development

1.1.5 Logical Schema

Figure 3 logical schema for Polly Pipe (Author 2021)

Learner has converted the ER diagram into a logical relational schema.

Page | 15 L.B Pamudi Manushika

Unit 04 – Database...