Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
laboratoare:laborator00b [2015/10/08 17:03] Andrei Roșu-Cojocaru |
laboratoare:laborator00b [2015/10/18 19:55] (current) Andrei Roșu-Cojocaru [Activitate de Laborator] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ====== Laborator 00 (partea a II-a) ====== | ====== Laborator 00 (partea a II-a) ====== | ||
| - | ====== Sistemului de Gestiune pentru Baze de Date. MySQL (recapitulare) ====== | + | ====== Sisteme de Gestiune pentru Baze de Date. MySQL ====== |
| ===== Obiective ==== | ===== Obiective ==== | ||
| Line 15: | Line 15: | ||
| ===== Materiale Ajutătoare ====== | ===== Materiale Ajutătoare ====== | ||
| + | |||
| + | {{ :laboratoare:laborator00b:aipi2015-lab00b-slides.pdf }} | ||
| ===== Sisteme de Gestiune pentru Baze de Date ===== | ===== Sisteme de Gestiune pentru Baze de Date ===== | ||
| Line 96: | Line 98: | ||
| ''gestiune_organizatie = { id_proiect, nume_proiect, id_angajat, nume_angajat, pozitie_angajat, salariu_tarifar_angajat, ore_lucrate_angajat }'' | ''gestiune_organizatie = { id_proiect, nume_proiect, id_angajat, nume_angajat, pozitie_angajat, salariu_tarifar_angajat, ore_lucrate_angajat }'' | ||
| - | {{ :laboratoare:laborator01_faraFN.jpg?nolink&600 }} | + | {{ :laboratoare:laborator00b:01no_normal_form.png?nolink&600 }} |
| Această schemă de relaţie este caracterizată prin **inconsistenţa datelor** (acelaşi departament poate fi exprimat prin valori diferite) cât şi prin **redundanţă** (pentru fiecare angajat se reţin de mai multe ori numele, departamentul şi salariul tarifar - dacă a lucrat la mai multe proiecte), ceea ce determină anomalii la principalele operaţii de manipulare a datelor: | Această schemă de relaţie este caracterizată prin **inconsistenţa datelor** (acelaşi departament poate fi exprimat prin valori diferite) cât şi prin **redundanţă** (pentru fiecare angajat se reţin de mai multe ori numele, departamentul şi salariul tarifar - dacă a lucrat la mai multe proiecte), ceea ce determină anomalii la principalele operaţii de manipulare a datelor: | ||
| Line 118: | Line 120: | ||
| normală 1, reprezentată prin următoarea diagramă funcţională: | normală 1, reprezentată prin următoarea diagramă funcţională: | ||
| - | {{ :laboratoare:laborator01_FN1.jpg?nolink&600 }} | + | {{ :laboratoare:laborator00b:02normal_form_1.png?nolink&600 }} |
| === Forma Normală 2 (FN2) === | === Forma Normală 2 (FN2) === | ||
| Line 144: | Line 146: | ||
| ''(id_proiect, id_angajat) → ore_lucrate_angajat'' | ''(id_proiect, id_angajat) → ore_lucrate_angajat'' | ||
| - | {{ :laboratoare:laborator01_FN2.jpg?nolink&600 }} | + | {{ :laboratoare:laborator00b:03normal_form_2.png?nolink&600 }} |
| === Forma Normală 3 (FN3) === | === Forma Normală 3 (FN3) === | ||
| Line 165: | Line 167: | ||
| ''(id_proiect, id_angajat) → ore_lucrate_angajat'' | ''(id_proiect, id_angajat) → ore_lucrate_angajat'' | ||
| - | {{ :laboratoare:laborator01_FN3.jpg?nolink&600 }} | + | {{ :laboratoare:laborator00b:04normal_form_3.png?nolink&600 }} |
| ==== ==== | ==== ==== | ||
| Line 181: | Line 183: | ||
| ''magazin_apropiat_tip = { denumire_locatie, tip_magazin, denumire_magazin_apropiat }'' | ''magazin_apropiat_tip = { denumire_locatie, tip_magazin, denumire_magazin_apropiat }'' | ||
| - | {{ :laboratoare:laborator01_FNBCa.jpg?nolink&300 }} | + | {{ :laboratoare:laborator00b:05normal_form_boyce_codd_a.png?nolink&300 }} |
| Candidate pentru chei sunt ''(denumire_locatie, tip_magazin)'' sau ''(denumire_locatie, denumire_magazin_apropiat)''. Toate cele trei atribute sunt prime, deci schema face parte din FN3. | Candidate pentru chei sunt ''(denumire_locatie, tip_magazin)'' sau ''(denumire_locatie, denumire_magazin_apropiat)''. Toate cele trei atribute sunt prime, deci schema face parte din FN3. | ||
| Line 192: | Line 194: | ||
| ② ''specializare = {__denumire_magazin_apropiat__, __tip_magazin__}'' | ② ''specializare = {__denumire_magazin_apropiat__, __tip_magazin__}'' | ||
| - | {{ :laboratoare:laborator01_FNBCb.jpg?nolink&300 }} | + | {{ :laboratoare:laborator00b:06normal_form_boyce_codd_b.png?nolink&300 }} |
| O problemă o reprezintă totuşi faptul că schema de relaţie permite ca pentru o ''denumire_locatie'' să se specifice mai multe ''denumire_magazin_apropiat'' de acelaşi tip. | O problemă o reprezintă totuşi faptul că schema de relaţie permite ca pentru o ''denumire_locatie'' să se specifice mai multe ''denumire_magazin_apropiat'' de acelaşi tip. | ||
| Line 208: | Line 210: | ||
| Pentru un arbore B+ de ordin p, sunt îndeplinite următoarele caracteristici: | Pentru un arbore B+ de ordin p, sunt îndeplinite următoarele caracteristici: | ||
| - | - nodurile interne au structura ''<P<sub>1</sub>, K<sub>1</sub>, P<sub>2</sub>, K<sub>2</sub>, ..., P<sub>q-1</sub>, K<sub>q-1</sub>, P<sub>q</sub>>'', ''q ≤ p'', unde ''P<sub>i</sub>'' reprezintă un pointer către un subarbore, iar ''K<sub>i</sub>'' reprezintă valori ale cheilor de căutare ordonate crescător: ''K<sub>1</sub> < K<sub>2</sub> < ... < K<sub>q-1</sub>''. Dacă se caută o valoare ''X'' în subardorele referit de ''P<sub>i</sub>'' atunci ''K<sub>i-1</sub> < X ≤ K<sub>i</sub>'', cu ''i ≠ 1'' şi ''i ≠ q'' (dacă ''i=1'', ''X ≤ K<sub>1</sub>'', dacă ''i=q'', ''X > K<sub>q-1</sub>''). Orice nod intern are cel mult ''p'' si cel puţin ''[ (p/2) ]'' pointeri către subarbori (nodul rădăcină are cel puţin 2 pointeri, dacă este nod intern). Un nod intern cu ''q'' pointeri are ''q-1'' valori ale câmpului de căutare.\\ {{ :laboratoare:laborator01_b_nodintern.jpg?nolink&500 }} | + | - nodurile interne au structura ''<P<sub>1</sub>, K<sub>1</sub>, P<sub>2</sub>, K<sub>2</sub>, ..., P<sub>q-1</sub>, K<sub>q-1</sub>, P<sub>q</sub>>'', ''q ≤ p'', unde ''P<sub>i</sub>'' reprezintă un pointer către un subarbore, iar ''K<sub>i</sub>'' reprezintă valori ale cheilor de căutare ordonate crescător: ''K<sub>1</sub> < K<sub>2</sub> < ... < K<sub>q-1</sub>''. Dacă se caută o valoare ''X'' în subardorele referit de ''P<sub>i</sub>'' atunci ''K<sub>i-1</sub> < X ≤ K<sub>i</sub>'', cu ''i ≠ 1'' şi ''i ≠ q'' (dacă ''i=1'', ''X ≤ K<sub>1</sub>'', dacă ''i=q'', ''X > K<sub>q-1</sub>''). Orice nod intern are cel mult ''p'' si cel puţin ''[ (p/2) ]'' pointeri către subarbori (nodul rădăcină are cel puţin 2 pointeri, dacă este nod intern). Un nod intern cu ''q'' pointeri are ''q-1'' valori ale câmpului de căutare.\\ {{ :laboratoare:laborator00b:07Bplus_tree_internal_node.png?nolink&500 }} |
| - | - nodurile frunză au structura ''< <K<sub>1</sub>, Pr<sub>1</sub>>, <K<sub>2</sub>, Pr<sub>2</sub>>, ..., <K<sub>q-1</sub>, Pr<sub>q-1</sub>>, P<sub>urm</sub> >'', ''q ≤ p'', unde ''Pr<sub>i</sub>'' reprezintă un pointer către datele cărora le corespunde valoarea cheii de căutare ''K<sub>i</sub>'' (acestea fiind de asemenea ordonate crescător ''K<sub>1</sub> ≤ K<sub>2</sub> ≤ ... ≤ K<sub>q-1</sub>''), iar ''P<sub>urm</sub>'' este un pointer către următorul nod frunză (ţinând cont de faptul că toate nodurile frunză se găsesc pe acelaşi nivel). Fiecare nod frunză are cel puţin ''[ (p/2) ]'' valori.\\ {{ :laboratoare:laborator01_b_nodfrunza.jpg?nolink&500 }} | + | - nodurile frunză au structura ''< <K<sub>1</sub>, Pr<sub>1</sub>>, <K<sub>2</sub>, Pr<sub>2</sub>>, ..., <K<sub>q-1</sub>, Pr<sub>q-1</sub>>, P<sub>urm</sub> >'', ''q ≤ p'', unde ''Pr<sub>i</sub>'' reprezintă un pointer către datele cărora le corespunde valoarea cheii de căutare ''K<sub>i</sub>'' (acestea fiind de asemenea ordonate crescător ''K<sub>1</sub> ≤ K<sub>2</sub> ≤ ... ≤ K<sub>q-1</sub>''), iar ''P<sub>urm</sub>'' este un pointer către următorul nod frunză (ţinând cont de faptul că toate nodurile frunză se găsesc pe acelaşi nivel). Fiecare nod frunză are cel puţin ''[ (p/2) ]'' valori.\\ {{ :laboratoare:laborator00b:08Bplus_tree_leaf_node.png?nolink&500 }} |
| Line 734: | Line 736: | ||
| Se proiectează schema conceptuală a bazei de date în care se evidențiază structura fiecărei tabele, cu cheile primare și legăturile de tip cheie străină: | Se proiectează schema conceptuală a bazei de date în care se evidențiază structura fiecărei tabele, cu cheile primare și legăturile de tip cheie străină: | ||
| - | {{ :laboratoare:laborator01_schemaconceptuala_exemplu.jpg?nolink&600 }} | + | {{ :laboratoare:laborator00b:09_database_conceptual_scheme.png?nolink&600 }} |
| Pe baza acesteia se emit instrucțiunile pentru crearea bazei de date și a tabelelor componente, alegându-se tipuri de date corespunzătoare pentru atribute și impunându-se constrângerile de integritate necesare: | Pe baza acesteia se emit instrucțiunile pentru crearea bazei de date și a tabelelor componente, alegându-se tipuri de date corespunzătoare pentru atribute și impunându-se constrângerile de integritate necesare: | ||
| - | <file mysql Laborator01.sql> | + | <file mysql aipi2015-lab00b-DDL.sql> |
| + | CREATE DATABASE IF NOT EXISTS veterinary_clinic; | ||
| + | |||
| + | USE veterinary_clinic; | ||
| CREATE DATABASE IF NOT EXISTS veterinary_clinic; | CREATE DATABASE IF NOT EXISTS veterinary_clinic; | ||
| Line 744: | Line 750: | ||
| CREATE TABLE IF NOT EXISTS breed ( | CREATE TABLE IF NOT EXISTS breed ( | ||
| - | id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, | + | id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, |
| name VARCHAR(50) NOT NULL, | name VARCHAR(50) NOT NULL, | ||
| - | description VARCHAR(1000) | + | description VARCHAR(1000), |
| + | KEY (id) | ||
| ); | ); | ||
| + | ALTER TABLE breed ADD CONSTRAINT pk_breed_id PRIMARY KEY (id); | ||
| CREATE TABLE IF NOT EXISTS owner ( | CREATE TABLE IF NOT EXISTS owner ( | ||
| - | id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, | + | id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, |
| first_name VARCHAR(50) NOT NULL, | first_name VARCHAR(50) NOT NULL, | ||
| last_name VARCHAR(50) NOT NULL, | last_name VARCHAR(50) NOT NULL, | ||
| email VARCHAR(50), | email VARCHAR(50), | ||
| phone_number INT(10) NOT NULL, | phone_number INT(10) NOT NULL, | ||
| - | bank_account VARCHAR(50) NOT NULL | + | bank_account VARCHAR(50) NOT NULL, |
| + | KEY (id) | ||
| ); | ); | ||
| + | ALTER TABLE owner ADD CONSTRAINT pk_owner_id PRIMARY KEY (id); | ||
| ALTER TABLE owner ADD CONSTRAINT owner_email_format CHECK (email LIKE '%@%.%'); | ALTER TABLE owner ADD CONSTRAINT owner_email_format CHECK (email LIKE '%@%.%'); | ||
| CREATE TABLE IF NOT EXISTS animal ( | CREATE TABLE IF NOT EXISTS animal ( | ||
| - | id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, | + | id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, |
| name VARCHAR(50) NOT NULL, | name VARCHAR(50) NOT NULL, | ||
| breed_id INT(10) UNSIGNED, | breed_id INT(10) UNSIGNED, | ||
| - | gender CHAR(1) DEFAULT 'M', | + | gender CHAR(1) DEFAULT 'M' NOT NULL, |
| birth_date DATETIME NOT NULL, | birth_date DATETIME NOT NULL, | ||
| death_date DATETIME, | death_date DATETIME, | ||
| Line 770: | Line 779: | ||
| father_id INT(10) UNSIGNED, | father_id INT(10) UNSIGNED, | ||
| mother_id INT(10) UNSIGNED, | mother_id INT(10) UNSIGNED, | ||
| - | FOREIGN KEY(breed_id) REFERENCES breed(id) ON UPDATE CASCADE ON DELETE SET NULL, | + | KEY (id) |
| - | FOREIGN KEY(owner_id) REFERENCES owner(id) ON UPDATE CASCADE ON DELETE SET NULL | + | |
| ); | ); | ||
| - | + | ALTER TABLE animal ADD CONSTRAINT pk_animal_id PRIMARY KEY (id); | |
| - | ALTER TABLE animal ADD CONSTRAINT father_id_foreign_key FOREIGN KEY(father_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL; | + | ALTER TABLE animal ADD CONSTRAINT fk_animal_breed_id FOREIGN KEY(breed_id) REFERENCES breed(id) ON UPDATE CASCADE ON DELETE SET NULL; |
| - | ALTER TABLE animal ADD CONSTRAINT mother_id_foreign_key FOREIGN KEY(mother_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL; | + | ALTER TABLE animal ADD CONSTRAINT fk_animal_ownder_id FOREIGN KEY(owner_id) REFERENCES owner(id) ON UPDATE CASCADE ON DELETE SET NULL; |
| - | + | ALTER TABLE animal ADD CONSTRAINT fk_animal_father_id FOREIGN KEY(father_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL; | |
| - | ALTER TABLE animal ADD CONSTRAINT sex_possible_values CHECK (gender in ('M', 'F')); | + | ALTER TABLE animal ADD CONSTRAINT fk_animal_mother_id FOREIGN KEY(mother_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL; |
| + | ALTER TABLE animal ADD CONSTRAINT chk_animal_sex_possible_values CHECK (gender in ('M', 'F')); | ||
| CREATE TABLE IF NOT EXISTS doctor ( | CREATE TABLE IF NOT EXISTS doctor ( | ||
| - | id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, | + | id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, |
| first_name VARCHAR(50) NOT NULL, | first_name VARCHAR(50) NOT NULL, | ||
| last_name VARCHAR(50) NOT NULL, | last_name VARCHAR(50) NOT NULL, | ||
| Line 787: | Line 796: | ||
| code VARCHAR(20) NOT NULL, | code VARCHAR(20) NOT NULL, | ||
| email VARCHAR(50), | email VARCHAR(50), | ||
| - | phone_number INT(10) NOT NULL | + | phone_number INT(10) NOT NULL, |
| + | KEY (id) | ||
| ); | ); | ||
| - | + | ALTER TABLE doctor ADD CONSTRAINT pk_doctor_id PRIMARY KEY (id); | |
| - | ALTER TABLE doctor ADD CONSTRAINT doctor_email_format CHECK (email LIKE '%@%.%'); | + | ALTER TABLE doctor ADD CONSTRAINT chk_doctor_email_format CHECK (email LIKE '%@%.%'); |
| CREATE TABLE IF NOT EXISTS diagnosis ( | CREATE TABLE IF NOT EXISTS diagnosis ( | ||
| - | id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, | + | id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, |
| name VARCHAR(50) NOT NULL, | name VARCHAR(50) NOT NULL, | ||
| description VARCHAR(1000) NOT NULL, | description VARCHAR(1000) NOT NULL, | ||
| severity VARCHAR(20) NOT NULL, | severity VARCHAR(20) NOT NULL, | ||
| - | cure VARCHAR(1000) NOT NULL | + | cure VARCHAR(1000) NOT NULL, |
| + | KEY (id) | ||
| ); | ); | ||
| + | ALTER TABLE diagnosis ADD CONSTRAINT pk_diagnosis_id PRIMARY KEY (id); | ||
| CREATE TABLE IF NOT EXISTS medical_record ( | CREATE TABLE IF NOT EXISTS medical_record ( | ||
| - | id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, | + | id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, |
| animal_id INT(10) UNSIGNED, | animal_id INT(10) UNSIGNED, | ||
| doctor_id INT(10) UNSIGNED, | doctor_id INT(10) UNSIGNED, | ||
| Line 807: | Line 819: | ||
| date DATETIME NOT NULL, | date DATETIME NOT NULL, | ||
| treatment VARCHAR(1000), | treatment VARCHAR(1000), | ||
| - | FOREIGN KEY(animal_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL, | + | KEY (id) |
| - | FOREIGN KEY(doctor_id) REFERENCES doctor(id) ON UPDATE CASCADE ON DELETE SET NULL, | + | |
| - | FOREIGN KEY(diagnosis_id) REFERENCES diagnosis(id) ON UPDATE CASCADE ON DELETE SET NULL | + | |
| ); | ); | ||
| + | ALTER TABLE medical_record ADD CONSTRAINT pk_medical_record_id PRIMARY KEY (id); | ||
| + | ALTER TABLE medical_record ADD CONSTRAINT fk_medical_record_animal_id FOREIGN KEY(animal_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL; | ||
| + | ALTER TABLE medical_record ADD CONSTRAINT fk_medical_record_doctor_id FOREIGN KEY(doctor_id) REFERENCES doctor(id) ON UPDATE CASCADE ON DELETE SET NULL; | ||
| + | ALTER TABLE medical_record ADD CONSTRAINT fk_medicak_record_diagnosis_id FOREIGN KEY(diagnosis_id) REFERENCES diagnosis(id) ON UPDATE CASCADE ON DELETE SET NULL; | ||
| </file> | </file> | ||
| Line 1866: | Line 1880: | ||
| O tranzacție este marcată prin intermediul instrucțiunii ''START TRANSACTION'' sau ''BEGIN [WORK]''. Instrucțiunile din cadrul acesteia vor fi executate împreună în momentul în care se apelează instrucțiunea ''COMMIT'' sau se va reveni la starea anterioară acesteia dacă se apelează ''ROLLBACK''. | O tranzacție este marcată prin intermediul instrucțiunii ''START TRANSACTION'' sau ''BEGIN [WORK]''. Instrucțiunile din cadrul acesteia vor fi executate împreună în momentul în care se apelează instrucțiunea ''COMMIT'' sau se va reveni la starea anterioară acesteia dacă se apelează ''ROLLBACK''. | ||
| - | {{ :laboratoare:laborator01_tranzactii.jpg?nolink&300 }} | + | {{ :laboratoare:laborator00b:10_transaction.png?nolink&300 }} |
| În cadrul unei tranzacții modul autocommit (marcarea modificărilor pe discul local imediat ce este emisă o anumită instrucțiune) - activat în mod implicit - este dezactivat pe toată perioada acesteia. Modificarea acestui comportament se poate controla prin intermediul variabilei de sesiune ''autocommit''. | În cadrul unei tranzacții modul autocommit (marcarea modificărilor pe discul local imediat ce este emisă o anumită instrucțiune) - activat în mod implicit - este dezactivat pe toată perioada acesteia. Modificarea acestui comportament se poate controla prin intermediul variabilei de sesiune ''autocommit''. | ||
| Line 1924: | Line 1938: | ||
| ==== Comentarii ==== | ==== Comentarii ==== | ||
| - | Comentariile se pot întinde fie pe o linie (fiind precedate de ''#'' sau ''-- '' până la sfârşitul liniei în cauză), fie pe mai multe linii (fiind încadrate între ''/*'' şi ''*/''), această variantă putând fi folosită şi pentru comentariile de un singur rând care nu se întind însă până la sfârşitul acesteia. | + | Comentariile se pot întinde fie pe o linie (fiind precedate de ''#'' sau ''-- '' până la sfârşitul liniei în cauză), fie pe mai multe linii (fiind încadrate între ''%%/*%%'' şi ''%%*/%%''), această variantă putând fi folosită şi pentru comentariile de un singur rând care nu se întind însă până la sfârşitul acesteia. |
| ==== Script-uri specifice ==== | ==== Script-uri specifice ==== | ||
| Line 1943: | Line 1957: | ||
| Se doreşte proiectarea unei baze de date ce va fi folosită în cadrul unui sistem ERP pentru o librărie care comercializează doar cărţi. | Se doreşte proiectarea unei baze de date ce va fi folosită în cadrul unui sistem ERP pentru o librărie care comercializează doar cărţi. | ||
| - | O carte este identificată prin numele autorului sau a autorilor săi, titlul volumului, editura unde a fost publicată, anul apariţiei, ediţia, colecţia din care face parte, domeniul în care poate fi încadrată. Totodată, pentru fiecare carte se reţine o descriere şi informaţii cu privire la stocul existent şi la preţ. Este obligatoriu ca informaţiile ediţie, stoc existent şi preţ să fie numere pozitive. | + | O carte poate avea unul, nici unul sau mai mulți autori, un titlu, un subtitlu și o descriere, fiind caracterizată printr-un număr de ediție, anul în care a fost tipărită și colecția din care face parte. Cartea poate fi disponibilă în mai multe formate (tipărit, electronic). Pentru fiecare format se reține un isbn, limba în care este scrisă cartea, stocul existent precum și prețul. De asemenea, unei cărți i se pot specifica una, nici una sau mai multe categorii. O categorie este definită printr-un nume și o descriere. |
| - | Pentru fiecare autor se va reţine şi o scurtă biografie. | + | Este obligatoriu ca informațiile număr de ediție, stoc existent și preț să fie numere pozitive. |
| + | |||
| + | De asemenea, pentru fiecare autor se va reţine şi o scurtă biografie. | ||
| + | |||
| + | O colecție aparține unei edituri, pentru aceasta reținându-se un nume și o descriere. Pentru fiecare editură sunt stocate denumirea, codul unic de identificare, descrierea, adresa poștală, codul zip, țara și pagina Internet de unde pot fi obținute informații suplimentare. | ||
| Librăria emite comenzi de aprovizionare către edituri şi facturi la clienţi. Atât comanda de aprovizionare cât şi factura vor fi identificate printr-un cod (unic) – spre exemplu serie şi număr –, data la care a fost emisă, starea precum şi entitatea căreia i se adresează (editură / client). Ele pot conţine mai multe produse, pentru fiecare reţinându-se cantitatea. | Librăria emite comenzi de aprovizionare către edituri şi facturi la clienţi. Atât comanda de aprovizionare cât şi factura vor fi identificate printr-un cod (unic) – spre exemplu serie şi număr –, data la care a fost emisă, starea precum şi entitatea căreia i se adresează (editură / client). Ele pot conţine mai multe produse, pentru fiecare reţinându-se cantitatea. | ||
| - | O editură este caracterizată prin denumire, cod unic de identificare, descriere, localitatea, regiunea şi ţara în care operează. | + | Un client este identificat prin cod numeric personal, nume, prenume, adresa, telefon, email. Presupunând că librăria dispune şi de o platformă on-line pentru achiziţii, pentru fiecare client se vor reţine tipul (administrator / client) și credențialele sub forma unui nume de utilizator și a unei parole. Cu excepţia numărului de telefon, nici un câmp nu poate fi vid. Pentru email trebuie să se respecte formatul ''username@domain.country''. |
| - | Un client este identificat prin CNP, nume, prenume, adresa, telefon, email. Presupunând că librăria dispune şi de o platformă on-line pentru achiziţii, pentru fiecare client se vor reţine tipul (administrator / client), precum şi rolul (super-administrator, administrator simplu / client inregistrat, client verificat). Cu excepţia numărului de telefon, nici un câmp nu poate fi vid. Pentru email trebuie să se respecte formatul ''username@domain.country''. | + | O schemă conceptuală a bazei de date descrisă mai sus din punct de vedere funcțional poate fi următoarea: |
| - | - Să se proiecteze schema conceptuală a bazei de date.\\ Studenţii care nu participă la ora de laborator şi trimit rezolvările prin email, vor ataşa schema conceptuală a bazei de date ca fişier .pdf. | + | {{ :laboratoare:laborator00b:aipi2015-lab00b-conceptualscheme.png?nolink&800 }} |
| - | - Să se implementeze baza de date având schema conceptuală definită folosind sistemul de gestiune pentru baze de date MySQL.\\ Să se populeze corespunzător baza de date.\\ Se vor specifica, pentru fiecare atribut în parte, şi constrângerile indicate folosind clauza ''CHECK'', chiar dacă MySQL nu le implementează încă. | + | |
| - | - Să se afişeze conţinutul tabelei care conține cărțile comercializate în librărie. Pentru fiecare carte se va afişa identificatorul, titlul şi autorii.\\ Întrucât o carte poate avea mai mulţi autori, se va folosi funcţia de grup ''GROUP_CONCAT'', a cărei sintaxă este: <code> GROUP_CONCAT([DISTINCT] expr [,expr ...] | + | **1.** Să se cloneze în directorul de pe discul local conținutul depozitului la distanță de la [[https://www.github.com/aipi2015/Laborator00|]]. În urma acestei operații, directorul ''Laborator00'' va trebui să conțină subdirectoarele ''samples'' și ''labtasks'', fișierele ''README.md'' și ''LICENSE''. <code> |
| + | student@aipi2015:~$ git clone https://www.github.com/aipi2015/Laborator00 | ||
| + | </code> | ||
| + | |||
| + | **2.** Să se ruleze, în ordine, script-urile: | ||
| + | * ''aipi2015-lab00b-DDL.sql'' care conține instrucțiunile necesare pentru definirea bazei de date ''bookstore''; | ||
| + | * ''aipi2015-lab00b-DML.sql'' care conține instrucțiunile necesare pentru popularea tabelelor din baza de date ''bookstore''. | ||
| + | Se poate folosi utilitarul MySQL Workbench sau fișierele se pot încărca cu ajutorul unui client MySQL, fie prin redirectarea intrării, fie prin folosirea comenzilor ''source'' sau ''\.''. | ||
| + | |||
| + | <note important>Operația de populare a bazei de date poate dura o perioadă de timp semnificativă datorită numărului mare de înregistrări.</note> | ||
| + | |||
| + | **3.** Să se afişeze conţinutul tabelei care reține cărțile comercializate în librărie. Pentru fiecare carte se va afişa identificatorul, titlul şi autorii, denumirea colecției, denumirea editurii, numărul de ediție și anul tipăririi precum și denumirea țării. Întrucât o carte poate avea mai mulţi autori, se va folosi funcţia de grup ''GROUP_CONCAT'', a cărei sintaxă este: <code> GROUP_CONCAT([DISTINCT] expr [,expr ...] | ||
| [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] | [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] | ||
| [SEPARATOR str_val]) | [SEPARATOR str_val]) | ||
| </code> | </code> | ||
| - | - Să se majoreze cu 20% preţul cărţilor apărute înainte de anul 2010. Ulterior se vor lista toate volumele existente în librărie grupate pe ani de apariţie (în ordine cronologică), pentru fiecare an în parte ordonate descrescător în funcţie de preţ. | + | |
| - | - Să se afişeze toate cărţile unui autor, fie că le-a redactat singur, fie că acestea au mai mulţi autori.\\ Să se identifice numărul de volume elaborate, precizându-se numărul celor la care e unic autor şi al celor pe care le-a realizat colaborând cu alţi autori. | + | **4.** Să se majoreze cu 15% preţul cărţilor care sunt disponibile în mai mult de trei formate diferite. Să se afișeze cărțile pentru care s-au majorat prețurile, grupate pe formate. Informațiile care vor fi vizualizate sunt identificatorul cărții, titlul și subtitlul, denumirea formatului, prețul actualizat. |
| - | - Să se creeze două rutine stocate pe server (funcții) care determină valoarea unei comenzi de aprovizionare, respectiv a unei facturi.\\ Folosind aceste rutine stocate, să se identifice primele trei edituri, respectiv primii trei clienţi în funcţie de volumul tranzacţiilor din librărie. | + | |
| - | - Să se creeze o procedură stocată care calculează suma tuturor facturilor pentru un client, afişând lista facturilor şi valoarea lor, ordonată cronologic. | + | **5.** Să se afişeze toate cărţile unui autor, fie că le-a redactat singur, fie că acestea au mai mulţi autori.\\ Să se afișeze, în cadrul aceleiași interogări, numărul de volume elaborate, precizându-se numărul celor la care e unic autor şi al celor pe care le-a realizat colaborând cu alţi autori.\\ Vor fi incluși doar autorii care au elaborat mai mult de 4 titluri din care mai mult de 2 sunt individuale. |
| - | - Să se identifice lista celor mai profitabile cărţi, ordonate în funcţie de suma obţinută din vânzări. | + | |
| - | - Să se implementeze constrângerile cu privire la atributele tabelelor folosind triggere. | + | **6.** Să se șteargă din baza de date acei scriitori pentru care nu se găsește nici o carte în baza de date. |
| - | - Să se parcurgă lista de utilizatori folosind un cursor, stabilindu-se vârsta medie a bărbaţilor şi vârsta medie a femeilor. | + | |
| + | **7.** Să se creeze o rutină stocată pe server (funcție) care determină valoarea unei facturi.\\ Folosind această rutină stocată, să se identifice primii trei clienţi în funcţie de volumul tranzacţiilor din librărie. | ||
| + | |||
| + | **8.** Să se creeze o rutină stocată pe server (procedură) care calculează suma tuturor facturilor pentru un client, aceasta fiind întoarsă sub forma unui parametru. | ||
| + | |||
| + | **9.** Folosind doar rutina stocată și având acces doar la tabela de utilizatori, să se determine utilizatorul pentru care suma facturilor este cea mai mare. | ||
| + | |||
| + | <spoiler|Indicații de Rezolvare> | ||
| + | Este necesar să se folosească un cursor. | ||
| + | </spoiler> | ||
| + | |||
| + | **10.** Să se implementeze un trigger la actualizarea tabelei ce conține diferitele formate în care sunt dispuse cărțile, astfel încât atunci când este modificat prețul, dacă acesta depășește media prețurilor celorlalte cărți având același format, să se modifice prețul la valoarea mediei, afișându-se și un mesaj corespunzător. | ||
| ===== Resurse ====== | ===== Resurse ====== | ||
| Line 1979: | Line 2019: | ||
| ===== Soluții ===== | ===== Soluții ===== | ||
| - | TODO | + | [[https://github.com/aipi2015/Laborator00/blob/master/solutions/aipi2015-lab00b-solutions.sql|Rezolvări]] |
