(Chapter 4) Intermediate SQL

( 출처 : 연세대학교 데이터베이스 시스템 수업 (CSI6541) 강의자료 )

Chapter 4. Intermediate SQL

(1) Views

In some cases, not desirable for all users to see the entire logical model!

\(\rightarrow\) Use view

• role : enables to “hide certain data” from the view of certain users
• definition : any relation that is not of the conceptual model but is made visible to a user as a “virtual relation”

View Definition

CREATE VIEW my_view 
AS <query>

• NOT creating a new relation
• just SAVING of an EXPRESSION

Example 1)

CREATE VIEW facutly 
AS (SELECT ID, name, dept_name
   	FROM instructor);
   	
SELECT name
FROM faculty
WHERE dept_name = 'Biology'

Example 2)

CREATE VIEW dept_tot_sal (dept_name, total_salary)
AS (SELECT dept_name, sum(salary)
   	FROM instructor
   	GROUP BY dept_name);

can create “view” from “another view”

Update view

• add a new tuple to “faculty view”

INSERT INTO faculty
VALUES ('307','Green','Music')

Most SQL implementations allow updates only on simple views

• The from clause has only one relation

• The select clause contains only attribute names of relation (does not have any expressions or aggregates)

• Attributes not listed in the select clause can be set to null

• The query does not have a group by or having clause

(2) Integrity Constraints

guard against accidental damage to the DB

examples)

• A checking account must have a balance greater than $$10,000
• A salary of a bank employee must be at least $$4.00 an hour
• A customer must have a (non-null) phone number

a) Not null

b) Primary key

c) Unique

d) check (P), where P is a predicate

e) Domain constraints

CREATE TABLE 대여 (
    테이프번호	char(7),
    고객번호	char(7),
    대여일자	date,
    대여시간	time,
    대여기간	integer
);

CREATE DOMAIN Dollars numeric (12,2);
CREATE DOMAIN Pounds numeric (12,2);

• cannot assign or compare a value of type Dollars to a value of type Pounds

f) Referential integrity

• Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation
• also called “subset dependency”

CREATE TABLE instructor (
  ID		varchar(5),
  name		varchar(20),
  dept_name	varchar(20),
  salary		numeric(8,2) CHECK (salary > 29000),	
  PRIMARY KEY	(ID),
  FOREIGN KEY	(dept_name) REFERENCES department
);

CREATE TABLE department (
  dept_name	varchar(20),
  building		varchar(15),
  budget		numeric(12,2) CHECK (budget > 0),
  PRIMARY KEY	(dept_name) 
);

CREATE TABLE course (
		…
  FOREIGN KEY	(dept_name) REFERENCES department
  	ON DELETE CASCADE
  	ON UPDATE CASCADE,
  	...
	);

ON DELETE CASCADE

• 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제가 된다.

ON UPDATE CASCADE

• 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키 값이 포함된 행에서 키 값을 업데이트하려고 하면 해당 외래 키를 구성하는 모든 값도 키에 지정된 새 값으로 업데이트된다.

g) Assertions

h) Triggers

(3) Authorization

GRANT <privilege list>
ON <relation name or view name> TO <user-list>

(4) 실습

pgAdmin4 :

• powerful GUI platform for PostgreSQL

Psycopg :

• most popular PostgreSQL adapter for the Python programming language

[ Basic Commands ]

import psycopg

HOST = 'localhost'
DBNAME = 'dbms_22-2'
USER = 'mathcombio'
PASSWORD = '1234'

CONNECTION = f"host={HOST} dbname={DBNAME} user={USER} password={PASSWORD}"

with psycopg.connect(CONNECTION) as conn:
  with conn.curosor() as cur:
    cur.execute("SELECT * FROM ex_schema.ex_table")
    result = cur.fetchone()
    while result:
      print(result)
	    result = cur.fetchone()
    conn.commit() # make change in DB

with psycopg.connect(CONNECTION) as conn:
  with conn.curosor() as cur:
    # [SQL statement 1]
    cur.execute("""
    	CREATE TABLE ex_table2 (
    		num integer NOT NULL,
    		name character varying(45),
    		"studentID" integer
    		address character varying(45),
    		phone character varying(45),
    		CONSTRAINT ex_table2_pkey PRIMARY KEY (num)
    	)
    """)
    
    # [SQL statement 2]
    SQL_INSERT = """
    	INSERT INTO ex_table2(num,name,...)
    	VALUES (%s, %s, %s, %s, %s)
    """
    with conn.pipeline() as p:
      cur.execute(SQL_INSERT, (0,'Hong',1,'Seoul','010'))
      cur.execute(SQL_INSERT, (1,'Lee',2,'Seoul','011'))
      
    # [SQL statement 3]
    cur.execute("SELECT * FROM ex_table2")
    results = cur.fetchall()
    
    for result in results:
      print(result)
    
    conn.commit()      

(0,'Hong',1,'Seoul','010')
(1,'Lee',2,'Seoul','011')

Client-server message flow :

• each statement : client -> server, as a stream of request messages
• server : execute the statement & describe the results back as a stream of messages

Pipeline mode

• allows the client to combine several operations,

  in longer streams of messages to the server,

  then to receive more than one response in a single batch