ch6sUfzF

1CREATE TABLE IF NOT EXISTS `employees` 
2(
3/* CREATE THE COLUMNS AND DATATYPES FOR THE DATA */
4  `employeeRecordID` int NOT NULL AUTO_INCREMENT, 			/* employeeID = to store the ID of each employee entered on this table using an INT datatype and auto increment the value each new entry for the employees table */
5
6  `firstName` varchar(25) NOT NULL,							/* firstName = FK: to store the employee's first name using a VARCHAR datatype */
7  `lastName` varchar(25) NOT NULL,							/* lastName = FK: to store the employee's last name using a VARCHAR datatype */	
8   `gender` varchar(6) NOT NULL,						/* gender = FK: to store the employee's gender using a VARCHAR datatype */
9  `dob` date NOT NULL,										/* dob = FK: to store the employee's date of birth using a DATE datatype */
10  `salary` decimal(10,2) NOT NULL,							/* salary = FK: to store the employee's salary using a DECIMAL datatype */
11  `tel` varchar(10) NOT NULL,										/* tel = FK: to store the employee's telephone number using a INT datatype */
12  `phone` varchar(10) NOT NULL,										/* phone = FK: to store the employee's personal phone number using a INT datatype */
13  `department` varchar(25) DEFAULT NULL,					/* department = FK: to store the employee's department using a VARCHAR datatype */
14  `jobTitle` varchar(30) NOT NULL,							/* jobTitle = to store the employee's job title using a VARCHAR datatype */
15  `staffEmail` varchar(100) NOT NULL,						/* staffEmail = FK: to store the employee's staff email using a VARCHAR datatype */
16  PRIMARY KEY (`employeeID`),								/* Set PRIMARY KEY of the nurses table as, 'employeeID' */
17  UNIQUE KEY `employeeRecordID_UNIQUE` (`employeeRecordID`), 	/* Make the employeeID a UNIQUE KEY (UQ) so that the values have to be unique */
18  /* Doctor's Foreign Keys */
19  CONSTRAINT `doctor_firstName_FK` FOREIGN KEY (`firstName`) REFERENCES `doctors` (`firstName`) ON DELETE CASCADE,
20)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;