SQL questions regarding the music database

[frankie54]

Here's my first post! (baby steps...)

I wanted to greet everyone and say that I was very
pleasantly surprised to find such a wealth of information
and lively discussions on this forum; anyway, on to my
newbie rantings...

In an example recently quoted on this forum (music dbase),
and supposing I am using some kind of browser front end to
input the data into a MySQL database (for ex. the song,
label and artist data for a CD), there must be a PHP script
which automatically send the SQL statement that populates
the various tables (artist, song, label, CD and whatever
else) when I click the submit button after typing the
information. --oversimplification--

After entering a few of those CD's, what would the steps
required to find a particular song (and its corresponding
CD, artist, etc...) and display those results back in the
browser?

In other words, after the data is entered into those
various tables, where and how does the database server
maintain the relationship which associates a particular
song with several artists, several CD's....?

Lookup tables which keep each table's primary keys
associated? (artists, songs, CD, and so on)

What would the SQL query statement look like to retrieve
the info on a particular song?

How about the statement needed to automatically update the
lookup table(s) when adding new songs to the database?

Sorry if this topic has been covered before.

Any help appreciated.

FK

[Vinay]

lookup tables?

anyway, you might want a query in the form of:

$query = "SELECT * FROM table WHERE title LIKE '%$searchstring%' OR ARTIST LIKE '%$searchstring%'";


$searchstring if the thing you are searching for... the % signs are wildcards in mySQL ... after that, just loop through resullts and output

$result = mysql_query($query);
while($row = mysql_fetch_Array($result))
{
$artist = $row['artist'];
echo $artist;
// etc
}

[freakysid]

I believe FK is referring to look-up tables in terms of bridging entites in many-to-may realationships; ie, a Song may have a many-to-many relationship to Album (An Album has many Songs and a Song may appear on many albums). So your relational schema will require the tables Song, Album, and the "lookup table" Song_Album whose rows contain foreign keys to SongTitle and Album records.

If I get the gist of what you are asking FK, the way you use these relationships in your queries is through joins. A join between two tables produced a set which is the product of the two sets being joined. For example, if we have table X with the values with the records {X1, X2 X3} and table Y which contains the records {Y1, Y2, Y3} and we join the two tables we get these results:

Table X
-------
X1
X2
X3

and 

Table Y
-------

Y1
Y2
Y3

and we join these two tables we get

Table X . Table Y
---------------
X1   Y1
X1   Y2
X1   Y3
X2   Y1
X2   Y2
X2   Y3
X3   Y1
X3   Y2
X3   Y3

So what's this gotto do with your question?

OK, lets say you want to find the details of all the CDs that contain a certain song and you have the following database schema:

Song
-----
ID
name
length

primary key = ID

Album
------
ID
name
length

primary key = ID

Now the relationship between Song and Album is many-to-many, so you need the bridging entity Song_Album.

Song_Album
-----------
songID
albumID

primary key = songID, albumID

Note that for Song_Album I have specified a "composite" key; that is, for each record the combination of songID and albumID must be unique.

Now, to find the details (Album.name, Album.length) of all albums that a certain song (Song.name) appears on I would write the following query which "joins" the three tables Album, Song and Song_Album. Using php the song name to search for is stored in $songName.

$sql = "
   SELECT Album.name AS albumName, Album.length AS albumLength
   FROM Song, Album, Song_Album
   WHERE Album.ID = albumID
   AND Song.ID = songID
   AND Song.name = '$songName' ";

The "FROM" clause joins the three tables creating a mega "table" which is the product of all three tables (as explained above).

The "WHERE" clause tells the sql server to narrow down the result set to just those records in Album which have a relationship to entries in Song_Album which in turn have a relationship to records in Song which have a name that matches $songName !

Note that in the WHERE clause I use both the table name and column name to identify Song.ID and Album.ID. This is to avoid the ambiguity that arises from both Song and Album having an attribute "ID". If I had ommitted this, when I send the sql string in a query, the sql server would return an error.

Note I haven't considered performance issues in the syntax of my query.

Look through the resent postings in this forum as somewhere I've given some urls to an online tutorial and book on SQL.

[frankie54]

Originally posted by freakysid
I believe FK is referring to look-up tables in terms of bridging entites in many-to-may realationships; ie, a Song may have a many-to-many relationship to Album (An Album has many Songs and a Song may appear on many albums). So your relational schema will require the tables Song, Album, and the "lookup table" Song_Album whose rows contain foreign keys to SongTitle and Album records.

Look through the resent postings in this forum as somewhere I've given some urls to an online tutorial and book on SQL.

Yes, you are correct.

In the case I am asking about, there is also a record label table involved. How does that change the join and select statement?

Also, are the "bridging" tables created on-the-fly, and how complicated and time-consuming is this for the database server? How would one go about making those tables permanent or cached to avoid choking either in processor or
application?

Thanks for taking the time and answer.

FK

[freakysid]

The look-up or "bridging" tables are just regular tables that you need to create and populate with data yourself. Most relational database management systems (RDBMS) allow you to create "referential integrity" rules so that if you update or delete an item in one table, the records in tables which have a foreign keys relating to the record are also updated or deleted. But, MySQL doesn't handle this (yet). So you must write appropriate sql statements to update all tables that may be affected by an update or delete to maintain the referential integrity of your data.

Regarding the music database. Can you post a link to where this is discussed? I remember a discussion involving this example. I think I even contributed to it and managed only to confuse with my post!

[frankie54]

Originally posted by freakysid
The look-up or "bridging" tables are just regular tables that you need to create and populate with data yourself. Most relational database management systems (RDBMS) allow you to create "referential integrity" rules so that if you update or delete an item in one table, the records in tables which have a foreign keys relating to the record are also updated or deleted. But, MySQL doesn't handle this (yet). So you must write appropriate sql statements to update all tables that may be affected by an update or delete to maintain the referential integrity of your data.

Regarding the music database. Can you post a link to where this is discussed? I remember a discussion involving this example. I think I even contributed to it and managed only to confuse with my post!

Hi there,

The original discussion was posted here.

Quite an interesting one, I might add.

Now about your above comment, are you saying that this
would be one of the merits of using PostgresSQL or such
rather than MySQL?

Any chance of hearing anyone's experiences with database
abstraction layers (which from what I understand allow the
author to port their code to different back-end database
servers) in such situations where one system supports
referential integrity and the other doesn't?

Also, where do I find working examples of those "bridging
tables" being automatically created with PHP when the user
is entering new data?

Thanks

FK

[freakysid]

No RDBMS is going to automatically create a table. So you need to create all the tables you need to represent your relational schema. You also need to insert all the records in each table to represent how, say a song might relate to an album. The refrenetial integrity stuff just means that some RDBMS will help maintain the integrity of your data by following certain rules such as "don't allow a record to be deleted if that record is referenced by a foreign key in another table". As MySQL doesn't allow this you have to maintain this logic in your application (say your PHP code) which is OK if you have just one application accessing the database. But if the database is a server to many applications then your data is less secure because you are relying on each application to maintain the relational integrity of the data.

So, the lack of data integrity in MySQL is IMHO one of its weaknesses (at the moment).

Regarding MySQL vs PostegreSQL. Id on't have much experience in either. But I believe MySQL's advantage is in speed - handy for web application where most of the time you are "reading from the database" with SELECT queries and doing less UPDATE, INSERT, DELETE queries.

Regarding the RecordLabel table you alude to. I'll assume that there is a one-to-many relationship between albums and record labels. That is an album can belong to only one record label, but a record label can relate to many albums. In this case, you just need a column in your Album called, say, "label_ID" which is a foreign key to a record in the Record_Label table. You don't need a bridging entity in this case.

Now, lets say we want to modify my previous sql query statement to find include the record label name. Lets assume that table Record_Label looks like this.

Record_Label
------------
ID 
name
location

primary key = ID

So the sql will look like this

$sql = "
   SELECT Album.name AS albumName, Album.length AS albumLength, Record_Label.name AS labelName
   FROM Song, Album, Song_Album, Record_Label
   WHERE label_ID = Record_Label.ID
    AND Album.ID = albumID
   AND Song.ID = songID
   AND Song.name = '$songName' ";

So now you are joining 4 tables. Note, it might be more effiecient to break this up into a couple of seperate queries.

Have you read Kevin Yank's tutorial on PHP and MySQL at www.webmasterbase.com ?I think there is a link to it in the music database thread.

[frankie54]

Originally posted by freakysid

So now you are joining 4 tables. Note, it might be more effiecient to break this up into a couple of seperate queries.

Have you read Kevin Yank's tutorial on PHP and MySQL at www.webmasterbase.com ?I think there is a link to it in the music database thread.

--------------------------------------------------------

Hi again,

Thanks for the detailed answer.
I am in the process of reading the tutorial in question,
which probably will take a while to absorb (108 pages) !!!!

Curious on how I can determine the efficiency of a query?
I seem to remember hearing about some kind of benchmarking tool
you can run on your database server, or this part of the MySQL utilities??

FK

[freakysid]

A simple way to test the time required to run your query is to enter it at the command line in mysql server. As well as receiving your result set you will get the info as to how long it took to process the query.

You may be able to have this info returned to PHP by the query. I dunno. Perhaps others here will know this.

With many RDBMS you can even query the server as to how it plans to execute a query. By looking at its explaination you can tweek your sql and indexing. Again I don't know about how you do this in mysql. Someone else here may chip in and explain.