· 6 years ago · Jun 23, 2019, 08:08 AM
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
16CREATE TABLE `cableCompany`.`accounts` (
17 `accountID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
18 `amount` DOUBLE NOT NULL ,
19 `customer_id` INT UNSIGNED NOT NULL ,
20 CONSTRAINT FOREIGN KEY ( `customer_id` )
21 REFERENCES `cableCompany`.`customers` ( `customerID` )
22 ON DELETE RESTRICT ON UPDATE CASCADE
23) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
24
25CREATE TABLE `cableCompany`.`plans` (
26 `planID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
27 `name` VARCHAR( 25 ) NOT NULL,
28 `monthly_fee` DOUBLE NOT NULL
29) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
30
31CREATE TABLE `cableCompany`.`contracts` (
32 `contractID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
33 `contractDate` DATE NOT NULL ,
34 `customer_id` INT UNSIGNED NOT NULL ,
35 `plan_id` INT UNSIGNED NOT NULL ,
36 CONSTRAINT FOREIGN KEY ( `customer_id` )
37 REFERENCES `cableCompany`.`customers`( `customerID` ) ,
38 CONSTRAINT FOREIGN KEY ( `plan_id` )
39 REFERENCES `cableCompany`.`plans` ( `planID` ) ,
40 UNIQUE KEY( `customer_id`, `plan_id` )
41) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
42
43CREATE TABLE `cableCompany`.`payments`(
44 `paymentID` INT AUTO_INCREMENT PRIMARY KEY ,
45 `contract_id` INT UNSIGNED NOT NULL ,
46 `paymentAmount` DOUBLE NOT NULL ,
47 `month` TINYINT NOT NULL ,
48 `year` YEAR NOT NULL ,
49 `dateOfPayment` DATETIME NOT NULL ,
50 CONSTRAINT FOREIGN KEY ( `contract_id` )
51 REFERENCES `cableCompany`.`contracts`( `contractID` ) ,
52 UNIQUE KEY( `contract_id`, `month`, `year` )
53)ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
54
55CREATE TABLE `cableCompany`.`debtors`(
56 `customer_id` INT UNSIGNED NOT NULL ,
57 `plan_id` INT UNSIGNED NOT NULL ,
58 `debt_amount` DOUBLE NOT NULL ,
59 FOREIGN KEY ( `customer_id` )
60 REFERENCES `cableCompany`.`customers`( `customerID` ) ,
61 FOREIGN KEY ( `plan_id` )
62 REFERENCES `cableCompany`.`plans`( `planID` ) ,
63 PRIMARY KEY ( `customer_id`, `plan_id` )
64) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
65
66INSERT INTO customers (firstName, middleName, lastName, email, phone, address) VALUES
67('Kostadin', 'Hristov', 'Krushkov', 'kostadin01998@gmail.com', '0898268835', 'Studentski Grad 59B'),
68('Preslava', 'asdf', 'asdf', 'Preslava@gmail.com', '0898268835', 'Studentski Grad 4'),
69('Iva', 'fdsa', 'fdas', 'Iva@gmail.com', '0898268835', 'Ovcha kupel'),
70('Nikola', 'faaa', 'faaaaa', 'Nikola@gmail.com', '0898268835', 'Manchester'),
71('Rumen', 'ggggg', 'gggggg', 'Rumen@gmail.com', '0898268835', 'Sofia Grad 59B');
72
73INSERT INTO accounts (amount, customer_id) VALUES
74('51100', '1'),
75('312121', '4'),
76('21222', '5'),
77('15121', '3'),
78('51111', '2');
79
80INSERT INTO plans (name, monthly_fee) VALUES
81('A1', '14.99'),
82('Viva', '18'),
83('Comnet', '11'),
84('Telco', '70');
85
86INSERT INTO contracts (contractDate, customer_id, plan_id) VALUES
87('2019-05-11', '1', '1'),
88('2021-12-02', '5', '2'),
89('2012-11-01', '2', '1'),
90('2016-07-09', '4', '3'),
91('2011-05-11', '3', '4');
92
93INSERT INTO payments (contract_id, paymentAmount, month, year, dateOfPayment) VALUES
94('1','1231', '1','1998','2001-01-01 09:31:21'),
95('2','1131','2','2011','2011-01-01 09:31:21'),
96('3','4121','3','2014','2014-01-01 09:31:21'),
97('4','5111','4','2019','2019-01-01 09:31:21'),
98('5','511','5','2002','2003-01-01 09:31:21');
99
100INSERT INTO debtors (customer_id, plan_id, debt_amount) VALUES
101('1', '1', '69'),
102('5', '2', '0'),
103('2', '3', '1'),
104('3', '1', '11'),
105('4', '4', '100');
106
107SELECT * FROM payments;