QLDT DB, Sql Server

Language: Layout:

CREATE TABLE GIAOVIEN(
	MAGV NCHAR(10),
	HOTEN NVARCHAR(30),
	LUONG INT,
	PHAI NCHAR(3),
	NGSINH DATETIME,
	DIACHI NVARCHAR(50),
	GVQLCM NCHAR(10),
	MABM NCHAR(10),
	PRIMARY KEY(MAGV)
);

CREATE TABLE KHOA(
	MAKHOA NCHAR(10),
	TENKHOA NVARCHAR(30),
	NAMTL INT,
	PHONG NCHAR(10),
	DIENTHOAI INT,
	TRUONGKHOA NCHAR(10),
	NGAYNHAMCHUC DATETIME,
	PRIMARY KEY (MAKHOA)
);

CREATE TABLE BOMON(
	MABM NCHAR(10),
	TENBM NVARCHAR(30),
	PHONG NCHAR(10),
	DIENTHOAI INT,
	TRUONGBM NCHAR(10),
	MAKHOA NCHAR(10),
	NGAYNHAMCHUC DATETIME,
	PRIMARY KEY (MABM)
);

CREATE TABLE GV_DT(
	MAGV NCHAR(10),
	DIENTHOAI INT,
	PRIMARY KEY(MAGV, DIENTHOAI)
);

CREATE TABLE NGUOITHAN(
	MAGV NCHAR(10),
	TEN NVARCHAR(30),
	NGSINH DATETIME,
	PHAI NCHAR(3),
	PRIMARY KEY (MAGV, TEN)
);

CREATE TABLE CHUDE(
	MACD NCHAR(10),
	TENCD NVARCHAR(30),
	PRIMARY KEY(MACD)
);

CREATE TABLE DETAI(
	MADT NCHAR(10),
	TENDT NVARCHAR(50),
	CAPQL NVARCHAR(20),
	KINHPHI INT,
	NGAYBD DATETIME,
	NGAYKT DATETIME,
	MACD NCHAR(10),
	GVCNDT NCHAR(10),
	PRIMARY KEY(MADT)
);

CREATE TABLE CONGVIEC(
	MADT NCHAR(10),
	SOTT INT,
	TENCV NVARCHAR(40),
	NGAYBD DATETIME,
	NGAYKT DATETIME,
	PRIMARY KEY(MADT, SOTT)
);

CREATE TABLE THAMGIADT(
	MAGV NCHAR(10),
	MADT NCHAR(10),
	SOTT INT,
	PHUCAP INT,
	KETQUA NVARCHAR(20),
	PRIMARY KEY(MAGV, MADT, SOTT)
);

ALTER TABLE BOMON
ADD CONSTRAINT FK_BOMON_KHOA
FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA)
ALTER TABLE BOMON
ADD CONSTRAINT FK_BOMON_GIAOVIEN
FOREIGN KEY (TRUONGBM) REFERENCES GIAOVIEN(MAGV)

ALTER TABLE GV_DT
ADD CONSTRAINT FK_GV_DT_GIAOVIEN
FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV)

ALTER TABLE NGUOITHAN
ADD CONSTRAINT FK_NGUOITHAN_GIAOVIEN
FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV)

ALTER TABLE KHOA
ADD CONSTRAINT FK_KHOA_GIAOVIEN
FOREIGN KEY (TRUONGKHOA) REFERENCES GIAOVIEN(MAGV)

ALTER TABLE THAMGIADT
ADD CONSTRAINT FK_THAMGIADT_GIAOVIEN
FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV)
ALTER TABLE THAMGIADT
ADD CONSTRAINT FK_THAMGIADT_CONGVIEC
FOREIGN KEY (MADT, SOTT) REFERENCES CONGVIEC(MADT, SOTT)

ALTER TABLE CONGVIEC
ADD CONSTRAINT FK_CONGIVEC_DETAI
FOREIGN KEY (MADT) REFERENCES DETAI(MADT)

ALTER TABLE DETAI
ADD CONSTRAINT FK_DETAI_CHUDE
FOREIGN KEY (MACD) REFERENCES CHUDE(MACD)
ALTER TABLE DETAI
ADD CONSTRAINT FK_DETAI_GIAOVIEN
FOREIGN KEY (GVCNDT) REFERENCES GIAOVIEN(MAGV)

ALTER TABLE GIAOVIEN
ADD CONSTRAINT FK_GIAOVIEN_GIAOVIEN
FOREIGN KEY (GVQLCM) REFERENCES GIAOVIEN(MAGV)
ALTER TABLE GIAOVIEN
ADD CONSTRAINT FK_GIAOVIEN_BOMON
FOREIGN KEY (MABM) REFERENCES BOMON(MABM)

INSERT INTO GIAOVIEN VALUES 
('001', 'NGUYEN HOAI AN', '2000', 'NAM', '2/15/1973', 'TP HCM', NULL, NULL),
('002', 'TRAN TRA HUONG', '2500', 'NU', '6/20/1960', 'TP HCM', NULL, NULL),
('003', 'NGUYEN NGOC ANH','2200','NU','5/11/1975','TP HCM', '002', NULL),
('004', 'TRUONG NAM SON', '2300', 'NAM', '6/20/1959', 'BIEN HOA', NULL, NULL),
('005', 'LY HOANG HA', '2500', 'NAM', '10/23/1954', 'TP HCM', NULL, NULL),
('006','TRAN BACH TUYET','1500','NU','05/20/1980','MY THO',NULL,NULL),
('007','NGUYEN AN TRUNG','2100','NAM','06/05/1976','BIEN HOA',NULL,NULL),
('008','TRAN TRUNG HIEU','1800','NAM','08/06/1977','MY THO',NULL,NULL),
('009','TRAN HOANG NAM','2000','NAM','11/22/1975','TP HCM',NULL,NULL),
('010','PHAM NAM THANH','1500','NAM','12/12/1980','TP HCM',NULL,NULL)

INSERT INTO KHOA VALUES
('CNTT', 'CONG NGHE THONG TIN', '1995', 'B11', '0838123456', '002', '2/20/2005'),
('HH', 'HOA HOC', '1980', 'B41', '0838456456', '001', '10/15/2001'),
('SH', 'SINH HOC', '1980', 'B31', '083845454', '004', '10/11/2000'),
('VL', 'VAT LY', '1976', 'B21', '0838223223', '005', '9/18/2003')

INSERT INTO BOMON VALUES
('CNTT', 'CONG NGHE TRI THUC', 'B15', '0838126126', NULL, 'CNTT', NULL),
('HHC', 'HOA HUC CO', 'B44', '0838222222', NULL, 'HH', NULL),
('HL', 'HOA LY', 'B42', '0838878787', NULL, 'HH', NULL),
('HPT', 'HOA PHAN TICH', 'B43', '0838777777', '007', 'HH', '10/15/2007'),
('HTTT', 'HE THONG THONG TIN', 'B13', '0838125125', '002', 'CNTT', '9/20/2004'),
('MMT','MANG MAY TINH','B16','0838676767','001','CNTT','05/15/2005'),
('SH','SINH HOA','B33','0838898989',NULL,'SH',NULL),
('VLDT','VAT LY DIEN TU','B23','0838234234',NULL,'VL',NULL),
('VLUD','VAT LY UNG DUNG','B24','0838454545','005','VL','02/18/2006'),
('VS','VI SINH','B32','0838909090','004','SH','01/01/2007')

INSERT INTO NGUOITHAN VALUES
('001', 'HUNG', '1/14/1990', 'NAM'),
('001', 'THUY', '12/8/1994', 'NU'),
('003', 'HA', '9/3/1998', 'NU'),
('003', 'THU', '9/3/1998', 'NU'),
('007','MAI','03/26/2003','NU'),
('007','VY','02/14/2000','NU'),
('008','NAM','05/06/1991','NAM'),
('009','AN','08/19/1996','NAM'),
('010','NGUYET','01/14/2006','NU')

INSERT INTO GV_DT VALUES
('001', '0838912112'),
('001', '0903123123'),
('002', '0913454545'),
('003', '0838121212'),
('003', '0903656565')

INSERT INTO CHUDE VALUES
('NCPT', 'NGHIEN CUU PHAT TRIEN'),
('QLGD', 'QUAN LY GIAO DUC'),
('UDCN', 'UNG DUNG CONG NGHE')

INSERT INTO DETAI VALUES
('001', 'HTTT QUAN LY CAC TRUONG DAI HOC', 'DHQG', '20', '10/20/2007', '10/20/2008', 'QLGD', '002'),
('002', 'HTTT QUAN LY GIAO VU CHO 1 KHOA', 'TRUONG', '20', '10/12/2000', '10/12/2001', 'QLGD', '002'),
('003', 'NGHIEN CUU CHE TAO SOI NANO PLATIN', 'DHQG', '300', '5/15/2008', '5/15/2010', 'NCPT', '005'),
('004', 'TAO VAT LIEU SINH HOC BANG MANG OI NGUOI', 'NHA NUOC', '100','1/1/2007', '12/31/2009', 'NCPT', '004'),
('005', 'UNG DUNG HOA HOC XANH', 'TRUONG', '200', '10/10/2003', '12/10/2004', 'UDCN', '001'),
('006','NGHIEN CUU TE BAO GOC','NHA NUOC','4000','10/20/2006','10/20/2009','NCPT','004'),
('007','HTTT QUAN LY THU VIEN CAC TRUONG DAI HOC','TRUONG','20','05/10/2009','05/10/2010','QLGD','001')

INSERT INTO CONGVIEC VALUES
('001', '1', 'KHOI TAO VA LAP KE HOACH', '10/20/2007', '12/20/2008'),
('001', '2', 'XAC DINH YEU CAU', '12/21/2008', '3/21/2008'),
('001', '3', 'PHAN TICH HE THONG', '3/22/2008', '5/22/2008'),
('001', '4', 'THIET KE HE THONG', '5/23/2008', '6/23/2008'),
('001', '5', 'CAI DAT THU NGHIEM', '6/24/2008', '10/20/2008'),
('002', '1', 'KHOI TAO VA LAP KE HOACH', '5/10/2009', '7/10/2009'),
('002', '2', 'XAC DINH YEU CAU', '7/11/2009', '10/11/2009'),
('002', '3', 'PHAN TICH HE THONG', '10/12/2009', '12/20/2009'),
('002', '4', 'THIET KE HE THONG', '12/21/2009', '3/22/2010'),
('002', '5', 'CAI DAT THU NGHIEM', '3/23/2010', '5/10/2010'),
('006','1','LAY MAU','10/20/2006','02/20/2007'),
('006','2','NUOI CAY','02/21/2007','08/21/2008')

INSERT INTO THAMGIADT VALUES
('001', '002', '1', '0', NULL),
('001', '002', '2', '2', NULL),
('002', '001', '4', '2', 'DAT'),
('003', '001', '1', '1', 'DAT'),
('003', '001', '2', '0', 'DAT'),
('003','001','4','1','DAT'),
('003','002','2','0',NULL),
('004','006','1','0','DAT'),
('004','006','2','1','DAT'),
('006','006','2','1','DAT'),
('009','002','3','1','NULL'),
('009','002','4','1','NULL')

UPDATE GIAOVIEN
SET MABM='MMT'
WHERE MAGV='001';

UPDATE GIAOVIEN
SET MABM='HTTT'
WHERE MAGV='002';

UPDATE GIAOVIEN
SET MABM='HTTT'
WHERE MAGV='003';

UPDATE GIAOVIEN
SET MABM='VS'
WHERE MAGV='004' OR MAGV='006';

UPDATE GIAOVIEN
SET MABM='VLDT'
WHERE MAGV='005';

UPDATE GIAOVIEN
SET MABM='HPT'
WHERE MAGV='007' OR MAGV='008' OR MAGV='010';

UPDATE GIAOVIEN
SET MABM='MMT'
WHERE MAGV='009';

SELECT * FROM GIAOVIEN
SELECT * FROM KHOA
SELECT * FROM BOMON
SELECT * FROM GV_DT
SELECT * FROM NGUOITHAN
SELECT * FROM CHUDE
SELECT * FROM DETAI
SELECT * FROM CONGVIEC
SELECT * FROM THAMGIADT

View schema
Execution time: 0,12 sec, rows selected: 72, rows affected: 82, absolute service time: 0,31 sec