Job Satifactions and Autonomy
Essay by people • August 29, 2011 • Study Guide • 7,650 Words (31 Pages) • 1,229 Views
test-- After Creating N4BI Database.Run this script to create all tables, views, and stored procedures.
-- No modifications needed to run the script only connect to the server the N4BI database exists.
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
USE [N4BI]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[bi_etl_log]') AND type in ('U'))
BEGIN
CREATE TABLE dbo.bi_etl_log(
log_time datetime NOT NULL default getdate(),
log_type nvarchar(10) NOT NULL DEFAULT 'INFO',
procedure_running nvarchar(255),
error_message nvarchar(255) NULL,
description nvarchar(255) NULL
) ON [N4BI_DATA]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[bi_version]') AND type in ('U'))
BEGIN
CREATE TABLE dbo.bi_version(
version_number bigint NOT NULL,
schema_number BIGINT NOT NULL DEFAULT 0,
description nvarchar(255) NOT NULL,
date_installed datetime NOT NULL,
CONSTRAINT [PK_bi_version] PRIMARY KEY CLUSTERED (version_number) ON [N4BI_DATA]
) ON [N4BI_DATA]
END
GO
TRUNCATE TABLE bi_version
GO
INSERT INTO bi_version (version_number, description, date_installed) VALUES (020100, 'Initial Install', getdate())
GO
-----------------------------------------------------------------------------------------------------
--Dimention tables.
-----------------------------------------------------------------------------------------------------
-- Hirarchy Dimention Tables.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[Dm_Operator]') AND type in ('U'))
BEGIN
CREATE TABLE Dm_Operator (
opr_gkey BIGINT NOT NULL,
operator_id NVARCHAR(20),
operator_name NVARCHAR(30),
CONSTRAINT PK_Dm_Operator PRIMARY KEY (opr_gkey) ON [N4BI_DATA]
)
END
--INSERT INTO Dm_Operator (opr_gkey, operator_id, operator_name)
-- VALUES (0, 'Unknown', 'Unknown')
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[Dm_Complex]') AND type in ('U'))
BEGIN
CREATE TABLE dbo.Dm_Complex (
opr_gkey BIGINT,
cpx_gkey BIGINT NOT NULL,
complex_id NVARCHAR(20),
complex_name NVARCHAR(30),
CONSTRAINT PK_Dm_Complex PRIMARY KEY CLUSTERED ( cpx_gkey) ON [N4BI_DATA]
) ON [N4BI_DATA]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[Dm_Facility]') AND type in ('U'))
BEGIN
CREATE TABLE dbo.Dm_Facility(
opr_gkey BIGINT ,
cpx_gkey BIGINT ,
fcy_gkey BIGINT NOT NULL,
facility_id NVARCHAR(20) NULL,
facility_name NVARCHAR(30) NULL,
life_cycle_state NVARCHAR(3) NULL,
jms_provider NVARCHAR(15) NULL,
CONSTRAINT PK_Dm_Facility PRIMARY KEY CLUSTERED (fcy_gkey) ON [N4BI_DATA]
) ON [N4BI_DATA]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[Dm_Yard]') AND type in ('U'))
BEGIN
CREATE TABLE Dm_Yard (
opr_gkey BIGINT ,
cpx_gkey BIGINT ,
fcy_gkey BIGINT ,
yard_gkey BIGINT NOT NULL,
yard_name NVARCHAR(40),
yard_id NVARCHAR(20),
CONSTRAINT PK_Dm_Yard PRIMARY KEY (yard_gkey)
) ON [N4BI_DATA]
END
GO
--------------------------Dimention Tables
----------- Dm_Crane_Activity
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[Dm_Crane_Activity]') AND type in ('U'))
BEGIN
CREATE
...
...