Page 73 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes student(st_id, coursecode, marks). In the avgmarks table changing the class average
marks for coursecode “MA 03” to 50 from a calculated value of 40, cannot be accounted
for a single tuple in the Student base table, as the average marks are computed from
the marks of all the Student tuples for that coursecode. Thus, this update will be
rejected.
2. The views in SQL that are defined using joins are normally NOT updatable in general.
3. WITH CHECK OPTION clause of SQL checks the updatability of data from views, therefore,
must be used with views through which you want to update.
Views and Security

Views are useful for security of data. A view allows a user to use the data that is available
through the view; thus, the hidden data is not made accessible. Access privileges can be given on
views. Let us explain this with the help of an example.
Consider the view that we have created for teacher-STUDENT-PERFORMANCE. We can grant
privileges to the teacher whose name is ‘ABC’ as:

GRANT SELECT, INSERT, DELETE ON STUDENT-PERFORMANCE TO ABC WITH GRANT
OPTION;

Notes The teacher ABC has been given the rights to query, insert and delete the records on
the given view. Please also note s/he is authorised to grant these access rights (WITH
GRANT OPTION) to any data entry user so that s/he may enter data on his/her behalf. The
access rights can be revoked using the REVOKE statement as:
REVOKE ALL ON STUDENT-PERFORMANCE FROM ABC;

Task Create a new table with five column and create view on this table.

4.5 Joined Relations

SQL joins are used to query data from two or more tables, based on a relationship between
certain columns in these tables. A JOIN is a means for combining fields from two tables by using
values common to each.
SQL is relational database query language and as such, one of its most important features is its
ability to retrieve information from several different related tables. In relational database terms,
this process is called a join. The tables to be joined are named in the From clause of the Select
with each table name separated by a comma. The relationships between the tables in a join are
defined by the predicate in the Where clause.
SQL specifies four types of JOINs:
1. INNER
2. OUTER
3. LEFT
4. RIGHT

66 LOVELY PROFESSIONAL UNIVERSITY