eKiQhfua

1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
2
3--Creating Database
4CREATE DATABASE IF NOT EXISTS `iceemployeesdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
5USE `iceemployeesdb`;
6
7--Creating Tables
8CREATE TABLE IF NOT EXISTS `departments` (
9  `ID` int(11) NOT NULL AUTO_INCREMENT,
10  `department_name` varchar(50) NOT NULL,
11  PRIMARY KEY (`ID`)
12) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
13
14CREATE TABLE IF NOT EXISTS `employees` (
15  `ID` int(11) NOT NULL AUTO_INCREMENT,
16  `name` varchar(100) NOT NULL,
17  `contact_number` varchar(20) NOT NULL,
18  `email` varchar(50) NOT NULL,
19  `job_type_id` int(11) NOT NULL,
20  PRIMARY KEY (`ID`),
21  KEY `job_type_id` (`job_type_id`)
22) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
23
24CREATE TABLE IF NOT EXISTS `employee_department` (
25  `ID` int(11) NOT NULL AUTO_INCREMENT,
26  `employee_id` int(11) NOT NULL,
27  `department_id` int(11) NOT NULL,
28  PRIMARY KEY (`ID`),
29  UNIQUE KEY `unique_index` (`employee_id`,`department_id`),
30  KEY `employee_id` (`employee_id`),
31  KEY `department_id` (`department_id`)
32) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
33
34CREATE TABLE IF NOT EXISTS `job_types` (
35  `ID` int(11) NOT NULL AUTO_INCREMENT,
36  `job_type` varchar(50) NOT NULL,
37  PRIMARY KEY (`ID`)
38) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
39
40CREATE TABLE IF NOT EXISTS `salaries` (
41  `ID` int(11) NOT NULL AUTO_INCREMENT,
42  `employee_id` int(11) NOT NULL,
43  `salary` decimal(8,2) NOT NULL,
44  `date` date NOT NULL,
45  PRIMARY KEY (`ID`),
46  KEY `employee_id` (`employee_id`)
47) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
48
49--Adding Foreign Key Constraints
50ALTER TABLE `employees`
51  ADD CONSTRAINT `job_type_constraint` FOREIGN KEY (`job_type_id`) REFERENCES `job_types` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
52
53ALTER TABLE `employee_department`
54  ADD CONSTRAINT `department_constraint` FOREIGN KEY (`department_id`) REFERENCES `departments` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
55  ADD CONSTRAINT `employee_constraint` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
56
57ALTER TABLE `salaries`
58  ADD CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`ID`);
59  
60--Dumping Sample Data
61INSERT INTO `departments` (`ID`, `department_name`) VALUES
62(1, 'Software Development'),
63(2, 'Networking'),
64(3, 'Certifications');
65
66INSERT INTO `employees` (`ID`, `name`, `contact_number`, `email`, `job_type_id`) VALUES
67(1, 'Alex Spiteri', '79281426', 'aspiteri6@gmail.com', 2),
68(3, 'Peppi Azzopardi', '21249200', 'info@xarabank.com', 1);
69
70INSERT INTO `employee_department` (`ID`, `employee_id`, `department_id`) VALUES
71(1, 1, 1),
72(2, 1, 2),
73(5, 1, 3),
74(7, 3, 1);
75
76INSERT INTO `job_types` (`ID`, `job_type`) VALUES
77(1, 'part_time'),
78(2, 'full_time');
79
80INSERT INTO `salaries` (`ID`, `employee_id`, `salary`, `date`) VALUES
81(1, 1, '35000.00', '2015-07-30'),
82(2, 3, '12500.50', '2015-07-30'),
83(3, 1, '33000.50', '2014-12-01');