QLTV

CREATE DATABASE QLTV
USE QLTV

CREATE TABLE Sach
(
    MaS CHAR(20) PRIMARY KEY,
    TenS CHAR(20) NOT NULL,
    TenTG CHAR(20),
    NXB CHAR(50),
    NamXB datetime
)

INSERT INTO Sach
VALUES ('MS01','Toan Cao Cap','Nguyen Van A','NXB Giao Duc','1989')
INSERT INTO Sach
VALUES ('MS02','Vat Ly','Nguyen Van B','NXB Giao Duc','2005')
INSERT INTO Sach
VALUES ('MS03','Tin Hoc','Nguyen Van C','NXB Kim Dong','2009')

SELECT * FROM Sach


CREATE TABLE DocGia
(
    MaDG CHAR(20) PRIMARY KEY,
    TenDG CHAR(20) NOT NULL,
    Donvi CHAR(50),
    ngaycapthe datetime,
    ngayhethanthe datetime
)

INSERT INTO DocGia
VALUES ('DG01','Nguyen Thi E','Bac Ninh','2/3/2010','2/3/2011')
INSERT INTO DocGia
VALUES ('DG02','Nguyen Thi G','Thai Nguyen','4/5/2010','4/5/2011')
INSERT INTO DocGia
VALUES ('DG03','Nguyen Thi F','Ha Noi','2/6/2011','2/6/2012')

SELECT * FROM DocGia

CREATE TABLE SoMuon
(
    MaDG CHAR(20),
    SosoMuon CHAR(20) PRIMARY KEY,
    ngaycap datetime,
    CONSTRAINT fk1 FOREIGN KEY (MaDG) REFERENCES DocGia(MaDG)
)

INSERT INTO SoMuon
VALUES ('DG01','SS01','2/2/2010')
INSERT INTO SoMuon
VALUES ('DG02','SS02','2/2/2010')
INSERT INTO SoMuon
VALUES ('DG03','SS03','2/2/2011')


CREATE TABLE MuonTra
(
    SosoMuon CHAR(20),
    MaS CHAR(20) NOT NULL,
    NgayM datetime,
    NgayHT datetime,
    NgayT datetime
    CONSTRAINT pk PRIMARY KEY (SosoMuon, MaS, NgayM),
    CONSTRAINT fk2 FOREIGN KEY (SosoMuon) REFERENCES SoMuon(SosoMuon),
    CONSTRAINT fk3 FOREIGN KEY (MaS) REFERENCES Sach(MaS)
)


INSERT INTO MuonTra
VALUES ('SS01','MS01','12/2/2010','12/3/2010','12/3/2010')
INSERT INTO MuonTra
VALUES ('SS02','MS01','5/2/2011','4/3/2010','2/3/2011')
INSERT INTO MuonTra
VALUES ('SS03','MS03','4/2/2011','6/3/2010','12/3/2011')

SELECT * FROM MuonTra