/******************************************************
 *
 * Name:         exploring-views-part4.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     01-06-2013
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    Talk about views - part 4.
 *
 ******************************************************/


--
-- 4A - Create the sample database
--

-- Which database to use.
USE [master]
GO

-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS')
DROP DATABASE [AUTOS]
GO

-- Add new databases.
CREATE DATABASE [AUTOS] ON PRIMARY
( NAME = N'AUTOS_DAT', FILENAME = N'C:\MSSQL\DATA\AUTOS-DAT.MDF' , SIZE = 32MB , MAXSIZE = UNLIMITED, FILEGROWTH = 4MB)
LOG ON
( NAME = N'AUTOS_LOG', FILENAME = N'C:\MSSQL\LOG\AUTOS-LOG.LDF' , SIZE = 4MB , MAXSIZE = UNLIMITED , FILEGROWTH = 1MB)
GO


--
-- 4B - Create the sample tables with good contraints
--

-- Do not allow data truncation
SET ANSI_WARNINGS ON;
GO

-- Use the database
USE [AUTOS];
GO

-- Create makes table, add constraints with alter
CREATE TABLE [DBO].[MAKES]
(
    MAKER_ID INT IDENTITY(1,1),
    MAKER_NM VARCHAR(25),
    START_YR SMALLINT,
    END_YR SMALLINT 
);
GO

-- User defined constraint
ALTER TABLE MAKES
ADD CONSTRAINT CHK_START_YR CHECK (ISNULL(START_YR, 0) >= 1903 and ISNULL(START_YR, 0) <= YEAR(GETDATE()) + 1);
GO

-- User defined constraint
ALTER TABLE MAKES
ADD CONSTRAINT CHK_END_YR CHECK ( (END_YR IS NULL) OR (END_YR >= 1903 AND END_YR <= YEAR(GETDATE()) + 1) );
GO

-- Entity constraint - natural key
ALTER TABLE MAKES
ADD CONSTRAINT UNQ_MAKER_NM UNIQUE (MAKER_NM); 
GO

-- Entity constraint - surrogate key
ALTER TABLE MAKES
ADD CONSTRAINT PK_MAKER_ID PRIMARY KEY CLUSTERED (MAKER_ID);
GO


-- Create models table with inline constraints
CREATE TABLE [DBO].[MODELS]
(
    MODEL_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    MODEL_NM VARCHAR(25) NOT NULL CONSTRAINT DF_MODEL_NM DEFAULT 'UNKNOWN' CONSTRAINT UNQ_MODEL_NM UNIQUE (MODEL_NM),
    MODEL_YR SMALLINT CONSTRAINT CHK_MDOEL_YR CHECK (ISNULL(MODEL_YR, 0) >= 1903 and ISNULL(MODEL_YR,0) <= YEAR(GETDATE()) + 1),
    MSRP SMALLMONEY NOT NULL CONSTRAINT DF_MSRP DEFAULT 0,
    MAKER_ID INT NOT NULL CONSTRAINT FK_MAKES FOREIGN KEY (MAKER_ID)
    REFERENCES [DBO].[MAKES] (MAKER_ID)
);
GO


--
-- 4C - Add good data to the tables
--


-- Add data to makes
INSERT INTO [DBO].[MAKES] (MAKER_NM, START_YR, END_YR) VALUES
    ('Chevrolet', 1912, Null),
    ('Dodge', 1915, Null),
    ('Ford', 1903, Null),
    ('Lincoln', 1917, Null),
    ('Mercury', 1938, 2011);
GO

-- Review data
SELECT * FROM [DBO].[MAKES]
GO

-- Add data
INSERT INTO MODELS (MODEL_NM, MODEL_YR, MSRP, MAKER_ID) VALUES
    ('Corvette', 2013, 49600.00, 1),
    ('Ram 1500', 2013, 22589.99, 2),
    ('Mustang Boss 302', 2013, 42200.00, 3),
    ('Navigator', 2013, 57775.00, 4),
    ('Mariner', 2011, 23082.00, 5)
GO

-- Review data
SELECT * FROM [DBO].[MODELS]
GO



--
-- 4D - Demonstrate updatable views
--

CREATE VIEW DBO.VW_JOIN_MAKES_2_MODELS
AS
  SELECT 
      MA.[MAKER_ID]
    , MA.[MAKER_NM]
    , MA.[START_YR]
    , MA.[END_YR]
    , MO.[MODEL_ID]
    , MO.[MODEL_NM]
    , MO.[MODEL_YR]
    , MO.[MSRP]
    , MO.[MAKER_ID] as MAKER_ID2
  FROM 
    [dbo].[MAKES] MA JOIN [dbo].[MODELS] MO ON MA.MAKER_ID = MO.MAKER_ID 
GO


-- Review data
SELECT * FROM [DBO].[VW_JOIN_MAKES_2_MODELS]
GO


-- Update single field
UPDATE DBO.VW_JOIN_MAKES_2_MODELS
SET MAKER_NM = 'Dodge Ram'
WHERE MAKER_ID = 2;


-- Update single field
UPDATE DBO.VW_JOIN_MAKES_2_MODELS
SET MODEL_NM = '1500'
WHERE MAKER_ID = 2;


-- Update two field
UPDATE DBO.VW_JOIN_MAKES_2_MODELS
SET 
    MAKER_NM = 'Dodge'
  , MODEL_NM = 'Ram 1500'
WHERE MAKER_ID = 2;


-- Show the data 
SELECT * FROM DBO.VW_JOIN_MAKES_2_MODELS


-- Add data to one table
INSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (MAKER_NM, START_YR, END_YR)
    VALUES ('Tesla', 2007, NULL);


-- Add data to one table
INSERT INTO DBO.VW_JOIN_MAKES_2_MODELS (MAKER_ID2, MODEL_NM, MODEL_YR, MSRP)
    VALUES (6, 'Model S', 2013, 87400);


-- Delete from both tables
DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MAKER_ID = 6 AND MAKER_ID2 = 6;

-- Delete from parent tables
DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MODEL_ID = 6;

-- Delete from child table
DELETE FROM DBO.VW_JOIN_MAKES_2_MODELS WHERE MAKER_ID2 = 6;


--
-- 4E - Demonstate aggregates that do not allow updates
--

-- Add data
INSERT INTO MODELS (MODEL_NM, MODEL_YR, MSRP, MAKER_ID) VALUES
    ('Model X', 2014, 100000.00, 6)
GO


--
-- 4F - Create a view to demonstrate updates
--

CREATE VIEW DBO.VW_MAKES_AVG_MODEL_COST
AS
  SELECT 
      MA.[MAKER_ID]
    , MA.[MAKER_NM]
    , AVG(MO.[MSRP]) AS AVG_MSRP
  FROM 
    [dbo].[MAKES] MA JOIN [dbo].[MODELS] MO ON MA.MAKER_ID = MO.MAKER_ID 
  GROUP BY
      MA.[MAKER_ID]
    , MA.[MAKER_NM]
GO


-- Show the data 
SELECT * FROM DBO.VW_MAKES_AVG_MODEL_COST
GO

-- Try an update?
UPDATE DBO.VW_MAKES_AVG_MODEL_COST
SET AVG_MSRP = 50000
WHERE MAKER_ID = 6;
GO

-- Try an insert?
INSERT INTO DBO.VW_MAKES_AVG_MODEL_COST VALUES
    (7, 'Toyota', 50000);
GO

-- Try a delete?
DELETE FROM DBO.VW_MAKES_AVG_MODEL_COST
WHERE MAKER_ID = 6;
GO