SQL2

Lab Materials

This lab will use the following data:

Rider

Name Age Gender
Lindsay 16 Female
Tom 21 Male
Eric 33 Male
Marco 13 Male
Kristopher 45 Male
Kelly 21 Female
Mitchell 24 Male
Tammy 30 Female
Brad 18 Male

Make

Name Make
Lindsay Honda
Lindsay Yamaha
Tom Honda
Eric Honda
Eric Yamaha
Marco Honda
Marco Yamaha
Marco Kawasaki
Marco Suzuki
Marco KTM
Mitchell Honda
Mitchell Yamaha
Kelly Honda
Kristopher Honda
Kristopher Yamaha
Kristopher Kawasaki
Tammy Honda
Tammy Yamaha
Brad Honda
Brad Yamaha

Track

Track Type Bike
Carters Racetrack dirt Honda
Carters Racetrack dirt Yamaha
RaceTrack Empire asphalt Honda
RaceTrack Empire asphalt Kawasaki
Dirt Roads dirt Honda
Dirt Roads dirt Kawasaki
Dirt Roads dirt KTM
Dirt Roads dirt Suzuki
Speedy Pavement asphalt Honda
Speedy Pavement asphalt KTM
Speedy Pavement asphalt Yamaha
Expressways asphalt Honda
Expressways asphalt Yamaha
Expressways asphalt Kawasaki
Expressways asphalt KTM
Expressways asphalt Suzuki
Georgia ATV Trails dirt Honda
Georgia ATV Trails dirt KTM
Georgia ATV Trails dirt Suzuki

Creek Data

Creek Name Alky Ph Tot P
Balus Creek NULL 6.8 0.125
Balus Creek 44 6.8 -0.01
Flat Creek 34 7.3 -0.01
Limestone Creek 7 NULL 0.019

Lab Content

Objective

Students use Python to interact with a relational database management system.

Procedure

Working with Check Constraints

  1. We can add a check constraint using the SQL as follows:

    cur.execute("""CREATE TABLE creek_data(
                name varchar,
                alky float CHECK (alky > 20),
                ph float,
                tot_p float CHECK (tot_p > 20);""")
    
  2. Now insert the data from the Creek Data relation. Check constraint can refer to several columns. If we only wanted data that had a lower alky than tot_p we would do it as follows:

    cur.execute("""CREATE TABLE creek_data(
               name varchar,
               alky float CHECK (alky > 20),
               ph float CHECK (ph < 20),
               tot_p float CHECK (tot_p < 1) ,
               CONSTRAINT alt CHECK (alky < tot_p));""")
    
  3. We can add a check constraint and give it a name using the SQL as follows:

    cur.execute("""CREATE TABLE creek_data(
                   name varchar,
                   alky float CONSTRAINT positive_alky CHECK(alky > 0),
                   ph float,
                   tot_p float);""")
    

Not Null Constraints

  1. We can can create columns that will except NULL for a value or that will except not except NULL for a value. NULL and NOT NULL can be used in conjunction with CONSTRAINTS as show in the SQL below:

    cur.execute("""CREATE TABLE creek_data(
               name varchar NOT NULL,
               alky float NOT NULL CONSTRAINT positive_alky CHECK(alky > 0),
               ph float NULL,
               tot_p float);""")
    

Unique Constraints

  1. A unique constraint that the data for the column with the constraint is unique. We can do this using the following SQL:

    cur.execute("""CREATE TABLE creek_data(
               name varchar UNIQUE,
               alky float,
               ph float,
               tot_p float);""")
    
  2. Notice if you change the name of Balus Creek to Shallow Creek, the parameters have been met. If you would like to put a unique constraint on more than one column you could use the following SQL:

    cur.execute("""CREATE TABLE creek_data(
               name varchar,
               alky float,
               ph float,
               tot_p float,
               UNIQUE(name, alky);""")
    
  3. Like we did with constraint, you can give your UNIQUE constraint a name:

    cur.execute("""CREATE TABLE creek_data(
           name varchar CONSTRAINT must_be_unique UNIQUE,
           alky float,
           ph float,
           tot_p float,
           UNIQUE(name, alky);""")
    

Primary Keys Constraint

  1. Primary Keys are the same as UNIQUE NOT NULL:

    cur.execute(“”“CREATE TABLE creek_data(

    name varchar UNIQUE NOT NULL, alky float, ph float, tot_p float);”“”)

    cur.execute(“”“CREATE TABLE creek_data(

    name varchar PRIMARY KEY, alky float, ph float, tot_p float);”“”)

Notice the error says Key(name)=(Balus Creek) already exists If we change our SQL to say PRIMARY the results are the same.
  1. We can also have more than one primary key:

    cur.execute("""CREATE TABLE creek_data(
               name varchar,
               alky float,
               ph float,
               tot_p float,
               PRIMARY KEY(name, alky));""")
    

Foreign Keys Constraint

  1. A foreign key constraint allows us to link tables together based on a common columns:

    cur.execute("""CREATE TABLE rider(
                   name varchar PRIMARY KEY,
                   age integer,
                   gender varchar);""")
    
    cur.execute("""CREATE TABLE make(
                   name varchar REFERENCES rider(name),
                   make varchar);""")
    
    #Or we can shorten it
    cur.execute("""CREATE TABLE make(
                   name varchar REFERENCES rider,
                   make varchar);""")
    
  2. We can also use a foreign key to constrain and reference more than one column. The tables we created for this assignment do not have enough columns in common to be able to use more than one reference, but if we were to use more than reference we would use Python as follows:

    cur.execute("""CREATE TABLE example1(
        a1 integer PRIMARY KEY,
        b1 integer,
        c1 integer,
        FOREIGN KEY (b1, c1) REFERENCES other_table(b2, c2)
        );""")
    #Note: number of constrained columns must match number and type of referenced columns
    
  3. A table may contain more than one foreign key constraint. An example would be creating a new table containing the riders name and the names of the riders from the make table. We can do this using Python as follows:

    cur.execute("""CREATE TABLE riders(
                   name_rider varchar PRIMARY KEY,
                   age integer,
                   gender varchar
                );""")
    
    cur.execute("""CREATE TABLE make(
                   name_make varchar PRIMARY KEY,
                   make varchar
               );""")
    
    cur.execute("""CREATE TABLE riderMake(
                   name_rider varchar REFERENCES riders,
                   name_make varchar REFERENCES make,
                   PRIMARY KEY(name_rider, name_make)
                );""")
    
  4. We can set up constraints that will not allow users to delete a table that references another table or that will delete the data from both tables. We can do this using Python as follows:

    cur.execute("""CREATE TABLE riders(
                   name_rider varchar PRIMARY KEY,
                   age integer,
                   gender varchar
                );""")
    
    cur.execute("""CREATE TABLE make(
                   name_make varchar PRIMARY KEY,
                   make varchar
               );""")
    
    cur.execute("""CREATE TABLE riderMake(
                   name_rider varchar REFERENCES riders ON DELETE RESTRICT,
                   name_make varchar REFERENCES make ON DELETE CASCADE,
                   PRIMARY KEY(name_rider, name_make)
                );""")
    

References

Update soon.

#Adding this line to try and get changes to push. Metadata ======== | Title:Relational Algebra | CreationDate:12/16/2013 | SoftwareUsed:None | SoftwareVersion: None | Contributors: Zac Miller, Crystal Lyliston