From the course: Learning Data Analytics: 1 Foundations

Learning database datasets

From the course: Learning Data Analytics: 1 Foundations

Learning database datasets

- [Instructor] Sometimes it's not spreadsheets that we're tasked to learn, sometimes it's databases. So let's take a look at how I learn a new database. One of the very first things I'm going to do is look at the names of the tables. Do they seem to be meaningfully named? I see things like dates, login log, subscriptions, users, users subscriptions, video categories all the way through to video subscriptions. Super interesting. So now what I'll do is actually take a look at some of the data. Okay. I'll start with the login log. I'll double click that. I see a lot of IDs here. This means that I'm probably going to tie things together. Looks like I'll tie users, probably to users. I see company ID, I see login dates and times. Let me go check these data types. I'll go to the design view. So I see that login log for this database is an auto number. I see that user ID is a number. And thankfully I see the login date and time is actually a date and time. Fantastic. I'll go through each one of these tables, right click and go to the design view, and I'll look at all of the different information. Okay, let's take a look at videos. Right. I noticed that there are 42 videos. That means any report that I write on videos is going to have some version up to 42 videos. So I would never see videos in the numbers of 84, right? Only 42 videos exist. However, maybe a user watched video, maybe more than once, I would say it counts there, but you get the idea. All right, let's take a look at the design view. Okay, I'll just scroll through and take a look. I'll also look at the field properties below. Okay. So once I've spent a little bit of time looking at the table names, the table values and the data types. I want to take a look at the relationships. So I'll go to the database tools, and choose relationships. Now, if you ever open up a database and all of the relationships don't show, or this is blank, you might want to choose the all relationships button. Now I see this database does have relationships, and I see primary keys are established. And I know that because I see the primary key shape. So if I came here first, I would have a lot of information. Okay. Also make note of the fact that not every database you open has relationships like this, even though it may be relationally designed. All right, let me show you another trick. So I want to see everything that videos has associated to it. So I'll go back to those database tools, and I'll choose the object dependencies. And I'll choose objects that depend on me at the top. And I noticed that DBO videos played depends on the video ID. And I also noticed that the ability to interpret query also depends on videos. Let's see what it depends on. I'll choose refresh. Okay, so just videos played. This will help you walk through the database just by seeing what's associated to each object. All right, let me close that. Once I've looked at the tables, then I'll move on to the queries. So I see the ability to interpret query. I'll go ahead and open that up. I noticed that there are 1,769 records included here. I'm going to go ahead and take a look at the design view. I see that this query has these tables and I look at their join types. Now let me look and see which fields are available. Perfect. Okay. I'll go ahead and close this, and I don't need to save any changes. Now I see two queries managers and managers only. My mind tells me that if I open up managers only, I'm going to see only managers. When I double click and run this query, I really see a bunch of records. I see 620 records with video names, but because it's called managers only, I'm assuming it's only showing the users who are managers. I wonder how they make managers. So I'll go ahead and change the view. I see they're using that managers query to control what records show up. They're leveraging those joins. Okay, great. So, means to me, I need to look and see how they define a manager. I'll right click and go to the design view of managers. And I'll take a look at the inside of this query. So it's using the user's table. And then if I look in the criteria field, it's telling me that any user ID that's greater than 5,000 is actually a manager. That would be a business role that I might want to document. Okay. Interesting. I've learned a lot here. I'll go ahead and close that without saving. Go ahead and close this without saving. It's important to just start getting an understanding of tables and how they're named, and how their fields are named. You can work backwards from queries. It's a great starting point. Just get an idea of what's available to you in the database. This is going to help you be more successful, faster.

Contents