1 / 25

My CD Database

My CD Database. THE BEST Shoemaker, Ray, Gleisberg. MY CDDB. The CD Database will be used by a person who wants to store information about their CD collection. Contains information about CD’s title Songs Track #’s and Lengths Genre Label and Date the CD was released Artists

gage
Télécharger la présentation

My CD Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. My CD Database THE BEST Shoemaker, Ray, Gleisberg

  2. MY CDDB • The CD Database will be used by a person who wants to store information about their CD collection. • Contains information about • CD’s title • Songs • Track #’s and Lengths • Genre • Label and Date the CD was released • Artists • Groups (The Beatles) • Individual (Paul McCartney) • Main or Supporting

  3. Sample Queries • What CDs have the word “all” in the title? • What are the names of all of the songs on Eminem Show? • What are the names of all rap CDs? • On what songs are The Beatles main artists? • Did Blink 182 release an album in 2001? • What CDs in the database have every track length longer than the average track length? • What is the longest song on Eminem Show? The shortest?

  4. Relational Model • genre(name) • artist(id, name, type) • members(aid, gid) • Foreign Key: aid and gid references id in artist • cd(id, title, releaseyear, genre, aid) • Foreign Key: aid references id in artist • Foreign Key: genre references name in genre • cdtrack(cdid, track_num, musicpiece, length) • Foreign Key: cdid references id in cd • performance(cdid, track_num, aid, type) • Foreign Key: aid references id in artist • Foreign Key: cdid, track_num references cdtrack • cdlabel(cdid, label) • Foreign Key: cdid references id in cd

  5. SQL Schema CREATE TABLE genre ( id SERIAL PRIMARY KEY, name CHARACTER VARYING (20) UNIQUE NOT NULL); CREATE TABLE artist ( id SERIAL PRIMARY KEY, name CHARACTER VARYING (50) NOT NULL, type CHARACTER VARYING (10) DEFAULT 'individual' CHECK (type IN ('individual','group','various')));

  6. SQL Schema CREATE TABLE members ( aid INTEGER, gid INTEGER, PRIMARY KEY (aid, gid), FOREIGN KEY (aid) REFERENCES artist (id) ON DELETE CASCADE, FOREIGN KEY (gid) REFERENCES artist (id) ON DELETE CASCADE); CREATE TABLE cd ( id SERIAL PRIMARY KEY, title CHARACTER VARYING (60) DEFAULT 'none', releaseyear NUMERIC (4) NOT NULL, gid INTEGER NOT NULL, aid INTEGER NOT NULL, FOREIGN KEY (aid) REFERENCES artist (id), FOREIGN KEY (gid) REFERENCES genre (id));

  7. SQL Schema CREATE TABLE cdtrack ( id SERIAL PRIMARY KEY, cdid INTEGER NOT NULL, track_num INTEGER NOT NULL, musicpiece CHARACTER VARYING (100) DEFAULT 'no name', length INTERVAL (0), UNIQUE (cdid,track_num), FOREIGN KEY (cdid) REFERENCES cd (id) ON DELETE CASCADE); CREATE TABLE performance ( tid INTEGER, aid INTEGER, type CHARACTER VARYING (10) DEFAULT 'main' CHECK (type IN ('main', 'supporting')), PRIMARY KEY (tid, aid), FOREIGN KEY (aid) REFERENCES artist (id) ON DELETE CASCADE, FOREIGN KEY (tid) REFERENCES cdtrack (id) ON DELETE CASCADE);

  8. SQL Schema CREATE TABLE cdlabel ( cdid INTEGER, label CHARACTER VARYING (20), PRIMARY KEY (cdid, label), FOREIGN KEY (cdid) REFERENCES cd (id) ON DELETE CASCADE);

  9. User Interface at a Glance • Inserting, modifying, and deleting data.

  10. Searching • How it works • The typed in search string is sent to a php web page • The string along with the options chosen created into an SQL query. • Here is an example of the SQL query string created with • like, and cd options are chosen: • SELECT id, title • FROM cd • WHERE title LIKE ‘%all%’ order by title • This query returns a cd’s id and title.

  11. My CDDB Home Page

  12. Searching • Regular Searching • Searching with EXACTLY only returns exact matches • LIKE returns results that include the entered sub string. • This is done by using the PostgreSQL wild card character %. • Ex. select name from genre where name LIKE ‘%all%’ • The last option is what you are searching.

  13. Searching • After the SQL query is created • Query is run using pg_query() • The results are accessed using pg_fetch_array() • Get the number of rows returned using pg_numrows()

  14. Advanced Search

  15. Advanced Searching • Accomplishing the SQL • All the tables involved are joined. • If a field is entered, then that condition is added to the end of the sql statement. • Example: SELECT c.title FROM cd c, artist a, performance p, cdtrack t WHERE c.id = t.cdid AND p.aid = a.id AND p.tid = t.id AND a.name =‘john lennon’ AND p.type = ‘supporting’;

  16. Simple Searches • What CDs have the Beatles released? • What are the names of all of the songs on Eminem Show? • What are the names of all the rap CDs?

  17. Advanced Searches • What songs are The Beatles main artists? • Did Blink 182 release an album in 2001?

  18. SQL Queries • What CDs in the database have every track length longer than the average track length? • What is the longest song on Eminem Show? The shortest?

More Related