“Life’s a
Game” SQL Project - Part One
IS 475/675 - Spring 2008
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.
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.
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!
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 | |