Entity

Task: Gran teatre
Auth Service

Task: 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):

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:

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)

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?

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):

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

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.

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.

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.

PENDENT

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

Postgres - Entity

Task: Gran teatre

Auth Service