Data Analysis and Design Report: HND Computing Unit 33 (L5) - Database

This report provides a detailed exploration of data analysis and database design, covering key concepts from data models and schema to the design and implementation of relational database systems. It begins with an introduction to various data models, including hierarchical, network, entity-relationship, and relational models, along with a discussion of conceptual, logical, and physical schema. The report then delves into different types of database management systems (DBMS), such as traditional file processing systems and DBMS, highlighting their advantages and disadvantages. The design and implementation of relational databases are examined, including the creation of a conceptual ERD and data dictionary, and the use of MS SQL code for database creation. Furthermore, the report covers data manipulation and query tools, including SQL features and functionalities and DML application, along with features of a query tool. Finally, the report addresses database testing methods, test plans, the importance of technical and user documentation, and the verification and validation processes, along with control mechanisms.

COVER PAGE

Contents
Task L01.......................................................................................................................................................3
1.1 Data models and schema in data analysis.........................................................................................3
1.2 Types of database management systems..........................................................................................6
1.3 Database design approaches.............................................................................................................8
L02: Design and implementation of relational database systems...............................................................8
2.1 Design................................................................................................................................................8
2.2 Ms Sql relational database code......................................................................................................11
2.2 User interface design.......................................................................................................................13
L03: Data Manipulation and Query tools...................................................................................................14
3.1 SQL features and functionalities......................................................................................................14
3.2 Applying DML..................................................................................................................................15
3.3 Features of a query tool...................................................................................................................18
L04: Testing and documenting relational database systems.....................................................................18
4.1 Database testing methods;..............................................................................................................18
4.2 Test plan..........................................................................................................................................18
4.3 Importance of technical and user documentation...........................................................................21
4.4 Verification and validation...............................................................................................................22
4.5 Control mechanisms........................................................................................................................22
References.................................................................................................................................................22

Task L01
1.1 Data models and schema in data analysis
A data model is a simplified representation of complex real world data structures. In database
management systems there are different types of data models each of each has its own advantages and
disadvantages. Types of data models include;
 Hierarchical data model
 Network and relational data model
 Entity-relationship model
 Relational model
 Schema (Conceptual, Logical and pysical schema).
Hierarchical data model
This type of data model is used to make a hierarchical database. A hierarchical database is designed to
use one-to- many relationships for entities making the database. The database uses a tree like structure
where an entity acts as a parent and other child entities linked to the parent entity are the child entities.
The concept behind hierarchical database model is useful in specific types of databases but this model
lacks versatility because it is limited to the hierarchical nature of the data where by an entity cannot
have more than one parent node. The model has to maintain a hierarchy. An example of data that can
be represented using hierarchical model is shown in the figure below;
Figure 1: hierarchical data model example
Network data model
The network data model was designed as an extension of the hierarchical data model. This model
improved on the hierarchical model by allowing an entity to have more than one parent entity thus
resulting to a graph like structure compared to the tree like structure of the hierarchical model.
Databases designed using the network model have more relationships because of the many

relationships existing between the data. Because of the many relationships, the network model makes it
easier and fast to access data. The figure below shows an example of a graph like network model.
Figure 2: network data model example
Entity-relationship model
Entity relationship data model involves designing the database by designing objects into entities and
then designing the characteristics of the object into attributes. The entities are related with one another
through relationships. Entity relationship diagrams are commonly used in this data model to model the
database. The entities represented in the entity relationship diagram can then be turned into tables to
form a relational database. A good example is designing a database for a school where a student is an
object. The student object can be designed as an entity having attributes like student id, name, address
and age as shown in the figure below;

Figure 3: Entity-relationship model
Relational Model
Relational model involves organizing data in two dimensional tables and maintaining the relationship by
through a common field. E.F Codd introduced this model in 1970 and it’s the most widely used data
model to date. Tables in relational model are known as relations which is the basic structure of data. The
concept of normalization is widely used in relational model to achieve highly normalized relations. The
figure below shows sample data in a relational model;

Figure 4: Relational model sample data
Schema
Conceptual, logical and physical data model are used to model data. All of them contain entities,
relationships but they are different in terms of their purpose and the target audience they are intended
for. In general, the conceptual and logical data models are to model data in a business oriented manner
while the physical data model is used by the database designer to represent the physical database
structure for implementation of the database.
Conceptual model is specifically used to model data gathered from business requirements. The entities
and relationships in this model are designed based on the business requirements.
Logical model is an advanced conceptual model that is also modelled based on the business
requirements. It differs from the conceptual model because it shows columns and data types although
the column type can be optional.
Physical model represents the final blueprint of the relational database that will be used to implement
the database in a relational database management system. This model is used by the database designer
and is supposed to be followed as it is.
1.2 Types of database management systems
 Traditional file processing system
This type of database management system was developed in an attempt to shift from the
manual filing system (Thakur, 2013). It offers a method through which files can be stored and

organized thus making it easy to access the data contained in the files. Traditional file systems
used storage devices like CD-ROM or a hard disk. Characteristics of the file processing system
are;
o Data is stored as a group of files.
o Each data file is independent on its own.
o Flat file is the name used to describe each file.
o A file contains processed information for a specific function, for example inventory or
accounting.
Traditional file processing system have been in use for a long time. Advantages of using
traditional file processing system are;
o They are easy to use- Setting up and using traditional file processing systems is easy but
on condition that the number of files in question is small (Kokemuller, 2011).
o Requires minimal investment- Adapting traditional file processing systems requires
minimal investment because it does not require development of complex software to
manage the data. The purchase of a file processing system can be a one off small fee
with minimal management and maintenance.
However, traditional file processing system has presented its limitations over the years due to
the diversification of data needs by organizations. Some of the limitations are;
o Isolated and separated data- Making decisions involves analysis on the different files to
determine which file has the right data or to relate the data between the files.
o Data duplication- Data is stored in multiple files most of the times. This results to
unnecessary duplication of data which leads to waste of the storage space thus
increasing the associated costs (Singh, 2014).
o Using traditional file processing systems presents a challenge of presenting the data
from the user’s view because of the multiple isolated data files.
o Data security in traditional file system because the information stored in flat files is
easily accessible.
o Concurrency problems- access of the same data file by different users can create
concurrency problems in the data if the two users are accessing data simultaneously.
o Transactional problems- Traditional file based system does not meet ACID (Atomicity,
Consistency, Isolation, Durability) properties.
 Database management system (DBMS)
DBMS is a system that is designed to act as an interface between the database and the end user
of the database. The end user can be a real user or an application. DBMS provides an interface
through which CRUD (Create, Read, Update, and Delete) actions can be performed on a
database.
DBMS systems were designed to solve limitations of the traditional file system and to improve
on its features. Relational Database Management System (RDBMS) is a type of DBMS that has
row-based table structure connecting data elements and includes other additional functions
that ensure accuracy, security, consistency and integrity of the data.
Advantages of DBMS can be looked at on the basis of the limitations of the traditional file based
system. These advantages are;

o DBMS eliminates the issue of redundant data by use of normalization. By maintaining
one repository of data that can be accessed by many users there is no need to maintain
multiple files for each user as it is in the case of traditional file processing system.
o DBMS eliminates the issue of concurrency through normalization and other additional
mechanisms that ensure there is consistency when data is accessed and modified
simultaneously by more than one user (Rouse, 2015).
o Data integrity in DBMS is ensured as the database is high normalized thus valid
relationships exist between the entities. DBMS makes it possible to enforce constraints
like cascade update which ensure that data is always consistent between entities that
are related.
o Data searching is fast in DBMS compared to the traditional file processing system
because by using queries a DBMS can search large sums of data.
1.3 Database design approaches
The following approaches can be used to design the database;
 Top-down approach- this database design approach starts from the general moving to the
specific meaning that the starting point of the database design is the general idea of what is
expected of the system and then determining what data has to be stored by the end users. Top
down approach requires a deep understanding of the system (Studytonight.com, n.d.).
 Bottom up approach starts with the specific moving up to the general details of the required
system. The starting point of the design process is review of reports, forms and screens then
working backwards to determine what type of data will be stored in the database.
 Tools and techniques approach
This approach involves designing the database through three phases; conceptual phase, logical
phase and physical phase. In each phase a model is achieved.
Conceptual phase is specifically used to model data gathered from business requirements. The
entities and relationships in this model are designed based on the business requirements
(Visual-paradigm.com, n.d.).
Logical model is an advanced conceptual model that is also modelled based on the business
requirements. It differs from the conceptual model because it shows columns and data types
although the column type can be optional.
Physical model represents the final blueprint of the relational database that will be used to
implement the database in a relational database management system. This model is used by the
database designer and is supposed to be followed as it is.
L02: Design and implementation of relational database
systems
2.1 Design
Based on the requirements, the following conceptual model can be used to model the database.

Figure 5: Conceptual ERD
The conceptual ERD can be used to design a data dictionary that will be used to implement the database
as follows;
Table Column Data Type Null/Not Null Constraint
Employee EmployeeID Char(5) Not null Primary key
firstName Varchar(50) Not null
Lastname Varchar(50) Not null
Position Varchar(25) Not null
supervisor Char(5) Null Foreign key
references
employee
(employeeID)
Branch branchCode Char(5) Not null Primary key
manager Integer Not null Foreign key

references
employee
(employeeID)
Street Varchar(50) Not null
City Varchar(50) Not null
postcode Integer Not null
Equipment equipmentID Char(5) Not null Primary key
Name Varchar(50) Not null
branchCode Char(5) Not null Foreign key
references Branch
(branchCode)
Client clientID Char(5) Not null Primary key
firstName Varchar(50) Not null
lastName Varchar(50) Not null
Age Integer Not null
Phone Varchar(25) Not null
Street Varchar(50) Not null
City Varchar(50) Not null
postcode Integer Not null
Sales saleID Char(5) Not null Primary key
clientID Char(5) Not null Foreign key
references client
(clientID)
equipmentID Char(5) Not null Foreign key
references
equipment
(equipmentID)
salesMan Char(5) Not null Foreign key
references
employee
(employeeId)
SalesDate Date Not null
hiring hiringID Char(5) Not null Primary key
BranchCode Char(5) Not null
clientID Char(5) Not null Foreign key
references client
(clientID)
equipmentID Char(5) Not null Foreign key
references
equipment
(equipmentID)
hireDate Date Not null
returnDate Date Null
salesMan Char(5) Not null Foreign key
references
employee
(employeeId)

2.2 Ms Sql relational database code
create table employee (
employeeID char(5) primary key,
firstName varchar(50) not null,
lastname varchar(50) not null,
position varchar(25) not null,
supervisor char(5) null
);
alter table employee add foreign key (supervisor) references employee
(employeeID) on delete cascade on update cascade;
create table branch (
branchCode char(5) primary key,
manager char(5) not null,
street varchar(50) not null,
city varchar(25) not null,
postcode integer not null,
foreign key (manager) references employee (employeeID) on update
cascade on delete no action
);
create table equipment (
equipmentID char(5) primary key,
name varchar(50) not null,
branchCode char(5) not null,
foreign key (branchCode) references branch (branchCode) on update
cascade on delete no action
);
create table client (
clientID char(5) primary key,

firstname varchar(50) not null,
lastname varchar(50) not null,
age integer not null,
phone varchar(25) not null,
street varchar(50) not null,
city varchar(50) not null,
postcode integer not null
);
create table sales (
saleID char(5) primary key,
clientID char(5) not null,
equipmentID char(5) not null,
salesman char(5) not null,
salesDate date not null,
foreign key (equipmentID) references equipment (equipmentID),
foreign key (clientID) references client (clientID),
foreign key (salesman) references employee (employeeID) on update
cascade on delete no action
);
create table hiring (
hiringID char(5) primary key,
branchCode char(5) not null,
clientID char(5) not null,
equipmentID char(5) not null,
hireDate date not null,
returnDate date null,
salesman char(5) not null,
foreign key (equipmentID) references equipment (equipmentID),