Database Design CIW

State_Capitals Physical Database

Create primary and secondary keys for the attached unfinishedphysical database design.

CREATE DATABASE STATE_CAPITALS;
GO
USE STATE_CAPITALS;
GO
CREATETABLE Country(
	Country_Code varchar(10) NOT NULL,
	Country_Name varchar(50) NOT NULL,
	Population int NOT NULL,
	Country_Size float NOT NULL
)
GO
CREATE TABLE Region(
	Country_Code varchar(10) NOT NULL,
	Region_Code varchar(10) NOT NULL,
	Region_Name varchar(50) NOT NULL
)
GO
CREATE TABLE State(
	Region_Code varchar(10) NOT NULL,
	State_Code char(2) NOT NULL,
	State_Name varchar(50) NOT NULL,
	Date_of_Statehood int NOT NULL,
	State_Size float NOT NULL,
	State_Population int NOT NULL
)
GO
CREATE TABLE Capital(
	State_Code char(2) NOT NULL,
	Capital_Code tinyint NOT NULL,
	Capital_Name varchar(50) NOT NULL,
	Capital_Since smallint NOT NULL,
	Land_Area float NOT NULL,
	Most_Populous_City bit NOT NULL,
	Municipal_Population int NOT NULL,
	Metropolitan_Population int NULL,
	Note varchar(300) NULL
)
GO

Ensure your submission addresses each of these components:

• Introduction: Explain the process to create a physicaldatabase.
• Body: The attached DDL statements are used to create thephysical database “State_Capitals” that consists of four tables:“Country,” “Region,” “State,” and “Capital.” However, thestatements to create the appropriate primary and foreign keys forthe tables are missing.
• Complete the “State_Capitals” physical database by creating theappropriate primary and foreign keys for them.
• Conclusion: Summarize how DDL is used to create a physicaldatabase objects and the use of primary and foreign keys inrelational database.

Submit your project in a word documentformat