Advantages And Functionality Of Database Management Systems And SQL DML Statements

What is a DBMS?

DBMS can be define as an application that enables a user to create, define and maintain database while providing security of the data and way to maintain multiple access control and security of the data. Before the DBMS introduced, flat file systems were used to store data in single tables which was capable of holding limited amounts of manageable data [1]. There were several drawbacks of the flat file system as this tables only could be accessed with a single file at a time. There are several advantages of the database management systems including data redundancy, integrity, consistency and authorization.

1. Advantages of the DBMS

Controlling Redundancy:  In every file system, redundancy is a critical issue as every application has their own private files which cannot be shared among multiple applications. The DBMS works as a centralize database, so most of the redundancy can be eliminated [2]. However, for some technical reasons multiple copies of same data is generated. In a database system redundancy can be controlled.

Data Integrity:  Data integrity implies that every data in database is always accurate. DBMS provides capabilities for enforcing and defining the constraints. For instance, if a college database having science, history and Geography classes. Then Philosophy class cannot be entered as this is an invalid data entry.   

Consistency: Inconsistency occurs when same data is duplicated and one site has been modified. The two different entries will produce inconsistency. If redundancy is removed, the inconsistency data is also removed as well.

Authorization: when multiple user access the same database, authorization issues can appear as everybody will be able to access every data. DBMS provides authentication through a username and password. It also offers security which is used by the DBA to create accounts and to specify account restrictions.

1. Function of DBMS

Data Dictionary Management: Data dictionary is used by the DBMS where data elements and their relations are stored. DBMS uses this function to show data components and their relations. This function also eliminates data and structural dependency.

Data storage Management: This function is utilized for the storage of data and related data entry forms, procedural code, report definitions and structures that can handle both picture and video formats [3].

Data transformation and presentation: This particular function is used to transform data entries into desired data structure. Physical and Logical data formats can also be differentiated by this function.

Security management: This function allows specific security schema as it determines the specific users that are allowed to access the database generally through username and password.

1. Data Manipulation Language statements are used for managing data in database. For storing data into a database this statement can be used.

Advantages of DBMS

INSERT INTO table_name VALUES (data1 , data2, ….);

Suppose a table named Department with following fields:

INSERT INTO Department VALUES (‘COMPUER SCIENCE’, ‘CS’, ‘G. ROW’, 50);

The DML statement insert values into the called database, in this case Department. The values are added continuously as defined. The values are separated through comma (,) and strings are identified with single quota (‘’) [4]. We can also add specific values at specific columns as following:

INSERT INTO Department(D_id, D_head) VALUES ( ‘ME’, ‘M.Lancer ’);

1. For Update any value on a specific table following statement can be used:

UPDATE table_name SET column_name = new_value WHERE some_condition;

The UPDATE comment is used to update any certain value from selected table [5]. For instance, if the head of department altered with K. Lin and the faculty number with 60 the following statement will update the value.

UPDATE Department SET D_head = ‘K. Lin’, Faculty_count = 60 WHERE D_id= ‘CS’;

1. SELECT * FROM table_name;

This statement is used to view certain data entries in a specific table.

SELECT * FROM Department;

This comment will showcase the table as followed:

Parent Table: Student

Child Table: class

1. Delete a record from parent table: ON DELETE RESTRICT
2. While deleting any row from the parent table the RESTRICT function does not allow to delete such row which is directly associated with the other table’s foreign key. The RESTRICT, returns an error which states “delete error : ……..”.

Foreign key Relation

Step 1: Error while trying to delete a column from student table

Result 1: The Class table is also unaffected by the action

1. CASCADE: while trying to delete a row from the parent table and specifying ON CASCADE DELETE, the child table is also effected as well. The rows will be deleted along with the rows on the child table where the deleted row values are directly associated.

Step 1: Set the foreign key relations and specify ON DELETE CASCADE

Step 2: Deleted the second row from the student table which contains the value (student_id=2)

Result 1: the child table is been effected as the row associated with the student id = 2 is eliminated.

Table Student in sql

Table: Class

1. Restrict

Result 1: showing error while deleting a row from student table

1. Cascade

Step 1: specify ON DELETE CASCADE

Result 1: the second row has been deleted

Result 2: the updated class table

RESTful is mainly used to create scalable, maintainable and lightweight in nature. The underlying protocol for REST is HTTP [6]. REST allow to access resources which lie in a particular environment. For instance, URL of a web application is https://www.deakin.edu.au/. Now in order to access the locations of the key areas, user can issue commend https://www.deakin.edu.au/locations. The commend tells web server to send location related data. The requests are send through verb. The browser issue a GET verb to instruct the endpoint it wants to get data. The GET verb requests to retrieve resources information only [7]. GET request does not change the state of the resource as it is considered as a safe method.

Reference

Horikawa, “Latch-free data structures for DBMS: design, implementation, and evaluation” In Proceedings of the 2013 ACM SIGMOD International Conference on Management of Data, 2013, pp. 409-420.

Coronel, and S. Morris, ” Database systems: design, implementation, & management. Cengage Learning”, 2016.

K. Nidzwetzki, and R.H. Güting, “Distributed secondo: an extensible and scalable database management system”, Distributed and Parallel Databases, 2017, 35(3-4), pp.197-248.

Schwichtenberg, “Reading and Modifying Data with SQL, Stored Procedures, and Table-Valued Functions” In Modern Data Access with Entity Framework Core,2018, pp. 305-315.

O. Babatunde, E.A. Adedokun, and A. Ore-ofe, “Efficient Scheduling System for a Company’s Dispatch Trucks”, small, 2017, 173(7).

M. Kulkarni, and M.S. Takalikar, “Analysis of REST API Implementation”, 2018.

Pautasso, “RESTful web services: principles, patterns, emerging technologies” In Web Services Foundations, 2014, pp. 31-51.

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order