Final SQL Project

Global Demographic Insights System (GDIS)

Project Overview

You are given a dataset demographic [Link] containing demographic indicators for
different regions and years.
You must design and implement a complete SQL solution that supports:

• Database design and normalization (at least 3NF)

• Data importing and mapping

• Structuring tables with constraints

• ALTER / ADD / DROP operations

• INSERT, UPDATE, DELETE operations

• Filtering and sorting queries

• Joins and Subqueries

• Views and Functions

• Stored Procedures and Triggers

• Transactions and Integrity

All work must be done in a single database called DemographicDB.

Dataset (Column Snapshot)

The CSV file contains columns such as (not exhaustive):

Column Description (short)

SortOrder Sorting order ID
LocID Location numeric ID
Notes Notes or remarks
ISO3 code, ISO2 code, SDMX code Location codes
LocTypeID, LocTypeName Location type (e.g., region)

1
ParentID Parent location ID
Location Location/Region name (e.g., “ADB re-
gion: Central and West Asia”)
VarID, Variant Variant of projection (e.g., Medium)
Time Year
TPopulation1Jan, TPopulation1July Total population (1 Jan, 1 July)
TPopulationMale1July, TPopulationFe- Population by sex
male1July
PopDensity Population density
PopSexRatio Sex ratio
MedianAgePop Median age of population
NatChange, NatChangeRT Natural change and rate
PopChange, PopGrowthRate Population change and growth rate
DoublingTime Doubling time (years)
Births, Births1519, CBR, TFR, NRR, Fertility-related indicators
MAC, SRB
Deaths, DeathsMale, DeathsFemale, Death and mortality indicators
CDR
LEx, LExMale, LExFemale Life expectancy at birth
LE15, LE65, LE80 Life expectancy at ages 15, 65, 80
(male/female variants)
InfantDeaths, IMR, LBsurvivingAge1 Infant mortality indicators
Under5Deaths, Q5, Q0040, Q0060, Under-5 and survival probabilities
Q1550, Q1560
NetMigrations, CNMR Migration-related indicators

2
Part 1 – Database Setup & Data Import

1. Create a new database named DemographicDB.

2. Create a staging table Staging Demographics whose columns match the CSV
header (same names and data types chosen appropriately).

3. Import the file demographic [Link] into Staging Demographics using SSMS
Import Wizard or BULK INSERT.

4. Verify:

• Total row count

• Distinct years (Time)
• Distinct locations (LocID, Location)

Part 2 – Structuring, Constraints & Normalization

5. Design a normalized schema (at least 3NF) with the following core tables (you may
extend if needed):

• Location
LocationID (PK), LocID, LocationName, ISO3 code, ISO2 code, SDMX code,
LocTypeID (FK), ParentLocationID (self-FK, nullable)
• LocationType
LocTypeID (PK), LocTypeName
• Variant
VariantID (PK), VarID, VariantName
• DemographicFact
DemographicID (PK), LocationID (FK), VariantID (FK), Year (Time), plus
key measures such as:
TPopulation1Jan, TPopulation1July, TPopulationMale1July, TPopulationFemale1July,
PopDensity, PopSexRatio, MedianAgePop,
NatChange, NatChangeRT, PopChange, PopGrowthRate, DoublingTime, Births,
Deaths, TFR, LEx, NetMigrations, etc.

6. Apply constraints:

• Primary keys on each table.

• Foreign keys for relationships between LocationType, Location, Variant, and
DemographicFact.

3
 • Reasonable CHECK constraints, e.g.:
– Year between 1950 and 2100
– TPopulation1Jan ≥ 0
– PopGrowthRate between -20 and 20

7. Perform ALTER / ADD / DROP:

• Add a column RegionGroup to Location.

• Modify PopGrowthRate datatype to a suitable precise type (e.g. DECIMAL(6,3)).
• Drop one numeric column from DemographicFact that you decide is unneces-
sary.

8. Populate normalized tables:

• Insert distinct location types into LocationType.

• Insert distinct variants into Variant.
• Insert distinct locations into Location.
• Insert all fact rows from Staging Demographics into DemographicFact, re-
solving FK IDs via joins or subqueries.

Part 3 – DML, Filtering & Sorting

9. Insert a hypothetical future record into DemographicFact for year 2100 for one
chosen location with sample values.

10. Add a field (or computed column) TotalPopulation = TPopulationMale1July +

TPopulationFemale1July, and backfill it (if stored as normal column).

11. Insert a row with Year = 1800 and then write a DELETE statement to remove all
rows where Year < 1950.

12. Write queries to:

• List the top 10 locations by TPopulation1July for a chosen year.

• Retrieve all records where PopGrowthRate > 2 and NetMigrations < 0.
• Show all years for a given location where MedianAgePop < 20, sorted by year
ascending.

4
Part 4 – Joins & Subqueries

13. Write join queries:

• Join DemographicFact, Location and Variant to display LocationName, Year,

VariantName, TotalPopulation, PopDensity.
• For each LocTypeName, compute the average PopGrowthRate over all years.

14. Write subquery-based queries:

• Find all locations whose latest-year total population is above the global average
for that year.
• Find the location(s) with the maximum NetMigrations in any year.
• For each location, list the year(s) when its population was highest (use a cor-
related subquery).

Part 5 – Views & Functions

15. Create views:

• vw CurrentPopulation: for each location, show the latest year, total popula-
tion, median age, and growth rate.
• vw FastGrowingLocations: subset of the above where latest PopGrowthRate
> 1.5.

16. Create a scalar function:

• fn CalcDependencyRatio(@TotalPop, @MedianAge) that returns a numeric

“dependency ratio” based on any simple formula you define.

17. Create a table-valued function:

• fn LocationPopulationTrend(@LocationID) that returns all years and cor-

responding total population for that location, sorted by year.

18. Use the functions in SELECT statements to:

• Show a population trend for one location.

• Calculate dependency ratios for all entries in vw CurrentPopulation.

5
Part 6 – Stored Procedures & Triggers

19. Create stored procedures:

• sp InsertDemographicRecord to insert a new row into DemographicFact

given location name, variant name, year and key metrics. Resolve foreign
keys inside the procedure.
• sp TopLocationsByPopulation @Year, @TopN to return the top N locations
by total population in the given year.

20. Create an audit table:

• DemographicFact Audit with fields: AuditID, DemographicID, Operation,

ChangedOn, OldValueTotalPopulation, NewValueTotalPopulation, ChangedBy.

21. Create triggers:

• An AFTER UPDATE trigger on DemographicFact that inserts into DemographicFact Audit

whenever TotalPopulation is modified.
• (Optional) An INSTEAD OF DELETE trigger to prevent deletion of records for
year ≥ 2000 and instead log the attempted delete.

Part 7 – Transactions & Integrity

22. Write a transaction script that:

• Begins a transaction.
• Inserts a new location and several years of demographic facts for that location.
• If any insert fails (e.g., invalid year), roll back; otherwise commit.
• Add comments explaining how Atomicity and Consistency are preserved.

23. Write another transaction that:

• Attempts to increase PopGrowthRate by 0.5 for all rows.

• Before COMMIT, checks that no row exceeds PopGrowthRate > 10.
• If any row violates this rule, roll back the transaction.

6
Part 8 – Normalization & Documentation

24. In SQL comments or a separate document, briefly explain:

• How the original wide Staging Demographics structure violates 3NF.

• How your Location, LocationType, Variant, and DemographicFact tables
move towards 3NF.
• Any denormalizations retained intentionally.

25. Draw and submit an ER diagram of the final schema, showing:

• Main entities/tables
• Primary keys and foreign keys

End of Final SQL Project Specification