PROYECTO FASE 1 - GRUPO 10 

ACÁ PODRÁS VER TODO EL TRABAJO DE LA FASE 1 

https://drive.google.com/file/d/1gQMeyHPfWuTLGkPpuLANLjPLSR7mEpU1/view?usp=sharing

CÓDIGO 

3. Diseñe la base de datos para la aplicación, debe contener al menos 5

tablas, relacione las tablas (llaves foráneas)

Deben incluir en el trabajo los comandos (scripts) de creación de BD y

tablas.

5. En cada tabla ingrese 10 registros (vía sentencias insert)

create database BDI_Urgencias_Clinicas

use BDI_Urgencias_Clinicas

create table Paciente(

id_paciente int not null primary key identity,

nom_paciente varchar(50) not null,

apellido_paciente varchar(50) not null,

direccion_paciente varchar(50) not null,

fecha_ingreso datetime not null,

cedula_paciente int not null

)

insert into Paciente values('santiago','marin','carrera

68a',CURRENT_TIMESTAMP)

insert into Paciente values('andres','gallego','carrera

62b',CURRENT_TIMESTAMP)

insert into Paciente values('juan','osorio','carrera

61c',CURRENT_TIMESTAMP)

insert into Paciente values('sofia','marin','carrera

63b',CURRENT_TIMESTAMP)

insert into Paciente values('salome','arias','carrera

63a',CURRENT_TIMESTAMP)

insert into Paciente values('veronica','ramirez','carrera

78a',CURRENT_TIMESTAMP)

insert into Paciente values('james','rodriguez','carrera

58a',CURRENT_TIMESTAMP)

insert into Paciente values('mateo','carvajal','carrera

28a',CURRENT_TIMESTAMP)

insert into Paciente values('mateo','ospina','carrera

88a',CURRENT_TIMESTAMP)

insert into Paciente values('manuela','osorio','carrera

38c',CURRENT_TIMESTAMP)

select* from Paciente

create table farmacia(

id_medicamento int not null primary key identity,

nom_farmacia varchar(50) not null,

fecha_caducidad date not null,

precio int not null,

)

insert into farmacia values('Abacavir',CURRENT_TIMESTAMP,10000)

insert into farmacia values('Abatacept',CURRENT_TIMESTAMP,20000)

insert into farmacia values('Acitretin',CURRENT_TIMESTAMP,25000)

insert into farmacia values('Aciclovir',CURRENT_TIMESTAMP,35000)

insert into farmacia values('Alectinib',CURRENT_TIMESTAMP,55000)

insert into farmacia values('Amikacina',CURRENT_TIMESTAMP,55000)

insert into farmacia values('Acetaminofen',CURRENT_TIMESTAMP,15000)

insert into farmacia values('Ampicilina',CURRENT_TIMESTAMP,35000)

insert into farmacia values('Apomorfina',CURRENT_TIMESTAMP,35000)

insert into farmacia values('Asparaginase',CURRENT_TIMESTAMP,35000)

select * from farmacia

create table area(

id_area int not null primary key identity,

nom_area varchar(50) not null

)

insert into area values('Urgencias')

insert into area values('Comedor')

insert into area values('farmacia')

insert into area values('cuidados intensivos')

insert into area values('rayos x')

insert into area values('sala de espera')

insert into area values('Odontologia')

insert into area values('Pediatria')

insert into area values('Medico general')

insert into area values('ginecologia')

select * from area

create table empleado(

id_empleado int not null primary key identity,

nom_empleado varchar(50) not null,

apellido_empleado varchar(50) not null,

puesto_empleado varchar(50) not null,

id_area int not null,

telefono int not null,

sueldo_empleado int not null,

id_sub int not null,

foreign key(id_area)references area(id_area)

on delete cascade on update cascade

)

insert into empleado

values('vanesa','ramirez','enfermera',1,455,3046706,14000)

insert into empleado values('jose','ocampo','Doctor',2,455,3046734,1450)

insert into empleado values('jorge','vanegas','especialista

',3,320,3076106,12000)

insert into empleado

values('viviana','perez','odontologo',4,320,3045671,13000)

insert into empleado

values('jhoana','ramirez','cardiologo',5,290,3035490,12600)

insert into empleado

values('alex','hernandez','enfermero',6,455,3307431,15600)

insert into empleado values('juliana','florez','enfermera',7,455,3044422,16000)

insert into empleado values('mateo','gallego','auxiliar',8,320,3056600,13000)

insert into empleado

values('daniela','ospina','oftamologo',9,290,3043450,12000)

insert into empleado

values('diego','vanegas','dermatologo',10,290,3042241,11800)

create table consulta(

id_consulta int not null primary key identity,

id_paciente int not null,

id_empleado int not null,

id_area int not null,

fecha_consulta date not null,

enfermedad varchar(50) not null,

estado varchar(50) not null,

id_medicamento int not null,

foreign key(id_paciente)references Paciente(id_paciente)

on delete cascade on update cascade,

foreign key(id_empleado)references empleado(id_empleado)

on delete cascade on update cascade,

foreign key(id_medicamento)references farmacia(id_medicamento)

on delete cascade on update cascade,

foreign key(id_area)references area(id_area)

)

insert into consulta

values(1,1,1,CURRENT_TIMESTAMP,'varicela','grave',1)

insert into consulta values(2,2,2,CURRENT_TIMESTAMP,'covid','normal',2)

insert into consulta values(3,3,3,CURRENT_TIMESTAMP,'gripa','muy

grave',3)

insert into consulta values(4,4,4,CURRENT_TIMESTAMP,'dolor de

cabeza','muy grave',4)

insert into consulta values(5,5,5,CURRENT_TIMESTAMP,'sida','grave',5)

insert into consulta

values(6,6,6,CURRENT_TIMESTAMP,'malestar','normal',6)

insert into consulta

values(7,7,7,CURRENT_TIMESTAMP,'apendicitis','normal',7)

insert into consulta values(8,8,8,CURRENT_TIMESTAMP,'alergia','grave',8)

insert into consulta values(9,9,9,CURRENT_TIMESTAMP,'dolor de

muela','normal',9)

insert into consulta values(10,10,10,CURRENT_TIMESTAMP,'diabetes',

'muy grave',10)

create table factura(

id_factura int not null primary key identity,

id_consulta int not null,

id_paciente int not null,

id_medicamento int not null,

nom_factura varchar(50) not null,

direccion varchar(50) not null,

fecha_factura date not null,

telefono int not null,

NIT int not null,

cantidad_medicamento int not null,

total_apagar int not null,

foreign key(id_consulta)references consulta(id_consulta),

foreign key(id_paciente)references Paciente(id_paciente),

foreign key(id_medicamento)references farmacia(id_medicamento)

)

insert into factura values(1,1,1,'cita medica','carrera

79c',CURRENT_TIMESTAMP,4253252,23344,5,656565)

insert into factura values(2,2,2,'cita medica','carrera

79c',CURRENT_TIMESTAMP,42534,2233,1,6565)

insert into factura values(3,3,3,'radiografia','carrera

72c',CURRENT_TIMESTAMP,2223252,44344,7,633565)

insert into factura values(4,4,4,'cita pedriata','carrera

73c',CURRENT_TIMESTAMP,6777,2567,6,67865)

insert into factura values(5,5,5,'cita medica','carrera

63c',CURRENT_TIMESTAMP,33252,24344,8,32435)

insert into factura values(6,6,6,'cuidados intensivos','carrera

49b',CURRENT_TIMESTAMP,883252,22344,10,336565)

insert into factura values(7,7,7,'cita medica','carrera

79c',CURRENT_TIMESTAMP,4253252,23344,11,656565)

insert into factura values(8,8,8,'cita odontologia','carrera

39c',CURRENT_TIMESTAMP,23252,4544,3,556565)

insert into factura values(9,9,9,'cita odontologia','carrera

69c',CURRENT_TIMESTAMP,4562,3245,2,62365)

insert into factura values(10,10,10,'cita medica','carrera

79c',CURRENT_TIMESTAMP,422,344,2,665)

6. Escriba 2 consultas del CRUD (insertar, seleccionar, actualizar,

borrar) y muestre las resultados.

/* SELECCION DE DATOS */

select* from factura where nom_factura='cita medica' and direccion ='carrera

79c'

select* from Paciente where nom_paciente ='santiago' or nom_paciente

='mateo'

select* from consulta where enfermedad='covid' and estado='normal'

select * from factura where id_consulta=1 or id_medicamento=2

select * from empleado where puesto_empleado ='auxiliar'

/* BORRAR DATOS */

delete from factura where factura.cantidad_medicamento < 4

delete from consulta where consulta.estado ='normal'

delete from area where area.nom_area = 'rayos x'

/* ACTUALIZAR DATOS */

update consulta set estado = 'grave'

where estado = 'normal'

update area set nom_area = 'pediatra'

where nom_area = 'Medico general'

update paciente set nom_paciente = 'andres'

where nom_paciente = 'santiago'