FfAAD9EB

1create database LantPoliclinici;
2
3use LantPoliclinici;
4
5create table if not exists unitate_medicala(
6id int unique not null primary key,
7nume varchar(20),
8adresa varchar(20));
9
10create table if not exists program_unitate_medicala(
11id int primary key,
12ora_deschidere time,
13ora_inchidere time,
14zi_saptamana varchar(20),
15foreign key (id) references unitate_medicala(id)
16);
17
18create table if not exists utilizator(
19CNP varchar(10) unique primary key,
20nume varchar(20),
21prenume varchar(20),
22adresa varchar(20),
23nr_tel varchar(10),
24email varchar(20),
25IBAN varchar(30),
26nr_contact varchar(10),
27data_angajare date,
28functie varchar(20));
29
30create table if not exists asistent(
31CNP varchar(20) primary key,
32tip_asistent varchar(10),
33grad_asistent varchar(10),
34foreign key (CNP) references angajat(CNP));
35
36create table if not exists angajat(
37CNP varchar(10) primary key,
38salariu int,
39nr_ore int,
40foreign key (CNP) references utilizator(CNP));
41
42create table if not exists user(
43CNP varchar(10) primary key,
44username varchar(20),
45password varchar(20),
46foreign key (CNP) references utilizator(CNP));
47
48create table if not exists medic(
49CNP varchar(10) primary key,
50grad_medic varchar(10),
51cod_parafa varchar(10) unique,
52titlu_stiintific varchar(20),
53post_didactic varchar(20),
54procent_servicii int,
55foreign key (CNP) references angajat(CNP));
56
57create table if not exists competenta(
58id int unique primary key,
59denumire_competenta varchar(20));
60
61create table if not exists competente_medic(
62CNP_medic varchar(10),
63id_competenta int,
64foreign key (CNP_medic) references medic(CNP),
65foreign key (id_competenta) references competenta(id));
66
67select nume,denumire_competenta from utilizator join competenta
68join competente_medic on utilizator.CNP=competente_medic.CNP_medic and competenta.id=competente_medic.id_competenta;
69
70create table if not exists serviciu_medical(
71id int unique primary key,
72grad_medic varchar(20),
73denumire varchar(20),
74specialitate varchar(20),
75pret int,
76durata int);
77
78create table if not exists concediu(
79CNP varchar(10) primary key,
80data_inceput DATE,
81data_sfarsit DATE,
82foreign key (CNP) references angajat(CNP));
83
84create table if not exists salariu(
85CNP varchar(10) primary key,
86nr_ore_lucrate int,
87foreign key (CNP) references angajat(CNP));
88
89create table if not exists pacient(
90id int unique primary key,
91nume varchar(20),
92prenume varchar(20));
93
94create table if not exists programare(
95id int unique primary key,
96id_pacient int,
97data DATE,
98ora time,
99CNP_medic varchar(10),
100durata int,
101foreign key (id_pacient) references pacient(id));
102
103create table if not exists SM_programare(
104id_programare int,
105id_SM int,
106foreign key (id_programare) references programare(id),
107foreign key (id_SM) references serviciu_medical(id));
108
109create table if not exists istoric(
110id_pacient int,
111id_raport int,
112foreign key (id_pacient) references pacient(id),
113foreign key (id_raport) references raport(id));
114
115create table if not exists raport(
116id int unique primary key,
117id_pacient int,
118CNP_medic varchar(10),
119id_programare int,
120simptome varchar(30),
121investigatii varchar(30),
122diagnostic varchar(30),
123recomandari varchar(30),
124parafa varchar(10),
125foreign key (id_pacient) references pacient(id),
126foreign key (CNP_medic) references medic(CNP),
127foreign key (id_programare) references programare(id));