xyUshpDb

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5-- -----------------------------------------------------
6-- Schema bookDB
7-- -----------------------------------------------------
8DROP SCHEMA IF EXISTS bookDB;
9
10CREATE SCHEMA IF NOT EXISTS bookDB DEFAULT CHARACTER SET utf8 ;
11USE bookDB ;
12
13-- -----------------------------------------------------
14-- Table `bookDB`.`user`
15-- -----------------------------------------------------
16DROP TABLE IF EXISTS bookDB.user;
17
18CREATE TABLE IF NOT EXISTS bookDB.user (
19  f_name VARCHAR(45) NULL DEFAULT NULL,
20  m_name VARCHAR(45) NULL DEFAULT NULL,
21  l_name VARCHAR(45) NULL DEFAULT NULL,
22  emailAddress VARCHAR(45) NOT NULL,
23  gender VARCHAR(45) NULL DEFAULT NULL,
24  userPassword VARCHAR(45) NOT NULL,
25  age INT(3) NULL DEFAULT NULL,
26  PRIMARY KEY (emailAddress))
27ENGINE = InnoDB
28DEFAULT CHARACTER SET = utf8;
29
30-- -----------------------------------------------------
31-- Table `bookDB`.`bookOrder`
32-- -----------------------------------------------------
33DROP TABLE IF EXISTS bookDB.bookOrder;
34
35CREATE TABLE IF NOT EXISTS bookDB.bookOrder (
36  id INT(11) NOT NULL AUTO_INCREMENT,
37  userEmail VARCHAR(45) NOT NULL,
38  billingAddress VARCHAR(45) NOT NULL,
39  shippingAddress VARCHAR(45) NOT NULL,
40  credit_card_used INT(20) NOT NULL,
41  order_status VARCHAR(45) NOT NULL,
42  order_date DATE NOT NULL,
43  bookISBN INT(11) NOT NULL,
44  quantity INT(11) NOT NULL,
45  cost FLOAT(11) NOT NULL,
46  FOREIGN KEY (userEmail) REFERENCES user.emailAddress,
47  FOREIGN KEY (bookISBN) REFERENCES book.ISBN,
48  PRIMARY KEY (id))
49ENGINE = InnoDB
50AUTO_INCREMENT = 1
51DEFAULT CHARACTER SET = utf8;
52
53-- -----------------------------------------------------
54-- Table `bookDB`.`book`
55-- -----------------------------------------------------
56DROP TABLE IF EXISTS bookDB.book;
57
58CREATE TABLE IF NOT EXISTS bookDB.book (
59  ISBN INT(13) NOT NULL,
60  bookName VARCHAR(45) NOT NULL,
61  bookAuthors VARCHAR(45) NULL DEFAULT NULL,
62  publisher VARCHAR(45) NULL DEFAULT NULL,
63  bookSubject VARCHAR(45) NULL DEFAULT NULL, 
64  quantity INT(11) NULL DEFAULT NULL,
65  price INT(11) NULL DEFAULT NULL,
66  bookLanguage VARCHAR(45) NULL DEFAULT NULL,
67  description VARCHAR(45) NULL DEFAULT NULL,
68  datePublished date NULL DEFAULT NULL,
69  PRIMARY KEY (ISBN))
70ENGINE = InnoDB
71DEFAULT CHARACTER SET = utf8;
72
73-- -----------------------------------------------------
74-- Table `bookDB`.`keywords`
75-- -----------------------------------------------------
76DROP TABLE IF EXISTS bookDB.keyword;
77
78CREATE TABLE IF NOT EXISTS bookDB.keyword (
79  bookISBN INT(13) NOT NULL,
80  word VARCHAR(45) NOT NULL,
81  FOREIGN KEY (bookISBN) REFERENCES book.ISBN)
82ENGINE = InnoDB
83DEFAULT CHARACTER SET = utf8;
84
85-- -----------------------------------------------------
86-- Table `bookDB`.`ratings`
87-- -----------------------------------------------------
88DROP TABLE IF EXISTS bookDB.ratings;
89
90CREATE TABLE IF NOT EXISTS bookDB.ratings (
91  userEmail VARCHAR(45) NOT NULL,
92  bookISBN INT(13) NOT NULL,
93  score INT(11) NOT NULL,
94  comments VARCHAR(45) NULL DEFAULT NULL,
95  FOREIGN KEY (bookISBN) REFERENCES Book.ISBN,
96  FOREIGN KEY (userEmail) REFERENCES User.emailAddress)
97ENGINE = InnoDB
98DEFAULT CHARACTER SET = utf8;
99
100
101SET SQL_MODE=@OLD_SQL_MODE;
102SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
103SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;