Database Languages
eXercise #4
Creating The MUSIC
Database
The MUSIC DATABASE
is used as one of the example databases throughout this section of the
course.
During this exercise,
you will take a set of table definitions and transform them into a working
mSQL database.
The 3 related tables
in the MUSIC database are as follows:
ALBUMS
albname |
artist |
media |
pdate |
recco |
sernum |
varchar(50) |
varchar(50) |
char(2) |
dec(4) |
char(10) |
varchar(50) |
|
|
NOT NULL |
|
|
NOT NULL |
|
|
|
|
|
KEY |
TRACKS
sernum |
track |
disk |
song |
varchar(50) |
dec(2) |
dec(2) |
varchar(60) |
NOT NULL |
NOT NULL |
NOT NULL |
NOT NULL |
KEY |
KEY |
KEY |
|
|
PERFORMERS
sernum |
artist |
instrument |
varchar(50) |
varchar(50) |
varchar(50) |
NOT NULL |
NOT NULL |
NOT NULL |
KEY |
KEY |
KEY |
|
- Using the PUBLIC
Database Portal,
- Using the CREATE
TABLE command, create in turn each of the three tables specified
above.
- Check you have
created the tables correctly by using the table
viewer to check the tables exist.
- You need to 'harvest'
the data necessary to populate each of the tables. The data is supplied
as TEXT (delimeted) for easy importaton by following each of the following
links:Albums | Tracks
| Performers. FOR EACH of the files, save
them to your desktop using their supplied name.
- Populate the
ALBUMS table with some rows, issuing the INSERT INTO command
- Check your ALBUMS
table contains data by issuing the command: SELECT * FROM ALBUMS
- if you do not get any rows, you may need to perform the previous
step again, or seek assistance.
- Now populate
the TRACKS and PERFORMERS tables, checking their populations arrive
safely (by SELECTing from each of the tables systematically)