CSC 150 Database Applications and Design Using SQL

Spring 2010

Design Process, Step-by-Step
Connecticut Community Colleges Gateway Community College Student Info System Contact Instructor

Home

Assignments

Syllabus

 The following SQL example shows how two tables can be joined in a SQL statement. Both tables must contain a field with the same name. Keep this in mind when designing the tables for your term projects. 

select employee_id,
       first_name,
       last_name,
       department_name
from l_employee, l_departments
where l_employee.dept_code = l_departments.dept_code;

You do not have to identify the table from which a column came from if the column is unique to only one table. You do have to identify which table a column is from if the column exists in more than one table.

To identify which table the column is in, use the following format:

table_name.column_name

First thoughts on a database to store recipes.

Recipes consist of 3 pieces of data

Recipe_Name
Ingredients
Directions

Since I may have more than one recipe with the same name, I cannot use that field as my primary key. I could use an automatic row counter for my primary key, but I would rather not do that, so I decide to create a Recipe_ID field with two alpha characters (loosely based on what type of recipe) followed by four numerals, fixed length of 6, that I will use as my primary key.

I think that variable length field of 30 characters will be enough to hold the names of my recipes. If I find that it is too short, I can always make the field longer later. (You can always make a field longer at a later date if you need to with no loss of data. You cannot shorten the length of field without the risk of truncating the data already in your table.)

Since recipe directions can be very long, I will use the memo datatype for that field.

My problem is now the ingredients. I know that all recipes will have more than one ingredient, but some recipes have very few ingredients and some recipes have a lot of ingredients. I also know that the rules of Normalization of databases state that I shouldn't have multiple columns/fields holding the same information. So, I decide that I will put my ingredients into a separate table. I can identify which recipe the ingredients go with by including the Recipe_ID field in my table. And since an ingredient is only used once in each recipe, I can use the combination of the Recipe_ID and the Ingredient as my primary key.

My tables so far. Bold names indicate my primary keys.

Recipe Table

Recipe_ID char(6)
Recipe_Name varchar(30)
Directions memo

Ingredients Table

Recipe_ID char(6)
Ingredient ??

The Ingredients Table

As I start thinking about how I want to store the ingredients for my recipes and how I will want to use this database, I realized that I will have to split the Ingredient field up. First, ingredients consist not only of the actual thing like chicken or cauliflower, there is a quantity involved as well, i.e. 1 tsp, 1 bunch, 1 lb, and so on. As Bill mentioned in class, this quantity involves a unit of measurement, tablespoon, cup, or pound. So, I will split the ingredient field into several fields: one for the actual ingredient, one for the quantity of that ingredient (which must be decimal as some ingredients are 1/2 or 1/4), and one for the unit of measure for that ingredient.

I also want to be able to search my recipes by the ingredients in case I find a sale at the store on lamb chops or broccoli. By having the actual ingredient in a field all by itself, it will be easy to search for a specific ingredient.

I take a quick look at some of my recipes and I realize that in many recipes, the ingredients need some kind of pre-preparation such as "wash and dry" or "core and peel" or "mince". I decide that I should include a field for this information as well. I know that sometimes this field and the Unit_Measurement field will be null.

Here's what I decide the Ingredients table should look like. I think my fields are long enough, but if not, I can always add more length later with no loss to my data.

Ingredients Table

Recipe_ID char(6)
Ingredient varchar(30)
Qty Decimal
Unit_Measurement varchar(15)
Pre_Prep varchar(30)

Dates?

I know for the project, I should have a date field somewhere in my database if that is at all possible. I also can be forgetful about names of files and I use the "order by date" option for my regular computer files to help me find things. So, I decide that I will add a Date_Entered field to my Recipe database. The most logical place to add it is to my Recipes table. So now that table will look like this:

Recipe Table

Recipe_ID char(6)
Recipe_Name varchar(30)
Directions memo
Date_Added datetime

More thoughts on how the Recipes database will be used.

I'm happy so far with the design of my database, but I needed to spend some more time thinking about how I actually want to use the data. The way I have my design right now, I can search for recipes by recipe name, by an ingredient, and by the date I entered it into the database. But recipes also fall into categories such breakfast, appetizers, soup, main course, and so on. I'd like to be able to search my recipes by those categories as well. To help maintain the integrity of my data and to reduce redundancy, I decide to create another table for the database with a Cat_Code to identify my recipe categories and the Category_Name holding the actually names of the categories. The design for this table is below:

Categories Table

Cat_Code char(3)
Category_Name varchar(20)

Now, I have to figure out to link the categories with my recipes. I could add the appropriate Cat_Code to the recipes table as another field, but the more I would really like to be able to put one recipe into more than one category. If I try to add several Cat_Codes columns to the Recipes table, I run into the same problem I initially had with the ingredients; how many columns should I have, how will I write queries to find the categories, and so on. And if I add several category columns, my database would by normalized any more. So, I decide the best thing to do is to create another table containing the Recipe_ID and the appropriate Cat_Code. I can have several entries for each recipe bit since there will only be one entry for and Recipe_ID Cat_Code pair, I can use these fields as my primary key. Here's what this table that will link the recipes to their categories looks like:

RecipeCategories Table

Recipe_ID char(6)
Cat_Code char(3)

And....

I really want this database to be as useful to me as it can possible be, and I decide that there are three more piece of information that I would like to keep for all my recipes. First, I want to be able to search by cooking method such as baking, roasting and grilling. I will set up a table like my categories table so I can maintain the integrity of this field. And since each recipe contains only one cooking method, I can just add the Method_Code to my Recipes table.

I want to know the source of my recipes, things like FoodTV or Granma's recipe box. This is exactly like the cooking method, one source for each recipes, so I'm going to do the exact same thing for sources as I did for cooking methods.

Finally, there are times I want to cook something uncomplicated. I decide that I'd like to know how many ingredients a recipe contains. This can just be a number field in the Recipes table.

So here are the two new tables I need for cooking methods and recipes sources as well as my updated version of my Recipes table.

CookingMethods Table

Method_Code char(3)
Method varchar(12)

Sources Table

Source_Code char(6)
Source varchar(30)

Recipe Table

Recipe_ID char(6)
Recipe_Name varchar(30)
Directions memo
Method_Code char(3)
Source_Code char(3)
Num_Ingredients byte

My final table designs

Here's my final tables for my Recipes database

Recipe Table

Recipe_ID char(6)
Recipe_Name varchar(30)
Directions memo
Method_Code char(3)
Source_Code char(3)
Num_Ingredients byte

Ingredients Table

Recipe_ID char(6)
Ingredient varchar(30)
Qty Decimal
Unit_Measurement varchar(15)
Pre_Prep varchar(30)

Categories Table

Cat_Code char(3)
Category_Name varchar(20)

RecipeCategories Table

Recipe_ID char(6)
Cat_Code char(3)

CookingMethods Table

Method_Code char(3)
Method varchar(12)

Sources Table

Source_Code char(6)
Source varchar(30)

Final Project

Tables

Recipe_ID char(6)
Recipe_Name varchar(30)
Directions memo
Method_Code char(3)
Source_Code char(3)
Num_Ingredients byte

Recipe Table - main table holding recipe information. Recipe ID is the primary key. Child table to CookingMethods Table and Sources Table. Parent table to RecipeCategories Table.

Recipe_ID char(6)
Ingredient varchar(30)
Qty Decimal
Unit_Measurement varchar(15)
Pre_Prep varchar(30)

Ingredients Table - holds all of the recipe ingredients. Linked to the Recipe table via the RecipeID. Primary key consists of Recipe ID and Ingredient fields.

Cat_Code char(3)
Category_Name varchar(20)

Categories Table - used as a lookup table for the different categories of recipes such as Breakfast, Lunch Desserts etc. Primary key is Cat_Code. Parent table to RecipeCategories Table

Recipe_ID char(6)
Cat_Code char(3)

RecipeCategories Table - used to link the category of recipes to the main recipe table since one recipe can fall into more than one category. Primary key is Recipe_ID and Cat_Code field. Child table to Recipe and Categories tables.

Method_Code char(3)
Method varchar(12)

CookingMethods Table - contains all possible cooking methods such as oven, grill, stove top and so on. Primary Key is Method_Code. Serves as a look up table. Parent table to the Recipes table.

Source_Code char(6)
Source varchar(30)

Sources Table - holds the source of a recipe such as Art of French Cooking, Martha Stewart Web Site, etc. Primary key is Source_Code. Parent table to Recipes table.

Data Dictionary

Note: all entries should be in alphabetic order

Directions - a memo field type. Contains all of the cooking directions for a recipe. Used in the Recipes table.

Method_Code - a fixed length string of 3 characters. Used to identify each cooking methods. Is the primary key in the Cooking Methods tables and is a foreign key in the Recipes Table.

Num_Ingredients - a numeric field 1 byte in length. Contains the number of ingredients in a recipe. Used in the Recipes table.

Recipe_ID - fixed length character string that is 6 characters long. A unique value for each recipe entered into the database. Serves as primary key to Recipes Table, and as part of the primary key to Ingredients Table. This field is a foreign key in the RecipeCategories Table

Recipe_Name - a variable length string with a maximum of 30 characters. Holds the name of a recipe. Used in the Recipes Table.

Source_Code - fixed length character string 3 characters long. Used to identify the source of a recipe. Is the primary key in the Sources table and a foreign key in the Recipes table.

and so on.....

Last Updated: 5/1/10