FIIT PDT session 4 https://github.com/fiit-pdt/hands-on

pdt-session-4-exercise-1.md

Exercise 1

[use fiitpdt/postgres for this excercise] Suppose the following relational schema exists, where there are travel agents selling flights to some destination. A flight can have multiple services from the set {business class, lunch, drinks, priority boarding, online check-in}.

AGENT (**a_id**, name)
FLIGHT (**destination**, pilot_name, a_id)
SERVICE (**flight**, **service**)

• Transform this schema to an object version using 2 data types
• Insert some test data
• Write query which returns services provided on a flight to London. Results should be in 1st normal form

Transform this schema to an object version using 2 data types

create type t_agent as (
    name text
);

create type t_flight as (
    destination text,
    pilot_name text,
    agent t_agent,
    services text array
);

create table flights of t_flight;

Insert some test data

insert into flights values ('Brusel', 'Tomas', row('Agent 001'), array ['drinks', 'lunch']);
insert into flights values ('Paris', 'Pavol', row('Agent 002'), array ['business class', 'lunch', 'drinks', 'priority boarding', 'online check-in']);
insert into flights values ('London', 'Jozef', row('Agent 002'), array ['business class', 'lunch', 'online check-in']);

Write query which returns services provided on a flight to London. Results should be in 1st normal form

select destination, unnest(services) from flights where destination = 'London';

pdt-session4.sql

!!! Ked sa niekde pise, ze iba 2 typy, tak robit iba 2 typy
create type t_agent as (
	name text
)

create type t_flight as (
	destination text,
	pilot_name text,
	agent t_agent,
	services text array
)

create table flights_types of t_flight;

!!! Pozor, syntax prednasajuceho je trochu ina, treba sa ju len naucit

insert into flights_types values ('Brusel', 'Tomas', row('Agent 001'), "{'drinks', 'lunch'}")
insert into flights_types values ('Brusel', 'Tomas', row('Agent 001'), ['drinks', 'lunch'])

select * from flights_types where destination = 'Brusel'
select (agent).name from flights_types where destination = 'Brusel'
select services from flights_types where destination = 'Brusel'
select unnest(services) from flights_types where destination = 'Brusel'

#vsetky lety kde dostanem obed
select * flights_types where 'lunch' = any(services)


create type t_person as (
	name text,
	address text,
	skills text array
);

create type t_car as (
	make text,
	model text,
	performance text,
	mechanic t_person,
	driver t_person
);

create table garage of t_car;

insert into garage values ('2016', 'Skoda', 'maly', row('Jozef', 'Krivan', array ['driving', 'repair']), row('Pali', 'LM', array ['driving', 'winning']));

insert into garage values ('2016', 'Skoda', 'maly', row('Karol', 'LM', array ['driving', 'repair']), row('Stefan', 'LM', array ['driving', 'winning']));
insert into garage values ('2015', 'Nissan', 'velky', row('Peter', 'Krivan', array ['repair']), row('Matej', 'BA', array ['driving', 'winning']));

select (mechanic).name from garage where 'driving' = any((mechanic).skills);
select * from garage where (mechanic).address = (driver).address;
select (driver).name, (driver).skills[1] from garage;