IS 475/675 - Database Design and Implementation

University of Nevada, Reno

Spring Semester 2008

Instructor: Dr. Dana Edberg

Graduate Level Database Design Project

“Sale Catalog”

Bilal Gonen

Objective:

The objective of this report is to demonstrate and explain the data model of an online listing posting system (such as Greg’s list) in third normal form. And also to demonstrate the skill for writing cursor and stored procedure in SQL server.

Description of Data Model:

This system allows users to post some listings to sell. They post their listings under some predefined categories. A listing can be under one category only (such as books, cars, roommates…etc). A listing may have multiple images associated with it. Users have an email inbox where they receive messages from prospective customers.

If the listing is a car, then we store some additional information (such as modal, make, transmission type, mileage, year…etc.) in car, carModel, and carMake tables.

I use also archive_listing table to archive listings.

As for the city table, because this system is intended to use in Turkey, there are no states in Turkey. We have around 80 cities in Turkey.

Archiving Data

I use also archive_listing table to archive listings if they are added more than some user-defined time ago. In this report, I am archiving listings if they are added 10 years ago. This is just to demonstration purpose. Archiving interval may be even a few weeks.

I used cursor to traverse every row in the listing table. In each row, I check to see if the listing was added 10 years ago. If so, then I insert a new record into archive_listing table with the current variables’ values. I didn’t have to use stored procedure to insert the values into archive table. I could just have used simple insert query. But, in order to demonstrate the skill, I execute a stored procedure inside the cursor.

After inserting the record into the archive table, I delete the row from listing table.

The code for both stored procedure and cursor may look too short, but by using cursor, stored procedure, if-else statement, and a code block with begin-end, I tried to make the code comprehensive. By reading a lot of resources about stored procedures and cursor, and obtaining this knowledge, I believe I can write more code.

ERD Diagram

Because this is a small system, the logical design and physical design are the same. The ERD diagram is at the last page of this document.

Resources:

I learned stored procedures, their performance advantages, syntax…etc, from several websites. I think the page below helped me the most;

http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

Also, I learned stored procedures and T-SQL, cursors, from several websites. I think the slides below (around 500 slides) helped me the most;

http://www.mssqlserver.com/tsql/

Cursor and Stored Procedure Example in SQL Server

CREATE PROCEDURE usp_insertArchiveListing

@listingID int,

@title varchar(255),

@msjBody varchar(255),

@catID int,

@userID int,

@price money,

@addedDate datetime

AS

INSERT INTO archive_listing

VALUES (@listingID, @title, @msjBody, @catID, @userID, @price, @addedDate)

*****

Below is the Cursor Code For Archiving

declare @listingID int,

@title varchar(255),

@msjBody varchar(255),

@catID int,

@userID int,

@price money,

@addedDate datetime

declare curlisting cursor for

select * from listing for read only

open curlisting

fetch curlisting into @listingID, @title, @msjBody, @catID, @userID, @price, @addedDate

--Loop through all of the rows

while @@fetch_status = 0

begin

if DATEPART(yyyy, getdate()) - DATEPART(yyyy, @addedDate) > 9

begin

EXECUTE usp_insertArchiveListing @listingID, @title, @msjBody, @catID, @userID, @price, @addedDate

print 'listingID '+CONVERT(varchar(20), @listingID)+ ' was moved to archive_listing table'

end

--Subsequent fetches

fetch curlisting into @listingID, @title, @msjBody, @catID, @userID, @price, @addedDate

end

close curlisting

deallocate curlisting

Result on the SQL Server:

(1 row(s) affected)

listingID 884 was moved to archive_listing table

(1 row(s) affected)

listingID 886 was moved to archive_listing table

Contents of listing table BEFORE the code run

dbo.sp_project1
listingID	title	description	catID	userID	price	addedDate
874	Microeconomics by R. Glenn Hubbard and Anthon	Description of Microeconomics by R. Glenn Hubbard and Anthon	2	138	$40.00	7/12/1999
875	Be the Elephant	Description of Be the Elephant	2	139	$5.00	2/14/2006
876	The Girls Guide to everything	Description of The Girls Guide to everything	2	140	$8.00	2/12/2007
877	Flemings tenth edition Arts & Ideas	Description of Flemings tenth edition Arts & Ideas	2	141	$15.00	10/9/2005
878	Organic Chemistry I	Description of Organic Chemistry I	2	142	$15.00	11/2/2005
879	First Year (1L) Law School Books	Description of First Year (1L) Law School Books	2	138	$0.00	11/6/2004
880	molecular cell biology, fifth edition	Description of molecular cell biology, fifth edition	2	139	$50.00	4/1/1999
881	Principle of biochemistry (BCMB 3100)	Description of Principle of biochemistry (BCMB 3100)	2	140	$89.00	4/12/2006
882	Chilton Repair manual: Chevrolet Camaro	Description of Chilton Repair manual: Chevrolet Camaro	2	141	$10.00	5/6/2006
883	Haynes Repair Manual Dodge Neon 1995-1999	Description of Haynes Repair Manual Dodge Neon 1995-1999	2	142	$10.00	8/23/2005
884	MARS 8010 textbook	Description of MARS 8010 textbook	2	138	$25.00	8/30/1998
885	Applied Linear Statistical Models	Description of Applied Linear Statistical Models	2	139	$0.00	8/12/1999
886	The Educational Assessment of Students	Description of The Educational Assessment of Students	2	140	$30.00	8/15/1989
887	Applied Behavior Analysis for Teachers	Description of Applied Behavior Analysis for Teachers	2	141	$65.00	3/12/2006
888	Microbiology book For MIBO 3500	Description of Microbiology book For MIBO 3500	2	142	$80.00	12/11/2006
889	Gaming desktop: dual core, 3 GB RAM	Description of Gaming desktop: dual core, 3 GB RAM	5	143	$750.00	4/1/1999
890	dell laptop - dual core, 1GB RAM	Description of dell laptop - dual core, 1GB RAM	5	144	$750.00	4/12/2006
891	Cheap Student Desktop Computer	Description of Cheap Student Desktop Computer	5	145	$410.00	5/6/2006
892	Desktop for sale	Description of Desktop for sale	5	146	$150.00	8/23/2005
893	98 Mazda Protege LX	Description of 98 Mazda Protege LX	3	139	$4,900.00	8/24/2005
894	1989 Nissan Maxima	Description of 1989 Nissan Maxima	3	140	$750.00	8/25/2005
895	For sale pick up truck F-150	Description of For sale pick up truck F-150	3	141	$2,500.00	8/26/2005
896	2002 Dodge truck 1500 Sport Edition	Description of 2002 Dodge truck 1500 Sport Edition	3	142	$16,400.00	8/27/2005
897	1995 Mercury Cougar	Description of 1995 Mercury Cougar	3	143	$1,700.00	8/28/2005
898	2001 Red Saturn SC2	Description of 2001 Red Saturn SC2	3	144	$4,500.00	4/10/2008
899	1 F Roomate needed for great house	Description of 1 F Roomate needed for great house	7	145	$300.00	4/11/2008
900	looking for rommate(s)	Description of looking for rommate(s)	7	146	$0.00	4/12/2008
901	roommate wanted	Description of roommate wanted	7	147	$340.00	4/13/2008
902	1 roommate fully furnished 2 bedroom	Description of 1 roommate fully furnished 2 bedroom	7	148	$450.00	4/14/2008

Contents of archive_listing table AFTER the code run

dbo.sp_project1
listingID	title	msjBody	catID	userID	price	addedDate
884	MARS 8010 textbook	Description of MARS 8010 textbook	2	138	$25.00	8/30/1998
886	The Educational Assessment of Students	Description of The Educational Assessment of Students	2	140	$30.00	8/15/1989

Contents of listing table AFTER the code run

dbo.sp_project1
listingID	title	description	catID	userID	price	addedDate
874	Microeconomics by R. Glenn Hubbard and Anthon	Description of Microeconomics by R. Glenn Hubbard and Anthon	2	138	$40.00	7/12/1999
875	Be the Elephant	Description of Be the Elephant	2	139	$5.00	2/14/2006
876	The Girls Guide to everything	Description of The Girls Guide to everything	2	140	$8.00	2/12/2007
877	Flemings tenth edition Arts & Ideas	Description of Flemings tenth edition Arts & Ideas	2	141	$15.00	10/9/2005
878	Organic Chemistry I	Description of Organic Chemistry I	2	142	$15.00	11/2/2005
879	First Year (1L) Law School Books	Description of First Year (1L) Law School Books	2	138	$0.00	11/6/2004
880	molecular cell biology, fifth edition	Description of molecular cell biology, fifth edition	2	139	$50.00	4/1/1999
881	Principle of biochemistry (BCMB 3100)	Description of Principle of biochemistry (BCMB 3100)	2	140	$89.00	4/12/2006
882	Chilton Repair manual: Chevrolet Camaro	Description of Chilton Repair manual: Chevrolet Camaro	2	141	$10.00	5/6/2006
883	Haynes Repair Manual Dodge Neon 1995-1999	Description of Haynes Repair Manual Dodge Neon 1995-1999	2	142	$10.00	8/23/2005
885	Applied Linear Statistical Models	Description of Applied Linear Statistical Models	2	139	$0.00	8/12/1999
887	Applied Behavior Analysis for Teachers	Description of Applied Behavior Analysis for Teachers	2	141	$65.00	3/12/2006
888	Microbiology book For MIBO 3500	Description of Microbiology book For MIBO 3500	2	142	$80.00	12/11/2006
889	Gaming desktop: dual core, 3 GB RAM	Description of Gaming desktop: dual core, 3 GB RAM	5	143	$750.00	4/1/1999
890	dell laptop - dual core, 1GB RAM	Description of dell laptop - dual core, 1GB RAM	5	144	$750.00	4/12/2006
891	Cheap Student Desktop Computer	Description of Cheap Student Desktop Computer	5	145	$410.00	5/6/2006
892	Desktop for sale	Description of Desktop for sale	5	146	$150.00	8/23/2005
893	98 Mazda Protege LX