slide - Information & Database Systems
Download
Report
Transcript slide - Information & Database Systems
CSED421
Database Systems Lab
Constraints
Group functions
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
Introduction
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
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
Give NOT NULL constraint to existing table
CREATE TABLE Students (
name CHAR(20) NOT NULL, … … …);
ALTER TALBE Students MODIFY name CHAR(20) NOT NULL;
Remove NOT NULL constraint
ALTER TALBE Students MODIFY name CHAR(20);
UNIQUE 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
Give UNIQUE constraint to existing table
CREATE TABLE Students (
login CHAR(10) UNIQUE, … … …);
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);
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
… … …);
Give PRIMARY KEY constraint to existing attribute
CREATE TABLE Students (
sid CHAR(20) PRIMARY KEY,
CREATE TABLE Students (
sid CHAR(20),… … …
PRIMARY KEY (sid));
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)
FOREIGN KEY Constraints
Values in one table must appear in another table
Create table with FOREIGN KEY
Give FOREIGN KEY constraint to existing attribute
ALTER TABLE Enrolled ADD FOREIGN KEY (sid) REFERENCES Students
(sid);
Remove FOREIGN KEY constraint to existing attribute
CREATE TABLE Enrolled (
sid
CHAR(20),
FOREIGN KEY (sid) REFERENCES Students (sid));
CREATE TABLE Enrolled (
sid CHAR(20) REFERENCES Students (sid));
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
NO ACTION
integrity check is done after trying to alter the table
RESTRICT
changes from the parent table and automatically adjust the matching rows in the
child table
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
CHECK Constraints
Requires a value in the database to comply with a specified condition
Create table with CHECK constraint
Give CHECK constraint to existing attribute
ALTER TABLE Students ADD CHECK (age > 0);
Change CHECK constraint
CREATE TABLE Students (
… … … , age INTEGER CHECK (age > 0));
ALTER TABLE Students MODIFY age CHECK (age > 0);
In MySQL, use TRIGGER instead
“The CHECK clause is parsed but ignored by all storage engines.”
Example of ICs
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
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
Page 11
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
customer_addr
amount
);
VARCHAR(20) REFERENCES Customer(id),
VARCHAR(20),
INTEGER,
Group function
GROUP BY
Sort the data with distinct value for data of specified columns
Usage form of GROUP BY
Select
from
[where
[GROUP BY
[order by
column
table
condition]
column[, column2, …]]
column [ASC|DESC]
GROUP BY
Table DevelopTeam
Select job
from DevelopTeam
group by job
Select job,salary
from DevelopTeam
group by job,salary
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;
Page 15
HAVING clause
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;
Page 16
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 used only with having clause
Page 17
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;
Page 18
Practice
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
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 19
Insert data into table
<course>
<enrolled>
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);
Page 20
Practice
2. Enrolled 테이블에서 각 과목별로 몇 명의 수강생이 있는지를 검색
하시오.
결과는 과목명과 수강생 수를 출력
3. Enrolled 테이블에서 각 과목별 학점이 4.0이상인 학생수를 검색하
시오
결과는 과목명, 학생 수(column명을 numStu로 표현)를 출력
Page 21
Practice
4. Enrolled 테이블에서 수강생이 4명 이상인 과목의 중간고사 평균을
구하시오.
결과는 과목명, 수강생 수와 중간고사 평균을 출력
5. Enrolled 테이블에서 CSE 학생들의 각 과목별 기말고사의 최고점을
검색하시오.
결과는 과목명과 점수를 출력.
단, 최고점이 90점 이상일 때만 출력
Page 22
Practice
6. Enrolled 테이블에서 과목별, 학과별 중간고사, 기말고사 평균을 검
색하시오.
결과는 과목명, 학과명과
중간고사, 기말고사 평균 출력