98LX5YwH

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 ) 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
17VALUES 
18	(NULL, 'Ivan', 'Ivanov', 'Gechev', 'nay-dobriya@mail.bg', '1234567890', 'Sofia'),
19    (NULL, 'bay', 'Ivan', 'traktorista', 'traktor@tor.bg', '0987654321', 'Gorno Nadolnishte');
20
21CREATE TABLE `cableCompany`.`accounts` (
22	`accountID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
23	`amount` DOUBLE NOT NULL ,
24	`customer_id` INT UNSIGNED NOT NULL ,
25	CONSTRAINT FOREIGN KEY ( `customer_id` )
26		REFERENCES `cableCompany`.`customers` ( `customerID` )
27		ON DELETE RESTRICT ON UPDATE CASCADE
28) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
29
30INSERT INTO accounts
31VALUES 
32	(NULL, 50, 1),
33	(NULL, 1000000.5, 1),
34    (NULL, 0.1, 2);
35
36CREATE TABLE `cableCompany`.`plans` (
37	`planID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
38	`name` VARCHAR(32) NOT NULL,
39	`monthly_fee` DOUBLE NOT NULL
40) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
41
42INSERT INTO plans
43VALUES
44	(NULL, 'plan-A', 29.9),
45    (NULL, 'plan-B', 39.9);
46
47CREATE TABLE `cableCompany`.`payments`(
48	`paymentID` INT AUTO_INCREMENT PRIMARY KEY ,
49	`paymentAmount` DOUBLE NOT NULL ,
50	`month` TINYINT NOT NULL ,
51	`year` YEAR NOT NULL ,
52	`dateOfPayment` DATETIME NOT NULL ,
53	`customer_id` INT UNSIGNED NOT NULL ,
54	`plan_id` INT UNSIGNED NOT NULL ,		
55	CONSTRAINT FOREIGN KEY ( `customer_id` )
56		REFERENCES `cableCompany`.`customers`( `customerID` ) ,
57	CONSTRAINT FOREIGN KEY ( `plan_id` ) 
58		REFERENCES `cableCompany`.`plans` ( `planID` ) ,
59	UNIQUE KEY ( `customer_id`, `plan_id`,`month`,`year` )
60)ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
61
62INSERT INTO payments
63VALUES 
64	(NULL, 29.9, 1, 2000, '2000-01-01', 1, 1),
65    (NULL, 39.9, 2, 2000, '2000-02-01', 1, 2),
66    (NULL, 29.9, 3, 2000, '2000-03-01', 2, 1);
67
68CREATE TABLE `cableCompany`.`debtors`(
69	`customer_id` INT UNSIGNED NOT NULL ,
70	`plan_id` INT UNSIGNED NOT NULL ,
71	`debt_amount` DOUBLE NOT NULL ,
72	FOREIGN KEY ( `customer_id` )
73		REFERENCES `cableCompany`.`customers`( `customerID` ) ,
74	FOREIGN KEY ( `plan_id` )
75		REFERENCES `cableCompany`.`plans`( `planID` ) ,
76	PRIMARY KEY ( `customer_id`, `plan_id` )
77) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;