skQnkPFr

1CREATE TABLE IF NOT EXISTS ValidDates(
2  chosenYear YEAR PRIMARY KEY, 
3  startDate DATE NOT NULL,
4  endDate DATE NOT NULL,
5  maxReservationsPerDay INTEGER default 50, 
6  CONSTRAINT chkDates CHECK(startDate < endDate)
7) ENGINE=INNODB;
8
9CREATE TABLE IF NOT EXISTS Families(
10  fID INTEGER PRIMARY KEY AUTO_INCREMENT,
11  signature BLOB DEFAULT NULL,
12  reservationDate DATE NOT NULL,
13  signatureTS TIMESTAMP,
14  createdTS TIMESTAMP NOT NULL,
15  createdBy INTEGER,
16  FOREIGN KEY (createdBy) REFERENCES Employees(eID) ON DELETE CASCADE
17) ENGINE=INNODB;
18
19CREATE TABLE IF NOT EXISTS FamilyMembers(
20  fmID INTEGER PRIMARY KEY AUTO_INCREMENT,
21  fName VARCHAR(255) NOT NULL,
22  lName VARCHAR(255) NOT NULL,
23  age INTEGER NOT NULL,
24  sex BIT(1) NOT NULL, 
25  ssNum VARCHAR(9) UNIQUE NOT NULL,
26  recievedBig BIT(1) DEFAULT 0,
27  recievedNormal BIT(1) DEFAULT 0,
28  recievedStocking BIT(1) DEFAULT 0,
29  famID INTEGER,
30  INDEX fIndex (famID), 
31  FOREIGN KEY (famID) REFERENCES Families(fID)  ON DELETE CASCADE 
32) ENGINE=INNODB;