Learning Outcomes
All Learning Outcomes are assessed in this assignment:
Discuss, analyse and apply techniques for information, data and knowledge management.
Analyse and explain the implications of technological innovation for data usage, data security and data management.
Reflect on the limitations of knowledge management systems and technologies in a socio-technical and organisational context.
Critically assess and implement different forms of data models and database architecture to solve a given organisational scenario.
Submission
You must submit via Moodle the following files:
• Word document
o Database Design and related documentation
o Report
• Database/Program
o Database must be exported in appropriate format, please use your student number as the database name e.g. st20093309 .sql and include in a zip archive.
Database Design for a Bank Loan Application Form
Overview
A local bank requires a computer system to store information on personal loan applicants. The data is presently collected and stored using the application form provided below.
The data captured in the “Bank Loan Application Form” will be stored in SQL Server Express Database.
Bank Loan Application
Task 1
You are required to analyse the attached Bank Loan Application Form.
Normalize the data to a 3rd normal form (3NF).
Using the above:
1. Create an Entity Relationship Diagram
2. Create Database Schema
3. Create Data Dictionary
4. Implement the above using Microsoft SQL Server Express
5. Produce at least four queries making use of group, inner, left and right joins demonstrate the tables and relations implemented.
Task 2
In addition to the practical element of the assignment you are required to produce a short report evaluating the database design and development you have undertaken and related issues.
This will include:
– An analysis of the data security and data management considerations connected with the “Bank Loan Application Form” above.
– Evaluate the use of a relational database for the “bank loan application form” and discuss it benefits and shortcomings in a banking context.
– Provide a rationale for the use of the relational model over other database models in the context of the “Bank Loan Application”
NOTE: The assignment will be discussed in lectures and support provided in tutorials, including the use of SQL Server Express and SQL Management Studio
Marking Scheme
Task Marks
1 Database Design Documentation
Implementation 30
20
2 Report
Identification of Security and Data Management issues
Evaluation of database models in the context of the given scenario
Rationale for the use of the selected model
Research and referencing
10
10
15
15
Marking Criteria
Mark Range Criteria
70 – 100 The data from the form has been successfully normalised to 3NF. In the ER diagram there are no missing entities, appropriate names are used and there are no redundant entities, etc. The cardinalities and optionalities all shown and correct. A complete list of relations is present, showing all applicable attributes, primary keys and foreign keys. The data dictionary is complete and agrees with the ER diagram.
The design document as a whole represents a sophisticated, comprehensive solution, well presented with correct diagrams/notation. The solution is easy to read and understand
The SQL presented implements the database designed above including all relevant business rules extracted from the form. The example queries are present and evidence is presented to show they are both functional and robust (i.e. include attempts to violate business rules).
The report is well written and lists the relevant security and data management issues. The report considers the strengths and weaknesses of a number of alternative database modules in the context of the scenario provided and provides a well-supported rationale for the selected model. A wide selection of references have been used correctly to support the arguments given.
60 – 69 The data from the form has been successfully normalised to 3NF. In the ER diagram there are no missing entities, appropriate names are used and there are no redundant entities, etc. The cardinalities and optionalities all shown and correct. A list of relations is present, showing all applicable attributes, primary keys and foreign keys. The data dictionary is nearly complete and correlates well with the ER diagram.
The design document as a whole represents a reasonably complete solution the standard of presentation is high with good use of the relevant diagrams/notation.
The SQL presented implements the database designed above including some business rules extracted from the form. The example queries are present and evidence is presented to show they work.
The report lists the relevant security and data management issues. The strengths and weaknesses of a number of alternative database models are considered with some consideration of the given scenario. A supported rationale for the selected model is presented supported by a selection of references.
50 – 59 The data from the form has been successfully normalised to 3NF. In the ER diagram there are no missing entities and there are no redundant entities, etc. Some cardinalities and optionalities are correct. A list of relations is present, showing all applicable attributes, primary keys and foreign keys. The data dictionary is present.
The design document as a whole represents a reasonable solution the standard of presentation is acceptable and the correct diagrams/notation is used.
The SQL presented implements all or a portion of the database designed above, some of example queries are present and evidence is presented to show they work.
The report lists the security and data management issues, but does not link them to the scenario. The strengths and weaknesses of a number of alternative database models are listed, but not in the context of the scenario. A rationale for the selected model is presented supported by a handful of references.
40 – 49 An ER diagram is presented however there are issues e.g. missing entities, redundant entities, etc. The data dictionary is present. The design document demonstrates some progress towards a coherent solution. The standard of documentation presented is poor and the notation used is incomplete/incorrect.
The SQL presented implements a portion of the database designed above, no examples queries are evidenced.
The report is incomplete but mostly correct and supported by a small number of references.
35 – 39 An ER diagram is presented however there are issues e.g. missing entities, redundant entities, etc. The design document demonstrates some progress towards a coherent solution. The standard of documentation presented is poor and the notation used is incomplete/incorrect.
The SQL presented implements or is close to implementing a portion of the database designed above.
The report is incomplete, factually incorrect or based on opinion only.
Under 35 Design documentation is absent.
No SQL / Database file is presented.
