|
q회사 ER 스키마는 관계 데이터베이스에서 총 9개의 릴레이션으로 사상되었음
EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary, Dno)
PROJECT(Projno, Projname, Budget, Manager)
DEPARTMENT(Deptno, Deptname, Floor)
SUPPLIER(Suppno, Suppname, Credit)
PART(Partno, Partname, Price, Subpartno)
DEPENDENT(Empno, Depname, Sex)
WORKS_FOR(Empno, Projno, Duration, Responsibility)
SUPPLY(Suppno, Projno, Partno, Quantity)
PROJ_LOC(Projno, Location)
인데emppyee랑 project까지는 되는데 이후로는 안되네요 ㅠ
create table department(
deptno number not null,
deptname char(20),
floor number,
primary key(deptno)
);
create table employee(
empno char(10) not null,
empname char(20),
title char(10) ,
city char(10),
ku char(10),
dong char(10),
salary number,
dno number,
primary key(empno),
foreign key (dno) references department(deptno)
);
create table project(
projno char(4) not null,
projname char(40),
budget number,
startdate char(20),
manager char(10),
primary key (projno),
foreign key (startdate) references project(manager),
foreign key (manager) references employee(empno)
);
create table supplier(
suppno char(4) not null,
suppname char(20),
credit number,
primary key(suppno)
);
create table part(
partno number,
partname char(20),
price number,
subpartno number,
primary key(partno)
);
create table dependents(
empno char(4) not null,
depname char(20) not null,
sex char(20),
primary key(empno, depname) ,
foreign key (empno) references employee(empno)
);
create table work_for(
empno char(4) not null,
projno char(20),
duration char(20),
responsibility char(20),
primary key(empno, projno),
foreign key (empno) references employee(empno),
foreign key (projno) references project(projno)
);
create table supply(
suppno char(4) not null,
projno char(4) not null,
partno char(4) not null,
quantity number,
primary key(suppno, projno, partno),
foreign key (suppno) references supplier(suppno),
foreign key (projno) references project (projno),
foreign key (partno) references part(partno)
);
create table proj_loc(
projno, char(4) not null,
location char(20),
primary key(projno, location),
foreign key (projno) references project(projno)
); |
