Database Systems for Toulis Construction: Diagrams, SQL Queries, Security Measures and Future Improvements

This report discusses the database management system for Toulis Construction, including diagrams, SQL queries, security measures, and future improvements. It covers the database system diagram, data dictionary, physical and logical entity relationship diagram, SQL queries, security measures, and system administration and management tools. The report concludes with future improvements for the database system.

DATA BASE SYSTEMS

TABLE OF CONTENT
INTRODUCTION...........................................................................................................................3
MAIN BODY..................................................................................................................................3
Database system diagram............................................................................................................3
Data dictionary.............................................................................................................................5
Physical and logical Entity relationship diagram........................................................................5
SQL queries tables in database system......................................................................................12
Security measures to control access to data...............................................................................18
System administration and management tools for managing the security and authorisation....20
CONCLUSION..............................................................................................................................22
REFERENCE................................................................................................................................23

INTRODUCTION
Database management system refer s computerized system for keeping the data. It offers
the facilities to the users to perform several kind of operation for manipulating the data into the
system. It can be of hierarchical, network and relational database (Medina-Martínez and et.al
2020). Among all RDBMs is widely adapted and use as in this data is arranged in logical order
inside the independent tables. Thus it provides the better management of incoming data as it
arranges it in order and gives the ways for data modification also can be retrieved by the user. It
allows to perform the create, delete, update operation easily. With this Toulis construction ltd.
Company can be able to manage their information, they will be allowed to maintain all their
projects with greater efficiency. Company will easily proceed to monitor and manage the
accuracy and relevancy in the data that can be used for best. Following report will cover the
database management system for the organisation in order to arrange the useful details. It
includes the database system diagram which shows the relationship among tables that carries
reliable information, also involve the entity relationship diagram using both chen’s and crow foot
notation. And it will also highlight the SQL queries for system and also discuss the security
measures in the system.
MAIN BODY
Database system diagram
DBMS diagram usually shows the graphical structure of the system where it represents the
relations among the database objects. Where foreign key and primary constraint are involving to
develop the relationship between the tables. It mostly considers as the visual layout of the system
to develop the understanding about the system, to allow for analysing the data flow and other
system’s operation database diagram made it easier and simple (García and López-Morteo,
2021). Database management tools helps in providing the better web interface for administrating

the database system. For this report SQL server is used for creating the system. As for the
database system it covers the tables entities which are based on the scenario which are project,
project manager, employee, description and department table along with its attributes.

Data dictionary
It is the collection of the names, data attributes and other elements which are stored in the
system. Data dictionary can also be understood as the catalogue that consists of data and provide
the meaningful description.
Entities Attributes
Project p_id, p_name, start_date, end_date, dept_id
Department dept_id, dept_name, pmanager_id
Employee emp_id, emp_name, emp_address, emp_phoneNo, dept-id, p_id
Project manager pmanager_id, pmanager_name, pmanager_phoneNO
Description descr_id, work_unit, installation_cost, time_unit, material_costUnit,
emp_id, p_id
Physical and logical Entity relationship diagram
ER diagram shows the relationship between the entity sets that are being kept in the
database system. It develops the understanding in logical arrangement of the database, it
basically consists of the entity, attribute and relationship concepts (Viloria and et.al 201). To
avoid any kind of misunderstanding entity is represented in rectangular shape, attribute can be
shows with ellipse and relationship can be shown by using diamond shape so greater
understanding will be reached. In this physical ER diagram mainly shows the actual design of
database system, it mainly accords with transformation from the logical to schema designing

level that is supported by the relational database system. Whereas logical ER diagram is made on
the business rules and also in terms of the specific technology that are being implemented. ER
diagrams are associated to the data structure which concentrated on the element relationship
among the entities rather than directly connected with entities.
This report has shown the ER diagram by using Visio software for designing these
diagram. ER diagram consists of the relationship, entities and attributes that are required for the
toulis organisation to develop the database system. Thus for this project suitable diagrams have
been developed by using the chens’ and crow foot notation. Both are efficient in order to give the
better visual representation of the ER diagram (Perez-Riverol and et.al 2019).

SQL queries tables in database system
A standard query language SQL is used for retrieving and manipulating the data that are
stored in the database management system. It allows user to execute the query to find certain or
specific output. It helps in performing the several operations which are executing, retrieving,
inserting, updating and to develop the new records all can be done by implementing SQL
queries. It is shown below, certain queries have been run to create the tables and later it also
represents the insert operation to load the data tables for forming the database system for the
Toulis construction organisation (Krochmal, Cisek and Husi, 2018). In SQL query section it also
describes the delete and update operation that are performed on the table. For every table SQL
queries have written down with its appropriate result, further it runs the insert queries along with
the output.
Project table

Employee table

Project manager

Department

Description

UPDATE and DELETE query
Security measures to control access to data
Database security define as the use of tools, controls that are developed and preserves the
database confidentiality. It helps in keeping data integrity and availability of the data. Database
security is mainly a complex and challenging which covers the all information technologies for
security. As it is seen that if there are more access and utilisation of the data then it gives more
vulnerabilities to security threats.

Authentication: It refer to the best process for keeping the data secure, it ensures that only the
user is login to the system on the basis of the information that are provided them only to access
the system. User can only login to their privilege as they are not allowing to access the sensitive
information. By using such authentication system confidentiality and reliability can be
maintaining easily and often reduces the chances for the security threats (Yamazaki and et.al
2020).
Access control: An DBMS security mechanism always involves some provisions that restricts
the unauthorised user to access the information.
Figure Testing solution for database

Data recovery in SQL server
It is the process; it recovers the data which have been lost due to any reason. In the SQL
management studio there is an option for restoring the database so that user can access the
information even it is accidently deleted or corrupted.
System administration and management tools for managing the security and authorisation
Maintaining the security is very important factor, in SQL server there is a option present
related to security, that can be easily found on the object explorer segment. In this user can easily
develops the logins and can proceed to allot the server roles for any database system. Also there
are services which helps in allotting the roles based safer logins. In SQL server it provides the
inbuilt feature that helps in enabling the security which covers the encrypted communication
over the windows data protection, also utilised to encrypt the data, authentication. SQL server is
developed for delivering the better and secure platform so that database can easily have
developed and maintained. Below it shows the security operation.

Encryption in the SQL server can be done by using the SSL (secure sockets layer). SQL
server usually transforms the data by using the tabular data protocols which is generally
unencrypted. Administrators enables the certificate and also enables the SSL by force encryption
that are present in the database configuration. Windows data security also termed as the
encryption ability which is developed in the windows. In SQL server mostly authentication is lay
out with the use of active directory. It is analysed that when the SQL platform verified the AD
details ion data base engine then it means that it allows the user to access the perform several
operations over the database system. Thus system uses the highly encrypted algorithms to
provide the better security mechanism so there will be reduce in chances for the risks and
attacks.
For the effectiveness of the system mainly SQL server helps in enhancing the overall
performance by allowing to build the transparent data and encryption attributes. For encrypting
or securing the data it is not necessary to modify the programs. This platform also provides the
allowance management tools along with the access controls that are mainly develop for
providing users a secured information. Thus by using this database system toulis construction
company will be able to manage their projects, they can easily monitor the project performance.
Future improvements for the database system
As for the future improvement to continue the effectiveness ion the database system, toulis
company can begin to optimise the queries, as if the query structure is poor and ineffective then

it ultimately impacts the system performance. To proceed with proper indexing that helps in
query execution, indexes allow to implement the data structure that keeps the information in
organised manner so that information can be easily accessed without waste of time and efforts.
Company can also proceed to focus on investing on the stronger CPU which will directly
enhances the database performance. If the CPU runs properly than ultimately database respond
properly. Another option for future improvement can be to use the data fragmentation if the
database system is slow and not able to give the fast solutions. It will be beneficial for the toulis
company to use the disks type that helps in performing the SQL queries easily. As by using the
SSD disks company can easily improvise their overall performance of the system that guides in
better layout of the projects for increasing their business profitability. Database version is also
important, it is essential to upgrade the software for better use and to deliver the better outcomes.
CONCLUSION
From the above report it is concluded that utilisation of the database system helps in
managing the information in appropriate manner. Above report have discussed the description of
database diagram that have been used for representing the database management system for the
organisation. It further illustrated the description about the entity relationship diagram which
covers both logical as well as physical ER model using chen and crow foot notation, it developed
in the Visio software. Further it covered the SQL queries to run the specific operations that are
performed on DBMS system, it covered the create table query to develop the table for storing the
information further covered the insert operation to load the data into the system. Report further
illustrated the discussion about the future improvements in order to continue the effectiveness of
the database system. It also performs the update and delete query and lastly concluded with the
description of the security concern with the system, as how suitable SQL server could provide
the security tools for maintaining the confidentiality and reliability of the system.

REFERENCE
Books and journals
García, C.A.E. and López-Morteo, G., 2021, August. Proposal of topics for data science through
the development of a database management system. In 2021 Mexican International
Conference on Computer Science (ENC) (pp. 1-7). IEEE.
Krochmal, M., Cisek, K. and Husi, H., 2018. Database Creation and Utility. Integration of
Omics Approaches and Systems Biology for Clinical Applications, pp.286-300.
Medina-Martínez, D. and et.al 2020, November. Formal Verification of a Database Management
System. In 2020 8th International Conference in Software Engineering Research and
Innovation (CONISOFT) (pp. 102-109). IEEE.
Perez-Riverol, Y. and et.al 2019. The PRIDE database and related tools and resources in 2019:
improving support for quantification data. Nucleic acids research. 47(D1). pp.D442-
D450.
Viloria, A. and et.al 2019. Integration of data mining techniques to PostgreSQL database
manager system. Procedia Computer Science. 155. pp.575-580.
Yamazaki, R. and et.al 2020. Development of JT-60SA experiment database system. Fusion
Engineering and Design. 155. p.111555.