Computing and Systems Development Case Study Assignment

Purpose of this assignment 

You will explore database architecture, DBMS and the use of databases in an organisational context. Databases has been the engine behind the information revolution, databases enable the users and companies to store, model and retrieve data in an efficient way. Database development is a mature field with solid grounding in well-established techniques and practises. Database Management Systems (DBMS) provide the integrated tools and interfaces by which the organisation can manage and maintain their data repositories and use the required information to assist in the effective running of the organisation. This assignment will enable you to develop your understanding of the concepts and issues relating to databases and database design as well as the practical skills to translate that understanding into the design and creation of complex databases. You will investigate database design techniques and apply their theoretical understanding to design, create and document a relational database system using techniques such as normalised E-R models and Standard Query Language (SQL). (This assignment brief must be read in conjunction with Edexcel Unit 17 R/601/0447 specification, provided separately) 

Scenario 

DoubleClick Consultancy Limited 

You are to assume the role of a database consultant working for DoubleClick Consultancy Limited, a computing consultancy company. 

Client: 

The vehicle information firm HPI is your client, this company provide details on vehicles to detect fraud. HPI is still using the old-fashioned paper-based file system along with a rudimentary industry database which contained some vehicle details. HPI now wish to move on to a complete and modern electronic storage system. The company employed you to carry out a research and produce a report that will inform them about different issues and uses of databases. They also need help with difficult decisions such as what type of database to implement and which developmental methodology to follow.

Purpose for the new system: 

Database to tackle car fraudsters Dishonest motorists will find it harder to get away with filing bogus insurance claims, thanks to a new database. The database, to be run by vehicle information firm HPI, will log details of written-off or stolen vehicles. This will help insurers identify fraudsters who insure their car with several firms and then file multiple claims following a staged accident.

The Association of British Insurers (ABI) said that this type of database could help cut insurance costs for honest motorists.

 Savings 

The ABI estimates that the database could save insurers £20,000 a day in detecting and deterring fraudulent insurance claims. It added that insurers could be paying out as much as £500m a year to fraudulent claimants. "Dishonest motor insurance claims are paid for by honest customers, this is why insurers are determined to stamp them out," ABI spokesman said, in addition, "Not only will it help detect more motor fraud but it will act as a strong deterrent, making anyone thinking of cheating on their insurance think twice." The new database will combine information from insurers and the Police National Computer.

Previously, insurers tracked fraudsters by searching for inconsistencies in claim forms. They used a rudimentary industry database which contained some vehicle details. The database system should provide: 

(a) The names and other details of the case worker for a particular fraud inquiry. 

(d) The details of all vehicles under investigation.

(e) The differentiation of vehicles through registration numbers. 

(f) The number of drivers linked to a particular vehicle. 

(g) The details of Vehicle with MOT based filter. 

(k) The cars classified as written off or stolen by insurers. 

(l) The cases for specified dates. 

(m) The cars with multiple insurance. 

(n) The report that shows suspected fraud cases by date. 

(o) The report to show all cars classified as written off or stolen by date. 

(p) The report of drivers disqualified to drive by date. 

(q) The report of drivers with criminal records. 

(r) The maintenance documents for the technical staff to support database system. 

(s) The user manual to show users how to use the database system. 

Task 1 (LO1 AC 1.1, 1.2 and M1)

You will explain the following concepts and discuss the importance of these in database management systems within organisational environments: 1. Different Database Architectures 2. Data Consistency 3. Methods of data access, data recovery and data security 4. Role of database administrator in keeping data integrity 5. Role of Industry standards (e.g. Microsoft SQL, Oracle, Sybase, MySQL) The concepts discussed will be presented in a structured report for the directors of HPI company. This report will help them to make strategic decisions on the scale and modalities of the required database system.

 Task 2 (LO2 AC 2.1, 2.2, 3.2 and M1, M2, D3 )

You will analyse the requirements of the client for the database system and translate these requirements to SMART (Specific, Measurable, Achievable, Realistic and Timely) Objectives (requirement specifications). These objectives will inform the design and evaluation process including the verification during the implementation and testing phase. You will provide reflections on the importance of your chosen system development methodology for the database design process. You will show the understanding of database design methods and will provide the design models according to the requirement specifications.

The developed designs and models must show the normalisation process up-to 3NF with at-least 4 inter-relational tables. You must also provide a running commentary of your design solutions (e.g. using E-R models, data dictionaries etc.) explaining your rationale and each design decision made. Your designs will also show appropriate user interface for your database systems according to the specification requirements.

 Task 3 (LO3 AC 3.1, 3.2, 3.3, 3.4 and M2, D1, D3)

You will implement your solution using SQL software and appropriate tools within the development environment. You will test the database system according to the specification, using suitable technologies. The tests will be carried out for coding errors, functional requirements, usability, consistency, integration, UAT, layout & style and accessibility. You will show the implementation of verification and validation techniques throughout implementation and testing phase. You will provide the user manual and maintenance documents in a structured way. These documentations will be used by the users of the system and technical staff in-charge of administration and maintenance of the database system respectively. 

Task 4 (LO4 AC 1.2, 3.4 and D1)

You will get the users feedback for the system and analyse and comment on the feedback. Your analysis should show what worked for the users and how to improve the system for future iterations. You will provide reflections on the system development process and lessons learnt. You will suggest the improvements for the future based on the analyses of the feedback and your own reflections for the whole system development process.