Adding the Database
Data Requirements
To store the data about countries and cities a SQL database will be used. The tables will be very simple and will record:
Countries
- Id
- Country Name
Cities
- Id
- Country Id
- City Name
- City Population
Adding the SQL Database
- Open Microsoft SQL Management Studio and connect to a Database Engine. (If using MS SQL Express, just accept the defaults.)
- Object Explorer should be visible, if not select View > Object Explorer from the menu.
- Right-click on Databases and select New Database...
- Enter a name for the database in the New Database pop-up form. Leave all other entries as defaults.
- Having created the database, highlight it, right-click and select 'New Query' or select 'New Query' from the toolbar.
- Copy the code from below (click the 'Copy' button) and paste into the 'New Query' tab in SQL.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Countries](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
(
[CountryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cities](
[CityId] [int] IDENTITY(1,1) NOT NULL,
[CountryId] [int] NOT NULL,
[CityName] [varchar](50) NOT NULL,
[CityPopulation] [int] NOT NULL,
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
(
[CityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Cities] ADD CONSTRAINT [DF_Cities_CityPopulation] DEFAULT ((0)) FOR [CityPopulation]
GO
ALTER TABLE [dbo].[Cities] WITH CHECK ADD CONSTRAINT [FK_Cities_Countries] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Countries] ([CountryId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Cities] CHECK CONSTRAINT [FK_Cities_Countries]
GO
- Make sure the 'CountriesDb' database is selected and click the 'Execute' button on the toolbar to run the code. You should see a confirmation message to say the command has completed successfully.
The 'CREATE TABLE' sections actually create the tables; the CONSTRAINT sections add the primary keys and the 'ALTER TABLE section at the end of the script adds a foreign key to the Cities table to link it with the Countries table.
- Close the 'New Query' tab; there is no need to save the query.
- There is no need to do this, but expanding 'CountriesDb' and then Tables will show the two new tables. Right-click on the tables and selecting 'Design' will open a pane showing the column names and their attributes.
The database is now created and SQL Management Studio may be closed.
