Home

Database Example: 
Georgetown Cleaning Services

 

Description

This is a small database of 3 tables. It is used to processed orders for a dry cleaning store:

-- =============================================
-- Database: gcs1
-- Name:     Georgetown Cleaning Services
-- Creator:  FunctionX
-- Date:     Wednesday 13 June 2007
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'gcs1'
)
DROP DATABASE gcs1
GO

CREATE DATABASE gcs1
GO
-- =========================================
-- Database: gcs1
-- Table:    Customers
-- Purpose:  This table holds a list of customers
-- =========================================
USE gcs1
GO

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
  DROP TABLE dbo.Customers
GO

CREATE TABLE dbo.Customers
(
	CustomerID int identity(1,1) NOT NULL, 
	PhoneNumber varchar(20) NOT NULL, 
	FullName varchar(50) NOT NULL,
    DateCreated datetime NOT NULL, 
    CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
)
GO
-- =========================================
-- Database: gcs1
-- Table:    OrderStatus
-- Purpose:  This table holds the current status of c cleaning order
--           It specifies whether the order is still being processed,
--           whether it is ready but has not been picked up yet,
--           or it has been done and it has been picked up
--           by the customer
-- =========================================
USE gcs1
GO

IF OBJECT_ID('dbo.OrderStatus', 'U') IS NOT NULL
  DROP TABLE dbo.OrderStatus
GO

CREATE TABLE dbo.OrderStatus
(
	OrderStatusID int identity(1,1) NOT NULL, 
	StatusOfOrder varchar(32) NOT NULL,
    Notes Text,
    CONSTRAINT PK_OrderStatus PRIMARY KEY (OrderStatusID)
)
GO

INSERT INTO OrderStatus(StatusOfOrder, Notes)
VALUES('Being Processed',
       'This is the default status. 
	It indicates that the order has been received and 
        it is being processed.');
GO
INSERT INTO OrderStatus(StatusOfOrder, Notes)
VALUES('Ready',
       'This status indicates that the items of the cleaning 
        order have been processed and they are ready to be picked up.');
GO
INSERT INTO OrderStatus(StatusOfOrder, Notes)
VALUES('Picked Up', 'This status shows that the order has been 
       processed and it has been retrieved by the customer. In other words, 
       the cleanind order has been finalized.');
GO
-- =========================================
-- Database: gcs1
-- Table:    CleaningOrders
-- Purpose:  This table holds a list of customers' orders
-- =========================================
USE gcs1
GO

IF OBJECT_ID('dbo.CleaningOrders', 'U') IS NOT NULL
  DROP TABLE dbo.CleaningOrders
GO

CREATE TABLE dbo.CleaningOrders
(
    CleaningOrderID int identity(1,1) NOT NULL, 
    CustomerID int NOT NULL CONSTRAINT FK_Customers
 		FOREIGN KEY REFERENCES Customers(CustomerID), 
    DateLeft datetime NOT NULL,
    TimeLeft datetime NOT NULL,
    DateReady datetime,
    TimeReady datetime,
    DatePickedUp datetime,
    TimePickedUp datetime,
    UnitPriceShirts money,
    QuantityShirts int,
    UnitPricePants money,
    QuantityPants int,
    NameItem1 varchar(50),
    UnitPriceItem1 money,
    QuantityItem1 int,
    NameItem2 varchar(50),
    UnitPriceItem2 money,
    QuantityItem2 int,
    NameItem3 varchar(50),
    UnitPriceItem3 money,
    QuantityItem3 int,
    NameItem4 varchar(50),
    UnitPriceItem4 money,
    QuantityItem4 int,
    TaxRate decimal(6,2),
    OrderStatusID int NOT NULL CONSTRAINT FK_OrderStatus
		FOREIGN KEY REFERENCES OrderStatus(OrderStatusID),
    CONSTRAINT PK_CleaningOrders PRIMARY KEY (CleaningOrderID)
)
GO

 

 

Home Copyright © 2007-2012 FunctionX