Question

Database Design CIW

State_Capitals Physical Database

Create primary and secondary keys for the attached unfinished physical database design.

CREATE DATABASE STATE_CAPITALS;
GO
USE STATE_CAPITALS;
GO
CREATE TABLE 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 physical database.
• Body: The attached DDL statements are used to create the physical database “State_Capitals” that consists of four tables: “Country,” “Region,” “State,” and “Capital.” However, the statements to create the appropriate primary and foreign keys for the tables are missing.
• Complete the “State_Capitals” physical database by creating the appropriate primary and foreign keys for them.
• Conclusion: Summarize how DDL is used to create a physical database objects and the use of primary and foreign keys in relational database.

Submit your project in a word document format

Answer 1

• Primary key :
  • Primary key is used to uniquely identify each record in the table
  • Primary key value can not be duplicate and null
• Foreign key :
  • Primary key of one table is used as foreign key in another table to maintain relationship between one or more table

Below table shows primary and foreign key for each table

Sr.No	Table Name	Primary key	Foreign key	Reference table
1	Country	Country_Code	--	--
2	Region	Region_Code	Country_Code	Country
3	State	State_Code	Region_Code	Region
4	Capital	Capital_Code	State_Code	State

*********************************

DDL script :

CREATE DATABASE STATE_CAPITALS;  
GO      
USE STATE_CAPITALS;      
GO      
CREATE TABLE Country(  
Country_Code varchar(10) NOT NULL,  
Country_Name varchar(50) NOT NULL,  
Population int NOT NULL,  
Country_Size float NOT NULL   ,
primary key (Country_Code)
);      
GO      
CREATE TABLE Region(      
Country_Code varchar(10) NOT NULL,  
Region_Code varchar(10) NOT NULL,  
Region_Name varchar(50) NOT NULL,
primary key (Region_Code),
foreign key (Country_Code) references Country (Country_Code)
);      
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,
primary key (State_Code),
foreign key (Region_Code) references region (Region_Code)
);  
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,
primary key (Capital_Code),
foreign key (State_Code) references State (State_Code)
);      
GO