Preparació de la PS

Per preparar aquesta PS el que vaig fer va ser, per una banda, repassar els conceptes teòrics amb una mica de codi html i php, i de l’altra, repetir moltes vegades exemples de creació de models E-R i relacionals a partir d’un enunciat amb la seva posterior normalització.

Model E-R

– A partir d’un diagrama E-R definir les relacions, posar-lis cardinalitat i respecte dels atributs, indicar quins són els identificadors, i què signifiquen els que estàn marcats amb cercle doble i cercle discontinu.

Cercle doble: multivaluats

Cercle discontinu: derivats

Alerta amb cardinalitats 1:N i M:N

Model Relacional

– A partir d’un diagrama E-R passar-lo a model relacional.

Tenir en compte els atributs derivats i les relacions 1:N.

Possible relació recursiva 1:N? afegir una clau externa que faci referència a la pròpia taula.


 

Teoría

– Explica diferència entre una relació binària i una ternària. Posa exemples.

En els tipus de relacions binàries queden associats dos tipus d’entitats mentre que el les ternàries n’hi queden associats tres.

Exemple binària:

TREBALLADOR — treballar —- EMPRESA

Exemple ternària

ESTUDIANT — estudiar — ASSIGNATURA

                                   |

                              AULA

– Diferències clau primària i clau alternativa. Poden ser els dos tipus de claus utilitzats com clau forana? Indica si les claus foranes poden tenir valors NULL i posa un exemple que validi la teva resposta

Una clau primària és aquella clau candidata escollida pel dissenyador com element principal per identificar les entitats dins el conjunt d’entitats

Una clau forana és aquella que existint com a clau depenent en una taula, és alhora clau primària en una altra.

Una clau alternativa és aquella que no ha estat seleccionada com a clau primària, però que també pot identificar de forma única una fila dins una altra taula.

Sí. Tant una clau primària com una clau alternativa poden ser utilitzats com a clau forana. La condició és que han de ser claus que defineixin les entitats de forma única

Sí. Una clau forana pot tenir un valor NULL (si no disposa de restricció NOT NULL) i això permet crear les files a la base de dades encara que el valor de la clau forana ens sigui desconegut. Per exemple un ticket de suport enviat a un servei de suport amb un camp que designa l’operador que ha efectuat el servei.

– Explica què és un atribut derivat i com definir-lo en una base de dades. Posa un parell

d’exemples.

Un atribut derivat és un atribut el valor del qual es calcula a partir dels valors d’altres atributs. Es diferencia d’altres atributs en què, en la transformació del model entitat-relació (E-R) a model relacional, no es representa amb una columna en la taula generada. El seu valor es calcularà en el moment que sigui necessari, a partir d’altres dades i atributs de la mateixa taula, o altres taules amb les quals té relació.

-Explica els conceptes de Trigger i Procedure. Proposa un parell d’exemples on es pugui veure la utilitat de cadascun d’ells.

NOTA: No cal que implementis els exemples, tan sols cal que descriguis a on és podrien aplicar, què farien i el per què de la seva utilitat.

Un Procedure o procediment emmagatzemat és una aplicació escrita en un llenguatge basat en SQL que pot ser executat per un SGBD. La utilitat d’aquestes aplicacions rau en què poden realitzar diverses operacions de forma consecutiva (lectura, inserció o actualització) minimitzant així el nombre de missatges intercanviats entre l’aplicació que crida el procediment emmagatzemat i el SGBD.

Un Trigger o disparador és una aplicació que executa de forma automàtica una sèrie d’accions com a conseqüència d’alguna operació d’actualització sobre una taula determinada. Els triggers es poden asociar els esdeveniments d’INSERT, UPDATE o DELETE en un atribut o taula.

La diferència principal entre un procediment emmagatzemat i un disparador és que els procediments emmagatzemats s’han de cridar explícitament i un disparador s’executa automàticament en un SGBD en realitzar-se una acció.

– Menciona tres operadors del llenguatge relacional i explica en què consisteix cadascun d’ells. A continuació, proposa un petit conjunt de dades i posa un exemple de cadascun d’aquests operadors sobre el conjunt de dades.

  1. a) unió, agrega els registres de la primera taula a la segona
  2. b) diferència, proporciona els registres que apareixen en la primera però no en la segona taula
  3. c) intersecció, proporciona els registres comuns a les dues taules
  4. d) producte, concatenació de cada registre de la primera taula amb cada registre de la segona
  5. e) projecció, selecciona atributs específics d’una taula (SELECT)
  6. f) restricció, selecciona registres que cumpleixen una condició (WHERE)

– Pot una relació 1:N contenir atributs?

No, ja que no genera cap taula. No genera cap taula perquè si necessitéssim atributs tot anirien a la part n.

PHP

– Diferències GET I POST.

L’atribut method indica com s’han d’enviar les dades del formulari al servidor.

Amb el mètode GET:

  1. els paràmetres es passen com part de l’url que crida l’aplicació per processar el formulari
  2. agrega les dades a l’url assignat a l’atribut action, separant els paràmetres de la url amb signe interrogació ‘?’ i els paràmetres entre ells amb ‘&’.
  3. limitació de mida dades
  4. les dades són visibles
  5. no permet tramesa arxius adjunts
  6. les dades enviades es troben disponibles a la variable superglobal $_GET que és un array de claus clau/valor on clau és el ‘name’ dels camps del formulari i valor el que ha introduït l’usuari

Amb el mètode POST:

  1. envia les dades també com afegit a l’url però de forma invisible a l’usuari fent servir el mètode HTTP POST
  2. permet enviar formularis amb dades extenses
  3. les dades enviades es troben disponibles a la variable superglobal $_POST

– Exemple codi insertar valor a registre amb dades de GET

<html>


<form method=‘get’ action=‘agenda.php’>


<p>Nombre: <input name=‘nom’ type=‘text’ id=‘nom_id’></p>

<p>Edad: <input name=‘edat’ type=‘text’ id=‘edat_id’ size=‘5’></p>

<p><input type=‘submit’ name=‘Submit’ value=‘Enviar’></p>

</form>

</html>

– Exemple codi insertar valor a registre amb dades de POST

<html>


<form method=‘post’ action=‘agenda.php’>


<p>Nombre: <input name=‘nom’ type=‘text’ id=‘nom’></p>

<p>Edad: <input name=‘edat’ type=‘text’ id=‘edat’ size=‘5’></p>

<p><input type=‘submit’ name=‘Submit’ value=‘Enviar’></p>

</form>

</html>

– Exemple codi recuperar valor camp formulari edat i assignar a variable ‘edat’

<?php


$nom = ‘‘;

if (isset($_GET[‘edat’] )){

$nom = $_GET[‘edat’];

}

 ?>

<?php


$nom = ‘‘;

if (isset($_POST[‘edat’] )){

$nom = $_POST[‘edat’];

}

 ?>


 

– El següent codi HTML presenta un formulari per a obtenir la quantitat de persones a una localitat amb el mateix nom i edat. Per exemple, (Joan Rigo, 23 anys, Barcelona).

<html>


<head>


<title>Formulari d’exemple</title>

</head>


<body>

<form method=‘get’ action=‘agenda.php’>


<p>Nombre: <input name=‘nom’ type=‘text’ id=‘nom’></p>

<p>Edad: <input name=‘edat’ type=‘text’ id=‘edat’ size=‘5’></p>

<p>Localidad: <input name=‘localitat’ type=‘text’ id=‘localitat’></p>

<p><input type=‘submit’ name=‘Submit’ value=‘Enviar’></p>

</form>

</body>

</html>

Respon a les següents preguntes:

Quin nom ha de tenir script que tracti les dades enviades pel formulari?

agenda.php

Com es podrà obtenir el valor de la variable ‘nom’ des de l’script que tracti les dades enviades pel formulari?

<?php


$nom = ‘‘;

if (isset($_GET[‘nom’] )){

$nom = $_GET[‘nom’];

}

 ?>

Com serà la url resultant de l’enviament del formulari?

http://www.exemple.com/agenda.php?nom=Joan+Rigo&edat=23&localitat=Barcelona

– Ompliu els camps del formulari marcats amb XXX per tal que la URL resultant de l’enviament del formulari sigui la següent:

http://www.exemple.com/formulari.php?nom=Salvador&cognoms=Espriu+i+Castello

<form XXX=‘get’ action=‘XXX’>


<input type=‘XXX’ name=‘nom’ value=‘XXX’>

<input type=‘text’ name=‘cognoms’ value=‘XXX’>

<input type=‘XXX’ value=‘Enviar Formulari’>

</form>

<form method=‘get’ action=‘formulari.php’>

<input type=‘text’ name=‘nom’ value=‘Salvador’>

<input type=‘text’ name=‘cognoms’ value=‘Espriu i Castello’>

<input type=‘submit’ value=‘Enviar Formulari’>

</form>

És necessari afegir algun altre camp al formulari per tal d’obtenir la URL?
Nota: es considera que el contingut dels camps no es modificarà i s’enviaran els valors indicats al formulari

No. El formulari ja conté tots els camps indicats.


 

– A partir de la definició de la taula següent :

CREATE TABLE Contactes(


ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Name VARCHAR (128),


Surname VARCHAR (128),


Age INT

);

i la següent url de crida a una funció:

 

http://www.example.com/contacte.php?nom=Salvador&cognom=Espriu&edat=72

Escriviu el codi PHP necessari per a recuperar els paràmetres de la crida i inserir el nou contacte a la taula Contactes.

Nota: No fa falta crear el codi de connexió a la Base de dades, s’assumirà que la connexió a la base de dades s’ha fet correctament i es troba emmagatzemada a la variable $con.

 

<?php


$nom = ‘‘; $cognom = ‘‘; $edat = ‘‘;

if (isset($_GET[‘nom’] )){

$nom = $_GET[‘nom’];

}

if (isset($_GET[‘cognom’] )){

$cognom = $_GET[‘cognom’];

}

if (isset($_GET[‘edat’] )){

$edat = $_GET[‘edat’];

}

$query = ‘INSERT INTO Contactes (ID, Name, Surname, Age) VALUES (‘’, $nom, $cognom, $edat)’;


mysqli_query ($con, $query);

if (!mysqli_query ($con, $query)) die (‘Error al insertar’);

?>

– A continuació es mostra la consulta mySQL per a la creació d’una taula i una part del fitxer formulari.php allotjat al web www.example.com

Consulta de creació:

CREATE TABLE agenda (


id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

nom VARCHAR (32),


cognoms VARCHAR (64),


telefon VARCHAR (16)

);

Codi php incomplert del fitxer formulari.php:

<?php


if (isset( $_POST[‘nom’] )){

if ( !empty ( $_POST[‘nom’] )){

$nom = $_POST[‘nom’];

$link = mysqli_connect(‘myhost’,’myuser’,’mypassw’,’mybd’) or die(‘Error ‘ . mysqli_error($link));

}

}

?>

assumint que el codi es correcte:

 

1) Indiqueu la URL que serà visible al navegador quan s’enviï un formulari amb destinació (action) la pàgina formulari.php

SOLUCIÓ:

Al codi es veu que el mètode utilitzat per a recuperar els paràmetres és el mètode POST.
Per tant la url  visible serà
www.example.com/formulari.php

2) Completeu el codi PHP per tal de mostrar en pantalla el nom, cognoms i telèfon de tots els contactes de l’agenda en què el seu nom coincideixi amb el valor recuperat del formulari.

SOLUCIÓ:

$query = ‘SELECT nom, cognoms, telefon FROM agenda where nom=$nom’;

$result = $link->query($query);

$result = mysqli_query($query);

if( !$result ){

die(‘Error in the consult..’ . $link->error);

die(‘Error in the consult..’ . mysqli_error($link));

} else {

while($row = $result->fetch_assoc() ){


while($row = mysqli_fetch_array($result)){


echo $row[‘nom’].’ ‘.$row[‘cognoms’].’ ‘.$row[‘telefon’].’<br>‘;

}


}

– Una de les diferències entre el mètode GET i el mètode POST és la visibilitat dels paràmetres a l’URL de crida al fitxer.

Quin mètode s’està utilitzant en la següent crida?

http://examens.uoc.edu/DBD/examen.php?id_alume=123456

Mètode GET

Escriu el codi PHP necessari per a recuperar el valor del paràmetre id_alumne i guardar-lo en una variable PHP anomenada $id_alumne.

 

if isset($_GET[‘id_alumne’]){

$id_alumne=$_GET[‘id_alumne’];

} else {

      echo ‘error’;

}

 


 

Troba 6 errors en el codi PHP utilitzat per a inserir les dades recuperades en la taula contactes de la base de dades agenda. Les dades es recuperaran de la següent url:

 

http://www.uoc.edu/script.php?id_usuari=66&nom=Salvador&cognoms=Espriu+i+Castell ó&telefon=654321987

Nota: Assumeix que la taula contactes està creada i conté els atributs ID, Nom, Cognoms i Telefon.

Per a cada error, indica l’error i la línia on es troba.

 

  1. <?php
  2. $id_usuari = ‘‘; 

  3. $nom = ‘‘ (falta ;)

  4. $cognoms = ‘‘; 

  5. $telefon = ‘‘; 

  6. if (isset($_POST $_GET[‘id_usuari’] ) && !empty($_POST $_GET [‘id_usuari’])) { 

  7. $id_usuari = $_POST $_GET [‘id_usuari’]; 

  8. } 

  9. if (isset($_GET[‘nom’] ) && !empty($_GET[‘nom’])) { 

  10. $nom = $_GET[‘nom’]; 

  11. } 

  12. falta recuperar valors variable cognom
  13. if (isset($_GET[‘telefon] ) && !empty($_GET[‘telefon])) { 

  14. $telephon = $_GET[‘telefon’]; 

  15. } 

  16. $con=mysqli_connect(‘localhost’,’usuari_bd’,’pwd_bd’,’my_db’’agenda’); 

  17. if (mysqli_connect_errno()) 

  18. { 

  19. imprimeix echo ‘Failed to connect to MySQL: ‘ . mysqli_connect_error(); 

  20. } 

  21. &query $query= ‘INSERT INTO VALUES( ‘’, $nom, $cognoms, telefon) ‘ falta ;

  22. mysqli_query($connexio $con, $query); 


26.


  1. mysqli_close($con);
  2. ?>

SOLUCIÓ:

Línia 2: Falta el ; al final de la instrucció PHP

Línia 6: Les dades es recuperen amb el mètode get, no amb el mètode post. La variable a

utilitzar hauria de ser $_GET


Línia 13: Falta recuperar les dades de la variable $cognoms

Línia 18: La base de dades a utilitzar per a conectar-se al SGBD s’anomena contactes, mentre que a l’exercici s’utilitza la base de dades my_db

Línia 21: S’utilitza la instrucció imprimeix en comptes de la instrucció echo


Línia 24: La variable ‘&query’ s’hauria d’escriure’s amb el signe $ i no pas amb el signe &. La

variable hauria de ser $query


Línia 24: Falta el ; al final de la instrucció PHP

Línia 25: S’utilitza la variable $connexio en comptes de la variable $con, correctament declarada anteriorment

 

Operacions amb bases de dades amb MySQL (orientat a objectes, procedural)

<?php

$servername = ‘localhost’;

$username = ‘username’;

$password = ‘password’;

$dbname = ‘myDB’;

// Crear una connexió

$conn = new mysqli($servername, $username, $password, $dbname);

$conn=mysqli_connect($servername, $username, $password, $dbname);

// Comprovar connexió

if ($conn->connect_error) {

die(‘Connection failed: ‘ . $conn->connect_error);

if (mysqli_connect_errno()) { 


die(‘Connection failed: ‘ . mysqli_connect_error(); 


} 


// Crear base de dades
$sql = ‘CREATE DATABASE myDB’;
if ($conn->query($sql) === TRUE) {
    echo ‘Database created successfully’;
} else {
    echo ‘Error creating database: ‘ . $conn->error;
}

if (mysqli_query($conn, $sql)) {
    echo ‘Database created successfully’;
} else {
    echo ‘Error creating database: ‘ . mysqli_error($conn);
}

// Crear taula

$sql = ‘CREATE TABLE Convidats (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 

nom VARCHAR(30) NOT NULL,

edat VARCHAR(3) NOT NULL

)’;

if ($conn->query($sql) === TRUE) {
    echo ‘Table MyGuests created successfully’;
} else {
    echo ‘Error creating table: ‘ . $conn->error;
}

if (mysqli_query($conn, $sql)) {
    echo ‘Database created successfully’;
} else {
    echo ‘Error creating database: ‘ . mysqli_error($conn);
}

//Insertar nou registre

$sql = ‘INSERT INTO Convidats (nom, edat) VALUES (‘Xisco’, ’33’)’;
if ($conn->query($sql) === TRUE) {
    echo ‘New record created successfully’;
} else {
    echo ‘Error: ‘ . $sql . ‘<br>‘ . $conn->error;
}

if (mysqli_query($conn, $sql)) {
    echo ‘Table MyGuests created successfully’;
} else {
    echo ‘Error creating table: ‘ . mysqli_error($conn);
}

//Seleccionar i mostrar registres

$sql = ‘SELECT id, nom, edat FROM Convidats’;
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {

echo $row[‘id’] . ‘ – ‘ . $row[‘nom’] . ‘ – ‘ . $row[‘edat’] . ‘<br>‘;

}
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_assoc($result)) {
echo $row[‘id’] . ‘ – ‘ . $row[‘nom’] . ‘ – ‘ . $row[‘edat’] . ‘<br>‘;

}

$conn->close();

mysqli_close($conn);
?>

SQL QUERYS

Creating tables

CREATE TABLE customers (

id INT NOT NULL AUTO_INCREMENT,

PRIMARY KEY(id),

name VARCHAR(128),

age INT,

weight FLOAT

);

 

Inserting data

INSERT INTO customers (name, age) VALUES (‘Brian’, 33);

Querying data

SELECT * FROM customers;

SELECT * FROM customers WHERE age > 21;
SELECT * FROM customers WHERE age < 21 AND state = ‘NY’;
SELECT * FROM customers WHERE plan IN (‘free’, ‘basic’);
SELECT name, age FROM customers;
SELECT * FROM customers WHERE age > 21 ORDER BY age DESC;
 

Aggregating data

SELECT MAX(age) FROM customers;
SELECT gender, COUNT(*) FROM students GROUP BY gender;

Joining related tables

SELECT customers.name, orders.item FROM customers, orders WHERE customers.id = orders.customer_id;

Updating and deleting data

UPDATE customers SET age = 33 WHERE id = 73;
DELETE FROM customers WHERE id = 73;

SQL Exercicis

CREATE TABLE movies (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id),

name VARCHAR(128),

release_year INT

);

INSERT INTO movies VALUES (‘Avatar’, 2009);

INSERT INTO movies VALUES (‘Titanic’, 1997);

INSERT INTO movies VALUES (‘Star Wars: Episode IV – A New Hope’, 1977);

INSERT INTO movies VALUES (‘Shrek 2’, 2004);

INSERT INTO movies VALUES (‘The Lion King’, 1994);

INSERT INTO movies VALUES (‘Disney’s Up’, 2009);

SELECT * FROM movies;

SELECT * FROM movies WHERE release_year >= 2000;

CREATE TABLE todo_list (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id),

item VARCHAR(128),

minutes INT

);

INSERT INTO todo_list VALUES (‘Wash the dishes’, 15);

INSERT INTO todo_list VALUES (‘vacuuming’, 20);

INSERT INTO todo_list VALUES (‘Learn some stuff on KA’, 30);

INSERT INTO todo_list VALUES (‘pisha’, 15);

SELECT SUM(minutes) FROM todo_list

CREATE TABLE store (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

colour VARCHAR(128),

size VARCHAR(128),

material VARCHAR(128),

procedence VARCHAR(128),

price FLOAT

);

INSERT INTO store VALUES (‘red’, ‘XXL’, ‘cotton’, ‘China’, 23);

INSERT INTO store VALUES (‘yellow’, ‘XL’, ‘cotton bio’, ‘EU’, 13);

INSERT INTO store VALUES (‘black’, ‘XXL’, ‘cotton’, ‘USA’, 20);

INSERT INTO store VALUES (‘red’, ‘L’, ‘cotton’, ‘EU’, 12);

INSERT INTO store VALUES (‘grey’, ‘M’, ‘cotton bio’, ‘China’, 13);

INSERT INTO store VALUES (‘red’, ‘L’, ‘cotton’, ‘EU’, 23);

INSERT INTO store VALUES (‘yellow’, ‘XS’, ‘cotton’, ‘China’, 12);

INSERT INTO store VALUES (‘black’, ‘S’, ‘cotton’, ‘EU’, 23);

INSERT INTO store VALUES (‘red’, ‘M’, ‘cotton’, ‘USA’, 12);

INSERT INTO store VALUES (‘grey’, ‘XL’, ‘cotton bio’, ‘USA’, 43);

INSERT INTO store VALUES (‘red’, ‘XXL’, ‘cotton bio’, ‘China’, 43);

INSERT INTO store VALUES (‘black’, ‘L’, ‘cotton’, ‘USA’, 25);

INSERT INTO store VALUES (‘grey’, ‘M’, ‘cotton’, ‘EU’, 25);

INSERT INTO store VALUES (‘black’, ‘XS’, ‘cotton bio’, ‘USA’, 29);

INSERT INTO store VALUES (‘grey’, ‘S’, ‘cotton’, ‘EU’, 12);

SELECT colour, size, price, material FROM store WHERE price >15 ORDER BY price ASC;

SELECT MAX(price) FROM store;

CREATE TABLE songs (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    title VARCHAR(128),

    artist VARCHAR(128),

    mood VARCHAR(128),

    duration INT,

    released INT

);

INSERT INTO songs (title, artist, mood, duration, released)

    VALUES (‘Bohemian Rhapsody’, ‘Queen’, ‘epic’, 60, 1975);

INSERT INTO songs (title, artist, mood, duration, released)

    VALUES (‘Let it go’, ‘Idina Menzel’, ‘epic’, 227, 2013);

INSERT INTO songs (title, artist, mood, duration, released)

    VALUES (‘I will survive’, ‘Gloria Gaynor’, ‘epic’, 198, 1978);

INSERT INTO songs (title, artist, mood, duration, released)

    VALUES (‘Twist and Shout’, ‘The Beatles’, ‘happy’, 152, 1963);

INSERT INTO songs (title, artist, mood, duration, released)

    VALUES (‘La Bamba’, ‘Ritchie Valens’, ‘happy’, 166, 1958);

INSERT INTO songs (title, artist, mood, duration, released)

    VALUES (‘I will always love you’, ‘Whitney Houston’, ‘epic’, 273, 1992);

INSERT INTO songs (title, artist, mood, duration, released)

    VALUES (‘Sweet Caroline’, ‘Neil Diamond’, ‘happy’, 201, 1969);

INSERT INTO songs (title, artist, mood, duration, released)

    VALUES (‘Call me maybe’, ‘Carly Rae Jepsen’, ‘happy’, 193, 2011);

SELECT title FROM songs ;

SELECT title FROM songs WHERE mood=’epic’ OR released>1990;

SELECT title FROM songs WHERE mood=’epic’ AND released>1990 AND duration<240;

CREATE TABLE artists (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    name VARCHAR(128,

    country VARCHAR(128,

    genre VARCHAR(128

);

INSERT INTO artists (name, country, genre) VALUES (‘Taylor Swift’, ‘US’, ‘Pop’);

INSERT INTO artists (name, country, genre) VALUES (‘Led Zeppelin’, ‘US’, ‘Hard rock’);

INSERT INTO artists (name, country, genre) VALUES (‘ABBA’, ‘Sweden’, ‘Disco’);

INSERT INTO artists (name, country, genre) VALUES (‘Queen’, ‘UK’, ‘Rock’);

INSERT INTO artists (name, country, genre) VALUES (‘Celine Dion’, ‘Canada’, ‘Pop’);

INSERT INTO artists (name, country, genre) VALUES (‘Meatloaf’, ‘US’, ‘Hard rock’);

INSERT INTO artists (name, country, genre) VALUES (‘Garth Brooks’, ‘US’, ‘Country’);

INSERT INTO artists (name, country, genre) VALUES (‘Shania Twain’, ‘Canada’, ‘Country’);

INSERT INTO artists (name, country, genre) VALUES (‘Rihanna’, ‘US’, ‘Pop’);

INSERT INTO artists (name, country, genre) VALUES (‘Guns N’ Roses’, ‘US’, ‘Hard rock’);

INSERT INTO artists (name, country, genre) VALUES (‘Gloria Estefan’, ‘US’, ‘Pop’);

INSERT INTO artists (name, country, genre) VALUES (‘Bob Marley’, ‘Jamaica’, ‘Reggae’);

CREATE TABLE songs (

    INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    artist VARCHAR(128),

    title VARCHAR(128

);

INSERT INTO songs (artist, title) VALUES (‘Taylor Swift’, ‘Shake it off’);

INSERT INTO songs (artist, title) VALUES (‘Rihanna’, ‘Stay’);

INSERT INTO songs (artist, title) VALUES (‘Celine Dion’, ‘My heart will go on’);

INSERT INTO songs (artist, title) VALUES (‘Celine Dion’, ‘A new day has come’);

INSERT INTO songs (artist, title) VALUES (‘Shania Twain’, ‘Party for two’);

INSERT INTO songs (artist, title) VALUES (‘Gloria Estefan’, ‘Conga’);

INSERT INTO songs (artist, title) VALUES (‘Led Zeppelin’, ‘Stairway to heaven’);

INSERT INTO songs (artist, title) VALUES (‘ABBA’, ‘Mamma mia’);

INSERT INTO songs (artist, title) VALUES (‘Queen’, ‘Bicycle Race’);

INSERT INTO songs (artist, title) VALUES (‘Queen’, ‘Bohemian Rhapsody’);

INSERT INTO songs (artist, title) VALUES (‘Guns N’ Roses’, ‘Don’t cry’);

SELECT title FROM songs WHERE title IN (SELECT title FROM songs WHERE artist= ‘Queen’);

SELECT name FROM artists WHERE genre =‘Pop’;

SELECT title FROM songs WHERE artist IN (SELECT name FROM artists WHERE genre=‘Pop’);

CREATE TABLE books (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    author VARCHAR(128),

    title VARCHAR(128),

    words INT

);  

INSERT INTO books (author, title, words)

    VALUES (‘J.K. Rowling’, ‘Harry Potter and the Philosopher’s Stone’, 79944);

INSERT INTO books (author, title, words)

    VALUES (‘J.K. Rowling’, ‘Harry Potter and the Chamber of Secrets’, 85141);

INSERT INTO books (author, title, words)

    VALUES (‘J.K. Rowling’, ‘Harry Potter and the Prisoner of Azkaban’, 107253);

INSERT INTO books (author, title, words)

    VALUES (‘J.K. Rowling’, ‘Harry Potter and the Goblet of Fire’, 190637);

INSERT INTO books (author, title, words)

    VALUES (‘J.K. Rowling’, ‘Harry Potter and the Order of the Phoenix’, 257045);

INSERT INTO books (author, title, words)

    VALUES (‘J.K. Rowling’, ‘Harry Potter and the Half-Blood Prince’, 168923);

INSERT INTO books (author, title, words)

    VALUES (‘J.K. Rowling’, ‘Harry Potter and the Deathly Hallows’, 197651);

INSERT INTO books (author, title, words)

    VALUES (‘Stephenie Meyer’, ‘Twilight’, 118501);

INSERT INTO books (author, title, words)

    VALUES (‘Stephenie Meyer’, ‘New Moon’, 132807);

INSERT INTO books (author, title, words)

    VALUES (‘Stephenie Meyer’, ‘Eclipse’, 147930);

INSERT INTO books (author, title, words)

    VALUES (‘Stephenie Meyer’, ‘Breaking Dawn’, 192196);

INSERT INTO books (author, title, words)

    VALUES (‘J.R.R. Tolkien’, ‘The Hobbit’, 95022);

INSERT INTO books (author, title, words)

    VALUES (‘J.R.R. Tolkien’, ‘Fellowship of the Ring’, 177227);

INSERT INTO books (author, title, words)

    VALUES (‘J.R.R. Tolkien’, ‘Two Towers’, 143436);

INSERT INTO books (author, title, words)

    VALUES (‘J.R.R. Tolkien’, ‘Return of the King’, 134462);

SELECT author, SUM(words) AS total_words FROM books

    GROUP BY author

    HAVING total_words>1000000;

SELECT author, AVG(words) AS avg_words FROM books

    GROUP BY author

    HAVING avg_words>150000;

CREATE TABLE student_grades (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    name VARCHAR(128),

    number_grade INT,

    fraction_completed FLOAT

);   

INSERT INTO student_grades (name, number_grade, fraction_completed) VALUES (‘Winston’, 90, 0.805);

INSERT INTO student_grades (name, number_grade, fraction_completed) VALUES (‘Winnefer’, 95, 0.901);

INSERT INTO student_grades (name, number_grade, fraction_completed) VALUES (‘Winsteen’, 85, 0.906);

INSERT INTO student_grades (name, number_grade, fraction_completed) VALUES (‘Wincifer’, 66, 0.7054);

INSERT INTO student_grades (name, number_grade, fraction_completed) VALUES (‘Winster’, 76, 0.5013);

INSERT INTO student_grades (name, number_grade, fraction_completed) VALUES (‘Winstonia’, 82, 0.9045);

SELECT name, number_grade, ROUND (fraction_completed*100) AS percent_completed FROM student_grades;

SELECT number_grade,

    CASE

        WHEN number_grade >90 THEN ‘A’

        WHEN number_grade >80 THEN ‘B’

        WHEN number_grade >70 THEN ‘C’

        ELSE ‘F’

    END AS ‘letter_grade’

FROM student_grades

GROUP BY letter_grade;

CREATE TABLE marvels (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

       name VARCHAR(128),

       popularity INT,

       alignment VARCHAR(128),

       gender VARCHAR(128),

       height_m FLOAT,

       weight_kg FLOAT,

       hometown VARCHAR(128),

       intelligence INT,

       strength INT,

       speed INT,

       durability INT,

       energy_Projection INT,

       fighting_Skills INT

);   

INSERT INTO marvels VALUES(1, ‘Spider Man’, 1, ‘Good’, ‘Male’, 1.78, 75.75, ‘USA’, 4, 4, 3, 3, 1, 4);

INSERT INTO marvels VALUES(2, ‘Iron Man’, 20, ‘Neutral’, ‘Male’, 1.98, 102.58, ‘USA’, 6, 6, 5, 6, 6, 4);

INSERT INTO marvels VALUES(3, ‘Hulk’, 18, ‘Neutral’, ‘Male’, 2.44, 635.29, ‘USA’, 6, 7, 3, 7, 5, 4);

INSERT INTO marvels VALUES(4, ‘Wolverine’, 3, ‘Good’, ‘Male’, 1.6, 88.46, ‘Canada’, 2, 4, 2, 4, 1, 7);

INSERT INTO marvels VALUES(5, ‘Thor’, 5, ‘Good’, ‘Male’, 1.98, 290.3, ‘Norway’, 2, 7, 7, 6, 6, 4);

INSERT INTO marvels VALUES(6, ‘Green Goblin’, 91, ‘Bad’, ‘Male’, 1.93, 174.63, ‘USA’, 4, 4, 3, 4, 3, 3);

INSERT INTO marvels VALUES(7, ‘Magneto’, 11, ‘Neutral’, ‘Male’, 1.88, 86.18, ‘Germany’, 6, 3, 5, 4, 6, 4);

INSERT INTO marvels VALUES(8, ‘Thanos’, 47, ‘Bad’, ‘Male’, 2.01, 446.79, ‘Titan’, 6, 7, 7, 6, 6, 4);

INSERT INTO marvels VALUES(9, ‘Loki’, 32, ‘Bad’, ‘Male’, 1.93, 238.14, ‘Jotunheim’, 5, 5, 7, 6, 6, 3);

INSERT INTO marvels VALUES(10, ‘Doctor Doom’, 19, ‘Bad’, ‘Male’, 2.01, 188.24, ‘Latveria’, 6, 4, 5, 6, 6, 4);

INSERT INTO marvels VALUES(11, ‘Jean Greay’, 8, ‘Good’, ‘Female’, 1.68, 52.16, ‘USA’, 3, 2, 7, 7, 7, 4);

INSERT INTO marvels VALUES(12, ‘Rogue’, 4, ‘Good’, ‘Female’, 1.73, 54.43, ‘USA’, 7, 7, 7, 7, 7, 7);

SELECT name AS the_most_intelligent_villain, MAX(intelligence) AS intelligence FROM marvels WHERE alignment=‘Bad’; — els alias són opcionals

CREATE TABLE persons (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    name VARCHAR(128,

    age INT);

INSERT INTO persons (name, age) VALUES (‘Bobby McBobbyFace’, 12);

INSERT INTO persons (name, age) VALUES (‘Lucy BoBucie’, 25);

INSERT INTO persons (name, age) VALUES (‘Banana FoFanna’, 14);

INSERT INTO persons (name, age) VALUES (‘Shish Kabob’, 20);

INSERT INTO persons (name, age) VALUES (‘Fluffy Sparkles’, 8);

INSERT INTO persons (name, age) VALUES (‘Jander Klander’, 19);

CREATE table hobbies (

    INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    person_id INT,

    name VARCHAR(128);

INSERT INTO hobbies (person_id, name) VALUES (1, ‘drawing’);

INSERT INTO hobbies (person_id, name) VALUES (1, ‘coding’);

INSERT INTO hobbies (person_id, name) VALUES (2, ‘dancing’);

INSERT INTO hobbies (person_id, name) VALUES (2, ‘coding’);

INSERT INTO hobbies (person_id, name) VALUES (3, ‘skating’);

INSERT INTO hobbies (person_id, name) VALUES (3, ‘rowing’);

INSERT INTO hobbies (person_id, name) VALUES (3, ‘drawing’);

INSERT INTO hobbies (person_id, name) VALUES (4, ‘coding’);

INSERT INTO hobbies (person_id, name) VALUES (4, ‘dilly-dallying’);

INSERT INTO hobbies (person_id, name) VALUES (4, ‘meowing’);

INSERT INTO hobbies (person_id, name) VALUES (6, ‘fistring’);

SELECT persons.name, hobbies.name FROM persons, hobbies

    WHERE hobbies.person_id=persons.id;

SELECT persons.name, hobbies.name FROM persons

    JOIN hobbies

    ON hobbies.person_id=persons.id

    WHERE persons.name=‘Bobby McBobbyFace’;

CREATE TABLE customers (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    name VARCHAR(128),

    email VARCHAR(128)

);   

INSERT INTO customers (name, email) VALUES (‘Doctor Who’, ‘doctorwho@timelords.com’);

INSERT INTO customers (name, email) VALUES (‘Harry Potter’, ‘harry@potter.com’);

INSERT INTO customers (name, email) VALUES (‘Captain Awesome’, ‘captain@awesome.com’);

CREATE TABLE orders (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    customer_id INT,

    item VARCHAR(128),

    price FLOAT

);

INSERT INTO orders (customer_id, item, price) VALUES (1, ‘Sonic Screwdriver’, 1000.00);

INSERT INTO orders (customer_id, item, price) VALUES (2, ‘High Quality Broomstick’, 40.00);

INSERT INTO orders (customer_id, item, price) VALUES (1, ‘TARDIS’, 1000000.00);

SELECT customers.name, customers.email, orders.item, orders.price

    FROM customers

    LEFT OUTER JOIN orders

    ON customers.id=customer_id;

SELECT customers.name, customers.email, SUM(orders.price) as total_spent

    FROM customers

    LEFT OUTER JOIN orders

    ON customers.id=customer_id

    GROUP BY customers.name

    ORDER BY total_spent DESC ;

CREATE TABLE movies (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

       title VARCHAR(128),

       released INT,

       sequel_id INT

);

INSERT INTO movies VALUES (1, ‘Harry Potter and the Philosopher’s Stone’, 2001, 2);

INSERT INTO movies VALUES (2, ‘Harry Potter and the Chamber of Secrets’, 2002, 3);

INSERT INTO movies VALUES (3, ‘Harry Potter and the Prisoner of Azkaban’, 2004, 4);

INSERT INTO movies VALUES (4, ‘Harry Potter and the Goblet of Fire’, 2005, 5);

INSERT INTO movies VALUES (5, ‘Harry Potter and the Order of the Phoenix ‘, 2007, 6);

INSERT INTO movies VALUES (6, ‘Harry Potter and the Half-Blood Prince’, 2009, 7);

INSERT INTO movies VALUES (7, ‘Harry Potter and the Deathly Hallows – Part 1’, 2010, 8);

INSERT INTO movies VALUES (8, ‘Harry Potter and the Deathly Hallows – Part 2’, 2011, NULL);

SELECT sequels.title, movies.title FROM movies sequels

    LEFT OUTER JOIN movies

    ON movies.id=sequels.sequel_id;

CREATE TABLE persons (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    fullname VARCHAR(128),

    age INT

);   

INSERT INTO persons (fullname, age) VALUES (‘Bobby McBobbyFace’, ‘12’);

INSERT INTO persons (fullname, age) VALUES (‘Lucy BoBucie’, ‘25’);

INSERT INTO persons (fullname, age) VALUES (‘Banana FoFanna’, ‘14’);

INSERT INTO persons (fullname, age) VALUES (‘Shish Kabob’, ‘20’);

INSERT INTO persons (fullname, age) VALUES (‘Fluffy Sparkles’, ‘8’);

CREATE table hobbies (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    person_id INT,

    name VARCHAR(128)

);   

INSERT INTO hobbies (person_id, name) VALUES (1, ‘drawing’);

INSERT INTO hobbies (person_id, name) VALUES (1, ‘coding’);

INSERT INTO hobbies (person_id, name) VALUES (2, ‘dancing’);

INSERT INTO hobbies (person_id, name) VALUES (2, ‘coding’);

INSERT INTO hobbies (person_id, name) VALUES (3, ‘skating’);

INSERT INTO hobbies (person_id, name) VALUES (3, ‘rowing’);

INSERT INTO hobbies (person_id, name) VALUES (3, ‘drawing’);

INSERT INTO hobbies (person_id, name) VALUES (4, ‘coding’);

INSERT INTO hobbies (person_id, name) VALUES (4, ‘dilly-dallying’);

INSERT INTO hobbies (person_id, name) VALUES (4, ‘meowing’);

CREATE table friends (

    id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

    person1_id INT,

    person2_id INT

);

INSERT INTO friends (person1_id, person2_id)

    VALUES (1, 4);

INSERT INTO friends (person1_id, person2_id)

    VALUES (2, 3);

SELECT persons.fullname, hobbies.name

    FROM persons

    JOIN hobbies

    ON persons.id=hobbies.person_id;

SELECT a.fullname, b.fullname FROM friends

    JOIN persons a

    ON friends.person1_id = a.id

    JOIN persons b

    ON friends.person2_id = b.id;

/* Create table about the people and what they do here */

CREATE TABLE famous (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

       first_name VARCHAR(128),

       last_name VARCHAR(128)

);

INSERT INTO famous (first_name, last_name) VALUES (‘Brad’, ‘Pitt’);

INSERT INTO famous (first_name, last_name) VALUES (‘Angelina’, ‘Jolie’);

INSERT INTO famous (first_name, last_name) VALUES (‘Elizabeth’, ‘Taylor’);

INSERT INTO famous (first_name, last_name) VALUES (‘Richard’, ‘Burton’);

INSERT INTO famous (first_name, last_name) VALUES (‘Antonio’, ‘Banderas’); 

INSERT INTO famous (first_name, last_name) VALUES (‘Jessica’, ‘Biel’);

INSERT INTO famous (first_name, last_name) VALUES (‘David’, ‘Carradine’); 

INSERT INTO famous (first_name, last_name) VALUES (‘Patrick’, ‘Steward’);    

CREATE TABLE marriages (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

       famous_id1 VARCHAR(128),

       famous_id2 VARCHAR(128)

); 

INSERT INTO marriages (famous_id1, famous_id2) VALUES (1, 2);

INSERT INTO marriages (famous_id1, famous_id2) VALUES (3, 4);

CREATE TABLE movies (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

       name VARCHAR(128),

       year INT

);

INSERT INTO movies (name,year) VALUES (‘Lara Croft’, 2003);

INSERT INTO movies (name,year) VALUES (‘Seven’, 1999);

INSERT INTO movies (name,year) VALUES (‘Cleopatra’, 1963);

INSERT INTO movies (name,year) VALUES (‘Hamlet’, 1964);

INSERT INTO movies (name,year) VALUES (‘The legend of Zorro’, 2000);

INSERT INTO movies (name,year) VALUES (‘Fight Club’, 1999);   

INSERT INTO movies (name,year) VALUES (‘Mr. and Mrs. Smith’, 2005);   

INSERT INTO movies (name,year) VALUES (‘Ocean’s Eleven’, 2001);

INSERT INTO movies (name,year) VALUES (‘Ocean’s Twelve’, 2004);   

INSERT INTO movies (name,year) VALUES (‘Ocean’s Thirteen’, 2007);

INSERT INTO movies (name,year) VALUES (‘Wagner’, 1983);

INSERT INTO movies (name,year) VALUES (‘Dune’, 1984);

INSERT INTO movies (name,year) VALUES (‘Moby Dick’, 1998);

INSERT INTO movies (name,year) VALUES (‘X-Men’, 2008);

INSERT INTO movies (name,year) VALUES (‘Star Trek X: Nemesis’, 2002);    

CREATE TABLE performances (

id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

       actor_id INT,

       movie_id INT

);   

INSERT INTO performances (actor_id, movie_id) VALUES (2, 1);

INSERT INTO performances (actor_id, movie_id) VALUES (1, 2);

INSERT INTO performances (actor_id, movie_id) VALUES (3, 3);

INSERT INTO performances (actor_id, movie_id) VALUES (4, 3);

INSERT INTO performances (actor_id, movie_id) VALUES (4, 4);

INSERT INTO performances (actor_id, movie_id) VALUES (1, 6);

INSERT INTO performances (actor_id, movie_id) VALUES (1, 7);

INSERT INTO performances (actor_id, movie_id) VALUES (2, 7);

INSERT INTO performances (actor_id, movie_id) VALUES (1, 8);

INSERT INTO performances (actor_id, movie_id) VALUES (1, 9);

INSERT INTO performances (actor_id, movie_id) VALUES (1, 10);    

INSERT INTO performances (actor_id, movie_id) VALUES (4, 11);

INSERT INTO performances (actor_id, movie_id) VALUES (8, 12);

INSERT INTO performances (actor_id, movie_id) VALUES (8, 13);     

INSERT INTO performances (actor_id, movie_id) VALUES (8, 14);

INSERT INTO performances (actor_id, movie_id) VALUES (8, 15);    

SELECT famous.first_name, famous.last_name, movies.name AS movie

FROM famous, performances, movies

       WHERE famous.id = performances.actor_id

AND movies.id = performances.movie_id

       ORDER BY famous.first_name

;

La definició de la següent taula en MySQL descriu l’entitat categoria i una relació reflexiva per a expressar la jerarquia de les categories.

CREATE TABLE Categoria(


ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

Nom VARCHAR(128),


profunditat INT,


ID_Categoria INT

);

  • Escriu una consulta que retorni el nom de les categories i el nom de la seva categoria pare.


SELECT Categoria.Nom, Pare.Nom

   FROM Categoria, Categoria AS Pare

   WHERE Categoria.ID_Categoria = Pare.ID

;

 

  • Quin serà el valor de l’atribut ‘ID_categoria’ de les categories del primer nivell de la jerarquia?

Com les categories del primer nivel no tendrán categoría pare, el valor d’aquest atribut será NULL.

 

 

– A partir de la definició de les següents dues taules.

CREATE TABLE Categoria(


ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

Nom VARCHAR(128),


profunditat INT,


ID_Categoria INT

);

CREATE TABLE Producte(


ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

Nom VARCHAR (128),


Preu_Majorista DECIMAL (10, 2),


Preu_Venda DECIMAL (10, 2),


Stock INT,


ID_Categoria INT

);

Crea una consulta que retorni el nom de la categoria, el nom del producte i la quantitat disponible d’aquells productes que tinguin més de 5 unitats en stock i el seu preu de venda sigui superior a 10 euros.

 

SELECT Categoria.Nom, Producte.Nom, Producte.Stock

      FROM Categoria, Producte

      WHERE Categoria.ID = Producte.ID_Categoria

AND Producte.Stock >5 AND Producte.Preu_Venda>10

;

A partir de la definició de les taules següents:

CREATE TABLE Empresa(


ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

Nom VARCHAR(128),


adreca VARCHAR(1024),

);

CREATE TABLE Treballador(


ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),


Nom VARCHAR (128),


Cognoms VARCHAR (128),


edat int,


anys_experiencia INT,


sou_anual INT,


ID_Empresa INT,


ID_Cap INT,


FOREIGN KEY (ID_Cap) REFERENCES Treballador(ID),

FOREIGN KEY (ID_Empresa) REFERENCES Empresa(ID)

);

Creeu una consulta que retorni el nom del treballador, el nom del seu cap i el nom de l’empresa on treballa d’aquells treballadors de més de 30 anys que tenen un sou anual superior a 30.000€.

 

SELECT Treballador.nom, Cap.nom, Empresa.nom

      FROM Treballador, Treballador AS Cap, Empresa

      WHERE Treballador.ID_Cap = Cap.ID

      AND Treballador.ID_Empresa = Empresa.ID

      AND Treballador.edat > 30

AND Treballador.sou_anual > 30000

;

– Donats el diagrama E-R i la consulta SQL següents:

 

      SELECT e.Nom, SUM(t.Salari), AVG(t.Salari) as avg

FROM Empresa AS e, Treballador AS t

WHERE t.ID_Empresa = e.ID

GROUP BY e.ID

ORDER BY avg DESC;

Expliqueu quin és el resultat de la consulta i què realitza cadascuna de les seves parts o claúsules.

La consulta ens proporciona una llista d’empreses amb el nom de l’empresa, la suma dels salaris dels seus treballadors, la mitja dels salaris dels seus treballadors agrupada per empresa ordenada de forma descendent per la mitja dels salaris dels treballadors.

SELECT tria els atributs que formaran la llista. SUM(t.Salari) calcula la suma dels salaris de tots els treballadors de cada empresa. AVG(t.Salari) la mitja. FROM Empresa, Treballador realitza el producte de les dues taules. WHERE filtra aquest producte per mostrar la llista només amb els registres que realment corresponen. GROUP BY ens permet realizar que només surti un registre per empresa, agrupant-los. ORDER BY ens ordena els resultats de forma descendent segons el salari.


 

A partir del model E-R que es presenta, definiu les consultes MySQL per crear les dues taules i la relació entre elles. El motor de cerca que cal utilitzar és InnoDB i la codificació UTF-8.

CREATE TABLE Empresa (

      ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

      Nom VARCHAR(128)

) ENGINE=INNODB CHARACTER SET = UTF8;

CREATE TABLE Treballador (

      ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

      Nom VARCHAR(128),

      Cognoms VARCHAR(128),

      Salari int,

      ID_Empresa int FOREIGN KEY (ID_Empresa) REFERENCES Empresa(ID)

) ENGINE=INNODB CHARACTER SET = UTF8;

A partir de la definició de les taules següents:

CREATE TABLE Empresa(


ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

Nom VARCHAR(128),


adreca VARCHAR(1024),

);


CREATE TABLE Treballador(

ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

Nom VARCHAR (128),


Cognoms VARCHAR (128),


edat int,

anys_experiencia INT,

sou_anual INT,

ID_Empresa INT,

ID_Cap INT,

FOREIGN KEY (ID_Cap) REFERENCES Treballador(ID),

FOREIGN KEY (ID_Empresa) REFERENCES Empresa(ID)

);

Creeu una consulta que retorni, per a cada empresa amb més de 10 treballadors, el seu nom, el número de treballadors i la suma dels salaris anuals pagats als seus treballadors.

 

SELECT Empresa.ID, Empresa.Nom, COUNT(Empresa.ID) as num_treb, SUM(Treballador.sou_anual)

FROM Empresa, Treballador –- millor un LEFT JOIN segons profe

WHERE (Treballador.ID_Empresa = Empresa.ID)


GROUP BY Empresa.ID

HAVING num_treb > 10

;

Donats el diagrama E-R i la consulta SQL següents, expliqueu quin és el resultat de la consulta i què realitza cadascuna de les seves parts o clàusules.

 

 

SELECT e.ID, e.Nom, SUM(t.Salari), AVG(t.Salari) as avg

FROM Empresa AS e


LEFT JOIN Treballador t

ON e.ID = t.ID_Empresa

WHERE t.ID_Superior = NULL

GROUP BY e.ID

ORDER BY avg DESC;

La consulta ens proporciona una llista amb empreses, presentant el seu identificador, nom, la suma dels salaris dels seus treballadors de la categoria superior, la mitja dels salaris dels seus treballadors de la categoria superior, ordenada de forma descendents per la mitja dels salaris esmentats.

 

 

Donat el següent model E-R, completa els forats de la consulta per tal de que retorni el nom dels escriptors que col·laboren amb el diari anomenat ‘La coma’.

 

 

SELECT escriptor.com

FROM diaris, escriptor

WHERE escriptor.ID_Diari = Diaris.ID AND

Diaris.Nom = ‘La coma’;

 

 

 

 

 

 

 


 

A partir de la definició de les taules següents:

CREATE TABLE Brand (

      ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

      Name VARCHAR (100),

      Country VARCHAR (100),

      Year_Established INT

);

CREATE TABLE Model (

      ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),

      Name VARCHAR (100),

      Colour VARCHAR (20),

      Cost_Price INT NOT NULL,

Selling_Price INT NOT NULL,


ID_Brand INT,
FOREIGN KEY (ID_Brand) REFERENCES Brand(ID)

);

Creeu una consulta que torni els noms de totes les marques que s’hagin establert abans de l’any 2000, juntament amb el nombre total de models de cadascuna d’aquelles marques que tinguin un preu de venda de, com a mínim, el doble del preu de cost. Ordena el resultat pel nom de la marca.

 

SELECT Brand.Name, COUNT(Brand.ID)

      FROM Brand, Model

      WHERE Brand.ID = Model.ID_Brand

      AND Model.Selling_Price > Model.Cost_Price * 2

GROUP BY Brand.ID

      ORDER BY Brand.Name

;

SELECT Brand.Name, COUNT(Brand.ID) AS Total

      FROM Brand, Model

      WHERE Brand.ID = Model.ID_Brand

      AND Model.Selling_Price > Model.Cost_Price * 2

GROUP BY Brand.ID

HAVING Total >10 — Només les brands amb més de 10 models

      ORDER BY Brand.Name

;

Normalització

Usa els criteris de la teoria de la normalització comentant, pas a pas, el procés de normalització aplicat. Normalitza fins al màxim necessari.

1FN:

L’atribut Adreça i Teatre
Els atributs Adreça i Teatre no tenen valors atòmics.
Separem l’atribut Adreça en tres atributs: Carrer, codi postal i ciutat. Separem l’atribut Teatre en 2 atributs: Teatre i Localitats.

L’atribut ‘paper’ es un atribut multivalor. Dividim les files i afegim un atribut ID a cada fila. Afegim una clau primària a la taula

2FN: Descompossem la taula original en diverses taules

Actor (Id, Nom, edat, carrer, codi_postal, ID_Ciutat ) Espectacle (Id, Nom)
Teatre (ID, Nom, localitats, Id_Darrer_Espectacle)

R_Actor_Espectacle( paper, ID_Actor, ID_Espectacle )

3FN:

Sembla que hi ha dependència transitiva entre Codi Postal i Ciutat.
Per tant hauríem de crear la taula Codi Postal i treure l’atribut Id_Ciutat de la taula Actor. Per mirant la taula veiem que no sempre tenim el codi postal de l’adreça de l’actor, però en canvi si que tenim l’atribut ciutat per a cada instància. Per tant, no podem treure l’atribut Ciutat de la taula Actor i la 2Fn també està en 3FN.

1FN:

L’atribut Adreça no té valors atòmics. El separem en 3 atributs: Adreça, codi costal i Ciutat. L’atribut entrenador l’hauríem de separar en 2 atributs: Nom entrenador i Cognom entrenador.

L’atribut dies treball entrenador i l’atribut clients són atributs que tenen més d’un valor. Dividim les files i afegim un atribut ID a cada fila.

2FN:

Descomposem la taula original en diverses taules
Gimnàs ( ID, Nom, Adreça, Codi Postal, Data_Inauguració, ID_Ciutat ) Cituat ( ID, Nom )
Entrenador ( Id, Nom, Cognoms )
R_Entrenador_Gimnàs( Id, dia, Id_Gimnàs, Id_Entrenador )
Client( ID, Nom, Cognoms )
R_Client_Gimnàs( ID, ID_Gimnàs, ID_Client, data_inscripció )

3FN:

La 2FN ja està en 3FN.

1FN:

L’atribut Adreça no té valors atòmics. El separem en 3 atributs: Adreça, codi costal i Ciutat L’atribut data d’inauguració ha de tenir un format estàndard per a tots els seus valors

Els atributs marques instruments i partitures tenen més d’un valor. Dividim les files i afegim un atribut ID a cada fila.

L’atribut Afinadors el dividim en dos atributs: Nom Afinador i Cognoms Afinador

2FN:

Botiga (ID, Nom, data_inaguració, ID_Afinador) Instruments (ID, Nom)
Compositors (ID, Nom)
Afinadors(ID, Nom, Cognoms)
R_Botiga_Instrument (ID, ID_Botiga, ID_Instrument) R_Botiga_Compositors (ID, ID_Botiga, ID_Compositor)

3FN:

La 2FN ja està en 3FN

1FN:

L’atribut Adreça no té valors atòmics. El separem en 3 atributs: Adreça, codi postal i Ciutat L’atribut inauguració biblioteca hauria de tindre un format estàndard per a tots els seus valors

L’atribut Usuari el dividim en dos atributs: Nom Usuari i Cognoms Usuari

Els atribut ‘Escriptors Favorits’ i Llibres en Préstec són atributs que tenen més d’un valor. Dividim les files i afegim un atribut ID a cada fila

Cada valor de l’atribut Escriptor Favorit l’haurem de dividir en atributs: Nom Llibre, Nom Escriptor, Cognoms Escriptor

2FN:
Biblioteca (ID, Nom, Data Inauguració, Adreça, Codi Postal, ID_Ciutat) Ciutat (ID, Nom)
Usuari (ID, Nom, Cognoms)
Escriptor (ID, Nom, Cognoms)
Llibres (ID, Nom, ID_Escriptor)
R_Usuari_Escriptors_Favorits(ID, ID_Usuari, ID_Escriptor) R_Usuari_Biblioteca_Llibre_Prestec(ID, ID_usuari, ID_Biblioteca, ID_Llibre)

3FN:
La 2FN ja està en 3FN

1FN:

L’atribut longitud i l’atribut Inaguració circuit han de tindre un format estandard per a tots els seus valors.

Els atributs Longitud i Termes Municipals tenen atributs multivalor.

Dividim les files i afegim un atribut ID a cada fila.

Afegim una clau primària a la taula

L’atribut Record Volta no té valors atòmics. separem en quatre atributs: temps, pilot, escuderia, any.

2FN: Descompossem la taula original en diverses taules

Circuit( ID, Nom, Longitud, Inauguració, temps_record, any_record, id_pilot, id_escuderia ) Escuderia ( ID, Nom, any_fundacio )
Pilot( ID, nom )
Municipis(Id, Nom)

R_Municip_Circuit(Id, ID_Municipi, ID_Circuit ) R_Pilot_Escuderia( ID, any_inici, any_fi )

3FN:

La 2FN ja està en 3FN.

Si un Pilot no pogués competir en diferents escuderies al llarg de la seva carrera, llavors s’hauria d’eliminar l’atribut ID_escuderia de la taula Circuit i fer que ID_Escuderia depengués del pilot, pero com un pilot pot competir en diferents escuderies, no existeix la dependencia transitiva i ja està en 3FN.

1FN

Taula Cotxes {ID, Marca, Model, Preu, Color, Tipus, Ciutat, País]

2FN

Taula Cotxes {ID, ID_Marca, Model, Preu, ID_Tipus, ID_Ciutat}

Taula Marca {ID, Nom}

Taula Colors {ID, Nom}

Taula Tipus {ID, Nom}

Taula Ciutat {ID, Ciutat, Pais}

Taula Color_Cotxe {ID_Cotxe, ID_Color}

3FN

Taula Cotxes {ID, ID_Marca, Model, Preu, ID_Tipus, ID_Ciutat}

Taula Marca {ID, Nom}

Taula Colors {ID, Nom}

Taula Tipus {ID, Nom}

Taula Ciutat {ID, Ciutat, ID_Pais}

Taula Pais {ID, Nom} // repassar si creen taula per país o només fan desaparèixer país

Taula Color_Cotxe {ID_Cotxe, ID_Color}

PS DBBDD_v1

Leave a Reply