Data Modelling and SQL Language: A Comprehensive Report

Desklib provides solved assignments and past papers to help students succeed.

Contents
Introduction................................................................................................................................2
Part 1 – SQL [Structured Query Language]...........................................................................3
Part 2 – Manipulation of Data................................................................................................6
Part 3- Testing of Database..................................................................................................10
Conclusion................................................................................................................................12
References................................................................................................................................13
List Of Figures
Figure 1: Update command........................................................................................................3
Figure 2: Delete Command........................................................................................................4
Figure 3: Create User Command in DCL..................................................................................4
Figure 4: Grant Command in DCL............................................................................................5
Figure 5: Revoke Command in DCL.........................................................................................5
Figure 6: Q1...............................................................................................................................6
Figure 7: Q2...............................................................................................................................6
Figure 8: Q3...............................................................................................................................6
Figure 9: Q4...............................................................................................................................7
Figure 10: Q5.............................................................................................................................7
Figure 11: Q6 (a)........................................................................................................................7
Figure 12: Q6 (b)........................................................................................................................8
Figure 13: Q7.............................................................................................................................8
Figure 14: Q8.............................................................................................................................8
Figure 15: Q9.............................................................................................................................8
Figure 16: Q10...........................................................................................................................9
Figure 17: Constraint 1.............................................................................................................10
Figure 18: Constraint 2.............................................................................................................11
Figure 19: Constraint 3.............................................................................................................11
Figure 20: Constraint 4.............................................................................................................11

Introduction
The entire report is based on the assignment named Data Modelling and SQL language. This
assignment contains three parts and each of the parts contains the proper explanation related
to some of the other parts of the database. The first part explains about the various commands
in the database along with the different sub-parts, the next part of the assignment contains the
proper implementation of the various queries performed on PHPMyAdmin followed by the
last part where the database testing is properly explained and shown with the examples of the
various constraints.

Part 1 – SQL [Structured Query Language]
The database is considered to be the most important aspect for each and every organization.
The time has gone when the entire data is used to store in the form of the files and it was
really very difficult to extract the appropriate information. As technology increased along
with the various software developed, this issue is also resolved as the introduction of the
Database Management System has helped in various different ways (An, 2018). Structure
Query Language also is known as SQL is the Query language that provided various
commands for creating, modifying the tables as well as helps in extracting out the particular
data. There are different commands present in SQL that are:
 DML Commands expressed as Data Manipulation Language
 DCL Commands expressed as Data Control Language
 DDL Commands Data Definition Language
1. DML Commands:
DML Commands also abbreviated as Data Manipulation Language contains the various
subcommands that are Update as well as the Delete command that helps in deleting the
inappropriate data when not required and update the data if something new has to be added
into the database.
Update Command-
Syntax-
UPDATE table_name
SET column1 = value1, column2 = value2...
WHERE condition;
Example-
Figure 1: Update command
Delete Command-
Syntax-

DELETE FROM table_name
WHERE condition;
Example-
Figure 2: Delete Command
2. DCL Commands:
DCL commands are also expressed as Data Control Language also contains the various sub-
commands that help in having the proper control onto the database as Grant and Revoke are
the two sub-commands that helps in providing access to the user as well helps in taking back
the requests from the user respectively (Armbrust et al., 2015). Both the commands are used
below as:
Grant Command-
Syntax-
 Creating user-
CREATE USER 'newuser'@'localhost'
IDENTIFIED BY 'password';
 Grant Command
GRANT type_of_permission
ON database_name.table_name
TO ‘username’@'localhost’;
Example-

Figure 3: Create User Command in DCL
Figure 4: Grant Command in DCL
Revoke Command-
Syntax-
REVOKE type_of_permission
ON database_name.table_name
FROM ‘username’@‘localhost’;
Example -
Figure 5: Revoke Command in DCL

Part 2 – Manipulation of Data
Q1
Figure 6: Q1
Q2
Figure 7: Q2
Q3
Figure 8: Q3

Q4
Figure 9: Q4
Q5
Figure 10: Q5
Q6
Figure 11: Q6 (a)

Figure 12: Q6 (b)
Q7
Figure 13: Q7
Q8
Figure 14: Q8
Q9
Figure 15: Q9
Q10

Figure 16: Q10

Part 3- Testing of Database
1. Database testing
Database testing is considered to be the most required as well as the most essential part after
the creation of the Database (McMinn et al., 2017). The entire database is needed to be tested
at different phases that would definitely help in creating out the best database. For testing the
database, a proper tester is definitely required that would help in checking out the entities,
along with the queries as well as looking out for the valid or appropriate output. The entire
process of the database testing is being performed on the web application as well as on
various desktop applications. The main purpose of the testing of the database is to have the
proper checking of all the structures, entities as well as the triggers that play a really
important part in the creation of the database. It is mainly being used for the optimization of
the queries in order to check the various responses from the databases. The purpose of doing
the testing is removing the various errors or the flaws of the present database and helps in
making the entire database efficient as well as effective.
Why is database testing considered as important?
Database testing is considered to be really very important because it helps in creating the
most effective as well as the efficient database. As the database is created by using the large
collection of the data from different sources and due to which a large number of errors can
occur, so the testing of the database is to be used. The rectifications of the errors are mainly
being done with the help of database testing.
2. Various Constraints are:
Field-size Validation- This is the first type of the constraint that mainly states that with each
of the column a data type is associated and each datatype has fixed limit that decides how
much data is to be entered. If the limit is crossed then, this causes the field-size validation.
Figure 17: Constraint 1

Null Values- The next constraint is the Null Values constraint that states that if any of the
columns, during the creation of the table contains the not null constraint then in case the null
values are entered, the null values constraint is caused.
Figure 18: Constraint 2
Check Constraints- This is the next constraint as this states that if any of the columns are
assigned or considered as the Primary key then if any of the duplicate entry is made in that
column then it causes the check constraint and provides the error.
Figure 19: Constraint 3
Referential Integrity- This is the last constraint where if any of the columns are considered
as the Primary key and then the similarly named column is present in the other table then that
column would be considered as the Foreign key and no changes could be made in that
column unless the changes are made in the primary key table.
Figure 20: Constraint 4

Conclusion
The above-made report is mainly created on the topic named as Data Modelling and SQL.
This report contains the three different tasks where the first task properly explains about the
Structured Query Language and various commands present under SQL. The next part of the
task is the implementation of the queries on PHPMyAdmin software. The last task explains
about the database testing and also explains the importance of the database testing. At last
various constraints are also mentioned along with the examples.