/*
The following script will create a very simple database called
ExperimentData. It contains fictional temperature readings fromm
several different experiments. Each experiments has several runs as
well as several different temperature readings for each run. The
database normalizes this into three tables:
- Experiments
- Runs
- RunValues
To create the database, copy this entire script into a new query
window in SQL Server Management Studio (SSMS). Then press F5
to run the query. This will succeed assuming that: a) you are
connected to an instance of SQL Server, b) you have appropriate
permissions to create databases on that instance.
*/

USE [master]
GO

/* Uncomment the following lines to recreate the
ExperimentData database. Warning: this deletes
all data in the ExperimentData database and recreates it. */
--IF (DB_ID('ExperimentData') is not null)
-- DROP DATABASE ExperimentData

-- Create the database:
CREATE DATABASE ExperimentData
GO
USE ExperimentData
GO

-- Create the tables:
CREATE TABLE [dbo].[Experiments](
[ExperimentId] [int] IDENTITY(1,1) NOT NULL,
[Experiment] [nvarchar](255) NULL,
[Description] [nvarchar](max) NULL,
CONSTRAINT [PK_Experiments] PRIMARY KEY CLUSTERED
(
[ExperimentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Runs](
[RunId] [int] IDENTITY(1,1) NOT NULL,
[ExperimentId] [int] NOT NULL,
[RunNumber] [int] NULL,
[RunDate] [date] NULL,
[StartTime] [time](0) NULL,
[EndTime] [time](0) NULL,
CONSTRAINT [PK_Runs] PRIMARY KEY CLUSTERED
(
[RunId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Runs] WITH CHECK ADD CONSTRAINT [FK_Runs_Experiments] FOREIGN KEY([ExperimentId])
REFERENCES [dbo].[Experiments] ([ExperimentId])
GO
ALTER TABLE [dbo].[Runs] CHECK CONSTRAINT [FK_Runs_Experiments]
GO
CREATE TABLE [dbo].[RunValues](
[RunValueId] [bigint] IDENTITY(1,1) NOT NULL,
[RunId] [int] NULL,
[Time] [time](7) NULL,
[Value] [float] NULL,
CONSTRAINT [PK_RunValues] PRIMARY KEY CLUSTERED
(
[RunValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RunValues] WITH CHECK ADD CONSTRAINT [FK_RunValues_RunId] FOREIGN KEY([RunId])
REFERENCES [dbo].[Runs] ([RunId])
GO
ALTER TABLE [dbo].[RunValues] CHECK CONSTRAINT [FK_RunValues_RunId]
GO

-- Insert the test data
INSERT [dbo].[Experiments]
VALUES('Exp1', 'Measure temperature values over time.'),
('Exp2', 'Temperature measurements in second environment'),
('Exp3', 'Third Environment')
INSERT [dbo].[Runs]
VALUES (1, 1, '2010-10-13', '13:00:00', '13:15:00'),
(1, 2, '2010-10-13', '13:16:00', '13:31:00'),
(1, 3, '2010-10-13', '13:32:00', '13:47:00'),
(1, 4, '2010-10-13', '13:48:00', '14:03:00'),
(1, 5, '2010-10-13', '14:04:00', '14:19:00'),
(1, 6, '2010-10-13', '14:20:00', '14:35:00'),
(1, 7, '2010-10-13', '14:36:00', '14:51:00'),
(1, 8, '2010-10-13', '14:52:00', '15:07:00'),
(2, 1, '2010-10-15', '11:00:00', '11:15:00'),
(2, 2, '2010-10-15', '11:17:00', '11:32:00'),
(2, 3, '2010-10-15', '11:34:00', '11:49:00'),
(2, 4, '2010-10-15', '11:51:00', '12:06:00'),
(2, 5, '2010-10-15', '12:08:00', '12:23:00'),
(2, 6, '2010-10-15', '12:25:00', '12:40:00'),
(2, 7, '2010-10-15', '12:42:00', '12:57:00'),
(2, 8, '2010-10-15', '12:59:00', '13:14:00'),
(2, 9, '2010-10-15', '13:16:00', '13:31:00'),
(2, 10, '2010-10-15', '13:33:00', '13:48:00'),
(2, 11, '2010-10-15', '13:50:00', '14:05:00'),
(2, 12, '2010-10-15', '14:07:00', '14:22:00'),
(3, 1, '2010-11-01', '12:44:00', '12:59:00'),
(3, 2, '2010-11-01', '13:03:00', '13:18:00'),
(3, 3, '2010-11-01', '13:22:00', '13:37:00'),
(3, 4, '2010-11-01', '13:41:00', '13:56:00'),
(3, 5, '2010-11-01', '14:00:00', '14:15:00'),
(3, 6, '2010-11-01', '14:19:00', '14:34:00'),
(3, 7, '2010-11-01', '14:38:00', '14:53:00'),
(3, 8, '2010-11-01', '14:57:00', '15:12:00'),
(3, 9, '2010-11-01', '15:16:00', '15:31:00'),
(3, 10, '2010-11-01', '15:35:00', '15:50:00'),
(3, 11, '2010-11-01', '15:54:00', '16:09:00'),
(3, 12, '2010-11-01', '16:13:00', '16:28:00'),
(3, 13, '2010-11-01', '16:32:00', '16:47:00'),
(3, 14, '2010-11-01', '16:51:00', '17:06:00'),
(3, 15, '2010-11-01', '17:10:00', '17:25:00'),
(3, 16, '2010-11-01', '17:29:00', '17:44:00'),
(3, 17, '2010-11-01', '17:48:00', '18:03:00'),
(3, 18, '2010-11-01', '18:07:00', '18:22:00'),
(3, 19, '2010-11-01', '18:26:00', '18:41:00'),
(3, 20, '2010-11-01', '18:45:00', '19:00:00')
INSERT [dbo].[RunValues]
VALUES (1, '13:05:00.0000000', 57),
(2, '13:21:00.0000000', 76),
(3, '13:37:00.0000000', 67),
(4, '13:53:00.0000000', 77),
(5, '14:09:00.0000000', 80),
(6, '14:25:00.0000000', 80),
(7, '14:41:00.0000000', 82),
(8, '14:57:00.0000000', 75),
(9, '11:05:00.0000000', 81),
(10, '11:22:00.0000000', 81),
(11, '11:39:00.0000000', 68),
(12, '11:56:00.0000000', 76),
(13, '12:13:00.0000000', 83),
(14, '12:30:00.0000000', 60),
(15, '12:47:00.0000000', 66),
(16, '13:04:00.0000000', 85),
(17, '13:21:00.0000000', 78),
(18, '13:38:00.0000000', 81),
(19, '13:55:00.0000000', 66),
(20, '14:12:00.0000000', 70),
(21, '12:49:00.0000000', 60),
(22, '13:08:00.0000000', 72),
(23, '13:27:00.0000000', 62),
(24, '13:46:00.0000000', 80),
(25, '14:05:00.0000000', 80),
(26, '14:24:00.0000000', 85),
(27, '14:43:00.0000000', 83),
(28, '15:02:00.0000000', 74),
(29, '15:21:00.0000000', 55),
(30, '15:40:00.0000000', 79),
(31, '15:59:00.0000000', 61),
(32, '16:18:00.0000000', 58),
(33, '16:37:00.0000000', 61),
(34, '16:56:00.0000000', 64),
(35, '17:15:00.0000000', 79),
(36, '17:34:00.0000000', 55),
(37, '17:53:00.0000000', 57),
(38, '18:12:00.0000000', 70),
(39, '18:31:00.0000000', 65),
(40, '18:50:00.0000000', 59),
(1, '13:10:00.0000000', 81),
(2, '13:26:00.0000000', 58),
(3, '13:42:00.0000000', 66),
(4, '13:58:00.0000000', 80),
(5, '14:14:00.0000000', 60),
(6, '14:30:00.0000000', 67),
(7, '14:46:00.0000000', 60),
(8, '15:02:00.0000000', 78),
(9, '11:10:00.0000000', 79),
(10, '11:27:00.0000000', 67),
(11, '11:44:00.0000000', 82),
(12, '12:01:00.0000000', 78),
(13, '12:18:00.0000000', 66),
(14, '12:35:00.0000000', 63),
(15, '12:52:00.0000000', 70),
(16, '13:09:00.0000000', 64),
(17, '13:26:00.0000000', 58),
(18, '13:43:00.0000000', 61),
(19, '14:00:00.0000000', 67),
(20, '14:17:00.0000000', 70),
(21, '12:54:00.0000000', 61),
(22, '13:13:00.0000000', 72),
(23, '13:32:00.0000000', 67),
(24, '13:51:00.0000000', 68),
(25, '14:10:00.0000000', 61),
(26, '14:29:00.0000000', 76),
(27, '14:48:00.0000000', 65),
(28, '15:07:00.0000000', 61),
(29, '15:26:00.0000000', 57),
(30, '15:45:00.0000000', 78),
(31, '16:04:00.0000000', 64),
(32, '16:23:00.0000000', 55),
(33, '16:42:00.0000000', 81),
(34, '17:01:00.0000000', 84),
(35, '17:20:00.0000000', 58),
(36, '17:39:00.0000000', 81),
(37, '17:58:00.0000000', 66),
(38, '18:17:00.0000000', 77),
(39, '18:36:00.0000000', 63),
(40, '18:55:00.0000000', 72),
(1, '13:15:00.0000000', 74),
(2, '13:31:00.0000000', 83),
(3, '13:47:00.0000000', 74),
(4, '14:03:00.0000000', 62),
(5, '14:19:00.0000000', 64),
(6, '14:35:00.0000000', 64),
(7, '14:51:00.0000000', 60),
(8, '15:07:00.0000000', 84),
(9, '11:15:00.0000000', 55),
(10, '11:32:00.0000000', 85),
(11, '11:49:00.0000000', 64),
(12, '12:06:00.0000000', 85),
(13, '12:23:00.0000000', 74),
(14, '12:40:00.0000000', 84),
(15, '12:57:00.0000000', 60),
(16, '13:14:00.0000000', 60),
(17, '13:31:00.0000000', 58),
(18, '13:48:00.0000000', 60),
(19, '14:05:00.0000000', 63),
(20, '14:22:00.0000000', 77),
(21, '12:59:00.0000000', 60),
(22, '13:18:00.0000000', 78),
(23, '13:37:00.0000000', 62),
(24, '13:56:00.0000000', 64),
(25, '14:15:00.0000000', 61),
(26, '14:34:00.0000000', 70),
(27, '14:53:00.0000000', 81),
(28, '15:12:00.0000000', 83),
(29, '15:31:00.0000000', 76),
(30, '15:50:00.0000000', 67),
(31, '16:09:00.0000000', 58),
(32, '16:28:00.0000000', 74),
(33, '16:47:00.0000000', 83),
(34, '17:06:00.0000000', 68),
(35, '17:25:00.0000000', 57),
(36, '17:44:00.0000000', 76),
(37, '18:03:00.0000000', 61),
(38, '18:22:00.0000000', 69),
(39, '18:41:00.0000000', 56),
(40, '19:00:00.0000000', 85)