-- =============================================
-- Database: FunDS1
-- Author: FunctionX
-- Date: Wednesday 27 January 2010 - 14:15
-- =============================================
USE master
GO
-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'FunDS1'
)
DROP DATABASE FunDS1
GO
CREATE DATABASE FunDS1
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Fun Department Store1\FunDS1.mdf')
LOG ON
( NAME = DataLog, FILENAME = 'C:\Fun Department Store1\FunDS1.ldf')
GO
USE FunDS1;
GO
-- =======================================================
-- Database: FunDS1
-- Table: StoreItems
-- Description: This table holds an inventory of all the
-- merchandise sold in the store
-- =======================================================
IF OBJECT_ID('StoreItems', 'U') IS NOT NULL
DROP TABLE StoreItems
GO
CREATE TABLE StoreItems
(
ItemNumber nchar(10) not null,
Manufacturer nvarchar(50) null,
Category nvarchar(40),
SubCategory nvarchar(40),
ItemName nvarchar(50) not null,
Size nvarchar(32),
UnitPrice money default 0,
CONSTRAINT PK_StoreItems Primary Key(ItemNumber)
);
GO
INSERT INTO StoreItems
VALUES(582604, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'0', 265.50),
(749374, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'8',65.85),
(379374, N'Shiseido', N'Women', N'Beauty', N'Zen Perfumed Shower Gel', N'3.3 Oz', 35.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(248592, N'Women', N'Clothing', N'Petite Tech Stretch Seamed Pencil Skirt', N'4', 100.00);
GO
INSERT INTO StoreItems
VALUES(757947, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'9', 65.85);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(294725, N'Women', N'Handbag', N'Perfect Tote', 295.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(264826, N'Cole Haan', N'Women', N'Clothing', N'Saddle East/West Leather Hobo Bag', 345.50);
GO
INSERT INTO StoreItems
VALUES(684604, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95),
(158824, N'Mulberribush', N'Boys', N'Clothing', N'2-7 Twill Flat-Front Pants', N'2T', 24.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(495007, N'Giorgio Armani', N'Men', N'Beauty', N'Acqua Di Gio After Shave Balm', 49.50),
(729741, N'Symphony Designs', N'Women', N'Accessories', N'Wool Pashmina-style Wrap', 16.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(927940, N'Women', N'Clothing', N'Petite New Femme Jacket', N'6', 210.00);
GO
INSERT INTO StoreItems
VALUES(790279, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'2', 265.50),
(957492, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'10', 65.85),
(557504, N'Trina', N'Women', N'Clothing', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'10', 265.50),
(779204, N'Wes & Willy', N'Boys', N'Clothing', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long', 4.95),
(864400, N'Trina', N'Women', N'Clothing', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50),
(925749, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85),
(628346, N'Fiona', N'Women', N'Clothing', N'Fiona High Heel Boot', N'6.50',295.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(979120, N'Women', N'Clothing', N'Pleated Neck Dress', N'16',180.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(707394, N'Kenneth Cole', N'Women', N'Wallet', N'Bar Tab Leather Tab Wallet', 19.95),
(972934, N'Ted Baker', N'Men', N'Ties', N'Grid Silk Tie', 89.50);
GO
INSERT INTO StoreItems
VALUES(295300, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'6 Months', 9.95);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(492957, N'Invicta', N'Women', N'Watches', N'Pro Diver Two-tone Watch', 58.95);
GO
INSERT INTO StoreItems
VALUES(297035, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'3T',18.95),
(286401, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'6',265.50),
(927351, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'9.5',65.85),
(649004, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'12',265.50),
(709274, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'8 Months',9.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(790402, N'Women', N'Clothing', N'Petite New Femme Jacket', N'2', 210.00);
GO
INSERT INTO StoreItems
VALUES(770240, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50),
(952735, N'Black Brown 1826', N'Men', N'Clothing', N'3-Button Top Coat', N'42R', 495.00),
(974815, N'Trina', N'Women', N'Clothing', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'0', 265.50),
(184081, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'8', 65.85),
(660284, N'Shiseido', N'Women', N'Beauty', N'Zen Perfumed Shower Gel', N'3.3 Oz', 35.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(208405, N'Women', N'Clothing', N'Petite Tech Stretch Seamed Pencil Skirt', N'4', 100.00);
GO
INSERT INTO StoreItems
VALUES(697224, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'9', 65.85);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(972947, N'Cole Haan', N'Women', N'Clothing', N'Saddle East/West Leather Hobo Bag', 345.50);
GO
INSERT INTO StoreItems
VALUES(820284, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(862040, N'Women', N'Accessories', N'Perfect Luxe Scarf', 45.00);
GO
INSERT INTO StoreItems
VALUES(297204, N'Mulberribush', N'Boys', N'Clothing', N'2-7 Twill Flat-Front Pants', N'2T', 24.50);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(602947, N'Women', N'Clothing', N'Pleated Neck Dress', N'16', 180.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(792002, N'Giorgio Armani', N'Men', N'Beauty', N'Acqua Di Gio After Shave Balm', 49.50),
(797140, N'Kenneth Cole', N'Women', N'Wallet', N'Bar Tab Leather Tab Wallet', 19.95);
GO
INSERT INTO StoreItems
VALUES(119723, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(740597, N'Women', N'Clothing', N'Petite New Femme Jacket', N'6', 210.00);
GO
INSERT INTO StoreItems
VALUES(628460, N'Mulberribush', N'Boys', N'Clothing', N'2-7 Twill Flat-Front Pants', N'3T',24.50),
(864402, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(186824, N'Invicta', N'Women', N'Watches', N'Pro Diver Two-tone Watch', 58.95);
GO
INSERT INTO StoreItems
VALUES(628316, N'Kenneth Cole', N'Men', N'Clothing', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(794750, N'Women', N'Clothing', N'Textured Jacket', N'6', 325.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(826114, N'Ted Baker', N'Men', N'Ties', N'Grid Silk Tie', 89.50);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(592733, N'Women', N'Clothing', N'Petite New Femme Jacket', N'2', 210.00);
GO
INSERT INTO StoreItems
VALUES(295001, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'6 Months', 9.95);
GO
INSERT INTO StoreItems
VALUES(641104, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'12',265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(797040, N'Kenneth Cole', N'Women', N'Wallet', N'Bar Tab Leather Tab Wallet', 19.95);
GO
INSERT INTO StoreItems
VALUES(860402, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(790002, N'Giorgio Armani', N'Men', N'Beauty', N'Acqua Di Gio After Shave Balm', 49.50);
GO
INSERT INTO StoreItems
VALUES(709174, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'8 Months', 9.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(592735, N'Women', N'Clothing', N'Petite New Femme Jacket', N'2', 210.00);
GO
INSERT INTO StoreItems
VALUES(119720, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T',18.95),
(779240, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4',265.50),
(602917, N'Wes & Willy', N'Boys', N'Clothing', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long',4.95),
(297030, N'Sweatheart Rose', N'Girls', N'Clothing', N'2-6X Two-For Polo & Pleated Plaid Dress', N'2',40.00),
(770241, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4',265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, SubCategory, ItemName, UnitPrice)
VALUES(286402, N'Andiamo', N'Bedding', N'Solid 500 Thread Count Egyptian Cotton Sheet', 44.95);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(862046, N'Symphony Designs', N'Women', N'Accessories', N'Wool Pashmina-style Wrap', 16.95);
GO
INSERT INTO StoreItems
VALUES(777240, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(797440, N'Kenneth Cole', N'Women', N'Wallet', N'Bar Tab Leather Tab Wallet', 19.95),
(790202, N'Giorgio Armani', N'Men', N'Beauty', N'Acqua Di Gio After Shave Balm', 49.50);
GO
INSERT INTO StoreItems
VALUES(952935, N'Black Brown 1826', N'Men', N'Clothing', N'3-Button Top Coat', N'42R', 495.00),
(779242, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(829411, N'Women', N'Clothing', N'Petite Tropical Wool Perfect Pencil Skirt', N'6', 110.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(200095, N'Citizen', N'Men', N'Watches', N'Eco-Drive Men''s Titanium Bracelet Watch', 115.00);
GO
INSERT INTO StoreItems
VALUES(872047, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'6', 265.50),
(827114, N'Joseph Abbound', N'Boys', N'Clothing', N'Guys 8-20 Blue & Tan Striped Dress Shirt', N'4', 45.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, Size, UnitPrice)
VALUES(860613, N'Women', N'Clothing', N'Narrow V-Neck Top', N'2', 55.50);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(790064, N'Women', N'Jewelry', N'Pearl and Ribbon Four-Strand Necklace', 65.00),
(227994, N'Women', N'Clothing', N'Perfect Skinny Belt', 30.00);
GO
INSERT INTO StoreItems
VALUES(797064, N'Trina', N'Women', N'Clothing', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'2', 265.50),
(909120, N'Mulberribush', N'Boys', N'Clothing', N'2-7 Twill Flat-Front Pants', N'3T', 24.50),
(600947, N'Wes & Willy', N'Boys', N'Clothing', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long',4.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(297249, N'Women', N'Handbags', N'Chain Handle Tote', 225.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(852040, N'Symphony Designs', N'Women', N'Accessories', N'Wool Pashmina-style Wrap', 16.95);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(995135, N'Women', N'Jewelry', N'Pearl Stretch Bracelet', 40.00);
GO
INSERT INTO StoreItems
VALUES(290030, N'Ralph Lauren', N'Girls', N'Clothing', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'3T',18.95),
(202835, N'Bonnie Jean', N'Girls', N'Clothing', N'Girls 2-6x Two-For Dress & Coat', N'4T',50.00),
(296030, N'Sweatheart Rose', N'Girls', N'Clothing', N'2-6X Two-For Polo & Pleated Plaid Dress', N'4',40.00);
GO
-- ===============================================================================
-- Database: FunDS1
-- Table: CustomersOrders
-- Description: This table holds the items that a typical has bought in the store.
-- Each record contains a receipt number, followed by an item bought
-- by a customer. Each item bought by a customer for a shopping
-- session shares the same receipt number, the same date, and the
-- same time
-- ===============================================================================
IF OBJECT_ID('CustomersOrders', 'U') IS NOT NULL
DROP TABLE CustomersOrders
GO
CREATE TABLE CustomersOrders
(
CustomerOrderID int identity(1, 1) not null,
ReceiptNumber int not null,
PurchaseDate date not null default getdate(),
PurchaseTime time not null,
ItemNumber nchar(10) not null,
ItemName nvarchar(50) not null,
Size nvarchar(32),
UnitPrice money default 0,
CONSTRAINT PK_CustomersOrders Primary Key(CustomerOrderID)
);
GO
/* The following record is created as a first and default.
This is used as a "dummy" record and will be used as a reference.
This record should never be deleted.
*/
INSERT INTO CustomersOrders(
ReceiptNumber, PurchaseDate, PurchaseTime,
ItemNumber, ItemName, Size, UnitPrice)
VALUES(100000, GetDate(), N'09:00 AM', 0, N'Unknown', N'Fits All', 0);
GO
-- ===============================================================================
-- Database: FunDS1
-- Table: ReceiptsSummaries
-- Description: This table holds a type of monetary summary for each receipt.
-- A typical record contains a receipt number, the date and the time
-- the purchase took place, the total value of the purchase, and the
-- mode of payment the customer used.
-- We also include two columns that can be omitted: one that
-- represents the amount the customer tended to the cashier
-- (unless a mode other than cash was used
-- ===============================================================================
IF OBJECT_ID('ReceiptsSummaries', 'U') IS NOT NULL
DROP TABLE ReceiptsSummaries
GO
CREATE TABLE ReceiptsSummaries
(
ReceiptSummaryID int identity(1, 1) not null,
ReceiptNumber int not null,
PurchaseDate nvarchar(50) not null,
OrderTotal money not null,
TypeOfCurrency nvarchar, -- This will include: Cash, Check, Store Card, Debit Card, or Credit Card
AmountTended money not null, -- This unnecessary column is used for claiming purposes
Change money, -- This unnecessary column is used for claiming purposes
CONSTRAINT PK_ReceiptsSummaries Primary Key(ReceiptSummaryID)
);
GO
COMMIT TRANSACTION DeptStore;
GO
-- Home