Grupo 1 Modelo E-A e Relacional

(1)

Página 1/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________

Identifique todas as folhas. Responda a cada grupo nas páginas respectivas do grupo. Antes de entregar, verifique que identificou todas as folhas. Estão disponíveis duas folhas de rascunho no final no final que podem ser destacadas. As folhas de rascunho têm de ser entregues no final da prova.

Exame sem consulta. Duração: 2H30.

Grupo 1 – Modelo E-A e Relacional

(6.0 v)

1a) (2.5 v) Apresente um modelo Entidade-Associação que represente os conceitos presentes no seguinte UoD: Pretende-se suportar o processo de registo de veículos automóveis, desde o registo após a importação até ao seu abate. Quando um veículo é importado, este tem um chassi (identificado pelo nrChassi) e um motor (identificado pelo nrMotor). Nota-se que é possível importar um veículo sem motor. Após o registo nacional, que acontece numa determinada data, o veículo passa a ter uma matrícula Nacional. Ao longo da sua vida e até ao abate, o veículo mantém sempre a mesma matrícula e o mesmo chassi, mas pode mudar de motor. Quando um veículo é abatido, a matrícula e o nrChassi são marcados como inválidos para circulação, mas o motor não, podendo ser aplicado noutro veículo. Em cada momento um veículo (com ou sem matrícula) só pode estar associado a um chassi e a um motor. Em qualquer momento, um veículo tem pelo menos um dono, sendo este identificado por um tipo de documento de identificação, pelo País emissor desse documento e pelo número desse documento. O sistema deverá manter o historial de todos os motores que um veiculo teve que teve. O modelo de EA a apresentar só deve ter os atributos expressos neste enunciado (ou seja, não deve ter chaves do tipo “IDs”) e todos os atributos devem ter valores (ou seja, não se admitem NULLs).

(2)

Página 2/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________

1b) (2.0 v) Converta o seguinte esquema Entidade-Associação num esquema relacional. Utilize a seguinte notação para indicar as chave primárias e chaves estrangeiras:

relacaoX(atributo1, atributo2, atributo3,... )

atributoi : FK relacaoY

atributoj, atributok : FK relacaoZ (atributox, atributoy)

unique (atributo1, …atributon )

em que os atributos que compõem a chave primária são sublinhados e FK Relacao significa que os atributos em questão são chave estrangeira para outra relação.

(3)

Página 3/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________ 1c) (1.5 v) Considere uma revista de turismo que tem pessoas a visitar cidades e a produzir comentários sobre as mesmas:

Reviewers(reviewer, name); City(city, name, country);

CityReview(city, date, reviewer, comment,rate) reviewer FK Reviewers;

city FK City;

Produza um modelo entidade Associação correspondente a estas tabelas. Ou seja, apresente um modelo EA cuja conversão relacional corresponde a estas tabelas.

(4)

Página 4/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________

Grupo 2 – SQL

(4.0 v)

Considerando as seguintes relações, escreva as expressões SQL que respondem às seguintes indicadas nas alíneas seguintes. Assuma que o atributo comment é o único que pode ser nulo. Escreva queries simples (pequenas)!

Reviewers(rid, name); City(cid, name);

CityReview(city, date, reviewer, comment,rate) reviewer FK Reviewers;

city FK City;

2a) (0.5 v) Qual é o nome dos reviewers que nunca visitaram cidades cujo nome começa por A?

(5)

Página 5/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________

2c) (1.0 v) Quais os reviewers com os mesmos comentários (entenda-se comentários de igual valor) em todas as cidades que visitou?

2d) (2.0 v) Considere a relação “City2(city,name,) city FK City;”. Escreva uma query que indique a diferença entre as tabelas City2 e City. Ou seja, que indique os registos que em existem numa tabela mas não existem na outra.

(6)

Página 6/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________

Grupo 3 – Índices

(3. 0 v)

Considere seguintes tabelas relativas à leitura de temperaturas por vários sensores em várias cidades.

Sensor(sid, properties); City(cid, name, country); AirTemp(cid, time, sid,temp); sid FK Sensor;

cid FK City;

com as seguintes características:

Tabela Nr. médio de registos Dados ordenados fisicamente por

Sensor 105 _sid

City 103 _name

AirTemp 1010 _cid

3a) (0.75 v) Indique, justificando cuidadosamente, que índices considera mais apropriados para a tabela City, tendo em conta as seguintes queries e frequências relativas:

Queries Frequência Relativa

Q1 INSERT INTO City(cid,name,country) VALUES (…..); 1 Q2 SELECT name, country FROM City WHERE cid = some_value > 10.000

3b) (0.75 v) Indique, justificando cuidadosamente, que índices considera mais apropriados para a tabela AirTemp, tendo em conta as seguintes queries e frequências relativas:

Q3 INSERT INTO AirTemp(cid,time,sid,temp) VALUES (…..) 1 Q4 SELECT AVG(temp), name FROM AirTemp, City

WHERE AirTemp.cid = City.cid AND date = today();

(7)

Página 7/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________ 3c) (0.75 v) Indique, como poderia alterar a tabela AirTemp para, sem cirar índices, melhorar o desempenho destas duas queires

Q3 INSERT INTO AirTemp(cid,time,sid,temp) VALUES (…..) 1 Q4 SELECT AVG(temp), name FROM AirTemp, City

WHERE AirTemp.cid = City.cid AND date = today();

1

3d) (0.75 v) Indique, justificando cuidadosamente, que índices considera mais apropriados para cada a tabela AirTemp, tendo em conta apenas a seguinte query:

Queries

Q5 SELECT cid, MAX(temp), MIN(temp) FROM AirTemp GROUP BY cid;

(8)

Página 8/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________

Grupo 4 – Aplicações e PHP

(3,0 v)

Considere o seguinte ficheiro html e excerto do programa X.php.

Ficheiro form.html <html><body>

Username: <input type="text" name="username"><br> <input type="submit"> </form> </body></html> FICHEIRO x.php <html><body><?php … $username= $_POST["username"];

$sql = "SELECT name FROM users WHERE username = $username”; $result = pg_query($sql) or die('ERROR: ' . pg_last_error()); $row = pg_fetch_assoc($result); while($row){ $valor = $row[“name”]; echo “<p>$valor</p>”; $row = pg_fetch_assoc($result); }; … ?></body></html>

4a) (3.0 v) Explique como poderia visualizar os nomes (campo name) de todos os utilizadores preenchendo apenas o campo Username do form definido em form.html.

(9)

Página 9/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________

Grupo 5 – Concorrência e Transações

(4,0 v)

5.1) (2 v) Indique, justificando a sua resposta, em que níveis de isolamento SQL asseguram cada uma seguintes regras:

1. Queries in current transaction cannot read data that has been modified but not yet committed by other transactions.

2. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

3. Other transactions cannot change data read by the current transaction until the current transaction completes.

(10)

Página 10/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________ 5.3) (2.0 v)

Considere seguintes queries nas transações 1 e 2 e indique, quais o valores lidos pela transação T1 para cada um dos modos de isolamento do SQL (expresso como X na tabela). Justifique a sua resposta

Tempo Transaction 1 Transaction 2

1 …. BEGIN

2 INSERT INTO City(cid,name,country) VALUES

(22,”Lisboa”,”Portugal”);

(23,”Sintra”,”Portugal”);

(24,”Porto”,”Portugal”);

5 COMMIT

6 BEGIN

7 SET TRANSATION ISOLATION LEVEL X 8 SELECT name FROM City WHERE cid = 22;

9 BEGIN

10 UPDATE City SETname = “Faro” WHERE cid = 23;

11 UPDATE City SETname = “Viseu” WHERE cid = 24

12 SELECT name FROM City WHERE cid = 23;

13 COMMIT;

14 SELECT name FROM City WHERE cid = 24;

Preencha a tabela com o valor name lido por T1 em cada momento (8, 12 e 14) em cada modo de isolamento

Tempo SERIALIZE REPEATABLE READ READ COMMITED READ UNCOMMITED 8

12 14

(11)

Página 11/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________ Página em branco

(12)

Página 12/12

Exame de SIBD – 2ª CH – 2018/19

Nº: ______________ Nome: _______________________________________________ Página em branco