Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- =====================================================================
- BlackBoxDB
- Database for movie rental site.
- =====================================================================
- */
- USE master
- GO
- -- Drop databases if they exist to start fresh
- IF EXISTS(SELECT * FROM sys.sysdatabases WHERE [name] = 'BlackBoxDB')
- DROP DATABASE BlackBoxDB
- GO
- -- Create database
- CREATE DATABASE BlackBoxDB
- GO
- -- Switch DB
- USE BlackBoxDB
- GO
- -- Create Tables
- /* Not necessary if creating a new database
- IF OBJECT_ID('dbo.Customer', 'u') IS NOT NULL
- DROP TABLE dbo.Customer
- IF OBJECT_ID('dbo.Movie', 'u') IS NOT NULL
- DROP TABLE dbo.Movie
- */
- CREATE TABLE Customer (
- CustomerID CHAR(8) CONSTRAINT PK_CustomerID PRIMARY KEY,
- Firstname NVARCHAR(50) NOT NULL,
- Lastname NVARCHAR(50) NOT NULL,
- Phone VARCHAR(20) NULL,
- )
- CREATE TABLE Movie (
- MovieID INT CONSTRAINT PK_MovieID PRIMARY KEY,
- Title NVARCHAR(100) NOT NULL,
- Tagline VARCHAR(100) NULL,
- Description VARCHAR(1024) CONSTRAINT DF_Description DEFAULT 'No
- description',
- Popularity INT CONSTRAINT CK_Popularity CHECK (Popularity BETWEEN 0 AND
- 100),
- RentalFee SMALLMONEY CONSTRAINT DF_RentalFee DEFAULT 1.99
- )
- -- Mapping Table. Shows which customer checkedout which movie
- CREATE TABLE CheckOut (
- CustomerID CHAR(8) CONSTRAINT FK_CustomerID REFERENCES
- Customer(CustomerID),
- MovieID INT CONSTRAINT FK_MovieID REFERENCES Movie(MovieID)
- )
- -- Populate Customer Table
- INSERT INTO Customer
- VALUES
- ('00081111', 'Donkey', 'Kong', '+1-111-123-1234'),
- ('00090001', 'Galaxian', 'Raider', '+1-111-123-1235')
- -- Populate Movie Table
- INSERT INTO Movie
- (MovieID, Title, Tagline, Popularity)
- VALUES
- ('1227', 'namehere', 'wow', floor(rand() * 100)),
- ('1228', 'namehere', 'wow', floor(rand() * 100))
- =========================================================================
- I added an extra part to check if tables exist before deleting them. Not
- needed, but useful to know.
- The constraint names (PK_CustomerID, FK_MovieID, etc.) are completely
- optional, but they make constraint management easier because we have a
- name or our choosing to work with.
- You can execute:
- SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
- or
- SELECT * FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT'
- to view all constraints in the database.
- To only view primary key constraints, you can do something fancy like this:
- SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
- WHERE TABLE_NAME = 'Candy' AND CONSTRAINT_TYPE LIKE 'PRIMARY%'
- Using our own names, such as PK_FieldID or SantaClaus, makes it easier
- to identify constraints.
- ======================================================================
- Here is another SQL script to create CandyDB. It also contains constraints.
- /*
- Create Databases for Candy Factory
- * CandyProduction
- * CandyInventory
- SQLCMD:
- sqlcmd -S DESKTOP-Q7GJKGP\SQLEXPRESS -E -i CandyFactory.sql
- BCP:
- TO CSV File:
- bcp CandyInventory.dbo.Candy OUT Candy2.csv -c -t',' -T -r\n -S
- DESKTOP-Q7GJKGP\SQLEXPRESS
- Tab-delimited:
- bcp CandyInventory.dbo.Candy OUT Candy.csv -c -T -r\n -S
- DESKTOP-Q7GJKGP\SQLEXPRESS
- FROM CSV File
- bcp CandyInventory.dbo.Candy IN Candy2.csv -c -t',' -T -r\n -S
- DESKTOP-Q7GJKGP\SQLEXPRESS
- Tab-delimited:
- bcp CandyProduction.dbo.Staff IN Staff.csv -c -T -r\n -S
- DESKTOP-Q7GJKGP\SQLEXPRESS
- Can lead to problems with CSV. Use tabs by default.
- */
- USE master
- GO
- -- Drop databases if they exist to start fresh
- IF EXISTS(SELECT * FROM sys.sysdatabases WHERE [name] = 'CandyProduction')
- DROP DATABASE CandyProduction
- GO
- IF EXISTS(SELECT * FROM sys.sysdatabases WHERE [name] = 'CandyInventory')
- DROP DATABASE CandyInventory
- GO
- -- Create databases
- CREATE DATABASE CandyProduction
- GO
- CREATE DATABASE CandyInventory
- GO
- -- Create Tables
- -- CandyProduction: Ingredients and the cooking staff
- USE CandyProduction
- GO
- CREATE TABLE Ingredient (
- IngredientID INT NOT NULL,
- [Name] VARCHAR(50),
- Sweetness INT,
- OrderID INT,
- CONSTRAINT PK_IngredientID PRIMARY KEY (IngredientID)
- )
- CREATE TABLE Staff (
- StaffID INT NOT NULL,
- Firstname VARCHAR(50) NOT NULL,
- Lastname VARCHAR(50) NOT NULL,
- Nickname VARCHAR(50) NULL,
- [Role] VARCHAR(30) NOT NULL CONSTRAINT DF_Role DEFAULT 'Worker',
- Salary MONEY NULL,
- CONSTRAINT PK_StaffID PRIMARY KEY (StaffID)
- )
- --CandyInventory: Information about products and candy sales
- USE CandyInventory
- GO
- CREATE TABLE Candy (
- CandyID INT CONSTRAINT PK_CandyID PRIMARY KEY,
- [Name] VARCHAR(50) NOT NULL,
- Quantity INT NULL CONSTRAINT DF_Quantity DEFAULT 0,
- Calories INT NULL CONSTRAINT DF_Calories DEFAULT 0,
- Sweetness INT NULL CONSTRAINT DF_Sweetness DEFAULT 0
- )
- CREATE TABLE Sales (
- SalesID INT CONSTRAINT PK_SalesID PRIMARY KEY,
- CandyID INT NOT NULL,
- UnitsSold INT NULL,
- Popularity INT NULL
- CONSTRAINT FK_CandyID FOREIGN KEY (CandyID) REFERENCES Candy(CandyID),
- CONSTRAINT CK_Popularity CHECK (Popularity BETWEEN 0 AND 10)
- )
- CREATE TABLE Customers (
- CustomerID INT CONSTRAINT PK_CustomerID PRIMARY KEY,
- [Name] VARCHAR(50) NOT NULL,
- CandyID INT NULL,
- QuantityOrdered INT NULL,
- OrderDate DATE NULL
- )
- INSERT INTO Candy VALUES
- (100, 'Bubblegum', 300, 10, 5),
- (200, 'Red Hots', 50, 120, 3),
- (300, 'Chocolate Munchies', 450, 450, 8)
- ========================================================================
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement