72dHP5AR

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `cs165` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `cs165` ;
7
8-- -----------------------------------------------------
9-- Table `cs165`.`personnel`
10-- -----------------------------------------------------
11CREATE  TABLE IF NOT EXISTS `cs165`.`personnel` (
12  `personnel_id` INT NOT NULL ,
13  `first_name` VARCHAR(45) NULL ,
14  `last_name` VARCHAR(45) NULL ,
15  `middle_name` VARCHAR(45) NULL ,
16  `role` ENUM('Photographer','PR Manager','Assistant') NULL ,
17  PRIMARY KEY (`personnel_id`) )
18ENGINE = InnoDB;
19
20
21-- -----------------------------------------------------
22-- Table `cs165`.`client`
23-- -----------------------------------------------------
24CREATE  TABLE IF NOT EXISTS `cs165`.`client` (
25  `client_id` INT NOT NULL ,
26  `personnel_id` INT NULL ,
27  `last_name` VARCHAR(45) NULL ,
28  `first_name` VARCHAR(45) NULL ,
29  `middle_name` VARCHAR(45) NULL ,
30  `present_address` VARCHAR(45) NULL ,
31  `contact_no` VARCHAR(45) NULL ,
32  `email_address` VARCHAR(45) NULL ,
33  PRIMARY KEY (`client_id`) ,
34  INDEX `personnel_id` () ,
35  CONSTRAINT `personnel_id`
36    FOREIGN KEY ()
37    REFERENCES `cs165`.`personnel` ()
38    ON DELETE NO ACTION
39    ON UPDATE NO ACTION)
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `cs165`.`transaction`
45-- -----------------------------------------------------
46CREATE  TABLE IF NOT EXISTS `cs165`.`transaction` (
47  `trans_id` INT NOT NULL ,
48  `client_id` INT NULL ,
49  `personnel_id` INT NULL ,
50  `package_option` VARCHAR(45) NULL ,
51  `service_type` VARCHAR(45) NULL ,
52  `venue` VARCHAR(45) NULL ,
53  `time` DATETIME NULL ,
54  `cost` DECIMAL(38) NULL ,
55  PRIMARY KEY (`trans_id`) ,
56  INDEX `client_id` () ,
57  INDEX `personnel_id` () ,
58  CONSTRAINT `client_id`
59    FOREIGN KEY ()
60    REFERENCES `cs165`.`client` ()
61    ON DELETE NO ACTION
62    ON UPDATE NO ACTION,
63  CONSTRAINT `personnel_id`
64    FOREIGN KEY ()
65    REFERENCES `cs165`.`personnel` ()
66    ON DELETE NO ACTION
67    ON UPDATE NO ACTION)
68ENGINE = InnoDB;
69
70
71
72SET SQL_MODE=@OLD_SQL_MODE;
73SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
74SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;