Terse Notes — Fundamentals of Database Systems Part 6
SQL Data Definition and Data Types
Book Credit to Elmasri Ramez And Navathe Shamkant
Book: Fundamentals Of Database System, 7th Edition by Elmasri Ramez And Navathe Shamkant
Ch. 6 — SQL Data Definition and Data Types
SQL is comprehensive database language; it includes both DDL and DML; defining views on the database; security and authorization; defining integrity constraints; transaction controls.
Data definition and data types:
For data definition, CREATE statement is used to create schemas, tables (relations), types, domains, views, assertions, triggers and other constructs.
Schemas — created with CREATE SCHEMA statement; the elements of a schema include tables, types, constraints, views, domains, other constructs. Identified with schema name and includes authorization identifier.
e.g. CREATE SCHEMA COMPANY AUTHORIZATION ‘billnye’;
Catalog — a named collection of schemas. Includes a special schema called INFORMATION_SCHEMA; integrity constraints can be defined only between relations in the same catalog. And these in the same catalog can share elements like type and domain definitions.
Create Table Command
Relations — CREATE TABLE commands is used to specify a new relation/table. What results is a base table, which means the table and its rows are created and stored as a file by the DBMS.
Base table/relation Versus Virtual table/relation -
A virtual table is created through the CREATE VIEW command, which may or may not correspond to an actual physical file.
Attributes/columns are ordered in the sequence in which they are specified in the CREATE TABLE statement.
Tuples/rows are not ordered in a table.
Attribute Data Types and Domains in SQL
Includes numeric, character string, bit string, boolean, date, time.
CREATE DOMAIN — used to declare a domain with the attribute specification. Makes it easier to change the datatype for a domain that is used by numerous attributes in a schema.
CREATE TYPE — used to create user defined types or UDTs. These can be used as data types for attributes or for creating tables.
Specifying Constraints in SQL
Attribute constraints:
NOT NULL — can be specified because SQL does allow null values. Its implicit for primary keys.
Default value — we can specify a default value for an attribute by appending DEFAULT <value> to an attribute definition.
CHECK — a clause that can restrict attribute or domain values; e.g. Number INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);
Key and referential integrity constraints:
PRIMARY KEY — a clause specified one or more attributes as a primary key. It can follow the attribute directly if a primary key has a single attribute.
UNIQUE — a clause to specify alternate unique keys. Aka candidate keys.
Referential integrity is specified with Foreign Key
FOREIGN KEY — a clause to specify foreign keys.
With referential integrity constraints we have to take into consideration what happens when that constraint is violated.
RESTRICT — this option is the default action taken when a referential integrity violation occurs. It rejects the update operation.
Referential triggered action — an action that can be designed into the DBMS for when integrity violation occurs.
CONSTRAINT — this is used to give constraint name to a constraint to identify a particular constraint in case it must be dropped later or replaced with another one. Must be unique.
CHECK on a table — this is another way to restrict tables; called row-based constraints because they apply to each row individually and are checked whenever a row is inserted or modified. It goes at the end of the CREATE TABLE statement.
Basic Retrieval Queries in SQL
SELECT — the one basic statement for retrieving information
Comparison operators are = < > <= >= and <> (which means not equal)
Projection attributes — the attributes that you are retrieving
Selection condition — in relational algebra this is the retrieved tuple
Join condition — it combines two tuples from different relations.
Select-project-join query — any query where you provide select and join conditions and projection attributes.
Aliases or tuple variables — declared alternative relation names by using the keyword AS
AS — a keyword that creates an alias, e.g. EMPLOYEE E or EMPLOYEE AS E, or EMPLOYEE AS E(Fn, Mi, Ln, Ssn)
You can rename the attribute names like the last example given above.
Asterisk * — means all.
DISTINCT — keyword used to select only the distinct tuples; hence this eliminates duplicate tuples from being selected.
LIKE — keyword to do string pattern matching; “Partial strings are specified using two reserved characters: % replaces an arbitrary number of zero or more characters, and the underscore (_) replaces a single character.” pp. 195
Concatenate operator = ||
BETWEEN — another comparison operator
ORDER BY — clause used to order the tuples of a query by an attribute
ASC — in ascending order
DESC — in descending order
Insert, delete and update statements in SQL
INSERT — used to add a single tuple (row) into a relation (table); values should be listed in the same order that they were in the create table command. Also possible to insert from the results of a query.
DELETE — removes tuples from a relation.
DROP TABLE — to delete the table and the table definition.
UPDATE — to modify attribute values of one or more selected tuples.
SET — a clause in the update command to specify attributes to be modified and their new values. More than one tuple can be updated. But only refers to a single relation
Review Questions
6.1. In SQL, relations (tables) differ from the formal relations defined in Chapter 3 in the following ways:
- SQL tables are multisets (bags) of tuples, meaning they can contain duplicate tuples, whereas formally defined relations are sets that cannot have duplicates. SQL allows duplicates for efficiency reasons and because users may want to see them in query results or when applying aggregate functions.
- SQL uses different terminology, such as “table” instead of “relation”, “row” instead of “tuple”, and “column” instead of “attribute”.
6.2. The data types allowed for SQL attributes include:
- Numeric types like INTEGER, SMALLINT, DECIMAL, REAL, DOUBLE PRECISION
- Character string types like CHAR, VARCHAR
- Bit string types like BIT, BIT VARYING
- Boolean types like BOOLEAN
- Date/time types like DATE, TIME, TIMESTAMP
- Other types like INTERVAL (for storing time intervals)
6.3. SQL allows implementing entity integrity and referential integrity constraints as follows:
- Entity integrity is enforced by declaring a PRIMARY KEY constraint on one or more attributes that uniquely identify each tuple.
- Referential integrity is enforced by declaring a FOREIGN KEY constraint that references the PRIMARY KEY of another table.
- Referential triggered actions like ON DELETE CASCADE or ON UPDATE SET NULL can be specified to handle violations of referential integrity constraints.
6.4. A simple SQL retrieval query has the following four main clauses:
- SELECT clause: Specifies the attributes to be retrieved in the result. Required.
- FROM clause: Specifies the tables/relations from which to retrieve data. Required.
- WHERE clause: Specifies conditions that the result tuples must satisfy. Optional.
- ORDER BY clause: Specifies attributes to order the result tuples by. Optional.
Other constructs like JOIN, GROUP BY, HAVING can also be used in more complex queries.
