Data Modeling and SQL: A Practical Guide (Database Systems, Fall 2023)

Desklib provides past papers and solved assignments for students. This assignment covers SQL, data manipulation, and database testing.

Table of Contents
Introduction................................................................................................................................2
Assignment No. 2.......................................................................................................................3
Part 1 - Structured Query Language.......................................................................................3
Part 2 – Data Manipulation....................................................................................................6
Part 3 – Database Testing.......................................................................................................7
Conclusion..................................................................................................................................9
References................................................................................................................................10
List of Figures
Figure 1: DDL (1)......................................................................................................................3
Figure 2: DDL (2)......................................................................................................................4
Figure 3: DDL (3)......................................................................................................................4
Figure 4: DDL(4).......................................................................................................................4
Figure 5: DCL (1).......................................................................................................................5
Figure 6: DCL (2).......................................................................................................................5
Figure 7: DCL (3).......................................................................................................................6
Figure 8: Query Number 1.........................................................................................................7
Figure 9: Query Number 2.........................................................................................................7
Figure 10: Query Number 3.......................................................................................................7
Figure 11: Query Number 4.......................................................................................................8
Figure 12: Query Number 5.......................................................................................................8
Figure 13: Query Number 6 (a)..................................................................................................8
Figure 14: Query Number 6 (b).................................................................................................9
Figure 15: Query Number 7 (a)..................................................................................................9
Figure 16: Query Number 7 (b).................................................................................................9
Figure 17: Query Number 7 (c)..................................................................................................9
Figure 18: Query Number 8.....................................................................................................10
Figure 19: Query Number 9.....................................................................................................10
Figure 20: Query Number 10...................................................................................................10
Figure 21: Constraint 1 (a).......................................................................................................11
Figure 22: Constraint 1 (b).......................................................................................................11
Figure 23: Constraint 2.............................................................................................................12
Figure 24: Constraint 3.............................................................................................................12
Figure 25: Constraint 4.............................................................................................................13

Introduction
The given assessment is based on Data Modelling and the SQL language. This assessment
consists of three parts. In part 1 Structured Query Language that is abbreviated as SQL is to
be explained with its commands and part 1 also contained sub-parts in which DCL and DDL
commands are to be explained with the suitable examples. In part 2 named Data
Manipulation; ten queries are to be performed based on the given ER diagram and the
database with given queries. Last and the third part is the Database Testing, in this part
Database Testing is to be explained and given constraints are needed to be defined with
suitable examples and the screenshots.

Assignment No. 2
Part 1 - Structured Query Language
SQL is extended as Structured Query Language often pronounced as sequel similar to Java
and C++ languages, SQL is Query Language. SQL queries are written and worked against
databases to store as well as retrieve the data (Sqlcourse.com, 2019). Data is mainly the
collection of all kind of information. SQL is further divided into three commands namely:
 Data Control Language abbreviated as DCL
 Data Definition Language abbreviated as DDL
 Data Manipulation Language abbreviated as DML
Task 1 is further divided into 2 sub-tasks:
1. Data Definition Language abbreviated as DDL
Create, Alter, Rename, Truncate and Drop comes under DDL commands (SQL Authority
with Pinal Dave, 2019). It is used for defining and modifying the data and its structure. Also,
it is used to build and modify the structure of the databases and the tables. Here, in sub-task
Create and Alter are being asked to explain with the suitable examples:
 Create Command –
It is used to create the objects in the databases as well also used to create the tables in the
databases. Create command is the first and the main command used in making the database as
well as starting any database creation (Dataquest, 2019). Let us know, begin with the Syntax.
Syntax:
Create table <name of the table>
{Name of the column datatype,
Name of the column datatype};
Here, the name of the table can be any name that can be given to the table to be created,
Name of the column is the name of the columns to be present in the created table and
datatype consists of the different type of values or the data that can be entered in the columns
created.
Example:
Creation of the table-

Figure 1: DDL (1)
Figure 2: DDL (2)
 Alter Command –
It is used to alter or modify the structure of the database as well as the tables that are already
created. Alter Command can also be used in adding the different columns or any other needs
in the already existing databases as well as the tables. It also helps in dropping the existing
column and changes the data type of the existing columns. Let us know to see the Syntax.
Syntax:
 Adding the new column in the existing table and database with the suitable datatype
required-
Alter Table <name of the table>
Add <Name of the column datatype>;
 Alter command used to change the older name of the column to the new column in the
table-
Alter Table <name of the table>
Change <old name of the column> <new column name>;
Example:
Changing the old column name –
Figure 3: DDL (3)

Figure 4: DDL(4)
2. Data Control Language abbreviated as DCL
Grant and Revoke are the commands that come under the DCL commands. DCL commands
are used to control the user access in the databases as well as the structure of the databases
because of the security issues. Permissions are given and taken away from the user by the
administrator who created the database. Grant and Revoke are the two commands asked to
explain with the proper syntax and the example:
 Grant Command –
It gives User access permissions to the database by the admin. The admin gives different
privileges to the different users to access a particular database. Select, Insert are some of the
privileges provided to the user in order to change the structure of the database or the tables
present in the database. Let us know to understand this command with the proper syntax and
the example.
Syntax:
Grant <name of the privilege>
On <Relation_name>
To <User_name>;
Example:
Figure 5: DCL (1)
Figure 6: DCL (2)
 Revoke Command –
It is used to cancel the previously given or granted permissions to the users. Revoke
command is used with the proper syntax in order to deny the provided access to the databases

or the structures of the database. To further understand this command, proper syntax with a
suitable example is being explained below.
Syntax:
Revoke <Privileged_list>
On <Relation_name>
From <user_name>;
Example:
Figure 7: DCL (3)

Part 2 – Data Manipulation
Queries:
1.
Figure 8: Query Number 1
2.
Figure 9: Query Number 2
3.
Figure 10: Query Number 3
4.

Figure 11: Query Number 4
5.
Figure 12: Query Number 5
6.
Figure 13: Query Number 6 (a)

Figure 14: Query Number 6 (b)
7.
Figure 15: Query Number 7 (a)
Figure 16: Query Number 7 (b)
Figure 17: Query Number 7 (c)
8.

Figure 18: Query Number 8
9.
Figure 19: Query Number 9
10.
Figure 20: Query Number 10

Part 3 – Database Testing
The Third Task of assessment 2 consists of 2 more sub-parts:
1. Database Testing is basically a process in which the behavior of the database is being
tested thoroughly with the reference of the front-end application that can be web/ desktop
application. It basically means that any changes made to the front-end would definitely reflect
the back-end part and the changes need to be there as well and vice-versa. Database Testing
mainly involves the few subjects that are:
 Database Performance
 Data Validity
 Data Integrity
 Testing of functions, procedures, and triggers
Why Database Testing is considered important?
Talking with the aspect of Software testing, Database Testing is considered to be very
important. As data is being collected from different sources, all the data gets mixed up and by
getting data from so many sources, definitely, there would be a source that could contain the
harmful data in it. Such harmful data would create the problems in the final created database,
in order to remove such threats from the database, Database testing is considered to be very
important (Sharma et al., 2019).
2. There are several constraints that are needed to be checked for creating a useful database:
 Field Size Validation –
The First constraint is the Field size Validation, in this part, each column in the table is
provided with the suitable datatype and size is defined with the datatype such as int(11),
varchar(45). If the size of the value entered in the column is more than the size defined
with the datatype then there would be an error. Let us understand it with the proper
example and screenshot of the example:
Figure 21: Constraint 1 (a)
Figure 22: Constraint 1 (b)
 Null Values –
The second constraint is the Null Values. In this part, if the particular column is defined
with the constraint Not Null and the value is not entered in that column then there would

be an error occurred, asking to enter the values in the vacant field of the columns. Let us
understand it with the proper example and screenshot of the example:
Figure 23: Constraint 2
 Check Constraints –
The third constraint is the Check Constraints. In this part, if any column is defined as the
Primary Key i.e. such column should be uniquely identified and in that column value gets
repeated then it would give the error of duplicate value is entered. Let us understand it
with the proper example and screenshot of the example:
Figure 24: Constraint 3
 Referential Integrity –
The fourth and the last constraint is Referential Integrity. In this part, if any column is
assigned as the foreign key as it is taking reference from the table in which similar
column is assigned as the Primary Key and the value in that column is updated without