SQL 입문해보기 - 1

이번 시간에는 정규화가 모두 완료된 테이블을 바탕으로 SQL 구문을 작성해 보겠습니다.

이번 시간의 목표는 해당 테이블들을 묶어 하나의 스키마 내에 여러 규약들을 접합시켜 구축을 하는 것 입니다.

이제부터 테이블을 하나씩 설명드리며 변수명, 타입, 제약조건 등을 만들어 가보도록 하겠습니다. (실제 값을 넣는 것은 이후에 진행)

Employee의 속성:

1. Fname(varchar15), Minit(char), Lname(varchar15), Ssn(char9), Bdate(date), Address(varchar30), Sex(char), Salary(decimal10,2), Super_ssn(char9), Dno(int)
2. 널값을 허용하지 않는 속성: Fname, Lname, Ssn
3. 기본키: Ssn (기본키니 널값 절대 들어오면 안됨.)
4. 외래키: Super_ssn, dno (당연히 널값 허용 함.) 또한 부모 테이블 갱신시 나도 갱신 할 것(cascade)
5. 제약조건:
   1. super_ssn: 자신 테이블의 'ssn'을 참조, 상급자의 정보 갱신시 같이 갱신
   2. dno: 'department' 테이블의 'dnumber'을 참조, 해당 테이블 갱신시 같이 갱신

create table employee 
(
fname varchar(15) not null,
minit char,
lname varchar(15) not null,
ssn char(9) not null,
bdate date,
address varchar(30),
salary decimal(10, 2),
super_ssn char(9),
dno int
constraint emppk
primary key (ssn),
constraint empsuperfk
foreign key (super_ssn) references employee(ssn)
on delete cascade on update cascade
constraint empdeptfk
foreign key (dno) references department(dnumber)
on delete set default on update cascade
);

해당 테이블의 특징:
1. dno의 외래키 널값을 허용하지 않게 되면 참조 오류가 발생한다(아직 존재하지도 않는 테이블을 참조하므로)
2. Ssn 키값이 자신의 테이블 내에 Super_ssn을 참조함. 이에 따라 상속 조건을 넣어줌. 널값을 허용해도 상관 없음.

참고: Decimal의 정의

DECIMAL(precision, scale)

일 때 precison은 숫자의 전체 길이를 정하고, scale은 소수점 이하의 자릿수를 정함.

예를 들어

DECIMAL(5,2)

라고 하면 123.45 까지 저장이 가능함 (precison = 1,2,3,4,5 + scale .45)

Department의 속성:

1. Dname(varchar15), Dnumber(int), Mgr_ssn(char9), Mgr_start_date(date)
2. 널값을 허용하지 않는 속성: Dname, Dnumber, Mgr_ssn
3. 기본키: Dnumber
4. 외래키: Mgr_ssn
5. 제약조건:
   1. deptpk: dnumber의 기본키 설정
   2. deptsk: dname에 unique(중복을 허용하지 않음) 조건 설정
   3. deptmgrfk: mgr_ssn을 외래키로, employee의 ssn을 기반으로함. 해당 테이블 갱신시 같이 갱신

create table department
(
dname varchar(15) not null,
dnumber int not null,
mgr_ssn char(9) not null,
mgr_start_date date,
constraint deptpk
primary key (dnumber),
constraint deptsk
unique (dname),
constraint deptmgrfk
foreign key (mgr_ssn) references employee(ssn)
on delete set default on update cascade
);

Department 테이블이 완성되었으니, emlpoyee 테이블을 갱신해 줘야합니다.

foreign key (dno) references department(dnumber)
on delete set default on update cascade

(employee 테이블을 구성하면서 사용한 dno의 외래키 참조 문법)

추가적으로 제약조건을 넣기 위해서 다음과 같은 구문을 넣어줍니다.

alter table employee
add constraint empdeptfk foreign key (dno) references department(dumber)
on delete set default on update cascade;

Dept_Locations의 속성:

1. Dnumber(int), Dlocation(varchar15)
2. 널값을 허용하지 않는 속성: Dnumber, Dlocation
3. 기본키: Dnumber, Dlocation
4. 외래키: Dnumber
5. 제약조건:
   1. dnumber가 department의 테이블의 dnumber를 참조함. 해당 테이블 갱신시 같이 갱신

create table dept_locations
(
dnumber int not null,
dlocation varchar(15) not null,
primary key (dnumber, dlocation),
foreign key (dnumber) references department (dnumber) 
on delete cascade on update cascade
);

Project의 속성:

1. Pname(varchar15), Pnumber(int), Plocation(varchar15), Dnum(int)
2. 널값을 허용하지 않는 속성: Pname, Pnumber, Dnum
3. 기본키: Pnumber
4. 외래키: Dnum
5. 제약조건:
   1. pnumber를 기본키로 사용
   2. pname은 중복을 허용하지 않음
   3. dnum은 department의 dnumber를 참조하는 외래키임.

create table project
(
pname varchar(15) not null,
pnumber int not null,
plocation varchar(15),
dnum int not null,
primary key (pnumber),
unique (pname),
foreign key (dnum) references department (dnumber)
);

Works_on의 속성:

1. essn(char9), pno(int), hours(decimal3,1)
2. 널값을 허용하지 않는 속성: essn, pno, hours
3. 기본키: essn, pno (복합키)
4. 외래키: essn, pno
5. 제약조건:
   1. 기본 키는 복합키(essn, pno)
   2. essn은 employee 테이블의 ssn을 참조하는 외래키 이면서 복합키의 일부
   3. pno는 project 테이블의 pnumber를 참조하는 외래키 이면서 복합키의 일부

create table works_on
(
essn char(9) not null,
pno int not null,
hours decimal(3,1) not null,
primary key (essn, pno),
foreign key (essn) references employee(ssn),
foreign key (pno) references project(pnumber)
);

Dependent의 속성:

1. essn(char9), dependent_name(varchar15), sex(char), bdate(date), relationship(varchar8)
2. 널값을 허용하지 않는 속성: essn, dependent_name
3. 기본키: essn, dependent_name (복합키)
4. 외래키: essn
5. 제약조건:
   1. 기본키는 복합키 (essn, dependent_name)
   2. essn은 employee 테이블의 ssn을 참조하는 외래키 이면서 복합키의 일부

create table dependent
(
essn char(9) not null,
dependent_name varchar(15) not null,
sex char,
bdate date,
relationship varchar(8),
primary key (essn, dependent_name),
foreign key (essn) references employee(ssn)
);

이렇게 완성된 테이블들의 관계를 그림으로 다시 확인해보겠습니다.

참조 관계만을 정리해서 다시 살펴보겠습니다.

Employee 테이블

1. ssn에 관한 제약조건 (자기 테이블의 자기 속성 참조)

constraint empsuperfk
foreign key (super_ssn) references employee(ssn)
on delete cascade on update cascade

2. dno에 관한 제약조건 (department 테이블의 dnumber 참조)

constraint empdeptfk
foreign key (dno) references department(dnumber)
on delete set default on update cascade

Department 테이블
1. mgr_ssn에 관한 제약조건 (employee 테이블의 ssn 참조)

constraint deptmgrfk
foreign key (mgr_ssn) references employee(ssn)
on delete set default on update cascade

Dept_Locations 테이블
1. dnumber에 관한 제약조건 (department 테이블의 dnumber 참조)

foreign key (dnumber) references department (dnumber) 
on delete cascade on update cascade

Project 테이블
1. dnum에 관한 제약조건 (department 테이블의 dnumber 참조)

foreign key (dnum) references department (dnumber)

Works_on 테이블
1. essn에 관한 제약조건 (employee 테이블의 ssn 참조)
2. pno에 관한 제약조건 (project 테이블의 pnumber 참조)

foreign key (essn) references employee(ssn),
foreign key (pno) references project(pnumber)

Dependent 테이블
1. essn에 관한 제약조건 (employee 테이블의 ssn 참조)

foreign key (essn) references employee(ssn)

이제부터 실제 값을 넣으면 되는데, 앞서 언급된 모든 제약조건을 지킨 채로 insert into 구문을 사용해야 합니다.

이제 이 틀을 이용해서 다음 시간에는 실제로 값을 넣어보고 여러 insert, select, order by 등의 문법을 이용하는 시간을 가져보겠습니다.