Database Design and Manipulation

This report discusses data manipulation language statements such as delete, update, and insert, along with transaction management in databases. It also covers the importance of database testing and provides examples of different types of tests. The report concludes with a discussion on database design and the use of structural query language for development.

Individual Report Database
Development
1

INTRODUCTION
A database is based on the collection of data or information that mainly organised
effectively and efficiently. This will provide the facility to access, manage and update large
amount of data within system. Usually database may consider as an important for aggregations
of information records, files and also containing the appropriate data about the transaction or
interactions with consumer.
The report is based on the database whereas company will use to collect or gather large
amount of information. in order to establish the coordination between one or two department
respectively. It will describe about the data manipulation language that usually contain the
different statement which mainly used in the database table design, creation as well as
modification. Furthermore, it will identify the transaction in the database for controlling and
managing the overall processes.
TASK 1
Discuss about the data Manipulation language statements such as delete, update with using
correct syntax with example.
Data manipulation language is based on the computer language that mainly include
various command which permitting user to manipulate data into database system. in this way, it
involved the manipulation for inserting data into database table. Furthermore, it also retrieve the
existing data or information, deleting from existing tables (Roy-Hubara and Sturm, 2020).
Generally, DML is commonly incorporated in the structure query language database.
Data manipulation is resemble with the English language so that it become easier for user
to access information (Chiusano, 2020). In order to enhance the efficient user interaction with
database system. it also used the different kind of commands within database such as select,
insert, update and delete.
Select: this type of command is basically used to retrieve rows from database table.
Syntax
 Select column_name
 From table
 Where condition
2

Update: this type of command is used for modifying the data of one or more records. In this way,
it can be used this type of command
Syntax
 Update table name
 Set column name = value
 Where condition
Insert: this command is basically used for adding one or more record within database table. In
order to insert the information through commands.
Syntax
 Insert into table name
 Colum value
Describe the transaction in the database with example.
A database transaction is a type of symbolizes unit of performed the different action in
the database management system (Roy-Hubara and Sturm, 2020). it can be performed the
activities against the database and also treated as coherent as well as reliable way to perform the
transaction process. The primary purpose of transaction in database is to provide the reliable unit
of work in which allow better recovery from failure (Komarov and et.al., 2020). In this way, it
keep maintain the consistent at the time of failure situation.
Furthermore, there large number of calculation because database contain numerical value
to perform the different tasks. Therefore, Transaction process will help to handle logical
operation in step by step manner.
For Example-
The database is used to store large amount of data or information require by real life
application such as healthcare, finance company. All the money stored in the database so as
require to protect information and keep maintain consistency (Roy-Hubara and Sturm, 2020). On
the other hand, transaction database contain acid properties to manage or organise the
information effectively.
3

TASK 2
List all employees who are working within HR department and Name start with “A”
Output
Listing all titles (software engineer) of employees who are getting more than £20000 salary.
Output
Listing full names and gender who belong to the marketing department.
Output
List the all department names and their department’s managers.
4

Output
List the employees who are hired between 1980 and 1995.
Output
5

Change the employee title who belong in finance department and first name start with “S”.
Increase salaries of all employees up to 10% who are working from 1997 and belong to
marketing department.
Output
Delete employees’ record who belongs to the sales department and with the last name beginning
with ‘B’.
Initially identifying records
Output
6

Create a database view to list full names of all employees, their departments and salaries.
Output
7

Database view to list all departments and their departments’ managers, who were hired between
1990 and 2000.
TASK 3
Define the Database Testing and why is it important?
Database testing is consider as a process which mainly used for performing the different
action in order to identify any type of bugs, errors within system. That’s why, it is mainly used
the database testing. It is an essential part to ensure that value that application retrieve or store in
the database in proper manner (Alaydrus and et.al., 2020). Furthermore, it may test the database
which require to have enough knowledge of SQL so as performing actions. Database testing can
be conducted the session to eliminate or remove the errors. Therefore, tester can easily
understand the entire performance of application in proper manner.
Generally testing provide as quality assurance which defining the procedures to carry out
the testing. The important good practice before starting the procedures to perform the queries at
one place (Komarov and et.al., 2020). This will help for tester team to execute query which will
create, modify and delete specifications.
In additional, database testing is important to achieve desirable goal and objective of
database system. it ensure the data mapping whether check the field in user interface and also
ensuring ACID properties to perform the action for maintain the consistency, durability, isolation
as well as atomicity.
8

Provide the screen shots of the testing process on Employees database
 Field size validation: this type of validation is an essential within database system. Each
and every database table contain the specific information or data but it is important to fix
the size of value, character. If in case, database designer will be setting the specific size
and then he will try to use limitless values. In this way, it will be increasing the errors
message (Komarov and et.al., 2020). The size of row is not appropriate completely.
Another way, it is validating the fundamental building block and used for conjunction
with other types of validation. It ensure the valid data.
 Null values: When creating the database table so that adding or recording the value in
each column but require to identify the suitable value. Primary and foreign are not
contained the null value because they are established the relationship between one or
more tables (Roy-Hubara and Sturm, 2020). Furthermore, it can be performed the null
value testing when there is no specific step are taken, it encountered for evaluating an
expression and then showing result or outcome (Komarov and et.al., 2020). It is possible
to exercise the more control on this process but require little care because it contain
expressions in context of primitives values. When testing the null value, to test whether
the column contain null value or not, prepend the string ‘Null’ sometimes, it yield a
Boolean value which is not completely true but contain the blank or floating point.
 Referential integrity (Primary/Foreign): it is mainly referred the relationship between one
or more tables. In this way, each table in database will be consist the primary value, also
appears within database table. When performing the test to valid the referential constraint
relationship between different tables (Roy-Hubara and Sturm, 2020). Sometimes, there
are no specific declarative constraint which are completely representing the column
value.
Firstly, it is important to enforce any other form of referential integrity constraint and
then enforce a set of validation procedures (Komarov and et.al., 2020). Afterwards, it can
easily detecting when referential integrity violation happens.
9

CONCLUSION
From above discussion, it concluded that database system is becoming more appropriate for
handling large amount of information or data in proper manner. Another way, it also identified
that can perform the different operations such as modify, retrieve, create and delete. In this
report, it has summarised about the structural query language which consider as important for
implementing the scripts for purpose of development. Each and every operations has been
depending on the script to generate specific result or output. Furthermore. It can be understand
the concept of data manipulation language that usually contain the different statement which
mainly used in the database table design, creation as well as modification. it can be determined
the transaction process within database to manage or organise data in proper manner.
10

REFERENCES
Book and Journals
Alaydrus, M.F. and et.al., 2020. Designing Web-based Database Applications in CV
Bahagia. E&ES. 426(1).p.012172.
Chiusano, M., 2020. IS 331-452: Database Design, Management and Applications.
Komarov, I. and et.al., 2020, April. Design of Tree-Like Database Structure for Solving Test
Modeling Tasks of Energy Equipment Training. In 2020 V International Conference on
Information Technologies in Engineering Education (Inforino) (pp. 1-4). IEEE.
Komarov, I. and et.al., 2020, April. Design of Tree-Like Database Structure for Solving Test
Modeling Tasks of Energy Equipment Training. In 2020 V International Conference on
Information Technologies in Engineering Education (Inforino) (pp. 1-4). IEEE.
Roy-Hubara, N. and Sturm, A., 2020. Design methods for the new database era: a systematic
literature review. Software and Systems Modeling. 19(2). pp.297-312.
11