Database Design and Implementation for Desklib

This article discusses data models, schema, types of database management systems, database design approaches, and implementation of relational database systems for Desklib. It also provides a sample MS SQL relational database code.

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),

foreign key (clientID) references client (clientID),
foreign key (branchCode) references branch (branchCode),
foreign key (salesman) references employee (employeeID) on update
cascade on delete no action
);
Successful execution of the code
2.2 User interface design
Factors to consider while designing user interface;
 Easy to use- The user interface should be easy to learn and grasp for the end user this making it
easy to use at all times.
 Functionality of UI- The user interface designed should be able to meets its functionality. It’s
better to design a simple design that meets all the functionalities required rather than designing
a complex user interface that does not meet all the requirements.
 Reliability- The user interface should be reliable at all times thus the designer should design it to
perform as expected at all times
 Consistency- Consistency in the design should be maintained to make it possible for the user to
switch through different elements of the user interface. The user should not feel like they have
are using a new application after switching from one screen to another.
 Performance- The user interface is expected to perform different functionalities using the least
amount of time and resources available.
 Menu driven- The user interface designed for the application should be easy to navigate making
it easy for the user to use the whole application consistently.
 Button-based design- There are many rules to designing buttons but perhaps one of the most
important rules is to make a button look like an actual button. This can involve adapting
common button designs that make it easy for the user to recognize. Another important factor to
consider when designing buttons is to put buttons where users expect them (Nielsen, 2018).
Data requirements

Figure 6: Data flow diagram
The data diagram shown in the figure above can be used to show the data requirements that will be
critical in designing the user interface.
L03: Data Manipulation and Query tools
3.1 SQL features and functionalities
SQL (structured Query language) is a domain specific language. SQL was designed to manage data
through RDBMS. It handles structured data that is held in a relational database. The handle operations
are performed using different types of SQL which include;
 DDL- Data definition language- this subtype of SQL is used to define data structures in a
database. DDL statements are used create, remove and modify database objects. These
statements are used for database objects like tables, users or indexes. Common DDL statements
include CREATE, DROP and ALTER.
 DML- Data Manipulation language is a subtype of SQL that is used to add, delete or modify data
in a database. Examples of common DML statements include INSERT, UPDATE and DELETE.
 DCL (Data Control Language) is a substype of SQL that is used to control the access of data in a
database. Example of a DCL command is GRANT.
There are different query tools that can be used to execute SQL statements for example using terminal
or using SQL server management studio. These tools make it easy to write and execute SQL statements
by making it easy for the user to write and debug queries. SQL tools also provide better aggregation of
data thus the data makes more sense to the user.

3.2 Applying DML
Database insert queries.
insert into employee (employeeID, firstname, lastname, position,
supervisor) values
('E001','John','Doe','Director',null),
('E002','Arya','Stark','manager','E001'),
('E003','Cersei','Lannister','manager','E001'),
('E004','Brianne','Tarth','Engineer','E002'),
('E005','Geofrey','Baratheon','Technician','E004'),
('E006','Khal','Drogo','salesman','E005'),
('E007','Snow','White','salesman','E005'),
('E008','Parker','Peter','salesman','E005');
insert into branch (branchcode,manager,street,city,postcode) values
('B001','E002','New York dr','New York',1234),
('B002','E002','los Angeles dr','los Angeles',1235),
('B003','E002','Queens dr','Quees',1233),
('B004','E003','Melbourne road','Melbourne',1234),
('B005','E003','New York dr','New York',1234),
('B006','E003','Winteefell dr','Winterfell',1235),
('B007','E002','kings landing dr','Kings landing',1235),
('B008','E002','New York dr','New York',1234);
insert into equipment (equipmentID,name,branchCode) values

('Q001','equipment one','B001'),
('Q002','equipment two','B002'),
('Q003','equipment three','B003'),
('Q004','equipment four','B004'),
('Q005','equipment five','B005'),
('Q006','equipment six','B006'),
('Q007','equipment seven','B007'),
('Q008','equipment eight','B008');
insert into client
(clientID,firstName,lastname,age,phone,street,city,postcode) values
('C001','Peter','Griffin',23,'[phone]','winterfell
drive','winterfell',1234),
('C002','lois','Griffin',24,'[phone]','winterfell
drive','winterfell',1234),
('C003','meg','Griffin',25,'+3434334','winterfell
drive','winterfell',1234),
('C004','Brian','Griffin',34,'[phone]','winterfell
drive','winterfell',1234),
('C005','Stewie','Griffin',56,'[phone]','winterfell
drive','winterfell',1234),
('C006','cleveland','Brown',23,'[phone]','cleveland
drive','cleveland',1234),
('C007','Rallo','Tabs',23,'[phone]','cleveland
drive','cleveland',1234),
('C008','roberta','tabls',23,'[phone]','cleveland
drive','cleveland',1234);

insert into sales (saleID,clientID,equipmentID,salesman,salesDate)
values
('S001','C001','Q001','E001','2018-12-12'),
('S002','C002','Q002','E002','2018-12-12'),
('S003','C003','Q003','E003','2018-12-12'),
('S004','C004','Q004','E004','2018-12-12'),
('S005','C005','Q005','E005','2018-12-12'),
('S006','C006','Q006','E006','2018-12-12'),
('S007','C007','Q007','E007','2018-12-12'),
('S008','C008','Q008','E008','2018-12-12');
insert into hiring
(hiringID,clientID,equipmentID,salesman,hireDate,branchCode,returnDate
) values
('H001','C001','Q001','E001','2018-12-12','B001',null),
('H002','C002','Q002','E002','2018-12-12','B002',null),
('H003','C003','Q003','E003','2018-12-12','B003',null),
('H004','C004','Q004','E004','2018-12-12','B004',null),
('H005','C005','Q005','E005','2018-12-12','B005',null),
('H006','C006','Q006','E006','2018-12-12','B006',null),
('H007','C007','Q007','E007','2018-12-12','B007',null),
('H008','C008','Q008','E008','2018-12-12','B008',null);

3.3 Features of a query tool
For the database, the query tool used to perform DML was SQL Server Management Studio 2017. This
tool made it easier to perform DDL and DML operations on the database. The tool provides a simplified
way of writing SQL queries using a graphical user interface that is easy to use and the SQL code is
executed on the background. After performing DDL and DML operations using the SQL server query tool
the tool can be used to show the status of the database by showing the number of tables and their
structures and showing records in a table. Database query tools can also be used to perform forward
engineering of databases to export
L04: Testing and documenting relational database
systems.
4.1 Database testing methods;
 Black box testing
Black box testing for a database is done without knowledge of the internal structure of the
system. This means that the user does not have to know the objects making up the system and
their relationships. A good example of a test is to just focus on the inputs and outputs without
the need to know how the processing will take place (Goel, 2018).
 White box testing
White box testing in database systems involves testing the internal structure of the database
regarding its inputs, outputs and relationships between the objects making up the database
system. For example a test can be done to test whether an input will result to a certain output.
This can also include just going through the code to identify changes.
 Acceptance testing- Acceptance testing for database systems is done to ensure that the
database system meets its requirements and operates how it is expcted to operate. For example
storing a new record for an employee should save the record.
4.2 Test plan
Test
Description
Expected
outcome
Actual outcome

Select top
200 rows in
branch table
using SQL
managemen
t server tool
The
database
should
select top
200 rows
in the
client
table.
Select top
200 rows in
client table
using SQL
managemen
t server tool
The
database
should
select top
200 rows
in the
table.
Select top
200 rows in
employee
table using
SQL
managemen
t server tool
The
database
should
select top
200 rows
in the
employee
table.
Get all hiring
records that
whose item
has not been
returned
The
database
should
return all
records in
the hiring
table
where the
return
date is
null

Change the
sales date of
the first
record in the
sales table.
The sales
date of
the first
record in
the sales
table
should be
updated
to the
new value
Update
employee Id
branch
manager
The id of
the
manager
should be
updated
and all
related
records in
the
branch
should be
updated
too
Change the
equipment
id of an
equipment
that is
involved in a
sale
The
dabatase
should
restrict
this action
because it
violates
this action
Delete an
equipment
that is
already in
the sales
table.
The
database
should
restrict
the action
because it
violates
the

foreign
key
Fetch
records from
client and
sales table
Join
should
work
between
the two
tables
Fetch
records from
the
employees
and
branches
table
Join
should
work
between
employee
s and
branches
table
4.3 Importance of technical and user documentation
Technical and user documentation is important in a database system because it acts as a basis through
which the end users can learn about the system from how to open and use the system. The user
documentation can also act as point of reference users want to refer incase the user has forgotten on
how the system works.
The following user document can be used for the following database system:
Opening the system.
To open the system your system should have the following minimum requirements:
Memory * Minimum:
Express Editions: 512 MB
All other editions: 1 GB
Recommended:
Express Editions: 1 GB

All other editions: At least 4 GB and should be increased as database size increases to ensure optimal
performance.
Processor Speed Minimum: x64 Processor: 1.4 GHz
Recommended: 2.0 GHz or faster
Processor Type x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support,
Intel Pentium IV with EM64T support
To open the system navigate to the sql server management studio and run the program.
4.4 Verification and validation
Verification and validation involves ensuring that database is running the way its intended to. Validation
and verification for the proposed database was enforced by defining the appropriate data types for each
column. For example a column like sales date in the sales table should only take a date value or a value
that can be parsed to a data implicitly by SQL server. Fields that take integers should only accept
integers or a value that can be implicitly converted to an integer. Verification and validation was also
achieved through referential constraints implemented for different relationships. Referential contraints
ensure that no record is inserted in the child tables for records that do not exist in the parent table.
4.5 Control mechanisms
There are various control mechanisms that are available in a database system. These mechanisms are;
 Users- this mechanism involves creating a user and granting rights and privileges to the user.
The privileges can include execution of different DML, DDL or DLC statements. The user is only
allowed to execute statements under the user he or she is assigned to.
 Roles- A role is a collection of a number of permissions that can be assigned to one or many
users. The privileges can include execution of different DML, DDL or DLC statements. A user
allocates only privileges which exist in the role that he or she is assigned to.
References
Goel, A. (2018). Database Testing: White Box and Black Box - Magoosh Data Science Blog. [online]
Magoosh Data Science Blog. Available at: https://magoosh.com/data-science/database-testing-white-
box-and-black-box/ [Accessed 4 Dec. 2018].
Kokemuller, N. (2011). Advantages & Disadvantages of Traditional File Organization. [online]
Yourbusiness.azcentral.com. Available at: https://yourbusiness.azcentral.com/advantages-
disadvantages-traditional-file-organization-28765.html [Accessed 4 Dec. 2018].
Nielsen, J. (2018). 10 Heuristics for User Interface Design: Article by Jakob Nielsen. [online] Nielsen
Norman Group. Available at: https://www.nngroup.com/articles/ten-usability-heuristics/ [Accessed 29
Nov. 2018].
Rouse, M. (2015). What is database management system (DBMS)? - Definition from WhatIs.com.
[online] SearchSQLServer. Available at: https://searchsqlserver.techtarget.com/definition/database-
management-system [Accessed 4 Dec. 2018].

Singh, C. (2014). Advantages of DBMS over file system. [online] beginnersbook.com. Available at:
https://beginnersbook.com/2015/04/dbms-vs-file-system/ [Accessed 4 Dec. 2018].
Studytonight.com. (n.d.). Database Models in DBMS | Studytonight. [online] Available at:
https://www.studytonight.com/dbms/database-model.php [Accessed 4 Dec. 2018].
Techopedia.com. (2014). What is a Hierarchical Database? - Definition from Techopedia. [online]
Available at: https://www.techopedia.com/definition/19782/hierarchical-database [Accessed 4 Dec.
2018].
Thakur, D. (2013). Traditional File Processing System. [online] Ecomputernotes.com. Available at:
http://ecomputernotes.com/fundamental/what-is-a-database/traditional-file-processing-system
[Accessed 4 Dec. 2018].
Visual-paradigm.com. (n.d.). Conceptual, Logical and Physical Data Model. [online] Available at:
https://www.visual-paradigm.com/support/documents/vpuserguide/3563/3564/85378_conceptual,l.ht
ml [Accessed 4 Dec. 2018].