CREATE DATABASE CARD2022;
USE CARD2022;

CREATE TABLE Table_Card (
CardID char(8) PRIMARY KEY,
Password varchar(8) NOT NULL,
Balance money NOT NULL CHECK (Balance>=0 ),
State char(1) NOT NULL CHECK (State in ('0','1','2')));


CREATE TABLE Table_Student (
StudentID char (12) NOT NULL,
CardID char(8),
Sname varchar(8) ,
Sex char(1) CHECK ( Sex in ('M','F')),
School varchar(20) NOT NULL,
PRIMARY KEY (StudentID),
FOREIGN KEY (CardID) REFERENCES Table_Card(CardID));


CREATE TABLE Table_Machine (
MachineID char(8) PRIMARY KEY ,
State char(1),
Address varchar(20));

CREATE TABLE Table_Salebill (
number int PRIMARY KEY IDENTITY(1,1),
CardID char(8) NOT NULL,
MachineID char(8) NOT NULL,
payamount decimal(10,2) NOT NULL,
saledate date NOT NULL,
CONSTRAINT "salebill_CIDfk" FOREIGN KEY (CardID) REFERENCES Table_Card(CardID) ON DELETE CASCADE ,
FOREIGN KEY (MachineID) REFERENCES Table_Machine(MachineID) ON DELETE CASCADE);



INSERT INTO Table_Student (StudentID, School) VALUES ('2021024921