Created
May 13, 2020 20:29
-
-
Save gregori/e728efcf9e3bbf54990a6ec43aad3425 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
-------------------------------------------------------------------- | |
© 2017 sqlservertutorial.net All Rights Reserved | |
-------------------------------------------------------------------- | |
Name : BikeStores | |
Link : http://www.sqlservertutorial.net/load-sample-database/ | |
Version: 1.0 | |
-------------------------------------------------------------------- | |
*/ | |
CREATE DATABASE BikeStores | |
GO | |
USE BikeStores | |
GO | |
-- create schemas | |
CREATE SCHEMA production; | |
go | |
CREATE SCHEMA sales; | |
go | |
-- create tables | |
CREATE TABLE production.categories ( | |
category_id INT IDENTITY (1, 1) PRIMARY KEY, | |
category_name VARCHAR (255) NOT NULL | |
); | |
CREATE TABLE production.brands ( | |
brand_id INT IDENTITY (1, 1) PRIMARY KEY, | |
brand_name VARCHAR (255) NOT NULL | |
); | |
CREATE TABLE production.products ( | |
product_id INT IDENTITY (1, 1) PRIMARY KEY, | |
product_name VARCHAR (255) NOT NULL, | |
brand_id INT NOT NULL, | |
category_id INT NOT NULL, | |
model_year SMALLINT NOT NULL, | |
list_price DECIMAL (10, 2) NOT NULL, | |
FOREIGN KEY (category_id) REFERENCES production.categories (category_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
FOREIGN KEY (brand_id) REFERENCES production.brands (brand_id) ON DELETE CASCADE ON UPDATE CASCADE | |
); | |
CREATE TABLE sales.customers ( | |
customer_id INT IDENTITY (1, 1) PRIMARY KEY, | |
first_name VARCHAR (255) NOT NULL, | |
last_name VARCHAR (255) NOT NULL, | |
phone VARCHAR (25), | |
email VARCHAR (255) NOT NULL, | |
street VARCHAR (255), | |
city VARCHAR (50), | |
state VARCHAR (25), | |
zip_code VARCHAR (5) | |
); | |
CREATE TABLE sales.stores ( | |
store_id INT IDENTITY (1, 1) PRIMARY KEY, | |
store_name VARCHAR (255) NOT NULL, | |
phone VARCHAR (25), | |
email VARCHAR (255), | |
street VARCHAR (255), | |
city VARCHAR (255), | |
state VARCHAR (10), | |
zip_code VARCHAR (5) | |
); | |
CREATE TABLE sales.staffs ( | |
staff_id INT IDENTITY (1, 1) PRIMARY KEY, | |
first_name VARCHAR (50) NOT NULL, | |
last_name VARCHAR (50) NOT NULL, | |
email VARCHAR (255) NOT NULL UNIQUE, | |
phone VARCHAR (25), | |
active tinyint NOT NULL, | |
store_id INT NOT NULL, | |
manager_id INT, | |
FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
FOREIGN KEY (manager_id) REFERENCES sales.staffs (staff_id) ON DELETE NO ACTION ON UPDATE NO ACTION | |
); | |
CREATE TABLE sales.orders ( | |
order_id INT IDENTITY (1, 1) PRIMARY KEY, | |
customer_id INT, | |
order_status tinyint NOT NULL, | |
-- Order status: 1 = Pending; 2 = Processing; 3 = Rejected; 4 = Completed | |
order_date DATE NOT NULL, | |
required_date DATE NOT NULL, | |
shipped_date DATE, | |
store_id INT NOT NULL, | |
staff_id INT NOT NULL, | |
FOREIGN KEY (customer_id) REFERENCES sales.customers (customer_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
FOREIGN KEY (staff_id) REFERENCES sales.staffs (staff_id) ON DELETE NO ACTION ON UPDATE NO ACTION | |
); | |
CREATE TABLE sales.order_items ( | |
order_id INT, | |
item_id INT, | |
product_id INT NOT NULL, | |
quantity INT NOT NULL, | |
list_price DECIMAL (10, 2) NOT NULL, | |
discount DECIMAL (4, 2) NOT NULL DEFAULT 0, | |
PRIMARY KEY (order_id, item_id), | |
FOREIGN KEY (order_id) REFERENCES sales.orders (order_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
FOREIGN KEY (product_id) REFERENCES production.products (product_id) ON DELETE CASCADE ON UPDATE CASCADE | |
); | |
CREATE TABLE production.stocks ( | |
store_id INT, | |
product_id INT, | |
quantity INT, | |
PRIMARY KEY (store_id, product_id), | |
FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
FOREIGN KEY (product_id) REFERENCES production.products (product_id) ON DELETE CASCADE ON UPDATE CASCADE | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment