CREATE TABLE PROP | |
( | |
NAME VARCHAR(32) NOT NULL, | |
PROP_VALUE INTEGER NOT NULL, | |
CONSTRAINT PROP_PK PRIMARY KEY(NAME) | |
) | |
CREATE TABLE PERSON | |
( | |
PERSON_ID INTEGER NOT NULL, | |
LAST_NAME VARCHAR(30) NOT NULL, | |
FIRST_NAME VARCHAR(30), | |
EMAIL VARCHAR(60) NOT NULL, | |
PASSWD VARCHAR(20) NOT NULL, | |
VERIFIED BIT NOT NULL, | |
ADMIN BIT NOT NULL, | |
LOCKED_OUT BIT NOT NULL, | |
AUTH_CODE VARCHAR(30), | |
LAST_ACCESS TIMESTAMP, | |
CONSTRAINT PERSON_PK PRIMARY KEY (PERSON_ID) | |
) | |
CREATE TABLE PUBLISHER | |
( | |
PUBLISHER_ID INTEGER NOT NULL, | |
NAME VARCHAR(40) NOT NULL, | |
CONSTRAINT PUBLISHER_PK PRIMARY KEY (PUBLISHER_ID), | |
UNIQUE (NAME) | |
) | |
ALTER TABLE PERSON ADD CONSTRAINT UNIQUE_EMAIL UNIQUE(EMAIL) | |
CREATE TABLE BOOK | |
( | |
BOOK_ID INTEGER NOT NULL, | |
AUTHOR VARCHAR(60) NOT NULL, | |
HOLDER_ID INTEGER NOT NULL INDEX_BLIST, | |
OWNER_ID INTEGER NOT NULL INDEX_BLIST, | |
TITLE VARCHAR(100) NOT NULL, | |
DESCRIPTION VARCHAR(2000), | |
ISBN VARCHAR(20), | |
PUBLISHER_ID INTEGER NOT NULL INDEX_BLIST, | |
LENDABLE BIT NOT NULL, | |
HIDDEN BIT NOT NULL, | |
DATE_ADDED TIMESTAMP NOT NULL, | |
CONSTRAINT BOOK_PK PRIMARY KEY (BOOK_ID), | |
CONSTRAINT HOLDER_FK FOREIGN KEY (HOLDER_ID) REFERENCES PERSON (PERSON_ID), | |
CONSTRAINT OWNER_FK FOREIGN KEY (OWNER_ID) REFERENCES PERSON (PERSON_ID), | |
CONSTRAINT PUBLISHER_FK FOREIGN KEY (PUBLISHER_ID) REFERENCES PUBLISHER (PUBLISHER_ID) | |
) | |
INSERT INTO PROP (NAME, PROP_VALUE) VALUES ("next-key", 5000) | |
INSERT INTO PERSON (PERSON_ID, LAST_NAME, FIRST_NAME, EMAIL, PASSWD, VERIFIED, ADMIN, LOCKED_OUT, AUTH_CODE, LAST_ACCESS) | |
VALUES (1000, 'Lewis Ship', 'Howard', 'hship@primix.com', 'secret', 1, 1, 0, NULL, DATEOB()) | |
INSERT INTO PERSON (PERSON_ID, LAST_NAME, FIRST_NAME, EMAIL, PASSWD, VERIFIED, ADMIN, LOCKED_OUT, AUTH_CODE, LAST_ACCESS) | |
VALUES (1001, 'Queue', 'Suzy', 'squeue@bug.org', 'secret', 1, 0, 0, NULL, DATEOB()) | |
INSERT INTO PERSON (PERSON_ID, LAST_NAME, FIRST_NAME, EMAIL, PASSWD, VERIFIED, ADMIN, LOCKED_OUT, AUTH_CODE, LAST_ACCESS) | |
VALUES (1002, 'CubeDweller', 'Dilbert', 'dilbert@bigco.com', 'secret', 1, 0, 0, NULL, DATEOB()) | |