WEST LONDON COLLEGE OF BUSINESS & MANAGEMENT SCIENCES Department of Information Technology Assignment front cover sheet Qualification HND in Computing and Systems Development

Unit number and title Unit 33: Data Analysis and Design

Student Name Assessors Name J Heaton Date Issued 15/02/2016 Required completion date 03/06/2016

Submitted on

Assignment title Designing & implementing Student DBMS for WLCBMS College

LO Learning outcomes AC In this assessment you will have the opportunity to present evidence that shows you are able to: Task No. LO1 Understand data models and database technologies 1.1 Critically compare different data models and schemas 2

1.2 Critically discuss the benefits and limitations of different database technologies 1/2/6 1.3 Analyse different approaches to database design 1/2/3/6

LO2 Be able to design and implement relational database systems 2.1 Design a relational database system to meet a given requirement 2 2.2 Build a relational database system based on a prepared design 3

2.3 Apply a range of database tools and techniques to enhance the user interface 2 LO3 Be able to use manipulation and querying tools 3.1 Explain the benefits of using manipulation and query tools in a relational database system 4

3.2 Implement a query language into the relational database system 4 3.3 Critically evaluate how meaningful data has been extracted through the use of query tools 4

LO4 Be able to test and document relational database system 4.1 Critically review and test a relational database system 5 4.2 Create documentation to support the implementation and testing of a relational database system 5

4.3 Create user documentation for a developed relational database system 5 4.4 Explain how verification and validation has been addressed 5 4.5 Explain how control mechanisms have been used. 5

Learner declaration I confirm that the evidence submitted is my own work and all sources are correctly referenced.

Learner Signature: ______________________________________ Date: _______________

In this assessment you will have opportunities to provide evidence against the following criteria.

Grading Criteria

To achieve the criteria the evidence must show that the learner is able to: Task No. Type of Evidence Evidence (Page N0.) LO1: Understand data models and database technologies 1.1 Critically compare different data models and schemas. To do this you will need to look at hierarchical models against network and relational, data manipulation and data definition languages, data independence and data redundancy issues. With regard to schema you will need to critically compare tables field relationships, views indexes conceptual and physical schema and data dictionaries. 2 Report - Word

1.2 Critically discuss the benefits and limitations of different database technologies including dynamic storage, data mining and data warehousing, web enabled database applications, multimedia databases and document management systems and digital libraries. You should also look at the purpose of normalisation and discuss different normal forms. You should then discuss whether these are relevant or not to the requirements of the brief. 1/2/6 Report - Word 1.3 Analyse different approaches to database design, this should include top down and bottom up, tools and techniques such as entity analysis, entity relational diagrams, determinancy diagrams, data flow diagrams, entities, attributes and key identifiers, relationship types and enterprise rules, showing degrees of relationships, functional dependency, first second and third normal forms. 1/2/3/6 Report – Word / Database

LO2: Be able to design and implement relational database systems 2.1 Design a relational database system to meet the given requirements. Your design should specify data types, entity and referential constraints. Your design should be able to convert easily from the logical database design to a physical implementation and you should highlight the tools and techniques used. Ensure you take into account the issues regarding degrees of normalisation chosen and outline validity checks, data definition and control mechanisms employed. 2 Report - Word

2.2 Build a relational database system based on your prepared design, ensuring specification requirements, relational requirements and all other requirements are met, for example is there a need to integrate with any legacy systems, future company requirements, timescales and cost. 3 Report - Word 2.3 Apply a range of database tools and techniques to enhance the user interface to accommodate functionality, reliability, consistency and performance, providing a menu driven, HCI interface. 2 Report - Word

LO3: Be able to use manipulation and querying tools 3.1 Explain the benefits of using manipulation and query tools in a relational database system 4 Report – Word / Database 3.2 Implement a query language into the relational database system 4 Report – Word / Database 3.3 Critically evaluate how meaningful data has been extracted through the use of query tools 4 Report – Word / Database

LO4: Be able to test and document relational database system 4.1 Critically review and test a relational database system 5 Report - Word 4.2 Create documentation to support the implementation and testing of a relational database system 5 Report - Word 4.2 Create user documentation for a developed relational database system 5 Report - Word

4.4 Explain how verification and validation has been addressed 5 Report - Word 4.5 Explain how control mechanisms have been used. 5 Report - Word

In addition to the above PASS criteria, this assignment gives you the opportunity to submit evidence in order to achieve the following MERIT and DISTINCTION grades

Grade Descriptor Indicative characteristic/s Contextualisation

M1: Identify and apply strategies to find appropriate solutions • effective judgements have been made • complex problems with more than one variable have been explored

• an effective approach to study and research has been applied Identify and apply strategies When explaining the database design, the relationship between the entities and how the components of the database are used, learners will select specific appropriate examples. The explanation will be comprehensive and use appropriate terminology in descriptions and explanations.

M2: Select / design and apply appropriate methods / techniques • relevant theories and techniques have been applied • a range of methods and techniques have been applied • a range of sources of information has been used

• the selection of methods and techniques/sources has been justified • the design of methods and techniques/sources has been justified • complex information/data has been synthesised and processed

• appropriate learning methods/techniques have been applied Select, design and apply appropriate methods at this level to ensure all elements of the database are fully functional and meet current requirements for relational database management systems. You should produce good analytical reports using SQL. . and should discuss the access control mechanisms for the system.

M3: Present and communicate appropriate findings • the appropriate structure and approach has been used

• coherent, logical development of principles/concepts for the intended audience. • a range of methods of presentation has been used and technical language have been accurately used

• communication has taken place in familiar and unfamiliar contexts. • the communication is appropriate for familiar and unfamiliar audiences and appropriate media have been used. Present and communicate appropriate findings where clear evidence of planning and requirement analysis should be present indicating how the learner developed a range of ideas and selected the most appropriate for the solution. The learner will review their work on an on-going basis and their plan should be updated regularly to ensure work is completed on time. Records of production are likely to include a log or diary with production notes, diagrams, screenshots, etc. The database should have been fully tested with records of any problems logged.

D1: Use critical reflection to evaluate own work and justify valid conclusions. • Critical reflection has been used to evaluate own work.

• Conclusions are justified Use critical reflection to evaluate and explain fully how elements of the database are used by citing specific examples. The arguments made will be clear and expressed using fluent technical language. Fuller and more extensive explanations, better application of examples and provision of arguments to support and justify conclusions made.

D2: Take responsibility for managing and organising activities. • Demonstrate that activities have been managed and organised in an appropriate manner. Demonstrate responsibility by providing evidence of planning and management, including the creative design process, research and consideration of good practice and current requirements. You will consult the tutor (acting as client) to clarify items of the specification and seek approval for designs. You should provide evidence of planning the database design in the form of data flow diagrams (up to level 2), ERD, screen design, normalization etc. D3: Demonstrate convergent / lateral / creative thinking. • Demonstrate Innovation and creative thought has been applied

• Show your use of Tools to design and develop the required outcome Demonstrate lateral and creative thinking when producing your analytical reports using SQL. You should discuss the access control mechanisms best suited for the system with proper examples given.

Assignment brief Qualification HND in Computing and Systems Development Unit number and title Unit 33: Data Analysis and Design

Date Issued 09/02/2015 Required completion date 12/06/2015 Assessor Amrit Singh

Assignment title Designing & implementing Student DBMS for WLCBMS College

Purpose of this assignment The purpose of this assignment is to understand, design, query, implement, test and maintain a database(s). Learners will also have a theoretical insight into the requirement for designing a database that meets a given user or system requirement and that is functional, user friendly and robust. Scenario

Assume that you are required to build a Relational Database System for a driving school.

Task 1: Requirement Specification

Complete the normalisation task for the Driving School. Use the spreadsheet on Google drive assignments folder.

Task 2: Design Relational Database Systems

a) Describe & critically compare different data models and schemas. [AC-1.1]

b) Using Visual Paradigm create the Entity Relationship Diagram (ERD) identifying the main Entities and Relationships indicating the degree of relationship and optionality.

• Explain entity types and Key attributes and their types in a data model. • List the Primary key and foreign key(s) for each entity (list the name of the table it is in and the name of the table it references) stating the assumptions you made.

• For each relationship in your model, explain the choice of optionality in each direction. State any assumptions made. [AC-2.1 PART] c) Identify the individual steps of your normalisation for your design. How did you proceed from one step to another? What questions needed to be solved? [AC-1.2 PART, AC-1.3 PART] d) Critically discuss the functionality of your designed system. [AC-1.3 PART]

Task 3: Implement the Relational Database System

e) Build a relational database system based on a prepared design using any using appropriate RDBMS software, e.g. MS Access, SQL Server, Oracle etc. Load the data from the spreadsheets into entities/tables that you have identified from your design. Create the relationships. Make sure you follow all the necessary steps in building the database. In building the database, you are expected to include the following:

• Tables, fields, relationships

• Data types • Referential constraints

• Views, indexes etc. (optional) [AC-2.2]

f) Critically analyse the process of converting the logical database design to a physical implementation. [AC-1.3]

Task 4: Querying/Reporting database system a) Implement a query language into the relational database system. a. Create queries using the following:

i. numeric criteria ii. character criteria

iii. a range

iv. multi-table query v. ordering

vi. parameter input b. Formulate the SQL queries to produce some reports. Make sure that you have enough data in your database to show reports. [AC-3.2]

b) Explain the benefits of using manipulation and query tools in a relational database system. [AC-3.1]

c) Explain in simple English two SQL queries. What meaningful data is being retrieved for a user? One must be a multi-table query [AC-3.3]

Task 5: Testing & Documentation

a) Create a Test Plan that includes:

• What elements of the system are going to be tested(e.g. Tables, Relationships, Queries etc) • What types of testing will be carried out (e.g. Component testing, Integrative testing, Systems testing, Acceptance testing) • What test data values will be used to test the system (e.g. Normal values, Extreme values, Exceptional values)

b) Now critically review and test your relational database system. [AC-4.1]

c) Create documentation to support the implementation and testing of a relational database system. [AC-4.2] d) Explain how verification and validation (e.g. format check, check digits numerical data,

check data type etc) has been addressed. [AC- 4.4]

e) Explain how control mechanisms have been used.

[AC-4.5] f) Create user documentation for your developed relational database system. [AC- 4.3] Task 6: Discuss new database development technologies

Discuss the following with example: • Dynamic storage • Data mining and data warehousing

• Web enabled database applications • Multimedia databases

• Document management systems • Digital libraries [AC-1.2,1.3]

Learning outcomes:

On successful completion of this unit a learner will: Assessment Criteria

Evidence required to obtain a Pass

The learner must: Evidence required to obtain a Merit

The learner can:

Evidence required to obtain a Distinction

The learner can: LO1: Understand data models and database technologies (Task 1.2.3.6) 1.1: Critically compare different data models and schemas (Task 2) 1.2: Critically discuss the benefits and limitations of different database technologies (Task 1,2,6)

1.3: Analyse different approaches to database design (Task 1,2,3,6) 1. Critically compare different data models and schemas. To do this you will need to look at hierarchical models against network and relational, data manipulation and data definition languages, data independence and data redundancy issues. With regard to schema you will need to critically compare tables field relationships, views indexes conceptual and physical schema and data dictionaries. 1.2 Critically discuss the benefits and limitations of different database technologies including dynamic storage, data mining and data warehousing, web enabled database applications, multimedia databases and document management systems and digital libraries. You should also look at the purpose of normalisation and discuss different normal forms. You should then discuss whether these are relevant or not to the requirements of the brief. 1.3 Analyse different approaches to database design, this should include top down and bottom up, tools and techniques such as entity analysis, entity relational diagrams, determinancy diagrams, data flow diagrams, entities, attributes and key identifiers, relationship types and enterprise rules, showing degrees of relationships, functional dependency, first second and third normal forms. (Report – Word Document) M1: Identify and apply strategies When explaining the database design, the relationship between the entities and how the components of the database are used, learners will select specific appropriate examples. The explanation will be comprehensive and use appropriate terminology in descriptions and explanations.

LO2: Be able to design and implement relational database systems (Task 2,3) 2.1: Design a relational database system to meet a given requirement (Task 2)

2.2: Build a relational database system based on a prepared design (Task 3)

2.3: Apply a range of database tools and techniques to enhance the user interface (Task 2)

2. Design a relational database system to meet the given requirements. Your design should specify data types, entity and referential constraints. Your design should be able to convert easily from the logical database design to a physical implementation and you should highlight the tools and techniques used. Ensure you take into account the issues regarding degrees of normalisation chosen and outline validity checks, data definition and control mechanisms employed. 2.2: Build a relational database system based on a prepared design using any using appropriate RDBMS software, e.g. MS Access, SQL Server, Oracle etc. ensuring specification requirements, relational requirements and all other requirements are met, for example is there a need to integrate with any legacy systems, future company requirements, timescales and cost.

2.3a: Draw Entity Relationship Diagram (ERD) identifying the main Entities and Relationships indicating the degree of relationship and options.

2.3b: Sketch out your design as a series of screens or storyboard. Apply a range of database tools and techniques to enhance the user interface to accommodate functionality, reliability, consistency and performance, providing a menu driven, HCI interface.

(Report – Word Document - Database) M2: Select, design and apply appropriate methods at this level to ensure all elements of the database are fully functional and meet current requirements for relational database management systems. You should produce good analytical reports using SQL. . and should discuss the access control mechanisms for the system.

D1: Use critical reflection to evaluate and explain fully how elements of the database are used by citing specific examples. The arguments made will be clear and expressed using fluent technical language. Fuller and more extensive explanations, better application of examples and provision of arguments to support and justify conclusions made.

LO3: Be able to use manipulation and querying tools (Task 4) 3.1: Explain the benefits of using manipulation and query tools in a relational database system (Task 4) 3.2: Implement a query language into the relational database system (Task 4)

3.3: Critically evaluate how meaningful data has been extracted through the use of query tools (Task 4) 3.1a: Explain the benefits of using manipulation and query tools in a relational database system

3.2a: Implement a query language into the relational database system.

Formulate the SQL queries to produce some reports. Make sure that you have enough data in your database to show reports. 3.3a: Critically evaluate how meaningful data has been extracted through the use of query tools

(Report – Word Document - Database) D2: Demonstrate responsibility by providing evidence of planning and management, including the creative design process, research and consideration of good practice and current requirements. You will consult the tutor (acting as client) to clarify items of the specification and seek approval for designs. You should provide evidence of planning the database design in the form of data flow diagrams (up to level 2), ERD, screen design, normalization etc. LO4: Be able to test and document relational database system (Task 5) 4.1: Critically review and test a relational database system (Task 5)

4.2: Create documentation to support the implementation and testing of a relational database system (Task 5)

4.3: Create user documentation for a developed relational database system (Task 5)

4.4: Explain how verification and validation has been addressed (Task 5)

4.5: Explain how control mechanisms have been used (Task 5) 4.1a: Create a Test Plan that includes: • What elements of the system are going to be tested(e.g. Tables, Relationships, Queries etc) • What types of testing will be carried out (e.g. Component testing, Integrative testing, Systems testing, Acceptance testing) • What test data values will be used to test the system (e.g. Normal values, Extreme values, Exceptional values) Now critically review and test your relational database system.

4.2a: Create a Test Plan that includes: • What elements of the system are going to be tested(e.g. Tables, Relationships, Queries etc) • What types of testing will be carried out (e.g. Component testing, Integrative testing, Systems testing, Acceptance testing) • What test data values will be used to test the system (e.g. Normal values, Extreme values, Exceptional values)

Create documentation to support the implementation and testing of a relational database system. 4.3a: Create a Test Plan that includes: • What elements of the system are going to be tested(e.g. Tables, Relationships, Queries etc) • What types of testing will be carried out (e.g. Component testing, Integrative testing, Systems testing, Acceptance testing)

• What test data values will be used to test the system (e.g. Normal values, Extreme values, Exceptional values) Create user documentation for your developed relational database system 4.4a: Create a Test Plan that includes: • What elements of the system are going to be tested(e.g. Tables, Relationships, Queries etc)

• What types of testing will be carried out (e.g. Component testing, Integrative testing, Systems testing, Acceptance testing) • What test data values will be used to test the system (e.g. Normal values, Extreme values, Exceptional values) Explain how verification and validation (e.g. format check, check digits numerical data, check data types etc...) has been addressed.

4.5a: Create a Test Plan that includes: • What elements of the system are going to be tested(e.g. Tables, Relationships, Queries etc) • What types of testing will be carried out (e.g. Component testing, Integrative testing, Systems testing, Acceptance testing) • What test data values will be used to test the system (e.g. Normal values, Extreme values, Exceptional values)

Explain how control mechanisms have been used. (Report – Word Document) M3: Present and communicate appropriate findings where clear evidence of planning and requirement analysis should be present indicating how the learner developed a range of ideas and selected the most appropriate for the solution. The learner will review their work on an on-going basis and their plan should be updated regularly to ensure work is completed on time. Records of production are likely to include a log or diary with production notes, diagrams, screenshots, etc. The database should have been fully tested with records of any problems logged. D3: Demonstrate lateral and creative thinking when producing your analytical reports using SQL. You should discuss the access control mechanisms best suited for the system with proper examples given.

Submission Requirements: 1. An electronic copy of your report 2. Professional Structured report 3. Word count guide ~ 3-4000 words

Student must meet the pass criteria to achieve Merit and/or Distinction. This brief has been verified as being fit for purpose

Assessor J Heaton Signature Date Internal verifier A Singh Signature Date