CSED421Database Systems Lab

ConstraintsGroup functions

Connect to linux server brynn.postech.ac.kr Id : student pw : student

Connect to sql Type in terminal : mysql -u [hemos ID] –p Pw : student id

Connect to mysql server

Introduction

Condition that must be true for any instance of databases Specified when schema is defined Checked when relations are modified

5 types of constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK

Integrity Constraints(ICs)

Prohibits a database value from being null. null : either unknown or not applicable

To satisfy a NOT NULL constraint,every row in the table must contain a value for the col-umn.

Create table with NOT NULL constrained attribute CREATE TABLE Students (

name CHAR(20) NOT NULL, … … …);

Give NOT NULL constraint to existing table ALTER TALBE Students MODIFY name CHAR(20) NOT NULL;

Remove NOT NULL constraint ALTER TALBE Students MODIFY name CHAR(20);

NOT NULL Constraints

Prohibits multiple rows from having the same value in the same column or combination of columns, but allows some values to be null

Create table with UNIQUE constrained attribute CREATE TABLE Students (

login CHAR(10) UNIQUE, … … …);

Give UNIQUE constraint to existing table ALTER TABLE Students ADD UNIQUE (login); ALTER TABLE Students MODIFY login CHAR(10) UNIQUE;

Remove UNIQUE constraint ALTER TABLE Students DROP INDEX login; ALTER TABLE Students MODIFY login CHAR(10);

UNIQUE Constraints

Prohibits multiple rows from having the same value in the same column or combination of columns, and prohibits values from being null

NOT NULL constraint + UNIQUE constraint

Create table with PRIMARY KEY CREATE TABLE Students (

sid CHAR(20) PRIMARY KEY, … … …); CREATE TABLE Students (

sid CHAR(20),… … …PRIMARY KEY (sid));

Give PRIMARY KEY constraint to existing attribute ALTER TABLE Students ADD PRIMARY KEY (sid); ALTER TABLE Students MODIFY sid CHAR(20) PRIMARY KEY;

Remove PRIMARY KEY constraint ALTER TABLE Students DROP PRIMARY KEY; ALTER TABLE Students MODIFY sid CHAR(20)

PRIMARY KEY Constraints

Values in one table must appear in another table

Create table with FOREIGN KEY CREATE TABLE Enrolled (

sid CHAR(20), FOREIGN KEY (sid) REFERENCES Students (sid));

CREATE TABLE Enrolled (sid CHAR(20) REFERENCES Students (sid));

Give FOREIGN KEY constraint to existing attribute ALTER TABLE Enrolled ADD FOREIGN KEY (sid) REFERENCES Students

(sid);

Remove FOREIGN KEY constraint to existing attribute ALTER TABLE Enrolled DROP FOREIGN KEY constraint_name;

Confirm whether two columns are linked SHOW CREATE TABLE Enrolled; SELECT * FROM information_schema.KEY_COLUMN_USAGE;

FOREIGN KEY Constraints

Referential actions What if referenced table is deleted or updated, 5 different actions take

place CASCADE

changes from the parent table and automatically adjust the matching rows in the child table

NO ACTION integrity check is done after trying to alter the table

RESTRICT Rejects the delete or update operation for the parent table

SET DEFAULT, SET NULL

FOREIGN KEY (sid) REFERENCES Students (sid) ON UPDATE cascade ON DELETE restrict

FOREIGN KEY Constraints

Requires a value in the database to comply with a speci-fied condition

Create table with CHECK constraint CREATE TABLE Students (

… … … , age INTEGER CHECK (age > 0));

Give CHECK constraint to existing attribute ALTER TABLE Students ADD CHECK (age > 0);

Change CHECK constraint ALTER TABLE Students MODIFY age CHECK (age > 0);

In MySQL, use TRIGGER instead “The CHECK clause is parsed but ignored by all storage en-

gines.”

CHECK Constraints

Page 11

1. 다음의 IC 를 만족하는 두 테이블을 생성하라 Table Customer

id varchar(20) ame varchar(20) pw varchar(10) age integer Address varchar(20)

Table Orders customer_id varchar(20) customer_addr varchar(20) amout integer

Example of ICs

Constraints of Customer Id is unique and not null Name is not null Age must be bigger than 0

Constraints of Orders Customer id references id of

customer table

CREATE TABLE Customer( id VARCHAR(20) PRIMARY KEY, name VARCHAR(20) NOT NULL, pw VARCHAR(10) age INTEGER CHECK(age>0), address VARCHAR(20) );

CREATE TABLE Orders( customer_id VARCHAR(20) REFERENCES Customer(id), customer_addr VARCHAR(20), amount INTEGER,);

Example of ICs

Group function

GROUP BY Sort the data with distinct value for data of specified columns

Usage form of GROUP BY Select column from table

[where condition][GROUP BY column[, column2, …]][order by column [ASC|DESC]

Table DevelopTeam

Select job Select job,salary from DevelopTeam from DevelopTeam group by job group by job,salary

GROUP BY

Page 15

Group by clause is usually used with aggregate function(min, max, count, sum, avg)

Find the job and average salary of each jobs Select job, avg(salary) from DevelopTeam group by job;

Find the job and largest salary of each jobs Select job, max(salary) from DevelopTeam group by job;

GROUP BY

Page 16

Giving condition on data is applied with group by clause

Usage form of HAVING clause SELECT column1

FROM table[WHERE condition][GROUP BY column2][HAVING group_function_condition][ORDER BY column3 [ASC|DESC]]

Find the job and average salary of all job whose average salary is greater than 350 Select job, avg(salary) from DevelopTeam group by job having avg(salary)>350;

HAVING clause

Page 17

Select job, avg(salary) from DevelopTeam where salary>350 ⋯⋯ ① group by job ⋯⋯ ② having avg(salary)>350; ⋯⋯ ③

Note : Where is applied before grouping Having is applied after grouping

→aggregate function can be used only with having clause

Difference between WHERE and HAVING

① ②

③

Page 18

각 직업별 연봉이 300 이상인 사람수를 검색하시오 Select job, count(*) as ‘num of person’

from DevelopTeamwhere salary>=300group by job;

각 직업별 연봉의 최소값이 400 이상인 직업을 검색하시오 Select job, min(salary)

From DevelopTeamGroup by jobHaving min(salary)>=400;

Example

Page 19

1. 다음의 IC 를 만족하는 두 테이블을 생성하라 Table Course

cName varchar(20) language varchar(20) room varchar(30)

Table Enrolled cName varchar(20) sName varchar(20) gpa float department varchar(20) midterm int final int

Practice

Constraints of Course Course name is primary key

Constraints of Enrolled Course name references

course’s course name Department is not null Midterm and final must lie in

0~100

Page 20

insert into Course values('DB','english','PIRL 142'), ('AI','english','B4 101'), ('PL','korean','B2 102');

insert into Enrolled values('DB','a',3.3,'CSE',80,90), ('DB','b',4.0,'CSE',85,70), ('DB','c',3.9,'MGT',75,85), ('DB','d',3.1,'MGT',70,80), ('DB','e',4.1,'MTH',90,100), ('AI','a',3.3,'CSE',90,70), ('AI','g',3.3,'CSE',95,75), ('AI','h',3.2,'CSE',85,80), ('PL','a',3.3,'CSE',65,95), ('PL','e',4.1,'MTH',100,100), ('PL','k',3.4,'MTH',75,90), ('PL','i',2.7,'MGT',55,70);

Insert data into table

<course>

<enrolled>

Page 21

2. Enrolled 테이블에서 각 과목별로 몇 명의 수강생이 있는지를 검색하시오 . 결과는 과목명과 수강생 수를 출력

3. Enrolled 테이블에서 각 과목별 학점이 4.0 이상인 학생수를 검색하시오 결과는 과목명 , 학생 수 (column 명을 numStu 로 표현 ) 를 출력

Practice

Page 22

4. Enrolled 테이블에서 수강생이 4 명 이상인 과목의 중간고사 평균을 구하시오 . 결과는 과목명 , 수강생 수와 중간고사 평균을 출력

5. Enrolled 테이블에서 CSE 학생들의 각 과목별 기말고사의 최고점을 검색하시오 . 결과는 과목명과 점수를 출력 . 단 , 최고점이 90 점 이상일 때만 출력

Practice

6. Enrolled 테이블에서 과목별 , 학과별 중간고사 , 기말고사 평균을 검색하시오 . 결과는 과목명 , 학과명과

중간고사 , 기말고사 평균 출력

Practice