Data Analysis and Design: A Comprehensive Guide to Relational Database Systems

This report delves into the fundamental concepts of data analysis and design, focusing on relational database systems (RDBMS). It explores various data models, database technologies, and design approaches, providing a critical comparison of their benefits and limitations. The report then guides you through the process of designing and implementing a relational database system, covering ERD modeling, normalization, and the use of database tools and techniques. It also examines data manipulation and querying tools, demonstrating their implementation and benefits in extracting meaningful data. Finally, the report discusses testing and documentation procedures, including verification and validation techniques, control mechanisms, and user feedback. This comprehensive guide serves as a valuable resource for students and professionals seeking to understand and apply the principles of data analysis and design in the context of relational database systems.

Data Analysis and design

Table of Contents
Introduction......................................................................................................................................2
LO1 Understand data models and database technologies................................................................3
1.1 Critically compare different data models and schemas....................................................3
1.2 Critically discuss the benefits and limitations of different database technologies................6
1.3 Analyse different approaches to database design..................................................................8
LO2 Be able to design and implement relational database systems................................................9
2.1 Design a relational database system to meet a given requirement........................................9
2.2 Build a relational database system based on a prepared design..........................................14
2.3 Apply a range of database tools and techniques to enhance the user interface...................18
LO3 Be able to use manipulation and querying tools....................................................................26
3.1 Explain the benefits of using manipulation and query tools in a relational database system
...................................................................................................................................................26
3.2 Implement a query language into the relational database system that has been
implemented..............................................................................................................................29
3.3. Critically evaluates how meaningful data has been extracted through the use of query
tools for the given case study.....................................................................................................39
LO4 Be able to test and document relational database systems....................................................45
4.1 Critically reviews and tests the implemented relational database system...........................45
4.2 Create documentation to support the implementation and testing of a relational database
system........................................................................................................................................48
4.3 Create user documentation for a developed relational database system..............................52
4.4 Explain how verification and validation has been addressed..............................................56
4.5 Explain how control mechanisms have been used..............................................................59
Conclusion.....................................................................................................................................64
1

Introduction
This report, it has been discussed about the issues which are important and the concepts of the
RDBMS. In the beginning it has been discussed about the schemas and models taken in use.
Technologies of database and various approach has been discussed. In the next part there has
been a discussion on ERD model and database normalization. Implementation of database has
been done. Tools and techniques of database have been discussed.
In the next part data manipulation has been discussed and languages and tools of query. In the
last part, the conversation is on several feedbacks which are going to be observed by many users,
and various test case plan, the user documentation, verification and validation, and control
mechanism will be explained properly. The main aim of this assignment is to discuss the
concepts of designing and analysis of the data.
2

LO1 Understand data models and database technologies
1.1 Critically compare different data models and schemas
Data model:
The data model is considered as an abstract model which explains the data which has been
accessed and represented. The data model assimilates elements of data group together in the
form of records.
There are several data models mentioned below:
1. Hierarchical Model:
The hierarchical model is the one where data is arranged in the form of a tree. In this
structure, the information is replaced by parent-child relation every individual parent can
have more than one child but every individual child will have only one parent which is
also known as one to many relationships.
Benefits :
1. The relationship present between the different layers is simple logically.
2. The hierarchical used is secured fully .
3. The relationship in the model looks like the relationship of parent-child because of
which there is a powerful connection between the child selector and parent sector
beneath it.
4. This model is efficient as the database has one too many relationships.
2. Network model:
It makes use of fundamental concepts named sets and records which help in organizing
the data. Records have fields in it and sets describes the one-to-many relationships among
records. In this model, there is more than one parent model which has been organized in
the form of a graph.
Benefits:
1. It is easy and simple in terms of implementation.
2. It can manage many to many and one to one relationship.
3. It is easy to access in comparison with the hierarchical model.
3

4. As it is based on the relationship among the parent and child there is a connection
between child sector and parent sector beneath it.
5. When data independence comes into consideration then it is better than hierarchical
model.
3. Relational model:
A relational model gives permission to the operations of storage and retrieval, integrity
constraints and description of data structures. In this model, the relation among the data is
organized in the form of tables.
Benefits:
1. It is considered simple in comparison with other.
2. If there is a change in one data sector so there is no need to change in the other.
3. Implementation of data is easier.
4. It is a collection of more than one column when combined has a value which is unique.
4. Object-oriented model:
In this type of data model, the data and relationship both are combined into one structure
which is considered an object. In the object-oriented model, the attributes which the
object have describes the traits of the object.
Benefits:
1. It gives support to features including abstraction, inheritance, encapsulation, and
polymorphism.
2. It is considered in terms of searching any records of data and identifying.
3. It maintains the code bases easily.
Database Schema:
4

The schema here says that the design for implementing the database. It describes the
relationship between the entities and the attributes. It stocks the definition of data of all the
formulas which has been taken in use for implementing the database.
It has been categorizing into different levels:
Physical:
It has been explained how the database is going to be stored in indices form, files and in various
ways of the RAM. It also explains the different types of various files and layout of the records.
Conceptual:
This level is also considered as logical level. Here, how the representation of the rallies going to
be done. It displays the concept diagram.
External:
The relationship which the various entities have among them is shown . It also explains the view
of data according to logical or conceptual level.
5

1.2 Critically discuss the benefits and limitations of different database technologies
The different types of database technologies are:
File-based:
It is a type of database technology which stores the data in an unorganized and old way. This
technique was very popular when there were no computers available. The main benefit of this
database is that it minimizes the redundancy occurred in the data and is also secure. The
limitation of this database is that it is not easy to develop and design which results in consuming
more time to design.
Relational:
A relational database is that type of database which has a relationship with the data. The
advantage of this database is that it is an already established and flexible database and it is easy
to understand. The limitation of a relational database is that to represent it in the real world is a
complicated procedure.
Object-oriented:
The data stored in this database is in the organized format and those objects are created which
has some attributes and something defining the traits of object. The benefit of this database is
that it is scalable , simple and strong. The limitation of this database is that if there is a huge
amount of data which gets deleted it causes problem as it reduced the flexible property.
Distributed:
In the distributed database, it stores the database in several locations as there is no fixed physical
location. It is very much reliable. The benefit of the distributed database is faster and can be
accessed from anywhere and at any time. The limitation is that it is exposed to data integrity
because of which it is not that secure.
Data warehousing:
6

Data warehousing manages the database and cleans it and when there is a requirement it also
fetches the data. To obtain useful data it is the technology which is most required. The benefit of
this technology the datasets which have a very high capacity can be easily handled. The
limitation is that it is very costly and not as much compatible and flexible.
Web-based technology:
It is related to cloud computing where the database has been given by several companies . It is
fast and simple. The benefit in this is that data can be fetched at any time and from anywhere
only need to have an internet connection. The limitation is that it only have limited functions and
it requires net every time when there is an editing needs to be done in database.
7

1.3 Analyse different approaches to database design
They are different approaches to develop a database as mentioned below:
Top-down approach:
In top-down approach first, it moves in a general format and then it is going to move forward in a
specific way. In the beginning, only needs to be described as what the system needs and then
going to move forward to the bottom towards more specific details about the interaction of the
system. This process highlights the recognition of different types of entity and attribute of each
entity.
Bottom-up approach:
The bottom-up approach deals first with the specific details and then move forward with the
general. which is performed by identification of elements of data and then bind them in a data
sets. It first identifies attributes and then builds entities.
Difference between top-down approach and bottom-up approach:
Top-down approach Bottom-up approach
It consumes a lot of time and is more in cost in
comparison to bottom-up
It does not cost that much and design the
database in a short span of time.
In the beginning it requires a lot of planning . It does not require planning in the beginning.
It is considered complicated for designing the
database.
It is simpler in this approach to design the
database.
8

LO2 Be able to design and implement relational database systems
2.1 Design a relational database system to meet a given requirement
ERD Model:
ERD model is of different type of elements which includes:
Attributes:
Which explains the entity is considered as attributes.
Entities:
It is considered as the elements which are related to attributes and the relationship among them.
Relationships:
It defines the connection among the entities.
Fig. ERD Diagram
9

Chenn notations have been taken in use in the diagram to describe the relationship among the
entities.
Normalization:
It is a technique where the tables are organized and the redundancy and data dependency is
detached.
Types of Normalization:
Un-normalized form:
It is a form of the database which is not in any form
1NF (First Normal Form):
In this form, the atomicity is being detached from the table.
Person Study details
staff_id Projector
staff_name classroom_attendance
staff_email classroom_date
student_Name classroom_timing
student_contactNumber course_Name
student_DOB course_fee
student_type course _type
course_paymentMode
course_extrafees
coursespopular_Type
course_avgattendance
department_Name
department_HoD
department_contactnumber
10

Mark_type
mark_year
Mark_semesters
2NF (Second Normal Form):
The functional dependency is being detached in the second normal form.
Staff Classroom
staff_id classroom_id
staff_name projector
staff_email classroom_attendance
department_id classroom_date
classroom_timing
Student Mark_type
student_id mark_year
student_Name Mark_semesters
student_contactNumber
student_DOB Department details
student_type department_id
department_id department_Name
payment_mode department_HoD
department_contactnumber
course_Name
course_fee
course _type
11

course_paymentMode
course_extrafees
coursespopular_Type
course_avgattendance
3NF (Third Normal Form):
The transitivity which the attribute is having is being removed.
Classroom course Student
classroom_id course_Id student_id
Projecter course_Name student_Name
classroom_attendance course_fee student_contactNumber
classroom_date course _type student_DOB
classroom_timing course_paymentMode student_type
course_Id course_extrafees department_id
staff_id department_id course_Id
coursespopular_Type payment_mode
course_avgattendance
Department Mark Analysis Staff
department_id mark_id staff_id
department_Name Mark_type staff_name
department_HoD mark_year staff_email
department_contactnumber Mark_semesters department_id
student_id course_Id
The database has been constructed using Top-Down approach. Which have taken in use ER
diagrams for database designing.
12

2.2 Build a relational database system based on a prepared design
Tables Design View
Classroom table:
It has the details of the classroom and has classroom_id as the primary key.
Fig. Design View for classroom table
Fig. Datasheet view for classroom table
Course table:
It has the details of the course and has course_id as the primary key.
Fig. Design View for course table
13

Fig. Datasheet view for course table
Department table:
It has the details of the department and has department_id as the primary key.
Fig. Design View for department table
Fig. Datasheet view for department table
Mark Analysis table:
It has the details of mark analysis and has mark_id as the primary key.
14

Fig. Design View for mark analysis table
Fig. Datasheet View for mark analysis table
Staff table:
It has the details of staff and has staff_id as the primary key.
Fig. Design View for staff table
Fig. Datasheet View for staff table
15

Student table:
It has the details of student and has student_id as the primary key.
Fig. Design View for staff table
Fig. Datasheet View for student table
16

2.3 Apply a range of database tools and techniques to enhance the user interface
There are different components which Microsoft access has taken in use. Whose main is to make
interface better in terms of appearance from the client side. To add the record and for storing the
data forms and table are designed. Few tools are mentioned below:
Form:
It’s main purpose is to insert all that data which is important through the user.
Fig. Form view for classroom
Fig. Form view for course
17

Report:
It serves the purpose of finding out the particular data to make the process of search easy.
Fig. Report view for classroom and staff
Fig. Report view for classroom and department
Relationship:
It describes the relation among various entities.
18

Fig. Relationship Diagram
Fig. ERD diagram
Tables:
It shows the attribute of the entities.
19

Figure Design View
Figure Datasheet View
The database has been designed on the MS Access. Front-end technology has been used for
database. Database developer has been shown in back-end . In front-end form and query has
been taken in use. Table and report of query is used as backend.
20

Fig. Dashboard design
Fig. Browse the database
21

Fig. Form for the database
22

Fig. To quit the page
Fig. Table for database
23

24

LO3 Be able to use manipulation and querying tools
3.1 Explain the benefits of using manipulation and query tools in a relational
database system
Query Language
The query is something where to achieve an output there is an input. The database is not that
easy. To get the record of information from a database using a query by performing diverse
check so that the result can be displayed.
Benefits of Query Language:
1. To manage and control the data it is not that hard of a task as it does not require coding
for College.
2. The user can input huge amount data making the process of searching easy and fast to get
the desired output.
Benefits of query tool:
1. After implementing packages, security, triggers, tablespaces and packages inclusive data
and information can be achieved.
2. By creating tables, queries, forms and report through the developer database can be
designed.
Data Manipulation:
The data can be modified where several operations like update, delete, insert and alter. The data
is represented in an advanced way.
Benefits:
1. It is required to establish the connection between the database and the user for interaction
purpose.
2. It is very easy to modify the information and data stored.
3. Through manipulation, specific data can be fetched by the user.
Aggregate function:
To estimate various columns for a particular condition. There are many functions like:
 Minimum(): To Find record which is minimum.
 Maximum(): To Find record which is maximum.
 Count(): To Count the row in table.
 Sum(): Find the sum of data.
25

 Average(): Find the average of data.
Relational database queries:
Query 1:
It shows the connection between the classroom and staff.
Fig. Query for the relation between classroom and staff
Query 2:
This query shows the connection between the classroom and course.
Fig. Query for the relation between classroom and course
Query 3:
This query shows the connection between course and department
Fig. Query for the relation between course and department.
Query 4:
This query shows the details of the department.
Fig. Query for details of the department.
Query 5:
This query shows the details of the fee.
Fig. Query for details of Fee.
26

Query 6:
This query shows for overseeing student details
Fig. Query for details of student
Query 7:
This query shows the connection between student and mark.
Fig. Query for the relationship between student and mark.
Query 8:
This query shows the connection between student, department, and mark.
Fig. Query shows the relation between student, department, and marks.
27

3.2 Implement a query language into the relational database system that has been
implemented.
Query 1
SQL View:
Fig. Design for Query 1
Fig. Output for query 1
28

Fig. Query 1 Report View
Query 2:
SQL view:
Fig. Design view for Query 2
29

Fig. Output View for query 2
Fig. Report view for Query 2
Query 3:
SQL view:
30

Fig. Design view for query 3
Fig. Output for query 3
Fig. Report View for query 3
31

Query 4:
SQL view:
Fig. Design view for Query 4
Fig. Output for Query 4
Fig. Report View For Query 4
Query 5:
32

SQL view:
Fig. Design view for Query 5
Fig. Output for Query 5
33

Fig. Report view for Query 5
Query 6:
SQL view:
Fig. Design view for Query 6
34

Fig. Output for Query 6
Fig. Report View for Query 6
Query 7:
SQL view:
Fig. Design view for Query 7
35

Fig. Output for Query 7
Fig. Report View for Query 7
Query 8:
SQL view:
36

Fig. Design view for Query 8
Fig. Output For Query 8
37

Fig. Report view for Query 8
3.3. Critically evaluates how meaningful data has been extracted through the use of
query tools for the given case study.
There is a requirement for the languages and tool to find the particular data with the help of
which user will be able to get a particular output. Some queries which have been implemented
using MS Access along with the output.
Query 1:
SQL view:
38

Fig. Output for query 1
Query 2:
SQL view:
Fig. Output for query 2
Query 3:
SQL view:
39

Fig. Output for query 3
Query 4:
SQL View:
Fig. Output for query 4
Query 5:
SQL View:
40

Fig. Output for query 5
Query 6:
SQL view:
Fig. Output for query 6
41

Query 7:
SQL view:
Fig. Output for query 7
Query 8:
SQL view:
42

Fig. Output for query 8
43

LO4 Be able to test and document relational database systems
4.1 Critically reviews and tests the implemented relational database system
User feedback is very vital as it is going to increase the database which has been already
developed as there is a requirement for more improvisation. The feedback from several users is
required to make the database strong for the database of college. To learn the database there must
be a separate training provided to the members of staff of the college. There have been various
forms designed for the process of data storing and inserting. To get the particular output there
has been different queries of search and reports.
Feedback Questions USER 1 USER 2 USER 3
Is the functionality of the
database is easy to use and
up to the mark?
Not up to the mark Yes Average
Is the database been useful
for you?
Yes Yes Don’t Know
Is the database designed
has security and protected?
Little less Secured fully Maybe Yes
Are you a frequent user or
someone who uses this
database occasionally?
Frequent Frequent Occasional user
what rating are you going
to give to this database on a
scale from 1 to 10?
7 10 8
Review:
Technical review:
44

The main part of the technical review is the documents of the database, lists of logging,
Technical review test log file. Database log file has been taken is used for the technical review.
Formal review:
It highlights mainly on the structure of database and control. Meeting, outset, reworks, follow-
up, planning, and preparation are considered as the phase of formal review. All the six steps have
been followed and have been executed the formal review using the phase of database design.
Informal review:
It is a faster review of the database which saves time. Here, it needs technical review, peer
review, and walkthrough review. During the informal review, there is a test relation among
tables.
Testing:
Black-box testing:
The user does not know about the input, result, and process. There has been a report designed
and execution of the query for the black-box testing.
Automated testing:
The mock-up data is checked with SQL query and DTM data generator. There has been a
comparison between SQL data and normal data. Query design has been designed by me.
Performance testing:
It checks efficiency, database load time, scalability and feasibility.
Manual testing:
With the help of DDL, DML and DCL database has been designed. The relationship and syntax
of the table have been checked and also the SQL query has been designed.
White-box testing:
The user has a knowledge of the input, process, and output. The column data invariants, record
data, referential integrity rules have been checked.
45

Functional testing:
According to the necessities of the user and system, the system is checked. It is of two types such
as white box and black box. This testing of the database is cheap.
46

4.2 Create documentation to support the implementation and testing of a
relational database system.
5. S.no. Test case Expected Result Actual Result Remark
1 Data value invalid in
the student table
(number enter in text
format)
An error
message should
occur for the
invalid data
value
An error
message occurs
for the invalid
data value
Performed
2 Primary key distinct The primary key
should be
distinct
Pop message to
re-enter the
primary key
Performed
3 Date format is wrong An error
message should
occur for Date
format
An error
message
occurred for
Date format
Performed
4 Foreign key not present
of mark analysis table
referential
integrity should
occur in the
database
referential
integrity
occurred in the
database
Performed
5 Primary key null
(course table)
The primary key
should contain a
unique value
Course table
Primary key
does not null
value
Performed
6 Foreign key not present
of the staff table
referential
integrity should
occur in the
database
referential
integrity
occurred in the
database
Performed
7 Primary key null (staff
table)
The primary key
should contain a
unique value
staff table
Primary key
does not null
value
Performed
47

Fig. Test plan 1
Fig. Test plan 2
Fig. Test plan 3
48

Fig. Test plan 4
Fig. Test plan 5
Fig. Test plan 6
49

Fig. Test plan 7
50

4.3 Create user documentation for a developed relational database system
User Documentation:
User documentation is very crucial as through that only the user will be able to understand and
navigate the database. At the end of the client side, this is required. All the information related
and task in the database which includes erasing, embed, refresh and inquiry the user needs to
have a knowledge about all this. So each client has an idea about all the characteristics and
functions are given to them in the database. It helps the client in properly using the database.
Fig.Form view of Classroom
51

Fig. Form view of course.
Fig. Form view of the department
52

Fig. Form view of the mark
Fig. Form view of staff.
53

F
Fig. Form view of the student.
54

4.4 Explain how verification and validation has been addressed
V-Model:
For the process of verification and validation, it is used by the database. It functions same like
waterfall model. The process is verified in V-Shaped Life Cycle. At a time it exists in a single
phase. In order for the next phase to start the previous phase needs to be completed. There has
been V-model used for the verification and validation of my database.
Fig. Course table for validation
Phases of V-Model:
Requirement Analysis:
All the need of the client is collected and among the organization and client, there is a
communication. Then there is testing for acceptance which takes requirement in place of input
and makes sure that the needs of the client are validated or not.
System design:
55

Then after that according to the needs of the client and system database has been designed. For
the development of database, there is a setup. To check the system design there is no need of
performing system plan.
Architectural design:
It is for designing of the database so that the feasibility of database can be offered at the final
design. The design of the database is broken into modules depending upon the functionality.
Module design:
This is the fourth phase of the V-Model. It takes in use the low-level design which is compatible
with external systems and system architecture. To find out the errors and faults in the database
design unit test is performed.
Coding Phase:
This is the fifth phase of the V-Model. The database is designed on the basis of coding To design
the database guidelines and database standards has been used.
Validation:
To check the correctness and quality of the information of the data it requires validation of the
data. Which increases the meaning of the data and its use. The data dictionary is used for storing
the data which is further utilized for the purpose of validation.
Verification:
In this process, data is being verified for a large amount and inconsistency. In this stability and
accuracy of the data is being verified. The transfer of data from one system to another is also
ensured. The security of the database is also secured.
56

Fig. course table for verification
57

4.5 Explain how control mechanisms have been used.
It has been implemented for factors like security, integrity, and availability. To maintain these
factors there are many control mechanisms like MAC, DAC, and RBAC
Fig. The control mechanism of classroom
58

Fig. control mechanism for the course.
Fig. control mechanism for the department
59

Fig. control mechanism for mark
Fig. control mechanism for staff.
There is a various field which is given in the above images in which field size, validation rule,
indexed value and many more and their description is also given.
60

Fig. control mechanism for the student.
According to the requirement of the user and the system, database has been designed keeping in
mind the database standards and guidelines. It has been designed for about 15-20 days through
the resources available. It has been designed keeping in mind the budget of the user. Using
enough human resource it provides the database of the best quality. To save time and budget a
proper plan is needed to be thought of.
ER diagrams and MS access are used to control the process of the database and for designing the
database. For information storage, MS Access uses data fields and data type.
Encryption:
It protects the information of the database. Only can be accessed by someone who has the keys.
Access Control:
It gives the security and only authorized access is allowed.
Flow control:
It manages the flow of data in the database.
61

Inference control:
It manages dependency of data in the database.
62

Conclusion
Here there has been a discussion made on the data analysis and design. Moving forward in the
first part there has been a discussion on the models of data, technologies, schemas, designs, and
approaches in relation to the database. In the second part, the database of college has been
implemented and ERD has been made, forms, normalization, and reports. Using ERD plus and
MS-Access database has been made. In the third part, various query tools have been discussed
with languages. In the last part, there has been a discussion on several test cases, a mechanism
to control the database r, user documentation, verification, and validation and feedback from the
user
63

References:
 Agiledata (2018). Database Testing: How to Regression Test a Relational Database.
[online] Agiledata. Available at: http://www.agiledata.org/essays/databaseTesting.html
[Accessed 11 Jun. 2018].
 Database Management (2018). Database Design Strategies. [online] Database
Management. Available at:
http://databasemanagement.wikia.com/wiki/Database_Design_Strategies [Accessed 11
Jun. 2018].
 Ercim (2018). DATABASE TECHNOLOGY. [online] Ercim. Available at:
https://www.ercim.eu/medconf/papers/jeffery.html [Accessed 11 Jun. 2018].
 Lucidchart (2018). What is a Database Schema. [online] Lucidchart. Available at:
https://www.lucidchart.com/pages/database-diagram/database-schema [Accessed 11 Jun.
2018].
 prezi (2018). Task 1- The use of and tools/techniques used in database. [online] prezi.
Available at: https://prezi.com/fgzx3bki2ner/task-1-the-use-of-and-toolstechniques-used-
in-database/ [Accessed 11 Jun. 2018].
 SearchDataManagement (2018). What is RDBMS (relational database management
system)? - Definition from WhatIs.com. [online] SearchDataManagement. Available at:
https://searchdatamanagement.techtarget.com/definition/RDBMS-relational-database-
management-system [Accessed 11 Jun. 2018].
 Studytonight (2018). 1NF, 2NF, 3NF and BCNF in Database Normalization |
Studytonight. [online] Studytonight. Available at:
https://www.studytonight.com/dbms/database-normalization.php [Accessed 11 Jun.
2018].
 Tabona, A. (2018). Top 10 free database tools for sys admins. [online] GFI Blog.
Available at: https://techtalk.gfi.com/top-10-free-database-tools-for-sys-admins/
[Accessed 11 Jun. 2018].
 Techopedia (2018). What is Data Manipulation Language (DML)? - Definition from
Techopedia. [online] Techopedia. Available at:
https://www.techopedia.com/definition/1179/data-manipulation-language-dml [Accessed
11 Jun. 2018].
64

 Techterms (2018). RDBMS (Relational Database Management System) Definition.
[online] Techterms. Available at: https://techterms.com/definition/rdbms [Accessed 11
Jun. 2018].
65