CSCI 343 SQL — The final points

Constraints

SQL provides a way to place constraints on the attributes of a table when it is created. Here’s a few CREATE TABLE statements with lots of constraints.

CREATE TABLE R(A INT, B INT, C TEXT NOT NULL, D TEXT UNIQUE,
         PRIMARY KEY(A, B),
         FOREIGN KEY(D) REFERENCES T(X)) ;
create table Highschooler(ID int, name text, grade int,
         PRIMARY KEY(ID)) ;
create table Friend(ID1 INT, ID2 INT,
         FOREIGN KEY(ID1) REFERENCES Highschooler(ID),
         FOREIGN KEY(ID2) REFERENCES Highschooler(ID)) ;

The PRIMARY KEY constraint states that a collection, usually one, of attributes uniquely identifies each row of the tab.

The FOREIGN KEY constraint is similar to a pointer declaration. It states the an attribute “points to” a row of another table. That is, that attribute’s value is also an attribute value within another table.

The UNIQUE and NOT NULL attributes are self explanatory.

If a row is deleted, inserted, or updated so that a constraint is no longer satisfied, the database is supposed to indicate an exception and abort the operation.

Most SQL systems make an effort to create and maintain indexes on attributes used for keys.

Finally, some database professionals believe that these constraints should be supported in the middleware, rather than in SQL.

References from sqlite

Examples from Stanford database course

Many database managements systems to not support CHECK.

create table Student(sID int primary key, sName text, GPA real, sizeHS int);

create table College(cName text, state text, enrollment int,
                     primary key (cName,state));

create table Apply(sID int, cName text, major text, decision text,
                   unique(sID,cName), unique(sID,major));

create table Student(sID int, sName text,
                     GPA real check(GPA <= 4.0 and GPA > 0.0),
                     sizeHS int check(sizeHS < 5000));

Views

SQL allows the creation of views that provide a way of looking at several joined tables as a single table.

CREATE VIEW HighschoolFriends(name1 TEXT, name2 TEXT) AS
SELECT H1.name AS name1, H2.name AS name2
FROM Higschooler H1, Highschooler H2, Friend F
WHERE H1.ID = F.ID1 AND H2.ID = F.ID2 and H1.name < H2.name ;

Views provide a way of denormalizing relations and of looking at selected rows of a database. Most “users” of the database, human and program, only access the database through views. Views also provide a means of protecting internal details of the databases, similar to how private variables hide implementation details in object-oriented programming.

Obviously, the updating of a view must be done very carefully.

References from sqlite

Examples from Stanford database course

create view CSaccept as
select sID, cName
from Apply
where major = 'CS' and decision = 'Y';

create view CSberk as
select Student.sID, sName, GPA
from Student, CSaccept
where Student.sID = CSaccept.sID and cName = 'Berkeley' and sizeHS > 500;

Triggers

Triggers are sequences of SQL commands that intercept databases updates and perform a programmer specified actions. For example, when a view is “updated,” a trigger may be called to update the many tables used in the view. They are difficult to program.

Triggers use the identifiers NEW and OLD to refer to the present and hoped-for values of updated attributes.

References from sqlite

Examples from Stanford database course

create trigger CSacceptDelete
instead of delete on CSaccept
for each row
begin
  delete from Apply
  where sID = Old.sID
  and cName = Old.cName
  and major = 'CS' and decision = 'Y';
end;

Transactions

Database systems support a transaction mechanism to make sure that several related SQL commands are executed either completely or not at all. That is the query passes the ACID test: Atomic, Consistent, Isolated, and Durable.

The implementation of transactions varies widely in SQL database management systems. There are even variations in the syntax used to specify transactions.

Transactions is a topic for another lecture in which the transaction mechanisms of SQL will be compared to those of journaled based file system such as Windows NTFS and Linux ext4.

References from sqlite

Access privileges

Almost all database support the SQL GRANT statement which can be used to control access (SELECT, INSERT and UPDATE) to relations and attributes of a database. Because sqlite depends totally on the file access control provided by the operating system, it does not have GRANT and REVOKE commands.

Database system administrators usually execute the GRANT and REVOKE commands. Database developers stick with the other SQL commands.

References from MySQL

How your database was created

create database brockjdDB ;
grant all on brockjdDB.* TO 'brockjd'@'%' ;