Advertisement
Guest User

BlackBox DB

a guest
Feb 25th, 2020
204
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 5.50 KB | None | 0 0
  1. /*
  2. =====================================================================
  3. BlackBoxDB
  4.  
  5. Database for movie rental site.
  6. =====================================================================
  7. */
  8.  
  9. USE master
  10. GO
  11. -- Drop databases if they exist to start fresh
  12. IF EXISTS(SELECT * FROM sys.sysdatabases WHERE [name] = 'BlackBoxDB')
  13.    DROP DATABASE BlackBoxDB
  14. GO
  15.  
  16. -- Create database
  17. CREATE DATABASE BlackBoxDB
  18. GO
  19. -- Switch DB
  20. USE BlackBoxDB
  21. GO
  22.  
  23. -- Create Tables
  24. /* Not necessary if creating a new database
  25. IF OBJECT_ID('dbo.Customer', 'u') IS NOT NULL
  26.    DROP TABLE dbo.Customer
  27.  
  28. IF OBJECT_ID('dbo.Movie', 'u') IS NOT NULL
  29.    DROP TABLE dbo.Movie
  30. */
  31.  
  32. CREATE TABLE Customer (
  33.    CustomerID CHAR(8) CONSTRAINT PK_CustomerID PRIMARY KEY,
  34.    Firstname NVARCHAR(50) NOT NULL,
  35.    Lastname NVARCHAR(50) NOT NULL,
  36.    Phone VARCHAR(20) NULL,
  37. )
  38.  
  39. CREATE TABLE Movie (
  40.    MovieID INT CONSTRAINT PK_MovieID PRIMARY KEY,
  41.    Title NVARCHAR(100) NOT NULL,
  42.    Tagline VARCHAR(100) NULL,
  43.    Description VARCHAR(1024) CONSTRAINT DF_Description DEFAULT 'No
  44. description',
  45.    Popularity INT CONSTRAINT CK_Popularity CHECK (Popularity BETWEEN 0 AND
  46. 100),
  47.    RentalFee SMALLMONEY CONSTRAINT DF_RentalFee DEFAULT 1.99
  48. )
  49.  
  50. -- Mapping Table. Shows which customer checkedout which movie
  51. CREATE TABLE CheckOut (
  52.    CustomerID CHAR(8) CONSTRAINT FK_CustomerID REFERENCES
  53. Customer(CustomerID),
  54.    MovieID INT CONSTRAINT FK_MovieID REFERENCES Movie(MovieID)
  55. )
  56.  
  57. -- Populate Customer Table
  58. INSERT INTO Customer
  59. VALUES
  60. ('00081111', 'Donkey', 'Kong', '+1-111-123-1234'),
  61. ('00090001', 'Galaxian', 'Raider', '+1-111-123-1235')
  62.  
  63.  
  64. -- Populate Movie Table
  65. INSERT INTO Movie
  66. (MovieID, Title, Tagline, Popularity)
  67. VALUES
  68. ('1227', 'namehere', 'wow', floor(rand() * 100)),
  69. ('1228', 'namehere', 'wow', floor(rand() * 100))
  70.  
  71.  
  72. =========================================================================
  73. I added an extra part to check if tables exist before deleting them. Not
  74. needed, but useful to know.
  75.  
  76. The constraint names (PK_CustomerID, FK_MovieID, etc.) are completely
  77. optional, but they make constraint management easier because we have a
  78. name or our choosing to work with.
  79.  
  80. You can execute:
  81.  
  82. SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  83.  
  84. or
  85.  
  86. SELECT * FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT'
  87.  
  88. to view all constraints in the database.
  89.  
  90. To only view primary key constraints, you can do something fancy like this:
  91.  
  92. SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  93. WHERE TABLE_NAME = 'Candy' AND CONSTRAINT_TYPE LIKE 'PRIMARY%'
  94.  
  95.  
  96. Using our own names, such as PK_FieldID or SantaClaus, makes it easier
  97. to identify constraints.
  98.  
  99.  
  100. ======================================================================
  101. Here is another SQL script to create CandyDB. It also contains constraints.
  102.  
  103.  
  104.  
  105. /*
  106. Create Databases for Candy Factory
  107.  
  108.    * CandyProduction
  109.    * CandyInventory
  110.  
  111.  
  112.    SQLCMD:
  113.  
  114.        sqlcmd -S DESKTOP-Q7GJKGP\SQLEXPRESS -E -i CandyFactory.sql
  115.  
  116.    BCP:
  117.  
  118.    TO CSV File:
  119.  
  120.        bcp CandyInventory.dbo.Candy OUT Candy2.csv -c -t',' -T -r\n  -S
  121. DESKTOP-Q7GJKGP\SQLEXPRESS
  122.  
  123.    Tab-delimited:
  124.  
  125.        bcp CandyInventory.dbo.Candy OUT Candy.csv -c -T -r\n  -S
  126. DESKTOP-Q7GJKGP\SQLEXPRESS
  127.  
  128.    FROM CSV File
  129.  
  130.        bcp CandyInventory.dbo.Candy IN Candy2.csv -c -t',' -T -r\n  -S
  131. DESKTOP-Q7GJKGP\SQLEXPRESS
  132.  
  133.    Tab-delimited:
  134.  
  135.        bcp CandyProduction.dbo.Staff IN Staff.csv -c  -T -r\n  -S
  136. DESKTOP-Q7GJKGP\SQLEXPRESS
  137.  
  138.  
  139.    Can lead to problems with CSV. Use tabs by default.
  140.  
  141. */
  142.  
  143. USE master
  144. GO
  145.  
  146.  
  147. -- Drop databases if they exist to start fresh
  148. IF EXISTS(SELECT * FROM sys.sysdatabases WHERE [name] = 'CandyProduction')
  149.    DROP DATABASE CandyProduction
  150. GO
  151.  
  152. IF EXISTS(SELECT * FROM sys.sysdatabases WHERE [name] = 'CandyInventory')
  153.    DROP DATABASE CandyInventory
  154. GO
  155.  
  156.  
  157. -- Create databases
  158. CREATE DATABASE CandyProduction
  159. GO
  160.  
  161. CREATE DATABASE CandyInventory
  162. GO
  163.  
  164.  
  165. -- Create Tables
  166.  
  167. -- CandyProduction: Ingredients and the cooking staff
  168. USE CandyProduction
  169. GO
  170.  
  171. CREATE TABLE Ingredient (
  172.    IngredientID INT NOT NULL,
  173.    [Name] VARCHAR(50),
  174.    Sweetness INT,
  175.    OrderID INT,
  176.    CONSTRAINT PK_IngredientID PRIMARY KEY (IngredientID)
  177. )
  178.  
  179. CREATE TABLE Staff (
  180.    StaffID INT NOT NULL,
  181.    Firstname VARCHAR(50) NOT NULL,
  182.    Lastname VARCHAR(50) NOT NULL,
  183.    Nickname VARCHAR(50) NULL,
  184.    [Role] VARCHAR(30) NOT NULL CONSTRAINT DF_Role DEFAULT 'Worker',
  185.    Salary MONEY NULL,
  186.    CONSTRAINT PK_StaffID PRIMARY KEY (StaffID)
  187.    
  188. )
  189.  
  190.  
  191. --CandyInventory: Information about products and candy sales
  192. USE CandyInventory
  193. GO
  194.  
  195. CREATE TABLE Candy (
  196.    CandyID INT CONSTRAINT PK_CandyID PRIMARY KEY,
  197.    [Name] VARCHAR(50) NOT NULL,
  198.    Quantity INT NULL CONSTRAINT DF_Quantity DEFAULT 0,
  199.    Calories INT NULL CONSTRAINT DF_Calories DEFAULT 0,
  200.    Sweetness INT NULL CONSTRAINT DF_Sweetness DEFAULT 0
  201. )
  202.  
  203. CREATE TABLE Sales (
  204.    SalesID INT CONSTRAINT PK_SalesID PRIMARY KEY,
  205.    CandyID INT NOT NULL,
  206.    UnitsSold INT NULL,
  207.    Popularity INT NULL
  208.    CONSTRAINT FK_CandyID FOREIGN KEY (CandyID) REFERENCES Candy(CandyID),
  209.    CONSTRAINT CK_Popularity CHECK (Popularity BETWEEN 0 AND 10)
  210. )
  211.  
  212. CREATE TABLE Customers (
  213.    CustomerID INT CONSTRAINT PK_CustomerID PRIMARY KEY,
  214.    [Name] VARCHAR(50) NOT NULL,
  215.    CandyID INT NULL,
  216.    QuantityOrdered INT NULL,
  217.    OrderDate DATE NULL
  218. )
  219.  
  220.  
  221. INSERT INTO Candy VALUES
  222. (100, 'Bubblegum', 300, 10, 5),
  223. (200, 'Red Hots', 50, 120, 3),
  224. (300, 'Chocolate Munchies', 450, 450, 8)
  225.  
  226.  
  227. ========================================================================
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement