4kK8rnZL

1package SQL;
2
3public class DataBaseManger {
4
5    //Data base connection details:
6    public static final String URL = "jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE";
7    public static final String USER_NAME = "root";
8    public static final String PASSWORD = "12345678";
9
10    //Create data base:
11    private static final String CREATE_DB = "CREATE SCHEMA if not exists droneLab";
12    private static final String DROP_DB = "DROP droneLab";
13
14
15    //Create table companies:
16    private static final String CREATE_TABLE_COMPANIES = "CREATE TABLE if not exists `Coupons`.`companies` " +
17            "(`id` INT NOT NULL AUTO_INCREMENT," +
18            "`name` VARCHAR(20) NOT NULL," +
19            "`email` VARCHAR(50) NOT NULL," +
20            "`password` VARCHAR(20) NOT NULL," +
21            "`PRIMARY KEY (`id`));" ;
22
23    private static final String CREATE_TABLE_CUSTOMERS = "CREATE TABLE if not exists `Coupons`. `customers`" +
24            "(`id` INT NOT NULL AUTO_INCREMENT," +
25            "`first_name` VARCHAR(20) NOT NULL," +
26            "`last_name` VARCHAR(20) NOT NULL," +
27            "`email` VARCHAR(50) NOT NULL," +
28            "`password` VARCHAR(20) NOT NULL," +
29            "`PRIMARY KEY (`id`));" ;
30
31
32    private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE if not exists `Coupons`. `categories`" +
33            "(`id` INT NOT NULL AUTO_INCREMENT," +
34            "`name` VARCHAR(20) NOT NULL," +
35            "`PRIMARY KEY (`id`));" ;
36
37
38    private static final String CREATE_TABLE_COUPONS = "CREATE TABLE if not exists `Coupons`.`coupons` " +
39            "(`id` INT NOT NULL AUTO_INCREMENT," +
40            "`company_id` INT NOT NULL," +
41            "`category_id` INT NOT NULL," +
42            "`title` VARCHAR(50) NOT NULL," +
43            "`description` VARCHAR(200) NOT NULL," +
44            "`start_date` DATE NOT NULL," +
45            "`end_date` DATE NOT NULL," +
46            "`amount` INT NOT NULL," +
47            "`price` DOUBLE NOT NULL," +
48            "`image` VARCHAR(150) NOT NULL," +
49            "PRIMARY KEY (`id`)," +
50            "FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE," +
51            "FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE CASCADE);";
52
53    private static final String CREATE_TABLE_CUSTOMERS_VS_COUPONS = "CREATE TABLE if not exists `Coupons`.`categories_vs_coupons` " +
54            "(`id` INT NOT NULL AUTO_INCREMENT," +
55            "`customer_id` INT NOT NULL," +
56            "`coupon_id` INT NOT NULL," +
57            "PRIMARY KEY (`customer_id`)," +
58            "PRIMARY KEY (`coupon_id`)," +
59            "FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE," +
60            "FOREIGN KEY(coupon_id) REFERENCES coupons(id) ON DELETE CASCADE);";
61}
62
63
64