Database System Development and Management for Toulis Construction

This report details the design and development of a database system for Toulis Construction Ltd, a company requiring a new project management system. The report includes a database system diagram illustrating relationships between tables (project, project manager, employee, description, and department), a data dictionary defining entities and attributes, and both physical and logical Entity Relationship Diagrams (ERD) using Chen's and Crow's Foot notation. SQL queries for table creation, data insertion, updates, and deletions are provided, along with a discussion of security measures, authentication, access control, and data recovery. The report also covers system administration and management tools for security and authorization, emphasizing SQL Server's features for encryption and user role management. Future improvements for the database system, such as query optimization and hardware upgrades, are also discussed. The report concludes that the database system facilitates efficient information management, enhanced project monitoring, and improved security for Toulis Construction Ltd. Desklib offers similar solved assignments and past papers for students.

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