Database Management System Project: A Case Study of Bank of Scotland
VerifiedAdded on 2025/04/11
|56
|7192
|395
AI Summary
This project demonstrates the design and implementation of a database management system for Bank of Scotland. Desklib provides past papers and solved assignments.
UNIT 38: DATABASE MANAGEMENT
SYSTEMS
SYSTEMS
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Contents
Introduction:...............................................................................................................................5
LO1 Analyse different types of database management systems:...............................................6
P1 Compare and contrast the different types of database models:.........................................6
M1 Access how relational database models and the process of normalization can provide
reliable and efficient data structure:.....................................................................................10
D1 Critically evaluates different database management systems:.......................................13
LO2 Design a database management system using a relational model to meet client
requirements:............................................................................................................................15
P2 Produce a design for a relational database management system:...................................15
M2 Analyse how the design will optimise system performance:........................................28
D2 Critically evaluates the effectiveness of the system design and development against
client and system requirement:.............................................................................................29
LO3 Develop a database management system using a suitable platform:...............................30
P3 Develop a fully functional system which meets clients and system requirements, using
an open source language:.....................................................................................................30
P4 Test the system for functionality and performance:.......................................................44
M3 Implement effective features in the solution to handle concurrency, security, user
authorisations and data recovery:.........................................................................................48
LO4 Demonstrate the system administration and management tools available on the chosen
platform:...................................................................................................................................49
P5 demonstrate the tools available in the system to monitor and optimise system
performance and examine the audits logs:...........................................................................49
P6 Demonstrate the tools available in the system to manage security and authorisations:. 51
M4 Assess the effectiveness of the system administration and management tools available
on the platform identifying any shortcomings of the tool:...................................................52
D3 Assess any future improvements that may be required to ensure the continued
effectiveness of the database system:...................................................................................53
Conclusion:..............................................................................................................................54
References:...............................................................................................................................55
Introduction:...............................................................................................................................5
LO1 Analyse different types of database management systems:...............................................6
P1 Compare and contrast the different types of database models:.........................................6
M1 Access how relational database models and the process of normalization can provide
reliable and efficient data structure:.....................................................................................10
D1 Critically evaluates different database management systems:.......................................13
LO2 Design a database management system using a relational model to meet client
requirements:............................................................................................................................15
P2 Produce a design for a relational database management system:...................................15
M2 Analyse how the design will optimise system performance:........................................28
D2 Critically evaluates the effectiveness of the system design and development against
client and system requirement:.............................................................................................29
LO3 Develop a database management system using a suitable platform:...............................30
P3 Develop a fully functional system which meets clients and system requirements, using
an open source language:.....................................................................................................30
P4 Test the system for functionality and performance:.......................................................44
M3 Implement effective features in the solution to handle concurrency, security, user
authorisations and data recovery:.........................................................................................48
LO4 Demonstrate the system administration and management tools available on the chosen
platform:...................................................................................................................................49
P5 demonstrate the tools available in the system to monitor and optimise system
performance and examine the audits logs:...........................................................................49
P6 Demonstrate the tools available in the system to manage security and authorisations:. 51
M4 Assess the effectiveness of the system administration and management tools available
on the platform identifying any shortcomings of the tool:...................................................52
D3 Assess any future improvements that may be required to ensure the continued
effectiveness of the database system:...................................................................................53
Conclusion:..............................................................................................................................54
References:...............................................................................................................................55
List of images:
Figure 1: Relational model Structure.........................................................................................7
Figure 2: Network Model structure............................................................................................8
Figure 3: Hierarchical Model Structure.....................................................................................9
Figure 4: Object-oriented model structure.................................................................................9
Figure 5: Account Table...........................................................................................................17
Figure 6: Banker Table.............................................................................................................17
Figure 7: Branch Table.............................................................................................................17
Figure 8: Credit Card Table.....................................................................................................18
Figure 9: Customer Table.........................................................................................................18
Figure 10: Loan Table..............................................................................................................18
Figure 11: Account query.........................................................................................................18
Figure 12: Banker Query..........................................................................................................19
Figure 13: Branch Query..........................................................................................................19
Figure 14: Credit Card Query..................................................................................................19
Figure 15: Customer Query......................................................................................................19
Figure 16: Loan Query.............................................................................................................19
Figure 17: Account Form.........................................................................................................20
Figure 18: Banker Form...........................................................................................................20
Figure 19: Branch Form...........................................................................................................21
Figure 20: Credit Card Form....................................................................................................21
Figure 21: Customer Form.......................................................................................................22
Figure 22: Loan Form..............................................................................................................22
Figure 23: Account Report.......................................................................................................23
Figure 24: Banker Report.........................................................................................................23
Figure 25: Branch Report.........................................................................................................24
Figure 26: Credit Card Report..................................................................................................24
Figure 27: Customer Report.....................................................................................................25
Figure 28: Loan Report............................................................................................................25
Figure 29: Logical ERD...........................................................................................................26
Figure 30: Conceptual ERD.....................................................................................................27
Figure 31: Test Case 1..............................................................................................................27
Figure 32: Test Case 2.............................................................................................................27
Figure 33: Test Case 3..............................................................................................................28
Figure 34: Test Case 4..............................................................................................................28
Figure 35: Test Case 5..............................................................................................................28
Figure 36: Account Table.........................................................................................................31
Figure 37: Account table in SQL.............................................................................................31
Figure 38: Banker Table...........................................................................................................32
Figure 39: Banker table in SQL...............................................................................................32
Figure 40: Branch Table...........................................................................................................32
Figure 41: Banker table in SQL...............................................................................................32
Figure 42: Credit Card Table...................................................................................................33
Figure 1: Relational model Structure.........................................................................................7
Figure 2: Network Model structure............................................................................................8
Figure 3: Hierarchical Model Structure.....................................................................................9
Figure 4: Object-oriented model structure.................................................................................9
Figure 5: Account Table...........................................................................................................17
Figure 6: Banker Table.............................................................................................................17
Figure 7: Branch Table.............................................................................................................17
Figure 8: Credit Card Table.....................................................................................................18
Figure 9: Customer Table.........................................................................................................18
Figure 10: Loan Table..............................................................................................................18
Figure 11: Account query.........................................................................................................18
Figure 12: Banker Query..........................................................................................................19
Figure 13: Branch Query..........................................................................................................19
Figure 14: Credit Card Query..................................................................................................19
Figure 15: Customer Query......................................................................................................19
Figure 16: Loan Query.............................................................................................................19
Figure 17: Account Form.........................................................................................................20
Figure 18: Banker Form...........................................................................................................20
Figure 19: Branch Form...........................................................................................................21
Figure 20: Credit Card Form....................................................................................................21
Figure 21: Customer Form.......................................................................................................22
Figure 22: Loan Form..............................................................................................................22
Figure 23: Account Report.......................................................................................................23
Figure 24: Banker Report.........................................................................................................23
Figure 25: Branch Report.........................................................................................................24
Figure 26: Credit Card Report..................................................................................................24
Figure 27: Customer Report.....................................................................................................25
Figure 28: Loan Report............................................................................................................25
Figure 29: Logical ERD...........................................................................................................26
Figure 30: Conceptual ERD.....................................................................................................27
Figure 31: Test Case 1..............................................................................................................27
Figure 32: Test Case 2.............................................................................................................27
Figure 33: Test Case 3..............................................................................................................28
Figure 34: Test Case 4..............................................................................................................28
Figure 35: Test Case 5..............................................................................................................28
Figure 36: Account Table.........................................................................................................31
Figure 37: Account table in SQL.............................................................................................31
Figure 38: Banker Table...........................................................................................................32
Figure 39: Banker table in SQL...............................................................................................32
Figure 40: Branch Table...........................................................................................................32
Figure 41: Banker table in SQL...............................................................................................32
Figure 42: Credit Card Table...................................................................................................33
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
Figure 43: Credit card table in SQL.........................................................................................33
Figure 44: Customer Table.......................................................................................................33
Figure 45: Customer table in SQL...........................................................................................34
Figure 46: Loan Table..............................................................................................................34
Figure 47: Loan table in SQL...................................................................................................34
Figure 48: Account query.........................................................................................................34
Figure 49: Banker Query..........................................................................................................35
Figure 50: Branch Query..........................................................................................................35
Figure 51: Credit Card Query..................................................................................................35
Figure 52: Customer Query......................................................................................................35
Figure 53: Loan Query.............................................................................................................35
Figure 54: Account Form.........................................................................................................36
Figure 55: Banker Form...........................................................................................................36
Figure 56: Branch Form...........................................................................................................37
Figure 57: Credit Card Form....................................................................................................37
Figure 58: Customer Form.......................................................................................................38
Figure 59: Loan Form..............................................................................................................38
Figure 60: Account Report.......................................................................................................39
Figure 61: Banker Report.........................................................................................................39
Figure 62: Branch Report.........................................................................................................40
Figure 63: Credit Card Report..................................................................................................40
Figure 64: Customer Report.....................................................................................................41
Figure 65: Loan Report............................................................................................................41
Figure 66: Data Validation rule 1.............................................................................................42
Figure 67: Insert statement.......................................................................................................43
Figure 68: Update Statement....................................................................................................43
Figure 69: Delete Statement.....................................................................................................43
Figure 70: Merge Statement.....................................................................................................44
Figure 71: Test Case 1..............................................................................................................45
Figure 72: Test Case 2..............................................................................................................45
Figure 73: Test Case 3..............................................................................................................45
Figure 74: Test case 4..............................................................................................................45
Figure 75: Test Case 5..............................................................................................................45
Figure 76: Account table test case...........................................................................................46
Figure 77: Banker query test case............................................................................................46
Figure 78: Branch form test case.............................................................................................46
Figure 79: Credit card form test case.......................................................................................47
Figure 80: Customer report test case........................................................................................47
Figure 81: Loan query test case...............................................................................................47
Figure 44: Customer Table.......................................................................................................33
Figure 45: Customer table in SQL...........................................................................................34
Figure 46: Loan Table..............................................................................................................34
Figure 47: Loan table in SQL...................................................................................................34
Figure 48: Account query.........................................................................................................34
Figure 49: Banker Query..........................................................................................................35
Figure 50: Branch Query..........................................................................................................35
Figure 51: Credit Card Query..................................................................................................35
Figure 52: Customer Query......................................................................................................35
Figure 53: Loan Query.............................................................................................................35
Figure 54: Account Form.........................................................................................................36
Figure 55: Banker Form...........................................................................................................36
Figure 56: Branch Form...........................................................................................................37
Figure 57: Credit Card Form....................................................................................................37
Figure 58: Customer Form.......................................................................................................38
Figure 59: Loan Form..............................................................................................................38
Figure 60: Account Report.......................................................................................................39
Figure 61: Banker Report.........................................................................................................39
Figure 62: Branch Report.........................................................................................................40
Figure 63: Credit Card Report..................................................................................................40
Figure 64: Customer Report.....................................................................................................41
Figure 65: Loan Report............................................................................................................41
Figure 66: Data Validation rule 1.............................................................................................42
Figure 67: Insert statement.......................................................................................................43
Figure 68: Update Statement....................................................................................................43
Figure 69: Delete Statement.....................................................................................................43
Figure 70: Merge Statement.....................................................................................................44
Figure 71: Test Case 1..............................................................................................................45
Figure 72: Test Case 2..............................................................................................................45
Figure 73: Test Case 3..............................................................................................................45
Figure 74: Test case 4..............................................................................................................45
Figure 75: Test Case 5..............................................................................................................45
Figure 76: Account table test case...........................................................................................46
Figure 77: Banker query test case............................................................................................46
Figure 78: Branch form test case.............................................................................................46
Figure 79: Credit card form test case.......................................................................................47
Figure 80: Customer report test case........................................................................................47
Figure 81: Loan query test case...............................................................................................47
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Introduction:
The database management system is a software package that used to create, manage, define,
retrieve and manipulate data of an organization. Users can read, delete, update and create data
in a database management system. In the role of a database consultant in a leading company,
create a database for the selected organization “Bank of Scotland” with using table, forms,
reports and queries. Bank of Scotland is a clearing and commercial bank, based in Edinburg.
The current assignment of database management system defines different types of database
models in its first part with the evaluation of the database management system. In the second
section of the assignment, design a database management system with using a relational
model as per the client requirement. The third part of the assignment develops a platform of
the database on application software, MySQL with front end Microsoft Access. The last
section of the assignment includes a demonstration of tools and future improvements.
The database management system is a software package that used to create, manage, define,
retrieve and manipulate data of an organization. Users can read, delete, update and create data
in a database management system. In the role of a database consultant in a leading company,
create a database for the selected organization “Bank of Scotland” with using table, forms,
reports and queries. Bank of Scotland is a clearing and commercial bank, based in Edinburg.
The current assignment of database management system defines different types of database
models in its first part with the evaluation of the database management system. In the second
section of the assignment, design a database management system with using a relational
model as per the client requirement. The third part of the assignment develops a platform of
the database on application software, MySQL with front end Microsoft Access. The last
section of the assignment includes a demonstration of tools and future improvements.
LO1 Analyse different types of database management systems:
P1 Compare and contrast the different types of database models:
Database models are used to represent the logical structure of the database with including
relationship and constraints to access the data. Here are different types of database models as
below:
Relational model:
The relational model is the most widely database model that used to sort data into a table
format that is called as relations between row and columns. A column includes a list of
attributes like name, contact number, DOB and others. The relational model introduces in
1970 by EF Codd.
Figure 1: Relational model Structure
Source: (studytonight, 2018)
Microcomputers used the software of relational database and SQL is used by relational
database models. The main application of the relational database is computer integrated
manufacturing. Here are pros and cons of the RDB as follow:
o Pros:
o It improves the conceptual simplicity and provides flexibility in the database
o Easy to implement, design, manage and use
o Provide data security with proper management
o Cons:
P1 Compare and contrast the different types of database models:
Database models are used to represent the logical structure of the database with including
relationship and constraints to access the data. Here are different types of database models as
below:
Relational model:
The relational model is the most widely database model that used to sort data into a table
format that is called as relations between row and columns. A column includes a list of
attributes like name, contact number, DOB and others. The relational model introduces in
1970 by EF Codd.
Figure 1: Relational model Structure
Source: (studytonight, 2018)
Microcomputers used the software of relational database and SQL is used by relational
database models. The main application of the relational database is computer integrated
manufacturing. Here are pros and cons of the RDB as follow:
o Pros:
o It improves the conceptual simplicity and provides flexibility in the database
o Easy to implement, design, manage and use
o Provide data security with proper management
o Cons:
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
o Facilitate with poor implementation and design
o Substantial with hardware and system software (lucidchart, 2018)
Network model:
Use the network model to create data relationship and improve the performance of database
using a schema. It is the collection of one to many relationships. It is constructed with the set
of related records. In the features of the network model, it is better than the hierarchical
model and supports different kinds of relationships.
Figure 2: Network Model structure
Source: (studytonight, 2018)
Provide the structure or example of the network database model and provide pros and cons of
the model as below:
o Pros:
o Conceptual simplicity with flexibility
o Promote data integrity & independence
o Manage more relationships
o Cons:
o It is difficult to manage at the first time
o The system is complex and difficult in database alteration
o Lack of structural independence (Gillani, 2016)
Hierarchical model:
In the evaluation of the hierarchical model, it introduced in the 1970s and early of 1980s. It
used in a tree structure and use in the database of mainframe computers. The data starts from
o Substantial with hardware and system software (lucidchart, 2018)
Network model:
Use the network model to create data relationship and improve the performance of database
using a schema. It is the collection of one to many relationships. It is constructed with the set
of related records. In the features of the network model, it is better than the hierarchical
model and supports different kinds of relationships.
Figure 2: Network Model structure
Source: (studytonight, 2018)
Provide the structure or example of the network database model and provide pros and cons of
the model as below:
o Pros:
o Conceptual simplicity with flexibility
o Promote data integrity & independence
o Manage more relationships
o Cons:
o It is difficult to manage at the first time
o The system is complex and difficult in database alteration
o Lack of structural independence (Gillani, 2016)
Hierarchical model:
In the evaluation of the hierarchical model, it introduced in the 1970s and early of 1980s. It
used in a tree structure and use in the database of mainframe computers. The data starts from
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
root data and add child nodes in parent’s nodes. This database model uses in one to many
relationships.
o Pros:
o Provide database security with database integrity
o Efficient to use and data independence
o Conceptual simplicity
o Cons:
o Complex in implementation
o It is difficult to manage with implementation limitations
Figure 3: Hierarchical Model Structure
Source: (studytonight, 2018)
Object-oriented Model:
This database model was developed in 1981 by McLeod and Hammer and the basis of this
model is OODM (object-oriented data model). This model includes entity relationship factors
in it that used to describe factual content. The object of this model is real-world entity and
attributes define the properties of objects.
Figure 4: Object-oriented model structure
relationships.
o Pros:
o Provide database security with database integrity
o Efficient to use and data independence
o Conceptual simplicity
o Cons:
o Complex in implementation
o It is difficult to manage with implementation limitations
Figure 3: Hierarchical Model Structure
Source: (studytonight, 2018)
Object-oriented Model:
This database model was developed in 1981 by McLeod and Hammer and the basis of this
model is OODM (object-oriented data model). This model includes entity relationship factors
in it that used to describe factual content. The object of this model is real-world entity and
attributes define the properties of objects.
Figure 4: Object-oriented model structure
Source: (tutorial4beginners, 2017)
o Pros:
o Include the semantic content with visual representation
o Database integrity
o It has both data independence and structural
o Cons:
o Complex to access data
o Transaction speed it slow
o Pros:
o Include the semantic content with visual representation
o Database integrity
o It has both data independence and structural
o Cons:
o Complex to access data
o Transaction speed it slow
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
M1 Access how relational database models and the process of
normalization can provide reliable and efficient data structure:
A relational data model is a type of primary data model that mostly used for data storage and
process. On the other side, normalization is a process to organize data into the database. This
process uses the conceptual schema or the logical data structure. Relational data models and
normalization are the processes that provide a reliable and efficient data structure.
The relational data structure includes different factors like:
Relations: relations instance is a finite set of tuples that used in RDBMS system and a
relation schema include the name and its attributes.
Attributes: the properties of the entities are called as attributes that include name, age
and class as the attribute. Different types of attributes are used like:
o Simple attribute
o Derived attribute
o Composite attribute
o Single value and multi-value attribute
Domain: an original set of atomic values that used as the model data is called a
domain. Like: the domain of the shift is Mon, Tue, Wed…
Tuple: contain a single record in a single row of is known as a tuple.
Cardinality: this term refers to the total number of rows present in a table.
Constraints: constraints are the conditions that used to create a relation with different terms
and these conditions are known as relational integrity constraints that are three types as
below:
Key constraints: it must have minimum one number of the subset of the attribute in
relation and this attribute is known as a key and more than one subset is known as a
candidate key.
Domain constraints: an attribute contain a specific value to match with the real world
scenario, like, age is a positive integer and an attribute of an employee.
Referential integrity constraints: it is the concept of the foreign key that creates a
relationship with an attribute and can be referred to another relation (guru99, 2018).
normalization can provide reliable and efficient data structure:
A relational data model is a type of primary data model that mostly used for data storage and
process. On the other side, normalization is a process to organize data into the database. This
process uses the conceptual schema or the logical data structure. Relational data models and
normalization are the processes that provide a reliable and efficient data structure.
The relational data structure includes different factors like:
Relations: relations instance is a finite set of tuples that used in RDBMS system and a
relation schema include the name and its attributes.
Attributes: the properties of the entities are called as attributes that include name, age
and class as the attribute. Different types of attributes are used like:
o Simple attribute
o Derived attribute
o Composite attribute
o Single value and multi-value attribute
Domain: an original set of atomic values that used as the model data is called a
domain. Like: the domain of the shift is Mon, Tue, Wed…
Tuple: contain a single record in a single row of is known as a tuple.
Cardinality: this term refers to the total number of rows present in a table.
Constraints: constraints are the conditions that used to create a relation with different terms
and these conditions are known as relational integrity constraints that are three types as
below:
Key constraints: it must have minimum one number of the subset of the attribute in
relation and this attribute is known as a key and more than one subset is known as a
candidate key.
Domain constraints: an attribute contain a specific value to match with the real world
scenario, like, age is a positive integer and an attribute of an employee.
Referential integrity constraints: it is the concept of the foreign key that creates a
relationship with an attribute and can be referred to another relation (guru99, 2018).
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Normalization: it is process or technique to manage the data in the database and reduce
redundancy from the tables of data. It used relational schema and provide a mechanism to
remove redundancy from the database. Normalization has some types like:
1NF (First normal form): in this step divide user’s data into the table forms and assign
a primary key to a table.
2NF (Second normal form): this step uses the table of previous normal form and
partly depends on the primary key.
3NF (Third normal form): check the tables of previous normal form and remove the
dependency (Watt, 2018).
Conduct research on database system of the relational model and provide 12 rules. These
rules are known as Dr Codd rules that define the capabilities of the relational database and
used in the normalization process.
Sr. No. Rules Description
1. Information rule This rule provides the information of data that
store in a database as user or metadata and use
a table for the format.
2. Guaranteed access rule Each data element includes a table name,
attribute name and primary key.
3. Systematic treatment
of NULL values
NULL values of the database provide a
uniform and systematic treatment that
interpreted as the data is missing or not
applicable.
4. Active online catalog The database description store in the online
catalogue that is called as the data dictionary.
5. Comprehensive data
sub-language rule
Each database can access with using linear
support data definition that is like, data
manipulation and transaction management.
6. View updating rule Represent the theoretical view of the
database.
7. High-level insert,
update and delete rule
Database support high-level updation,
deletion and not limited in a single row.
8. Physical data The stored data is independent and
redundancy from the tables of data. It used relational schema and provide a mechanism to
remove redundancy from the database. Normalization has some types like:
1NF (First normal form): in this step divide user’s data into the table forms and assign
a primary key to a table.
2NF (Second normal form): this step uses the table of previous normal form and
partly depends on the primary key.
3NF (Third normal form): check the tables of previous normal form and remove the
dependency (Watt, 2018).
Conduct research on database system of the relational model and provide 12 rules. These
rules are known as Dr Codd rules that define the capabilities of the relational database and
used in the normalization process.
Sr. No. Rules Description
1. Information rule This rule provides the information of data that
store in a database as user or metadata and use
a table for the format.
2. Guaranteed access rule Each data element includes a table name,
attribute name and primary key.
3. Systematic treatment
of NULL values
NULL values of the database provide a
uniform and systematic treatment that
interpreted as the data is missing or not
applicable.
4. Active online catalog The database description store in the online
catalogue that is called as the data dictionary.
5. Comprehensive data
sub-language rule
Each database can access with using linear
support data definition that is like, data
manipulation and transaction management.
6. View updating rule Represent the theoretical view of the
database.
7. High-level insert,
update and delete rule
Database support high-level updation,
deletion and not limited in a single row.
8. Physical data The stored data is independent and
independence application can be access with the database.
9. Logical data
independence
The user’s application data is independent is
as per the database view.
10. Integrity independence The database has its own integrity that used in
different programs.
11. Distribution
independence
The database works as per the requirement
across a network and distributed graphically
12. Non-subversion rule Change the data by using bypass integrity
when a system is allowed by low level
(studytonight, 2018)
9. Logical data
independence
The user’s application data is independent is
as per the database view.
10. Integrity independence The database has its own integrity that used in
different programs.
11. Distribution
independence
The database works as per the requirement
across a network and distributed graphically
12. Non-subversion rule Change the data by using bypass integrity
when a system is allowed by low level
(studytonight, 2018)
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
1 out of 56
Related Documents
Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
![[object Object]](/_next/static/media/star-bottom.7253800d.svg)
Unlock your academic potential
Copyright © 2020–2025 A2Z Services. All Rights Reserved. Developed and managed by ZUCOL.