Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    5

    Question Unanswered: database HELP PLEASE

    I would like to design a database to store information about my Cd collections.

    using Primary keys and Foreign keys NOT NUll fields and all relationships in the database

    CD. ARTIST, YEAR, PRICE, SONGS, ALBUM

    I am new at this and really need help can anyone help me please
    i just can not seem to understand the relationship part of this.

    Thank You
    Denise

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Hi Denise,

    Very briefly, relationships are usually pointers that associate records in different database tables.

    Your CD database could have a tables for "Artists" and "Songs".

    The "Artists" table could contain records with artist/group name and some arbitrary key. I.E. "Beethoven", 123.

    The "Songs" table could contains records with titles and the key to the "Artists" table. I.E. "Symphony Number 5", 123.

    The "123" in the "Songs" table points to the "123" in the "Artists" table.

    You would have a "one to many" relationship between the "Artists" table and the "Songs" table.

    The design of your database is usually dictated by how the data will be accessed. Will you be searching for "Songs" by "Artists" or "Songs" withing "Albums", etc.?

    If the volume of data is large or the response time is critical, the database designs become more complicated. There are many good books on the subject, if you really get into this, check one out.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Hi Denise,

    Crash course in Database Design.

    The "CD" table should have a foreign key column for the primary key from "Labels".

    The "CD" table should have a foreign key column for the primary key from "Artists".

    The "Songs" table should have a foreign key column for the primary key from "CD".

    The "Songs" table should have a foreign key column for the primary key from "Artists".

    I cannot answer the question about MySQL...I don't use it. Most of the database packages are "point and click" so you should be able to add the relationships to the existing tables.

    WARNING...only put in a couple of test records in each table until you get it working...this will prevent you losing your data during the design/redo/design/redo phase of your project.

    Here are some sample SQL commands to search your database. Again, I cannot tell you what the format would be for MySQL. But they should be similar.

    To select all CDs after a date:

    Select * from CD Where <fieldname> >= mm/dd/yyyy

    <fieldname> is your date field and mm/dd/yyyy is your date.

    It's been a long time since I did a three table query(CD&Song&Artist). It should be a "Union" or "Join", but I don't have one available to get the syntax for you. Sorry.

    To total the cost of your CDs:

    Select Sum(<fieldname>) from CD

    <fieldname> is your cost field.

    Good Luck,
    Bruce Baasch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •