Report on Data Analysis and Design of Relational Database Systems

This report provides a comprehensive analysis and design of relational database systems, focusing on the Dominican College database system. It covers various data models, database technologies, and database design approaches, including normalization, verification, and user documentation. The report details the design and implementation of a relational database system using MS Access, including table design, relationships, and query language implementation. It also includes an evaluation of data extraction through query tools, along with database testing, documentation, user feedback, and control mechanisms. The report thoroughly addresses verification, validation, and control measures to ensure data integrity and system reliability.

Data Analysis and Design

Table of Contents
Introduction......................................................................................................................................4
LO1 Understand data models and database technologies................................................................5
1.1 Critically compare different data models and schemas.........................................................5
1.2 Critically discuss the benefits and limitations of different database technologies................9
1.3 Analyse different approaches to database design................................................................11
LO2 Be able to design and implement relational database systems..............................................15
2.1 Design a relational database system to meet a given requirement......................................15
2NF (second normal form):.......................................................................................................17
2.2 Build a relational database system based on a prepared design..........................................19
2.3 Apply a range of database tools and techniques to enhance the user interface...................21
LO3 Be able to use manipulation and querying tools....................................................................22
3.1 Explain the benefits of using manipulation and query tools in a relational database system
...................................................................................................................................................22
3.2 Implement a query language into the relational database system........................................23
3.3 Critically evaluate how meaningful data has been extracted through the use of query tools
...................................................................................................................................................39
LO4 Be able to test and document relational database systems....................................................43
4.1 Critically review and test a relational database system.......................................................43
4.2 Create documentation to support the implementation and testing of a relational database
system........................................................................................................................................44
4.3 Create user documentation for a developed relational database system..............................47
4.4 Explain how verification and validation has been addressed..............................................52
4.5 Explain how control mechanisms have been used..............................................................53
Conclusion.................................................................................................................................55
References:................................................................................................................................56
Appendix........................................................................................................................................57
1

List of Figures
Figure 1 Network data model..........................................................................................................5
Figure 2 Hierarchical database model.............................................................................................6
Figure 3 Object-oriented database model........................................................................................6
Figure 4 Relational database model.................................................................................................7
Figure 5 Database schema with 3 layers..........................................................................................8
Figure 6 Relationship diagram.......................................................................................................15
Figure 7 ER diagram......................................................................................................................16
Figure 8 Design view of academic manager table.........................................................................19
Figure 9 Design view of admin table.............................................................................................19
Figure 10 Design view of course table..........................................................................................19
Figure 11 Design view of lecturer table........................................................................................19
Figure 12 Design view of student table.........................................................................................20
Figure 13 Design view of units table.............................................................................................20
Figure 14 Query 1 design view......................................................................................................23
Figure 15 Query 1 output view......................................................................................................23
Figure 16 Query 1 report view.......................................................................................................24
Figure 17 Query 2 design view......................................................................................................25
Figure 18 Query 2 output view......................................................................................................25
Figure 19 Query 2 report view.......................................................................................................26
Figure 20 Query 3 design view......................................................................................................27
Figure 21 Query 3 output view......................................................................................................27
Figure 22 Query 3 report view.......................................................................................................28
Figure 23 Query 4 design view......................................................................................................29
Figure 24 Query 4 output view......................................................................................................29
Figure 25 Query 4 report view.......................................................................................................30
Figure 26 Query 5 design view......................................................................................................31
Figure 27 Query 5 output view......................................................................................................31
Figure 28 Query 5 report view.......................................................................................................32
Figure 29 Query 6 design view......................................................................................................33
Figure 30 Query 6 output view......................................................................................................33
Figure 31 Query 6 report view.......................................................................................................34
Figure 32 Query 7 design view......................................................................................................35
Figure 33 Query 7 output view......................................................................................................35
Figure 34 Query 7 report view.......................................................................................................36
Figure 35 Query 8 design view......................................................................................................37
Figure 36 Query 8 output view......................................................................................................37
Figure 37 Query 8 report view.......................................................................................................38
Figure 38 SQL view of admin query.............................................................................................39
Figure 39 Output view of admin query..........................................................................................39
2

Figure 40 SQL view of academic manager query.........................................................................39
Figure 41 Output view of academic manager query......................................................................40
Figure 42 SQL view of college admin query.................................................................................40
Figure 43 Output view of college admin query.............................................................................41
Figure 44 SQL view of student query............................................................................................41
Figure 45 Output view of student query........................................................................................42
Figure 46 Test case 1.....................................................................................................................44
Figure 47 Test case 2.....................................................................................................................45
Figure 48 Test case 3.....................................................................................................................45
Figure 49 Test case 4.....................................................................................................................45
Figure 50 Test case 5.....................................................................................................................46
Figure 51 Academic manager form...............................................................................................47
Figure 52 Admin form...................................................................................................................47
Figure 53 Course form...................................................................................................................47
Figure 54 Lecturer form.................................................................................................................48
Figure 55 Student form..................................................................................................................48
Figure 56 Unit form.......................................................................................................................49
Figure 57 Academic manager form report.....................................................................................49
Figure 58 Admin form report........................................................................................................50
Figure 59 Course form report........................................................................................................50
Figure 60 Student form report.......................................................................................................51
Figure 61 Validation On lecturer phone no...................................................................................52
Figure 62 Control mechanism for admin table..............................................................................53
Figure 63 Control mechanism for admin table in course id..........................................................54
List of Tables
Table 1 Un normalized table..........................................................................................................12
Table 2 First NF form....................................................................................................................12
Table 3 Second (1) NF...................................................................................................................12
Table 4 Second(2) NF....................................................................................................................12
Table 5 Third (1) NF......................................................................................................................13
Table 6 Third (2) NF......................................................................................................................13
Table 7 Third (3) NF......................................................................................................................13
Table 8 First normal form table of database..................................................................................17
Table 9 Second normal form of database......................................................................................17
Table 10 Third normal form of database.......................................................................................18
Table 11 Feedback table by different users...................................................................................43
Table 12 Test cases table...............................................................................................................44
3

Introduction
Database design is the design process of the database and recognize the general basics of the
database system. For designing the database MS Access framework is used. The detailed
description of this report will generate for Dominican college database system. In this
assignment, the assignment is divided into four tasks. Different database technologies and
diverse methods for the creation of a database model are deliberated. In this, it will also contain
the information of normalization, verifications, and requirements of database and the
documentation for user which helps the operator to access and practise the database. It also
contains the detail of query language. In last part of the report the diverse test cases, test plans
and the user feedback are described.
4

LO1 Understand data models and database technologies
1.1 Critically compare different data models and schemas
Database management system: It is the software for a computer that can communicate with an
application, database, End-user for the analysis and for collecting data. Database management
system will allow database administrator to update, define, creation of database. Database will
contain information which can be managed by a database administrator. Different types of
database are network database, object-oriented, relational, hierarchical database.
Network database: This model contains a logical type of database. In this model, the connection
is contained among the nodes, their direction and the node cost. This type of database is
completed extremely because many-to-many relationship contains in between the nodes for the
one record that can be used by entire database.
Figure 1 Network data model
Source: (tutorial4begineer, 2018)
Advantages:
 It can be handled by several kinds of relationship.
 Data integrity and access flexibility are there in this.
Disadvantages:
 In this the complex system is present
 Complex to maintain the links.
Hierarchical database model: This model forms a tree-like structure which can be contained by
one to numerous type relationships. One parent is behaving as a root which is the top node. It is a
physical type of database which can be used for storing the information.
5

Advantages:
 Used for sharing the data.
 It offers a secure database.
Disadvantages:
 Some limitation is faced by the time of implementation of database.
 In-depth the hierarchical knowledge is required.
Figure 2 Hierarchical database model
Source: (tutorial4begineer, 2018)
Object-oriented database model: This kind of database can store the data in the form of
objects. The object-oriented languages like C++, JAVA, and others.
Figure 3 Object-oriented database model
Source: (tutorial4begineers, 2018)
6

Advantages:
 According to the need of user, the data type can be defined.
 It can save the method of data automated.
Disadvantages:
 Without the knowledge of OOPS, the user cannot use.
 It uses the poor reporting and querying tool.
Relational database: It can be widely used for data storage and processing. It stores the data in
table format where row defines the record and in a column, the attributes are defined.
Advantages:
 For database design, it can use a simple concept.
 For integration of data it can provide a communication in a effective manner.
Disadvantages:
Figure 4 Relational database model
Source: (Parker, 2009)
Database schema: It uses the three-layer structural design where individual level is vital on
behalf of the company. In table format, the data are stored and show the relations with the
different table present in database.
7

Figure 5 Database schema with 3 layers
Source: (LearnSoftwareDevelopment, 2009)
External level: In this, the different views for user are defined. It can use the physical data type
for the recognition of function in database. The feature is:
 SQL & MySQL are supported in the client query.
 The normalization process can be performed by a client or also by the user.
Internal level: It can be used for defining the database that can be used in an intelligent manner
so the developer and engineers which users can understand easily.
 All characteristic is explained.
 Elements & relationship are contained.
Conceptual level: In the user will use the concept of data model for the implementation. It has
some key feature that is:
 In this, the database connections and also the elements are not specified.
 The qualities are not defined in this.
8

1.2 Critically discuss the benefits and limitations of different database technologies
Database: It can be defined as a set of information, data, & records. It can be used for storing the
client-server and server information like documents, files, and also the records in a database. The
different type of database is explained here with their limitations & benefits are:
Multimedia Database: This can be highly used for storing multimedia information in a database
like images, videos.
Benefits:
 It will be user-friendly.
 This type of database can offer a large no. of styles for learning like auditory learning,
visual learning.
Imitations:
 Several types of compatibility issues are faced in this.
 For downloading the data, it can be depending on the web.
Cloud computing: It is the online type of database in which the data are stored on the internet.
Benefits
 The cost is low for the cloud software.
 Maintenance issue is not present.
Imitations:
 The speed is low with the comparison with the other type of database.
 The feature is limited in this type.
Relational database: It stores the data into the table format.
Benefits: It is flexible in use.
 Its implementation is also flexible and also very easy to understand.
Imitations: The design is poor in visual and also implementation poor.
 Hardware & software overhead is present in the relational database.
File-based database: In this, the files on the devices are accessed with the help of a database.
Benefits:
9

 It can use a simple concept.
 It can allow a sharing remotely.
Imitations:
 It is the database which is dependent on application database.
 The user view demonstration s difficult.
Data warehousing: This database can use a data reporting and also the data analyzing technique.
This stores the data in an annual or quarterly manner that can be used for the organizing and
report the data management.
Benefits:
 The data can be stored in a cleaner manner.
 The security can be provided for the data with the access.
Imitations:
 The database is expensive for using.
 This is a time consuming for implementing the data.
10

1.3 Analyse different approaches to database design
Database design approaches are described:
Top-Down approach:
This type is used globally for the designing the database. It can be developed by using the
scratch. The ER is the example of this type of approach because the complex outline of the
database is created.
Modelling the ER process:
It is the collection of entities. The relationships are also present in many entities in database
which allow developer to design the sketch in a very easy manner. It can be of 2 types the
concepts and the logical ER in concept the relationships with the entities are define and in the
logical the logical design of database is present.
Entity: It represents in a rectangular format where the database is in the table form.
Attribute: This can be represented in the oval that can be the property of the entity in the design.
It can be simple, derived, single value, multi-value, and composite attribute type.
Relationship: This represents the diamond symbol. This shows the relations between the entities
in a design.
Bottom-Up approach:
This is a common method of selection for the global schema description that can be used. This
converts the database in the form of local schema. This defines the connection and the element in
the database. The example of the bottom-up is normalization because it describes the relationship
of the database. (Burleson, 2018)
Normalization Process:
For removing the redundant data from the database the normalization process can be used. This
can be used to reduce the complexity so that throughput of accessing the data is improved. The
normalization process provides integrity and consistent data in the table of database.
Un-Normalization Process:
It has the set of rules which are:
 Table column will not contain the element in a left to right manner.
 Not containing the element in table in a top to down manner.
 Duplication data are not present in the rows.
11