Data Management Class Notes for 24/3/2014

Data Definition Language And Data Control Language For Relational Databases

• Overview of Reference Integrity

  • 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.

  
  
  
• Data Domains

  • Integers

  • Real Numbers

  • Character Strings

  • Bit Strings

  • Dates and Times

  
  
  
• Minimum Specification for on table

  • Name of the table

  • Name of the columns

  • Domains of the columns

  
  
  
• Keywords

  • 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.

  
  
  
• Maintenance of Referential Integrity on Update

  • The constraint will act when:

  • An INSERT or an UPDATE

  • A DELETE or an UPDATE

  
  

  • Default is NO ACTION

  
  
  
• Altering The Definition To Add Constraints

  • e.g. ALTER TABLE Plant ADD CONSTRAINT C_20 PRIMARY KEY (P);

  
  
  
• Referencing Unique

  • Foreign Key can refer to UNIQUE and not only to PRIMARY KEY

  
  
  
  • First Define Table and then Add Constraints

    • 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"