• To provide an overview of the class agenda and the expected learning objectives

Coach welcome apprentices to lesson and run through the session outline and learning objectives. Coach can run an ice breaker from here .

• Understand basic concepts of a database

In a module about databases it is important apprentices understand the basic concepts. In this section you will be guiding apprentices through the basic definition of a database management system, the key concepts and key people involved in building and maintaining them. Ask apprentices what experience they have with databases to assess baseline understanding. In this module they will be learning primarily about relational databases but we will also have a short amount of time for NoSQL databases too.

• Understand the basic concepts of a RDBMS

Most of the data that apprentices will use will likely come from a RDBMS, it is structured, efficient and allows for improved analytics by providing opportunities for querying. Often SQL will be used to access the information, but that is not always true. Later on in the session we will discuss how to normalise a database, but in this section it is important apprentices are aware of key terms such as keys, normalisation, relationships and data redundancy. Use the 'bad example' to assess apprentices ability to spot pain points which will be discussed later.

• Describe the different types of key in a RDBMS

Keys are how relationships are defined between tables in a RDBMS and are important for apprentices to identify when thinking about joins. The three types of key we will be looking at are primary (unique identifiers for each record), foreign (a primary key in another table, establishes the relationship) and composite (when two or more fields are combined to make a primary key). In the exercise apprentices will look at a table and try and identify the logical keys, use this to reinforce that primary keys must be unique and sometimes you may need to add a new field (e/g/ rowID) to create this key. Issues around normalisation can be explored at this point, for example, are the apprentices introducing redundant data?

• Understand the principles behind normalisation
• Modify a table so it is in 3NF

A RDBMS is efficient because it optimises storage through creating several smaller tables with defined relationships, rather than one large table where information is repeated or missing (which takes up storage space). Make sure apprentices are aware of what happens at each stage and why it is important in the context of optimising the database- lower storage leads to quicker querying which leads to faster insights. Go through the example slowly, asking apprentices to spot non compliance with NF and what they think can be done to fix it. Examples are interspersed of redudant data and how they can be sorted. Apprentices often get confused with the difference between transitive and partial dependency, there is a slide later in the section to address this. Partial occurs only when there is a composite key and transitive indicates redundant data. Give a good 15 minutes at least for the exercise so apprentices can discuss and experiment, understanding what constitutes normal form.

• Understand the different types of NoSQL database

The RDBMS may be the most common database type, but NoSQL is becoming more and more popular. In this section you will help apprentices understand four common types of database, how they are used and why they are useful. They key takeaway is that NoSQL databases can store unstructured data and also (sometimes) remove the need for joins which speeds up data retrieval. It is possible some organisations we work with have started to implement NoSQL databases, so encourage apprentices to share their experience or research them more.

• Justify the use of Big Data in analysis by considering the benefits and drawbacks

Other than RDBMS and NoSQL, there are other ways data may be stored in an organisation that apprentices need to be aware of. If their organisation does use them, they need to be able to describe what they are used for and why they are useful in their portfolio. First you will go through historical types of database (network, hierarchical, multi-dimensional and object orientated) to explain key features and why they may be useful. You will then follow this up with a brief introdution to data warehouses. Do not spend too much time on this, as it is covered again in module 4. The key take away is that data warehouses allow for advanced analytics to be carried out on a unified data source within an organisation.

• To recap the day

Go through the learning objectives again and show apprentices the assignments and check for their understanding. You may want to create a kahoot quiz, but if you do please make sure you have submitted a create resource request first. Remind apprentices to complete session attendance log and update their OTJT.

• To provide an overview of the class agenda and the expected learning objectives

Coach welcome apprentices to lesson and run through the session outline and learning objectives. Coach can run an ice breaker from here .

• Explain the difference between a conceptual, logical and physical model

Session 2 focusses more on the design and maintenance of a DBMS after the previous introduction to them. In this section you will be guiding apprentices through the basic design process, from concept to fleshing them out logically and finally considering what the final DBMS will look like. Each stage has its own requirements and considerations which you should go through. There is a brief introduction to common data types, keep this low level as it will be explored in more detail during the SQL bootcamp. Apprentices just need to know what we mean by 'string', not anything more detailed like how they used or analysed.

• Explain why ERDs are useful

The final form of any data model is the ERD, as it shows within the DBMS the different entities (tables) and how they are related. Go through the key features of an ERD and discuss with apprentices examples. The crow foot notation can be confusing, so make sure you are clear on the relationships in the examples and what they mean. The examples are built in Lucid Chart and embedded, they may be slow to load, so refresh the page if this happens. Give apprentices time to explain each ERD example so they are clear on the steps involved and how to define relationship.s

• Design an ERD for a database

Don't spend too much time on the Spotify example, but use it to reinforce the process of building an ERD. Give apprentices 15 minutes to design the ERD from the example and time for feedback and discussion.

• Describe various debugging techniques

Like anything in life, building databases isn't always easy and problems can arise. In this section you will explore various ways apprentices can overcome these issues. The principle apply to any problem they may have in their role, especially with technical skills like coding which we will be exploring in later modules. Try to foster an atmosphere of honesty by giving examples of problems you have faced in your current or previous role and how you overcame them. This section will help develop growth mindset as apprentices should see that problems aren't the end of the world, and by calmly utilising these skills they can develop action plans to solve them.

• Understand principles behind Quality Control

Now we have explored troubleshooting techniques, we will now specifically look at how to deal with problems in our DBMS. Again, reinforce that problems can arise but there is often a simple solution. Stress the importance of having quality assurance processes in place with a DBMS so issues can easily be identified, reported and solved. A functional database will be more efficient and give analysts confidence in their results. Draw attention to the 6 dimensions of data quality as they will be referred to again throughout the rest of the course.

• Understand how to improve the performance of a RDBMS

A common problem with a RDBMS is that they perform inefficiently. Analysts write queries which take an unreasonable amount of time to run and slow down decision making. In this section you will introduce a couple of performance techniques including indexing and denormalisation that can help speed the process up. Note, denormalisation does not mean not normalising, but introducing redundant data to a normalised database to avoid costly joins. Allow apprentices time to reflect on these concepts. Typically, this would not be part of their role to perform these techniques, but a data engineers. However, if they are experiencing performance issues they can suggest these ideas.

• Describe techniques to maintain a database

Finally, you will be guiding apprentices through concepts around maintaining a database. Common issues such as metadata taking up storage, or tables fragmenting can slow down processes, including querying. Explain how these issues can be solved and again suggest that these are ideas they can bring to their organisations database owners. Discuss the other techniques and ask apprentices to consider their usefulness and where they may be able to apply them in their role.

• Recap the day

Go through the learning objectives and assignments and clarify anything apprentices may still not understand. Go to Kahoot! and run the module 1 Kahoot quiz (apprentices can download the app on their phone, or just use their browser). Make sure apprentices update tehir OTJ and complete the SAL.