CREATE TABLE department (deptID int unsigned NOT NULL auto_increment, deptName varchar(100) NOT NULL default '0', `departmentDesc` varchar(255) NOT NULL default '', PRIMARY KEY  (`deptID`), UNIQUE (`deptName`));

CREATE TABLE category ( catID int unsigned NOT NULL auto_increment, catName varchar(100) NOT NULL default '0', deptID int, PRIMARY KEY  (`catID`), UNIQUE(`catName`));

CREATE TABLE library_bookinfo( bookID int(8) NOT NULL auto_increment, bookDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, bookMaterialType varchar(50) NOT NULL default 'Book', bookISBN varchar(255) default NULL, bookAuthor varchar(255) NOT NULL default '',bookName tinytext, bookedition varchar(50) default NULL, bookPublisher varchar(255) NOT NULL default '', bookPublishedYear year(4) default NULL, bookUrl text NOT NULL, bookSubject varchar(255) default NULL, bookDesc mediumtext NOT NULL, bookImg blob NOT NULL, bookStock int, bookPrice numeric(5,10),catID int unsigned, deptID int unsigned, PRIMARY KEY  (`bookID`));


CREATE TABLE relateddep( relID int NOT NULL auto_increment, bookID int, deptID int, PRIMARY KEY(relID));
CREATE TABLE members( userID int unsigned NOT NULL auto_increment, userAccount varchar(255), userPassword varchar(32) NOT NULL default '',`lastLogin` timestamp NOT NULL default '0000-00-00 00:00:00', usertypeID int default '', userName varchar(50) NOT NULL default '', userEmail varchar(100), userDept varchar(100), userSex varchar(5), userBirthday  date NOT NULL default '2000-01-01', userAddress varchar(255) NOT NULL default '', userPhone varchar(20) default null, userExpire date NOT NULL default '0000-00-00',  userDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, booksInHand int default 0,PRIMARY KEY  (`userID`));

CREATE TABLE membertype( membertypeID int NOT NULL auto_increment, membertypeName varchar(255), lendDuration int, maxbookAllowed int, PRIMARY KEY  (membertypeID));
CREATE TABLE transaction( transactionID int unsigned NOT NULL auto_increment, useraccount varchar(255), bookName tinytext, bookID int, transactionDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,transactionStatus boolean default false, PRIMARY KEY  (`transactionID`));

CREATE TABLE types_privileges( membertypeID smallint(3) default NULL,  menuItems varchar(50) default NULL);
CREATE TABLE suggestions( suggID int(8) NOT NULL auto_increment, suggName varchar(100), suggDesc mediumtext default NULL, PRIMARY KEY  (`suggID`) );
