Advertisement
Artcik

projo_2c

Jun 1st, 2017
922
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.15 KB | None | 0 0
  1. -- Artur Pyśœk 246832
  2. -- Grupa lab. 5
  3. -- Zad proj. 2b
  4.  
  5. CREATE TABLE Adres(
  6. id SERIAL PRIMARY KEY,
  7. miejscowosc VARCHAR(25) NOT NULL CHECK(LENGTH(miejscowosc)>2),
  8. ulica VARCHAR(25) NOT NULL CHECK(LENGTH(ulica)>2),
  9. numer_budynku VARCHAR(6) NOT NULL,
  10. numer_mieszkania INTEGER,
  11. kod_pocztowy VARCHAR(10) NOT NULL CHECK(LENGTH(kod_pocztowy)>4)
  12. );
  13.  
  14. CREATE TABLE Menadzer(
  15. id SERIAL PRIMARY KEY,
  16. imie VARCHAR(25) NOT NULL CHECK(LENGTH(imie)>2),
  17. nazwisko VARCHAR(25) NOT NULL CHECK(LENGTH(nazwisko)>2),
  18. telefon INTEGER NOT NULL,
  19. Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE,
  20. staz INTEGER NOT NULL DEFAULT(0)
  21. );
  22.  
  23.  
  24.  
  25. CREATE TABLE Artysta(
  26. id SERIAL PRIMARY KEY,
  27. imie VARCHAR(25) NOT NULL CHECK(LENGTH(imie)>2),
  28. nazwisko VARCHAR(25) NOT NULL CHECK(LENGTH(nazwisko)>2),
  29. telefon INTEGER NOT NULL,
  30. gatunek VARCHAR(15) NOT NULL CHECK(LENGTH(gatunek)>2),
  31. Adresy_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE,
  32. Menadzer_id INTEGER REFERENCES Menadzer(id) ON UPDATE CASCADE,
  33. bonus INTEGER DEFAULT(0),
  34. rok_dolaczenia INTEGER NOT NULL
  35. );
  36.  
  37.  
  38. CREATE TABLE Album(
  39. id SERIAL PRIMARY KEY,
  40. nazwa VARCHAR(20) NOT NULL UNIQUE CHECK(LENGTH(nazwa)>3) ,
  41. data_wydania DATE NOT NULL DEFAULT NOW(),
  42. dlugosc INTEGER NOT NULL,
  43. Artysta_id INTEGER REFERENCES Artysta(id) ON UPDATE CASCADE
  44. );
  45.  
  46.  
  47. CREATE TABLE Dystrybutor(
  48. id SERIAL PRIMARY KEY,
  49. nazwa VARCHAR(20) NOT NULL UNIQUE,
  50. kraj VARCHAR(15) NOT NULL,
  51. telefon INTEGER NOT NULL,
  52. email VARCHAR(25) NOT NULL UNIQUE,
  53. Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE,
  54. kontynent VARCHAR(30),
  55. rabat INTEGER
  56. );
  57.  
  58.  
  59. CREATE TABLE Producent(
  60. id SERIAL PRIMARY KEY,
  61. imie VARCHAR(15) NOT NULL CHECK(LENGTH(imie)>2),
  62. nazwisko VARCHAR(20) NOT NULL CHECK(LENGTH(nazwisko)>2),
  63. telefon INTEGER NOT NULL,
  64. email VARCHAR(25) NOT NULL UNIQUE,
  65. Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE
  66. );
  67.  
  68.  
  69. CREATE TABLE Mastering(
  70. id SERIAL PRIMARY KEY,
  71. nazwa VARCHAR(20) NOT NULL UNIQUE,
  72. telefon INTEGER NOT NULL,
  73. email VARCHAR(25) NOT NULL UNIQUE,
  74. Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE
  75. );
  76.  
  77.  
  78. CREATE TABLE Studio_Nagraniowe(
  79. id SERIAL PRIMARY KEY,
  80. nazwa VARCHAR(20) NOT NULL UNIQUE,
  81. telefon INTEGER NOT NULL,
  82. email VARCHAR(25) NOT NULL UNIQUE,
  83. Adres_id INTEGER REFERENCES Adres(id) ON UPDATE CASCADE
  84. );
  85.  
  86.  
  87. CREATE TABLE Rezerwacja_Studia(
  88. id SERIAL PRIMARY KEY,
  89. data DATE NOT NULL DEFAULT NOW(),
  90. Studio_Nagraniowe_id INTEGER REFERENCES Studio_Nagraniowe(id) ON UPDATE CASCADE,
  91. Artysta_id INTEGER REFERENCES Artysta(id) ON UPDATE CASCADE
  92. );
  93.  
  94.  
  95. CREATE TABLE Album_Wydany(
  96. Album_id INTEGER REFERENCES Album(id) ON UPDATE CASCADE,
  97. Dystrybutor_id INTEGER REFERENCES Dystrybutor(id) ON UPDATE CASCADE,
  98. Producent_id INTEGER REFERENCES Producent(id) ON UPDATE CASCADE,
  99. Studio_Nagraniowe_id INTEGER REFERENCES Studio_Nagraniowe(id) ON UPDATE CASCADE,
  100. Mastering_id INTEGER REFERENCES Mastering(id) ON UPDATE CASCADE,
  101.  
  102. CONSTRAINT PK_Album_Wydany PRIMARY KEY(Album_id, Dystrybutor_id, Producent_id, Studio_Nagraniowe_id,
  103. Mastering_id)
  104. );
  105.  
  106.  
  107. INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
  108. VALUES('Berlin', 'Konstanzer Strasse', '71', 5, '35647');
  109.  
  110. INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
  111. VALUES('Ampfing', 'Kieler Srasse', '20', 2, '84536');
  112.  
  113. INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
  114. VALUES('Montreal', 'Rene-Levesque', '193', 6, '54856');
  115.  
  116. INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
  117. VALUES('Warszawa', 'Aleje Jerozolimskie', '55', 2, '02-011');
  118.  
  119. INSERT INTO Adres(miejscowosc,ulica,numer_budynku,numer_mieszkania,kod_pocztowy)
  120. VALUES('Kraków', 'Dêbowa', '40', 1, '43-300');
  121.  
  122.  
  123. INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
  124. VALUES('Adam', 'Deby', 791584568, 2,2);
  125.  
  126. INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
  127. VALUES('Michal', 'Wisniewski', 584754568, 1, 1);
  128.  
  129. INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
  130. VALUES('Mikhail', 'Krakov', 795424568, 3, 7);
  131.  
  132. INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
  133. VALUES('Nathaniel', 'Sernik', 874584568, 5, 4);
  134.  
  135. INSERT INTO Menadzer(imie,nazwisko,telefon,Adres_id,staz)
  136. VALUES('Michael', 'Crook', 884568, 4, 0);
  137.  
  138.  
  139. INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
  140. VALUES('Matthew', 'Bellamy', 3242412, 'rock', 2, 1, 1997);
  141.  
  142. INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
  143. VALUES('Michael', 'Jackson', 24324543, 'pop', 3, 3, 2002);
  144.  
  145. INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
  146. VALUES('Sarsa', 'Markiewicz', 24324521, 'pop', 1, 2, 2014);
  147.  
  148. INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
  149. VALUES('Samuel', 'Jackson', 45224452, 'rock', 4, 5, 2012);
  150.  
  151. INSERT INTO Artysta(imie,nazwisko,telefon,gatunek,Adresy_id, Menadzer_id,rok_dolaczenia)
  152. VALUES('Michal', 'Wisniewski', 6546234, 'pop', 5, 2, 2017);
  153.  
  154.  
  155. INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
  156. VALUES('Origin of Symmetry', '2014-06-22', 45, 1);
  157.  
  158. INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
  159. VALUES('Shadowmoon', '2011-06-22', 45, 1);
  160.  
  161. INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
  162. VALUES('Markers', '2004-02-12', 55, 2);
  163.  
  164. INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
  165. VALUES('Amen', '2002-01-26', 34, 3);
  166.  
  167. INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
  168. VALUES('Droga', '2013-06-22', 46, 4);
  169.  
  170. INSERT INTO Album(nazwa,data_wydania,dlugosc,Artysta_id)
  171. VALUES('Drive', '2015-09-11', 64, 5);
  172.  
  173.  
  174.  
  175. INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
  176. VALUES('Step Records', 'Poland', 789456248, 'steprecords@gmail.com', 5);
  177.  
  178. INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
  179. VALUES('Warner Music', 'Germany', 32245432, 'warner.germany@warner.com', 2);
  180.  
  181. INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
  182. VALUES('Organek Records', 'Polska', 789456248, 'steprecordss@gmail.com', 4);
  183.  
  184. INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
  185. VALUES('Global Records', 'Germany', 324113265, 'global.records@gmail.com', 2);
  186.  
  187. INSERT INTO Dystrybutor(nazwa,kraj,telefon,email,Adres_id)
  188. VALUES('Universal Music', 'Canada', 36523413, 'universal.music@uni.com', 3);
  189.  
  190.  
  191.  
  192. INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
  193. VALUES('Michal', 'Skarzynski', 784582485, 'm.skarz@gmail.com', 3);
  194.  
  195. INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
  196. VALUES('Shawn', 'Morrison', 2351235, 's.morrison@gmail.com', 1);
  197.  
  198. INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
  199. VALUES('Peter', 'Parker', 5382485, 'p.parker@gmail.com', 2);
  200.  
  201. INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
  202. VALUES('Piotr', 'Polak', 334582485, 'piotr.polak@gmail.com', 5);
  203.  
  204. INSERT INTO Producent(imie,nazwisko,telefon,email,Adres_id)
  205. VALUES('Sara', 'Larsson', 69854872, 'sara.larsson@gmail.com', 4);
  206.  
  207.  
  208. INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
  209. VALUES('Mastering Ltd.', 48545468, 'mas.ltd@gmail.com', 1);
  210.  
  211. INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
  212. VALUES('Sterling Sound', 4234212, 'sterl.sound@gmail.com', 2);
  213.  
  214. INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
  215. VALUES('Bain', 5632435, 'bain@gmail.com', 3);
  216.  
  217. INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
  218. VALUES('Clio Sound', 113545468, 'clio.so@gmail.com', 4);
  219.  
  220. INSERT INTO Mastering(nazwa, telefon, email, Adres_id)
  221. VALUES('Berk Sound', 66545468, 'berk.sd@gmail.com', 5);
  222.  
  223.  
  224. INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
  225. VALUES('Polskie Radio', 795845648, 'polskie.ra@gmail.com', 1);
  226.  
  227. INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
  228. VALUES('Republica', 8654895, 'republica@gmail.com', 3);
  229.  
  230. INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
  231. VALUES('TR Studios', 5345235, 'trstudios@gmail.com', 2);
  232.  
  233. INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
  234. VALUES('Pasja Sounds', 2587458, 'pasja@gmail.com', 5);
  235.  
  236. INSERT INTO Studio_Nagraniowe(nazwa,telefon,email,Adres_id)
  237. VALUES('Nonagram', 7633452, 'nonagram@gmail.com', 4);
  238.  
  239.  
  240. INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
  241. VALUES('2014-05-22', 2, 1);
  242.  
  243. INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
  244. VALUES('2013-01-22', 2, 1);
  245.  
  246. INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
  247. VALUES('2010-02-11', 4, 2);
  248.  
  249. INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
  250. VALUES('2011-02-15', 4, 2);
  251.  
  252. INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
  253. VALUES('2005-04-21', 5, 4);
  254.  
  255. INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
  256. VALUES('2015-09-28', 3, 3);
  257.  
  258. INSERT INTO Rezerwacja_Studia(data, Studio_Nagraniowe_id, Artysta_id)
  259. VALUES('2001-01-01', 5, 5);
  260.  
  261.  
  262. INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
  263. VALUES(1, 2, 4, 2, 2);
  264.  
  265. INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
  266. VALUES(5, 3, 4, 2, 1);
  267.  
  268. INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
  269. VALUES(1, 2, 3, 2, 5);
  270.  
  271. INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
  272. VALUES(2, 2, 4, 2, 2);
  273.  
  274. INSERT INTO Album_Wydany(Album_id,Dystrybutor_id,Producent_id,Studio_Nagraniowe_id,Mastering_id)
  275. VALUES(4, 3, 4, 2, 5);
  276.  
  277. --1a) Tworzymy widok
  278. --Widok będzie pokazywał artystów, którzy rezerwowali przynajmniej dwa razy studio nagraniowe przed 2013 rokiem.
  279.  
  280. CREATE VIEW ilosc_albumow_artystow
  281. AS
  282. SELECT a.imie, a.nazwisko, COUNT(a.id) AS ilosc_rezerwacji FROM Artysta a
  283. INNER JOIN Rezerwacja_Studia re ON a.id=re.Artysta_id WHERE (extract(year from re.data)<2013)
  284. GROUP BY a.imie, a.nazwisko
  285. HAVING COUNT(a.id)>=2;
  286.  
  287.  
  288. --1b) Sprawdzenie, że widok działa
  289.  
  290. SELECT imie, nazwisko, ilosc_rezerwacji
  291. FROM ilosc_albumow_artystow
  292. ORDER BY ilosc_rezerwacji ASC;
  293.  
  294. --2a) Tworzymy funkcjê 1
  295. --Funkcja sprawdzająca czy dane studio ma wolny termin na rezerwacje po podaniu ID studia oraz daty.
  296.  
  297. CREATE FUNCTION dostepna_rezerwacja(id_studia INTEGER, data DATE) RETURNS VARCHAR(30)
  298. AS
  299. DECLARE
  300. prawda VARCHAR(30);
  301. falsz VARCHAR(30);
  302. BEGIN
  303. prawda:='Rezerwacja jest dostepna';
  304. falsz:='Rezerwacja nie jest dostepna.';
  305.  
  306. IF EXISTS(SELECT data FROM Rezerwacja_Studia WHERE data=$2)
  307. THEN
  308. RETURN falsz;
  309. ELSE
  310. RETURN prawda;
  311. END;
  312. $$ LANGUAGE plpgsql;
  313.  
  314. --2b) Sprawdzenie, ¿e funkcja 1 dzia³a
  315.  
  316. SELECT * FROM dostepna_rezerwacja(5,'2001-01-01');
  317. SELECT * FROM dostepna_rezerwacja(5,'2009-01-01');
  318.  
  319. --3a) Tworzymy funkcjê 2
  320. --Funkcja zwracaj¹ca ³¹czn¹ d³ugoœæ trwania albumów danego artysty.
  321.  
  322. CREATE FUNCTION dlugosc_albumow_artysty (
  323. @id_artysty INT)
  324. RETURNS INT
  325. AS
  326. BEGIN
  327. RETURN (SELECT SUM(dlugosc) AS laczna_dlugosc FROM Album WHERE @id_artysty=Artysta_id);
  328. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement