qZA2thqN

1DROP SCHEMA IF EXISTS lantFarmacii;
2
3CREATE DATABASE IF NOT EXISTS lantFarmacii;
4use lantFarmacii;
5
6CREATE TABLE IF NOT EXISTS pacienti
7( id_pacient int primary key not null auto_increment,
8cnp bigint(13) NULL,
9nume varchar(45) NULL,
10adresa varchar(45) NULL,
11varsta int(3) NULL
12);
13
14CREATE TABLE IF NOT EXISTS doctori
15( id_doctor int primary key not null auto_increment,
16cnp bigint(13) NULL,
17nume varchar(45) NULL,
18specialitate varchar(45) NULL,
19experienta int(3) NULL
20);
21
22CREATE TABLE IF NOT EXISTS companie
23( id_companie int primary key not null auto_increment,
24nume varchar(45) NULL,
25adresa varchar(45) NULL,
26telefon varchar(45) NULL,
27id_medicament int not null
28);
29
30CREATE TABLE IF NOT EXISTS medicamente
31( id_medicament int primary key not null auto_increment,
32nume varchar(45) NULL,
33cod_formula varchar(45) NULL
34);
35
36CREATE TABLE IF NOT EXISTS farmacie
37( id_farmacie int primary key not null auto_increment,
38nume varchar(45),
39adresa varchar(45),
40telefon varchar(45),
41farmacist varchar(45)
42);
43
44CREATE TABLE IF NOT EXISTS contracte
45( id_companie int,
46id_farmacie int,
47data_inceput date,
48data_sfarsit date,
49text_contract varchar(45),
50supervizor varchar(45)
51);
52
53CREATE TABLE IF NOT EXISTS reteta
54(id_doctor int,
55id_medic int,
56id_pacient int
57);
58
59CREATE TABLE IF NOT EXISTS comercializare
60(id_medicamente int,
61id_farmacie int,
62pret int
63);
64
65CREATE TABLE IF NOT EXISTS pac_farm
66(	id_pacient int,
67farmacist varchar(45),
68PRIMARY KEY(farmacist)
69
70);
71
72ALTER TABLE reteta
73ADD FOREIGN KEY(id_doctor) REFERENCES doctori(id_doctor),
74ADD FOREIGN KEY(id_medic) REFERENCES medicamente(id_medicament),
75ADD FOREIGN KEY(id_pacient) REFERENCES pacienti(id_pacient);
76
77ALTER TABLE comercializare
78ADD FOREIGN KEY(id_medicamente) REFERENCES medicamente(id_medicament),
79ADD FOREIGN KEY(id_farmacie) REFERENCES farmacie(id_farmacie);
80
81ALTER TABLE pac_farm
82ADD FOREIGN KEY(id_pacient) REFERENCES pacienti(id_pacient);
83
84ALTER TABLE farmacie
85ADD FOREIGN KEY(farmacist) REFERENCES pac_farm(farmacist);
86
87ALTER TABLE companie
88ADD FOREIGN KEY(id_medicament) REFERENCES medicamente(id_medicament);
89
90ALTER TABLE contracte
91ADD FOREIGN KEY(id_companie) REFERENCES companie(id_companie),
92ADD FOREIGN KEY(id_farmacie) REFERENCES farmacie(id_farmacie);