ITC 4302 Database Design and Implementation

ITC 4302 – Unit VII Project: Query State_CapitalsDatabase
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Introduction: What is SQL? What is the main purpose of the structured query language?
What are SQL comments?
Body: Use the database ‘State_Capitals’, construct five SQL statements for the following:
What is Country Name and Country Population?
What is the capital city of California?
What is the capital with the least population? What is the one with the most population?
What is the size of the largest state? What is the size of the smallest state?
List the region name, state name and capital name for every state in the United States of
America.
*** Only include the SQL statements, not data ***
You must use either single line or block comments to document each of your query. At a
minimum, you need:
Author Info,
Create Date,
Description,
Updated by,
Last updated date.
Example: What is Country Code and Country Name?
Conclusion: Summarize how joins have been used in the above SQL statements to
deliver the right results. Summarize how you would use comments to document your
codes.
Submit your project in a word document format. Your assignment should be at least two pages
in length. APA Style is not required for this assignment.
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,
CONSTRAINT PK_Country 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,
CONSTRAINT PK_Region PRIMARY KEY (Country_Code),
CONSTRAINT FK_Region 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,
CONSTRAINT PK_State PRIMARY KEY (State_Code),
CONSTRAINT FK_State 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,
CONSTRAINT PK_Capital PRIMARY KEY (Capital_Code),
CONSTRAINT FK_Capital FOREIGN KEY (State_Code) references State(State_Code)
)
GO
AL
State_Code
01
Capital_Code
Capital_Name
Montgomery
Capital_Since
1846
Land_Area
155,40
AK
02
Juneau
1906
2716,70
AZ
AR
03
04
Phoenix
Little Rock
1889
1821
474,90
116,20
CA
CO
05
06
Sacramento
Denver
1854
1867
97,20
153,40
CT
07
Hartford
1875
17,30
DE
08
Dover
1777
22,40
FL
09
Tallahassee
1824
95,70
GA
HI
ID
IL
10
11
12
13
Atlanta
Honolulu
Boise
Springfield
1868
1845
1865
1837
131,70
85,70
63,80
54,00
IN
IA
KS
14
15
16
Indianapolis
Des Moines
Topeka
1825
1857
1856
361,50
75,80
56,00
KY
17
Frankfort
1792
14,70
LA
18
Baton Rouge
1880
76,80
ME
19
Augusta
1832
55,40
MD
20
Annapolis
1694
6,73
MA
21
Boston
1630
48,40
MI
22
Lansing
1847
35,00
MN
MS
23
24
Saint Paul
Jackson
1849
1821
52,80
104,90
MO
MT
NE
NV
NH
25
26
27
28
29
Jefferson City
Helena
Lincoln
Carson City
Concord
1826
1875
1867
1861
1808
27,30
14,00
74,60
143,40
64,30
NJ
30
Trenton
1784
7,66
NM
NY
NC
ND
31
32
33
34
Santa Fe
Albany
Raleigh
Bismarck
1610
1797
1792
1883
37,30
21,40
114,60
26,90
OH
35
Columbus
1816
210,30
OK
OR
PA
36
37
38
Oklahoma City
Salem
Harrisburg
1910
1855
1812
607,00
45,70
8,11
RI
39
Providence
1900
18,50
SC
SD
40
41
Columbia
Pierre
1786
1889
125,20
13,00
TN
42
Nashville
1826
473,30
TX
UT
43
44
Austin
Salt Lake City
1839
1858
251,50
109,10
VT
45
Montpelier
1805
10,20
VA
WA
WV
WI
WY
46
47
48
49
50
Richmond
Olympia
Charleston
Madison
Cheyenne
1780
1853
1885
1838
1869
60,10
16,70
31,60
68,70
21,10
Most_Populous_City
No
Municipal_Population
205764
Metropolitan_Population
374536
No
31275
Yes
Yes
1445632
193524
4192887
877091
No
Yes
466488
600158
2527123
2552195
No
124512
1212381
No
36047
162310
No
181412
367413
Yes
Yes
Yes
No
420003
337256
205671
116250
5268860
953207
616561
208182
Yes
Yes
No
829718
203433
127473
1756221
580255
230824
No
25527
70758
No
229553
802484
No
19136
117114
No
38394
Yes
617594
4522858
No
114297
464036
No
Yes
285068
173514
3502891
539057
No
No
No
No
No
43079
28190
258379
55274
42695
149807
74801
302157
No
84913
366513
No
No
No
No
75764
97856
403892
61272
183732
857592
1130490
108779
Yes
787033
1836536
Yes
No
No
580000
154637
49528
1252987
390738
647390
Yes
178042
1630956
Yes
No
131686
13646
913797
No
635710
1582264
No
Yes
842592
186440
1716291
1124197
No
7855
No
No
Yes
No
Yes
204214
46478
51400
233209
59466
1231675
234670
304214
561505
91738
Note
Birmingham is the state’s largest city
Juneau is the largest capital by land area.
Anchorage is the state’s largest city.
Phoenix is the most populous U.S. state capital
and only capital with more than 1 million citizens
The Supreme Court of California is headquartered
in San Francisco. Los Angeles is the state’s largest
city.
Denver was called Denver City until 1882.
Bridgeport is the state’s largest city, but Hartford
is the largest metro area.
Longest-serving capital in terms of statehood.
Wilmington is the state’s largest city.
Jacksonville is the largest city, and Miami has the
largest metro area.
Atlanta is the state capital with the most
populous metro area in the U.S.
Chicago is the state’s largest city.
Indianapolis is the second largest city in the
Midwest.
Wichita is the state’s largest city.
Louisville is the state’s largest city. Frankfort
ranks as the twelfth most populous city in the
state.
New Orleans is the state’s largest city and home
to the Louisiana Supreme Court. Louisiana is
home to the tallest state capitol building.
Augusta was officially made the capital 1827, but
the legislature did not sit there until 1832.
Portland is the state’s largest city.
Annapolis is the third-longest serving capital in
the United States after Santa Fe and Boston. Its
capitol building is the oldest still in use. It is also
the smallest capital by land area. Baltimore is the
state’s largest city.
Boston is the longest continuously serving capital
in the United States. The Boston-WorcesterManchester Combined Statistical Area
encompasses the state capitals of Massachusetts,
New Hampshire, and Rhode Island.
Lansing is the only state capital that is not also
the county seat of the county in which it is
situated.[citation needed ] Detroit is the state’s largest
city.
Minneapolis is the state’s largest city; it and Saint
Paul form the core of the state’s largest
metropolitan area.
Kansas City is the state’s largest city, and Greater
St. Louis is the state’s largest metropolitan area.
Billings is the state’s largest city.
Omaha is the state’s largest city.
Las Vegas is the state’s largest city.
Manchester is the state’s largest city.
Newark is the state’s largest city. Trenton served
as the US capital for a short period in the late
18th century.
Santa Fe is the longest serving capital in the
United States. El Paso del Norte served as the
capital of the Santa Fe de Nuevo México colony-inexile during the Pueblo Revolt of 1680–1692.
Santa Fe has the highest elevation of any state
capital. Albuquerque is the state’s largest city.
New York City is the state’s largest city.
Charlotte is the state’s largest city.
Fargo is the state’s largest city.
Columbus is Ohio’s largest city, and the fourthlargest state capital, but the Cincinnati and
Cleveland metropolitan areas are both larger.
Oklahoma City is the shortest serving current
state capital in the United States.
Portland is the state’s largest city.
Philadelphia is the state’s largest city.
Providence also served as the capital 1636–1686
and 1689–1776. It was one of five co-capitals
1776–1853, and one of two co-capitals
1853–1900.
Columbia is the largest city in South Carolina but
second largest metro area and combined
statistical area behind Greenville.
Sioux Falls is the state’s largest city.
Memphis is the state’s largest city, and Nashville
is the largest metro area.
Houston is the state’s largest city and the
previous capital, and Dallas–Fort Worth is the
largest metro area. Austin is the largest state
capital that is not also the state’s largest city.
Texas would have the tallest state capitol building
if all of the underground work were above
ground.
Montpelier is the least populous U.S. state
capital. Burlington is the state’s largest city.
Virginia Beach is the state’s largest city, and
Northern Virginia is the state’s largest metro
area.
Seattle is the state’s largest city.
Milwaukee is the state’s largest city.
Region_Code
W
SE
SE
W
W
W
NE
NE
SE
SE
W
MW
W
MW
MW
MW
SE
SE
NE
NE
NE
MW
MW
MW
SE
W
SE
MW
MW
NE
NE
W
W
NE
MW
S
W
NE
NE
SE
MW
SE
S
W
SE
NE
W
MW
SE
W
State_Code
AL
AK
AZ
AR
CA
CO
CT
DE
FL
GA
HI
ID
IL
IN
IA
KS
KY
LA
ME
MD
MA
MI
MN
MS
MO
MT
NE
NV
NH
NJ
NM
NY
NC
ND
OH
OK
OR
PA
RI
SC
SD
TN
TX
UT
VT
VA
WA
WV
WI
WY
State_Name
Alabama
Alaska
Arizona
Arkansas
California
Colorado
Connecticut
Delaware
Florida
Georgia
Hawaii
Idaho
Illinois
Indiana
Iowa
Kansas
Kentucky
Louisiana
Maine
Maryland
Massachusetts
Michigan
Minnesota
Mississippi
Missouri
Montana
Nebraska
Nevada
New Hampshire
New Jersey
New Mexico
New York
North Carolina
North Dakota
Ohio
Oklahoma
Oregon
Pennsylvania
Rhode Island
South Carolina
South Dakota
Tennessee
Texas
Utah
Vermont
Virginia
Washington
West Virginia
Wisconsin
Wyoming
Date_of_Statehood
1819
1959
1912
1836
1850
1876
1788
1787
1845
1788
1959
1890
1818
1816
1846
1861
1792
1812
1820
1788
1788
1837
1858
1817
1821
1889
1867
1864
1788
1787
1912
1788
1789
1889
1803
1907
1859
1787
1790
1788
1889
1796
1845
1896
1791
1788
1889
1863
1848
1890
State_Size
50750,00
570641,00
113642,00
52075,00
155973,00
103730,00
4845,00
1955,00
53997,00
57919,00
6423,00
82751,00
55593,00
35870,00
55875,00
81823,00
39732,00
43566,00
30865,00
9775,00
7838,00
56539,00
79617,00
46914,00
68898,00
145556,00
76878,00
109806,00
8969,00
7419,00
121365,00
47224,00
48718,00
68994,00
40953,00
68679,00
96003,00
44820,00
1034,00
30111,00
75898,00
41220,00
261914,00
82168,00
9249,00
39598,00
66582,00
24087,00
54314,00
97105,00
State_Population
4887871
737438
7171646
3013825
39557045
5695564
3572665
967171
21299325
10519475
1420491
1754208
12741080
6691878
3156145
2911505
4468402
4659978
1338404
6042718
6902149
9995915
5611179
2986530
6126452
1062305
1929268
3034392
1356458
8908520
2095428
19542209
10383620
760077
11689442
3943079
4190713
12807060
1057315
5084127
882235
6770010
28701845
3161105
626299
8517685
7535591
1805832
5813568
577737
Region_Code
MW
NE
NE
S
S
SE
W
Region_Name
Midwest
Northeast
Northest
South
South
Southeast
West
Country_Code
USA
Country_Name
United States
Population
Country_Size
298444215
9631420,00
1
Normalization
Brian K. Mozee
Professor Charles Sessions
ITC-4302
Columbia Southern University
October 30, 2023
2
In relational database architecture, normalization is a procedure that reduces duplication
and enhances data integrity by organizing and structuring data. To reduce data duplication and
any abnormalities, it entails dividing a big database into smaller, linked tables. The Boyce-Codd
Normal Form (BCNF) and the Third Normal Form (3NF) are two popular degrees of
normalization.
When a relation is in the Third Normal Form (3NF) of normalization and no non-key
attribute is transitively reliant on the main key, the relation is in the Two-Key Form (2NF). It is
employed to achieve data integrity and lessen data duplication. 3NF is achieved when a table is
in 2NF and there is no transitive dependency for non-prime attributes.
An extension of 3NF, the Boyce-Codd Normal Form (BCNF) is governed by stricter
guidelines than 3NF. P ought to be the super key in the case of, say, P->Q functional
dependence. It is believed that BCNF is more powerful than 3NF. BCNF is a stricter version
where for any dependency A → B, A must be a super key.
Normalization
Country
Region
States
United States
Midwest
Iowa (IA), Illinois (IL), Indiana
(IN), Kansas (KS), Michigan (MI),
Minnesota (MN), Missouri (MO), North
Dakota (ND), Nebraska (NE), Ohio (OH),
South Dakota (SD), Wisconsin (WI)
United States
Northeast
Connecticut (CT), Delaware (DE),
Massachusetts (MA), Maryland (MD),
3
Maine (ME), New Hampshire (NH), New
Jersey (NJ), New York (NY), Pennsylvania
(PA), Rhode Island (RI), Vermont (VT)
United States
South
Oklahoma (OK), Texas (TX)
United States
Southeast
Alabama (AL), Arkansas (AR),
Florida (FL), Georgia (GA), Kentucky
(KY), Louisiana (LA), Mississippi (MS),
North Carolina (NC), South Carolina (SC),
Tennessee (TN), Virginia (VA), West
Virginia (WV)
United States
West
Alaska (AK), Arizona (AZ),
California (CA), Colorado (CO), Hawaii
(HI), Idaho (ID), Montana (MT), New
Mexico (NM), Nevada (NV), Oregon
(OR), Utah (UT), Washington (WA),
Wyoming (WY)
It is evident that every state is a part of a single country’s area. Thus, this may be divided
into two tables:
Table 1: Regions
RegionID
Country
Region
4
Table 2: States
StateID
RegionID
State
According to this design, the RegionID primary key in the Regions database is linked to
by the RegionID foreign key in the States table. With this design, there is less duplication, and
every piece of data (Country, Region, and State) depends solely on the main key.
By separating the data into distinct tables, the normalization procedure has successfully
reduced duplication and avoided data abnormalities. It offers an organized framework for putting
the “State Capitals” database into practice, which facilitates data management, querying, and
upkeep. Depending on the circumstances, 3NF or BCNF may be used; BCNF is the more
stringent choice when it comes to guaranteeing data integrity.
5
References
Database Star. (2022, September 25). Database normalization: A step-by-step-guide with
examples. https://www.databasestar.com/database-normalization/