Data - Postgres - Definició de dades (Avaluació)

Gran teatre

El "Gran Teatre" ha decidit informatitzar el seu sistema de venda d’entrades, i el responsable d’informàtica, el professor Codd, ha dissenyat aquesta base de dades utilitzant entitats dèbils (taules en que la clau primaria està formada per varies columnes, de les quals algunes fan referència a la clau primària d’una altra taula):

classDiagram
direction LR

class zona { 
  # codi
  nom 
}
class butaca { # numero}
butaca --> zona: # zona

class espectacle { 
  #codi
  nom
  descripcio
}
class sessio { # dia}
sessio --> espectacle: # espectacle

class entrada { preu }
entrada --> butaca : # butaca
entrada --> sessio : # sessio

1.- Escriu les sentències SQL que permeten generar les taules d’aquest disseny, amb les restriccions d’integritat que consideris adients (primary key, foreign key, not-null, unique i check):

create table zona (
    codi char primary key,
    nom text not null
);

create table butaca (
    zona char references zona,   // i not null  (idem altres) corretgir
    numero int check (numero > 0),
    primary key (zona,numero)
);

create table espectacle (
    codi text primary key,
    nom text not null,
    descripcio text not null
);

create table sessio (
    espectacle text references espectacle,
    dia date not null,
    primary key (espectacle,dia)
);

create table entrada (
    zona char,
    butaca int,
    espectacle text,
    dia date,
    preu numeric(5,2) not null check (preu > 0),
    primary key (zona,butaca,espectacle,dia),
    foreign key (zona,butaca) references butaca,
    foreign key (espectacle, dia) references sessio
);

2.- Inserta una 'entrada' a la base de dades per comprobar el seu funcionament:

insert into zona values('A','Platea');
insert into butaca values('A',1);
insert into espectacle values ('TRO','Il trovatore','Leonora i Manrico, una parella ...');
insert into sessio values('TRO','2022-04-06');
insert into entrada values('A',1,'TRO','2022-04-06', 60);

3.- Codd ha decidit que el preu d’una entrada depèn de la zona i de la sessio, i cal normalitzar (eliminar redundància), i ha canviat el disseny:

classDiagram
direction LR

class zona { 
  # codi
  nom 
}
class butaca { # numero}
butaca --> zona: # zona

class espectacle { 
  #codi
  nom
  descripcio
}
class sessio { # dia}
sessio --> espectacle: # espectacle

class entrada
entrada --> butaca : # butaca
entrada --> sessio : # sessio

class preu { preu}
preu --> zona : # zona
preu --> sessio: # sessio

Per tant:

Crea la taula preu
Crea un "preu" per l’entrada que has creat abans
Elimina la columna preu de la taula entrada

create table preu (
    zona char references zona,
    espectacle text,
    dia date,
    preu numeric(5,2),
    primary key (zona,espectacle,dia),
    foreign key (espectacle,dia) references sessio
);

insert into preu values ('A','TRO','2022-04-06', 60);

alter table entrada drop column preu;

4.- El nou disseny ha quedat normalitzat, però per saber el preu d’una entrada s’ha de fer un join. Encara que aquest join és molt simple, ja que es pot fer amb using(col1, col2, …), no és evident si no et donen aquesta pista. Pert tant, Codd ha decidit crear una vista que mostri les entrades amb el seu preu.

La teva tasca es crear aquesta vista i comprovar que funciona (fes un select)

create view entrada_amb_preu as
  select *
  from entrada e join preu using(espectacle,dia,zona);

select * from entrada_amb_preu;

5.- Durant les proves del sistema es detecta un error: Quan es fa una consulta a la vista entrada_amb_preu no apareixen varies entrades en el resultat de la consulta.

Com pot ser això?
Com es pot corretgir?

// add foreign keys

Auth Service

El responsable d’informàtica, el professor Codd, ha decidit dissenyar el seu propi sistema d'identificació i autorització.

Ha de ser un sistema molt eficient en el temps de resposta, i per tant, ha decidit que només s’utilitzaran claus subrogades com a claus primàries (enlloc de fer servir claus naturals).

2.1.- Escriu les sentències SQL que permeten generar les taules d’aquest disseny, amb les restriccions d’integritat que consideris adients (primary key, foreign key, not-null, unique i check):

classDiagram
direction LR

class usuari { 
  # usuari_id
  nom
  contrasenya 
}
class login { 
  # login_id
  data
  ip?
}
login ..> usuari

create table usuari (
	usuari_id int primary key generated always as identity,
	nom text not null,
	contrasenya text not null,
      unique (nom, contrasenya)
);

create table login (
	login_id int primary key generated always as identity,
	usuari_id int references usuari,
	data timestamp not null default CURRENT_TIMESTAMP,
      ip inet
);

2.2.- Insereix un nou login sense IP i un altre amb IP

insert into usuari(nom,contrasenya) values ('joan','P@ssw0rd');
insert into login(usuari_id) values (1);
insert into login(usuari_id,ip) values (1,'217.76.130.44');

2.3.- Perquè les consultes siguin molt ràpides i eficients, la taula usuari té el nom d’usuari i la contrasenya, però també té un problema de seguretat. Com a solució temporal Codd ha decidit que has de crear una vista usuari_pub sense la contrasenya.

create view usuari_pub as
    select usuari_id, nom from usuari;

2.4.- Després de repensar el disseny, Codd ha decidit que és millor que les contrasenyes estiguin en una taula contrasenya, amb la data que es va crear.

classDiagram
direction LR

class usuari { 
  # usuari_id
  nom
}
class login { 
  # login_id
  data
  ip?
}
login ..> usuari

class contrasenya { 
  # contrasenya_id
  contrasenya
  data 
}
contrasenya ..> usuari

Per tant, has de crear la taula contrasenya, moure dades, esborrar la columna contrasenya de la taula usuari, i eliminar la vista usuari_pub.

create table contrasenya (
    contrasenya_id int primary key generated always as identity,
    usuari_id int references usuari,
    contrasenya text not null,
    data timestamp not null default CURRENT_TIMESTAMP
);

insert into contrasenya(usuari_id,contrasenya)
    select usuari_id, contrasenya from usuari;
    
alter table usuari drop column contrasenya;

drop view usuari_pub;

2.5.- El professor Codd està molt content amb aquest disseny perquè un usuari pot tenir vàries contrasenyes, i els dissenyadors de les aplicacions web tenen varies opcions: tenir una política de màxim temps que una contrasenya pot estar registrada, utilitzar les contrasenyes antigues com a factor de recuperació de contrasenya, només permetre la contrasenya més recent, etc.

Però tothom es queixa, per què com es pot obtenir només la contrasenya més recent?. Fàcil. Però per evitar més queixes, Codd diu que has de crear una vista contrasenya_ultima, i que afegeixis dos usuaris nous, i dos contrasenyes amb data diferent per cada usuari perquè tothom pugui veure que la vista funciona.

create view contrasenya_ultima as
    select distinct on (usuari_id) usuari_id, contrasenya
    from contrasenya
    order by usuari_id, data desc;

insert into usuari(nom) values ('Eva'),('Marc');
insert into contrasenya(usuari_id,contrasenya)
	values (1, 'pass1'), (2, 'pass1'), (3, 'pass1');
insert into contrasenya(usuari_id,contrasenya)
	values (1, 'pass2'), (2, 'pass2'), (3, 'pass2');

select * from contrasenya_ultima;

2.6.- El professor Codd ha decidit modificar el disseny per motius de seguretat. A partir d’ara es podran guardar totes les contrasenyes utilitzades pel usuaris, per tant, és necessita una columna activa. A més, també afegim una columna opcional ip que permet limitar l’ús d’una contrasenya a una determinada ip. I per més seguretat, la taula login tindrà una columna contrasenya_id per saber quina contrasenya s’ha utilitzat per fer el login.

classDiagram
direction LR

class usuari { 
  # usuari_id
  nom
}
class login { 
  # login_id
  data
  ip?
}
login ..> usuari
login ..> contrasenya

class contrasenya { 
  # contrasenya_id
  contrasenya
  data
  activa
  ip?
}
contrasenya ..> usuari

alter table contrasenya add column activa bool default true;
alter table contrasenya add column ip inet;

alter table login add column contrasenya_id int references contrasenya;

PENDENT

Normalitzar usuari (usuari_id, nom) Hash contrasenya (update)