Constraints Group functions

1. CSED421Database Systems Lab ConstraintsGroup functions

2. Connect to mysql server • 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

3. Introduction

4. Integrity Constraints(ICs) • 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

5. NOT NULL Constraints • 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 column. • 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);

6. UNIQUE Constraints • Prohibits multiple rows from having the same value in the same column or combination of columns, but allowssome values to be null • Create table with UNIQUEconstrained attribute • CREATE TABLE Students (login CHAR(10) UNIQUE, … … …); • Give UNIQUEconstraint to existing table • ALTER TABLE Students ADD UNIQUE(login); • ALTER TABLE StudentsMODIFY login CHAR(10) UNIQUE; • Remove UNIQUEconstraint • ALTER TABLE Students DROP INDEX login; • ALTER TABLE Students MODIFY login CHAR(10);

7. PRIMARY KEY 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 ( sidCHAR(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)

8. FOREIGN 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;

9. 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) REFERENCESStudents (sid) ON UPDATE cascade ON DELETE restrict

10. CHECK Constraints • Requires a value in the database to comply with a specified 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 engines.”

11. Example of ICs • 1. 다음의 IC를 만족하는 두 테이블을 생성하라 • Table Customer • id varchar(20) • amevarchar(20) • pw varchar(10) • age integer • Address varchar(20) • Table Orders • customer_idvarchar(20) • customer_addrvarchar(20) • amout integer • 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

12. Example of ICs • 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,);

13. 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]

14. GROUP BY • Table DevelopTeam • Select job Select job,salary from DevelopTeamfrom DevelopTeam group byjob group byjob,salary

15. GROUP BY • 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;

16. HAVING clause • Giving condition on data is applied with group by clause • Usage form of HAVING clause • SELECT column1FROM 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;

17. Difference between WHERE and HAVING • 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 usedonly with having clause ① ② ③

18. Example • 각 직업별 연봉이 300이상인 사람수를 검색하시오 • Select job, count(*) as ‘num of person’ from DevelopTeam where salary>=300 group by job; • 각 직업별 연봉의 최소값이 400이상인 직업을 검색하시오 • Select job, min(salary) From DevelopTeam Group by job Having min(salary)>=400;

19. Practice • 1. 다음의 IC를 만족하는 두 테이블을 생성하라 • Table Course • cNamevarchar(20) • language varchar(20) • room varchar(30) • Table Enrolled • cNamevarchar(20) • sNamevarchar(20) • gpa float • department varchar(20) • midterm int • final int • Constraints of Course • Course name isprimary key • Constraints of Enrolled • Course name references course’s course name • Department is not null • Midterm and final must lie in 0~100

20. Insert data into table • 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); <course> <enrolled>

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

22. Practice • 4. Enrolled 테이블에서 수강생이 4명 이상인 과목의 중간고사 평균을 구하시오. • 결과는 과목명, 수강생 수와 중간고사 평균을 출력 • 5.Enrolled 테이블에서 CSE 학생들의 각 과목별 기말고사의 최고점을 검색하시오. • 결과는 과목명과 점수를 출력. • 단, 최고점이 90점 이상일 때만 출력

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