SQL Tutorial

Views

Let us use the exercise from last week to see how views can be used for logical data independence.

Getting started

Move into last week’s directory and load the UniversityDB:

$ cd ~/cs44/
$ cd exercises
$ cd week04
$ sqlite3 universityDB

Set up settings for sqlite:

> .mode column
> .header ON
> .separator '$'
> PRAGMA foreign_keys=ON;

Run .schema. Do you see a Student TABLE? If not, we can reCREATE it quickly:

> CREATE TABLE Student( id INTEGER PRIMARY KEY, name CHAR(20), major CHAR(20), level CHAR(5), age integer);
> .import '/home/soni/public/cs44/universityDB/student_start' Student

Query your TABLE:

> SELECT * FROM Student;
id          name        major       level       age       
----------  ----------  ----------  ----------  ----------
1111        Mo          CS          SO          20        
1122        Peter       English     JR          20        
1234        Tyler       Math        FR          18        
2222        Jo          Math        SR          21        
2323        Jo          Math        JR          22        
3333        Tanya       Math        JR          21        
4444        Malik       CS          JR          20        
4545        Sarah       English     SR          21        
5555        Chris       Math        FR          19        
6666        Charles     English     FR          18        
6767        Sarah       Math        FR          18        
7777        Josh        CS          SO          19        
7878        Heather     Math        SR          22        
8888        Elmo        CS          SO          20        
9999        Jo          Math        FR          19

Create a Student View

Views provide an external schema that abstracts away the underlying relational model. This has many advantages, one of which is data access control. For example, imagine we want to give a user query access to student data, but we do not want them to view sensitive information such as the age and ID of a student. We can define a view that limites the information that is visible:

> CREATE VIEW StudentView AS SELECT name, major, level FROM Student;

with the result:

> SELECT * FROM StudentView;
name        major       level     
----------  ----------  ----------
Mo          CS          SO        
Peter       English     JR        
Tyler       Math        FR        
Jo          Math        SR        
Jo          Math        JR        
Tanya       Math        JR        
Malik       CS          JR        
Sarah       English     SR        
Chris       Math        FR        
Charles     English     FR        
Sarah       Math        FR        
Josh        CS          SO        
Heather     Math        SR        
Elmo        CS          SO        
Jo          Math        FR

Note that this is not a copy of the Student data, just an abstraction. For example, if we insert a value into the Student TABLE, it will appear in the StudentView as well:

> INSERT INTO Student VALUES (1010, "Ameet", "CS", "FAC", 36);
> SELECT * FROM Student WHERE age > 30;
id          name        major       level       age       
----------  ----------  ----------  ----------  ----------
1010        Ameet       CS          FAC         36   
> SELECT * FROM StudentView;
name        major       level     
----------  ----------  ----------
Ameet       CS          FAC       
Mo          CS          SO  
...

Exercise: CS View

Imagine wanting to give CS faculty a view that only includes their majors. Define a CS view that has only students with CS majors, and it just displays their name and level.

Solution:

> CREATE VIEW CSView AS SELECT name, level FROM Student WHERE major='CS';
> select * from CSView;
name        level     
----------  ----------
Ameet       FAC       
Mo          SO        
Malik       JR        
Josh        SO        
Elmo        SO

Converting ER Models to Relational Models

We will consider two exercises that help us understand how to model key constraints and participation constraints. Then we will finish the worksheet from last week to model Employees and Departments. Here is the SQL code to define your solution (be sure to try writing this out first):

CREATE TABLE Child(
    name VARCHAR,
    age INTEGERS,
    parent_ssn CHAR(9),
    PRIMARY KEY (name, parent_ssn),
    FOREIGN KEY (parent_ssn) REFERENCES Employee(ssn) ON DELETE CASCADE
		ON UPDATE CASCADE
)

CREATE TABLE Employee(
  ssn CHAR(9) PRIMARY KEY,
  salary REAL,
  phone CHAR(10)
)

CREATE TABLE Department(
  dno INTEGER PRIMARY KEY,
  dname VARCHAR,
  budget REAL,
  managerSSN CHAR(9) NOT NULL,
  FOREIGN KEY (managerSSN) REFERENCES Employee(ssn) ON DELETE NO ACTION
)

CREATE TABLE WorksIn(
  dno INTEGER REFERENCES Department(dno),
  ssn CHAR(9) REFERENCES Employee(ssn),
  PRIMARY KEY(dno, ssn)
)