Transcript My CD Database THE BEST Shoemaker, Ray, Gleisberg

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
• Groups (The Beatles)
• Individual (Paul McCartney)
• Main or Supporting
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?
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
SQL Schema
CREATE TABLE genre (
id
SERIAL
name CHARACTER VARYING (20)
PRIMARY KEY,
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')));
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));
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);
SQL Schema
CREATE TABLE
cdid
label
PRIMARY KEY
FOREIGN KEY
cdlabel (
INTEGER,
CHARACTER VARYING (20),
(cdid, label),
(cdid) REFERENCES cd (id) ON DELETE CASCADE);
User Interface at a Glance
• Inserting, modifying, and deleting data.
Searching
•
How it works
1. The typed in search string is sent to a php web page
2. The string along with the options chosen created into
an SQL query.
3. Here is an example of the SQL query string created
with
4. like, and cd options are chosen:
5.
6.
7.
SELECT id, title
FROM cd
WHERE title LIKE ‘%all%’ order by
title
8. This query returns a cd’s id and title.
My CDDB Home Page
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.
Searching
•
After the SQL query is created
1.
2.
3.
Query is run using pg_query()
The results are accessed using pg_fetch_array()
Get the number of rows returned using pg_numrows()
Advanced Search
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’;
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?
Advanced Searches
• What songs are The Beatles main artists?
• Did Blink 182 release an album in 2001?
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?