Chapter 5 review (Sections 5.1–5.6): schemas, data types, and SQL table commands

Section 5.1

Database Schemas

Before any SQL runs, something has to own the tables it creates. That something is a schema.

A schema is a logical group of database objects, such as tables, views, and indexes, that belong together and are associated with a single owner. It is the namespace everything else in this page lives inside. SQL creates one with a dedicated statement that also names its owner:

CREATE SCHEMA AUTHORIZATION store_admin;

Before store_admin or anyone else can touch that schema, the DBMS has to verify they are who they say they are. That check is called authentication, confirming a user has a legitimate right to access the database at all, which happens before any question of what that user is allowed to do is even considered.

Where the DBMS keeps track of everything

The DBMS tracks every object in every schema in its data dictionary: the stored description of each table, each column's name and data type, and every constraint attached to it. The dictionary holds metadata, data about data, such as the fact that P_PRICE is a numeric column rather than an actual price value itself.

Two families of SQL commands

Everything you write in SQL falls into one of two families, and this page is organized around the boundary between them:

DDL builds the containers; DML works with what's inside them.

Family	What it does	Covered in
Data Definition Language (DDL)	Defines and reshapes structures: schemas, tables, views	Sections 5.3, 5.4, 5.6
Data Manipulation Language (DML)	Inserts, updates, deletes, and retrieves the rows stored in those structures	Section 5.5, and Chapter 4

A database object is the general term for anything the DBMS creates and manages this way, a table, a view, an index, or a schema itself.

Section 5.2

Data Types

A column's data type is a promise to the DBMS about what kind of value will live there, and the promise determines what the DBMS will let you do with it.

Numbers: exact versus approximate, and how big

The numeric types you'll choose between most often.

Type	Holds
INTEGER	Whole numbers only, no digits after a decimal point
SMALLINT	Whole numbers like INTEGER, but across a smaller range, which costs less storage
NUMERIC(L,D) / NUMBER(L,D)	A number with exactly L total digits, D of them after the decimal point
DECIMAL(L,D)	The same idea as NUMERIC, except L is a minimum; the DBMS may store more precision than asked for

The two numbers in NUMERIC(L,D) are not arbitrary. L is the total count of digits the column can hold, and D is how many of those sit to the right of the decimal point. A column defined as NUMBER(7,2) has 7 digits total and 2 after the point, leaving 5 before it, so the largest value it can store is 99999.99, not 9999999.99. Get this wrong on a homework problem and it's almost always because L was read as "digits before the decimal" instead of "digits total."

Text: fixed width versus actual length

CREATE TABLE TEST_TYPES (
    FIXED_NAME    CHAR(25),
    FLEX_NAME     VARCHAR(25)
);

INSERT INTO TEST_TYPES VALUES ('Smith', 'Smith');

CHAR(L) always reserves exactly L characters, padding the unused space with blanks; 'Smith' stored in CHAR(25) occupies all 25 positions, 5 letters plus 20 trailing spaces. VARCHAR(L) stores only the characters actually entered, up to a maximum of L; the same 'Smith' in VARCHAR(25) takes just 5 characters of storage. L is a ceiling for VARCHAR, not a fixed size.

Dates get their own type for a reason

A DATE column isn't text that happens to look like a date, it's stored in a form the DBMS can do arithmetic on directly: adding 30 days to an invoice date, or subtracting two dates to get an interval, works only because the value is a real DATE, not a VARCHAR that merely resembles one.

Choosing the right type for the job

The same decision shows up constantly: does this value get used in arithmetic?

Value	Right type	Why
A US postal code, e.g. 06512	CHAR(5)	It's never added or multiplied, and a leading zero must be preserved, which a numeric type would silently drop
A customer's last name	VARCHAR(35)	Length varies widely; storing the fixed maximum every time would waste space
An invoice date	DATE	Needed for date arithmetic, like computing a due date
Quantity on hand	INTEGER	Always a whole unit count, no fractional part is meaningful

Section 5.3

Creating Tables

A table is its columns, its data types, and the rules, constraints, that keep bad data out from the moment it's created.

CREATE TABLE and its constraints

CREATE TABLE PRODUCT (
    P_CODE      CHAR(10)      NOT NULL,
    P_DESCRIPT  VARCHAR(40)   NOT NULL,
    P_PRICE     NUMBER(8,2)   CHECK (P_PRICE >= 0),
    P_QOH       INTEGER       DEFAULT 0,
    V_CODE      INTEGER,
    PRIMARY KEY (P_CODE),
    FOREIGN KEY (V_CODE) REFERENCES VENDOR
);

What each constraint on this table actually enforces.

Constraint	Enforces
PRIMARY KEY	The column or columns that uniquely identify every row; also forbids NULL automatically
NOT NULL	The column can never be left empty
UNIQUE	No two rows may share a value in this column, even though it isn't the primary key
CHECK (condition)	Every value entered must satisfy the condition; here, no negative price can ever be stored
DEFAULT value	Supplies this value automatically whenever a row is inserted without one
FOREIGN KEY ... REFERENCES	Every value must match an existing primary key value in the referenced table, enforcing referential integrity
ON UPDATE CASCADE	A table-level option on the foreign key: if the referenced primary key value changes, every matching foreign key value changes with it automatically

Building a table from a query instead of from scratch

CREATE TABLE ... AS SELECT, sometimes abbreviated CTAS, creates a new table and fills it with the rows a SELECT returns, in a single statement:

CREATE TABLE PART AS
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM   PRODUCT;

Three things about the result trip people up every time:

• The new table's column names come straight from the select list, including any alias; nothing extra is added.
• PART is a real, independent copy of the matching rows. It does not stay linked to PRODUCT; a later change to PRODUCT never appears in PART.
• PART does not inherit PRODUCT's primary key constraint, or any other constraint. If P_CODE needs to be a primary key in PART too, that has to be added afterward with ALTER TABLE.

If PART already exists and you just want to copy rows into it, that's a different statement entirely, not CREATE TABLE AS SELECT:

INSERT INTO PART (P_CODE, P_DESCRIPT, P_PRICE)
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM   PRODUCT;

Section 5.4

Altering Table Structure

A table rarely stays exactly as first designed. ALTER TABLE is how its structure changes after rows already exist.

The four things ALTER TABLE does.

Clause	Does
ADD	Adds a new column to an existing table
MODIFY (or ALTER COLUMN)	Changes an existing column's data type or width
DROP COLUMN	Removes an existing column, and every value stored in it, permanently
ADD CONSTRAINT	Attaches a new rule, such as a foreign key, to a table that already exists

ALTER TABLE PRODUCT ADD P_SALECODE CHAR(1);

ALTER TABLE VENDOR DROP COLUMN V_ORDER;

Why the new column above has no NOT NULL

Adding P_SALECODE to PRODUCT with NOT NULL attached would fail outright, the moment the statement runs, on any table that already has rows: every existing row has no value for the brand new column, so every one of them would instantly violate the NOT NULL rule being created. The fix isn't a special syntax, it's to leave NOT NULL off, let the column start out NULL for existing rows, and backfill real values afterward if the business rule truly requires one everywhere.

Rules worth knowing before you try to change a table

What ALTER TABLE will and won't let you do.

Change	What happens
Changing a column's data type	Usually allowed only when the column currently holds no data; existing, incompatible values block it
Increasing a column's width	Generally allowed even on a column that already has data, since nothing existing becomes invalid
Dropping a table another table's foreign key references	Fails until the referencing foreign key relationship is removed first
Adding a new NOT NULL column to a populated table	Fails, for the exact reason shown above

Section 5.5

Data Manipulation

Once a table exists, these four statements are how rows get into it, change, leave, or get read back out.

INSERT: three shapes

-- 1. One row, a value for every column, in table order
INSERT INTO PRODUCT
VALUES ('BRT345', 'Titanium drill bit', 25, 12.50, 21344);

-- 2. One row, only some columns named; the rest get NULL or their DEFAULT
INSERT INTO PRODUCT (P_CODE, P_DESCRIPT)
VALUES ('BRT345', 'Titanium drill bit');

-- 3. Many rows at once, copied from another table's query
INSERT INTO PART (P_CODE, P_DESCRIPT, P_PRICE)
SELECT P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT;

In the second form, the columns left out of the column list are not an error; each one receives NULL, or that column's DEFAULT value if one was defined when the table was created.

UPDATE: with and without WHERE

UPDATE PRODUCT
SET    P_PRICE = P_PRICE * 1.10
WHERE  V_CODE = 21344;

The WHERE clause is what limits an UPDATE to specific rows. Leave it off entirely and the SET applies to every row in the table, not zero rows and not an error:

UPDATE PRODUCT
SET    P_PRICE = P_PRICE * 1.10;
-- raises the price of every single product by 10%, store-wide

DELETE: with and without WHERE

DELETE FROM PRODUCT WHERE P_QOH = 0;
-- removes only the rows with zero quantity on hand; the table structure stays

DELETE FROM PRODUCT;
-- removes every row; PRODUCT still exists afterward, empty

COMMIT and ROLLBACK

COMMIT permanently saves every data change made since the last save point. ROLLBACK undoes every data change made since that same point, but only while those changes are still uncommitted. Once COMMIT has run, ROLLBACK can no longer touch that work:

UPDATE PRODUCT SET P_PRICE = P_PRICE * 1.10;
-- realize this was a mistake, before committing:
ROLLBACK;
-- every price is back to what it was

Section 5.6

Views

A view looks like a table in every query you write against it, but there's no second copy of the data sitting behind it.

A view is a virtual table based on a SELECT query: its rows are not stored anywhere on their own, they're produced fresh from the underlying base table every time the view is referenced.

CREATE VIEW PRICEGT50 AS
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM   PRODUCT
WHERE  P_PRICE > 50;

SELECT * FROM PRICEGT50;
-- runs the stored SELECT against PRODUCT right now, every time

Dropping a view removes only its saved definition. The base table, and every row in it, is completely unaffected:

DROP VIEW PRICEGT50;
-- PRICEGT50 no longer exists as a name; PRODUCT and its rows are untouched

When a view can be updated, and when it can't

An updatable view can appear on the receiving end of an UPDATE, with the change actually applied to its base table. Whether a given view qualifies comes down to whether the DBMS can trace each displayed row back to exactly one row of exactly one base table, a property called being a key preserved table: the view's rows still correspond one to one with the base table's primary key.

What breaks that one-to-one correspondence.

Disqualifies an updatable view	Why
Aggregate functions and GROUP BY	Each output row summarizes many base table rows; there's no single row left to write an update back to
UNION, INTERSECT, and EXCEPT	A single output row could trace back to either side of the set operation, with no way to know which base table a change belongs in

-- Updatable: every row maps to exactly one PRODUCT row
CREATE VIEW V2 AS
SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE > 50;

-- NOT updatable: GROUP BY collapses many rows into one per vendor
CREATE VIEW V1 AS
SELECT V_CODE, SUM(P_QOH * P_PRICE) AS TOTVALUE
FROM   PRODUCT
GROUP BY V_CODE;

A WHERE clause, by itself, does not disqualify a view; V2 above is still updatable even though it filters out products priced at $50 or below. It's aggregation and set operators specifically that break the one-row-to-one-row link an update needs.

Quick check