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