-- TẠO CSDL
Create Database QuanLy_SinhVien
On
( Name = QL_SV_data,
Filename = 'D:\DBMS_SQL SERVER\ql_sv.mdf',
size = 5,
Maxsize = 10,
filegrowth = 2
)
Log on
( Name = ql_sv_log,
Filename = 'D:\DBMS_SQL SERVER\ql_sv.ldf',
size = 5mb,
Filegrowth = 2mb
)
-----------------------------
--TẠO BẢNG
--Sinh_Vien (MaSV, HDem, Ten, NgSinh, QQuan, GTinh, MaL, EMail)
Use Qly_SinhVien
Create table Sinh_Vien
( MaSV int identity(1,1) primary key not null,
HDem varchar(20) not null,
Ten varchar (10) not null,
NgSinh date,
QQuan varchar(50),
GTinh char(3),
MaL char(10) not null,
EMail char(25)
)
--Khoa (MaKH, TenKH, DiaChi)
Create table Khoa
( MaKh char(10) primary key not null,
TenKh varchar(30),
Diachi varchar(20)
)
--M_Hoc (MaMH, TenMH, SoTC, MaKH)
Create table M_Hoc
( MaMH char(10) primary key not null,
TenMH varchar(30),
SoTC int,
MaKH char(10) not null
)
--Lop (MaL, TenL, MaKH)
Create table Lop
( MaL char(10) primary key not null,
TenL varchar(30),
MaKH char(10)
)
--Ket_Qua (MaMH, MaSV, Diem, KyThi)
Create table Ket_Qua
( MaMH char(10) not null,
MaSV int not null,
Diem float,
KyThi tinyint not null,
Constraint PK_KQ primary key (MaMH, MaSV, KyThi)
)
---------------------
-- TẠO CÁC RÀNG BUỘC
Alter table Sinh_Vien
--Drop constraint FK_SV_Lop
Add constraint FK_SV_Lop Foreign key (MaL) references Lop
Alter table M_Hoc
--Drop constraint FK_MHoc_Khoa
Add constraint FK_MHoc_Khoa Foreign key (MaKh) references Khoa
Alter table Lop
--Drop constraint FK_Lop_Khoa
Add constraint FK_Lop_Khoa Foreign key (MaKh) references Khoa
Alter table Ket_qua
--Drop constraint FK_KQ_SV
Add constraint FK_KQ_SV Foreign key (MaSV) references Sinh_Vien;
Alter table Ket_qua
Add constraint FK_KQ_MHoc Foreign key (MaMH) references M_Hoc
Alter table sinh_vien
--Drop constraint cc_sv
Add constraint CC_SV Check(GTinh = 'Nam' or GTinh = 'Nu')
Alter table Ket_qua
Add constraint CC_Kq1 Check(diem>=0 and diem<=10)
Alter table M_Hoc
Add constraint CC_MH Check(Sotc <= 10)
Alter table ket_qua
Add constraint DF_KQ Default 0 for Diem
------------------------------------------
--Insert dữ liệu
Insert into Khoa(MaKH, TenKH, DiaChi) Values ('CNTT', 'Cong nghe Thong tin',
'603_604')
Insert into Khoa(MaKH, TenKH, DiaChi) Values ('NH', 'Ngan hang', '503_504')
Insert into Khoa(MaKH, TenKH, DiaChi) Values ('NN', 'Ngoai ngu', '601_602')
Insert into Khoa(MaKH, TenKH, DiaChi) Values ('TC', 'BM GD the chat', '203_204')
Insert into Lop (MaL, TenL, MaKH) Values('CNTTAK14', 'Cong nghe Thong tin A
K14','CNTT')
Insert into Lop (MaL, TenL, MaKH) Values('CNTTBK14', 'Cong nghe Thong tin B
K14','CNTT')
Insert into Lop (MaL, TenL, MaKH) Values('TAAK14', 'Tieng Anh A K14','NN')
Insert into Lop (MaL, TenL, MaKH) Values('TABK14', 'Tieng Anh B K14','NN')
Insert into Lop (MaL, TenL, MaKH) Values('TCNHAK14', 'Tai chinh Ngan hang A
K14','NH')
Insert into Lop (MaL, TenL, MaKH) Values('CNTTAK13', 'Cong nghe Thong tin A
K13','CNTT')
Insert into Lop (MaL, TenL, MaKH) Values('CNTTBK13', 'Cong nghe Thong tin B
K13','CNTT')
Insert into Sinh_Vien(HDem, Ten, NgSinh, QQuan, GTinh, MaL, EMail)
Values ('Nguyen Van', 'An', '10/12/1993', 'Hai Phong', 'Nam', 'CNTTAK14',
'AnNv@[Link]')
Insert into Sinh_Vien(HDem, Ten, NgSinh, QQuan, GTinh, MaL)
Values ('Tran Viet', 'Anh', '12/12/1993', 'Hai Dương', 'Nam', 'CNTTAK14')
Insert into Sinh_Vien(HDem, Ten, NgSinh, QQuan, GTinh, MaL)
Values ('Le Thanh', 'Huong', '02/10/1992', 'Hai Duong', 'Nam', 'CNTTBK14')
Insert into Sinh_Vien(HDem, Ten, NgSinh, QQuan, GTinh, MaL, EMail)
Values ('Le Thi', 'Huyen', '02/15/1992', 'Ha Noi', 'Nu', 'TABK14',
'HuyenLT@[Link]')
Insert into Sinh_Vien(HDem, Ten, NgSinh, QQuan, GTinh, MaL)
Values ('Le ', 'Huyen', '02/15/1992', 'Ha Noi', 'nu', 'TABK14')
Insert into Sinh_Vien(HDem, Ten, NgSinh, QQuan, GTinh, MaL, EMail)
Values ('Tran Le', 'Khanh', '05/20/1992', N'Ha Noi', 'Nu', 'TABK14',
'Khanhtl@[Link]')
Insert into Sinh_Vien(HDem, Ten, NgSinh, GTinh, MaL, EMail)
Values ('Hoang Hai', 'Ha', '02/11/1992','Nam', 'CNTTBK13', 'Hahh@[Link]')
Insert into Sinh_Vien(HDem, Ten, NgSinh, QQuan, GTinh, MaL)
Values ('Nguyen Tuan', 'Thanh', '10/12/1991', 'Nghe An', 'Nam', 'CNTTBK13')
Insert into M_Hoc(MaMH, TenMH, SoTC, MaKH) Values ('CSDL1', 'Co so du lieu 1', 3,
'CNTT')
Insert into M_Hoc(MaMH, TenMH, SoTC, MaKH) Values ('CSDL2', 'Co so du lieu 2', 3,
'CNTT')
Insert into M_Hoc(MaMH, TenMH, SoTC, MaKH) Values ('TA1', 'Tieng Anh 1', 4, 'NN')
Insert into M_Hoc(MaMH, TenMH, SoTC, MaKH) Values ('TA2', 'Tieng Anh 2', 3, 'NN')
Insert into M_Hoc(MaMH, TenMH, SoTC, MaKH) Values ('TDC', 'Tin Dai cuong', 3,
'CNTT')
Insert into M_Hoc(MaMH, TenMH, SoTC, MaKH) Values ('CTDL & GT', 'Cau truc du lieu
& giai thuat', 3, 'CNTT')
Insert into M_Hoc(MaMH, TenMH, SoTC, MaKH) Values ('TDNH1', 'Tin dung ngan hang
1', 3, 'NH')
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('CSDL1', 1, 9, 3)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TDC', 1, 8, 1)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TA1', 2, 7, 1)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TA1', 1, 9, 1)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TDNH1', 2, 6, 4)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TA2', 4, 9, 3)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('CSDL1', 2, 9, 3)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('CSDL1', 3, 4, 3)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('CSDL1', 6, 7, 3)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TDC', 2, 4, 1)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TDC', 3, 8, 1)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TDC', 2, 8, 3)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TDC', 6, '', 1)
Insert into Ket_qua(MaMH, MaSV, Diem, KyThi) Values ('TDC', 6, '', 2)