Ass_DBS

DROP TABLE CHINHANH;
DROP TABLE DAODIEN;
DROP TABLE DAODIEN_PHIM;
DROP TABLE DIENVIEN;
DROP TABLE DIENVIEN_PHIM;
DROP TABLE DIENVIEN_TCN;
DROP TABLE GHE;
DROP TABLE GIAVE;
DROP TABLE HANGSX;
DROP TABLE HINHANH_CN;
DROP TABLE KHACHHANG;
DROP TABLE LICHSU;
DROP TABLE NGUOI;
DROP TABLE NHANVIEN;
DROP TABLE PHIM;
DROP TABLE PHIM_THELOAI;
DROP TABLE THELOAI;
DROP TABLE PHIMTHUE;
DROP TABLE PHONGCHIEU;
DROP TABLE SUATCHIEU;
DROP TABLE THELOAI;
DROP TABLE THELOAI_ST;
DROP TABLE VAIDIEN_PHIM;
DROP TABLE VE;
DROP TABLE PHONGCHIEU;

CREATE TABLE CHINHANH
(	TENCN		VARCHAR(15)	NOT NULL UNIQUE,
	MACN		VARCHAR(15) PRIMARY KEY,					-- The default of CHAR is also 1 byte
	SDT         VARCHAR(11) NOT NULL,
    ADDRESS     VARCHAR(30),
    CHECK( REGEXP_LIKE (SDT, '^[0-9]{10}$|[0-9]{11}$')),
    CHECK(REGEXP_LIKE(CN,'[0-9][0-9][0-9][0-9]')),
    CHECK (MACN>0)
);
 CREATE SEQUENCE SEQ_MACN START WITH 1 INCREMENT BY 1;
CREATE TABLE HINHANH_CN
(   MACN        INT     NOT NULL,
    HINHANH       CHAR(20),
    PRIMARY KEY (MACN, HINHANH),
    CONSTRAINT fk_MACN FOREIGN KEY(MACN)
                   REFERENCES CHINHANH (MACN)
                   ON DELETE SET NULL DEFERRABLE
);
INSERT INTO HINHANH_CN(HINHANH) VALUES ('C:\Users\TONGHUNG\Pictures\Camera Roll\a.jpg');
CREATE TABLE GIAVE
(   MACN       INT     NOT NULL,
    LOAI_VE NUMBER(1) CHECK(LOAI_VE > 0 AND LOAI_VE < 4),
    TEN_LOAIVE VARCHAR(30),
    CONSTRAINT          Check_BGVe
       CHECK( CONCAT(CONCAT(LOAI_VE,'-'), TEN_LOAIVE) IN ('1-2D','2-3D thuong','3-3D vip') ),
    GIA_VE INT,
    PRIMARY KEY (MACN, LOAI_VE),
        CONSTRAINT fk_MACN_GV FOREIGN KEY(MACN)
                   REFERENCES CHINHANH(MACN)
                   ON DELETE SET NULL DEFERRABLE
);
CREATE TABLE PHONGCHIEU
(   MACN       INT     NOT NULL,
    STT     SMALLINT,
    SL_GHE  SMALLINT              DEFAULT 0,
    PRIMARY KEY (MACN, STT),
     CONSTRAINT fk_MACN_PC FOREIGN KEY(MACN)
                   REFERENCES CHINHANH(MACN)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 5--------------
DROP TABLE GHE;
CREATE TABLE GHE
(   MACN       INT     NOT NULL,
    STT_PHONG     SMALLINT,
    LOAI_GHE    NUMBER(1) CHECK(LOAI_GHE=2 OR LOAI_GHE=1),
    SOGHE   DECIMAL(4) NOT NULL,
        PRIMARY KEY (MACN,STT_PHONG,SOGHE),
    CONSTRAINT fk_MACN_GHE FOREIGN KEY(MACN,STT_PHONG)
                   REFERENCES PHONGCHIEU(MACN,STT)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 6--------------
CREATE TABLE THELOAI
(   ID_SL INT PRIMARY KEY,
    TEN_SL CHAR(30)
);
----------BANG 7--------------
CREATE TABLE DIENVIEN
(   MADV  CHAR(6) PRIMARY KEY,
    QUOCTICH CHAR(10),
    CHECK(REGEXP_LIKE(MADV,'DV[0-9][0-9][0-9][0-9]'))
);
----------BANG 8--------------
CREATE TABLE DIENVIEN_TCN
(   MADV CHAR(6),
    VAI_DIEN        CHAR(30),
    PHIM_TC         CHAR(30),
    QUOCTICH CHAR(15),
    PRIMARY KEY(MADV,VAI_DIEN),
     CONSTRAINT fk_MADV FOREIGN KEY(MADV)
                   REFERENCES DIENVIEN(MADV)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 9--------------
CREATE TABLE DAODIEN
(   MADD CHAR(6) PRIMARY KEY,
    QUOCTICH CHAR(15),
    CHECK(REGEXP_LIKE(MADD,'DD[0-9][0-9][0-9][0-9]'))
);
----------BANG 10--------------

CREATE TABLE THELOAI_ST
(   MADD CHAR(6),
    ID_TL INT,
    PRIMARY KEY(MADD,ID_TL),
    CONSTRAINT fk_MADD_TL FOREIGN KEY(MADD)
                   REFERENCES DAODIEN(MADD)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_ID_TL FOREIGN KEY(ID_TL)
                   REFERENCES THELOAI(ID_SL)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 11--------------
CREATE TABLE NGUOI
(   MAID		  	CHAR(7) 			             	PRIMARY KEY,
	HOTEN		  	VARCHAR(50)		NOT NULL,
	NAMSINH			INT
					  CHECK( NAMSINH >= 1000 AND NAMSINH <= 9999),
	CMND		  	VARCHAR(12)		NOT NULL	UNIQUE
					  CHECK( REGEXP_LIKE (CMND, '^[0-9]{9}$|[0-9]{12}$' )),
	EMAIL		  	VARCHAR(50)
					  CHECK (REGEXP_LIKE (EMAIL , '^\w+(\.\w+)*+@\w+(\.\w+)+$')),
	SDT			  	VARCHAR(11)
					  CHECK( REGEXP_LIKE (SDT, '^[0-9]{10}$|[0-9]{11}$')),
    USERNAME    	VARCHAR(50)     UNIQUE NOT NULL,
    PASS            VARCHAR(16)     NOT NULL
);
----------BANG 12--------------
CREATE TABLE KHACHHANG
(   MAID CHAR(7) PRIMARY KEY,
    DIEM INT,
    LOAI_KH DECIMAL(1) CHECK(LOAI_KH=1 OR LOAI_KH=2),
    CONSTRAINT fk_ID FOREIGN KEY(MAID)
                   REFERENCES NGUOI(MAID)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 13--------------
CREATE TABLE NHANVIEN
(   MAID CHAR(7) PRIMARY KEY,
    MACN INT,
    CONSTRAINT fk_ID_NV FOREIGN KEY(MAID)
                   REFERENCES NGUOI(MAID)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_CN_NV FOREIGN KEY(MACN)
                   REFERENCES CHINHANH(MACN)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 14--------------
CREATE TABLE PHIM
(   MAPHIM CHAR(7) PRIMARY KEY,
    TENPHIM CHAR(30) NOT NULL,
    CHECK(REGEXP_LIKE(MAPHIM,'PH[0-9][0-9][0-9][0-9]')),
    TUOI    INT CHECK(TUOI>0 AND TUOI<=18),
    NAM_SX          DECIMAL(4,0),
    THOILUONG       INT NOT NULL,
    NOIDUNG         VARCHAR(50),
    TUOITT          SMALLINT,
    CHECK(TUOITT>0 AND TUOITT<19),
    THOIGIANBD		DATE ,
	THOIGIANKT  	DATE ,
    BOMTAN          DECIMAL(1), CHECK(BOMTAN IN(0,1)),
    TINHTRANG       DECIMAL(1) CHECK (TINHTRANG>-1 AND TINHTRANG<3),
    CONSTRAINT      ABC CHECK(THOIGIANKT>THOIGIANBD)
);
----------BANG 15--------------
CREATE TABLE PHIM_THELOAI
(   MAPHIM      CHAR(7) NOT NULL,
    ID_TL       INT,
    PRIMARY KEY(MAPHIM,ID_TL),
     CONSTRAINT fk_MAPHIM FOREIGN KEY(MAPHIM)
                   REFERENCES PHIM(MAPHIM)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_IDTL FOREIGN KEY(ID_TL)
                   REFERENCES THELOAI(ID_SL)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 16--------------
CREATE TABLE DIENVIEN_PHIM
(   MAPHIM      CHAR(6)     NOT NULL,
    MADV       CHAR(6),
    PRIMARY KEY (MAPHIM,MADV),
     CONSTRAINT fk_MAPHIMDV FOREIGN KEY(MAPHIM)
                   REFERENCES PHIM(MAPHIM)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_DVPHIM FOREIGN KEY(MADV)
                   REFERENCES DIENVIEN(MADV)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 17--------------
CREATE TABLE VAIDIEN_PHIM
(   MAPHIM      CHAR(6),
    MADV        CHAR(6),
    TEN_NV      VARCHAR(30)     NOT NULL,
    PRIMARY KEY(MAPHIM,MADV,TEN_NV),
     CONSTRAINT fk_MAPHIMVC FOREIGN KEY(MAPHIM,MADV)
                   REFERENCES DIENVIEN_PHIM(MAPHIM,MADV)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 18--------------
CREATE TABLE DAODIEN_PHIM
(   MAPHIM      CHAR(6),
    MADD        CHAR(6),
    PRIMARY KEY(MAPHIM,MADD),
     CONSTRAINT fk_MAPHIMDD FOREIGN KEY(MAPHIM)
                   REFERENCES PHIM(MAPHIM)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_DVDD FOREIGN KEY(MADD)
                   REFERENCES DAODIEN(MADD)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 19--------------
CREATE TABLE PHIM_TSX
(   MAPHIM      CHAR(6) PRIMARY KEY,
    VON       DECIMAL(10,3),
    TAITRO    DECIMAL(10,3),
    SO_RAPTHUE  SMALLINT,
     CONSTRAINT fk_MAPHIMTSX FOREIGN KEY(MAPHIM)
                   REFERENCES PHIM(MAPHIM)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 20--------------
CREATE TABLE LICHSU
(   MAPHIM      CHAR(6),
    NGAY       DATE,
    TIENTHUE    DECIMAL(10,3),
    PRIMARY KEY(MAPHIM,NGAY),
     CONSTRAINT fk_MAPHIMLS FOREIGN KEY(MAPHIM)
                   REFERENCES PHIM(MAPHIM)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 21--------------
CREATE TABLE HANGSX
(   TEN_SX      VARCHAR(20) PRIMARY KEY,
    EMAIL		  	VARCHAR(50),
    SDT         VARCHAR(11) NOT NULL,
    ADDRESS     VARCHAR(30),
    CHECK( REGEXP_LIKE (SDT, '^[0-9]{10}$|[0-9]{11}$')),
    CHECK (REGEXP_LIKE (EMAIL , '^\w+(\.\w+)*+@\w+(\.\w+)+$'))
);
----------BANG 22--------------
CREATE TABLE PHIMTHUE
(   MAPHIM      CHAR(6),
    TYLE		  	DECIMAL(1,2),
    HANG_SX         VARCHAR(11) NOT NULL,
    CONSTRAINT fk_MAPHIMTHUE FOREIGN KEY(MAPHIM)
                   REFERENCES PHIM(MAPHIM)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_HANG_SX FOREIGN KEY(HANG_SX)
                   REFERENCES HANGSX(TEN_SX)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 23--------------
CREATE TABLE SUATCHIEU
(   MAPHIM      CHAR(6),
    MACN		INT,
    STT         SMALLINT NOT NULL,
    NGAYCHIEU   DATE    NOT NULL,
    LOAISUAT    NUMBER(1) CHECK(LOAISUAT=1 OR LOAISUAT=2),
    VEBAN       INT DEFAULT 0,
    PRIMARY KEY(MAPHIM,MACN,STT,NGAYCHIEU),
    CONSTRAINT fk_MAPHIMSC FOREIGN KEY(MAPHIM)
                   REFERENCES PHIM(MAPHIM)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_MACNSC FOREIGN KEY(MACN,STT)
                   REFERENCES PHONGCHIEU(MACN,STT)
                   ON DELETE SET NULL DEFERRABLE
);
----------BANG 24--------------
CREATE TABLE VE
(   MAVE    INT PRIMARY KEY ,
    NGAYIN  DATE,
    NGAYDAT DATE,
    TINHTRANG NUMBER(1) CHECK(TINHTRANG>0 AND TINHTRANG<4),
    GIAVE   INT,
    MANV    CHAR(7) NOT NULL,
    MAKH    CHAR(7) NOT NULL,
    MAPHIM  CHAR(6) NOT NULL,
    MACN    INT     NOT NULL,
    STT     SMALLINT    NOT NULL,
    NGAYCHIEU    DATE   ,
    SOGHE   DECIMAL(4),
    CONSTRAINT fk_NVVE FOREIGN KEY(MANV)
                   REFERENCES NHANVIEN(MAID)
                   ON DELETE SET NULL DEFERRABLE,
     CONSTRAINT fk_KHVE FOREIGN KEY(MAKH)
                   REFERENCES KHACHHANG(MAID)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_VE FOREIGN KEY(MAPHIM,MACN,STT,NGAYCHIEU)
                   REFERENCES SUATCHIEU(MAPHIM,MACN,STT,NGAYCHIEU)
                   ON DELETE SET NULL DEFERRABLE,
    CONSTRAINT fk_VEE FOREIGN KEY(MACN,STT,SOGHE)
                   REFERENCES GHE(MACN,STT_PHONG,SOGHE)
                   ON DELETE SET NULL DEFERRABLE
);