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
- Create, query, update and drop views, and state their four purposes.
- Determine whether a view is updatable, and use
WITH CHECK OPTION. - Distinguish virtual from materialised views.
- Explain ODBC/JDBC and connect to a database from Python.
- 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:
DISTINCTGROUP BYorHAVING- 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
SELECTlist
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¶
- Connect — establish a session, supplying host, database, user and password.
- Create a cursor — the object that executes statements and holds results.
- Execute — run SQL, with parameters where values are involved.
- Fetch — retrieve result rows.
- Close — release the cursor and connection;
commitfirst 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:
- 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.
- Security. Granting
SELECTon a view while withholding it on the base table exposes exactly the rows and columns the view selects. A clerk givenPublicStudent(Rollno, Name)can never reachDateofbirth. - 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.
- 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
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');
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';
'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';
-- 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'@'%';
-- 4. REVOKE PRIVILEGES FROM A ROLE
REVOKE UPDATE ON college.* FROM 'data_entry';
REVOKE 'reader' FROM 'analyst'@'%';
-- 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;
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 + "'")
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,))
Two further defences (security must be layered):
-
Least privilege. Grant the application account only what it needs —
SELECT,INSERT,UPDATEon specific tables, neverDROP, neverALL PRIVILEGES, never access tomysql.*. Then even a successful injection cannot drop a table, because the account is not permitted to. -
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
- What is a view, and what does it store?
- Name four purposes of views.
- Give three constructs that make a view non-updatable.
- What does
WITH CHECK OPTIONprevent? - Distinguish virtual from materialised views.
- Expand ODBC and JDBC.
- Why is a role preferable to per-user grants?
- Name two costs of creating an index.
B. Write the SQL
- A view showing each student with their society names.
- A view of courses that are more than 80 per cent full.
- A view exposing only
RollnoandNamefromSTUDENT, and grant it to a new user. - A view of part-time courses that rejects inserts of full-time courses.
- Create users
librarianandauditorwith appropriate hosts. - Create roles
readonly,dataentryanddbadmin, grant sensible privileges to each, and assign them. - Revoke
DELETEfromdataentryand explain who is affected. - Create indexes you consider justified on the college schema, with a one-line justification for each.
- Grant a user the right to read only
NameandCoursetype, notTotalSeats.
C. Python
- Write a program that connects to
collegeand prints every course with its enrolment count. - Write a program that inserts a new student, taking values from the user safely.
- Write a program using
executemanyto insert five courses in one call. - Modify (19) so that a failed insert rolls back and reports the error.
D. Analytical
- Explain why
CourseEnrolmentcannot be updated, and describe what the DBMS would have to invent to allow it. - A view is granted to a user who lacks access to the base tables. Explain exactly how the DBMS authorises the query.
- Argue for and against materialised views for a daily management dashboard.
- A table has indexes on eight columns and inserts have become slow. Explain the cause and propose a method for deciding which to drop.
- Explain why an index on
(SID, Dateofadmission)does not helpWHERE Dateofadmission = '2019-07-15'. - 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
- A view stores a copy of the data.
- Dropping a view deletes the base table rows.
- All views are updatable.
- MySQL supports materialised views natively.
- Column-level
GRANTis possible. - Creating an index changes the conceptual schema.
- Parameterised queries prevent SQL injection.
Answers to Section E
- False. A standard view stores only its definition; the data is computed on reference. Only a materialised view stores data.
- False.
DROP VIEWremoves the definition only; base tables are untouched. - False. Views with aggregation,
GROUP BY,DISTINCT, set operations, or (with restrictions) joins are not updatable. - False. Oracle and PostgreSQL do; in MySQL they are simulated with a table plus a scheduled refresh.
- True.
GRANT SELECT (Rollno, Name) ON STUDENT TO ...— a granularity no file permission can express. - False. An index is an internal-level structure. The conceptual schema is unchanged — this is physical data independence.
- 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,DISTINCTand set operations prevent it.WITH CHECK OPTIONstops 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.