SQL Schema

Here are the SQL commands to create the tables in my database. Notice that there are a lot of referential-intergrity checks because there are many relationships between the different tables. I found it very easy to be left with dangling tuples until I added these constraints.

CREATE TABLE Album (
         groupName VARCHAR(255),
         albumName VARCHAR(255),
         year DATE,
         numTracks INTEGER CHECK (numTracks > 0),
         recordingStudio VARCHAR(255),
         PRIMARY KEY (groupName, albumName)
);

CREATE TABLE Artist (
         name VARCHAR(255) PRIMARY KEY,
         genre VARCHAR(255) NOT NULL,
         homepage VARCHAR(255) CHECK (homepage LIKE 'http://%'),
         hometown VARCHAR(255),
         bio VARCHAR(2000)
);

CREATE TABLE Musician (
         name VARCHAR(255) PRIMARY KEY,
         instrument VARCHAR(255) NOT NULL,
         hometown VARCHAR(255)
);

CREATE TABLE RecordCompany (
         name VARCHAR(255) PRIMARY KEY,
         address1 VARCHAR(255),
         address2 VARCHAR(255),
         city VARCHAR(255),
         state CHAR(2),
         zipCode CHAR(10),
         homepage VARCHAR(255) CHECK (homepage LIKE 'http://%'),
         phone VARCHAR(14),
         CHECK(address1 IS NOT NULL AND address1 <> address2)
);

CREATE TABLE Song (
         groupName VARCHAR(255),
         songName VARCHAR(255),
         length INT CHECK (length <= 4440),
         trackNo INT,
         PRIMARY KEY (groupName, songName),
         CHECK (trackNo > 0 AND trackNo < 4400 - length)
);

CREATE TABLE Transcriber (
name VARCHAR(255) PRIMARY KEY,
email VARCHAR(255) UNIQUE CHECK (email LIKE '%@%')
);

CREATE TABLE Tablature (
         url VARCHAR(255) PRIMARY KEY CHECK (url LIKE 'ftp://%'),
         transcriber VARCHAR(255),
         modified DATE
);

CREATE TABLE GuestArtist (
         songName VARCHAR(255),
         groupName VARCHAR(255),
         musicianName VARCHAR(255)
);

CREATE TABLE InGroup (
         groupName VARCHAR(255),
         musicianName VARCHAR(255),
         PRIMARY KEY (groupName, musicianName)
);

CREATE TABLE Influences (
musicianName VARCHAR(255),
groupName VARCHAR(255) PRIMARY KEY
);

CREATE TABLE OnAlbum (
         groupName VARCHAR(255),
         albumName VARCHAR(255),
         songName VARCHAR(255),
         PRIMARY KEY (groupName, songName)
);

CREATE TABLE ProducedBy (
         companyName VARCHAR(255),
         groupName VARCHAR(255),
         albumName VARCHAR(255),
         PRIMARY KEY (groupName, albumName)
);

CREATE TABLE TabFor (
         url VARCHAR(255) PRIMARY KEY,
         groupName VARCHAR(255),
         songName VARCHAR(255)
);

Author: Steven van Loben Sels - June 2000