Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Artur Pyśk 246832
- -- Grupa lab. 5
- -- Zad proj. 2b
- CREATE TABLE Adres(
- id SERIAL PRIMARY KEY,
- miejscowosc VARCHAR(25) NOT NULL CHECK(LENGTH(miejscowosc)>2),
- ulica VARCHAR(25) NOT NULL CHECK(LENGTH(ulica)>2),
- numer_budynku VARCHAR(6) NOT NULL,
- numer_mieszkania INTEGER,
- kod_pocztowy VARCHAR(10) NOT NULL CHECK(LENGTH(kod_pocztowy)>4)
- );
- CREATE TABLE Menadzer(
- id SERIAL PRIMARY KEY,
- imie VARCHAR(25) NOT NULL CHECK(LENGTH(imie)>2),
- nazwisko VARCHAR(25) NOT NULL CHECK(LENGTH(nazwisko)>2),
- telefon INTEGER NOT NULL,
- Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE,
- staz INTEGER NOT NULL DEFAULT(0)
- );
- CREATE TABLE Artysta(
- id SERIAL PRIMARY KEY,
- imie VARCHAR(25) NOT NULL CHECK(LENGTH(imie)>2),
- nazwisko VARCHAR(25) NOT NULL CHECK(LENGTH(nazwisko)>2),
- telefon INTEGER NOT NULL,
- gatunek VARCHAR(15) NOT NULL CHECK(LENGTH(gatunek)>2),
- Adresy_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE,
- Menadzer_id INTEGER REFERENCES Menadzer(id) ON UPDATE CASCADE,
- bonus INTEGER DEFAULT(0),
- rok_dolaczenia INTEGER NOT NULL
- );
- CREATE TABLE Album(
- id SERIAL PRIMARY KEY,
- nazwa VARCHAR(20) NOT NULL UNIQUE CHECK(LENGTH(nazwa)>3) ,
- data_wydania DATE NOT NULL DEFAULT NOW(),
- dlugosc INTEGER NOT NULL,
- Artysta_id INTEGER REFERENCES Artysta(id) ON UPDATE CASCADE
- );
- CREATE TABLE Dystrybutor(
- id SERIAL PRIMARY KEY,
- nazwa VARCHAR(20) NOT NULL UNIQUE,
- kraj VARCHAR(15) NOT NULL,
- telefon INTEGER NOT NULL,
- email VARCHAR(25) NOT NULL UNIQUE,
- Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE,
- kontynent VARCHAR(30),
- rabat INTEGER
- );
- CREATE TABLE Producent(
- id SERIAL PRIMARY KEY,
- imie VARCHAR(15) NOT NULL CHECK(LENGTH(imie)>2),
- nazwisko VARCHAR(20) NOT NULL CHECK(LENGTH(nazwisko)>2),
- telefon INTEGER NOT NULL,
- email VARCHAR(25) NOT NULL UNIQUE,
- Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE
- );
- CREATE TABLE Mastering(
- id SERIAL PRIMARY KEY,
- nazwa VARCHAR(20) NOT NULL UNIQUE,
- telefon INTEGER NOT NULL,
- email VARCHAR(25) NOT NULL UNIQUE,
- Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE
- );
- CREATE TABLE Studio_Nagraniowe(
- id SERIAL PRIMARY KEY,
- nazwa VARCHAR(20) NOT NULL UNIQUE,
- telefon INTEGER NOT NULL,
- email VARCHAR(25) NOT NULL UNIQUE,
- Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE
- );
- CREATE TABLE Rezerwacja_Studia(
- id SERIAL PRIMARY KEY,
- data DATE NOT NULL DEFAULT NOW(),
- Studio_Nagraniowe_id INTEGER REFERENCES Studio_Nagraniowe(id) ON UPDATE CASCADE,
- Artysta_id INTEGER REFERENCES Artysta(id) ON UPDATE CASCADE
- );
- CREATE TABLE Album_Wydany(
- Album_id INTEGER REFERENCES Album(id) ON UPDATE CASCADE,
- Dystrybutor_id INTEGER REFERENCES Dystrybutor(id) ON UPDATE CASCADE,
- Producent_id INTEGER REFERENCES Producent(id) ON UPDATE CASCADE,
- Studio_Nagraniowe_id INTEGER REFERENCES Studio_Nagraniowe(id) ON UPDATE CASCADE,
- Mastering_id INTEGER REFERENCES Mastering(id) ON UPDATE CASCADE,
- CONSTRAINT PK_Album_Wydany PRIMARY KEY(Album_id, Dystrybutor_id, Producent_id, Studio_Nagraniowe_id,
- Mastering_id)
- );
- INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
- VALUES('Berlin', 'Konstanzer Strasse', '71', 5, '35647');
- INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
- VALUES('Ampfing', 'Kieler Srasse', '20', 2, '84536');
- INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
- VALUES('Montreal', 'Rene-Levesque', '193', 6, '54856');
- INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
- VALUES('Warszawa', 'Aleje Jerozolimskie', '55', 2, '02-011');
- INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
- VALUES('Kraków', 'Dêbowa', '40', 1, '43-300');
- INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
- VALUES('Adam', 'Deby', 791584568, 2,2);
- INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
- VALUES('Michal', 'Wisniewski', 584754568, 1, 1);
- INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
- VALUES('Mikhail', 'Krakov', 795424568, 3, 7);
- INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
- VALUES('Nathaniel', 'Sernik', 874584568, 5, 4);
- INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
- VALUES('Michael', 'Crook', 884568, 4, 0);
- INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
- VALUES('Matthew', 'Bellamy', 3242412, 'rock', 2, 1, 1997);
- INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
- VALUES('Michael', 'Jackson', 24324543, 'pop', 3, 3, 2002);
- INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
- VALUES('Sarsa', 'Markiewicz', 24324521, 'pop', 1, 2, 2014);
- INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
- VALUES('Samuel', 'Jackson', 45224452, 'rock', 4, 5, 2012);
- INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
- VALUES('Michal', 'Wisniewski', 6546234, 'pop', 5, 2, 2017);
- INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
- VALUES('Origin of Symmetry', '2014-06-22', 45, 1);
- INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
- VALUES('Shadowmoon', '2011-06-22', 45, 1);
- INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
- VALUES('Markers', '2004-02-12', 55, 2);
- INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
- VALUES('Amen', '2002-01-26', 34, 3);
- INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
- VALUES('Droga', '2013-06-22', 46, 4);
- INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
- VALUES('Drive', '2015-09-11', 64, 5);
- INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
- VALUES('Step Records', 'Poland', 789456248, 'steprecords@gmail.com', 5);
- INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
- VALUES('Warner Music', 'Germany', 32245432, 'warner.germany@warner.com', 2);
- INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
- VALUES('Organek Records', 'Polska', 789456248, 'steprecordss@gmail.com', 4);
- INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
- VALUES('Global Records', 'Germany', 324113265, 'global.records@gmail.com', 2);
- INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
- VALUES('Universal Music', 'Canada', 36523413, 'universal.music@uni.com', 3);
- INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
- VALUES('Michal', 'Skarzynski', 784582485, 'm.skarz@gmail.com', 3);
- INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
- VALUES('Shawn', 'Morrison', 2351235, 's.morrison@gmail.com', 1);
- INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
- VALUES('Peter', 'Parker', 5382485, 'p.parker@gmail.com', 2);
- INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
- VALUES('Piotr', 'Polak', 334582485, 'piotr.polak@gmail.com', 5);
- INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
- VALUES('Sara', 'Larsson', 69854872, 'sara.larsson@gmail.com', 4);
- INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
- VALUES('Mastering Ltd.', 48545468, 'mas.ltd@gmail.com', 1);
- INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
- VALUES('Sterling Sound', 4234212, 'sterl.sound@gmail.com', 2);
- INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
- VALUES('Bain', 5632435, 'bain@gmail.com', 3);
- INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
- VALUES('Clio Sound', 113545468, 'clio.so@gmail.com', 4);
- INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
- VALUES('Berk Sound', 66545468, 'berk.sd@gmail.com', 5);
- INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
- VALUES('Polskie Radio', 795845648, 'polskie.ra@gmail.com', 1);
- INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
- VALUES('Republica', 8654895, 'republica@gmail.com', 3);
- INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
- VALUES('TR Studios', 5345235, 'trstudios@gmail.com', 2);
- INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
- VALUES('Pasja Sounds', 2587458, 'pasja@gmail.com', 5);
- INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
- VALUES('Nonagram', 7633452, 'nonagram@gmail.com', 4);
- INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
- VALUES('2014-05-22', 2, 1);
- INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
- VALUES('2013-01-22', 2, 1);
- INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
- VALUES('2010-02-11', 4, 2);
- INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
- VALUES('2011-02-15', 4, 2);
- INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
- VALUES('2005-04-21', 5, 4);
- INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
- VALUES('2015-09-28', 3, 3);
- INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
- VALUES('2001-01-01', 5, 5);
- INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
- VALUES(1, 2, 4, 2, 2);
- INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
- VALUES(5, 3, 4, 2, 1);
- INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
- VALUES(1, 2, 3, 2, 5);
- INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
- VALUES(2, 2, 4, 2, 2);
- INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
- VALUES(4, 3, 4, 2, 5);
- --1a) Tworzymy widok
- --Widok będzie pokazywał artystów, którzy rezerwowali przynajmniej dwa razy studio nagraniowe przed 2013 rokiem.
- CREATE VIEW ilosc_albumow_artystow
- AS
- SELECT a.imie, a.nazwisko, COUNT(a.id) AS ilosc_rezerwacji FROM Artysta a
- INNER JOIN Rezerwacja_Studia re ON a.id=re.Artysta_id WHERE (extract(year from re.data)<2013)
- GROUP BY a.imie, a.nazwisko
- HAVING COUNT(a.id)>=2;
- --1b) Sprawdzenie, że widok działa
- SELECT imie, nazwisko, ilosc_rezerwacji
- FROM ilosc_albumow_artystow
- ORDER BY ilosc_rezerwacji ASC;
- --2a) Tworzymy funkcjê 1
- --Funkcja sprawdzająca czy dane studio ma wolny termin na rezerwacje po podaniu ID studia oraz daty.
- CREATE FUNCTION dostepna_rezerwacja(id_studia INTEGER, data DATE) RETURNS VARCHAR(30)
- AS
- DECLARE
- prawda VARCHAR(30);
- falsz VARCHAR(30);
- BEGIN
- prawda:='Rezerwacja jest dostepna';
- falsz:='Rezerwacja nie jest dostepna.';
- IF EXISTS(SELECT data FROM Rezerwacja_Studia WHERE data=$2)
- THEN
- RETURN falsz;
- ELSE
- RETURN prawda;
- END;
- $$ LANGUAGE plpgsql;
- --2b) Sprawdzenie, ¿e funkcja 1 dzia³a
- SELECT * FROM dostepna_rezerwacja(5,'2001-01-01');
- SELECT * FROM dostepna_rezerwacja(5,'2009-01-01');
- --3a) Tworzymy funkcjê 2
- --Funkcja zwracaj¹ca ³¹czn¹ d³ugoæ trwania albumów danego artysty.
- CREATE FUNCTION dlugosc_albumow_artysty (
- @id_artysty INT)
- RETURNS INT
- AS
- BEGIN
- RETURN (SELECT SUM(dlugosc) AS laczna_dlugosc FROM Album WHERE @id_artysty=Artysta_id);
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement