CHƯƠNG IV

HIỆN THỰC HỆ THỐNG

 2.PHYSICAL FILES

 KHỞI TẠO FILE GIAOVIEN

 CREATE TABLE GIAOVIEN(
 MAGV CHAR(3) NOT NULL,
 HOTEN VARCHAR2(30) NOT NULL);
 ALTER TABLE GIAOVIEN ADD(
CONSTRAINT GV_KC
PRIMARY KEY (MAGV)
USING INDEX PCTFREE 10);
ALTER TABLE GIAOVIEN ADD(
CONSTRAINT GV_KT
CHECK (MAGV BETWEEN '001' AND '999'));

KHỞI TẠO FILE HE

CREATE TABLE HE(
MAHE CHAR(2) NOT NULL,
TENHE VARCHAR2(20) NOT NULL);
ALTER TABLE HE ADD(
CONSTRAINT HE_KC
PRIMARY KEY(MAHE)
USING INDEX PCTFREE 10);

KHỞI TẠO FILE HUONGDAN

CREATE TABLE HUONGDAN(
MALV CHAR(5) NOT NULL,
MAGV CHAR(3) NOT NULL);
ALTER TABLE HUONGDAN ADD(
CONSTRAINT HD_KC
PRIMARY KEY (MALV,MAGV)
USING INDEX PCTFREE 10);
ALTER TABLE HUONGDAN ADD(
CONSTRAINT HD_KN_GV
FOREIGN KEY (MAGV)
REFERENCES GIAOVIEN(MAGV));

KHỞI TẠO FILE LOP

CREATE TABLE LOP(
MALOP CHAR(4) NOT NULL,
TENLOP VARCHAR2(30) NOT NULL,
MAHE CHAR(2) NOT NULL);
ALTER TABLE LOP ADD(
CONSTRAINT LOP_KC
PRIMARY KEY(MALOP)
USING INDEX PCTFREE 10);
ALTER TABLE LOP ADD(
CONSTRAINT LOP_KN_HE
FOREIGN KEY(MAHE)
REFERENCES HE(MAHE));
CREATE INDEX LOP_CM_HE
ON LOP(MAHE)
PCTFREE 40;

KHỞI TẠO FILE LUANVAN

CREATE TABLE LUANVAN(
MALV CHAR(5) NOT NULL,
DETAI VARCHAR2(100) NOT NULL,
DIACHI VARCHAR2(30) NOT NULL,
NOIDUNG LONG NOT NULL,
NGAYBV CHAR(6) NOT NULL,
NGAYNOP CHAR(6) NOT NULL);
ALTER TABLE LUANVAN ADD(
CONSTRAINT LV_KC
PRIMARY KEY (MALV)
USING INDEX PCTFREE 10);

KHỞI TẠO FILE SINHVIEN

CREATE TABLE SINHVIEN(
MASV CHAR(8) NOT NULL,
HOTEN VARCHAR2(30) NOT NULL,
MALOP CHAR(4) NOT NULL);
ALTER TABLE SINHVIEN ADD(
CONSTRAINT SV_KC
PRIMARY KEY(MASV)
USING INDEX PCTFREE 10);
ALTER TABLE SINHVIEN ADD(
CONSTRAINT SV_KN_LOP
FOREIGN KEY(MALOP)
REFERENCES LOP(MALOP));
CREATE INDEX SV_CM_LOP
ON SINHVIEN(MALOP)
PCTFREE 40;

KHỞI TẠO FILE THUCHIEN

CREATE TABLE THUCHIEN(
MASV CHAR(8) NOT NULL,
MALV CHAR(5) NOT NULL,
DIEM NUMBER(5,2) NOT NULL);
ALTER TABLE THUCHIEN ADD(
CONSTRAINT TH_KC
PRIMARY KEY (MASV,MALV)
USING INDEX PCTFREEE 10);
ALTER TABLE THUCHIEN ADD(
CONSTRAINT TH_KT
CHECK (DIEM BETWEEN 0 AND 10));

PcLeHoan 1996 - 2002
Mirror : http://www.pclehoan.com
Mirror : http://www.lehoanpc.net
Mirror : http://www.ktlehoan.com