· 9 years ago · May 26, 2017, 03:18 PM
1DROP DATABASE IF EXISTS `cableCompany`;
2CREATE DATABASE `cableCompany`;
3USE `cableCompany`;
4
5CREATE TABLE `cableCompany`.`customers` (
6 `customerID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
7 `firstName` VARCHAR( 55 ) NOT NULL ,
8 `middleName` VARCHAR( 55 ) NOT NULL ,
9 `lastName` VARCHAR( 55 ) NOT NULL ,
10 `email` VARCHAR( 55 ) NOT NULL ,
11 `phone` VARCHAR( 20 ) NOT NULL ,
12 `address` VARCHAR( 255 ) NOT NULL ,
13 PRIMARY KEY ( `customerID` )
14) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
15
16insert into customers (firstName, middleName, lastName, email, phone, address) VALUES ('Darko', 'Andreev', 'Mikan', 'andreev.darko@gmail.com', '0899543', 'Vidlic 36');
17insert into customers (firstName, middleName, lastName, email, phone, address) VALUES ('Denis', 'Djamalov', 'Kalfov', 'd.kalfov@gmail.com', '089954123', 'Mladost 1');
18insert into customers (firstName, middleName, lastName, email, phone, address) VALUES ('Niki', 'Budinov', 'Zlatkov', 'd.budinow@gmail.com', '089776453', 'Nadejda 1');
19
20
21
22CREATE TABLE `cableCompany`.`accounts` (
23 `accountID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
24 `amount` DOUBLE NOT NULL ,
25 `customer_id` INT UNSIGNED NOT NULL ,
26 CONSTRAINT FOREIGN KEY ( `customer_id` )
27 REFERENCES `cableCompany`.`customers` ( `customerID` )
28 ON DELETE RESTRICT ON UPDATE CASCADE
29) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
30
31insert into accounts (amount, customer_id) VALUES (5467.87, 1);
32insert into accounts (amount, customer_id) VALUES (4567.23, 2);
33insert into accounts (amount, customer_id) VALUES (1998.32, 3);
34
35
36CREATE TABLE `cableCompany`.`plans` (
37 `planID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
38 `name` VARCHAR( 25 ) NOT NULL,
39 `monthly_fee` DOUBLE NOT NULL
40) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
41
42insert into plans (name, monthly_fee) VALUES ('plan1', 500);
43insert into plans (name, monthly_fee) VALUES ('plan2', 178);
44insert into plans (name, monthly_fee) VALUES ('plan3', 800);
45
46CREATE TABLE `cableCompany`.`contracts` (
47 `contractID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
48 `contractDate` DATE NOT NULL ,
49 `customer_id` INT UNSIGNED NOT NULL ,
50 `plan_id` INT UNSIGNED NOT NULL ,
51 CONSTRAINT FOREIGN KEY ( `customer_id` )
52 REFERENCES `cableCompany`.`customers`( `customerID` ) ,
53 CONSTRAINT FOREIGN KEY ( `plan_id` )
54 REFERENCES `cableCompany`.`plans` ( `planID` ) ,
55 UNIQUE KEY( `customer_id`, `plan_id` )
56) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
57
58insert into contracts (contractDate, customer_id, plan_id ) VALUES ('2017-05-25', 1, 2);
59insert into contracts (contractDate, customer_id, plan_id ) VALUES ('2013-03-14', 2, 1);
60insert into contracts (contractDate, customer_id, plan_id ) VALUES ('2016-11-10', 1, 3);
61
62
63
64
65CREATE TABLE `cableCompany`.`payments`(
66 `paymentID` INT AUTO_INCREMENT PRIMARY KEY ,
67 `contract_id` INT UNSIGNED NOT NULL ,
68 `paymentAmount` DOUBLE NOT NULL ,
69 `month` TINYINT NOT NULL ,
70 `year` YEAR NOT NULL ,
71 `dateOfPayment` DATETIME NOT NULL ,
72 CONSTRAINT FOREIGN KEY ( `contract_id` )
73 REFERENCES `cableCompany`.`contracts`( `contractID` ) ,
74 UNIQUE KEY( `contract_id`, `month`, `year` )
75)ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
76
77insert into payments (contract_id, paymentAmount, month, year, dateOfPayment) VALUES (2, 1768.34, 05, 2013, '2015-04-11');
78insert into payments (contract_id, paymentAmount, month, year, dateOfPayment) VALUES (1, 2367.22, 11, 2015, '2016-03-16');
79insert into payments (contract_id, paymentAmount, month, year, dateOfPayment) VALUES (3, 1567.11, 06, 2012, '2005-05-12');
80
81
82CREATE TABLE `cableCompany`.`debtors`(
83 `customer_id` INT UNSIGNED NOT NULL ,
84 `plan_id` INT UNSIGNED NOT NULL ,
85 `debt_amount` DOUBLE NOT NULL ,
86 FOREIGN KEY ( `customer_id` )
87 REFERENCES `cableCompany`.`customers`( `customerID` ) ,
88 FOREIGN KEY ( `plan_id` )
89 REFERENCES `cableCompany`.`plans`( `planID` ) ,
90 PRIMARY KEY ( `customer_id`, `plan_id` )
91) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
92
93insert into debtors (customer_id, plan_id, debt_amount) VALUES (3, 1, 865.13);