LdeimA64

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`.`client`
10-- -----------------------------------------------------
11DROP TABLE IF EXISTS `cs165`.`client` ;
12
13CREATE  TABLE IF NOT EXISTS `cs165`.`client` (
14  `client_id` INT NOT NULL ,
15  `last_name` VARCHAR(45) NULL ,
16  `first_name` VARCHAR(45) NULL ,
17  `middle_name` VARCHAR(45) NULL ,
18  `present_address` VARCHAR(45) NULL ,
19  `contact_no` VARCHAR(45) NULL ,
20  `email_address` VARCHAR(45) NULL ,
21  PRIMARY KEY (`client_id`) )
22ENGINE = InnoDB;
23
24
25-- -----------------------------------------------------
26-- Table `cs165`.`account`
27-- -----------------------------------------------------
28DROP TABLE IF EXISTS `cs165`.`account` ;
29
30CREATE  TABLE IF NOT EXISTS `cs165`.`account` (
31  `account_id` INT NOT NULL ,
32  `account_username` VARCHAR(45) NULL ,
33  `account_password` VARCHAR(45) NULL ,
34  PRIMARY KEY (`account_id`) )
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `cs165`.`personnel`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `cs165`.`personnel` ;
42
43CREATE  TABLE IF NOT EXISTS `cs165`.`personnel` (
44  `personnel_id` INT NOT NULL ,
45  `first_name` VARCHAR(45) NULL ,
46  `last_name` VARCHAR(45) NULL ,
47  `middle_name` VARCHAR(45) NULL ,
48  `role` ENUM('Photographer','PR Manager','Assistant') NULL ,
49  `person_account_id` INT NULL ,
50  PRIMARY KEY (`personnel_id`) ,
51  INDEX `account_id` (`person_account_id` ASC) ,
52  CONSTRAINT `account_id`
53    FOREIGN KEY (`person_account_id` )
54    REFERENCES `cs165`.`account` (`account_id` )
55    ON DELETE NO ACTION
56    ON UPDATE NO ACTION)
57ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61-- Table `cs165`.`transaction`
62-- -----------------------------------------------------
63DROP TABLE IF EXISTS `cs165`.`transaction` ;
64
65CREATE  TABLE IF NOT EXISTS `cs165`.`transaction` (
66  `trans_id` INT NOT NULL ,
67  `trans_client_id` INT NULL ,
68  `package_option` VARCHAR(45) NULL ,
69  `service_type` VARCHAR(45) NULL ,
70  `venue` VARCHAR(45) NULL ,
71  `time` DATETIME NULL ,
72  `cost` DECIMAL(38) NULL ,
73  `done` INT NULL ,
74  PRIMARY KEY (`trans_id`) ,
75  INDEX `client_id` (`trans_client_id` ASC) ,
76  CONSTRAINT `client_id`
77    FOREIGN KEY (`trans_client_id` )
78    REFERENCES `cs165`.`client` (`client_id` )
79    ON DELETE NO ACTION
80    ON UPDATE NO ACTION)
81ENGINE = InnoDB;
82
83
84-- -----------------------------------------------------
85-- Table `cs165`.`trans_personnel`
86-- -----------------------------------------------------
87DROP TABLE IF EXISTS `cs165`.`trans_personnel` ;
88
89CREATE  TABLE IF NOT EXISTS `cs165`.`trans_personnel` (
90  `tp_trans_id` INT NULL ,
91  `tp_personnel_id` INT NULL ,
92  INDEX `trans_id` (`tp_trans_id` ASC) ,
93  INDEX `personnel_id` (`tp_personnel_id` ASC) ,
94  CONSTRAINT `trans_id`
95    FOREIGN KEY (`tp_trans_id` )
96    REFERENCES `cs165`.`transaction` (`trans_id` )
97    ON DELETE NO ACTION
98    ON UPDATE NO ACTION,
99  CONSTRAINT `personnel_id`
100    FOREIGN KEY (`tp_personnel_id` )
101    REFERENCES `cs165`.`personnel` (`personnel_id` )
102    ON DELETE NO ACTION
103    ON UPDATE NO ACTION)
104ENGINE = InnoDB;
105
106
107
108SET SQL_MODE=@OLD_SQL_MODE;
109SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
110SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;