Informatyka UJ forum

Cupek

Wrzucam notki, ktore udalo mi sie zapisac. Nie sa kompletne - brakuje bodajze 2-ch cwiczen. Enjoy! :)

///////////////////////////////////////////////////////////////////////////////
// Bazy Danych Ćw. 2 - Maciej Smołka
///////////////////////////////////////////////////////////////////////////////

DDL - Data Definition Language (tworzenie tabel, indeksów, perspektyw, etc.)
CREATE, ALTER, DROP

DML - Data Modification Language (tworzenie, modyfikacja, usuwanie rekordów danych oraz wyszukiwanie)
SELECT, INSERT, UPDATE, DELETE

Typy danych:
- Tekstowe:
- Teksty o stałej długości CHARACTER(n), CHAR(n)
- Teksty o stałej długości NATIONAL CHARACTER(n), NCHAR(n) (trzymają znaki pojedyńczego zestawu znaków z np. UNICODE)
- Teksty o zmiennej długości CHARACTER VARYING(n), VARCHAR(n) [TEŻ MAJĄ WERSJĘ NATIONAL]
- Długie dane tekstowe TEXT
- Liczbowe:
- Całkowite INT, SMALLINT
- Stałoprzecinkowe DECIMAL, NUMERIC(m,n)
- Zmiennoprzecinkowe FLOAT(n), REAL, DOUBLE PRECISION
- Walutowe CURRENCY
- Data i czas DATE, TIME, TIMESTAMP, INTERVAL
- Dane binarne BIT(n), BIT VARYING(n), BLOB, (CLOB - to character large object)

CREATE TABLE nazwa_tabeli
(
nazwa kolumny typ [ograniczenia_kolumnowe],
...
[ograniczenia_tablicowe], ...
);

TWORZYMY PRZYKŁADOWĄ BAZĘ DANYCH

1. Pracownicy( Nr, PESEL, Imie, Nazwisko, DataUrodzenia, DataZatrudnioenia, Zarobki )
2. ZwolnienieChorobowe( NrPracownika, Poczatek, Koniec )

CREATE TABLE Pracownicy
(
Nr INT PRIMARY KEY,
PESEL CHAR(11) UNIQUE,
Imie VARCHAR(20) NOT NULL,
Nazwisko VARCHAR(50) NOT NULL,
DataUrodzenia DATE NOT NULL,
DataZatrudnienia DATE NOT NULL,
Zarobki DECIMAL(10,2),
CHECK( Zarobki > 0 ),
CHECK( DataZatrudnienia > DataUrodzenia )
);

CREATE TABLE ZwolnieniaChorobowe
(
NrPracownika INT NOT NULL REFERENCES Pracownicy( Nr ) [FOREIGN KEY( NrPracownika ) REFERENCES Pracownicy( Nr )], ON DELETE SET NULL, ON UPDATE CASCADE,
Poczatek DATE NOT NULL,
Koniec DATE
PRIMARY KEY(NrPracownika, Poczatek),
UNIQUE( NrPracownika, Koniec )
CHECK( Poczatek < Koniec )
);

ALTER TABLE ZwolnienieChorobowe ADD CONSTRAINT FK_Pracownicy_ZwolnienieChorobowe FOREIGN KEY...
ALTER TABLE ZwolnienieChorobowe ADD COLUMN costam TYP NOT NULL;

CREATE DOMAIN KeyType INT NOT NULL

///////////////////////////////////////////////////////////////////////////////
// Bazy Danych Ćw. 3 - Maciej Smołka
///////////////////////////////////////////////////////////////////////////////

Ogólna postać wyrażenia SELECT:

SELECT [ DISTINCT ] [* | <lista_kolumn> ]
FROM źródło_rekordów
[ WHERE wyrażenie_logiczne ]
[ GROUP BY lista_kolumn [ HAVING wyrażenie logiczne] ]
[ ORDER BY kolumna1 [DESC], ... ]

np.
SELECT * FROM Pracownicy;
SELECT Imie, Nazwisko FROM Pracownicy;
SELECT * FROM Pracownicy WHERE Zarobki > 1000;
SELECT * FROM Pracownicy WHERE Zarobki IS [ NOT ] NULL;
SELECT * FROM Pracownicy WHERE Zarobki > ( SELECT AVG( Zarobki ) FROM Pracownicy );

1. DISTINC - eliminuje powtarzające się rekordy

2. Funkcje agregujące: SUM, AVG, MIN, MAX, COUNT

3. Jeżeli w klauzuli WHERE pojawi się słowo NULL bez IS, albo IS NOT to automatycznie
wartość całego wyrażenia jest nieokreślona.

4. LIKE: '-' - pojedyńczy znak; '%' - dowolny ciąg znaków; [acf] - jeden znak spośród 'a', 'c', 'f'; [a-k] - itd.

5. Spójniki logiczne: AND, OR, NOT, ()

6. ORDER BY [ ASC | DESC ] - sortowanie rosnąca lub malejąco, domyślnie: ASC
można łączyć - np. ORDER BY DataUrodzenia DESC, Nazwisko ASC

7. GROUP BY

Pracownicy( Nr, Imie, Nazwisko, DataUrodzenia, DataZatrudnienia, Zarobki, KodDziału );

1 Jan Kowalski 10-01-1900 02-02-2000 1500 SK
2 Anna Nowak 10-02-1905 01-01-2003 2000 AD
3 Jan Nowak 10-01-1990 01-01-2004 4000 SK

SELECT KodDziału, AVG( Zarobki )
FROM Pracownicy
GROUP BY KodDziału
ORDER BY AVG( Zarobki );

Powyższe zapytanie wypisze wszystkie działy wraz z średnimi zarobkami pracowników do nich należących.

SK 4000
AD 2000

SELECT Kod Działu, AVG( Zarobki )
FROM Pracownicy
WHERE Zarobki > 1500
GROUP BY KodDziału HAVING AVG( Zarobki ) > 2500

SK 4000
AD 2000 <- tego już dzięki HAVING nie zobaczymy

///////////////////////////////////////////////////////////////////////////////
// Bazy Danych Ćw. 4 - Maciej Smołka
///////////////////////////////////////////////////////////////////////////////

1. Łączenie tabel: A, B; AxB= { a1...an, b1...bm }: (a1...an) e A i (b1...bm) e B

2. A [odwr. klepsydra] B - złączenie naturalne:

A B A[kleps.]B
A1 | A2 A2 | A3 A1 | A2 | A3
------- -------- -------------
a | 1 1 | 100 a | 1 | 100
a | 2 1 | 200 a | 1 | 200
b | 3 2 | 300 a | 2 | 300

3. Złaczenia theta ([odw. klepsydra + małe theta jako indeks dolny])
Theta jest predykatem - warunkiem który musi zostać spełniony.
Złączenia theta są bardziej ogólnymi złączeniami naturalnymi.

A [zł. theta] B = { (a1...an,b1...bm) e AxB: [theta] jest spełniony }

np. [theta] = (A.A2 = B.A2)

A [zł. theta] B
A1 | A.A2 | B.A2 | A3
---------------------
a | 1 | 1 | 100
a | 1 | 1 | 200
a | 2 | 2 | 300

np. [theta] = (A.A2 < B.A2)

A [zł. theta] B
A1 | A.A2 | B.A2 | A3
---------------------
a | 2 | 1 | 100
a | 2 | 1 | 200
b | 3 | 1 | 100
b | 3 | 1 | 200
b | 3 | 2 | 300

4. Jak to przenieść do SQL?

a) Iloczyn kartezjański:

SELECT *
FROM A CROSS JOIN B
np. [WHERE A.A2 = B.A2] - ograniczenie

SELECT *
FROM A, B

b) Złączenie naturalne:

SELECT *
FROM A NATURAL JOIN B

C) Złączenie theta:

SELECT *
FROM A JOIN B ON [warunek_złączenia]

np.
SELECT *
FROM A JOIN B ON (A.A2 = B.A2)

SELECT *
FROM A, B
WHERE A.A2 = B.A2

5. Pracownicy(Nr, Imie, Nazwisko), ZwolnieniaChorobowe(Nr_prac, Poczatek, Koniec)

Ranking pracowników najczęściej biorących zwolnienie chorobowe:
SELECT Imie, Nazwisko, COUNT( Nr_prac | Nr | * )
FROM A JOIN B ON Nr = Nr_prac
GROUP BY Nr
ORDER BY COUNT( Nr_prac | Nr | * ) DESC

Złaczenia deklarujemu poprzez A JOIN B ON [złączenie] zamiast A, B WHERE [złączenie]

6. Złączenia zewnętrzne (lewostronne, prawostronne)

To samo co w 5, tylko, że chcemy uwzgl. też tych co nie brali zwolnienia

Pracownicy:
1 Jan Kowalski
2 Anna Nowak
3 Jan Malinowski

Zwolnienia:
1 1-01-2000 1-02-2000
1 31-10-2003 17-11-2003
2 1-01-2007 2-01-2007

1 Jan Kowalski 1 ... ...
1 Jan Kowalski 1 ... ...
2 Ann Nowak 2 ... ...
->3 Jan Malinowski NULL NULL NULL

Zapytanie: A LEFT OUTER JOIN B ON ...
A RIGHT OUTER JOIN B ON ...
A FULL OUTER JOIN B ON ...
A LEFT OUTER NATURAL JOIN B

SELECT Imie, Nazwisko
FROM Pracownicy LEFT OUTER JOIN Zwolnienia
ON Nr = Nr_prac
WHERE Nr_prac IS NULL

7. Łączenie zapytań: UNION - suma mnogościowa, INTERSECT - przecięcie, EXCEPT - różnica

( SELECT Imie, Nazwisko, FROM Pracownicy )
UNION
( SELECT ImieKlienta, NazwiskoKlienta )

Operacje na wielozbiorach: UNION ALL, INTERSECT ALL, EXCEPT ALL
np. A= { a, a, b, b, b }, B = { a, c, c }
A UNION ALL B = { a, a, a, b, b, b, c, c }
A INTERSECT ALL B = { a }
A EXCEPT B = { a, b, b, b }

8. Operatory:

a) IN( zbiór ) - sprawdza czy w zbiorze
SELECT Imie, Nazwisko
FROM Pracownicy
WHERE Nazwisko IN ( SELECT nazwisko FROM klienci )

b) EXISTS( zbiór ) - sprawdza czy zbiór nie-pusty

c) x > ALL( zbiór )
x > ANY( zbiór ) | SOME( zbiór )

np.
SELECT Imie, Nazwisko
FROM Pracownicy
WHERE Zarobki >= ALL( SELECT Zarobki FROM Pracownicy )
WHERE Zarobki = ( SELECT Max( Zarobki ) FROM Pracownicy )

d) Wypisz pary pracowników, którzy zarabiają tyle samo

SELECT P1.Imie, P1.Nazwisko, P2.Imie, P2.Nazwisko, P1.Zarobki
FROM Pracownicy AS P1 JOIN Pracownicy AS P2 ON P1.Zarobki = P2.Zarobki
WHERE P1.Nr < P2.Nr

SELECT P1.Nr, P2.Nr
FROM Pracownicy AS P1, Pracownicy AS P2
WHERE P1.Zarobki = P2.Zarobki AND P1.Nr < P2.Nr

///////////////////////////////////////////////////////////////////////////////
// Bazy Danych Ćw. 5 - Maciej Smołka
///////////////////////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////////////////////
// Bazy Danych Ćw. 6 - Maciej Smołka
///////////////////////////////////////////////////////////////////////////////

[link widoczny dla zalogowanych]

SELECT RR.battle FROM Results AS RR FROM (SELECT MAX( X.ILE ) FROM (SELECT
R.Country, COUNT( R.ship ) AS ILE FROM Results AS R JOIN Ships AS S ON R.ship =
S.name JOIN ShipClasses AS SC ON SC.class = s.class WHERE R.battle = br.battle
GROUP BY sc.country) AS X) > 2))

Równoważne:
SELECT DISTINCT R.battle
FROM Results AS R JOIN Ships AS S
ON R.ship = S.name
JOIN ShipClasses AS SC ON SC.class = s.class

GROUP BY R.battle, SC.country
HAVING COUNT(*) >= 3;

6. SELECT S.name
FROM Ships AS S
JOIN ShipClasses AS SC ON SC.CLASS = S.Class
WHERE cannonCount =
( SELECT MAX( CannonCount )
FROM ShipClasses AS SCZ
WHERE SCZ.diameter = SC.diameter )

7. SELECT Shipclasses.class, MIN( ships.launched ) FROM Ships, ShipClasses
WHERE Ships.class = ShipClasses.class
GROUP BY Shipclasses.class

8. SELECT Ships.class, COUNT( Ships.name )
FROM Ships JOIN Results ON Ships.name = Results.ship
WHERE Results.result = 'sunk'
GROUP BY Ships.class

9. SELECT Ships.class, COUNT( Ships.name )
FROM Ships JOIN Results ON Ships.name = Results.ship
WHERE Results.result = 'sunk' AND Ships.class IN
( SELECT class FROM Ships
GROUP BY class
HAVING COUNT(*) >= 3 )
GROUP BY Ships.class

10. DELETE FROM Ships
WHERE name IN
( SELECT ship FROM Results
WHERE result = 'sunk' )

Implementacja struktur drzewiastych:

CREATE TABLE Zarobki
(
id INT PRIMARY KEY,
salary DECIMAL(10,2),
parent INT
)

Normalizacja:

CREATE TABLE Struktura
(
id INT PRIMARY KEY,
parent INT REFERENCES Struktura( id )
)

CREATE TABLE Info
(
id INT PRIMARY KEY,
salary DECIMAL(10,2) NOT NULL
)

1. Wybór rodzica:
SELECT parent_id
FROM Struktura
WHERE id = 4

2. Wybór dzieci:
SELECT id
FROM Struktura
WHERE parent_id = 4

3. Wybór korzenia:
SELECT id
FROM Struktura
WHERE parent_id IS NULL

4. to samo?
SELECT id
FROM Struktura
WHERE id NOT IN
( SELECT parent_id
FROM Struktura )

5. Wybór szefa szefa:
SELECT parent_id
FROM Struktura
WHERE id IN
( SELECT parent_id
FROM Struktura
WHERE id = 8 )

6. 1( 1, )
/ \
2(2, 3) 3(4, )
/ | \
4(5,6) 5(7,12) 6(15,16)
/ \
7(8,9) 8(10, 11)

Tworząc drzewo idziemy od lewej strony i po kolei wypełniamy pary.

///////////////////////////////////////////////////////////////////////////////
// Bazy Danych Ćw. 7 - Maciej Smołka
///////////////////////////////////////////////////////////////////////////////

1. Drzewo jak powyzej (ćw. 6):

CREATE TABLE Struktura
(
id INT PRIMARY KEY,
l INT NOT NULL,
r INT NOT NULL
)

CREATE TABLE Info
(
id INT PRIMARY KEY REFERENCES Struktura(id),
zarobki DECIMAL
)

Dane do info:
1 - 1000
2 - 700
3 - 800
4 - 700
5 - 700
6 - 600
7 - 500
8 - 500

SELECT id FROM Struktura
WHERE l(r) > 4 AND r(l) < 15
^
|
SELECT L FROM Struktura
WHERE id=3

SELECT S1.id, S2.id
FROM Struktura AS S1 JOIN Struktura AS S2
ON S1.l > S2.l
AND S1.r < S2.r

SELECT S1.id, SUM(Info.zarobki) FROM
Struktura AS S1 JOIN Struktura AS S2 <- tabele przodków i potomków
ON S1.l =< S2.l
AND S1.r => S2.r
JOIN INFO ON S2.id = Info.id
GROUP BY S1.id

Tyle narazie. Aktuala wersja bedzie tu: [link widoczny dla zalogowanych] Ale tak naprawde nie zamierzam jej aktualizować, bo juz mi sie kurwa nie chce pisac tego gowna. :D Ksiazki sa od tego.

urban

Moze mi ktos powiedziec co bylo dzis czyli 24 kwietnia na cwiczeniach z bd. Tak w punktach. Z gory dzieki. CHodzi mi o grupe o 8:30 u dr Smolki.

dzendras

1. Triggery
2. UML jakiś?

Yoter

w gr. o 11. były triggery a UML to tak a propos dokumentacji projektu czyli wykresy z encjami (entity) i z tabelkami (table) (odpowiednio model koncepcyjny i fizyczny, czy jakoś tak)

Madras

Eeej a na kolosie obowiązują też te postacie Boysa-Coda, normalne itd.?

Spectro

Nie, a przynajmniej nic mi o tym nie wiadomo.

Sobek

Ma ktoś rozpisane zapytania SQL-owe do tych zadan o statkach, które Smołka umiescil na swojej witrynie?

Byłbym baaaardzo wdzięczny

Spectro

W notatkach Cupka są do zadania 4. (na 2 sposoby) oraz 6. - 10. (patrz ćw.6 z dr Smołką).

1)
(SELECT name FROM Ships) UNION (SELECT ship FROM Results)

2)
(SELECT country FROM ShipClasses WHERE type = 'dreadnought')
INTERSECT
(SELECT country FROM ShipClasses WHERE type = 'battlecruiser')

3)
SELECT r1.ship FROM Results AS r1 JOIN Results AS r2
ON r1.ship = r2.ship
WHERE r1.result = 'damaged' AND
(SELECT date FROM Battles WHERE name = r1.battle)
<
(SELECT date FROM Battles WHERE name = r2.battle)

A to moje wczorajsze rozwiązanie (na 2 selektach):

SELECT r1.ship FROM Results AS r1 JOIN Battles AS b1
ON r1.battle = b1.name
WHERE r1.result = 'damaged' AND b1.date <
ANY (SELECT b2.date FROM Results AS r2 JOIN Battles AS b2
ON r2.battle = b2.name
WHERE r1.ship = r2.ship)

edit: Poprawiłem literówki w moim powyższym zapytaniu.

5)
SELECT DISTINCT Ship.class FROM Ships, Results
WHERE (Results.ship = Ships.name) AND (Results.result = 'sunk')

Sobek

Dzięki :)