SIT103 Data And Information Management12

5 Pages / 1,218 Words Published On: 14-01-2020

Question:

Task 1

Find, read, cite and reference several sources for each of the following: DBMS advantages, and DBMS functionality. In your own words:

Briefly describe four advantages of a DBMS.
Briefly describe four functions of a DBMS.

Task 2

Find, read, cite and reference several sources for each of the following three SQL statements. In your own words:

Briefly describe an SQL DML statement for storing new data into a table.
Briefly describe an SQL DML statement for changing existing data in a table.
c. Briefly describe an SQL DML statement for retrieving existing data from a table

Task 3

(a) ON DELETE RESTRICT

i.When deleting a record from a parent table, in your own words, briefly describe the consequences of specifying ON DELETE RESTRICT on a foreign key in a child table. For example, describe what happens when trying to delete the record containing Penfolds, or the one containing Barrabool Hills.
ii. When deleting a record from a parent table, in your own words, briefly describe the consequences of not specifying ON DELETE RESTRICT.

(b) ON DELETE CASCADE

i.When deleting a record from a parent table, in your own words, briefly describe the consequences of specifying ON DELETE CASCADE on a foreign key in a child table. For example, describe what happens when trying to delete the record containing Penfolds, or the one containing Barrabool Hills.
ii. When deleting a record from a parent table, in your own words, briefly describe the consequences of not specifying ON DELETE CASCADE.

(c) ON UPDATE RESTRICT

i.When updating the primary key of a record in a parent table, in your own words, briefly describe the consequences of specifying ON UPDATE RESTRICT on a foreign key in a child table. For example, describe what happens when trying to update the primary key of the record containing Penfolds, or the one containing Barrabool Hills.
ii. When updating the primary key of a record in a parent table, in your own words, briefly describe the consequences of not specifying ON UPDATE RESTRICT.

(d) ON UPDATE CASCADE

i.When updating the primary key of a record in a parent table, in your own words, briefly describe the consequences of specifying ON UPDATE CASCADE on a foreign key in a child table. For example, describe what happens when trying to update the primary key of the record containing Penfolds, or the one containing Barrabool Hills.
ii. When updating the primary key of a record in a parent table, in your own words, briefly describe the consequences of not specifying ON UPDATE CASCADE.

Answer:

TASK 1:

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.

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.

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.

TASK 2

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

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

Example,

Suppose a table named Department with following fields:

D_name

D_id

D_head

Faculty_count

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

D_name	D_id	D_head	Faculty_count
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 ’);

D_name	D_id	D_head	Faculty_count
COMPUER SCIENCE	CS	G. ROW	50
	ME	M.Lancer

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’;

D_name	D_id	D_head	Faculty_count
COMPUER SCIENCE	CS	K. Lin	60
	ME	M.Lancer

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:

D_name	D_id	D_head	Faculty_count
COMPUER SCIENCE	CS	K. Lin	60
	ME	M.Lancer

TASK 3:

Parent Table: Student

Child Table: class

MARIADB

Delete a record from parent table: ON DELETE RESTRICT
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

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.

SQL:

Table Student in sql

Table: Class

Restrict

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

Cascade

Step 1: specify ON DELETE CASCADE

Result 1: the second row has been deleted

Result 2: the updated class table

TASK 4:

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.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). SIT103 Data And Information Management12. Retrieved from https://myassignmenthelp.com/free-samples/sit103-data-and-information-management12.

"SIT103 Data And Information Management12." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/sit103-data-and-information-management12.

My Assignment Help (2020) SIT103 Data And Information Management12 [Online]. Available from: https://myassignmenthelp.com/free-samples/sit103-data-and-information-management12
[Accessed 07 June 2023].

My Assignment Help. 'SIT103 Data And Information Management12' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/sit103-data-and-information-management12> accessed 07 June 2023.

My Assignment Help. SIT103 Data And Information Management12 [Internet]. My Assignment Help. 2020 [cited 07 June 2023]. Available from: https://myassignmenthelp.com/free-samples/sit103-data-and-information-management12.