Creating A Cheesy Database

After reviewing my previous posts I thought that it would be helpful if all of my examples (1) worked and (2) worked together. So without further adieu, here are a few SQL scripts to get a basic database up and running. Once you’ve run these scripts you will be able to run the scripts found at my previous post on SQL Joins.

First we’ll create the database…

Example 1a

1

CREATE DATABASE CheesyDB

…then we’ll create the Odors table…

Example 1b

1
2
3
4
5
6

CREATE TABLE Odors
	(
	odorId int NOT NULL,
	name varchar(50) NOT NULL,
	PRIMARY KEY (odorId)
	)

…lets also create a “Cheeses” table…

Example 1c

1
2
3
4
5
6
7
8

CREATE TABLE Cheeses
	(
	cheeseId int NOT NULL,
	name varchar(50) NOT NULL,
	odorId int,
	PRIMARY KEY (cheeseId),
	CONSTRAINT fk_OdorsCheeses FOREIGN KEY (odorId) REFERENCES Odors(odorId)
	)

You should now have a new database named “CheesyDB” with two tables named “Cheeses” and “Odors”. We should also have a foreign key constraint named fk_OdorsCheeses that establishes a one-to-many relationship between Cheeses and Odors. Lets add a few more tables…

Example 1d

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TABLE Flavors
	(
	flavorId int NOT NULL,
	name varchar(50) NOT NULL,
	PRIMARY KEY (flavorId)
	)
 
CREATE TABLE CheesesToFlavors
(
	cheeseId int NOT NULL,
	flavorId int NOT NULL,
 
	CONSTRAINT [PK_CheeseToFlavor] PRIMARY KEY CLUSTERED
	(
		cheeseId,
		flavorId
	),
	CONSTRAINT fk_Cheeses_CheesesToFlavors FOREIGN KEY (cheeseId)
		REFERENCES Cheeses(cheeseId),
	CONSTRAINT fk_Flavors_CheesesToFlavors FOREIGN KEY (flavorId)
		REFERENCES Flavors(flavorId)
)

Now we have a new table “Flavors” and an intermediary table named “CheesesToFlavors” for tracking our many-to-many relationship between Cheeses and Flavors. The code on lines 13-17 makes the data from both the cheeseId and flavorId columns the primary key, and the code on lines 18-21 defines the foreign key constraints of the table.

I’ll be referencing this database in a series of related SQL and ColdFusion posts over the coming weeks. Cheers!