/*
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)
|
Hi, I copied and paste the scripts above , when I tried to run it, the error messages shows:
“Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘GO’.”
Any suggestions? Thanks!
It might happen if you manually highlight and copy the script from this page. That results on the whole script being on one line which messes things up with the “–” comments. Instead, if this is the issue, try going to the upper right corner of the code window and look for a copy button. When you paste from there it will be formatted correctly and worked when I tested it again. Thanks!
I get these errors
Msg 1801, Level 16, State 3, Line 9
Database ‘ExperimentData’ already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 3
There is already an object named ‘Experiments’ in the database.
Msg 2714, Level 16, State 4, Line 1
There is already an object named ‘FK_Runs_Experiments’ in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘RunValues’ in the database.
Msg 2714, Level 16, State 4, Line 1
There is already an object named ‘FK_RunValues_RunId’ in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
(3 row(s) affected)
(40 row(s) affected)
(120 row(s) affected)
It seems like the database and all of the objects are already in your database. Check the databases node and press F5 to refresh to see if it is there.