Download Database Design & Development for Polly Pipe and more Papers Computer science in PDF only on Docsity!
Higher Nationals
Internal verification of assessment decisions – BTEC (RQF)
INTERNAL VERIFICATION – ASSESSMENT DECISIONS Programme title BTEC^ HND^ in Computing Assessor Internal Verifier Unit(s) Unit 04: Database Design & Development Assignment title Database Solution for Polly Pipe Student’s name H. A. D. Danuka Tarindu Seneviratne 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 1
Verifier signature Date Programme Leader signature (if required) Date 2
Assignment Feedback
Formative Feedback: Assessor to Student Action Plan Summative feedback Feedback: Student to Assessor Assessor signature Date Student signature Date 4
Pearson Higher Nationals in
Computing
Unit 04: Database Design & Development
Assignment 01
- 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.
- 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 7
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 Pearson, UK.
- I understand that my assignment will not be considered as submitted if this document is not attached to the assignment. Student’s Signature: [email protected] Date: 2021/06/ ( Provide E-mail ID ) ( Provide Submission Date ) 8
tailor-made fo classified by ty assigned to ea are often very and masons facilities use e pumps and th computers in a Below are e Polly Pipe cur Staff Managem Staff Number Name Type SHA1 Dave Clark Plumber SHA8 John Smith Installation Manager SHA2 Freddy Davies Aquatics installer SHA11 McCloud Aquatics installer SHA23 Satpal Singh Plumber SHA66 Winstn Kodogo Aquatics installer SHA55 Alison Smith Brick Layer Equipment Typ Type Equipment Tanks 20 gallon tank, 50 gallon tank, 100 gallon tank, 200 gallon tank Thermostats Standard, Super Air Pumps Standard, Super Filters Air driven, Undergravel Instillation Man
Activity 1
1.1. Identif requirements to design a database for the above scenario and design a relational database sys Model) by including identifiers (primary Key) of entities and cardinalities, participations of relatio 10 Installation ID Installation Type Installation Name and Address Customer Equipment Types of Staff Required Period of Staff assignm ent 234 Freshwater Tropical Oak House, 17 Wroxton Road, Hertfordshi re, H5 667 Lee A. sun 2 air pumps 200 gallons fish tank 1 x standard thermostat 1 x Carpenter 1 x Aquatics installer 1 x Electrician From 1st Septemb er 2012 654 Freshwater Cold Bayliss House, Orange Street, Kent, K 988 ` Sally Dench 2 air pumps 200 gallons fish tank Large Gravel Bag 2 x standard thermostat s 5 x Carpenters 1 x Installation Manager 1 x Aquatics installer 1 x Plumber 3 x Labourers 1 st June 2005 – 1 st June 2011 767 Marine Eaglestone Castle, Eaglestone , Kent Perry Vanderru ne 2 x 200 gallons fish tanks 500 Wood panels 10 x Carpenters 2 x Installation Manager 1 x Aquatics installer 1 x Plumber 3 x Labourers From 30 th June 2012 943 Marine 23 Sackville Street, Wilts. W Eric Mackinto sh 2 air pumps 200 gallons fish tank 1 x standard thermostat No staff required 157 Freshwater Tropical Humbertso n Castle, Kent, K Perry Vanderru ne 2 air pumps 400 gallons fish tank 3 x standard thermostat 1 x Aquatics installer 1 st Septemb er 2005
logical database design using relational database model including primary keys foreign keys an contain at least five interrelated tables. Check whether the provided logical design is normalised. removing the anomalies. (Note:-It is allowed to have your own assumptions and related attributes within the scope of the case study given)
1.2. Design set of simple interfaces to input and output for the above scenario using Wireframe
Evaluate the effectiveness of the given design (ERD and Logical design) in terms of the identified user
Activity 2
Activity 2. a. Develop a relational database system according to the ER diagram you have created (Us evidence of the use of a suitable IDE to create a simple interface to insert, update and delete proper security mechanisms in the developed database. Evaluate the database solution developed and its effectiveness with relevant to the user an system security mechanisms (EX: -User groups, access permissions) and the maintenance of th Activity 2. a. Explain the usage of DML with below mentioned queries by giving at least one single e developed database. Assess the usage of the below SQL statements with the examples from that the data extracted through them are meaningful and relevant to the given scenario. Select/ Where / Update / Between / In / Group by / Order by / Having
Activity 3
Activity 3. Provide a suitable test plan to test the system against user and system requirements. provide releva have implemented. Assess how the selected test data can be used to improve the effectiveness of te Note:- Learner needs to give expected results in a tabular format and screenshots of the actual results with the conclusi Activity 3. Get independent feedback on your database solution from the non-technical users and some deve interviews or any other feedback collecting method) and make recommendations and suggestions conclusion/recommendations section. 11
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 Evaluate the database in terms of improvements needed to ensure the continued effectiveness of the system. 13
ACKNOWLEDGEMENT
Especially note of thanks Ms. Indumini Chanikawho is visiting lecturer of programming Subject at E-soft Metro Campus Colombo 04 and for her support & expert guidance. I would like to express my gratitude towards my mother family members and my loved ones, encouragement which helped me in the completion of this assignment. I would like to extend my sincere thanks to all of them. My thanks and appreciations also go to my classmates in developing the project and to the people who have willingly helped me out with their abilities. Thank you, H. A. D. Danuka Tarindu Seneviratne COL/E- 14
Customer ID - Attribute Name - Attribute Address - Attribute Contact Number – Attribute Staff – Entity Staff ID - Attribute Name - Attribute Address - Attribute Contact Number - Attribute Staff Type – Attribute Equipment – Entity Equipment type – Attribute Equipment code – Attribute Instillation – Entity Instillation ID - Attribute Instillation type - Attribute Instillation Name and address - Attribute Customer - Attribute Equipment Required - Attribute Staff Required - Attribute Period of Assignment – Attribute Entity Relationship Diagram (ERD) Tool Data modeling is often the initial step in database design, with designers first creating a conceptual model of how data items relate to one another. This is followed by a transition from the conceptual model to the logical model, and finally to the physical schema. These diagrams can be used as a template for building new software or re-engineering a legacy application. MAGE ERD EKA ATTACHED KARANNA ONE 16
https://www.google.com/url?sa=i&url=https%3A%2F%2Fmedium.com%2Fomarelgabrys- blog%2Fdatabase-modeling-entity-relationship-diagram-part-5- 352c5a8859e5&psig=AOvVaw1L_ZTGBVyksZoivJTGVT4c&ust=1623314640731000&so urce=images&cd=vfe&ved=0CAIQjRxqFwoTCIjJxuCUivECFQAAAAAdAAAAABAr 17
We have data from four Computer Science students in the table above. As can be seen, data for the fields branch, hod (Head of Department), and office_tel is repeated for students in the same college branch, which is known as Data Redundancy. Insertion Anomaly Suppose that for a new admission, data for the student cannot be inserted until and unless the student chooses a branch, or else the branch information must be set to NULL. Furthermore, if we need to insert data for 100 students from the same branch, the branch information will be repeated for all 100 students. Update Anomaly What happens if Mr. X drops out of college? or is no longer the department's head of computer science? In such a situation, all student records will need to be updated, and if we miss one by accident, data inconsistency will result. This is an Update anomaly. Deletion Anomaly Two types of information are saved combined in our Student table: student information and branch information. As a result, if student records are removed at the end of the academic year, we will also lose branch information. This is a Deletion anomaly. Normalization Rule The following are the normal forms of normalization rules:
- First Normal Form
- Second Normal Form
- Third Normal Form
- BCNF
- Fourth Normal Form 19 rollno name branch hod office_tel 201 Amal CSE Mr. X 53337 202 Binara CSE Mr. X 53337 203 Charith CSE Mr. X 53337 204 Kamal CSE Mr. X 53337
- 1NF –There should be no multivalued columns in the table in the First Normal Form, each column should have values of the same datatype, each column should have a unique name, and the order of the data is irrelevant.
- 2NF –There should be no partial dependencies in the Second Normal Form, and the table should include all of the properties of the First Normal Form.
- 3NF – There should be no transitive dependencies in the Third Normal Form, and the table should include all of the features of the Second Normal Form. 4.If there are any more anomalies after completing the third stage of database normalization, BCNF (Boyce-Codd Normal Form) can be utilized.
- BCNF – The table should have all of the features of the Third Normal Form and for any dependency A B, A should be the super key. Certain levels of database standardization, such as 4NF, 5NF, and so on, can be used to remove more abnormalities. The below tables have been brought into the Second Normal Form 20 H. A. D. D. T. Senevirtne Unit 4: Database Design & Development Assignment 01 rollno name Subjects Teacher Score 201 Amal Computer, Maths, Science Computer teacher, Maths teacher, Science teacher
202 Binara Science, Maths Science teacher, Maths teacher
203 Charith Business Studies Business Studies teacher
rollno name Subjects Teacher Score 201 Amal Computer Computer teacher 40 201 Amal Maths Maths teacher 50 201 Amal Science Science teacher 80 202 Binara Science Science teacher 50 202 Binara Maths Maths teacher 70 203 Charith Business Studies Business Studies teacher
rollno name 201 Amal 202 Binara 203 Charith
