· 9 years ago · Nov 16, 2016, 04:36 AM
1-----------------------------------------------------------
2----Crear base de datos -----
3-----------------------------------------------------------
4CREATE SCHEMA
5 IF NOT EXISTS `bdsyscitas` DEFAULT CHARACTER SET latin1 ;USE `bdsyscitas` ;
6 -------Crear tabla de base de datos ----------
7 --------------------------------------------------------
8 ------------ Table `Bdsyscitas`.`Dia`--------------
9 --------------------------------------------------------
10
11CREATE TABLE IF NOT EXISTS `bdsyscitas`.`dia`
12 (
13 `iddia` INT (11) NOT NULL auto_increment,
14 `dia` VARCHAR (45) NOT NULL,
15 `horainicio` VARCHAR (45) NOT NULL,
16 PRIMARY KEY (`iddia`)
17 )
18 engine = innodb auto_increment = 81 DEFAULT CHARACTER SET = latin1;
19
20 -- -----------------------------------------------------
21 -- Table `Bdsyscitas`.`Especialidad`----------
22 -- -----------------------------------------------------
23
24CREATE TABLE IF NOT EXISTS `bdsyscitas`.`especialidad`
25 (
26 `idespecialidad` VARCHAR (4) NOT NULL,
27 `nombres` VARCHAR (45) NOT NULL,
28 PRIMARY KEY (`idespecialidad`),
29 UNIQUE INDEX `nombres_unique` (`nombres` asc)
30 )
31 engine = innodb DEFAULT CHARACTER SET = utf8;
32
33 -- -----------------------------------------------------
34 -- -----Table `bdsyscitas`.`trabajador`-----------
35 -- -----------------------------------------------------
36
37CREATE TABLE IF NOT EXISTS `bdsyscitas`.`trabajador`
38 (
39 `idtrabajador` VARCHAR (10) NOT NULL,
40 `nombres` VARCHAR (100) NULL DEFAULT NULL,
41 `dni` VARCHAR (8) NOT NULL,
42 `direccion` VARCHAR (45) NULL DEFAULT NULL,
43 `email` VARCHAR (50) NOT NULL,
44 `cargo` VARCHAR (50) NOT NULL,
45 `telefono` VARCHAR (45) NOT NULL,
46 `foto` VARCHAR (200) NULL DEFAULT NULL,
47 `idespecialidad` VARCHAR (4) NOT NULL,
48 PRIMARY KEY (`idtrabajador`, `idespecialidad`),
49 INDEX `fk_trabajador_especialidad_idx` (`idespecialidad` asc),
50 CONSTRAINT `fk_trabajador_especialidad` FOREIGN KEY (`idespecialidad`) REFERENCES `bdsyscitas`.`especialidad` (`idespecialidad`) ON
51 DELETE no action
52 ON
53 UPDATE no action
54 )
55 engine = innodb DEFAULT CHARACTER SET = utf8;
56
57 -- -----------------------------------------------------
58 -- -------Table `bdsyscitas`.`horario`------------
59 -- -----------------------------------------------------
60
61CREATE TABLE IF NOT EXISTS `bdsyscitas`.`horario`
62 (
63 `idhorario` INT (11) NOT NULL auto_increment,
64 `idtrabajador` VARCHAR (10) NOT NULL,
65 `iddia` INT (11) NOT NULL,
66 `estado` VARCHAR (45) NULL DEFAULT NULL,
67 PRIMARY KEY (`idhorario`, `idtrabajador`, `iddia`),
68 INDEX `fk_horario_trabajador1_idx` (`idtrabajador` asc),
69 INDEX `fk_horario_dia1_idx` (`iddia` ASC),
70 CONSTRAINT `fk_horario_dia1` FOREIGN KEY (`iddia`) REFERENCES `bdsyscitas`.`dia` (`iddia`) ON
71 DELETE no action
72 ON
73 UPDATE no action,
74 CONSTRAINT `fk_horario_trabajador1` FOREIGN KEY (`idtrabajador`) REFERENCES `bdsyscitas`.`trabajador` (`idtrabajador`)
75 ON
76 DELETE no action
77 ON
78 UPDATE no action
79 )
80 engine = innodb auto_increment = 38 DEFAULT CHARACTER SET = utf8;
81
82 -- -----------------------------------------------------
83 -- -----Table `bdsyscitas`.`paciente`-------------
84 -- -----------------------------------------------------
85
86CREATE TABLE IF NOT EXISTS `bdsyscitas`.`paciente`
87 (
88 `idpaciente` VARCHAR (10) NOT NULL,
89 `nombres` VARCHAR (100) NULL DEFAULT NULL,
90 `fechanacimiento` DATE NULL DEFAULT NULL,
91 `dni` VARCHAR (8) NULL DEFAULT NULL,
92 `email` VARCHAR (50) NULL DEFAULT NULL,
93 `telefono` VARCHAR (45) NULL DEFAULT NULL,
94 `codigo` VARCHAR (10) NULL DEFAULT NULL,
95 `foto` VARCHAR (200) NULL DEFAULT NULL,
96 PRIMARY KEY (`idpaciente`),
97 UNIQUE INDEX `dni_unique` (`dni` asc),
98 UNIQUE INDEX `codigo_unique` (`codigo` ASC)
99 )
100 engine = innodb DEFAULT CHARACTER SET = utf8;
101
102 -- -----------------------------------------------------
103 ----------- Table `bdsyscitas`.`cita`--------------
104
105CREATE TABLE IF NOT EXISTS `bdsyscitas`.`cita`
106 (
107 `idcita` INT(11) NOT NULL auto_increment,
108 `fecha` DATETIME NULL DEFAULT NULL,
109 `estado` VARCHAR(45) NULL DEFAULT NULL,
110 `idpaciente` VARCHAR(10) NOT NULL,
111 `idhorario` INT(11) NOT NULL,
112 PRIMARY KEY (`idcita`, `idpaciente`, `idhorario`),
113 INDEX `fk_cita_paciente1_idx` (`idpaciente` asc),
114 INDEX `fk_cita_horario1_idx` (`idhorario` ASC),
115 CONSTRAINT `fk_cita_horario1` FOREIGN KEY (`idhorario`) REFERENCES `bdsyscitas`.`horario` (`idhorario`) ON
116 DELETE no action
117 ON
118 UPDATE no action,
119 CONSTRAINT `fk_cita_paciente1` FOREIGN KEY (`idpaciente`) REFERENCES `bdsyscitas`.`paciente` (`idpaciente`)
120 ON
121 DELETE no action
122 ON
123 UPDATE no action
124 )
125 engine = innodb auto_increment = 17 DEFAULT CHARACTER SET = utf8;
126
127 -- -----------------------------------------------------
128 -- -------Table `bdsyscitas`.`sancion`------------CREATE TABLE IF NOT EXISTS `bdsyscitas`.`sancion`
129 (
130 `idsancion` INT(11) NOT NULL auto_increment,
131 `fechasancion` DATE NULL DEFAULT NULL,
132 `fechavencimiento` DATE NULL DEFAULT NULL,
133 `estado` VARCHAR (45) NULL DEFAULT NULL,
134 `idpaciente` VARCHAR (10) NOT NULL,
135 PRIMARY KEY (`idsancion`, `idpaciente`),
136 INDEX `fk_sancion_paciente1_idx` (`idpaciente` asc),
137 CONSTRAINT `fk_sancion_paciente1` FOREIGN KEY (`idpaciente`) REFERENCES `bdsyscitas`.`paciente` (`idpaciente`) ON
138 DELETE no action
139 ON
140 UPDATE no action
141 )
142 engine = innodb auto_increment = 5 DEFAULT CHARACTER SET = utf8;
143
144 -- -----------------------------------------------------
145 -- -----Table `bdsyscitas`.`usuario`--------------
146
147CREATE TABLE IF NOT EXISTS `bdsyscitas`.`usuario`
148 (
149 `idusuario` INT (11) NOT NULL auto_increment,
150 `email` VARCHAR (50) NOT NULL,
151 `password` VARCHAR (45) NOT NULL,
152 `tipo` VARCHAR (45) NOT NULL,
153 `habilitado` BIT (1) NOT NULL,
154 `fecharegistro` VARCHAR (45) NULL DEFAULT NULL,
155 PRIMARY KEY (`idusuario`)
156 )
157 engine = innodb auto_increment = 19 DEFAULT CHARACTER SET = utf8;USE `bdsyscitas` ;