Data Modelling & SQL Language : Assignment

Data Modelling & SQL Language: Part 2
1

Contents
INTRODUCTION...........................................................................................................................3
TASK 1............................................................................................................................................3
Structure Query Language...............................................................................................................3
Describe the two DDL statement within an appropriate example and syntax applicable in
DBMS..........................................................................................................................................3
Discuss about the Data control language statement within the appropriate example and syntax.
.....................................................................................................................................................4
TASK 2............................................................................................................................................6
Data Manipulation...........................................................................................................................6
TASK 3..........................................................................................................................................10
Discuss about the database testing.............................................................................................10
why database testing is an essential...........................................................................................10
Describe the testing template and screenshots of testing process on the employee database. . .11
CONCLUSION..............................................................................................................................14
REFERENCES..............................................................................................................................18
2

INTRODUCTION
Database management system is defined as a collection of data that enables it’s the user to
access the database, manipulate data and also representing in effective manner. It useful for
controlling and managing the overall processing of database system.
This report will discuss about the structure query language which help to design tables,
columns in the system. It can be used the two different type of SQL statement and contain
specific syntax for generating appropriate results. Furthermore, it will describe the database
testing and how it is important for data management.
TASK 1
Structure Query Language
Describe the two DDL statement within an appropriate example and syntax applicable in DBMS.
DDL stands for data definition language that contain the multiple SQL command that will
define the data schema (Egea and Dania, 2019). It is mainly deals with the proper explanation of
data schema to create table and modify the overall structure in the form of objects.
For Example-
 Create command useful for creating database tables and its objects.
 Alter is mainly applicable to alter the structure in the database system.
 Drop can delete the objects from its database system.
Alter command
3

Create command
Discuss about the Data control language statement within the appropriate example and syntax.
A data manipulation is based on the programming language that can use for modifying,
deleting and adding the data in the database system (GoliMalekabadi, Sargolzaei, Javan and
Akbari, 2016). It can be permitting the user for manipulate the information in DBMS which
includes insert the data into the tables, existing data and also modifying the records.
For Example-
 Select command can use to retrieve the records from DBMS table and perform the
different operations.
 Delete command can use to eliminate multiple record from DBMS table as per the
specified conditions.
 Update SQL command can be modified data in the different records and apply an
appropriate syntax.
4

Data control language
It is based on the programming language that can applicable in the database system to
easily access the information and store in proper manner. It is the component of SQL to perform
the different operations (Jukic, Vrbsky and Nestorov,
2016). It is privilege that may revoke and grant from the developer and implementing the
different operations by using DML and DDL. It may always involve different statement such as
delete, usage, select, update, insert and execute etc.
DCL command is mainly used to enforce the DBMS security in the different users in the
environment. There are two types of DCL SQL command: Revoke and Grant. The user is only
providing the privilege to another objects.
On the other hand, Grant is another command that must be used to give privilege for
access the objects in DBMS.
Grant command:
SQL GRANT command is basically used for access or privileges on the database objects
to the users.
Syntax
GRANT Privilege_name
On object_name
To {user_name / public / role name}
[With Grant option];
 Privilege name is used for providing the privilege granted to users where it can execute
all operations.
 Object_name is considered the database object such as stored, table, sequence and view
etc.
 User_name is a person those who are accessing operations.
 Public is mainly used for providing access rights of users.
For Example-
If Admin wanted to grant insert, update, select and update privileges on the database table
such as employee to user name James. Use Grant statement:
5

If admin will use ALL keywords that indicate that they wish to grant permission of select, insert,
update, delete and references. For example-
If admin wanted to grant only select access on employee table and users where they could have
grant privilege to public.
Revoke Command:
The Revoke command is basically used to remove user access privileges and rights in
database objects.
Syntax
Revoke Privilege_name
On object_name
From {user name/ public role name}
 Privilege name is used for providing the privilege granted to users where it can execute
all operations.
 Object_name is considered the database object such as stored, table, sequence and view
etc.
 User_name is a person those who are accessing operations.
 Public is mainly used for providing access rights of users.
For Example-
If admin wanted to revoke delete privileges on employee table from user name is
Anderson so that it can use command such as:
If revoke all permissions of users related select, insert, delete and references.
6

If it had granted select privilges to the public on empliotee and wanted to revoke these privileges.
TASK 2
Data Manipulation
1. List the SQL statement of all manager and their titles.
2. Show the salary of all employee and their department name
3.Show the hire data and birth data those who belongs to Human resource department.
7

4. Show all departments and its managers.
5. Show the list of HR employee who were hired after 1986
6. Show the Statement to increase the employee salary up to 2% and assume the employee just
phone in with his/her name.
7. SQL statement to delete employee’s record who belongs to marketing department and name
start with A
8

8. Create database view to list full names of all departments manager and their salaries.
9. List the departments and their managers who were hired between 1980 and 1990.
10. Show the SQL statement to increase salaries of all departments manager up to 10% who are
Woking since 1990.
9

TASK 3
Discuss about the database testing
It is one the most common part of database system which requires for testing the overall
process in effective manner. It is also expertise in checking the tables and columns for writing
the procedure (Kolev and etal., 2016). Generally, testing may occur in the form of layered
method that consists of different such as User interface, enterprises layer and data access layer
etc. It can be divided into different groups:
 Testing of procedure
 Data validity testing
 Performance regarding data base
 Data integrity testing
It is creating the test plan and which requires to check the overall functionality of system. It is
also performed the action in the application. It involves save, deletion and addition etc.
Currently, database testing is very complex operations that should be required to expertise in the
particular area or field (Zaharia and et.al., 2016). Developer can use the variety of components
that testing the entire functionality.
Transactions: It is needed for testing the transactions which important to make sure
satisfied the Acid properties.
Stored Procedures: it is stored the procedures and also similar to the user defined
functions. It is used for calling the process to execute SQL statement. In this way, it is generating
the correct output and result.
Database Schema: It always make sure that data can be organized inside the database
engine. It can be stored and collected the information in proper manner.
why database testing is an essential.
The database testing is an essential for the software because it can retrieve and collect the
data in the applications. It has required to use SQL statement that can perform the operations and
functions (Storey and Song, 2017). It should be needed to understand about the knowledge of
SQL because without having idea, it is not possible to handle the task. Testing can be done to
maintain the quality of applications so that they were carried out the testing. As started the
testing, before tester must list down the queries at one place through the development team or
group. Test process can see each query if the data is being completely updated, modified and
10

deleted according to specifications. Database is the most common aspects of business application
such as banking software and other app. In this way, database can develop to record large
amount of data and also taking place the transaction by using accounts. Tester must understand
the DBMS tables that will use by going through the design.
Describe the testing template and screenshots of testing process on the employee database
Testing process is finding the defects, errors and bugs in the software system. A tester must
be performed the end user task to verify or validate all the working features of system. It also
executes the test cases.
Field size validation
If database develop can choose the validation choice and also ensure that data must be
entered in the proper field. It is selecting the data types and display message if they can enter the
correct data values in it.
Field size validation check the length of data constraints, in employee table it contain only
10 bytes values.
Figure :1
Emp_id is defined as the table that data cannot be longer than 10 bytes.
Figure : 2
11

Results:
Figure : 3
Null values
Null is a type of specific term that can represent the missing values in the table. It appears
the field to blank so that it is important to identify the null value that different to other value like
Zero. Sometimes, this type of problem arises in the database when the place holder doesn’t use to
show the missing values. In this way, it is used the null statement for solving the problem.
Generte Error:
12

Check constraints
It is applicable in the database to limit the wide range of values and also placed in the
column. If database developer may define the proper check constraint on particular column or
row. It allows on the certain place to test the overall limit of DBMS table.
Output:
If it will change the value in same queries that can show the errors message of duplicate primary
key.
Same Queries
13

Output
Referential integrity
It is based on the relational DBMS conceptual terms that states to create consistent
relationship between two tables. It must be required to use the primary key which referred as a
foreign key into another table. It is important aspects of keep maintain the security and privacy
because they are introduced bugs, errors and defect in the database.
For Example-
It shows error when cannot delete or update in primary key and use same values whether they are
developing referential integrity between two database tables.
14

Result:
15

CONCLUSION
As per discussion, it concluded that Database management system is defined as a collection
of data that enables it’s the user to access the database, manipulate data and also representing in
effective manner. It useful for controlling and managing the overall processing of database
system.
This report is discussed about the structure query language which help to design tables,
columns in the system. It can be used the two different type of SQL statement and contain
specific syntax for generating appropriate results. Furthermore, it also used the database testing
and how it is important for data management.
16

Screenshots
Insertion
17

Database tables:
18

19

REFERENCES
Book and Journals
Egea, M. and Dania, C., 2019. SQL-PL4OCL: an automatic code generator from OCL to SQL
procedural language. Software & Systems Modeling. 18(1). pp.769-791.
Goli-Malekabadi, Z., Sargolzaei-Javan, M. and Akbari, M.K., 2016. An effective model for store
and retrieve big health data in cloud computing. Computer methods and programs in
biomedicine. 132. pp.75-82.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and
data warehouses. Prospect Press.
Kolev, B. and et.al., 2016. CloudMdsQL: querying heterogeneous cloud data stores with a
common language. Distributed and parallel databases. 34(4). pp.463-503.
Storey, V.C. and Song, I.Y., 2017. Big data technologies and management: What conceptual
modeling can do. Data & Knowledge Engineering. 108. pp.50-67.
Zaharia, M. and et.al., 2016. Apache spark: a unified engine for big data
processing. Communications of the ACM. 59(11). pp.56-65.
20

21