Data Analysis and Design: A Comprehensive Guide to 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