CTOW/database.sql at master · thelittlewozniak/CTOW · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
-- -----------------------------------------------------
-- Table address
-- -----------------------------------------------------

CREATE TABLE  address (
  idaddress INT IDENTITY(1,1) NOT NULL,
  address_street VARCHAR(45) NULL,
  address_number VARCHAR(45) NULL,
  address_cp VARCHAR(45) NULL,
  address_city VARCHAR(45) NULL,
  address_country VARCHAR(45) NULL,
  PRIMARY KEY (idaddress))
-- -----------------------------------------------------
-- Table device
-- -----------------------------------------------------
CREATE TABLE  device (
  iddevice INT IDENTITY(1,1) NOT NULL,
  device_description VARCHAR(45) NULL,
  address_idaddress INT NOT NULL,
  PRIMARY KEY (iddevice),
  INDEX fk_device_address_idx (address_idaddress ASC),
  CONSTRAINT fk_device_address
    FOREIGN KEY (address_idaddress)
    REFERENCES address (idaddress)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
-- -----------------------------------------------------
-- Table type_data
-- -----------------------------------------------------
CREATE TABLE  type_data (
  idtype_data VARCHAR(45) NOT NULL,
  type_data_description VARCHAR(45) NULL,
  PRIMARY KEY (idtype_data))
-- -----------------------------------------------------
-- Table data
-- -----------------------------------------------------
CREATE TABLE  data (
  iddata INT IDENTITY(1,1) NOT NULL,
  value_data VARCHAR(45) NULL,
  type_idtype_data VARCHAR(45) NOT NULL,
  device_iddevice INT NOT NULL,
  date_time DATETIME NULL,
  PRIMARY KEY (iddata),
  INDEX fk_data_type1_idx (type_idtype ASC),
  INDEX fk_data_device1_idx (device_iddevice ASC),
  CONSTRAINT fk_data_type1
    FOREIGN KEY (type_idtype)
    REFERENCES type_data (idtype_data)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_data_device1
    FOREIGN KEY (device_iddevice)
    REFERENCES device (iddevice)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
-- -----------------------------------------------------
-- Table type_customer
-- -----------------------------------------------------
CREATE TABLE  type_customer (
  idtype_customer INT IDENTITY(1,1) NOT NULL,
  type_customer_description VARCHAR(45) NULL,
  PRIMARY KEY (idtype_customer))
-- -----------------------------------------------------
-- Table customer
-- -----------------------------------------------------
CREATE TABLE  customer (
  idcustomer INT IDENTITY(1,1) NOT NULL,
  customer_email VARCHAR(45) NULL,
  customer_password VARCHAR(45) NULL,
  type_customer_idtype_customer INT NOT NULL,
  PRIMARY KEY (idcustomer),
  INDEX fk_customer_type_customer1_idx (type_customer_idtype_customer ASC),
  CONSTRAINT fk_customer_type_customer1
    FOREIGN KEY (type_customer_idtype_customer)
    REFERENCES type_customer (idtype_customer)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
-- -----------------------------------------------------
-- Table customer_has_device
-- -----------------------------------------------------
CREATE TABLE  customer_has_device (
  customer_idcustomer INT NOT NULL,
  device_iddevice INT NOT NULL,
  PRIMARY KEY (customer_idcustomer, device_iddevice),
  INDEX fk_customer_has_device_device1_idx (device_iddevice ASC),
  INDEX fk_customer_has_device_customer1_idx (customer_idcustomer ASC),
  CONSTRAINT fk_customer_has_device_customer1
    FOREIGN KEY (customer_idcustomer)
    REFERENCES customer (idcustomer)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_customer_has_device_device1
    FOREIGN KEY (device_iddevice)
    REFERENCES device (iddevice)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)