Skip to content

Lesson 18: Views, ODBC and Database Administration

Virtual tables, application access, users, roles and privileges

About this lesson

Course: Database Management Systems

Unit 4: Structured Query Language · 12 Lecture Hours

Lesson duration: 2 lectures

Reading: Elmasri & Navathe, Ch. 7.3, 30.1 · Krogh, MySQL Connector/Python Revealed

Learning Outcomes

  1. Create, query, update and drop views, and state their four purposes.
  2. Determine whether a view is updatable, and use WITH CHECK OPTION.
  3. Distinguish virtual from materialised views.
  4. Explain ODBC/JDBC and connect to a database from Python.
  5. Write the basic DBA commands: CREATE USER, CREATE ROLE, GRANT, REVOKE, CREATE INDEX.

18.1 Views

View

A virtual table derived from one or more base tables (or other views) by a stored query. A standard view stores no data — only its definition is kept in the catalog, and the query is executed whenever the view is referenced.

A view is precisely the external schema of the three-schema architecture (Unit 1, Lesson 3).

Creating and using views

CREATE VIEW StudentCourses AS
SELECT s.Rollno, s.Name, c.Cname, c.Coursetype, a.Dateofadmission
FROM   STUDENT s
JOIN   ADMISSION a ON s.Rollno = a.Rollno
JOIN   COURSE    c ON a.SID    = c.SID;

-- Query it exactly like a table
SELECT Name, Cname FROM StudentCourses WHERE Coursetype = 'Parttime';

Course Q21 — a view tracking course names with the total number of students enrolled:

CREATE VIEW CourseEnrolment AS
SELECT   c.SID, c.Cname, c.TotalSeats,
         COUNT(a.Rollno)                AS TotalEnrolled,
         c.TotalSeats - COUNT(a.Rollno) AS Vacant
FROM     COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Cname, c.TotalSeats;

SELECT * FROM CourseEnrolment ORDER BY TotalEnrolled DESC;

The LEFT JOIN and COUNT(a.Rollno) are both required so that a course with no students shows 0, not disappearing (Lesson 4).

Managing views

CREATE OR REPLACE VIEW CourseEnrolment AS SELECT ...;   -- redefine
ALTER VIEW CourseEnrolment AS SELECT ...;               -- MySQL
DROP VIEW IF EXISTS CourseEnrolment;                    -- remove definition only
SHOW CREATE VIEW CourseEnrolment;                       -- inspect

Dropping a view removes only its definition; the base tables are untouched.

The four purposes of views

Why views exist

1. Simplification. A five-table join with grouping becomes SELECT * FROM CourseEnrolment. Complexity is written once and reused.

2. Security. Grant access to the view and withhold it from the base table; the user then sees only the rows and columns the view exposes.

CREATE VIEW PublicStudent AS SELECT Rollno, Name FROM STUDENT;
GRANT SELECT ON college.PublicStudent TO 'clerk'@'localhost';
-- The clerk can never see Dateofbirth or Mobile.

3. Logical data independence. When a base table is decomposed, a view with the old name and columns preserves the old interface for existing applications (Unit 1, Lesson 3).

4. Presentation. Rename columns, compute derived values, apply formatting — each user group gets the shape it wants over one consistent base.

Updatable views

When can you INSERT, UPDATE or DELETE through a view?

A view is updatable only if the DBMS can unambiguously map each change to exactly one row of exactly one base table. A view is generally not updatable if it contains any of:

  • DISTINCT
  • GROUP BY or HAVING
  • an aggregate function (COUNT, SUM, ...)
  • UNION, INTERSECT, EXCEPT
  • a join across several tables (restricted; some systems allow updates affecting one table only)
  • a subquery referring to the same table in the SELECT list

CourseEnrolment is not updatable — it aggregates. There is no single base row corresponding to TotalEnrolled = 2, and no meaning to setting it to 3.

PublicStudent is updatable — a simple projection of one table, so each view row corresponds to exactly one STUDENT row. Note, however, that an INSERT through it supplies no Dateofbirth, so the omitted column must be nullable or have a default.

WITH CHECK OPTION

CREATE VIEW FulltimeCourses AS
SELECT * FROM COURSE WHERE Coursetype = 'Fulltime'
WITH CHECK OPTION;

-- Rejected: the new row would not satisfy the view's WHERE clause
INSERT INTO FulltimeCourses VALUES ('DP01','Diploma',30,2,'Parttime','X');

Without WITH CHECK OPTION the insert succeeds, but the new row immediately vanishes from the view — it is in the base table yet outside the view's condition. WITH CHECK OPTION forbids inserts and updates that would push a row out of the view.

Virtual vs materialised views

Virtual view (standard) Materialised view
Stores data No — only the definition Yes — results are stored
Freshness Always current Stale until refreshed
Query cost Re-executes each time Fast — a simple table read
Storage None Full result size
Support All systems Oracle, PostgreSQL; MySQL has no native support

A materialised view is controlled redundancy (Unit 1, Lesson 1) — deliberate duplication that the DBMS keeps synchronised. In MySQL it is simulated with a real table refreshed by a scheduled event or triggers.

18.2 ODBC, JDBC and Application Connectivity

Call-Level Interfaces

ODBC (Open Database Connectivity) — a standard C-based API letting an application talk to any DBMS through a driver. The application is written once against the ODBC API; swapping the driver swaps the database.

JDBC (Java Database Connectivity) — the same idea for Java.

Native connectors — language-specific libraries such as MySQL Connector/Python, psycopg2, or PHP's PDO. Faster and more idiomatic than ODBC, but tied to one DBMS.

   APPLICATION  (Python / Java / C)
        |
        |  standard API calls
        v
   +----------------------+
   |  ODBC / JDBC Driver  |     <-- swap this to change DBMS
   |       Manager        |
   +----+------+-----+----+
        |      |     |
    +---v--+ +-v---+ +v-----+
    |MySQL | |Oracle| |Postgres|   driver per DBMS
    |driver| |driver| | driver |
    +---+--+ +--+--+ +---+----+
        |       |        |
     [MySQL] [Oracle] [PostgreSQL]

   The application code does not change when the database does.

Figure 6.1 — ODBC insulates the application from the DBMS.

The five steps of database programming

  1. Connect — establish a session, supplying host, database, user and password.
  2. Create a cursor — the object that executes statements and holds results.
  3. Execute — run SQL, with parameters where values are involved.
  4. Fetch — retrieve result rows.
  5. Close — release the cursor and connection; commit first if data changed.

MySQL Connector/Python

import mysql.connector
from mysql.connector import Error

try:
    conn = mysql.connector.connect(
        host='localhost', database='college',
        user='appuser', password='secret')

    cur = conn.cursor()

    # --- SELECT with a bound parameter -------------------------------
    sql = """SELECT s.Name, c.Cname
             FROM   STUDENT s
             JOIN   ADMISSION a ON s.Rollno = a.Rollno
             JOIN   COURSE    c ON a.SID    = c.SID
             WHERE  c.Coursetype = %s"""
    cur.execute(sql, ('Parttime',))          # note the tuple

    for name, cname in cur.fetchall():
        print(f"{name:25} {cname}")

    # --- INSERT, then COMMIT ------------------------------------------
    cur.execute("INSERT INTO STUDENT (Rollno, Name, Dateofbirth) VALUES (%s,%s,%s)",
                ('X4001', 'Neha Joshi', '2003-06-21'))
    conn.commit()                            # nothing is permanent until this
    print(cur.rowcount, "row inserted")

except Error as e:
    print("Database error:", e)
    if conn.is_connected():
        conn.rollback()                      # undo on failure
finally:
    if 'cur'  in dir(): cur.close()
    if conn.is_connected(): conn.close()

Never build SQL by string concatenation

# CATASTROPHIC -- SQL injection
cur.execute("SELECT * FROM STUDENT WHERE Rollno = '" + user_input + "'")

If the user types X2019'; DROP TABLE STUDENT; --, the concatenated string becomes two statements and the table is destroyed.

# SAFE -- parameterised
cur.execute("SELECT * FROM STUDENT WHERE Rollno = %s", (user_input,))

With a bound parameter the value is sent to the server separately from the statement text, so it can never be parsed as SQL. It is a data value, whatever characters it contains. This is not merely good style — it is the single most important security practice in database programming.

Useful cursor methods: fetchone(), fetchall(), fetchmany(n), executemany(sql, list_of_tuples) for bulk inserts, and cursor(dictionary=True) to receive rows as dictionaries.

18.3 Database Administration Commands

This section covers course practical part II: create user, create role, grant privileges to a role, revoke privileges from a role, create index.

Users

CREATE USER 'clerk'@'localhost'  IDENTIFIED BY 'StrongPass#1';
CREATE USER 'analyst'@'%'        IDENTIFIED BY 'AnotherPass#2';

ALTER USER 'clerk'@'localhost' IDENTIFIED BY 'NewPass#3';
DROP USER IF EXISTS 'clerk'@'localhost';

SELECT User, Host FROM mysql.user;

A MySQL account is 'username'@'host': 'clerk'@'localhost' may connect only from the server machine, 'analyst'@'%' from anywhere. Restrict the host — it is a second factor of access control.

Privileges

Privilege Permits
SELECT Reading
INSERT, UPDATE, DELETE Modifying data
CREATE, ALTER, DROP Modifying the schema
INDEX Creating and dropping indexes
CREATE VIEW, SHOW VIEW Managing views
EXECUTE Running stored procedures
GRANT OPTION Passing privileges on to others
ALL PRIVILEGES Everything

GRANT

-- Whole database
GRANT SELECT, INSERT, UPDATE ON college.* TO 'clerk'@'localhost';

-- One table
GRANT SELECT ON college.COURSE TO 'analyst'@'%';

-- SPECIFIC COLUMNS -- the finest granularity, and the point of DBMS security
GRANT SELECT (Rollno, Name) ON college.STUDENT TO 'clerk'@'localhost';

-- On a view, to expose a filtered slice
GRANT SELECT ON college.CourseEnrolment TO 'analyst'@'%';

-- With the power to re-grant
GRANT SELECT ON college.* TO 'hod'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;
SHOW GRANTS FOR 'clerk'@'localhost';

Column-level grants are what a file system cannot do (Unit 1, Lesson 1): the clerk reads a student's name but never their date of birth.

Roles

Role

A named collection of privileges. Privileges are granted to the role once, and the role is granted to users. Adding a new clerk becomes one statement instead of twenty, and changing what clerks may do updates every clerk at once.

CREATE ROLE 'reader', 'data_entry', 'course_admin';

-- Grant privileges TO the role
GRANT SELECT                         ON college.* TO 'reader';
GRANT SELECT, INSERT, UPDATE         ON college.* TO 'data_entry';
GRANT ALL PRIVILEGES                 ON college.* TO 'course_admin';

-- Grant the role TO users
GRANT 'reader'     TO 'analyst'@'%';
GRANT 'data_entry' TO 'clerk'@'localhost';

-- Activate (MySQL roles must be activated)
SET DEFAULT ROLE ALL TO 'clerk'@'localhost';

SHOW GRANTS FOR 'clerk'@'localhost' USING 'data_entry';

REVOKE

REVOKE INSERT, UPDATE ON college.* FROM 'clerk'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hod'@'localhost';

REVOKE UPDATE ON college.* FROM 'data_entry';   -- from a role: affects every member
REVOKE 'data_entry' FROM 'clerk'@'localhost';   -- take the role away from one user

DROP ROLE IF EXISTS 'data_entry';

Revoking from a role instantly affects every user holding it — the reason roles are preferred to per-user grants.

The principle of least privilege

Grant each account the minimum privileges needed for its job, and no more.

An application that only reads reports needs SELECT alone. A web front end needs SELECT, INSERT, UPDATE on specific tables — never DROP, and never on mysql.*. If the application account cannot drop tables, an SQL-injection flaw cannot drop tables either. Security is layered: parameterised queries and restricted privileges.

Indexes

Index

An auxiliary access structure that speeds up retrieval on the indexed column(s), at the cost of extra storage and slower writes. Creating one is an internal-level change and is invisible to queries — physical data independence (Unit 1, Lesson 3). The structures themselves are Unit 6.

CREATE INDEX idx_student_name  ON STUDENT(Name);
CREATE INDEX idx_course_type   ON COURSE(Coursetype);
CREATE INDEX idx_adm_sid_date  ON ADMISSION(SID, Dateofadmission);  -- composite
CREATE UNIQUE INDEX idx_course_cname ON COURSE(Cname);

SHOW INDEX FROM STUDENT;
DROP INDEX idx_student_name ON STUDENT;

EXPLAIN SELECT * FROM STUDENT WHERE Name = 'Ankit Sharma';   -- did it get used?

When an index helps, and when it hurts

Index a column that is frequently in WHERE, used as a join key, frequently in ORDER BY/GROUP BY, or highly selective (many distinct values).

Do not index a column with very few distinct values (a two-valued Coursetype on a small table — a scan is cheaper), a table that is tiny, or a column that is rarely queried.

The cost: every INSERT, UPDATE and DELETE must also maintain every index on the table. A table with eight indexes has slow writes. Indexes also consume storage.

Composite index column order matters. An index on (SID, Dateofadmission) serves WHERE SID=... and WHERE SID=... AND Dateofadmission=..., but not WHERE Dateofadmission=... alone — a B+-tree is ordered by leading prefix.

Primary keys and UNIQUE constraints are indexed automatically; do not duplicate them.

18.4 Solved Questions

Solved Question 1

Q. What is a view? State four purposes, and explain how a view relates to the three-schema architecture.

A. A view is a virtual table defined by a stored query over base tables. A standard view stores no data; only its definition is held in the catalog, and the query runs whenever the view is referenced.

Four purposes:

  1. Simplification. A complex multi-table join with grouping is written once and thereafter referenced as a single name, so application code and ad-hoc queries stay short and correct.
  2. Security. Granting SELECT on a view while withholding it on the base table exposes exactly the rows and columns the view selects. A clerk given PublicStudent(Rollno, Name) can never reach Dateofbirth.
  3. Logical data independence. After a base table is decomposed by normalisation, a view carrying the old name and columns preserves the interface, so existing applications continue to run unchanged.
  4. Presentation. Columns can be renamed, values computed and rows filtered so that each user group sees the shape it finds natural, over one consistent underlying design.

Relation to the three-schema architecture. A view is an external schema. The base tables are the conceptual schema; the view definitions stored in the catalog constitute the external/conceptual mapping; and the DBMS uses that mapping to rewrite a query on the view into a query on base tables. Views are therefore the concrete SQL realisation of the external level.

Solved Question 2

Q. When is a view updatable? Give an updatable and a non-updatable view over the college database, with reasons. What does WITH CHECK OPTION do?

A. A view is updatable when the DBMS can map each modification to exactly one row of exactly one base table unambiguously. A view is generally not updatable if it contains DISTINCT, GROUP BY, HAVING, an aggregate function, a set operation, or (with restrictions) a multi-table join.

Updatable:

CREATE VIEW PublicStudent AS SELECT Rollno, Name FROM STUDENT;
UPDATE PublicStudent SET Name = 'Ankit K. Sharma' WHERE Rollno = 'X2019';   -- works
It is a simple projection of one table; each view row corresponds to exactly one STUDENT row, identified by the primary key. (An INSERT through it supplies no Dateofbirth, so that column must be nullable or defaulted.)

Not updatable:

CREATE VIEW CourseEnrolment AS
SELECT c.SID, c.Cname, COUNT(a.Rollno) AS TotalEnrolled
FROM   COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Cname;
UPDATE CourseEnrolment SET TotalEnrolled = 5 WHERE SID='CS01'; is meaningless. TotalEnrolled is computed from many ADMISSION rows; setting it to 5 would require inventing three admissions, and the system cannot know which students to invent. There is no single base row to change.

WITH CHECK OPTION ensures that any row inserted or updated through the view still satisfies the view's WHERE clause. Without it:

CREATE VIEW FulltimeCourses AS SELECT * FROM COURSE WHERE Coursetype='Fulltime';
INSERT INTO FulltimeCourses VALUES ('DP01','Diploma',30,2,'Parttime','X');
The insert succeeds — the row lands in COURSE — but immediately disappears from the view, because it is not full-time. The user inserted a row through a view and then could not see it, which is confusing and usually a bug. Adding WITH CHECK OPTION rejects such an insert outright.

Solved Question 3

Q. Distinguish virtual from materialised views. When is each appropriate?

A.

Virtual view Materialised view
What is stored The definition only The result data
Freshness Always current — recomputed on reference Stale between refreshes
Query cost The full underlying query, every time A simple table read — fast
Storage cost None The full result
Write cost on base tables None Refresh work, or trigger maintenance
Availability All SQL systems Oracle, PostgreSQL; not native in MySQL

Use a virtual view when correctness must be absolute and up to the second — an account balance, a seat-availability check, anything a transaction depends on — or when the underlying query is cheap. This is the default choice.

Use a materialised view when the underlying query is expensive (aggregation over millions of rows), it is queried far more often than the base data changes, and some staleness is acceptable — the classic case being an overnight-refreshed reporting or dashboard summary.

A materialised view is controlled redundancy in the sense of Unit 1, Lesson 1: the data is deliberately duplicated, and the duplication is acceptable precisely because the system, not the application programmer, is responsible for keeping it in step.

In MySQL, which has no native support, the pattern is simulated by creating a real table and refreshing it with a scheduled EVENT or by triggers on the base tables.

Solved Question 4

Q. What is ODBC? Explain its architecture and write a Python program that connects to the college database and lists part-time students.

A. ODBC (Open Database Connectivity) is a standard C-based API through which an application can access any DBMS. The application is written once against the ODBC calls; a driver specific to each DBMS translates them into that product's native protocol. JDBC is the Java equivalent.

Architecture (Figure 6.1): application → ODBC API → Driver Manager → DBMS-specific driver → DBMS. Because the driver is the only DBMS-aware component, changing from MySQL to Oracle means changing the driver and the connection string — not the application code. This is portability at the application level, complementing the data independence the DBMS provides internally.

import mysql.connector
from mysql.connector import Error

conn = cur = None
try:
    conn = mysql.connector.connect(host='localhost', database='college',
                                   user='appuser', password='secret')
    cur = conn.cursor()
    cur.execute("""SELECT DISTINCT s.Rollno, s.Name
                   FROM   STUDENT s
                   JOIN   ADMISSION a ON s.Rollno = a.Rollno
                   JOIN   COURSE    c ON a.SID    = c.SID
                   WHERE  c.Coursetype = %s
                   ORDER BY s.Rollno""", ('Parttime',))
    rows = cur.fetchall()
    print(f"{len(rows)} part-time students\n")
    for rollno, name in rows:
        print(f"  {rollno:8} {name}")
except Error as e:
    print("Database error:", e)
finally:
    if cur:  cur.close()
    if conn and conn.is_connected(): conn.close()

Three points of technique. The value 'Parttime' is passed as a bound parameter (%s with a tuple), never concatenated — this is the defence against SQL injection. The finally block closes the cursor and connection whether or not an error occurred, so connections are not leaked. And DISTINCT prevents a student admitted to two part-time courses appearing twice.

Solved Question 5

Q. Write the SQL for course practical part II: create a user, create a role, grant privileges to the role, revoke privileges from the role, and create an index. Explain each.

A.

-- 1. CREATE USER
CREATE USER 'clerk'@'localhost' IDENTIFIED BY 'StrongPass#1';
CREATE USER 'analyst'@'%'       IDENTIFIED BY 'AnotherPass#2';
A MySQL account is the pair 'user'@'host'. 'clerk'@'localhost' may connect only from the server machine; 'analyst'@'%' from any host. Restricting the host is a genuine second layer of access control.

-- 2. CREATE ROLE
CREATE ROLE 'data_entry', 'reader';
A role is a named collection of privileges, so that privileges are maintained per job function rather than per person.

-- 3. GRANT PRIVILEGES TO A ROLE
GRANT SELECT, INSERT, UPDATE ON college.* TO 'data_entry';
GRANT SELECT                 ON college.* TO 'reader';
GRANT SELECT (Rollno, Name)  ON college.STUDENT TO 'reader';

-- assign roles to users, then activate
GRANT 'data_entry' TO 'clerk'@'localhost';
GRANT 'reader'     TO 'analyst'@'%';
SET DEFAULT ROLE ALL TO 'clerk'@'localhost', 'analyst'@'%';
The column-level grant is the point of DBMS security: the reader sees a student's roll number and name but never their date of birth — a distinction no file permission can express.

-- 4. REVOKE PRIVILEGES FROM A ROLE
REVOKE UPDATE ON college.* FROM 'data_entry';
REVOKE 'reader' FROM 'analyst'@'%';
Revoking from the role instantly affects every user holding it — one statement instead of one per user. That is the operational argument for roles.

-- 5. CREATE INDEX
CREATE INDEX idx_student_name ON STUDENT(Name);
CREATE INDEX idx_adm_sid      ON ADMISSION(SID);
CREATE UNIQUE INDEX idx_course_cname ON COURSE(Cname);
SHOW INDEX FROM STUDENT;
Indexes speed up WHERE, join and ORDER BY operations at the cost of storage and slower writes. Creating one changes only the internal schema, so no query or application needs to change — physical data independence.

Verify with SHOW GRANTS FOR 'clerk'@'localhost'; and EXPLAIN SELECT ... respectively.

Solved Question 6

Q. What is SQL injection? Show a vulnerable and a safe version, and state two defences beyond parameterisation.

A. SQL injection is an attack in which user-supplied input is concatenated into a SQL statement, so that characters in the input are parsed as SQL syntax rather than treated as a data value. The attacker thereby rewrites the query.

Vulnerable:

rollno = input("Roll number: ")
cur.execute("SELECT * FROM STUDENT WHERE Rollno = '" + rollno + "'")
Entering X2019' OR '1'='1 produces WHERE Rollno = 'X2019' OR '1'='1', which is true for every row — the entire table is disclosed. Entering X2019'; DROP TABLE STUDENT; -- submits a second statement and destroys the table.

Safe:

cur.execute("SELECT * FROM STUDENT WHERE Rollno = %s", (rollno,))
The statement text and the value travel to the server separately. The server parses the statement once, then binds the value into a placeholder, so the value can never become syntax — whatever characters it contains.

Two further defences (security must be layered):

  1. Least privilege. Grant the application account only what it needs — SELECT, INSERT, UPDATE on specific tables, never DROP, never ALL PRIVILEGES, never access to mysql.*. Then even a successful injection cannot drop a table, because the account is not permitted to.

  2. Views as a security boundary. Grant the application access to views rather than base tables, so that even a fully compromised query can reach only the rows and columns the view exposes. Combine with column-level grants.

A useful third measure is input validation — rejecting a roll number that does not match the expected pattern — but it must be treated as defence in depth, never as a substitute for parameterisation.

18.5 Exercises

A. Short answer

  1. What is a view, and what does it store?
  2. Name four purposes of views.
  3. Give three constructs that make a view non-updatable.
  4. What does WITH CHECK OPTION prevent?
  5. Distinguish virtual from materialised views.
  6. Expand ODBC and JDBC.
  7. Why is a role preferable to per-user grants?
  8. Name two costs of creating an index.

B. Write the SQL

  1. A view showing each student with their society names.
  2. A view of courses that are more than 80 per cent full.
  3. A view exposing only Rollno and Name from STUDENT, and grant it to a new user.
  4. A view of part-time courses that rejects inserts of full-time courses.
  5. Create users librarian and auditor with appropriate hosts.
  6. Create roles readonly, dataentry and dbadmin, grant sensible privileges to each, and assign them.
  7. Revoke DELETE from dataentry and explain who is affected.
  8. Create indexes you consider justified on the college schema, with a one-line justification for each.
  9. Grant a user the right to read only Name and Coursetype, not TotalSeats.

C. Python

  1. Write a program that connects to college and prints every course with its enrolment count.
  2. Write a program that inserts a new student, taking values from the user safely.
  3. Write a program using executemany to insert five courses in one call.
  4. Modify (19) so that a failed insert rolls back and reports the error.

D. Analytical

  1. Explain why CourseEnrolment cannot be updated, and describe what the DBMS would have to invent to allow it.
  2. A view is granted to a user who lacks access to the base tables. Explain exactly how the DBMS authorises the query.
  3. Argue for and against materialised views for a daily management dashboard.
  4. A table has indexes on eight columns and inserts have become slow. Explain the cause and propose a method for deciding which to drop.
  5. Explain why an index on (SID, Dateofadmission) does not help WHERE Dateofadmission = '2019-07-15'.
  6. Describe a complete defence-in-depth strategy against SQL injection for a college web application, naming at least four distinct measures.

E. Objective — True or False with reason

  1. A view stores a copy of the data.
  2. Dropping a view deletes the base table rows.
  3. All views are updatable.
  4. MySQL supports materialised views natively.
  5. Column-level GRANT is possible.
  6. Creating an index changes the conceptual schema.
  7. Parameterised queries prevent SQL injection.

Answers to Section E

  1. False. A standard view stores only its definition; the data is computed on reference. Only a materialised view stores data.
  2. False. DROP VIEW removes the definition only; base tables are untouched.
  3. False. Views with aggregation, GROUP BY, DISTINCT, set operations, or (with restrictions) joins are not updatable.
  4. False. Oracle and PostgreSQL do; in MySQL they are simulated with a table plus a scheduled refresh.
  5. True. GRANT SELECT (Rollno, Name) ON STUDENT TO ... — a granularity no file permission can express.
  6. False. An index is an internal-level structure. The conceptual schema is unchanged — this is physical data independence.
  7. True. The value is transmitted separately from the statement text, so it can never be parsed as SQL. It should still be combined with least privilege.

Lesson Summary

  • A view is a virtual table — the SQL realisation of the external schema. Purposes: simplification, security, logical data independence, presentation.
  • A view is updatable only if each row maps to one base row; aggregation, GROUP BY, DISTINCT and set operations prevent it. WITH CHECK OPTION stops rows escaping the view.
  • Materialised views trade freshness for speed; MySQL has none natively.
  • ODBC/JDBC insulate the application from the DBMS. Always use bound parameters — never string concatenation.
  • DBA commands: CREATE USER, CREATE ROLE, GRANT (including column-level), REVOKE, CREATE INDEX. Apply the principle of least privilege.
  • Index selectively: indexes speed reads and slow writes, and a composite index only serves its leading prefix.

End of Unit 4. Next: Unit 5, Lesson 1 — Mapping an ER Model to a Relational Database.