Bazy Danych II

Projekt

Temat: „Katalog albumów muzycznych”

Prowadzący: Przygotował:

mgr Zofia Kostrzewska Marcin Woźniak

Informatyka gr II rok III

1. Cel projektu:

Celem niniejszego projektu jest stworzenie bazy danych w której możliwe będzie skatalogowanie albumów muzycznych zarówno w domowej płytotece jak również dla potrzeb ZAIKS-u.

2. Opis projektu:

Projekt może służyć jako domowy katalog przechowujący informacje o naszych płytach CD, kasetach czy płytach winylowych (oraz równie dobrze o naszych utworach mp3), oraz może zostać wykorzystany do skatalogowania wszystkich wydawnictw na rynku muzycznym. Ponieważ wszelkie wydawane albumy są objęta prawami autorskimi ważne jest by tytuły utworów, czy nawet nazwy nowych zespołów nie pokrywały się z już istniejącymi. Projekt ten umożliwia łatwe dotarci do skatalogowanych wydawnictw i sprawdzenie czy nowe wydawnictwo nie narusza praw autorskich już istniejącego. Dzięki niemu łatwo i szybko możliwe jest odnalezienie np. szukanego utworu wśród setek a nawet często tysięcy utworów w naszej płytotece. Mamy dostęp do roku wydania albumu z szukanym utworem, do nazwy zespołu, roku jego założenia czy stylu muzycznego który zespół wykonuje. Wyszukiwanie można prowadzić wg wszystkich zawartych w projekcie kryteriów czyli: wg stylu muzycznego, wg zespołu, wg daty wydania, wg albumu czy nawet wg czasu trwania utworu. Zadając stosunkowo proste zapytania mamy dostęp do wszystkich tych informacji. Nasza baza - projekt składa się z pięciu tabel :

2.1. Style_muzyczne - tabela charakteryzująca rodzaj muzyki wykonywany przez zespół, pozwala na skatalogowanie utworów i zespołów wg wspólnych cech charakterystycznych dla nich jak np. rytm, tonacja, czy rify gitarowe. Te właśnie cechy jak i wiele innych decydują do jakiego stylu można zaliczyć zespół.

2.2. Zespoły - przechowuje liste zespolów, daty ich powstania oraz style do których się je zalicza

2.3. Typ_wydania - określa w jaki sposób został album wydany (kaseta MC, plyta CD lub winyl)

2.4. Płyty - zawiera ID zespołu do którego nalezy dany album oraz dane dotyczące daty wydania, stylu muzyucznego oraz tytułu albumu.

2.5. Utwory - Przechowuje wszystkie utwory w naszej bazie wraz z danymi odnośnie ID plyty z której utwór pochodzi, czasu trwania utworu, tytulu utworu, jego pozycji na plycie, oraz ID zespolu wykonującego dany utwór.

W niniejszym projekcie w celu ulatwienia urzytkowania go zostały utworzone funkcje dzieki którym łatwiejsze jest dodawanie rekordów do tabel.

3. Diagram

4. Tabele:

4.1. Style_muzyczne

Style_muzyczne

Id_stylu :number (2) Primary Key, Opis :varchar2 (20) not null;

Tworzenie tabeli:

CREATE TABLE Style_muzyczne (

Id_stylu number (2) CONSTRAINT pk_id_s PRIMARY KEY,

Opis varchar2 (20) not null);

Wypełnianie tebeli:

INSERT INTO Style_muzyczne VALUES (SStyle_muzyczne.nextval,'Rock_metal');

Lub za pomoca funkcji add_styl

EXEC add_styl('rock');

Zapytanie:

SELECT * FROM Style_muzyczne;

4.2. Zespoly

Zespoly

Id_zesp :number (4) Primary Key, Nazwa :varchar2 (30) not null, Data_powstania : Date Id_stylu :number(2) Foreign Key (Style_muzyczne) ;

Tworzenie tabeli:

CREATE TABLE Zespoly (

Id_zesp number (4) CONSTRAINT pk_id_z PRIMARY KEY,

Nazwa varchar2 (30) not null,

Data_powstania Date,

Id_stylu number(2) constraint fk_id_s references Style_muzyczne(id_stylu)

);

Wypełnianie tebeli:

INSERT INTO Zespoly VALUES (SZespoly.nextval,'METALLICA','1979/03/17',1);

Lub za pomocą procedury:

exec add_zespol ('METALLICA','1979/03/17',1);INSERT INTO Style_muzyczne VALUES (SStyle_muzyczne.nextval,'Rock_metal');

Lub za pomoca funkcji add_styl

EXEC add_styl('rock');

Zapytanie:

SELECT * FROM Zespoly;

4.3. Plyty

Plyty

Id_plyty :number (5) Primary Key, tytul :varchar2 (20) not null, Id_zesp :number (4) Foreign Key (Zespoly) not null, Data_wyd : Date, Id_typu :number (2) Foreign Key (Typ_wydania)

Tworzenie tabeli:

Create table Plyty(

id_plyty number (5) constraint pk_id_p primary key,

tytul varchar2(20) not null,

id_zespolu number(4) constraint fk_id_z references Zespoly(Id_zesp) not null,

Rok_wyd number (4),

id_typu number(2) constraint fk_id_ty references Typ_wydania(id_typu));

Wypełnianie tebeli:

INSERT INTO Plyty VALUES (SPlyty.nextval,'Master of Pupets',1,1982,2 );

Exec add_plyty('Master of Pupets',1,1982,2 );

Zapytanie:

SELECT * FROM Plyty;

4.4. Typ_wydania

Typ_wydania

Id_typu :number (2) Primary Key, Opis :varchar2 (30) not null, Side: :char

Tworzenie tabeli:

Create table Typ_wydania

(id_typu number(2) constraint pk_id_t Primary key,

opis varchar2 (30) not null,

Wypełnianie tebeli:

INSERT INTO Typ_wydania VALUES (STyp_wydania.nextval,'Płyta CD',1);

exec add_typ ('Płyta CD',1);

4.5. Utwory

Utwory

Id_utworu :number (6) Primary Key Id_plyty :number (5) Foreign Key (Plyty), Id_zsep :number (4) Foreign Key (Zespoly) not null, Dlugosc :number(4,2) check Dlugosc >0 not null, Lp :number (2), Side :char Tytul :varchar2 (20) not null;

Tworzenie tabeli:

Create table Utwory(

id_utworu number(6) constraint pk_id_u Primary Key,

id_plyty number(5) constraint fk_id_pl references Plyty(id_plyty),

id_zesp number(4) constraint fk_id_ze references Zespoly(id_zesp),

dlugosc number(8,2) constraint dl check (dlugosc >0),

lp number(2),

side char constraint sid check (side between 1 and 2),

Tytul varchar2(20) not null);

Wypełnianie tebeli:

INSERT INTO Utwory VALUES (SUtwory.nextval, 3,1,150,3,1,'Tytul3');

Zapytanie:

SELECT * FROM Utwory;

5. Tworzenie sekwencji:

1. Tworzenie sekwencji do tabeli Style_muzyczne

CREATE SEQUENCE SStyle_muzyczne INCREMENT BY 1 START WITH 1;

2. Tworzenie sekwencji do tabeli Zespoly

CREATE SEQUENCE S Zespoly INCREMENT BY 1 START WITH 1;

3. Tworzenie sekwencji do tabeli Typ_wydania

CREATE SEQUENCE S Typ_wydania INCREMENT BY 1 START WITH 1;

4. Tworzenie sekwencji do tabeli Plyty

CREATE SEQUENCE S Plyty INCREMENT BY 1 START WITH 1;

5. Tworzenie sekwencji do tabeli Utwory

CREATE SEQUENCE S Utwory INCREMENT BY 1 START WITH 1;

6. Tworzenie perspektyw

CREATE OR REPLACE VIEW P_plyty

As

select id_plyty, sum (dlugosc) czas from utwory group by id_plyty ;

7. Tworzenie funkcji

1. Funkcja dodająca rekord do tabeli style muzyczne

Create or replace procedure add_styl (nazwa varchar2)

is

robo varchar2(20);

begin

robo := nazwa;

INSERT INTO Style_muzyczne VALUES

(SStyle_muzyczne.nextval,robo);

end;

2. Funkcja dodająca nowy zespół do tabeli Zespol

Create or replace procedure add_zespol (nazwa varchar2,data Date, id char)

is

my_nazwa varchar2(30);

my_data Date;

my_styl number(2);

begin

my_nazwa := nazwa;

my_data := data;

my_styl :=id;

INSERT INTO Zespoly VALUES (SZespoly.nextval,my_nazwa,my_data,my_styl);

end;

3. Funkcja dodająca nowy zespół do tabeli typ_wydania.

Create or replace procedure add_typ (opis varchar2, side char)

is

my_opis varchar2(30);

my_side char;

begin

my_opis := opis;

my_side := side;

INSERT INTO Typ_wydania VALUES (STyp_wydania.nextval,my_opis,my_side);

end;

4. Funkcja dodająca nowy zespół do tabeli Plyty;

Create or replace procedure add_plyty (nazwa varchar2, id_zesp number,rok number, typ number)

is

my_nazwa varchar2(20);

my_id number(4);

my_rok number(4);

my_typ number (2);

begin

my_nazwa := nazwa;

my_id := id_zesp;

my_rok := rok;

my_typ := typ;

INSERT INTO Plyty VALUES (SPlyty.nextval,my_nazwa,my_id,my_rok,my_typ);

end;

5. Funkcja dodająca nowy zespół do tabeli Utwory;

Create or replace procedure add_utwory

( Id_p number,id_z number, dl number, lp number, side char, tyt varchar2 )

is

my_id_utworu number(6);

my_id_plyty number(5);

my_id_zesp number(4);

My_dlugosc number(4);

my_lp number(2);

my_side char;

my_Tytul varchar2(20) ;

begin

my_id_plyty := Id_p;

my_id_zesp := id_z;

My_dlugosc := dl;

my_lp := lp;

my_side := side;

my_Tytul := tyt;

INSERT INTO Utwory VALUES (SUtwory.nextval,my_id_plyty,my_id_zesp,My_dlugosc,my_lp,my_side,my_tytul);

end;

8. Przykladowe zapytania :

1. Wyszukiwanie plyty po tytule i wyswietlenie jej zawartosci :

Select * from utwory join plyty on plyty.id_plyty = utwory.id_plyty where plyty.Tytul='&plyta';

2. Wyszukanie utworu wg pierwszych liter tytulu :

select z.nazwa, p.tytul, u.tytul, u.dlugosc czas , u.lp from zespoly z, utwory u, plyty p where u.Id_zesp=z.id_zesp and p.id_plyty= u.id_plyty and u.tytul like '&tytul%';

3. Podaj jakie płyty powstały w danym przedziale czasu

select tytul from plyty where rok_wyd > &min and rok_wyd <&max;

8.4. Jakie plyty danego zespolu znajduja sie w bazie :

select plyty.tytul, plyty.rok_wyd from plyty where id_zespolu = (select id_zesp from zespoly where nazwa = '&nazwa');

8.5. Jaka jest najstarsza/najmlodsza płyta w bazie

select plyty.tytul plyta, zespoly.nazwa zespol, plyty.rok_wyd from plyty join zespoly on plyty.id_zespolu = zespoly.id_zesp where rok_wyd = (select min(rok_wyd) from plyty);

select plyty.tytul plyta, zespoly.nazwa zespol, plyty.rok_wyd from plyty join zespoly on plyty.id_zespolu = zespoly.id_zesp where rok_wyd = (select max(rok_wyd) from plyty);

1. Pokaz czasy trwania plyt :

select plyty.tytul, p_plyty.czas from plyty natural join P_plyty ;

Zespoly

Id_zesp

Nazwa Data_powstania : Id_stylu

Style_muzyczne

Id_stylu,

opis

Typ_wydania

Id_typu,

Opis,

side

Plyty

Id_plyty,

Tytul,

Id_zesp,

Data_wyd,

Id_typu

Utwory

Id_utworu

Id_plyty

Id_zsep

Dlugosc

Lp Side Tytul

---