Defining a database
Specifying its structure, data types, relationships and constraints.
Base table
logical structure used for storing data in a database.
CREATE TABLE <table name> ()
SQL statement for creating a base table by specifying its name, column names, types and constraints. A primary key should be provided to make it a
relational
table (no dupes) with the following syntax ''CREATE TABLE <name> ( ... <column_name><data_type>[<
CREATE TABLE LPM
The syntax of the table definition is checked first, then the logical schema is altered to include the new table definition, then a new empty table is created ready to receive data.
Table Naming Rules
Must be unique (existing tables may already have data and that might not be compatible with the new definition), start with a letter but can otherwise use letters, numbers and underscores up to 128 characters. Mustn't use keywords.
Exact numeric data types
INTEGER/INT, SMALLINT, NUMERIC(<p>, <scale>), DEC/DECIMAL(<p>, <scale>). Where, for the decimal numbers, <p> specificies the number of digits, <scale> of which occur after the decimal point.
Decimal Number
A number specified by base-10 positional notation. Includes decimal fractions which come after a decimal point and represent nested 'tenths' of the number to their left.
Approximate numeric data types
FLOAT(<p>), REAL, DOUBLE PRECISION are all floating point numbers where <p> represents the largest number of digits used for precision overall. The default precision for the platform is represented by the real type along with the double type for more prec
Character string data types
CHAR/CHARACTER(<len>), VARCHAR/VARYING CHARACTER(<len>) hold sequences of characters where the standard char type is fixed length and the varchar type will store the only characters that are needed as long as they are less then <len>. <len> will default t
Datetime data types
DATE, TIME(<p>), TIMESTAMP(<p>), INTERVAL represent time using years, months, days, hours, seconds, fractions of seconds appropriately. <p> is used to specify the precision (digit number) used in fractions of a second (ie. after the decimal point). Note t
Example column type definition
'name CHAR(16) NOT NULL' which defines a column called 'name' which can have values represented by a 16 character length string but is not allowed to be NULL.
NOT NULL constraint
Prohibits missing data values
. By default, columns allow null markers within a row. However you can constrain a column so that it won't allow null entries as part of the table definition with the keywords NOT NULL. E.g. 'manager_id CHAR(4) NOT NULL'. Try
Default Values
For when a row is inserted without a value we can specify a
constant
default value that will be used for that column using DEFAULT. E.g. 'cars_owned INTEGER NOT NULL DEFAULT 0'
PRIMARY KEY (<column_name>)
Used to specify the primary lkey within an SQL table definition (CREATE TABLE).
Literal Value
A fixed constant value that can be entered directly by a programmer using the language notation. E.g. 'Jonathan', 35, 15.3.
SQL System Variable as defaults
CURRENT_USER and CURRENT_DATE are equivalent to specifying the value of the user or todays date precisely when they are used. These can be used as a DEFAULT in a column definition for when insertions are made without specifying values for that column.
CREATE DOMAIN
Can be used to define a domain, stored as part of the schema, to subsequently be used in a table definition. They are pretty much identifiers that reference a column definition, e.g. 'CREATE DOMAIN a_domain CHAR(4) NOT NULL DEFAULT 'JIM'' where 'a_domain'
DROP TABLE <table_name>
Deletes a table completely including its contents and its definition from the logical schema. E.g. 'DROP TABLE old_footballers'
Modifying an existing table
May involve adding or removing columns, changing their data type, renaming them, adding a column (would require a value to be introduced for each existing column), adding or removing constraints and default values.
ALTER TABLE <table_name> <action>
Used to change the definition of an existing table. Allows the adding or dropping of columns, addition or removal of column defaults, addition or dropping of integrity constraints.
ALTER TABLE <table_name> ADD <column_def>
Used to add a new column to an existing table by specifying a column definition. E.g. ALTER TABLE footballers ADD pet_name CHAR(10) DEFAULT 'Rover'. By default this populates the new column with the default value or null if they don't exist. This means NO
ALTER TABLE <name> DROP <column>
Used to remove a column from a table definition, along with its data. E.g. 'ALTER TABLE footballers DROP pet_name'
ALTER TABLE <name> ALTER <column> SET <default>
Used to change the default value of a given column, e.g. 'ALTER TABLE footballers ALTER pet_name SET DEFAULT NULL'
ALTER TABLE <name> ALTER <column> DROP DEFAULT
Used to remove the default value from a column definition in an existing table definition. E.g. 'ALTER TABLE footballers ALTER pet_name DROP DEFAULT'. Ultimately if an INSERT is used without specifying a value for that column then a null marker will be us
Integrity Violation
Occurs when there is difference between the data and the information in the real world that it represents.
Integrity (Correctness)
When the data that has been recorded is incorrect, for example adding a cricket player as a football player.
Integrity (Consistency)
When two items of data, representing the same information, are different. For example if a footballer is classed as a midfielder in the team table but as a striker in a medical record.
SQL Integrity Constraints
NOT NULL
column constraint; unique constraint (primary or alternate keys);
referential constraint
(whereby when a column in one table has non-null value, this refers to a matching value in another table);
CHECK constraint
checks each row to see if it eval
Column constraint
Constraint defined as part of a column definition (e.g. NOT NULL). 'CREATE TABLE <name> (... <col_name><col_type><col_constraint(s)>, ...)'
Table constraint
Constraint defined as part of a table definition (e.g. PRIMARY KEY). Although these can often be defined as part of a column definition it is clearer to include them in the table definition. 'CREATE TABLE <name> (... <table_constraint(s)> ...)'
ALTER TABLE <name> ADD [<CONSTRAINT <constraint_name>] <table_constraint>
Used to add a constraint to a table with an optional reference name. The name is useful for future reference such as when deleting the constraint.
ALTER TABLE <name> DROP <constraint_name>
Used to remove a constraint from a logical schema table definition by referencing its name.
ALTER TABLE <name> ALTER <column> ADD <column_constraint>
Used to add a column constraint to a table definition in a logical schema. E.g. 'ALTER TABLE footballers ALTER pet_name ADD NOT NULL'
ALTER TABLE <name> ALTER <column> DROP <column_constraint>
Used to remove a column constraint from a table definition in a logical schema.
PRIMARY KEY (<column_name(s))
Ensures that the named column values are unique for each row and not null. Can be specified at the end of a column definition if a single column is used for the key, or as part of the table definition if the key is made up of a combination of columns. E.g
UNIQUE
Used to define the equivalent of an alternate key, ie. one or more columns which have unique value within a table. This can be added to column definitions, or as a table definition if a combination is required. E.g. 'nick_name VARCHAR(16) UNIQUE' or for a
ALTER TABLE <name> ADD UNIQUE(<column_name(s)>)
Used to add the equivalent of alternate keys to
existing tables
in the logical schema. Ie. it involves altering the existing table definition.
FOREIGN KEY <col_name> REFERENCES <related_table_name>(<key_columns>)
Used to add a
referential constraint
from the referencing table to the referenced table (ie. a
foreign key
that references a unique key (usually primary) in another table). Should a value be added to that key, the system will check that the value does ind
ALTER TABLE <name> ADD FOREIGN KEY (<column(s)>) REFERENCES <ref_table(<column(s)>)
Used to add a foreign key definition to an
existing table
by specifying the unique columns for the foreign key and the unique columns in the referenced table. E.g. 'ALTER TABLE <name> ADD FOREIGN KEY (manager_id, department) REFERENCES managers(id, depart
Foreign Keys & Primary Keys
Usually a foreign key definition will refer to the primary key in another table and so the syntax allows you to just refer to the table for simplicity. Therefore the foreign key must be null or contain a value exists in the referenced table primary keys.
Referential Action
To maintain referential integrity, by
default
, referenced rows in the referenced table can't be deleted/updated (
restrictive
) so that referencing rows don't have foreign keys that refer to non-existent rows. However, different referential actions can b
SET NULL, SET DEFAULT, CASCADE, NO ACTION / RESTRICT
The
actions
that can be defined to take place on a
referencing
(dependent) row when its
referenced
row has its primary key deleted or updated for some reason. These are used as responses to the violation of
referential integrity
between the dependent fore
ON DELETE / ON UPDATE
The conditions, used in a referential column definition, that determine which referential action will take place should our referenced row be
deleted
or have its referenced key
updated
(this should be rare). E.g. 'ALTER TABLE footballers ADD FOREIGN KEY p
Applying Constraints on Existing Data
When applying constraints to existing tables with existing data, if the existing data doesn't conform to the constraint then it can't be applied. It may be necessary to remove or update rows that don't comply so that the constraint can be applied in futur
Referential Integrity Violation Examples
Violating operations on the referencing table will include the addition or update of foreign key values that don't match any primary key in the referenced table. Violating operations in the referenced table will include deleting or updating the referenced
CHECK constraint
A general constraint evaluated for every row of the table which must evaluate to TRUE or UNKNOWN (due to nulls). Can be defined on a column or table definition. For a column '<column_name><data type> CHECK(<search condition>)' and for a table '... [CONSTR
CHECK column constraint
Whereby the value of an inserted or updated row over a particular (one) column are constrained using a search condition. E.g. 'age SMALLINT CHECK (age BETWEEN 16 AND 50)'.
CHECK table constraint
Whereby the value of an inserted or updated row is checked by using information from more than one column. E.g. an obvious constraint 'CHECK(start_date < end_date)' could be added to a table definition.
CHECK & subqueries (not generally supported)
Checks the value of an inserted, updated
or deleted
row against a search condition which will compare a row value(s) against the
results of a subquery
for a TRUE or UNKNOWN result, otherwise the operation fails on the given table. Additionally, the constr
CHECK, VALUE & domains
Instead of applying a CHECK constraint to a column in the table, it can be applied to a DOMAIN definition using VALUE as a generic variable that will refer to whatever column is defined on the domain. E.g. to define a constrained domain 'CREATE DOMAIN pro
CHECK Vs REFERENTIAL constraints
Although CHECK constraints can be used in place of referential constraints, they are less efficient, obfuscate the intention of the constraint and don't allow the use of referential actions.
CHECK side-effects & dependencies
It is worth being careful with CHECK constraints as they can be so general as to accidentally restrict the function of the database. They should also be authored so that they are independent, ie. they don't rely on the functionality of other constraints t
View (viewed table)
A table definition which exists only in the logical schema without any associated stored data (as opposed to a base table). Consists only of a query that produces the requred rows and columns of data and can itself be used in further queries.
Underlying base tables
The tables that ultimately provide the data for a view table even if the view tables query specification itself contains views.
CREATE VIEW <name> AS <query spec>
Used to create a view using an appropriate query specification such as 'CREATE VIEW counselling AS SELECT student_id, counsellor_no, region FROM student' where the column names will originate from the SELECT clause and multiple input tables can be used in
Views and column names
A views column names need to be explicitly defined if it is made up of more than one underlying base table with equivalent names (fully qualified names aren't used in a view) or there are un-named value expression columns. This follows the syntax 'CREATE
Views & changes to its underlying base tables
When a view is created with all the columns from an underlying base table (SELECT *) these will be added to the views definition. If a new column is subsequently added to the base table, it won't be included in the view table, which will then seem at odds
View LPM
When a query specification includes a view as part of a FROM clause (even in a sub-query), the table used will be that created from the query retrieved from the view definition. This is managed separately from the processing of the main query, which will
View Useage
Mainly for ease of use. A DBA could provide simple tables for users to query separately to the complex set of base tables and their relationships. They allow for the saving of common / repeatedy used queries. Can be used to simplify complex queries into m
DROP VIEW <view name>
Used to remove a view table definition from a logical schema (data doesn't exist for views, so doesn't have to be erased).
Referential Integrity & Tables
Note that a view or base table can effectively reference other tables and so a referential action such as CASCADE or RESTRICT can be specified should its referenced table be dropped itself.
Views, External Schemas & Updating
A view can be thought of as an external schema that provides a mapping to the data in base tables determining how it should be retrieved to the application process. Using this mapping, the reverse can be possible so that insertions or updates to the exter
Updating Views
Using INSERT or UPDATE on a view doesn't affect the view itself but actually passes on these operations to its underlying base tables (if they don't violate the base table constraints).
Updating View General Rule
A view may be updated if each value in an entry of that view corresponds to exactly one entry of an identifiable underlying base table entry. E.g updating value expressions, GROUP BY and joins makes no sense. Many views can't be updated, usually just thos
Updatable View SQL Criteria
SELECT can't include DISTINCT or value expressions; FROM can only include one table, no joins; WHERE can't use a subquery that references itself; GROUP BY and HAVING can't be used; composite queries can't be used with UNION, EXCEPT, INTERSECT.
Authorisation Identifier (user id)
Unique name used to identify users of an individual SQL database. An id is needed per DB. Creation of ids depends on implementation.
Database Connection
Must be established explicitly or implicitly (e.g by a user process) before a user can do anything with a database and represents the context within which a database executes a statement - must have a defined authorisation identifier. ie. 'logging on'.
Authentication
The security process by which a user id is verified to be that of the user. E.g. via a password to the DB (not standardised) or a password into the OS.
Table Owner
Refers the the user who created that table who subsequently has rights to modify its definition.
SQL Schema
A logical schema that is one of many that make the overall database schema; belongs to a user and has that
users id as its name
. Contains the tables defined by that user which aren't automatically shared with other users but partitioned. It is actually p
Referencing a users table
Unless you are working with a database connection associated with a user it is necessary to refer to that users tables with a qualified name which is essentially using that users id as a prefix (which corresponds to the name of the SQL Schema where the ta
Views and users tables
Sometimes it is more convenient to create a view of another users table for brevity and to remove the need for qualified names. 'E.g. CREATE VIEW football_table SELECT * FROM jonathan.football_table'
Privileges (Access Control)
How SQL table owners grant access rights to other users for a given table. Includes SELECT (retrieval only), INSERT (allows insertion), DELETE (allows deletion), UPDATE (allows changing of row values), REFERENCES (allows creation of foreign keys that refe
REFERENCE privilege
Foreign key privilege is controlled due to the fact that the declaration allows referential actions such as RESTRICT which might stop the table owner from deleting their own rows (if another user had declared that table as a referenced table, hence creati
ALL PRIVILEGES
Grants a user(s) all complete access to a table for retrieval, deletion, updates, insertion or referencing. Specific columns within the table can also be specified to control access further.
GRANT <privileges>(<columns>) ON <table> TO <users>
Statement used to grant controlled access to the tables of the user currently connected to the database. E.g. 'GRANT UPDATE (name) ON footballers TO moyes, round' where update access is only given to a specific column. Column choice is only available to t
Table definitions, privileges, LPM
Privileges are part of a table definition and included in the owners schema. On SQL statement execution, the system checks that the connected user has sufficient privileges on that table to carry out the appropriate operation.
PUBLIC
Can be used in place of a list of user names to give the privilege to all users. E.g. 'GRANT SELECT, UPDATE(quota) ON course TO PUBLIC'. Note the comma separated privileges and associated column for update (column option only available for UPDATE and REFE
REVOKE <privileges>(<columns>) ON <table> FROM <users>
Reverses the granting of the given privileges to the specified user. Ie. allows a user to take access to their tables away that was previously granted. E.g. 'REVOKE DELETE ON footballers FROM round'
WITH GRANT OPTION
Used to not only give an access privilege to a user, but also the ability to grant that same privilege to others. E.g. the
owner / grantor
could write 'GRANT UPDATE(status) ON footballers TO moyes WITH GRANT OPTION'. The grantee can then pass on this priv
Views and privileges
It is possible for users to limit other users to only accessing a subset of their tables by granting access to a View instead. This gives much more fine control over access rights if certain info needs to be hidden from certain people.
Granting privileges to a view
First define the view with CREATE VIEW <name> AS <query specification>. Then grant access as normal with GRANT <privileges> ON <view name> TO <users>.
Dynamic Personalised Views
Views can be specified that make use of the USER/CURRENT_USER system variable to allow the View specification to adapt to the currently connected user. This takes advantage of the fact that view tables are dynamically created on use rather than stored. Th
USER/CURRENT_USER
SQL Variables that can be used to replace a constant with the currently connected user in a query. Very useful for creating personalised views to be accessed by other users. E.g. 'CREATE VIEW my_student AS SELECT
FROM student WHERE counsellor_no = (SELECT
Database Restructuring
Involves changes to the logical schema of a database, including additional tables (easy) or modification to existing definition (riskier). The main risk of this process is existing data loss. However, SQL allows for change to happen within the DBMS if pla
Restructuring Approaches
In some cases an ALTER TABLE statement may be enough for the restructuring. However, commonly multiple steps will be needed. E.g. to rename a column; add a new column with the target name; update that column so that it contains the data from the old colum
Data Type Changes
Sometimes it is possible to use ALTER TABLE to change the format of existing data implicitly, without creating a new column to update with new values. For example, the character length of a column could be increased from CHAR(4) to CHAR(10) without loss.
Multiple Table Change Examples
Joining tables and disposing of the old one; a union of tables followed by the dropping of one of them; splitting a table by columns to make two new tables;
Migrating
a column from one table to another. All of these are multi-step changes that require
pla
Null & Restructuring
If we were to create a new table for each category of rows in an existing table, we could copy the appropriate rows to the appropriate new table and drop the existing table once finished. However, it is always worth remembering
the spectre of null
and thi
Impact of change
Although the design of a relational DBMS is meant to help provide data independence, some restructuring of the logical schema will affect user processes and therefore require an
assessment
of what further changes are needed. Principally, view definitions
Views & Decoupling
If views are used to give users access to a database, rather than direct access to base tables then this de-coupling allows restructuring to base tables
along with edits to those view definitions
which should mean the changes have no impact on the user. E
Views defined in terms of Views
A view can be defined in terms of other views rather than directly on base tables. Sometimes this further indirection/abstraction is useful to reduce the Views dependence on the base table structure (data independence). For example if a view used data fro
View Data Independence Limitations
Due to the fact that views don't always allow table updating (e.g. in the case of a view that joins multiple tables - no one-to-one mapping of entries), they can't always be used to sustain data independence. Sometimes it will be necessary to work on base
Referential Constraint
Essentially used to ensure there are matching values between columns in different tables. Achieved through the use of foreign keys.