mmVVFB8c

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