Nuclear Reactor SQL Project

Introduction

🗂️Background

• I am using the project for SQL practice, as I am trying to learn the big 4 data analysis and visualization programs: SQL, Power BI, Python, and Excel. I took an intrest in nuclear energy recently and wanted to uncover the current landscape and see what I can learn from it and SQL provides the right tools to do so. I will use data from the 2023 IAEA nuclear reactor data set and provide a comprehensive list of statistics and data points for nuclear energy around the world.

📋Setup, Procedures and Data

• First, I extracted data from the 2023 IAEA nuclear energy data set into tables in my PostgreSQL local server using dbeaver.
• I then devised multiple questions to be answered for analysis.
• Using dbeaver, I went through and answered each question to uncover the landscape.
• Then I used gemini to visualize the raw data.
• Finally, I interpreted the visualizations.

⚡️Major Topics Addressed

• Reactor Demographics and Industry Leaders
• Operational Performance and Efficiency
• Historical Development Trends

🔢ERD and Tables

• ERD

  

You can crtl + click on each underlined link to open up a seperate tab for extra information_

• Tables:

  • Operational Reactors (MAIN TABLE) - Shows the current reactors in operation with country, name, reactor type, model, energy capacity, connection date. Connects with operators, suppliers, fuel, coolant, and type.

    • Coolants : Links with operational reactors and shows the coolants associated with a specefic reactor.
    • Fuel Info : Links with operational reactors and shows the fuels associated with a specefic reactor.
    • NSSS Suppliers : Links with operational reactors and shows the suppliers associated with a specefic reactor.
    • Operator : Links with operational reactors and shows the operators associated with a specefic reactor.
    • Reactor Types : Links with operational reactors and shows the reactor type associated with a specefic reactor.

  • All Reactors (MAIN TABLE) : Shows everything in operational reactors table and includes shutdown reactors and shutdown dates.

  • Annual Global Summary (MAIN TABLE) : Shows the year, commercial operation reactor units, reactor construction starts, reactor grid connection units, energy ammount for connection, and energy ammount for commercial operation date

  • Outage Statistics 2023 (MAIN TABLE) : Shows the reactor type, number of units, outage hours per unit, planned outage percent, unplanned outage percent. and external outage percent

  Questions

  What are the major demographics and industry leaders for nuclear power since 1954?

  What reactor types are most involved in nuclear energy

• 📏 SQL Query and Raw Data:

  • Counts total type of reactors worldwide:

    We can see here that Pressurized Water Reactors (PWR) are the most abundant with 298 and there is only one High Temperature Gas Cooled Reactor (HTGR) in operation.

  • Each reactor type for each country:

    This data set shows us that the USA, China, and France have the most PWR's worldwide and the USA has the most Boiling Water Reactors (BWR), Canada has the most Pressurized Heavy Water Reactors (PHWR), Russia has the most Light Water Graphite Reactors (LWGR) and the only two Fast Breeder Reactors (FBR), the UK has the only 8 Gas Cooled Reactors (GCR), and China has the only High Temperature Gas Cooled Reactors (HTGR) as well.

  • CTE to find country reactor type percentage of all operational reactors per country:

    We can see that the USA has the highest share of BWRs and PWRs and the other countries distributions with a 75% and 19% share respectively.

  • Finds the top producing reactor types in MWE:

    It's obvious that this query will show that PWR has the highest MWE output with 283,757 MWE because it's the most abundant and the lowest is HTGR of 150 MWE with only one in operation worldwide.

  • Finds the percentage of top producing nuclear reactor types:

    Expanding the previous query, PWR has a 77% share of total energy while the HTGR only has a .04% energy share.

  • Searches for the top producing nuclear reactor types per country:

    Though the US has more PWRs in operation, France dominates the total output with 61,370 MWE for their PWRs. This is interesting to see, and France may have more efficient reactors in operation. The US however, dominates the BWR output with 32,709 MWE.

  • Queries the total energy per reactor type:

    This query finds interesting statistics, showing that BWRs have the most efficient energy per unit even though PWRs output the most cumulatively with 1,050 MWE/BWR reactor and 952 MWE/PWR reactors. This can be used to potentially transition the future reactors for BWR but more research must be done on the financial side of things.

  • Finds the top energy output reactor types for the USA:

    This query is just reiterating that the United States' focus on reactor types with BWRs and PWRs as the main sources.

  • Find the top energy output reactor types for top producing countries:

    We are honing in at the top producing nuclear energy countries of USA, France, and China to investigate what reactor types they prioritize over others and their output for each. USA channels BWRs and PWRs, France likes PWRs only and China is in with PHWRs, PWRs, and HTGRs

• 🧮 Visualization

  • Each reactor type for each country:

  • Counts total reactors per type worldwide:

  • Country and reactor type percentage of all operational reactors:

  • Top producing reactor types by MWE

  • Total energy production per reactor type

• 💡 Main Points:

1. PWRs are the most abundant and outputs the most energy worldwide.
2. USA leads the march in total reactors and PWRs, but France has the most PWR energy output.
3. BWRs are the most efficient.

• 🔑 Interpretation and Use:

  • This section focused on the reactor types and was incredibly insightful to which reactor types are preferred around the world and country. Pressurized water reactors are the top choice for a lot of countries and have the most output by a large margin. Now we can investigate that further as to why, is it cost effective, safe, or efficient at using resources for energy? We can also ask, why are other countries developing these other non-traditional reactor types? Finding out which reactor types are most popular is a great first step into discovering the nuclear energy landscape.

2. Who are the reactor suppliers and operators contributing the most?

• 📏 SQL Query and Raw Data:

  • Finds the top nuclear reactor operators:

    Électricité De France is the top operator worldwide with 56 current operational reactors.

  • CTE to find the percentage of operator share of the total nuclear reactor market:

    The dominance that Électricité De France has around the world, owning 13.75% of total reactors.

  • Finds the top nuclear reactor suppliers:

    Framatome and Westinghouse Electric Corp. are the top suppliers around the world, supplying 65 and 64 total reactors around the world.

  • CTE to find the percentage of supplier share of the total nuclear reactor market:

    The dominance that Framatome and Westinghouse Electric Corp has around the world, supplying 15.97% and 15.72% of total reactors.

  • Finds top suppliers for nuclear reactor type:

    The suppliers are further broken down into their reactor type where Framatome and Westinghouse supply all PWRs. By analyzing this with the supplier list, we can see that most suppliers only produce one reactor type, however, Atomenergomash supplies FBRs, PWRs and LWGRs.

  • Finds top operators for nuclear reactor type:

    This does the same analysis for reactor type but for the operator side of things and tells a different story of operators being variable to one reactor type. The operators that deviate are the Joint Stock Company operates, FBRs, PWRs and LWGRs, Korea Hydro and Nuclear Power Cooperating PWRs and PHWRs, CNNC Nuclear Operation Management Company that operate PHWRs and PWRs, EDF energy operates GCRs and PWRs, Nuclear Power Corporation of India operates PHWRs and PWRs. Energy Nuclear Operations, Exelon, First Energy Nuclear, Northen States Power, Progress Energy, PSEG Nuclear, Southern Nuclear, Tennessee Valley Authority, Teollisuuden Voima OYJ all share a mix of BWRs and PWRs.

  • Finds the relationships between reactor suppliers and operators:

    This is an interesting analysis showing what suppliers interact with operators. We can see that Framatome and Électricité De France have a tight relationship having supplied them all 56 of their reactors. Doing more research, Électricité De France is a majority owner of Framatome. We can also see this relationship between Joint Stock Company and Atomenergomash, where Atomenergomash owns their operator. One example where the two aren't closely related in business is General Electric as the supplier and Exelon the operator in the US.

  • Finds the relationships between reactor suppliers and operators in the USA:

    After analyzing the relationships between suppliers and operators just in the USA, it's clear that General Electric and Westinghouse Electric are at the top of the food chain in suppliers. Exelon remains the top operator in the USA.

  • Find the suppliers contributing the most nuclear energy:

    Looking at this briefly, it's easy to see that Électricité De France remains the top operator with producing 61,370 MWE out of France and the next top operator is about half that at 27,727 MWE coming from Joint Stock Company out of Russia.

• 🧮 Visualization

  • Nuclear reactors by operator:

  • Nuclear reactors by supplier:

  • Nuclear reactors by supplier and operator relationships

  

• 💡 Main Points:

1. Électricité De France is a champion nuclear operator.
2. Framatome and Westinghouse Electric Corp. are the heavyweights for reactor supply.
3. Many suppliers usually stick to one reactor type, while many operators mix and match reactor types.

• 🔑 Interpretation and Use:

  • This section focused on the supplier and operator data. This is a good section for potential investors into nuclear reactors to get a general idea of who owns majority of the reactors and supplies them as well. This can potentially expand into finding the financials into reactor data per supplier and operator but for now it's good starting point for general data. We can ask questions like; how would we compete with established operators and suppliers? Why do operators and suppliers use specific materials, is it based on their location and material availability? How do certain supplier-operator relationships work? This section is essential to understand the business side of things relating to nuclear reactors.

3. What countries are most involved with nuclear energy?

• 📏 SQL Query and Raw Data:

• Counts total reactors per country and percent dominance:

  The USA leads all operational reactors with 89 and that equates to 21.8% share overall, and the next top country is France with 56 and a 13.7% total share.

• Finds the reactor type diversity by country:

  China and Russia are the most diversified countries for reactor types with 3 different for each.

• Finds which countries are supplying the most energy:

  USA takes the lead with 90,781 total output in MWE.

• Shows the percent dominance in energy by country

  The query expands the previous query by extrapolating the energy percent share and the USA has a 24.9% total share of total nuclear energy worldwide.

• Shows total mwe output per countries reactors:

We can dive deeper into the energy produced by country by dividing the energy output for each country by their total reactors. The top three nuclear energy producing countries were used and France has 1,095 MWE per reactor, the USA has 1,020 MWE per reactor, and China is 966 MWE per reactor. This ultimately shows how efficient each country is per reactor.

• 🧮 Visualization

  • Total reactors for each country:

  • Counts reactor type diversity:

• 💡 Main Points:

1. The USA has the most operational reactors and total energy output.
2. China and Russia lead the way for unique reactor types and experimentation.
3. Nuclear reactor powerhouses include: United States, France, and China.

• 🔑 Interpretation and Use:

  • We found out the leverage each country has in nuclear energy. This is important for understanding which countries value clean energy the most and also may indicate which countries require the most energy. This can be further expanded into finding out which countries in the future may invest more into this energy type and if a nuclear engineer saw this they may think that the United States, China, and France are the best places for job security. This can potentially bring jobs to various countries and increase quality of living for the citizens.

What is the operational performance and efficiency for current nuclear reactors around the world?

4. What does the energy output look like for nuclear power?

• 📏 SQL Query and Raw Data:

• The total MWE (Mega Watts Electrical) produced by nuclear energy worldwide:

  Total nuclear energy produced worldwide is 364,640 MWE

• Finds MWE produced per reactor units world wide

  Nuclear reactor energy generation per model is 895 MWE. Dividing net reactor output / net reactors

• Finds the MWE produced for a specefic coolant type

  Most energy producing coolant is using light water coolant.

• 💡 Main Points:

1. Nuclear reactor generation per reactor is 895 MWE.

• 🔑 Interpretation and Use:

  • Energy was the focus for this question where we focused on total output worldwide, coolants associated with energy output, and what each reactor unit could carry for total energy. This gives anyone a good understanding of how much energy is output and optionally we can look at how much energy is required per country, region, and the world to figure out how many reactors we would need for these to be sustainable. We can expand this by asking; What is the energy requirement average for each country and how is that compared to the total generation for nuclear reactors? By using the reactor generation per model, how many nuclear reactors would we need to supply a region, country, or the world?

5. What nuclear reactor models have in common?

• 📏 SQL Query and Raw Data:

• Counts total reactor model types around the world

  There are 83 total unique reactor models supplied globally.

• Finds the most common reactor model type around the world:

  The VVER-V320 is the most supplied model with 25 in current operation. We also see that it is a PWR which is the most common reactor type.

• Finds the percentage of reactor model types worldwide

  When seeing that the VVER-V320 is the most popular we can find that the percentage is equal to 30% of total models.

• Shows the current top reactor models for each country

  The P4 REP 1300 is the most exclusive to an individual country which is France.

• Shows total mwe output per reactor model

  Furthering the research in model types, the P4 REP 1300 has the highest of 26,370 MWE and the VVER-V320 comes in second with 24,080 MWE even though it is the most popular reactor model worldwide.

• Shows the highest mwe output per reactor name

  The TAISHAN-1 and TAISHAN-2 coming from China, output the most MWE at 1,660.

• 🧮 Visualization

  • Most common reactor models:

  • Reactor models for each country :

  • MWE output for combined reactor models and individual models:

• 💡 Main Points:

1. There are 83 unique model types worldwide
2. The VVER-V320 is the most popular model type
3. The P4 REP 1300 has the highest energy yield even though the VVER-V320 is the most popular.

• 🔑 Interpretation and Use:

  • This insight is very useful for honing in on specific reactor models to see which one can carry out the most energy and what type it's associated with. It's clear to see that France "owns" the most reactor models in the world and China has the top performing individual reactor model. It can be taken further by developing relationships between reactor model, type, and output along with section 1 to find the most efficient reactor type and draw conclusions there. This section just adds depth in the search for energy efficiency within the nuclear reactor sector to see if it's a viable energy source for the future. I would ask questions like: What makes the P4 REP 1300 so powerful? How can I develop these models into more efficient models for the future?

What is the historical development of nuclear reactors and how does it influence future energy sources?

6. What does history tell us about nuclear reactors?

• 📏 SQL Query and Raw Data:

• Shows the median construction time for all reactors

  On average it takes about 6 years from start to finish to build a reactor.

• Finds the median contruction time for reactors per country

  The Netherlands takes the shortest amount to build their reactors with a time of 3.7 years, beating the global average by 2.3 years.

• Finds the median time from power generation to commercialability

  The time the nuclear reactor can produce energy to commercial ability is about 5 years. Using this information with the median construction time we can see that the average time from start to finish for commercial generation is 11 years from the first date of construction.

• Shows the current longest operational reactors

  BEZNAU-1 from Switzerland is the longest lasting reactor being built on July 17th 1969.

• Shows the current longest commercial reactors

  BEZNAU-1 from Switzerland is the longest lasting commercial reactor after availability on December 24th 1969.

• Shows the median lifespan of reactor operation

  The median operation lifespan for nuclear reactors in the past has been 31.7 years.

• Finds which years had the most grid connections

  I was able to find that in 1984 and 1985 they were at the peak with 33 connections for each year. I believe this rapid influx of connections was due to the cold war incentive of stockpiling uranium for nuclear warheads.

• Shows which years had the most total connections

  In 2005 we had a peak of 440 total operational reactors and since then it has steadily decreased, there are either shutdowns or there are not being any more nuclear reactor constructions.

• Finds the top reactor construction years

  With this query we can see that in 1976 we saw a peak of 43 reactor constructions and in 1995 there were 0 constructions worldwide.

• Analyzes the operational reactors between 1954 and 1984

  After running this query, I found that between 1954 and 1984 there were 143 operational reactors in 3 decades. I wanted to analyze these decades to see if there was a connection between the cold war and operational nuclear reactors.

• Analyzes the operational reactors between 1984 and 2014

  After running a similar query, the previous I found that between 1984 and 2014 there were 223 operational reactors in 3 decades.

• Shows the common reactor types between 2000 and 2010

  I found out that the common types of reactors in operation in this period were PWRs and PHWRs. I wanted to see if there were any significant technological shifts in nuclear reactor types between 2000-2010 and 2010-2020.

• Shows the common reactor types between 2010 and 2020:

  I found out that the common types of reactors in operation in this period was mainly PWRs with a mix of FBRs and PHWRs. It seems during this decade there was a slight introduction to new reactor types.

• Shows what reactor types were common in 2012

  Shows that 3 PWR reactors were in operation for 2012.

• Analyzed the United States' reactor constructions

  We can see that 1968 was a hot year for nuclear reactors, starting 27 in total.

• Which decade saw the least connections

  Seen that in 1950 there were only 11 total connections, this is during the start of nuclear reactor energy, so this makes sense.

• Compares reactor superpowers of France and the United States continual operation

  I wanted to compare the two most generative countries which are the USA and France to see which one has the longest average operational lifespan and we can see that the USA beats France by 4 years, with 44 and 40 years in average operation respectively.

• Finds the average length of continual operation per country

  I also wanted to see how this was broken down for each country and the Netherlands has the highest operational average lifespan of 52 years. It's also interesting to note that the Netherlands also was the shortest time to complete construction for their nuclear reactors.

• Finds the total continual operation worldwide

  This operation took the worldwide reactors' lifespan into account which was 33 years of average operation.

• Shows which years saw the highest change of nuclear reactor additions

  This complex operation found that in 1980 we saw a total of 13 new reactor additions which is the most historically.

• Finds which decade saw the most connections for a reactor type

  After running this, we can see that the highest grossing nuclear reactor addition decade was 1980, with total 127 total additions.

• 🧮Visualization

  • Countries and their median contruction time:

  • Percent change of nuclear reactor additions for each year since 1954:

  • Construction starts yearly:

• 💡 Main Points:

1. The Netherlands has the shortest average construction time and longest average operational lifespan for nuclear reactors.
2. 1984 and 1985 saw the highest grid connections for reactors worldwide.
3. Nuclear reactors usually last around 32 years based on historical data.

• 🔑 Interpretation and Use:

  • This section gives a lot of information about the timeline for nuclear reactors and may give some insights into the projection to build new ones. I provided a lot of information that can be exclusive to certain types of questions a nuclear reactor analyst may have for either the past, present or future. This also includes country specific information regarding timelines. This section is mainly to provide trends and insights into the future production of nuclear reactors. Some historical contexts as to why around 1980-1990 we saw a general increase of reactors can inferred that it was primarily fueled by the cold war involving an increase in nuclear reactors for specifically their waste materials to make nuclear weapons between Russia and the USA. We can ask further: Why do some countries have long-lasting nuclear reactors? Is this because of better funding? Less regulation? How does regulation play into each country’s reactor lifespan? What is the most common reason for nuclear reactors to last? Why did 1984 and 1985 see so many connections vs other years?

7. What is the current status for nuclear power?

• 📏 SQL Query and Raw Data:

• Finds out how many operational reactors are current and how many have shutdown in the past

  We can see that there are 412 total operational reactors and 209 shutdown reactors worldwide.

• Shows the distribution of shutdown and operational reactor types

  We can see that there are more PWRs operational and shutdown than any other type.

• Analyzes the percent of operational and shutdown reactor types

  This shows that Heavy-Water Moderated, Boiling Light-Water Cooled Reactor (HWLWR), Heavy-Water Moderated, Gas Cooled Reactor (HWGCR), Steam Generating Heavy-Water Reactor (SGHWR), and Other (X) reactors are permanetly shutdown and non are in current operation. The highest shutdown operational type is GCRs which have a 84% shutdown rate.

• Shows which countries have the most shutdown nuclear reactors

  We can see the country with the most shutdown reactors and the USA leads that statistic with 41% of reactors shutdown permanetaly.

• 🧮 Visualization

  • Operational vs Shutdown reactors count:

  • Shutdown nuclear reactors count:

• 💡 Main Points:

1. GCRs have a large 84% shutdown rate
2. 209 reactors have been shutdown in total

• 🔑 Interpretation and Use:

This gives insight into the history of nuclear reactors and how many have been shut down in total. We can use this information to understand how likely a reactor is going to be shut down and if it's feasible to take on the risk to develop a certain type of reactor. I would ask what is the main reason to why these reactors are being shutdown? If I were developing a reactor, what are the steps to avoid being shutdown? Why are GCRs shutdown often?

8. Common fuels for nuclear power?

• 📏 SQL Query and Raw Data:

• Shows the relationship between fuel types and reactor types

  This just shows the cross relationship many fuels have with the reactor types. We can see that Enriched Uranium Oxide is the most common and interacts with LWGRs, GCRs, PWRs, and BWRs.

• Shows the total count of each existing fuel types

  To no suprise UO2 is assosiated with the most reactors because it's used in PWRs, the most common reactor type.

• 🧮 Visualization

  • All fuel types distribution
  

• 🔑 Interpretation and Use:

We can see through this analysis the common types of coolants associated with nuclear reactors and help suppliers and operators understand what their main sources of coolants are for their reactors. I would ask further: Are non-PWR coolants more complicated than other coolants? Is there a reason why certain coolants are used vs. others for these reactors?

Conclusion:

🧩Complex SQL Skills That I Learned More About :

• Window functions
• Multi Step CTEs
• Sub queries
• Joins

⚙️What I Learned From This Project:

• SQL can be used in almost any field with the right data sets.
• The wide variety of queries that can be used to find unique questions from large data sets in an instant.
• How to extract data effiently using AI and distribute the data it into tables.
• Making visualizations and limiting certain queires to give a snap shot.
• That aggregation functions are great at finding totals and effiency related statistics while CTEs are great at finding percentages from a whole and percent growth statistics.

🔭 The Future:

I hope to continue making SQL projects to subjects that interest me so I can get a better grasp on this SQL and have fun with various topics. Making this project allowed me to get more comfortable with using SQL in everyday life. I hope to work on other projects that include organic chemistry, real estate, retail, and hope to make a positive impact for the people around using data to our advantage. Though nuclear reactors is incredibly niche, I can generalize the questions asked here and translate similar processes in data extraction to broader and more real-life job-related scenarios with retail, technology, and logistics.

🧰 Reources Utilized:

• DBeaver
• PostgreSQL
• Git
• GitHub
• VS Code

💻Sources

• Nuclear Power Reactors In The World (IAEA, 2024)