Music share database

1. Music share database

2. Specification It’s a digital music library where users can download and upload music files. We want to differentiate the music files, so we have to store the file’s informations: the size, the type and the bit rate and we also store the other informations, like: title, artist, album, year, genre, track number and duration. We should assign a new unique number for each music file too, this will be the ”identification number”. There aren’t any copies of the files, every file is different. The users have their own informations too. We store their name, adress, e- mail, phone number and birthday. We will give them a unique username, that they can choose. Every user will have different username. We will store their password too, to make the database more safety. It’s a limited library, because if someone never upload anything, she or he can download just few files a day (it’s a static number), but if a user upload files, she or he will get ”credits” (We will store their credits too.) and she or he can download more files a day. (Proportionately with their credits.) There will be some statistics too. We will store the users downloads (and count them), and from this, we can give an information for the users: to tell them the most downloaded file’s informations. The users can vote for their favourite songs, so each song will have their own ”popularity number” (that we will store too). So the users will can easily choose from the most popular musics, if they want.

3. Entity Realtionship diagram

4. Relational model & Functional dependencies & 1.NF, 2.NF, 3.NF, BCNF user(username, user_password, name, e_mail, phone_number, birthday, country, city, street)) F:={username -> user_password, username -> name, e_mail, username -> phone_number, username -> birthday, username -> country, username -> city, username -> street)} 1.NF,2.NF,3.NF,BCNF=>OK {username}+={username, user_password, name, e_mail, phone_number, birthday, country, city, street} downloader(username) uploader(username,credits) F:={username -> credits} 1.NF,2.NF,3.NF,BCNF=>OK {username}+={username, credits} music_file(id_number, title, genre, duration, bit_rate, size, file_type)) F:={id_number -> title, id_number -> genre, id_number -> duration, id_number -> bit_rate, id_number -> size, id_number -> file_type)} 1.NF,2.NF,3.NF,BCNF=>OK {id_number }+={id_number, title, genre, duration, bit_rate, size, file_type }

5. album(name, a_title, year, track_number) F:={name, a_title -> year, name, a_title -> track_number} 1.NF,2.NF,3.NF,BCNF=>OK {name, a_title }+={ name, a_title, year, name, a_title -> track_number} artist(name) upload(id_number) d_download(username, d_number_of_downloads) F:={username -> d_number_of_downloads} 1.NF,2.NF,3.NF,BCNF=>OK {username}+={username, d_number_of_downloads } u_download(username, id_number, u_number_of_downloads) F:={username, id_number -> u_number_of_downloads} 1.NF,2.NF,3.NF,BCNF=>OK {username, id_number }+={username, id_number, u_number_of_downloads } vote(username, id_number, number_of_votes) F:={username, id_number -> number_of_votes} 1.NF,2.NF,3.NF,BCNF=>OK {username, id_number }+={username, id_number, number_of_votes } perform(id_number, name) from(id_number, name, a_title)

6. SQL (Creating tables) An example: CREATE TABLE user( username varchar2(50) not null, user_passworrd varchar2(10) not null, name varchar2(50) not null, e_mail varchar2(50) not null, phone_number number not null, birthday date not null, country varchar2(50) not null, city varchar2(50) not null, street varchar2(50) not null, primary key(username) ); DESC user;

7. SQL (Populating the database) An example: INSERT INTO user( username, user_passworrd, name, e_mail, phone_number, birthday, country, city, street) values('Anna', 'abcdelg', 'Anna Kovács', 'anna@freemail.hu', 6205407774, '84-DEC.-31', 'Hungary', 'Budapest', 'Ady Endre‘ ); SELECT * FROM user;

8. SQL (Queries) An example: Find the users, who live in Budapest! SELECT username FROM user WHERE city='Budapest'; Other examples: What is the averege bitrate of the music files, that are performed by The Beatles? SELECT AVG(bit_rate) FROM music_file JOIN artist ON (artist.name = perform.name) WHERE artist.name=’The Beatles’; Find the artist, who play rock music! SELECT name FROM artist JOIN music_file ON (music_file.id_number = perform.id_number) WHERE music_file.genre=’rock’; Find the yungest user! SELECT name FROM user WHERE rownum = 1 ORDER BY DESC;

9. SQL (Queries) What kind of music styles are in the database? SELECT DISTINCT genre FROM music_file; What is the averege size of the music files, that have 192 Kbps? SELECT AVG(size) FROM music_file WHERE bitrate=192; Find the titles of the pop songs, that are longer than 2 minutes! SELECT title FROM music_file WHERE duration>120 AND genre=’pop’; Where do the the users live, whoose names starts with ’K’? SELECT country, city, street as address FROM user WHERE usename LIKE ’K%’; What kind of music styles are in the database? SELECT DISTINCT genre FROM music_file;

10. SQL (Alter tables, Updates) An example: ALTER TABLE user ADD(job VARCHAR2(50)); DESC user; Other examples: ALTER TABLE artist ADD(birthday DATE); ALTER TABLE music_file ADD(bpm NUMBER); UPDATE music_file SET genre=’drum and bass’ WHERE bpm BETWEEN 140 AND160; UPDATE music_file SET genre=’chill out’ WHERE bpm BETWEEN 90 AND 110;

11. Triggers An examle: CREATE OR REPLACE TRIGGER Print_music_file_changes AFTER DELETE ON music_file FOR EACH ROW DECLARE BEGIN DELETE FROM vote WHERE id_number=:OLD. id_number; DELETE FROM u_download WHERE id_number=:OLD. id_number; DELETE FROM perform WHERE id_number=:OLD. id_number; DELETE FROM from WHERE id_number=:OLD. id_number; END; /

12. Triggers Other examle: CREATE OR REPLACE TRIGGER Print_user_changes AFTER DELETE ON user FOR EACH ROW DECLARE BEGIN DELETE FROM vote WHERE user_id=:OLD.user_id; END; /