-- Department Store Database
-- =============================================
-- Database: FunDS1
-- Full Name: Fun DS
-- Fun Department Store
-- Author: FunctionX
-- Date: Saturday 24 October 2009 - 14:28
-- =============================================
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
GO
USE FunDS1;
GO
-- =========================================
-- Database: FunDS1
-- Table: Employees
-- Description: This table holds the information about each employee
-- =========================================
IF OBJECT_ID('Employees', 'U') IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
EmployeeNumber nchar(10) NOT NULL,
FirstName nvarchar(25) NULL,
LastName nvarchar(25) NULL,
HourlySalary money NULL,
Username nvarchar(20),
UserPassword nvarchar(25),
CONSTRAINT PKEmployees PRIMARY KEY (EmployeeNumber)
)
GO
-- =========================================
-- Database: FunDS1
-- Table: StoreCards
-- Description: This tables holds a list of store cards and
-- the customers to whom they were issued
-- =========================================
IF OBJECT_ID(N'StoreCards', N'U') IS NOT NULL
DROP TABLE StoreCards
GO
CREATE TABLE StoreCreditCards
(
AccountNumber nchar(20) NOT NULL,
DateCreated date default GetDate(),
CustomerName nvarchar(50) NULL,
ExpirationMonth nchar(4) not null,
ExpirationYear nchar(6) not null,
SecurityCode nchar(6) not null,
CreditLimit money,
InterestRate decimal(6, 2) default 0.00, -- 0%
CurrentBalance money,
CardStatus nvarchar(20) not null, -- Processing, Active, Disabled, Closed
CONSTRAINT PKCustomers PRIMARY KEY (AccountNumber)
)
GO
-- ===============================================================================
-- Database: FunDS1
-- Table: StoreItems
-- Description: This table holds an inventory of all the
-- merchandise sold in the store. The items are added/created by the
-- employees when these items are acquired by the department store.
-- Each item is represented by:
-- 1. ItemNumber: This is a random number specified by the store or
-- the employee who is creating the record
-- 2. Manufacturer: This is the make of the item. This value is
-- optional but can be helpful
-- 3. Category: The regular categories include Women, Men, Girls,
-- Boys, Babies, and Other
-- 4. SubCategory: This is a category inside the above category.
-- For women, it could be Dresses, Suits, Pants, Shorts,
-- Skirts, Jackets, Blouses, Sweaters, Shoes, Frangrance,
-- Watches, Jewelry, and Other
-- For men, it could be Suits, Pants, Shorts, Ties,
-- Sweaters, Blazers, Shoes, Frangrance, Watches,
-- Jewelry, and Other
-- 5. ItemName: This is the name (or short description) of the item
-- 6. ItemSize: This is the size. This is very vague and can be anything.
-- It can be 0, 2, 4, 6, 8, S, Small, M, Medium, L,
-- Large, XL, Extra Large, XXL for a clothing item.
-- It can be a decimal value for a fragrance.
-- This can be left empty if an item doesn't have a size
-- 7. UnitPrice: This is the price of an item
-- Note: This table purposely doesn't store the quantity of items.
-- This is because each item has a unique ItemNumber. If the
-- same item is entered many times, each will have its own
-- unique number. If necessary, a quantity would be
-- or calculated by counting the number of occurrences of an
-- item
-- 8. SaleStatus: When an item has been sold, its status changes from Available to Sold.
-- The values of this field are: Available, Processing, Sold, Other
-- ===============================================================================
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,
ItemSize nvarchar(32),
UnitPrice money default 0,
SaleStatus nvarchar(40) default N'Available',
CONSTRAINT PK_StoreItems Primary Key(ItemNumber)
);
GO
INSERT INTO StoreItems
VALUES(582604, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'0', 265.50, N'Available'),
(749374, N'Kenneth Cole', N'Men', N'Dresses', N'Simplicity Leather Dress Oxfords', N'8',65.85, N'Available'),
(925004, N'Nicole Miller', N'Women', N'Dresses', N'Two-Tone Sleeveless Sheath Dress', N'4', 314.95, N'Available'),
(379374, N'Shiseido', N'Women', N'Beauty', N'Zen Perfumed Shower Gel', N'3.3 Oz',35.00, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice, SaleStatus)
VALUES(248592, N'Women', N'Skirts', N'Petite Tech Stretch Seamed Pencil Skirt', N'4', 100.00, N'Processing'),
(359844, N'Women', N'Blouse', N'Short Sleeve Tie-Neck Blouse', N'L', 49.95, N'Available'),
(808031, N'Women', N'Shirts', N'Cotton Cap Sleeve V-Neck Top', N'M', 9.95, N'Processing');
GO
INSERT INTO StoreItems
VALUES(757947, N'Polo Ralph Lauren', N'Men', N'Shirts', N'Classic-Fit Washed Chino Shirt', N'Medium', 79.50, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice)
VALUES(294725, N'Women', N'Handbags', N'Perfect Tote', 295.00);
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(264826, N'Cole Haan', N'Women', N'Handbags', N'Saddle East/West Leather Hobo Bag', 345.50);
GO
INSERT INTO StoreItems
VALUES(684604, N'Ralph Lauren', N'Girls', N'Shirts', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95, N'Available'),
(201147, N'Polo Ralph Lauren', N'Men', N'Sweaters', N'Half-Zip Ribbed Mock-Turtleneck Sweater', N'Large', 79.50, N'Available'),
(158824, N'Mulberribush', N'Boys', N'Pants', N'2-7 Twill Flat-Front Pants', N'2T', 24.50, N'Available');
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, ItemSize, UnitPrice)
VALUES(927940, N'Women', N'Jackets', N'Petite New Femme Jacket', N'6', 210.00);
GO
INSERT INTO StoreItems
VALUES(790279, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'2', 265.50, N'Available'),
(957492, N'Nicole Miller', N'Women', N'Dresses', N'Two-Tone Sleeveless Sheath Dress', N'10', 314.95, N'Available'),
(557504, N'Trina', N'Women', N'Dresses', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'10', 265.50, N'Available'),
(779204, N'Wes & Willy', N'Boys', N'Shirts', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long', 4.95, N'Available'),
(864400, N'Trina', N'Women', N'Dresses', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50, N'Available'),
(925749, N'Nicole Miller', N'Women', N'Dresses', N'Two-Tone Sleeveless Sheath Dress', N'4', 314.95, N'Available'),
(628346, N'Fiona', N'Women', N'Shoes', N'Fiona High Heel Boot', N'6.50', 295.00, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(979120, N'Women', N'Dresses', N'Pleated Neck Dress', N'16',180.00);
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, UnitPrice, SaleStatus)
VALUES(294705, n'Women', N'Jewelery', N'Sparkling Flower Drop Earrings', 45, N'Processing');
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, N'Available'),
(900709, N'Nicole Miller', N'Women', N'Dresses', N'Two-Tone Sleeveless Sheath Dress', N'6', 314.95, N'Available')
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'Shirts', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'3T',18.95, N'Available'),
(286401, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'6',265.50, N'Available'),
(927351, N'Adrianna Papell', N'Women', N'Jackets', N'Bubble-Collar Belted Velvet Jacket', N'4',139.00, N'Available'),
(649004, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'12',265.50, N'Available'),
(709274, N'Little Me', N'Baby', N'Babies', N'Baby Bear Footies With Hat', N'8 Months',9.95, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(790402, N'Women', N'Jackets', N'Petite New Femme Jacket', N'2', 210.00);
GO
INSERT INTO StoreItems
VALUES(770240, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50, N'Available'),
(952735, N'Black Brown 1826', N'Men', N'Coats', N'3-Button Top Coat', N'42R', 495.00, N'Available'),
(974815, N'Trina', N'Women', N'Dresses', N'Trina Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'0', 265.50, N'Available'),
(184081, N'Nicole Miller', N'Women', N'Dresses', N'Two-Tone Sleeveless Sheath Dress', N'8', 314.95, N'Available'),
(660284, N'Shiseido', N'Women', N'Beauty', N'Zen Perfumed Shower Gel', N'3.3 Oz', 35.00, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(208405, N'Women', N'Skirts', N'Petite Tech Stretch Seamed Pencil Skirt', N'4', 100.00),
(266860, N'Women', N'Sweaters', N'Silk/Cashmere/Wool Flower Neck Sweater', N'S', 85),
(264800, N'Women', N'Sweaters', N'Silk/Cashmere/Wool Flower Neck Sweater', N'M', 85);
GO
INSERT INTO StoreItems
VALUES(697224, N'Nicole Miller', N'Women', N'Dresses', N'Two-Tone Sleeveless Sheath Dress', N'6', 314.95, N'Available'),
(777351, N'Adrianna Papell', N'Women', N'Jackets', N'Bubble-Collar Belted Velvet Jacket', N'6', 139.00, N'Available'),
(922251, N'Adrianna Papell', N'Women', N'Jackets', N'Bubble-Collar Belted Velvet Jacket', N'8', 139.00, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Manufacturer, Category, SubCategory, ItemName, UnitPrice)
VALUES(972947, N'Cole Haan', N'Women', N'Handbags', N'Saddle East/West Leather Hobo Bag', 345.50);
GO
INSERT INTO StoreItems
VALUES(820284, N'Ralph Lauren', N'Girls', N'Shirts', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95, N'Available'),
(201447, N'Polo Ralph Lauren', N'Men', N'Sweaters', N'Half-Zip Ribbed Mock-Turtleneck Sweater', N'X-Large', 79.50, N'Available');
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'Pants', N'2-7 Twill Flat-Front Pants', N'2T', 24.50, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(602947, N'Women', N'Dresses', N'Pleated Neck Dress', N'16', 180.00),
(234085, N'Women', N'Sweaters', N'Silk/Cashmere/Wool Ruffle Cardigan', N'S', 95),
(164860, N'Women', N'Sweaters', N'Silk/Cashmere/Wool Flower Neck Sweater', N'M', 85),
(294005, N'Women', N'Sweaters', N'Wool/Alpaca Shawl Collar Cardigan', N'XL', 125);
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'Wallets', N'Bar Tab Leather Tab Wallet', 19.95);
GO
INSERT INTO StoreItems
VALUES(119723, N'Ralph Lauren', N'Girls', N'Shirts', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95, N'Available'),
(920001, N'Adrianna Papell', N'Women', N'Jackets', N'Bubble-Collar Belted Velvet Jacket', N'10', 139.00, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(740597, N'Women', N'Jackets', N'Petite New Femme Jacket', N'6', 210.00);
GO
INSERT INTO StoreItems
VALUES(628460, N'Mulberribush', N'Boys', N'Pants', N'2-7 Twill Flat-Front Pants', N'3T', 24.50, N'Available'),
(201107, N'Polo Ralph Lauren', N'Men', N'Sweaters', N'Half-Zip Ribbed Mock-Turtleneck Sweater', N'Medium', 79.50, N'Available'),
(864402, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50, N'Available');
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'Adrianna Papell', N'Women', N'Dresses', N'Taffeta Ruffled Dress & Bolero Jacket', N'4', 288.00, N'Available'),
(611816, N'Adrianna Papell', N'Women', N'Dresses', N'Taffeta Ruffled Dress & Bolero Jacket', N'8', 288.00, N'Available'),
(680006, N'Adrianna Papell', N'Women', N'Dresses', N'Taffeta Ruffled Dress & Bolero Jacket', N'4', 288.00, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(794750, N'Women', N'Jackets', N'Textured Jacket', N'6', 325.00),
(294025, N'Women', N'Sweaters', N'Wool/Alpaca Shawl Collar Cardigan', N'L', 125),
(264860, N'Women', N'Sweaters', N'Silk/Cashmere/Wool Flower Neck Sweater', N'XS', 85);
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, ItemSize, UnitPrice)
VALUES(592733, N'Women', N'Jackets', 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, N'Available'),
(883094, N'Calvin Klein', N'Women', N'Bras', N'365 Microfiber Contour Bra', N'34B', 39.00, N'Available'),
(952749, N'Steve Madden', N'Women', N'Belts', N'Reversible Sequined Skinny Belt', N'Medium', 32.00, N'Available');
GO
INSERT INTO StoreItems
VALUES(641104, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'12', 265.50, N'Available');
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'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'8', 265.50, N'Available');
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, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(592735, N'Women', N'Jackets', N'Petite New Femme Jacket', N'2', 210.00),
(204005, N'Women', N'Sweaters', N'Wool/Alpaca Shawl Collar Cardigan', N'S', 125);
GO
INSERT INTO StoreItems
VALUES(119720, N'Ralph Lauren', N'Girls', N'Shirts', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T',18.95, N'Available'),
(779240, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4',265.50, N'Available'),
(602917, N'Wes & Willy', N'Boys', N'Shirts', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long',4.95, N'Available'),
(297030, N'Sweatheart Rose', N'Girls', N'Dresses', N'2-6X Two-For Polo & Pleated Plaid Dress', N'2',40.00, N'Available'),
(770241, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4',265.50, N'Available');
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'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50, N'Available'),
(273094, N'Calvin Klein', N'Women', N'Bras', N'365 Microfiber Contour Bra', N'32C', 39.00, N'Available'),
(881094, N'Calvin Klein', N'Women', N'Bras', N'365 Microfiber Contour Bra', N'36B', 39.00, N'Available');
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'Coats', N'3-Button Top Coat', N'42R', 495.00, N'Available'),
(779242, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'4', 265.50, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(829411, N'Women', N'Skirts', 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'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'6', 265.50, N'Available'),
(827114, N'Joseph Abbound', N'Boys', N'Shirts', N'Guys 8-20 Blue & Tan Striped Dress Shirt', N'4', 45.00, N'Available');
GO
INSERT INTO StoreItems(ItemNumber, Category, SubCategory, ItemName, ItemSize, UnitPrice)
VALUES(860613, N'Women', N'Shirts', 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'Accessories', N'Perfect Skinny Belt', 30.00);
GO
INSERT INTO StoreItems
VALUES(797064, N'Trina', N'Women', N'Dresses', N'Turk Printed 3/4-Sleeved Wrap Jersey Dress', N'2', 265.50, N'Available'),
(909120, N'Mulberribush', N'Boys', N'Pants', N'2-7 Twill Flat-Front Pants', N'3T', 24.50, N'Available'),
(950709, N'Steve Madden', N'Women', N'Belts', N'Reversible Sequined Skinny Belt', N'Large', 32.00, N'Available'),
(600947, N'Wes & Willy', N'Boys', N'Shirts', N'2-7 Cotton Short-Sleeved Polo Shirt', N'6 Long',4.95, N'Available');
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'Shirts', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'3T',18.95, N'Available'),
(202835, N'Bonnie Jean', N'Girls', N'Dresses', N'Girls 2-6x Two-For Dress & Coat', N'4T',50.00, N'Available'),
(296030, N'Sweatheart Rose', N'Girls', N'Dresses', N'2-6X Two-For Polo & Pleated Plaid Dress', N'4',40.00, N'Available');
GO
-- ===============================================================================
-- Database: FunDS1
-- Table: CustomersOrders
-- Description: This table holds the items that a typical customer 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 nchar(20) not null,
PurchaseDate nvarchar(40) not null default getdate(),
PurchaseTime nvarchar(20) not null,
ItemNumber nchar(10) not null,
ItemName nvarchar(50) not null,
ItemSize nvarchar(32),
UnitPrice money default 0,
CONSTRAINT PK_CustomersOrders Primary Key(CustomerOrderID)
);
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 nchar(20) not null,
PurchaseDate nvarchar(40) not null,
PurchaseTime nvarchar(40) not null,
OrderTotal money not null,
TypeOfPayment nvarchar(20), -- 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
-- =======================================================
-- Database: FunDS1
-- View: AvailableItems
-- Description: This view holds a list of store items that are available for sale.
-- The items in this view come from the StoreItems table
-- =======================================================
CREATE VIEW AvailableItems
AS
SELECT ItemNumber,
Manufacturer,
Category,
SubCategory,
ItemName,
ItemSize,
UnitPrice
FROM StoreItems
WHERE SaleStatus = N'Available';
GO
-- =======================================================
-- Database: FunDS1
-- View: AvailableItems
-- Description: This view holds a list of store items that are available for sale.
-- The items in this view come from the StoreItems table
-- =======================================================
CREATE VIEW SoldItems
AS
SELECT ItemNumber,
Manufacturer,
Category,
SubCategory,
ItemName,
ItemSize,
UnitPrice
FROM StoreItems
WHERE SaleStatus = N'Sold'
GO
-- Home