We all know that Excel is packed with tons of powerful features, libraries of formulas, and galleries of interesting charts, making it one of the most useful tools for business analysts.

Business analytics involves using data to make crucial strategy-level decisions. The question is how to represent that data in a way that’ll help our leadership team or decision-makers come to a consensus quickly. This is your skills as an analyst and a dashboarding specialist will shine.

In this article, I will show you how to create a dynamic and stunning dashboard in Excel. We will look at creating pivot tables, designing the dashboard layout, adding various charts to the dashboard, unifying the charts into one theme, and then adding slicers/filers that control all the charts. Here is a preview of what you will be able to build at the end of this article.

Now, that we are set and done, let’s get right into it!

Step 1: Dataset for Dashboard

For our Ultimate Excel Dashboard, we will use a huge amount of transactional sample data. Here is the snapshot of the sample sales data:

Step 2: Creating Visualizations for the Dashboard

For the given sales data, different types of plotting can be performed. We will create charts to analyze:

1. KPI (sales, profit, quantity, no. of orders, profit margin)
2. YOY Growth
3. Sales and Profit analysis
4. Category wise Profit
5. Category-wise Sales share %
6. Sales by State
7. Top 5 Categories by Sales

Now that you have your data set prepared, let’s start creating and preparing the Pivot Tables and Pivot Charts that we need to fill our Excel dashboard with life.

Create Pivot Tables and Pivot Charts:

First will work on creating 5 charts for our dashboard and then will move towards KPI.

For every Pivot Chart, we start by creating the Pivot Table. Once the Pivot Table is set up, we can drag and drop the required data fields into the rows, columns, and values of our Pivot Table section.

• Sales and Profit analysis

For Sales and Profit analysis we need to create a pivot table with sales and profit. To create a pivot table, navigate to the Insert tab, and then the PivotTable option on the left.

This will open up a new sheet with Pivot options on the right. We need month vise sales and profit so will simply drag the sales and profit field into the values section and the field into the values section and order date in the axis (categories). You will notice order date will display year wise so remove year and quarter from axis to keep list of months only.

Change the format of sales and profit values as shown below by right click on selecting sales and profit columns.

Now, let's create a chart for this pivot table. for that click on any cell on the pivot table to activate the pivot chart and select combo chart as we want to show sales and profit both on the same chart as a dual axis.

• Category wise Profit

We are going to create a waterfall chart for the category-wise profit. First, create the pivot table by taking profit in values and category in the axis field of the pivot table and try to select the waterfall chart which will give you the below error.

The Waterfall Chart and Map Chart are 2 exceptions Chart which currently requires copying the Pivot Table Data outside of the Pivot Table and then inserting a regular chart that references the copied data.

Format the profit values into $0.00,”K” format as shown before.

• Category-wise Sales share %

We want to know which category has highest sale out of all the available ones. For this, we will plot a pie/donut chart. In the pivot table, drag “Category” in the rows section and “Sales” in values. Show sales values as grand total as shown below.

To plot the donut plot, click on the pie icon in the pivot chart options and select the donut plot at the end. Display labels by clicking plus sign on the right corner. Next, remove all the unwanted field buttons to enhance the chart visually.

• Sales by State

For every new plot, you need to create a new pivot table. Therefore, repeat the process of creating a pivot table and navigate to the new pivot table sheet each time. Now, for sales by date, a Map plot would convey more information about the actual region of the country.

Drag “State” to rows and “Sales” to values to create a pivot table. we cannot plot the Map plot directly from a Pivot table. Therefore, you need to copy the pivot table, paste it into the same sheet, and then create the reference of the values to the pivot table values. Below image will help you to understand this part.

• Top 5 Categories by Sales

We want to show the Topmost 5 categories that have the highest sales, and for that create a pivot table with “Category” on rows and “Sales” on values. Then, sort the data from largest to smallest.

Choose the top 5 categories and then select pivot chart to show top 5 categories by sales.

• KPI (Key Performance Indicator):

KPI is a measurable value that demonstrates how effectively a company is achieving key business objectives. Organizations use KPIs to evaluate their progress and success at reaching targets.

Here, we going to create 5 KPIs for our dashboard. let’s start with the first one which is Sales. You can use a Sparkline chart for KPI but here I would like to use a line chart as a sparkline chart, will show you how.

Select the line chart from the pivot chart for a pivot table with month and sales and remove everything from the chart except the line only so we can use this line chart as a sparkline chart.

Repeat the same process for the other three KPIs for Profit, Quantity, No. of Orders.

For Profit Margin KPI, we have to calculate the profit margin which is =Profit/ Sales using calculated field.

Now, all five KPIs with charts are ready to go for a dashboard.

• YOY Growth

Year over Year (YoY) growth measures the change in an annualized metric across two comparable periods, typically the current period and the prior period as of the fiscal year-end date. The formula to find YOY growth is:

Increased YoY Growth → Positive

Decreased YoY Growth → Negative

Calculate YOY Growth for all KPI fields using the above formula. To show positive YOY growth give green color with an up arrow and give red color with a down arrow for negative YOY growth.

([Green]▲0.00%;[Red] ▼0.00%)

Step 3: Raw Dashboard Design

All the required charts are created and now we are ready to design the dashboard. Follow these steps:

1. Create a new sheet in the workbook
2. Goto to page layout, and under the sheet options, uncheck the Gridlines view checkbox.
3. Next, click on Background and choose a background image. You can search for images on Google, Unsplash, or create a fresh dashboard layout in Canva.
4. Now, you can add the title and subtitles to the report by going to the insert tab and selecting the text box option at the right. You can also change the font color, text, and other text properties.

Will start with adding KPIs to the dashboard first and put them next to each other in 1st row. For each KPI, there will be total shapes you need to add which are arranged as shown below. By selecting all these 4 shapes together, you can create a group so it will be easy to move them all together.

Similarly, keeps adding all other KPIs to their respective place and arranging them properly before creating a group with all five KPIs together.

After adding the background, title text, and KPIs it’s time to create the titled view of our dashboard. Insert a rectangle, fill the gradient fill color as you want, make it around 25% transparency, and remove the border colors. These boxes/tiles will be the base for our charts. Place them in an ordered collage manner. It would look like this:

Step 4: Insert and Redesign Pivot Charts

Now, to add the charts to their respective positions:

1. Simply copy them from their pivot sheets and paste them into their tiles.
2. You will need to adjust the size according to the tile size or modify the tile size to accommodate the charts.

For example, let’s add the first chart Sales and Profit Analysis.

For every chart to be in the same theme and immersive into the tiles made:

1. You need to change the gradient color respective to the selected theme and the Border color to none. This can be done by navigating to the format tab.
2. Change all text colors to white.
3. Remove the unnecessary field buttons from charts by going to the PivotChart Analyse tab and then to the Field Buttons option to the right. Deselect all the choices there or choose the Hide all option.

Add the waterfall chart and donut chart to their respective shapes. There are a few format options available that you need to do for the donut chart like hole size, explosion, different shades of color per donut section, label formatting, etc. as shown below.

The next one is Map Chart for Sales by State. Follow the same steps to add the chart and another formatting. Select a series of colors to make it more clear visualization.

Last chart remaining before we add slicers is the Top 5 Subcategory by Sales. Select show data labels from chart elements and format them to the inside end to save space as shown below.

The dashboard with all the charts placed, given title, and customizations applied should look like this:

Step 5: Add Slicers for Interactivity

Slicers are the magic component that will make our charts interactive and cross-dimensionally filterable. We insert a slicer for every dimension that we want to filter in and connect all slicers to every chart.

To achieve this, simply create a slicer for any of the charts placed.

1. Select a chart
2. Navigate to the Insert tab
3. Click on the Slicers option in the Filters section
4. Select the desired columns/fields for which you want to filter the data

Here, I have chosen a Waterfall Chart to create slicers and chosen Year, Segment, and Region so it will give three slicers.

Add these slicers to the dashboard by simply copying and pasting.

Now it’s time to format slicers……

Now, for slicers to update other charts as well,

1. Right-click on the slicer
2. Select Report Connections
3. Select/Check all the pivot tables checkboxes

Final Dashboard in Excel

And here is our final dashboard creation……

Have another look for the dashboard as GIF to understand its interactivity.

Conclusion:

In this blog, I have explained how to create dashboards in excel. I have given screenshots with descriptions to create pivot tables, insert charts into the sheets, format options, create the layout of a dashboard, how to add all charts together on the dashboard, make the charts beautiful with correct and even theme, and how to connect all these charts to common slicers.

Thank you for taking the time to read our content. Appreciate your claps and comments.

Imagine a condition that causes between 20 and 30 million deaths worldwide each year. However, many people have not heard of it, and even fewer understand what it is. I was one of them. The condition is called sepsis and it’s the reason many adults, children, and families, are grieving for their loved ones.

Our immune system protects our bodies from a variety of things, including viruses, bacteria, and toxins, so having a healthy one is important. Sometimes, however, an immune system can weaken or fail. It can even work against us, causing autoimmune disorders, which can put one at greater risk for sepsis and lead to septic shock.

Sepsis and Progression of Sepsis

Sepsis, also known as septicemia or blood poisoning, occurs when the body has a severe response to an infection. Instead of the immune system defending the body, it releases chemicals, potentially leading to mass inflammation and damage to the kidneys, lungs, liver, or other major organs.

Sepsis can be identified into four stages (figure 2):

1. SIRS (Systematic Inflammatory Response Syndrome)

Sepsis can be hard to diagnose at its early stages because its symptoms are similar to many other conditions, but SIRS still helps at a certain level to identify.

Identifying SIRS in a patient includes the following criteria:

• Temperature > 38.5ºC or < 35ºC (body temperature alterations which are known as hyperthermia or hypothermia)
• Heart rate >90 beats/min (Tachycardia)
• Respiratory rate >20 breaths/min or PaCO2 < 32 mmHg (Tachypnoea)
• WBC >12,000 cells/mm3 or < 4000 cells/mm3, or >10 percent immature (band) forms (leukocytosis or leukopenia)

Finding two or more of any of these criteria in a patient indicates the possibility of sepsis.

2. Sepsis

An infection reaches the bloodstream and causes inflammation in the body.

3. Severe Sepsis

The infection is severe enough to affect organ function.

4. Septic Shock

There’s a significant drop in blood pressure that can lead to respiratory or heart failure, stroke, dysfunction of other organs, and possibly death.

When Sepsis Becomes Septic Shock

Sepsis treatment usually requires intravenous (IV) fluids and antibiotics. It is essential that the treatment begin as early as possible. The chance of sepsis progressing to severe sepsis and septic shock, causing death, rises by 4% to 9% for every hour treatment is delayed.

Severe sepsis occurs when one or more of your organs stop working effectively. For example, you could need a ventilator to help you breathe or dialysis to filter toxins from your blood. Any organ can be affected.

People with severe sepsis are already very ill, but if your blood pressure starts to drop, you become even sicker. You go into septic shock. The medical definition of “shock” is a drop or fall in blood pressure. When it is associated with sepsis, it is called septic shock.

Severe Sepsis and Septic Shock:

Both of these are conditions in which multiorgan dysfunction is due to INFECTION only.

• The only difference between these two is the presence of volume-resistant hypotension in septic shock.
• The tendency to develop one (or both) of these conditions is NOT a function of the organism involved in the actual infectious process but rather, the degree of HOST RESPONSE.

Septic Shock Signs, Symptoms, & Complications:

Septic shock is a severe drop in blood pressure that results in highly abnormal problems with how cells work and produce energy. Progression to septic shock increases the risk of death. Signs of progression to septic shock include:

• The need for medication to maintain systolic blood pressure ≥ 65 mmHg.
• High levels of lactic acid in your blood (serum lactate). Having too much lactic acid in your blood means that your cells aren’t using oxygen properly.
• Difficulty breathing with increasing oxygen requirement.
• Lab abnormalities — kidney or liver dysfunction, blood count abnormalities.
• Confusion or change in mental status.

At this stage, immediate medical attention is critical.

Septic Shock Symptoms:

Depending on a patient’s progression through the phases of sepsis, symptoms range from mild to severe. The most common symptoms include:

• Low blood pressure (Hypotension)
• Respiratory issues
• Confusion
• Temperature exceeding 101º F
• Fever and chills
• Increased heart rate
• Organ failure
• Urological issues

Septic Shock Complications:

Septic shock is a very serious medical condition. It’s the most severe stage of sepsis. Septic shock can lead to:

• Brain damage.
• Lung failure.
• Heart failure.
• Kidney failure.
• Gangrene
• Death.

Septic Shock Treatment:

People who are in septic shock need urgent intensive care. The medical team has to approach treatment from different angles for it to be successful:

• IV fluids to raise blood pressure.
• Treat the infection: Your doctors must find out what caused the infection that triggered the sepsis and give the appropriate antibiotics.
• Oxygen, either by mask or nasal cannula or with a ventilator, helps raise oxygen levels in the blood.
• Surgery may be necessary to remove the source of infection, such as gallstones or an inflamed appendix.

Analysis with Sepsis Data using Tableau:

Let’s understand a bit of data….

The Sepsis dataset has 43 biomarkers which can broadly be classified into:

• Demographics
• Vital Signs or Clinical
• Laboratory values

Just like biomarkers, patients can be categorized under 3 conditions:

• Patients without sepsis, on admission (Sepsis Label 0).
• Patients without sepsis on admission, but later developed Sepsis in ICU (Sepsis Label 0 to 1). we can call them Onset Sepsis patients.
• Patients with sepsis, on admission (Sepsis Label 1).

Part-I: SIRS Analysis

SIRS Analysis part has already been done so I am not going to explain it here in detail. Below the table, I created in tableau based on SIRS symptoms (explain above) Trigger hour as well as an hour before the trigger hour gave name Previous hour. If any two of the SIRS conditions are satisfied that means the patient falls under the SIRS criteria, and they have the trigger started so we can compare it with the previous hour. We can see many patients who already started showing symptoms at least once and so with this and considering the other factors we can give priority to those patients who need more care or who need immediate care.

Color-coded was given based on SIRS conditions for the Trigger hour and the Previous hour.

Part II: Septic Shock Analysis

The sepsis dataset has 40,336 total patients count. Out of that 98.02% of patients have no sepsis, and 1.98% of the patients have sepsis. Figure.5 shows the total patient counts for Sepsis, SIRS, and Septic Shock out of that total Patient counts.

Severe sepsis with acute circulatory failure is characterized by persistent hypotension (SBP <90 mm Hg, MAP <60 mm Hg, or a decrease in SBP by >40 mm Hg) despite adequate fluid resuscitation.

Identifying septic shock in a patient includes the following criteria:

• SBP<90 mm Hg, MAP<60 mm Hg or SBP>40 mm Hg
• Respiratory rate ≥ 22 breaths/mins
• Lactate>2 mmol/L

The chance of sepsis progressing to severe sepsis and septic shock, causing death, rises by 4% to 9% with every hour of treatment delay. Laboratory tests can help assess organ dysfunction and contribute to the clinical diagnosis and appropriate medical management of sepsis, but lab results might take a little longer to get so for immediate pre-diagnose, the other 3 important vital signs (SBP, MAP, and RR) need to take into consideration.

The below visualization (figure. 6) helps doctors to know if patients are reaching the critical stage. Visualization contains Trigger hour, Previous hour, and biomarkers like SBP, MAP, Respiratory, and Lactate. Patients who satisfy septic shock conditions have either a sudden drop in blood pressure or a high respiratory rate or it can be both. The respiratory rate is high for all septic shock patients for their previous hour also, but they’re not many significant signs of BP drop in the previous hour, so doctors and nurses need to monitor sepsis patients continuously if the patient has a respiratory rate ≥22 with other imbalance vital signs.

High levels of lactic acid in your blood (serum lactate) is also important biomarker to diagnose if the patient identifies septic shock. Having too much lactic acid in your blood means that your cells aren’t using oxygen properly. In figure. 7, I highlighted lactate values that show high lactate (>2) and very high lactate (>4) with color codes for these septic shock patients. 9.3% of septic shock patients have high lactate in this data set.

From the below Sunburst chart (figure 8) we can see the number of patients who exhibits septic shock symptoms each hour. There is a total of 473 septic shock patients in this dataset which is 16.13% of sepsis patients.

Using the above comparison, we can see how patients quickly develop the symptoms of septic shock. Here in this dataset, there are a total of 9 patients who satisfy all four conditions together (Figure 9) on the higher side which shows these severe septic shock patients need urgent intensive care.

Conclusion:

Chances of survival for severe sepsis and septic shock are greatly dependent on the infection source and how far along the infection has progressed, but the septic shock symptom recognition helps ensure to get urgent intensive care immediately, it can be survivable. Early treatment can and does save lives.

References:

Septic Shock- National Library of Medicine- StatPearls

Sepsis and Septic shock — Pocket ICU

https://www.sciencedirect.com/topics/nursing-and-health-professions/systemic-inflammatory-response-syndrome

https://www.kaggle.com/code/namratakapoor1/sepsis-classification/data

I am working as a Data Analyst for the past few months, and it has been a wonderful experience analyzing data. My daily routine was spending half my time on Tableau and Excel to make sense of the data. This experience has really made me realize the importance of telling stories to employees using data.

These days you have a variety of tools and methods to build stunning reports or charts, from Excel to modern data visualization software like Tableau, and PowerBI. Be it numerical or textual qualitative big data, your presentation must be clear, easy to understand as well as beautiful, evidential, and persuasive.

In this blog, I am going to share….

• Clever Tips on How to present data effectively.
• Effective data presentation examples.

Clever Tips on How to present data effectively.

How you present data can double its impact, so take note of these few ways to ensure that your data is doing its job.

1. Choose the right visuals for the data

Whenever you decide to create some data visualization, use these best practices to make it more straightforward and effective.

• When doing a comparison among items: column chart, bar chart, bullet chart, radar chart, clustered column/bar chart, bar in a bar chart, etc.
• When doing comparison overtime: Line chart, multi-line chart, area chart, step chart, bump chart, etc.
• Composition (part to whole analysis): Pie, donut, waterfall, funnel, stacked bar/column, treemap, stacked area chart, etc.
• Correlation (relationship between variables): Scatter plot, bubble chart, etc.
• Distribution of data: Histogram, box and whisker plot, scatter plot.
• Location analysis: Heat map, bubble map, filled map, etc.
• Key performance indicators (KPIs): bullet chart, dial, thermometer, big number chart, gauge, etc.

2. Carefully choose the colors

• Consistency: As a general rule, you should always pick the same color to represent the same thing; be consistent with your color selection and what it represents in your visualizations. Humans naturally perceive color as a pattern, so when they are presented with color across multiple charts, they will assume it is a representation of the same object or entity.
• Don’t use too much color: It’s also important to not go overboard with your color selections; too many will simply be overwhelming. As a rule of thumb, the Data Visualization Society recommends limiting your palette size to 10 or fewer colors. Once you go beyond this threshold, your audience will tend to have trouble distinguishing between groups in your visualization.
• Choose a cohesive color scheme: It’s also important to note that you don’t use two light shades for both the background and text of your slide. To make your text stand out, you need to use contrasting colors. For example, you can make the background black and your text a bright shade of green to make it stand out, or vice versa. Just be sure that your text is easily readable.
• I personally experience, one of the strongest visualization effects comes with the use of grey plus some kind of action color evergreen tells me.

3. Don’t leave a lot of room for words

The key point in graphical data presentation is to tell the story using visuals and images, not words. Give your audience visual facts, not text. However, that doesn’t mean words have no importance.

As we don’t have a lot of room for wording, the little bits that we put into our data have to be really awesome.

4. Focus most on the points your data illustrates

Data slides or dashboards aren’t really about the data, but they are about the meaning of the data. It’s up to you to make that meaning clear before you click away. Otherwise, the audience won’t process.

When you connect data to the essential points it supports, the transition should be explicit and sound like this:

“This data shows…”

“This chart illustrates…”

“These numbers prove…”

These transitions can be as important as the conclusions themselves because you’re drawing the audience’s attention to those conclusions.

5. Label chart components clearly

While you have been working with the same chart for days or weeks, your audience will be exposed to it for seconds only. Give them the best chance of comprehending your data by using simple, clear, and complete language to identify X and Y axes, pie pieces, bars, and other diagrammatic elements. Try to avoid abbreviations that aren’t obvious, and don’t assume labeled components on one slide will be remembered on subsequent slides.

Some members of your audience are visual learners just like me. who process what they see much better than what they hear, so your chart’s visual clarity is crucial.

6. Formatting: First impressions matter

An audience will decide within the first few seconds whether or not they want to listen to what you’ve got to say. You might be the best at what you do, but if your body language and tone of voice don’t support your key message, you’re at risk of being dismissed. Formatting fits into this same category. Formatting and consistency of your presentation is an important support to your overall key message.

Formatting is equally important for slide presentations or any dashboard/story presentation. It shows care when presentations have good formatting. Even when you really do care about what you are talking about, poor formatting can give the opposite impression to your audience. Don’t let blurry images, the wrong brand colors, or misaligned text boxes undermine your key messages, etc. Pay close attention to the details to ensure every part of your presentation supports your overall message.

Effective data presentation examples

1. Team’s performances with a bar chart:

The above graph shows the Top 5 Teams’ performances with their scores in different categories that help to understand which team is leading or losing for a particular category compared to other teams. Now it’s time to show the final score for all top 5 teams and below is the graph for that.

These are one of the best examples of choosing a contrasting background gives a tremendous effect on visualization.

2. Sub-Category wise Sales with Funnel Chart

Here I have chosen a Funnel chart to show the Sub-Category wise Average sales. This is a very simple chart but an idea to show is formatting. If you notice, I have removed all background grid lines for rows and columns, given the border to the bar, showing the sheet title in bold letters, and the filter on the right corner shows all color codes for all sub-categories. These all techniques make your visualization clear to understand.

3. BUN (blood urea nitrogen) analysis for sepsis Patients with Bubble Chart:

Here, I want to show how sepsis patients are affected by BUN ranges. The analysis I found is, the normal BUN range is 6–24 mg/dl and low levels of BUN can be a sign of malnutrition, lack of protein in the diet, and liver disease. Anything more than 60mg/dl, the BUN value is very high, this can be related to kidney problems. But elevated BUN can also be due to Dehydration, Heart failure, a high protein diet, and even with sepsis. BUN needs to be monitored every 2–4 hrs. till the patient is stable.

The filter on the side shows the BUN range with colors.

4. Pareto Chart:

I have used the Pareto chart to show my analysis for sub-category-wise profit. This chart can now be used for insights such as the business’ three most profitable product Sub-Categories are causing about 50% of the total profit. Pareto Charts are an effective way to quickly highlight opportunities for improvement and provide a scale for how urgently a quality control problem should be treated.

5. Interactive Dashboard with action filter:

The dashboard is the one where we put all our required sheets together on one screen and give them a nice format to make it more effective. There is one more thing you can use here is, the action filter with parameters which helps to send information between sheets.

The above dashboard is my most appreciated visualization from one of the hackathon that I have organized. In that, I have all teams’ evaluations in detail with respect to all criteria. All you need to change is team numbers one by one to display their whole evaluation on one screen.

Conclusion

The above data presentation examples aim to help you learn how to present data effectively and professionally. You surely have got the idea that graphical visualizations must be interesting, clear, and easy to digest.

Presenting data is not an easy task. But it is absolutely possible. What it takes is passion. Don’t think that you have a whole bunch of numbers and charts to show. Instead, present the information with passion, understanding, and dedication.

All the very best for your next presentation.

Thank you.

Appreciate your likes and comments.

Hello everyone,

Recently, I got a chance to be the leader in organizing a SQL hackathon for 26 teams and more than 100+ participants. The reason to write this blog is to share a few innovative ideas that I discovered during this hackathon. And I hope this can be useful to future organizers too.

Let’s first understand a bit about “Hackathon” …….

What is Hackathon?

Initially, the word Hackathon comes from the word Marathon (a long or difficult activity in a short period) and Hack, here meant as exploratory programming.

During a Hackathon, you spend a short period of time building, creating, and delivering solutions for a given task. The idea is to collaboratively code in an extreme manner, to start from scratch, and end with a goal achieved with teamwork.

What is the structure of a Hackathon?

This hackathon was purely based on SQL only. SQL stands for Structured Query Language which is used to communicate with a database. We have used PostgreSQL with one of the healthcare dataset (Hospital data) for this hackathon.

It usually all starts with a Launch of the hackathon with guidelines like the kick-start date of the hackathon, team capacity, query documentation, submission date/time/file format, evaluation guidelines, etc.

This Hackathon went up for a week. So of course, for shorter Hackathons, there is more pressure. Adding to that competition, you can quickly remember the exam periods at university. Work, work, work, drink coffee, have a quick bite, sleep very few hours, work more. At least that’s what my exams period looked like!

After the work is done, now it’s time to present their work to the whole organization. In this case, the organizing team has to look at the presentation and all the queries submitted, and they will be in charge of choosing a winning team.

Still not 100% convinced that you should participate in a Hackathon? Well, wait till you read about the benefits.

What are the benefits of Hackathon?

• Kickstart careers
• Meet new people
• Inspire teamwork
• Add value to your CV
• Encourage diversity & affect positive change
• Innovation
• Personal satisfaction

Efforts of the organizing team

Let’s begin with the efforts that the organizing team including me has to put in, to run this hackathon. I have listed out all the efforts and divided them among the organizing team to work on.

• Database Creation with ERD (Entity relationship diagram) and required installation documents.
• Prepared 150+ unique questions including a new set of expected output. Generated queries for all questions with optimization concept.
• Introduced Trello board and guided the SMPO (Scrum Master Product Owner) members.
• Addressing Participant’s doubts through slack and setting up zoom calls with the participants as required.
• Total 2800+ Queries evaluated including extra queries attempted by each team.
• The evaluation took 6 days including the final presentation in which we put in a minimum of 14 hours a day for completing the evaluation. Zoom call was kept open in order to work throughout the day.

Innovation in Challenges

We always ask participants to innovate. However, innovation does not always mean the invention of something new. It can involve translation from another setting, of existing flow, or systematization of some existing good practices.

When existing evaluation tools, processes, methods, and systems are not enough to meet current challenges, you need to draw on innovations in evaluation.

Our organizing team had only 6 members. Think for a moment that 100+ participants, 2800+complex queries to check with the most optimized ones, and a number of new functions used in each query to markdown within 5 days. In these challenging days, I created one simple Evaluation sheet which made our evaluation easy and feasible.

Evaluation Criteria

We have taken the following criteria for the evaluation of the hackathon.

• Completion of all given queries
• Generation of correct output
• Query Optimization
• New Functions
• Extra queries apart from the given ones
• Trello board (Agile methodology)
• Documentation & Presentation

We have used relational marking for each criterion to make it fair for all participating teams. This evaluation sheet has all formulas set for required rows and columns.

Here is a glimpse of my creation of a simple evaluation sheet…

Finally, with the help of the evaluation sheet, we have all team’s performance results in our hands and it’s time to present. I have created an interactive dashboard in Tableau using action filter parameters that contain,

• Team number series: selecting a team number will display everything about that particular team with their team’s name.
• Horizontal bar Chart: it gives a count of each criterion for the selected team.
• Donut charts: It displays points for each criterion score by the selected team.

Out of 1000 points, the total point score by each team will decide their position in the hackathon.

Organizing a hackathon isn’t an easy feat, but the efforts you put into arranging and hosting the event will be worth it when you see how the event turns out. I hope the above ideas and resources are useful for you as you approach the different Hackathon Challenges. Please feel free to reach out to me with any further questions.

Let’s share if you come across any resources that would be helpful for other organizers while doing this challenging work. Let’s build knowledge about how to do evaluations better together.

Thanks to the organizing team and all the participants who trusted us and made this journey memorable.

Special thanks to my backbone organizers Durgalakshmi Penugonda and @V Ramasita for being there day and night with me.

Most of us have come across various articles or documents that explain different types of DBMS keys. It always seems confusing as all keys are related to each other almost similar with slight differences which make it difficult to have a clear understanding. For that reason, I have prepared this article with simple explanations with examples, which also helps while preparing for designing your DBMS system efficiently.

Table of Contents

• What are the Keys in DBMS?
• Why do we need Keys in DBMS?
• Types of Keys in DBMS

1. Super key

2. Candidate key

3. Primary key

4. Alternate key

5. Foreign key

6. Composite key

7. Surrogate key

What is Key in DBMS?

A key in DBMS is an attribute or a set of attributes that help to uniquely identify a row in a table (or in relation to another table). A row is sometimes also referred to as a record or a tuple. An attribute is a column in the table. Keys are also used to establish relationships between the different tables. Many times, keys are used to speed up access to a large database table. A value in the key field of a table is called a key value.

Why do we need Keys in DBMS?

We know that there are countless data in the real world. For storing the data in DBMS, a large number of tables are required. These tables may contain thousands of duplicates, sorted, and unsorted records. Now, to fetch any particular or specific record, without any constraints/ restrictions from these tables is a very difficult process. To overcome all the difficulties, a new concept of Keys arose. Keys ensure that there are no rows with duplicate records in a table.

Types of Keys in DBMS

1. Super Key
2. Candidate Key
3. Primary Key
4. Alternate Key
5. Foreign Key
6. Composite Key
7. Surrogate Key

For our understanding, I am going to consider the following table.

1. Super Key

Super Key is a single attribute or combination of attributes that can be used to uniquely identify a row in a table. A single table can have multiple super keys. A candidate key and primary key can be a super key, but the reverse does not hold true.

Example:

In the above Order Table, we have chosen the Order ID, Customer ID, and Customer Contact ID to uniquely identify tuples. So, the super key set can be as follows:

• [Order ID]
• [Customer ID]
• [Customer Contact ID]
• [Order ID, Customer ID]
• [Customer Contact ID, Order ID]
• [Customer ID, Customer Contact ID]
• [Order ID, Customer ID, Customer Contact ID]

Note: You may decide to use these keys along with other attributes to identify a particular row. Example: [Order ID, Order Date], [Customer ID, Product ID, Product Name], etc. However, in the above example same [Order Date] or [Product ID] and [Product Name] can be used by multiple order entries. So, it doesn’t make sense to use them as Super Keys.

2. Candidate Key

Candidate Keys are those attributes that uniquely identify rows of a table. The primary key of a table is decided based on one of the candidate keys. So, candidate keys have the same properties as the primary keys.

• A single table can have multiple candidate keys.
• Unique values must be present in all columns, chosen as candidate keys.
• They don’t contain any redundant attributes — in our Order Table example, we don’t specify [Order ID, Order Date]as a candidate key.

Example:

[Order ID], [Customer ID], and [Customer Contact ID] can be chosen as the candidate keys from the Order Table.

Real-Life Examples:

• SSN
• PAN No
• Account No
• Passport No

3. Primary Key

The Primary Key is an attribute that can uniquely identify a table. A table can have only one primary key. Out of all the chosen candidate keys, one of the keys should be selected as the primary key.

• Unique values must be present in a column, chosen as a primary key.
• A single table can have only one primary key.
• No NULL value must be present in the column chosen as a primary key.
• A new row cannot be inserted with an existing primary key.
• Every primary key is the candidate key, but a candidate key may or may not be a primary key.

Example:

[Order ID], [Customer ID], and [Customer Contact ID] can be chosen as the candidate keys from the Order Table. Any one of them can be chosen as a Primary key.

4. Alternate Key

All the candidate keys which are not selected as primary keys are known as the Alternate Key.

• Unique values must be present in all columns, chosen as an alternate key.
• The alternate key is a part of the candidate key but is not connected to a primary key.
• In case a table contains a single candidate key then it will be chosen as the primary key. In that case, there won’t be any alternate key.
• Sometimes Secondary/Alternate key is required for the indexing, for better and faster searching.

Example:

If the [Order ID] is selected as the Primary Key, then the [Customer ID]and the [Customer Contact ID] can be used as alternate keys.

5. Foreign Key

Foreign Key is used to establish relationships between two tables.

• The relationship between two tables is known as referential integrity.
• A single table can have multiple foreign keys.
• A foreign key can have NULL values.
• You can have duplicate values in foreign keys.
• The table consisting of the foreign key is known as the CHILD table and the table that is referred to by the foreign key is called the PARENT table.

Example:

[Student_ID] in the Student_Detail Table is referred to the [Student_ID] in the Student_Mark Table.

6. Composite Key

As the name suggests a composite key is a combination of multiple columns that can uniquely identify tuples.

Sometimes single attribute fails to uniquely identify the rows/tuples in a table. However, when taken all together, they ensure uniqueness. The ‘concatenated key’ is another name for a composite key.

Real-Life Examples:

1. Name (Sam Daniel Mccormick)

• First_Name = Sam
• Middle_Name = Daniel
• Last_Name = Mccormick

2. Vehicle License Number Plat (Georgia CDS7854)

• State_ID = Georgia
• License Plat No. = CDS7854

7. Surrogate Key

A Surrogate key is an artificial key that can distinctly identify every row in the table. It is unique, updatable, and can’t be NULL. Surrogate Keys of DBMS are allowed in certain cases when:

• The primary key is too big
• Complicated Primary Key
• Absence of key

Example:

Below Anonymous Table doesn’t have any primary key. Thus, [SR_No.]is used as the Surrogate key. It is generated at the time of the insertion of the records in a table.

Real-Life Examples:

• Phone No.
• Room No.
• Batch No.

I hope you find this blog informative and help to understand the concept of Keys in the DBMS world.

Thank You.

Appreciate your feedback and comment.