Overview

The purpose of this part of the project is to create tables with constraints and then populate those tables with data to produce a test order database for Life’s A Game, an online puzzle and game distributor. Life's a Game uses the Internet to distribute unusual games and puzzles directly to the public. The company specializes in games and puzzles that are challenging and different from those available in bricks and mortar distributors such as K-Mart and Toys-R-Us.

The database you will create, populate and access represents part of the online customer ordering system for the organization. It is a transaction processing database to capture and store all current orders for the organization.

Your job for this part of the project is to create seven tables in your existing database as described in this document and populate those tables with the data included on the pages following the table descriptions. Please do not make up different data – you must use the data included in this document in the format it is included. I want everyone in class to be working from exactly the same test dataset.

This part of the project is not especially difficult, but it can be time-consuming and tedious. It is a good way to become familiar with SQL syntax and SQL Server, so bear with it - the rest of the project is much more fun.

Methods of Table Creation and Population

I will explain in class how to create and populate tables via SQL. You must use SQL CREATE commands stored in SQL files to create your tables. This will help you understand the use of constraints (especially the referential integrity constraint) within a database.

The method of populating your tables is up to you. A simple, but tedious and time-consuming method of table population is the use of the SQL INSERT command. You must write one INSERT statement for each row of each table that you wish to populate. If you save your INSERT statements in a script file (a script file is just a plain old ASCII file created via a text editor such as WordPad, NotePad or EditPad), then you will be able to re-populate your tables quickly and easily in case there are problems with the data in the future.

It is OK to use the SQL Server data input screens to populate your tables. To do this, highlight and right click the table name. Select “open table” from the window. You will then see a grid displayed on the screen for data entry. All data entered through this grid will be committed to disk when you do any other function via SQL Server.

Feel free to use whatever method you want - just make sure you understand what you are doing to create and populate the tables accurately.

Very important note: Inaccurate output on other parts of the project will be graded as wrong - even if the error is a result of a simple data input error on this part of the project. You are responsible for the accuracy of your test data set!! The test data set you use MUST LOOK exactly like the test data set in this document. Even if I use the same project for more than one semester, I modify the contents of the test dataset.

Project Issues

A few issues concerning this part of the project are discussed below.

1. Do not create a database. We have created a database for you in the COBA labs – each student has his/her own database created automatically – so there is no need to issue a "create database" SQL command in the COBA labs, either. For this part of the project, you are creating tables in an existing database. Your starting place for this part of the project is to issue “CREATE TABLE” commands in SQL to create each of the tables.

2. Plan how to populate the tables. Data must be entered into parent tables before it is entered into child tables. Think about the concept of referential integrity and its implementation through foreign keys before populating the tables. An example of the concept is discussed below.

Many of the tables in this database are related to each other. One of those relationships, for example, is between the customer and order tables. The business rule for this relationship is: An order is only placed by an existing customer so the customer must be entered into the customer table before the order can be entered into the order table. The customer is the "parent" table and the order is the "child" table in this relationship. This business rule is established through a foreign key of the customerID in the order table. When a new order is entered in the order table (the child), the DBMS will check the customer table (the parent) to make sure the customer exists before permitting the entry into the order table. If the customer does not exist, the DBMS will not allow the order to be entered into the order table. This is a referential integrity constraint placed on the order table.

An issue for a database designer or administrator is deciding the order that the tables must be created and populated in a database, since the tables are related to one another. A solution to this issue is to create the tables without referential integrity constraints, populate the tables, and then add the referential integrity constraints. A problem with that solution is that the DBMS will not validate the data as you populate the tables. For example, if you enter an order with an invalid customerID (let’s say you make a typo), the DBMS will not validate the customerID and will let you enter the invalid data. Thus, you are responsible for validating the data as it is entered.

Another solution is to differentiate parent tables and child tables by looking at the ERD. Sometimes it can be difficult to differentiate parent from child because a table may be a parent to some tables while also being a child to other tables. For example, the TblOrderLine table is a child table when related to the TblOrder and TblItem tables, but it is a parent table when related to the TblShipLine table. So the TblOrderLine table must be populated after the TblOrder and TblItem tables, but before the TblShipLine table. This will take planning and an understanding of referential integrity on your part, but it works!

3. Do not include some of the formatting characters in data. Do not put parentheses or dashes in telephone numbers. We are going to use the telephone number as a way to learn how to use the SQL substring (SUBST) and (INSTR) commands so please input the telephone number without any formatting characters. Do not put commas or dollar signs in data representing dollars. Always include decimals points where applicable in numeric data. A decimal point is not a formatting character in SQL Server; you must include the decimal point to get the correct numeric amount if there are digits (other than zeros) after the decimal point.

4. Enter the capitalization exactly as shown. Some of the data are upper case and other are lower. Please enter the data exactly as shown on this handout – we will “fix” these data in future parts of the project.

5. Enter character fields with character strings. Computers differentiate character strings from numbers, even if numeric characters are stored in a field declared as a character data type. For example, the OrderID in this database is a character field (char) type. If you enter a number in a character field (meaning that you enter the data without quotation marks surrounding it) then SQL Server will store it as a number. So if the OrderID is 00722 and you enter it as a number when using the INSERT command (without quotes) then SQL Server will store it as 722. If you enter it as a character string, ‘00722’ then SQL Server will store the leading zeroes. Be sure to enter all character data with quotation marks, even if that data is numeric.

6. Enter the data accurately. I grade your output for SQL project parts 2-4 based on how well it matches my generated output. If your answers do not match my output, then I will take off points even if the problem is purely a data entry error. Of course, I won't take off as many points for data entry errors as for actual SQL errors, but you will not get full credit if you have inaccurate output. Check your data and make sure it is accurate!

Deliverables

Please turn in the following for grading of part one:

1) The "CREATE TABLE" statements for each table.

2) A discussion of how you populated the tables (examples: Used the data input mode through SQL Server, or connected Access to SQL Server and used the Access front end to enter the data, or used the "INSERT" statements used for each row for each table.)

3) The results from a "SELECT * from table_name" in SQL. Example:

SELECT *

FROM TblOrder;

Do not worry about "pretty" output for this part of the project. All I want is to see that you have correctly created and populated the tables.

Use the connection between MS Access and SQL Server to look at the results of your SQL query. Copy the results from MS Access to a Word document for better readability.

Make it somewhat more readable for me. You can improve the readability of your output by changing the Word page format to landscape and changing the font size to 8 or 10 point.

Put the paper output in a large envelope. Turn it in to me. There is no need to turn in any computer-based output for part 1. PLEASE DO NOT use a folder that requires a three-hole punch or that clasps the entire left side of the document. I much prefer that you simply clip the pages together with a paper clip and put all pages in an envelope.

Structures for Tables

Table: TblCustomer
Attribute Name	Data Type & Size	Primary Key	Foreign Key	Other Constraints
CustomerID	char(5)	yes	no	no null value
LastName	varchar(30)	no	no	no null value
FirstName	varchar(20)	no	no	null value OK
Address	varchar(30)	no	no	no null value
City	varchar(20)	no	no	no null value
State	char(2)	no	no	no null value
Zip	varchar(12)	no	no	no null value
Country	varchar(15)	no	no	null value OK
FirstBuyDate	datetime	no	no	null value OK
Email	varchar(60)	no	no	null value OK
Phone	char(15)	no	no	no null value

Table: TblOrder
Attribute Name	Data Type & Size	Primary Key	Foreign Key	Other Constraints
OrderID	char(6)	yes	no	no null value
CustomerID	char(5)	no	yes - reference tblcustomer	no null value
OrderDate	datetime	no	no	no null value
DiscountCode	char(2)	no	no	null value OK valid discount code include 02, 03, 04, 06, 08, 10, A1 and B3
CreditCode	char(3)	no	no	no null value
ShipName	varchar(30)	no	no	null value OK
ShipAddress	varchar(30)	no	no	null value OK
ShipZip	varchar(12)	no	no	null value OK
ShipCountry	varchar(30)	no	no	null value OK
ShipPhone	char(15)	no	no	null value OK

Table: tblItem
Attribute Name	Data Type & Size	Primary Key	Foreign Key	Other Constraints
ItemID	char(6)	yes	no	no null value
Description	varchar(300)	no	no	null value OK
ListPrice	money	no	no	no null value listprice must be greater than $5.

Table: tblOrderLine
Attribute Name	Data Type & Size	Primary Key	Foreign Key	Other Constraints
OrderID	char(6)	yes	yes – reference TblOrder	no null value
ItemID	char(6)	yes	yes – reference TblItem	no null value
Quantity	int	no	no	no null value, value must be greater than 0.
Price	money	no	no	no null value, value must be greater than 0.

Table: TblShipLine
Attribute Name	Data Type & Size	Primary Key	Foreign Key	Other Constraints
DateShipped	datetime	yes	no	no null value
OrderID	char(6)	yes	yes – reference TblOrderLine	no null value
ItemID	char(6)	yes	yes – reference TblOrderLine yes – reference TblItemLocation	no null value
LocationID	char(2)	yes	yes – reference TblItemLocation	no null value
QtyShipped	int	no	no	no null value
MethodShipped	varchar(30)	no	no	no null value

Table: TblItemLocation
Attribute Name	Data Type & Size	Primary Key	Foreign Key	Other Constraints
ItemID	char(6)	yes	yes – reference TblItem	no null value
LocationID	char(2)	yes	no	no null value
QtyOnHand	int	no	no	null value OK

Table: tblItemCostHistory
Attribute Name	Data Type & Size	Primary Key	Foreign Key	Other Constraints
ItemID	char(6)	yes	yes – reference tblItem	no null value
LastCostDate	datetime	yes	no	no null value
LastCost	money	no	no	no null value

Data for Table Population

TBLITEM

	itemid	name	listprice
1.	A23441	New York City Monopoly Game Collector’s Edition	29.95
2.	A34665	Boggle Deluxe 5x5	34.95
3.	A23771	Catch Phrase Game Music Edition	32.95
4.	A34882	Perudo	10.95
5.	B67123	Cranium WOW Edition	15.95
6.	B67466	Diplomacy: Game of Negotiation, Cunning and Deceit.	43.95
7.	B78244	Chicks Battle the Dudes Board Game	38.95
8.	B78500	You Might be a Redneck if…Jeff Foxworthy Board Game	35.95
9.	C34122	A Game of Strategy, Negotiation and Excitement for Office Retreats	169.95
10.	C29179	Managing Change: The Game for an Executive Retreat	259.95
11.	C26133	Knowledge Management: Create a Learning Organization	395.95

TBLCUSTOMER

	customerid	lastname	firstname	address1	city	state	zip	country	firstbuydate	email	phone
1.	00405	Barrington	Margaret	1765 Roundtree Pkwy	reno	nv	89509-1454	USA	12-Jul-1999	barry@hotmail.com	775-746-4561
2.	12006	Martinez	Guadalupe	223 North Pinetree Drive	Reno	NV	89511	null	14-Feb-2006	null	775-883-7612
3.	32018	Jones	Martin	10 South Wilders	reno	nv	89503-8912	USA	12-Feb-2007	null	775-331-4838
4.	78112	Guili	Mary Anne	4457 Meridith St.	Irvine	CA	97128	null	9-Oct-2005	null	619-562-1334
5.	00625	Dao	Phong	341 West Park	Fresno	CA	96137	null	2-Nov-2005	null	858-213-8982
6.	07831	Rodriguez	Karen	4589 Marthiam	Chico	CA	97111	null	6-Nov-2004	rodriquez@aol.com	819-382-1828
7.	08892	Twillers	Bethany	P.O. Box 5661	san jose	ca	98123	USA	1-Apr-1999	null	809-829-1838
8.	12001	Cranston	Brittany	12 Sandstone	Sparks	NV	89431	null	12-Apr-2006	null	775-331-2199
9.	21143	Jackson	Janice	2341 Bramble Bush Drive	Sparks	NV	89431-0112	null	6-May-2006	jj@isp.all.com	775-331-7188
10.	29188	Polanski	Tiffany	5778 Battlemount Ct.	RENO	NV	89507	null	23-Aug-2005	null	775-746-5771
11.	30192	Chen	Lian	2319 Crest Dr.	REno	Nv	89503-0113	null	30-Aug-1998	jester@here.com	775-721-8991
12.	06774	Phillips	Kendall	44512 Sawbuck Path	SPARKS	nv	89432	USA	12-Aug-1999	null	775-332-4636
13.	32817	Foster	Ben	318 Western Ave.	SAN diego	ca	92381	null	15-Aug-1989	foster_ben@aol.com	858-328-4483
14.	38817	Argiento	Bud	1001 Catchway	Anaheim	CA	95113	null	12-Mar-2006	null	803-771-8991
15.	21142	Candriller	Kathy	2 Sedgeway	Laguna Beach	CA	94567	null	11-Dec-2006	null	619-881-3929

TBLORDER

	orderid	orderdate	customerid	discountcode	creditcode	shipname	shipaddress	shippostalcode	shipcountry	shipphone
1.	123000	02-Feb-08	00405	A1	111	null	null	null	null	null
2.	400001	29-Feb-08	32018	B3	111	null	null	null	null	null
3.	980001	22-jan-08	78112	04	666	null	null	null	null	null
4.	781206	15-feb-08	38817	06	666	Carrington-Smythe	231 Dulwich St. Wellington	TA21 0AB	UK	020 8888 7009
5.	671100	02-feb-08	32018	null	111	null	null	null	null	null
6.	223344	09-feb-08	21142	null	444	null	null	null	null	null
7.	567123	26-jan-08	07831	null	444	Jenkins Corporation	2276 Brentell Street Suite 201	92128	USA	858 344 0669
8.	445511	15-feb-08	32018	02	444	null	null	null	null	null
9.	300221	30-jan-08	12006	03	111	null	null	null	null	null
10.	200335	15-jan-08	06774	null	111	Cordwin	Arch 162 Stamford Brook	W6 0SE	UK	44 181 741 7500
11.	651222	29-jan-08	12006	null	666	null	null	null	null	null
12.	892211	28-dec-08	00625	null	111	null	null	null	null	null
13.	450137	29-dec-08	07831	null	444	Frandsen LLC	435 Caminito Corriente	92129	USA	655 122 3298

TBLORDERLINE

	orderid	itemid	quantity	price		orderid	itemid	quantity	price
1.	123000	A23441	8	29.95	19.	300221	A34882	1	10.95
2.	123000	A34665	30	37.95	20.	300221	B67123	1	15.95
3.	123000	B67123	5	15.95	21.	300221	B78244	1	32.95
4.	400001	C26133	1	395.95	22.	200335	B67466	1	43.95
5.	980001	C34122	2	169.95	23.	200335	A34665	1	34.95
6.	980001	C29179	3	275.99	24.	200335	A23441	1	29.95
7.	781206	B67466	1	43.95	25.	200335	B67123	1	19.95
8.	671100	C29179	1	25.95	26.	651222	A34665	5	37.95
9.	223344	A23441	55	29.95	27.	651222	A34882	16	11.95
10.	223344	A34665	100	23.95	28.	651222	B78244	21	31.65
11.	223344	A23771	15	53.99	29.	892211	C26133	15	380.00
12.	223344	A34882	35	7.95	30.	892211	C29179	10	259.95
13.	223344	B67123	25	14.95	31.	892211	C34122	8	200.00
14.	223344	B67466	15	40.95	32.	450137	B67123	21	14.95
15.	567123	C26133	1	395.95	33.	450137	C34122	6	167.95
16.	445511	C34122	3	269.95	34.	450137	C26133	1	398.95
17.	300221	A34665	1	35.95	35.	450137	A34882	50	9.95
18.	300221	A23771	1	65.95	36.	450137	A34665	10	31.00

TBLITEMLOCATION

	itemid	locationid	qtyonhand
1.	A23441	10	11
2.	A23441	20	23
3.	A23441	30	25
4.	A34665	10	141
5.	A23771	10	6
6.	A23771	20	4
7.	A23771	30	5
8.	A34882	10	40
9.	A34882	30	55
10.	B67123	10	22
11.	B67466	10	0
12.	B78244	20	22
13.	B78500	20	8
14.	B78500	30	1
15.	C34122	10	16
16.	C34122	20	21
17.	C29179	10	15
18.	C29179	20	15
19.	C26133	10	0
20.	B67123	30	6
21.	B67123	40	4
22.	B67123	20	28

TBLSHIPLINE

	dateshipped	orderid	itemid	locationidfrom	qtyshipped	meth