Database Design & Development

Assignment - Manzaneque Limited

Learning Outcome 1: Use an appropriate design tool to design a relational database system for a substantial problem
Learning Outcome 2: Develop a fully-functional relational database system, based on an existing system design
Learning Outcome 3: Test the system against user and system requirements
Learning Outcome 4: Produce technical and user documentation.

Transferable skills and competencies developed

Computing-related cognitive skills:
? Demonstrate knowledge and understanding of essential facts, concepts, principles and theories relating to computing and computer applications
? Use such knowledge and understanding in the modelling and design of computer-based systems for the purposes of comprehension, communication, prediction and the understanding of trade-offs
? Recognise and analyse criteria and specifications appropriate to specific problems, and plan strategies for their solutions
? Critical evaluation and testing: analyse the extent to which a computer-based system meets the criteria defined for its current use and future development
? Methods and tools: deploy appropriate theory, practices and tools for the design, implementation and evaluation of computer-based systems.

Computing-related practical skills
? The ability to specify, design and construct reliable, secure and usable computer-based systems
? The ability to evaluate systems in terms of quality attributes and possible trade-offs presented within the given problem
? The ability to deploy effectively the tools used for the construction and documentation of computer applications, with particular emphasis on understanding the whole process involved in the effective deployment of computers to solve practical problems
? The ability to critically evaluate and analyse complex problems, including those with incomplete information, and devise appropriate solutions, within the constraints of a budget.
Generic skills for employability
? Intellectual skills: critical thinking; making a case; numeracy and literacy
? Self-management: self-awareness and reflection; goal setting and action planning
? Independence and adaptability; acting on initiative; innovation and creativity

Vocational scenario

Manzaneque Limited is a large real estate company that specialises in residential, commercial and industrial properties. It has a large IT function that supports development of raw land, property management, brokerages, lending and other professional services such as lawyers, interior designers and construction workers. Owing to further expansion, it is setting up an IT Helpdesk to handle hardware and software problems concerning the IT systems.
As a Database Developer, you have been tasked with designing and building the new system. Your role includes designing, developing and implementing database systems based on customer requirements. You are also responsible for optimising the database system for performance efficiency, as well as testing and troubleshooting and performing bug fixes.

System Required
When someone in the company has a problem, they can contact the helpdesk. One of the helpdesk operators will attempt to deal with the enquiry, but if an immediate answer cannot be given the problem is passed to one of several specialists.
An Information System is needed to log and track the helpdesk queries. This will enable analysts to see how the equipment is performing overall, whether the helpdesk specialists are sufficiently resourced to solve problems in an acceptable time and whether there are subject areas where employee training is needed.
Proposed System Operation
? When a new call comes into the helpdesk, the names of the caller and helpdesk operator are logged, along with the time of the call, the serial number of the computer and, if relevant, the operating system and software being used
? The caller's name will be checked against a register of all personnel to retrieve the caller's ID number, job title and department
? Their equipment will also be checked against a register of equipment to find the equipment type and make. Their software will be checked to see if it is under a valid license
? Every call is logged and each problem is given a problem number, which is supplied to the caller so it can be quoted on any subsequent calls about the same problem
? The helpdesk operator will also record notes and descriptions of the problem. A reason for each call is always recorded even if it is just a note to say how desperate the caller is getting (e.g. in the case of a follow-up call)
? When a problem is first reported, the helpdesk operator will also allocate a problem type, selecting it from a list of problem types. It is the skill of the operator to know what problem type is most relevant and how specific the problem is

? Some problem types are refinements of more general problem types and so it is possible that the problem type allocation may be altered later if more information becomes available
? When the problem area is identified the helpdesk operator can look up previous problems of the same type to see if the problem has occurred before and, if so, how it was resolved
? It is also possible to look up previous problems with the same equipment or from the same caller to see if there were other related problems
? If the problem can't be solved immediately, the helpdesk operator will use the system to
look up which specialist to refer the problem to
? Each specialist will be an expert in one or more problem types
? If there is no specialist listed for a more specific problem type, then a specialist from the more general problem type will be used
? The system will also list how many problems the specialist is currently working on so that if there is more than one specialist for a problem type, the specialist who is currently the least loaded can be allocated
? When a problem is eventually resolved, the helpdesk operator or the specialist will log the date and time it is resolved and record some indication of how the problem was resolved and the time taken to resolve the problem.

Assignment activity

Based on the Help Desk scenario you will need to:
1) Create a formal business proposal for Manzaneque Limited. It should include user and system requirements that develop a fully functional design of the relational database for the IT helpdesk system, including ERDs, normalisation, data validation, output designs and interface diagrams evaluating the reasons that the design will meet the requirements as given effectively
2) Create and fully implement the database Help Desk system for Manzaneque Limited, according to your design, including user interface, inputs, outputs, data validation, and querying across multiple tables using a query language. The system must also be fully secure and maintainable
3) Develop and action a test plan with effective tests:
a) This must include effective data, and extreme and erroneous data in order to build an accurate picture of the viability of the database
b) Tests must assess the breadth and usability of the programme as well as the requirements in the brief
c) Tests must include meaningful data and elements that would be in a successful implementation.
4) Produce a written report:
a) Evaluating the effectiveness of the database against user requirements and suggesting improvements
b) Include an assessment of the queries and data extracted in order to produce meaningful management information
c) Assess the effectiveness of testing, including an explanation of the choice of the test data used.
5) Produce a series of user videos as follows:
a) Technical: showing the backend of the system and its associated setup, including data flow diagrams and flowcharts, describing how the system works
b) User: showcasing the system from a user perspective.