{"id":2029,"date":"2020-11-28T01:18:33","date_gmt":"2020-11-27T22:18:33","guid":{"rendered":"http:\/\/cadredidactice.ub.ro\/simonavarlan\/?page_id=2029"},"modified":"2022-01-31T16:45:49","modified_gmt":"2022-01-31T13:45:49","slug":"bd-2021","status":"publish","type":"page","link":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/bd-2021\/","title":{"rendered":"BD 2021-2022"},"content":{"rendered":"<h2><strong><span style=\"color: #ff0000\">Pregatirea mediului de lucru<\/span><\/strong><\/h2>\n<p><span style=\"color: #0000ff\">I) Prima varinta de lucru\u00a0 (<\/span><span style=\"color: #ff6600\"><strong>DOAR DACA NU <\/strong>PUTETI\u00a0 SA VA INSTALATI PE CALCULATOR SERVERUL ORACLE 11g<\/span><span style=\"color: #0000ff\">)<\/span><\/p>\n<p>Exista posibilitatea de a lucra online, direct intr-un cont oracle pe platforma\u00a0https:\/\/livesql.oracle.com<\/p>\n<p>Va faceti cont, va logati, dupa care\u00a0mergeti la <strong>Upload Script<\/strong> (dreapta sus), pentru a incarca scriptul sql aferent schemei bazei de date cu care veti lucra la orele de laborator.<\/p>\n<p>Scriptul pentru crearea bazei de date este pe TEAMS MICROSOFT, in sectiunea FILES.<\/p>\n<p>Dupa ce ati facut UPLOAD,\u00a0 alegeti scriptul si dati RUN SCRIPT.<\/p>\n<p>La final veti lucra utilizand optiunea SQL WORKSHEET (meniu dreapta).<\/p>\n<h3><span style=\"color: #0000ff\"><strong>II) A doua varianta de lucru<\/strong>\u00a0 (<\/span><span style=\"color: #ff0000\">CEA RECOMANDATA<\/span><span style=\"color: #0000ff\">)<\/span><\/h3>\n<p><span style=\"color: #000000\">Mediul de lucru\u00a0<\/span>pentru testarea instructiunilor SQL este Oracle Database 11g Express Edition care se poate <span style=\"color: #800080\"><strong>downloada de pe Teams, sectiunea Files<\/strong> <\/span>(disponibil doar pentru studentii grupei FR anul I) si instalat pe calculatorul personal.<\/p>\n<ul>\n<li>In timpul instalarii veti fi intrebat o parola. Aceasta este parola de administrare ce va fi utila daca vreti sa administrati serverul de la distanta. Local (pe calculatorul personal) aceasta parola va fi ignorata de catre SQL*Plus.<\/li>\n<li><span style=\"color: #ff0000\">Dupa instalare<span style=\"color: #000000\">\u00a0accesati utilitarul Run SQL Command Line unde va veti conecta la serverul de SQL cu contul de administrator astfel:\u00a0\u00a0<\/span><\/span><\/li>\n<\/ul>\n<p><span style=\"color: #ff0000\"><span style=\"color: #000000\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 sql &gt;\u00a0 CONNECT\u00a0 SYS AS SYSDBA <\/span><\/span><\/p>\n<p><span style=\"color: #ff0000\"><span style=\"color: #000000\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0(cu parola)\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0sql&gt;\u00a0 sql<\/span><\/span><\/p>\n<ul>\n<li>Dupa ce v-ati conectat cu contul de administrator este <span style=\"color: #800080\"><strong>indicat sa va faceti un cont separat<\/strong> <\/span>in care sa creati schema bazei de date. Scrieti urmatoarele comenzi exact in ordinea data:<\/li>\n<\/ul>\n<p>sql&gt;\u00a0 CREATE USER STUDENT IDENTIFIED BY STUDENT DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;<\/p>\n<p>(crearea unui user cu numele STUDENT si parola STUDENT)<\/p>\n<p>sql&gt; ALTER USER STUDENT QUOTA 100M ON USERS;<\/p>\n<p>sql&gt; GRANT CONNECT TO STUDENT;<\/p>\n<p>sql&gt; GRANT CREATE TABLE TO STUDENT;<\/p>\n<p>sql&gt; GRANT CREATE VIEW TO STUDENT;<\/p>\n<p>sql&gt; GRANT CREATE SEQUENCE TO STUDENT;<\/p>\n<p>sql&gt; GRANT CREATE TRIGGER TO STUDENT;<\/p>\n<p>sql&gt; GRANT CREATE SYNONYM TO STUDENT;<\/p>\n<p>sql&gt; GRANT CREATE PROCEDURE TO STUDENT;<\/p>\n<ul>\n<li>La urma va conectati cu contul Student:<\/li>\n<\/ul>\n<p>sql&gt;\u00a0 CONN STUDENT\u00a0\u00a0cu parola STUDENT<\/p>\n<p>O lista completa cu comenzile SQL *PLUS gasiti aici:\u00a0<a href=\"https:\/\/docs.oracle.com\/database\/121\/SQPUG\/toc.htm\">https:\/\/docs.oracle.com\/database\/121\/SQPUG\/toc.htm<\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"text-decoration: underline\"><span style=\"color: #ff0000;text-decoration: underline\"><b>Pentru lucrul la orele de laborator<\/b>:<\/span><\/span><\/h3>\n<p>Pentru a rezolva exercitiile SQL veti lucra pe baza de date <strong>Employees<\/strong>.<\/p>\n<p><strong><span style=\"color: #800080\">SCRIPTUL PENTRU BAZA DE DATE EMPLOYEES il gasiti pe contul de TEAMS MICROSOFT in tabul FILES.<\/span><\/strong><\/p>\n<ul>\n<li>Daca folositi Oracle Database 11g Express Edition in contul STUDENT creat veti rula scriptul astfel:<\/li>\n<\/ul>\n<p><span style=\"color: #339966\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0sql&gt; @ &lt; calea unde se afla fisierul &gt;\\&lt;nume fisier&gt;.sql\u00a0 \u00a0 (comanda @ urmata de adresa locala unde ati salvat fisierul sql)<\/span><\/p>\n<ul>\n<li>Pentru a verifica daca tabelele au fost create scrieti urmatoarea instructiune select:<\/li>\n<\/ul>\n<p><span style=\"color: #339966\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0sql&gt;\u00a0 SELECT table_name FROM user_tables;<\/span><\/p>\n<ul>\n<li>Pentru a verifica structura fiecarui tabel scrieti instructiuni de tipul:<\/li>\n<\/ul>\n<p><span style=\"color: #339966\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0sql&gt;\u00a0 DESCRIBE &lt;nume tabel&gt;;\u00a0 \u00a0 \u00a0 <\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 De exemplu: DESCRIBE employees;<\/p>\n<ul>\n<li>Pentru a vedea inregistrarile dintr-un tabel scrieti:<\/li>\n<\/ul>\n<p><span style=\"color: #339966\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0sql&gt;\u00a0 SELECT * from &lt;nume tabel&gt;;<\/span> \u00a0 \u00a0 \u00a0 \u00a0De exemplu: SELECT * from employees;<\/p>\n<p>&nbsp;<\/p>\n<p>Schema bazei de date este: <a href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2017\/11\/SQL_Schema_ERD_and_Table_Designs.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">SQL_Schema_ERD_and_Table_Designs<\/a><\/p>\n<p>Baza de date cu care vom lucra la laborator contine urmatoarele tabele:<\/p>\n<p><strong>Employees<\/strong> (employee_id, job _id, department_id, manager_id \u2026)<\/p>\n<p><strong>Departments<\/strong> (department_id, location_id, \u2026)<br \/>\n<strong>Locations<\/strong> (location_id, country_id,\u2026)<br \/>\n<strong>Countries<\/strong> (country_id, region_id, \u2026)<br \/>\n<strong>Regions<\/strong> (region_id, \u2026)<\/p>\n<p><strong>Jobs<\/strong> (job_id, \u2026)<br \/>\n<strong>Job_history<\/strong> (employee_id, start_date, job_id, department_id, \u2026)<br \/>\n<strong>Job_grades<\/strong> (\u2026)<\/p>\n<p>Identificati concret care este structura fiecarui tabel folosind instructiunea DESCRIBE si de asemenea legaturile dintre tabele prin identificarea FK-urilor.<\/p>\n<p><span style=\"text-decoration: underline\"><strong><span style=\"color: #ff0000;text-decoration: underline\">Teme laborator 1:<\/span><\/strong><\/span><\/p>\n<p>(\u00a0 \u00a0Optional:\u00a0 un mic rezumat despre SQL: <a href=\"..\/files\/2011\/12\/interogarea-bazelor-de-date-sql.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">Interogarea bazelor de date SQL<\/a> &#8211; de citit )<\/p>\n<p>1) C<a href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2012\/04\/crearea-si-definirea-de-tabele.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">rearea-si-definirea-de-tabele<\/a>\u00a0&#8211; de citit<\/p>\n<p>2)\u00a0<a href=\"..\/files\/2011\/11\/introducere_in_sql.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">Introducere in SQL\u00a0<\/a>&#8211; de rezolvat exercitiile de la sfarsit<\/p>\n<p><a href=\"..\/files\/2011\/11\/introducere_in_sql.pdf\"><br \/>\n<\/a><span style=\"text-decoration: underline\"><strong><span style=\"color: #ff0000;text-decoration: underline\">Teme laborator 2:<\/span><\/strong><\/span><\/p>\n<p>3)\u00a0<a href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2017\/11\/FUNCTII-BUN.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">Functii<\/a><\/p>\n<p>4)\u00a0<a href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2012\/04\/joinuri.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">Joinuri<\/a><\/p>\n<p><a href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2022\/01\/Solutii-Exercitii-Join.pdf\">Solutii Exercitii Join<\/a><\/p>\n<p>5)\u00a0<a href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2012\/04\/subselect.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">Subselect<\/a><\/p>\n<p><a href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2022\/01\/Solutii-exercitii-Subcereri.pdf\">Solutii exercitii Subcereri<\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline\"><strong><span style=\"color: #ff0000;text-decoration: underline\">Tema de laborator 3:<\/span><\/strong><\/span><\/p>\n<p>6)\u00a0<a style=\"font-size: 13px\" href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2012\/01\/securitatea-bazelor-de-date.pdf\" target=\"_blank\" rel=\"noopener noreferrer\">Securitatea-bazelor-de-date<\/a>\u00a0(<span style=\"color: #ff0000\">de citit<\/span>)<\/p>\n<p>7)\u00a0<a style=\"font-size: 13px\" href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2012\/12\/tranzactii.doc\" target=\"_blank\" rel=\"noopener noreferrer\">Tranzactii<\/a>\u00a0 (<span style=\"color: #ff0000\">de citit<\/span>)<\/p>\n<p><span style=\"color: #ff00ff\">\u00a0<\/span><\/p>\n<p>Suport de curs: <a href=\"https:\/\/cadredidactice.ub.ro\/wp-content\/uploads\/sites\/284\/2020\/01\/BD_Bacau_2019.docx\">BD_Bacau_2019<\/a><\/p>\n<p>Alte referinte:\u00a0<a href=\"https:\/\/theswissbay.ch\/pdf\/Gentoomen%20Library\/Databases\/Molina%2CUllman%20-%20Database%20Systems%20The%20Complete%20Book.pdf\">https:\/\/theswissbay.ch\/pdf\/Gentoomen%20Library\/Databases\/Molina%2CUllman%20-%20Database%20Systems%20The%20Complete%20Book.pdf<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Pregatirea mediului de lucru I) Prima varinta de lucru\u00a0 (DOAR DACA NU PUTETI\u00a0 SA VA INSTALATI PE CALCULATOR SERVERUL ORACLE 11g) Exista posibilitatea de a lucra online, direct intr-un cont oracle pe platforma\u00a0https:\/\/livesql.oracle.com Va faceti cont, va logati, dupa care\u00a0mergeti &hellip;<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/cadredidactice.ub.ro\/simonavarlan\/bd-2021\/\">Continue reading<\/a><\/p>\n","protected":false},"author":269,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-2029","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/wp-json\/wp\/v2\/pages\/2029","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/wp-json\/wp\/v2\/users\/269"}],"replies":[{"embeddable":true,"href":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/wp-json\/wp\/v2\/comments?post=2029"}],"version-history":[{"count":53,"href":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/wp-json\/wp\/v2\/pages\/2029\/revisions"}],"predecessor-version":[{"id":2391,"href":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/wp-json\/wp\/v2\/pages\/2029\/revisions\/2391"}],"wp:attachment":[{"href":"https:\/\/cadredidactice.ub.ro\/simonavarlan\/wp-json\/wp\/v2\/media?parent=2029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}