Scribe: Lokesh Menghani and Hanieh Soleimani
Grades
Student | Grade |
Robert | 10 |
Jim | 9 |
ECTs
Student | Points |
Robert | 6 |
Jim | 6 |
As we can see the data in the table "ECTs" is dependent on the table of "Grades". If for example Jim got a 0 for his grades instead of 9 then his points should be changed to 0.
Integers
Real Numbers
Character Strings
Bit Strings
Dates and Times
Name of the table
Name of the columns
Domains of the columns
NOT NULL
e.g. CREATE TABLE Plant ( P CHAR(10) NOT NULL
This specifies that the values in these columns must not be NULL.
CONSTRAINTS
e.g. CONSTRAINT C_20 PRIMARY KEY (P)
C_20 here is the name of the constraint which is just an example. This name will be used later to ALTER or DROP by refering to its name. Constraint name should reflect something about the constraint.
PRIMARY KEY
e.g. CONSTRAINT C_20 PRIMARY KEY (P)
Primary key could be several columns and this requires that it must not be NULL
UNIQUE
e.g. CONSTRAINT C_30 UNIQUE (Pcity, Profit)
PCITY,Profit is a candidate key
Its columns are not required to be NOT NULL.
CHECK
e.g. CONSTRAINT C_50 CHECK ( (Pcity <> 'Chicago') OR (Profit > 1000) )
Checks if a condition between attributes, e.g. (Pcity <> Pname) OR (Profit > 1000) is satisfied. The condition is satisfied, when it is either TRUE or UNKNOWN
DEFAULTS
e.g. Cname CHAR VARYING(10) DEFAULT (NULL)
e.g. P CHAR(10) DEFAULT ('Main')
This sets default values from before for example if no value is inserted for CNAME then the default value is NULL. Same for PCHAR if no value is inserted the the default value is 'Main'.
FOREIGN KEY
e.g. CONSTRAINT C_70 FOREIGN KEY (P) REFERENCES Plant ON DELETE SET NULL
This means that P has a non NULL value and this value of P appears in Plant or P is NULL.
ON DELETE SET NULL
e.g. CONSTRAINT C_70 FOREIGN KEY (P) REFERENCES Plant ON DELETE SET NULL
If P listed in some tuple of Customer is deleted from Plant (that is the tuple with this value of primary key is deleted), then that value of P in Plant is replaced by NULL.
ON DELETE CASCADE
e.g. CONSTRAINT C_90 FOREIGN KEY (C) REFERENCES Customer ON DELETE CASCADE
If C listed in some tuple of Invoice is deleted from Customer (that is the tuple with this value of primary key is deleted), all the tuples with this value of C in Invoice must be deleted.
The constraint will act when:
An INSERT or an UPDATE
A DELETE or an UPDATE
Default is NO ACTION
e.g. ALTER TABLE Plant ADD CONSTRAINT C_20 PRIMARY KEY (P);
Foreign Key can refer to UNIQUE and not only to PRIMARY KEY
HUSBAND | |
PK | Hname |
FK1 | Wname |
WIFE | |
PK | Wname |
FK1 | Hname |
We cannot define husband before wife and niether wife before husband as it is a circular "dependency"