Introduction to Database Management (CS-504 (C)) Notes

How unit 3 is examined

This unit covers the SQL command categories, the DDL commands that build and change tables, keys, indexes and cursors; the marks sit in Primary Key (super key, primary key, foreign key) and in DML/DDL/DCL.

Categories of SQL Commands

Low weight

Definition. SQL commands are grouped by purpose into DDL (define structure), DML (manipulate data), DCL (control access), and also TCL (control transactions) and DQL (query).

Key points.

DDL (Data Definition Language) defines and changes the structure of database objects, using CREATE, ALTER, DROP and TRUNCATE.
DML (Data Manipulation Language) inserts, changes, deletes and reads the data inside tables, using INSERT, UPDATE, DELETE and SELECT.
DCL (Data Control Language) controls who may use the data, using GRANT and REVOKE.
TCL (Transaction Control Language) manages transactions with COMMIT, ROLLBACK and SAVEPOINT.
DDL statements auto-commit and cannot be rolled back, whereas DML changes can be rolled back until COMMIT.

Basis	DDL	DML	DCL
Purpose	Defines table structure	Manipulates rows of data	Grants and withdraws privileges
Commands	CREATE, ALTER, DROP, TRUNCATE	INSERT, UPDATE, DELETE, SELECT	GRANT, REVOKE
Acts on	Schema (tables, indexes)	Data (rows)	Users and permissions
Rollback	No, auto-commit	Yes, before COMMIT	No
Example	`CREATE TABLE emp(id INT);`	`INSERT INTO emp VALUES(1);`	`GRANT SELECT ON emp TO ravi;`

Answer frame. Open with the one-line definition of the three categories; give the table above; then one example per category; close by noting DDL changes structure, DML changes data, DCL changes permissions.

Asked: [7 marks] (Dec 2020) Differentiate DML, DDL and DCL with suitable example.

Data Definition Language

Definition. DDL is the set of SQL statements that create, modify and remove the structure (schema) of database objects such as tables, indexes and views.

Key points.

The DDL commands are CREATE, ALTER, DROP, TRUNCATE (and RENAME).
They change the data dictionary, not the rows stored in the tables.
Each DDL statement commits automatically, so it cannot be rolled back.
DDL also defines integrity constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE and CHECK.

Create table

Definition. CREATE TABLE defines a new table with its column names, data types and constraints.

CREATE TABLE student (
  roll  INT PRIMARY KEY,
  name  VARCHAR(30) NOT NULL,
  dob   DATE,
  fees  DECIMAL(8,2) CHECK (fees >= 0)
);

Key points.

Each column needs a name and a data type; constraints are optional.
Common types: INT/NUMBER (whole numbers), DECIMAL(p,s) (exact fixed-point), FLOAT (approximate), CHAR(n) (fixed length), VARCHAR(n) (variable length), DATE and TIME (date and time), BOOLEAN (true or false), BLOB (binary objects).
CHAR pads to n characters while VARCHAR stores only the characters entered.

Asked: [7 marks] (Dec 2020) List and explain the common data types available in SQL.

Drop table

Definition. DROP TABLE permanently removes a table, its rows, its indexes and its definition from the database.

Key points.

Syntax: DROP TABLE student;
It is DDL, so it commits automatically and cannot be rolled back.
A table referenced by a foreign key cannot be dropped unless CASCADE CONSTRAINTS is used (Oracle) or the child table is dropped first.
DROP TABLE IF EXISTS student; avoids an error when the table is absent.

Alter Table

Definition. ALTER TABLE changes the structure of an existing table, by adding, modifying or dropping columns and constraints.

ALTER TABLE student ADD email VARCHAR(40);
ALTER TABLE student MODIFY name VARCHAR(50);
ALTER TABLE student DROP COLUMN dob;
ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY (roll);

Key points.

ADD inserts a new column or constraint; existing rows get NULL in a new column.
MODIFY (ALTER COLUMN in SQL Server) changes the data type or size of a column.
DROP COLUMN removes a column and its data.
RENAME changes the table or column name.

Primary Key

Definition. A primary key is the minimal set of attributes, chosen from the candidate keys, that uniquely identifies every tuple of a relation and can never be NULL.

Key points.

A super key is any set of attributes that uniquely identifies a tuple; it may contain extra attributes, so a relation has many super keys.
A candidate key is a minimal super key: removing any attribute destroys uniqueness.
The primary key is the one candidate key the designer selects; the others are alternate keys.
A relation has exactly one primary key, but it may consist of several attributes (composite key).
A primary key value must be unique and NOT NULL (entity integrity), and SQL builds an index on it automatically.
A foreign key is an attribute of one relation that refers to the primary key of another relation, and its value must match an existing key value or be NULL (referential integrity).

Example. STUDENT(Roll, Email, Name): Roll and Email are each unique. Super keys: {Roll}, {Email}, {Roll, Name}, {Roll, Email}, {Roll, Email, Name}. Candidate keys: {Roll}, {Email}. Primary key chosen: {Roll}. In ENROLL(Roll, CourseID), Roll is a foreign key referring to STUDENT.Roll.

CREATE TABLE enroll (
  roll INT, course INT,
  PRIMARY KEY (roll, course),
  FOREIGN KEY (roll) REFERENCES student(roll)
);

Basis	Super key	Primary key
Definition	Any attribute set that uniquely identifies a tuple	Minimal candidate key chosen to identify tuples
Minimality	May contain redundant attributes	Always minimal
Number	Many per relation	Exactly one per relation
NULL values	Not restricted by definition	Cannot be NULL
Relationship	Superset of candidate keys	One selected candidate key
Example	{Roll, Name}	{Roll}

Answer frame. For "distinguish": open by defining both keys, give the table, then the STUDENT example, and close with "every primary key is a super key but not conversely". For "explain terms": define super key, primary key, foreign key in that order with one example each, and stress uniqueness (super key) versus minimality (primary key).

Pitfall: Writing that a primary key may be NULL, or that a relation can have two primary keys.

Asked: [7 marks] (Dec 2020) Distinguish between primary and super key. Asked: [7 marks] (Jun 2020) Explain following terms: i) Super key ii) Primary key iii) Foreign key

Foreign Key

Definition. A foreign key is a column (or set of columns) in a child table that refers to the primary key of a parent table, enforcing referential integrity.

Key points.

Syntax: FOREIGN KEY (dept_id) REFERENCES dept(dept_id).
The value must exist in the parent key or be NULL, so no orphan rows appear.
ON DELETE CASCADE deletes child rows with the parent; ON DELETE SET NULL sets the foreign key to NULL.
The parent table must be created first, and cannot be dropped while children refer to it.

Truncate Table

Definition. TRUNCATE TABLE removes all rows of a table at once but keeps the table structure.

Key points.

Syntax: TRUNCATE TABLE student;
It is DDL, so it auto-commits and cannot be rolled back, and it has no WHERE clause.
DELETE is DML: it removes chosen rows with WHERE, can be rolled back, and is slower because it logs each row.
TRUNCATE is faster and resets identity counters and storage space, while DROP removes the table itself.

Index

Definition. An index is a separate data structure (usually a B-tree) on one or more columns that lets the database find rows quickly without scanning the whole table.

Key points.

Syntax: CREATE INDEX idx_name ON student(name); and CREATE UNIQUE INDEX ... for unique values; DROP INDEX idx_name; removes it.
Indexes speed up SELECT with WHERE, JOIN and ORDER BY on the indexed column.
They slow INSERT, UPDATE and DELETE and use extra storage, because the index must be maintained.
Primary key and UNIQUE constraints create an index automatically.

Cursor

Definition. A cursor is a pointer to the private work area holding the rows returned by a query, letting a program process them one row at a time.

Key points.

An implicit cursor is opened automatically by SQL for every DML statement; an explicit cursor is declared by the programmer for a multi-row SELECT.
The explicit cursor steps are DECLARE, OPEN, FETCH (repeated) and CLOSE.
Attributes %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN report the cursor state.
Cursors are needed because host programs handle one row at a time while SQL returns a set.

DECLARE CURSOR c IS SELECT name FROM student;
OPEN c;  FETCH c INTO v_name;  CLOSE c;

Asked: [7 marks] (Dec 2020) Write short notes on (any three): i) Basic structure of SQL query ii) Data independence iii) Cursors in SQL iv) Trigger in SQL v) Transactions

Last-minute revision

DDL = CREATE, ALTER, DROP, TRUNCATE; DML = INSERT, UPDATE, DELETE, SELECT; DCL = GRANT, REVOKE; TCL = COMMIT, ROLLBACK, SAVEPOINT.
DDL auto-commits and cannot be rolled back; DML can be rolled back before COMMIT.
Super key: unique, may be redundant; candidate key: minimal super key; primary key: chosen candidate key.
Primary key: one per table, unique, NOT NULL, may be composite.
Foreign key refers to a parent primary key; it can be NULL and enforces referential integrity.
DROP removes table and data; TRUNCATE removes all rows, keeps structure; DELETE removes chosen rows.
ALTER TABLE: ADD, MODIFY, DROP COLUMN.
Index speeds reads and slows writes.
Cursor steps: DECLARE, OPEN, FETCH, CLOSE.
CHAR is fixed length; VARCHAR is variable length.

Memory hooks

DDL = Design, DML = Move data, DCL = Control access.
Super, Candidate, Primary: a funnel, wide to minimal to the one chosen.
Drop = demolish, Truncate = empty the house, Delete = remove chosen furniture.
Cursor = DOFC: Declare, Open, Fetch, Close.

Coverage checklist

Categories of SQL Commands: Dec 2020 DML, DDL and DCL.
Data Definition Language: covered, no past question.
Create table: Dec 2020 SQL data types.
Drop table: covered, no past question.
Alter Table: covered, no past question.
Primary Key: Dec 2020 primary vs super key; Jun 2020 super, primary, foreign key.
Foreign Key: covered, asked inside the Jun 2020 key terms question.
Truncate Table: covered, no past question.
Index: covered, no past question.
Cursor: Dec 2020 short notes including cursors.

Account

Introduction to Database Management Systems (CS-504 (C)) - Unit 3 Short Notes

How unit 3 is examined

Categories of SQL Commands

Data Definition Language

Create table

Drop table

Alter Table

Primary Key

Foreign Key

Truncate Table

Index

Cursor

Last-minute revision

Memory hooks

Coverage checklist