Database Analysis and Design: A Comprehensive Guide

Database analysis and Design

Table of Contents
LO1: Understand data models and database technologies......................................................5
1.1 There are different types of data models and schema in data analysis and design;
identify the various data models and schema and critically analyse them? P1.1.................5
1.2 Critically discuss the different types of database management systems and include the
benefits and limitations. Identify the limitations of traditional file-based systems and
illustrate clearly how a relational database system solves the problem of traditional file-
based system with valid example and suitable diagram?.....................................................6
1.3 Various database design approaches exist in the development process. Analyse these
approaches, and describe them appropriately with suitable examples...............................9
LO2: Be able to design and implement relational database systems.....................................10
2.1 Identify the entities, primary key, foreign key, relationship, attributes, and design a
relational database system for given requirements and present an ERD for the system.. .10
2.2 For the logical design produced in 2.1 above, build a relational database system in MS
SQL server. This will constitute the physical implementation [P2.2, D2]........................13
2.3 There are various methods, as well as factors, should consider the user interface,
discuss and apply a range of database tools and techniques to enhance the user interface
............................................................................................................................................ 16
LO3: Be able to use manipulation and querying tools............................................................17
3.1 Discuss the SQL and its features and functionalities; explain the benefits of query tools
in a relational database system..........................................................................................17
3.2 Apply the SQL (DML) to implement a query language into your design relational
database system [P3.2, D1]............................................................................................18
3.3 Identify the features of query tool. Critically evaluate how meaningful data has been
extracted through the use of query tools...........................................................................24
LO4 Be able to test and document relational database systems............................................25
1

4.1 There are various testing methods for the database system. Investigate different types
of testing methods. Provide suitable examples [P4.2, M3].............................................25
4.2 Create a test plan in a table format for testing the “BIKE SOLUTIONS” database you
have developed. Your test plan should show a minimum of 10 records testing. Use the
following headings in the table:..........................................................................................26
a) Test description.............................................................................................................. 26
b) Expected Outcome......................................................................................................... 26
c) Actual Outcome (i.e. screenshots of results and annotations)......................................26
Produce a report for all the tests and include the following:.............................................26
a) Black box and White box testing.....................................................................................26
b) Analyses the actual test results against the expected results to identify.......................26
Discrepancies......................................................................................................................26
4.3 Describe the importance of technical and user documentation and create a suitable
user document for your developed relational database system [P4.3]...........................31
4.4 Verification and Validation are very important terms; explain clearly, how you
addressed the verification and validation of your developed relational database system
[P4.4]................................................................................................................................ 35
4.5 There are various control mechanisms available in the database system. Investigate
the important control mechanism and explain how control mechanisms can be used in
your developed relational database system [P4.5]..........................................................37
References..............................................................................................................................39
2

List of Figures
Figure 1 Hierarchical database.................................................................................................6
Figure 2 Network Database......................................................................................................7
Figure 3 Relational Database....................................................................................................8
Figure 4: ERD.......................................................................................................................... 12
Figure 5 bike_design implementation....................................................................................13
Figure 6 Implementation of bike_stock..................................................................................13
Figure 7 Implementation of branch........................................................................................14
Figure 8 Implementation of customer....................................................................................14
Figure 9 implementation of design........................................................................................ 14
Figure 10 Implementation of employee.................................................................................15
Figure 11 implementation of employee_schedule.................................................................15
Figure 12 Query 1...................................................................................................................18
Figure 13 Query 2...................................................................................................................19
Figure 14 Query 3...................................................................................................................19
Figure 15 Query 4...................................................................................................................20
Figure 16 Query 5...................................................................................................................20
Figure 17 Query 6...................................................................................................................21
Figure 18 Query 7...................................................................................................................21
Figure 19 Query 8...................................................................................................................22
Figure 20: Architecture diagram of RDBMS............................................................................22
Figure 21 Data retrieval process.............................................................................................24
Figure 22 Employee testing....................................................................................................27
Figure 23 Availability testing.................................................................................................. 28
Figure 24 Bike store trigger testing........................................................................................28
Figure 25 branch row testing..................................................................................................29
Figure 26 Null value testing....................................................................................................29
Figure 27 Primary key testing.................................................................................................30
Figure 28 Bike type testing.....................................................................................................30
Figure 29 Choose from the downloads..................................................................................31
Figure 30 Installation..............................................................................................................32
3

Figure 31 Accept License........................................................................................................32
Figure 32 Update....................................................................................................................33
Figure 33 Install...................................................................................................................... 33
Figure 34 Location.................................................................................................................. 34
Figure 35 Payment................................................................................................................. 34
Figure 36 Bike info..................................................................................................................35
Figure 37 Admin..................................................................................................................... 35
Figure 38 Customer................................................................................................................ 36
Figure 39 Address...................................................................................................................37
Figure 40Customer.................................................................................................................38
4

LO1: Understand data models and database technologies
1.1 There are different types of data models and schema in data analysis
and design; identify the various data models and schema and critically
analyse them? P1.1
Database model is a kind of logical database structure which determines that how to store
data & how it can be accessed (En.cnki.com.cn, 2018). There are so many database models
& all are represented by accompanying database diagrams. We have so many database
models, some of them are as follows:
1. Relational model: This model is the most common used database model. As its name
implies it makes a relationship between rows & columns. This database model consist of all
kind of relationships of table including one-to-one, one-to-many, many-to-one. Relational
database mainly written in structured query language (SQL)
2. Hierarchical Model: The hierarchical model organizes all types of data in a tree-like
pattern. Mainly this model is the easiest model to understand. Only this database model is
used in real life to understand real-life examples.
3. Network Model: Basically the network model works on the basis of hierarchical structure
by allowing many-to-many relationships between all the associated records. Sometimes in
complex relationships there is a record which is presented in so many sets or models. In 70
century this was the most famous database model.
5

1.2 Critically discuss the different types of database management systems
and include the benefits and limitations. Identify the limitations of
traditional file-based systems and illustrate clearly how a relational
database system solves the problem of traditional file-based system with
valid example and suitable diagram? P1.2, M1
Database Management Systems: As its name implies database is collection of records. Or
this term is used to organize all the data. And database management systems are a system
which is used to managing all the data in a mannered way (Is.muni.cz, 2018).
Mainly there are four structural types of database management systems and they are as
follows:
1. Network schema
2. Object-oriented schema
3. Hierarchical Schema
4. Relational Schema
1. Hierarchical schema (DBMS): This database model is very fast & simple to use. For this
database model, we need to learn everything about the database. In these database model
systems, all the records are having information about three groups parent/child.
Hierarchical database management systems organize all the data in a tree-like structure.
Figure 1 Hierarchical database
6

Advantage: Hierarchical database management system is very simple & easy to use.
Moreover, it can be accessed easily because it collects all the data in a proper tree-like
structure. In this used feature is two-edged.
Disadvantage: This type of schema management system is having one child-node in the tree
but only having single parent-node relationships between children is not allowed.
2. Network database: Network database system is a comparatively efficient method to
managing all the data. This database management system is used on the large scale on
digital computers. The network database management system is approximately similar to
the hierarchical database management system by having the same tree-like structure.
Figure 2 Network Database
In network schema, those who inherit (child) are called members & super are called
occupier (Patents.google.com, 2018).
3. Relational schema: In the relational schema, the relationships between all the data files
are relational. Both of the databases hierarchical & the network schema require the user to
pass a hierarchical in order to access the data contained in the schema. In the relational
schema, data is stored in different control access tables.
7