﻿Hello and welcome to lesson four of our databases and SQL unit. Now, I'm Ben and in this lesson is all to do with putting to practise all of the skills that you've developed in lessons one to three and putting them into practise by completing a set of tasks, using a different context and a different database to the one that we've been working on so far. So all you'll need for this lesson is your computer. You'll also need that DBMS that we've been working with so far, which was DB browser for SQL Lite. And there's also a downloadable resource for this lesson. That includes a zip folder that has inside it, a database that you can use to complete the tasks for this lesson. So once you've downloaded that and you ready, if you can clear any distractions that you might have, maybe turn off your mobile phone and if you've got a nice quiet place to work, that will be perfect. Okay. So when you're ready, let's get started. Okay. So as mentioned in this lesson, we'll bring together all the knowledge that you've gained on databases and SQL and interrogate and update an existing database. Okay. So let's start off with an activity where it remind you of some of those key database structure, concepts that we learned about right back in lesson one. Okay. So new contacts then. Okay. So our local leisure centres in the process of building a new swimming pool, now they have asked you to set up the database for them and help them manage their swimming lessons. So my first question is how would you make the links between the following three tables? Because there could be an argument that without the links or the relationships, this is a flat file database, but we've got data, data stored in three separate tables. So we need to make the links between them or the relationships between them. So how would you make those relationships? So if you pause the video now and think about what fields would you link between which tables. Okay, so hopefully you've got an answer now. So this is the answer that I came up with. So it is this. So what I've done is I've made a link between the course ID in the TBL courses to course ID in TBL lessons. And I've also made the link between the member ID in TBL members and the member ID in TBL lessons. Okay. So that's how I made the links. So hopefully you did something similar. So the next question is, what type of relationships have I displayed on these three tables? Now it might help you to remind you a couple of things. So first of all, this is crow's feet notation. So the question is what does that crow's foot actually notate and with the three lines. I'll also give you another clue that we're there. We discussed three separate types of relationships. So, in lesson one. So that was a, one-to-one a relationship a one to many relationship or many to many relationships. So which one do you think it is? Okay. So hopefully you shouting out something that's screened to me right now and you've got the right answers. So the answer is eight, a one to many relationship. Okay. So hopefully you got that. So just to remind you of what that means, there's an example here, where if we look at member and the member ID in the members table, a member can only appear in the member's table once. So that members ID will only ever appear in the member table once, however, that member can take part in many lessons. So the question... next question is which fields in below are considered to be primary keys? Okay. If you got an answer for me, so we should have hopefully course ID. So in the TBL courses, course ID was really a primary key in TBL lessons, it was lesson ID and the TBL members, it was member ID. So those are our three primary keys. So next question is, which fields below a considered foreign keys? have you got an answer for me? Save and shout out it out on three, two, one, three, two, one, they were course ID and member ID. Okay. And they are in TBL lessons because if you remember, we have a primary key in TBL member's is member ID. So that ID will only ever appear once in my TBL members, but because that member, can take part in multiple lessons, the member ID for the tables from TBL lessons can appear in many times, but therefore, it's a foreign key because it's referencing the primary key in another table. So this brings us onto our swim challenge. So I've kind of already set the scene for you. So let's just set the scene a little bit more. Okay. So a local leisure centre in the process of building a new swimming pool, and you've been asked to set up a database for them that will help them manage their swimming lessons. Now part of this database has been made for you. Okay? So this database has been made for you. You need to download as it comes with this lesson, you'll download a zip file. And once you extract that zip file, there's that database that you can work with. Okay. Now you need to work through the tasks on your worksheet that require you to write, execute, and test SQL scripts to complete the database. So you should use this lesson and part of next lesson to complete tasks, one to four. So spend roughly about 30 to 40 minutes in this lesson, knowing that you'll also have part of lesson five to complete these task. So don't worry about it. If you haven't finished all the task after you spent about 30 to 40 minutes, work on this, because it will be time allocated in lesson five to complete this. Now I'd like you to pause the video and like to go ahead and download that file. If you haven't done already, I'd like to open up a DB browser for SQL Lite. And remember then once you've done that open that new database and you'll be ready to start the tasks on your worksheet. Okay. Now the key to success here is number one, have fun. Okay. This is meant to be a nice way to kind of switch contexts, but use the skills that you already have developed over the last three lessons. There's nothing that's going to test you here that you haven't already covered. So be a great problem solver. If you can't remember how to do it, then that's absolutely perfectly normal. I fully expect you to go back to the work that you did in lessons one, two, and three, and remind yourself about some of those queries that you were, that you learned how to create. Okay. So once you've done all of that, so, like I say, spend about 30 to 40 minutes on it. Don't worry if you don't get up to task four because there will be next lesson. And I really hope that you enjoy it. So good luck. And I'll be here when you get back. Okay. So how did you get on with that? and that's all for this lessons. If you've completed task one to four, you've done an excellent job because not only have you completed the task, but you've also been a great problem solver by going through and remembering all those tasks that we've done in lesson one to three, and putting these skills into practise. So hopefully you feel a really nice sense of achievement with that. Okay. Now I would really love to see the work that you've done so far in this lesson. I'd really like to see the databases that you're starting to build and manipulate. That would be really fantastic. So if you'd like to do that, please ask your parent or carer to share your work on Instagram, Facebook, or Twitter, tagging @OakNational and using the #LearnwithOak. So hopefully you're looking forward to lesson five, which is the last lesson in this unit where you continue with those tasks and complete your swim challenge database. Okay. So I'm looking forward to seeing you then, and I'll see you in less than five.

Which field is the primary key in this table?

What are the relationships between the tables?

How many records would the following query find?SELECT * FROM tblCourses WHERE Start_Date = “2020-10-04”; (Image source: DB Browser for SQLite)

Swim Challenge Part 1 -

In this lesson, we will use our SQL skills to build, interrogate, and update a database to manage swimming lessons.

Lesson overview:Swim challenge (Part 1)

Core Content

Databases and SQL: