Scribd
Upload
23 views3 pages

create database QuanLyGiaoVu

SQL

Uploaded by

ply58509
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
23 views3 pages

create database QuanLyGiaoVu

SQL

Uploaded by

ply58509
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

create database QuanLyGiaoVu

use QuanLyGiaoVu

create table KHOA


(
MAKHOA varchar(4) PRIMARY KEY,
TENKHOA varchar(40),
NGTLAP smalldatetime,
TRGKHOA char(4)
);

create table MONHOC


(
MAMH varchar(10) PRIMARY KEY,
TENMH varchar(40),
TCLT tinyint,
TCTH tinyint,
MAKHOA varchar(4),
CONSTRAINT FK_makhoa FOREIGN KEY(MAKHOA) REFERENCES KHOA(MAKHOA)
);

create table DIEUKIEN


(
MAMH varchar(10),
MAMH_TRUOC varchar(10),
CONSTRAINT PK PRIMARY KEY (MAMH, MAMH_TRUOC),
CONSTRAINT FK_mamh FOREIGN KEY (MAMH) REFERENCES MONHOC(MAMH)
);

create table GIAOVIEN


(
MAGV CHAR(4) PRIMARY KEY,
HOTEN VARCHAR(40),
HOCVI VARCHAR(10),
HOCHAM VARCHAR(10),
GIOITINH VARCHAR(3),
NGSINH SMALLDATETIME,
NGVL SMALLDATETIME,
HESO NUMERIC(4,2),
MUCLUONG MONEY,
MAKHOA VARCHAR(4),
CONSTRAINT FK_makhoa_GV FOREIGN KEY (MAKHOA) REFERENCES KHOA (MAKHOA)
);

create table LOP


(
MALOP CHAR(3) PRIMARY KEY,
TENLOP VARCHAR(40),
TRGLOP CHAR(5),
SISO TINYINT,
MAGVCN CHAR(4),
CONSTRAINT FK_magvcn FOREIGN KEY (MAGVCN) REFERENCES GIAOVIEN(MAGV),
);

create table HOCVIEN


(
MAHV CHAR(5) PRIMARY KEY,
HO VARCHAR(40),
TEN VARCHAR(10),
NGSINH SMALLDATETIME,
GIOITINH VARCHAR(3),
NOISINH VARCHAR(40),
MALOP CHAR(3),
CONSTRAINT FK_malop FOREIGN KEY (MALOP) REFERENCES LOP(MALOP)
);

ALTER TABLE LOP


ADD CONSTRAINT FK_trglop FOREIGN KEY (TRGLOP) REFERENCES HOCVIEN(MAHV);

create table GIANGDAY


(
MALOP CHAR(3),
MAMH VARCHAR(10),
MAGV CHAR(4),
HOCKY TINYINT,
NAM SMALLINT,
TUNGAY SMALLDATETIME,
DENNGAY SMALLDATETIME,
CONSTRAINT pk_GiangDay PRIMARY KEY(MALOP, MAMH),
CONSTRAINT FK_lop FOREIGN KEY (MALOP) REFERENCES LOP(MALOP),
CONSTRAINT FK_mon FOREIGN KEY (MAMH) REFERENCES MONHOC(MAMH),
CONSTRAINT FK_gv FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV),
);

create table KETQUATHI


(
MAHV CHAR(5),
MAMH VARCHAR(10),
LANTHI TINYINT,
NGTHI SMALLDATETIME,
DIEM NUMERIC(4, 2),
KQUA VARCHAR(10),
CONSTRAINT PK_lop_mh PRIMARY KEY(MAHV, MAMH, LANTHI),
CONSTRAINT FK_lop_KQT FOREIGN KEY (MAHV) REFERENCES HOCVIEN(MAHV),
);

-- I. Ngôn ngữ định nghĩa dữ liệu (Data Definition Language):


-- Cau 2:
ALTER TABLE HOCVIEN
ADD CONSTRAINT CHECK_MAHV CHECK(
LEN(MAHV)=5
AND LEFT(MAHV, 3) IN (SELECT MALOP FROM LOP)
AND ISNUMERIC(RIGHT(MAHV, 2)) = 1
);
-- Cau 3:
ALTER TABLE GIAOVIEN
ADD CONSTRAINT CHECK_gtGV CHECK(GIOITINH = 'Nam' OR GIOITINH = 'Nu');
ALTER TABLE HOCVIEN
ADD CONSTRAINT CHECK_gtHV CHECK(GIOITINH = 'Nam' OR GIOITINH = 'Nu');
-- Cau 4:
ALTER TABLE KETQUATHI
ADD CONSTRAINT CHECK_kqt CHECK(DIEM >= 0 AND DIEM <= 0);
ALTER TABLE KETQUATHI
ALTER COLUMN DIEM NUMERIC(4, 2);
-- Cau 5:
UPDATE KETQUATHI
SET KQUA = CASE
WHEN DIEM >= 5 THEN 'Dat'
ELSE 'Khong dat'
END;
-- Cau 6:
ALTER TABLE KETQUATHI
ADD CONSTRAINT CHECK_kqt_lanthi CHECK(LANTHI >= 1 AND LANTHI <= 3);
-- Cau 7:
ALTER TABLE GIANGDAY
ADD CONSTRAINT CHECK_GD_HK CHECK(HOCKY BETWEEN 1 AND 3);
-- Cau 8:
ALTER TABLE GIAOVIEN
ADD CONSTRAINT CHECK_GV_HV CHECK(HOCVI IN ('CN', 'KS', 'Ths', 'TS', 'PTS'));

You might also like