DEV Community

Kiron Roy
Kiron Roy

Posted on • Updated on

SQL Server Management Studio (Bicycle Example)

⚠️ This article refers to my Scientists and Transport Article.

Download (for windows) SQL Server Management Studio version 18.4.

I used the query window to recreate the tables into a new database called TransportTwo

Alt Text


I. Creating a Database


CREATE DATABASE [TransportTwo]
Enter fullscreen mode Exit fullscreen mode

II. Creating the tables. There are 5 tables total:

  1. dbo.Bicyles
  2. dbo.Cars
  3. dbo.Scientist
  4. dbo.ScientistBicyles
  5. dbo.ScientistCars

dbo. stands for database owner.

1. Bicycle Table
USE [TransportTwo]
GO

/****** Object:  Table [dbo].[Bicycles]    Script Date: 2/16/2020 2:07:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Bicycles](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [BicycleName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Bicycles] PRIMARY KEY CLUSTERED 
(
    [Id] 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

Enter fullscreen mode Exit fullscreen mode
2. Car Table
USE [TransportTwo]
GO

/****** Object:  Table [dbo].[Cars]    Script Date: 2/16/2020 2:10:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Cars](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CarName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED 
(
    [Id] 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

Enter fullscreen mode Exit fullscreen mode
3. Scientist
USE [TransportTwo]
GO

/****** Object:  Table [dbo].[Scientist]    Script Date: 2/16/2020 2:25:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Scientist](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Scientist] PRIMARY KEY CLUSTERED 
(
    [Id] 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



Enter fullscreen mode Exit fullscreen mode
4. ScientistBicyles
USE [TransportTwo]
GO

/****** Object:  Table [dbo].[ScientistBicyles]    Script Date: 2/16/2020 2:17:48 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ScientistBicyles](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ScientistId] [int] NOT NULL,
    [BicycleId] [int] NOT NULL,
 CONSTRAINT [PK_ScientistBicyles] PRIMARY KEY CLUSTERED 
(
    [Id] 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].[ScientistBicyles]  WITH CHECK ADD  CONSTRAINT [FK_ScientistBicyles_Bicycles] FOREIGN KEY([BicycleId])
REFERENCES [dbo].[Bicycles] ([Id])
GO

ALTER TABLE [dbo].[ScientistBicyles] CHECK CONSTRAINT [FK_ScientistBicyles_Bicycles]
GO

ALTER TABLE [dbo].[ScientistBicyles]  WITH CHECK ADD  CONSTRAINT [FK_ScientistBicyles_Scientist] FOREIGN KEY([ScientistId])
REFERENCES [dbo].[Scientist] ([Id])
GO

ALTER TABLE [dbo].[ScientistBicyles] CHECK CONSTRAINT [FK_ScientistBicyles_Scientist]
GO

Enter fullscreen mode Exit fullscreen mode
5. ScientistCars
USE [TransportTwo]
GO

/****** Object:  Table [dbo].[ScientistCars]    Script Date: 2/16/2020 2:22:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ScientistCars](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ScientistId] [int] NOT NULL,
    [CarId] [int] NOT NULL,
 CONSTRAINT [PK_ScientistCars] PRIMARY KEY CLUSTERED 
(
    [Id] 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].[ScientistCars]  WITH CHECK ADD  CONSTRAINT [FK_ScientistCars_Cars] FOREIGN KEY([CarId])
REFERENCES [dbo].[Cars] ([Id])
GO

ALTER TABLE [dbo].[ScientistCars] CHECK CONSTRAINT [FK_ScientistCars_Cars]
GO

ALTER TABLE [dbo].[ScientistCars]  WITH CHECK ADD  CONSTRAINT [FK_ScientistCars_Scientist] FOREIGN KEY([ScientistId])
REFERENCES [dbo].[Scientist] ([Id])
GO

ALTER TABLE [dbo].[ScientistCars] CHECK CONSTRAINT [FK_ScientistCars_Scientist]
GO

Enter fullscreen mode Exit fullscreen mode

Discussion (0)