Database Fundamentals

About this course

welcome to database fundamentals I'm here today with Brian alderman and we're going to talk you through a variety of really interesting things about what you

need to know about getting started with databases thanks folks for joining us I am as Pete pointed out Brian Altman I live in Scottsdale Arizona really enjoy that area we are be spending some time

as he's talked about as well talking about the database fundamentals not too deep on what we're going to be discussing but just kind of give an idea of what databases are all about I'm a

speaker for a lot of conferences specifically with SharePoint at this particular time and some sequel conferences as well so we're going to be spending some time

you're going to see some when we get in the image I mean references to SharePoint so but it's this this topic is all about sequel server and the understanding of that now Pete what

about you where are you from so I'm here I work for Microsoft and I work for the learning experiences team here I'm responsible for planning and building a lot of the technical training content

that guys like you trainers like you deliver to Microsoft customers so I have a hand in some of the technical content here I've been doing it for a very long time with content and I'm responsible

for the sequel server portfolio in addition to some of the web developer content that we have awesome so this session is a 100 level session and database fundamentals is a sort of core

technology concept that this session loosely mapped to our Microsoft Technology Associate exam for database fundamentals which is exam 98 364 so if you're looking to get started in

databases there's no prerequisite level required for them don't you have to learn I'd be able to spell SQL you have to be able to I don't even think you have to spell SQL you're gonna tell us

how to spell sq a little bit later that's a primary goal don't understand us I think the only real expectation we have for this audience is that they want to learn what a database is very good

and and we'll go from there all right what are we going to talk about today so the course is broken down into five modules the first one that we're going

to do right now is just introducing core concepts we're going to help you understand what a database is why you might want to actually consider getting a database we're going to go from there

into some of the basic concepts that make up a database we're going to talk about relational concepts including normalization referential integrity and some other topics there we're going to

go from there into how you actually create and use them what kinds of things are in a database we're going to talk a little bit about sequel server and how that works that's going to be the

platform that we use when we get into creating our database module four is going to be really super fun we're going to talk about DML which is data manipulation language we're going to

actually show you how you can get data in and out of the database which is probably the most exciting stuff I know you've got like all kinds of really fireworks enabled demos for that moans

for that and then module five we're going to talk a little bit about sequel server specifics we're talking about basic administration concepts that maybe you'll do as an

administrator or maybe you'll want to know about so that you can have a good conversation with your database administrator all right so let's dig in with the very first module where we're

going to talk about the core concepts or date with with databases and the ironic thing is a lot of people hear the word database and they don't understand how how much or it sits in but that's

involved in their life for instance if you go shopping if you go in the store this afternoon and buy some items off a grocery store way back in the old days you would probably go into a grocery

store and there'd be aisles blocked off because they're taking an inventory what's on the stout shelves what's in that in the stockroom so they even know what to order nowadays everything

because when we pick up an item off the shelf and soon's that we scan it that's being registered in a database so now they can set up and keep track of all of the items that are stored within a store

in a database and anytime I purchase anything that item count let's say we have 50 of them now I went down to 49 we can set up an alert in the database hey if this gets below 25 we need to reorder

so everything is automated if you go online and buy airline that's all information store in the database when you get ready to pick your seats that's all in the database so

every day even though we don't hear the term database we are talking and working with databases so that's why this is important for you to understand that you know the concepts of a database and

what's going on behind the scenes because it does affect you as a non IT person but it may be something that you want to get into as an IT person because there is a lot of need for database

administrators or DBA s so wrong way we'll go back this way here so we'll talk about relational databases and we're going to talk about some database components in terms kind of level the

playing field so everyone has an idea when we're referring to something what it is that we're referring to they're going to talk about a few different types of commands that we'll use when

we're talking allure than working with with sequel server so let's begin with an introduction to a database just to give you a better understanding of what the databases are all about a database

you might just see it as a DB is an organized collection of data and it's typically stored in an electronic format it's really not much different than what we've done in the past but we have a

different way to do it we allows us to manage content organize content or categorize it and more importantly allows us to retrieve that content fairly quickly traditional bait that

databases are stored in rows or records and then we have columns or attributes or properties uou those terms anonymously as it's referring to the type or the amount of content or the

information that's stored in a database now you may be thinking as I crawl Brian that sounds a lot like one of these good ole Excel spreadsheet and it is very similar to an expression sheet in the

fact that we have rows and we have columns and we've used these four for several things so Pete what's the first ring but do you remember the first time you use an Excel spreadsheet and you

thought it's the coolest thing because you can have rows of information and then columns of that information that was relevant to those rooms you know it's funny I actually still use one of

these today so I'm a little bit embarrassed about but I use a spreadsheet that's got all kinds of columns to manage my music collection my CDs okay right and I've got a row for

every CD in my library and then I have all of these columns and I'm so many columns for the person who introduced me to it and the genre of the music and the date that I acquired it's

sort of like the high fidelity movie like if you want to sort the thing by the date that you acquired it so you can look at your library in chronological order I do that right so I just I under

are just to let you know I have the same exact thing for both my CDs and my DVDs so I mean I still have three hundred some-odd CDs and they all alphabetized and and I'm not the only weird person

this is really good to know and it's funny too I'm actually gonna probably use this example throughout the course because it's the perfect thing that could make a really handy small database

and even though I've been working with databases for 20-something years I've never actually converted it to a database database but we could take that content and turn it converted into a

database and put it in the table this is an example just a brief example of a database table and that's as I mentioned it's a collection of rows and columns and allows us to organize content we're

going to focus on specifically we're going to focus on a relational databases this brings it to an entirely different level pretty much if I took this table here right may as well put this into a

spreadsheet because at this point we haven't introduced the idea of creating multiple tables to represent this this to this information which makes it and retire relationships and those we're

going to talk about all this into those these different tables so we can actually use this content for querying information and inserting information updating information and keeping this

information maintained so we're going to take an Excel spreadsheet and we're going to jazz it up by bringing in two tables or multiple tables in a database here so relational databases it's a

collection of tables now some everything's stored with 80 columns across in a single row we're going to have maybes fewer columns but we're going to multiple tables now the

relational piece comes into the sense that we have to have a way for us to be able to have those tables talk to each other so we're going to introduce the concept of primary Keys and referential

integrity and foreign keys we'll figure out how all that is how all that works for us but the idea now is taking that single row or single table as multiple columns 80 columns will say and busting

that out to multiple tables to to create environment that allows us to query that information and only store the information that we need to store and not have redundant information store in

that so that's the advantage of moving to a relational database from will say an Excel spreadsheet this is an example of a relational structure we're going to be using we're going to be referencing

throughout here and what this allows us to do is you'll see here that we have a product a table your called products or in the production schema and we've got a a product subcategory ID now this is

referring to another table and it's a product subcategory ID which in that table there is a product category ID which is referring to yet another table so this is what's called a relational

database and we're going to introduce these terms and discuss these in more detail but this uses what are called primary keys and foreign keys and so this year is a way for us to tie these

tables together so our tables can communicate and it's going to allow us to perform some of the commands that we're going to want to perform and will see us performing a little bit later on

so that that actually looks a lot like what we were just talking about where my spreadsheet full of CDs yeah in that in this example would be the products yeah and those genres in my spreadsheet would

sort of map to what you've got here for category yeah yeah for subcategory category like because I have jazz and I have like bebop as a subcategory of jazz and things like that is that kind of

what you're talking about you know and it's just going disco is not a not subcategory of jazz okay okay so subcategories that jazz disco disco jazz yeah I can't even picture that one

happening but but yeah it's exactly a great point yeah so we could take our example of our CDs that we have built into you know spreadsheet and we could create a

database and use a similar approach to get this a relational database with these tables so a great example here okay let's make sure everyone understands and we're going to go

through these sessions we want to make sure everyone understands some of the terms that we're going to be using as we're coming down through this first off you may have heard of a DBMS a database

management system so we're going to introduce a couple products like Microsoft sequel server was what we're going to be focusing on and using as our demonstrations but a DBMS is a database

management system that's a it's a programs that are used that allows the date for us to store the data and retrieve that data so a DBMS is also used for DBAs database administrators

and this allows them to be able to go ahead and manage the databases there to create the databases to add the databases to backup the databases any of those components that we would need it

allows us to do that using the DBMS so it's a collection of applications so now that allow us to not only look at the content manage the content within the tables but also the overall the overall

administrative tasks those also are managed using this DBMS now we can throw another letter in the front of that with a little bit of alphabet soup here we have an AR DBMS a relational database

management system and our DBMS takes advantage of the database management system and we make sure that we're taking advantage of the relational aspect when we're managing that content

so as I mentioned Microsoft sequel server is an example of an RDM ms Microsoft Access if you worked with that maybe you went from Excel spreadsheet and you graduated to access and then the

next year would be going to Microsoft sequel server even using my sequel if you're out there using my sequel you have those four relational databases and those are ways for us to manage your

content instead of the flat format that we're used to using with our Excel spreadsheets now in order for us to be able to install or use this content we have to have what's called a database

server a server that's dedicated to hosting our content now it could be a physical server it could be a virtual server but it's used to manage that content but from creating the databases

to the content that's stored in those databases this database server can have multiple instances what does that mean I can install sequel server several times and manage them separately we have the

same tools that we use for managing all those instances but I can install sequel server several times one may be hosting my SharePoint content another instance of may be hosting my line of business

applications and I can install that multiple times on the simples as a single server and that is what the idea of an instance is it's just multiple installations that reside on a single

server every time I do that installation there's a new copy or version of the sequel server XE that's used for managing that content so often what we'll do is create multiple database

servers and the idea this is to provide for us to provide high availability and improve performance if we have multiple servers we able to do that so let me just back up for one sec sure because I

have my spreadsheet full of CDs right and now you've gone all the way to the extreme end of things you were practically a Big Data which we're not going to talk about today but there's

this whole range of things that I can do to get my first database that start with something as simple as access which is a part of Microsoft Office where I can install it on my local machine I can

even export my spreadsheet in or import my spreadsheet into a database and start making the relational sort of connections that you alluded to these key things all the way up to a database

server that's dedicated for a professional environment enterprise application that runs an instance of sequel server or multiple instances of sequel server so I just wanted to kind

of point out that there's this range that you're talking about from getting from non-relational data that's in a spreadsheet all the way to massive multi table relational systems on multiple

servers and clusters and all that other stuff and for the scope of this conversation we're going to talk really straightforward about the fundamentals of what's in a database but Bryan's made

a really good point that we're at the very beginning of a long journey to using databases that go all the way up to professional database administration and database developers business

intelligence and and now Big Data and even databases in the cloud so there's the spectrum that we're talking about exact cross all of these sort of terms and we're going to the rest of the

session is going to be kind of on a smaller scale but we'll allude to these sort of different opportunities that you have across different business solutions and in my case my CD library management

system I'm expecting now I knew as much as I know you now that's gonna probably be done by the end of the weekend I'll expect a cycle I might do that as I own because they don't throw a bar on it

designer today design definitely all right so yeah we are going to keep it on a small scale as far as what we're going to discuss but I was as you know we pointed out this

could be to the enterprise level but we are we're not going to go to that level within here what we are going to introduce is because remember we can use access we can use my sequel we could use

a sequel server Microsoft sequel server so what we're going to do is we're going to work with a utility inside this that's provided with sequel server called a sequel server management studio

so the good thing is you don't need to know a bunch of commands there's a lot that we can do inside of sequel server inside specifically the sequel server management studio and this is just a

screenshot of it what we're going to do is we're going to go in and demo this and kind of just explore what we can do in this graphical user interface or GUI to manage databases we've managed the

implementation of the databases and even down to the table level of those of the content in the databases so let's jump out and explore we're going to go into a demo here and let's go into our contoso

database if you've got into any Microsoft class you've heard the term contoso we are going to go into I'm going to go to start in all programs and in here you're going to see that I've

got Microsoft sequel Server 2012 you'll see I've got SharePoint installed I'm going to go ahead and on that you'll see we have several tools we can use in here we have configuration tools expand

on that that's where we can use for some configuration options the one we're most concerned with the one we're going to spend the rest of today and is the sequel server management studio so we're

going to open this up and when it comes up and asks us to connect to the server so we'll go ahead and perform that connection and it brings us and it brings us in to the left-hand side we

have what's called you have object Explorer and the right-hand side we have the object Explorer details so whatever we click on on the left-hand side it gives us more detail about it on the

right-hand side let's start first off I talked about instances this is an instance a sequel server that's running here that's top-level I don't have multiple instances if I had I could use

the same tool for managing multiple installations of sequel server we're going to focus on just one in since here now in there the biggest the next big thing that we work with our

databases so if we expand databases you're going to see I've got several different databases here already including system databases that are automatically installed for every

instance of sequel server so if we expand on that you're going to see we have these four system databases we're not going to go into the detail of those but every instance of sequel servers

these four databases that are used to manage the infrastructure in the logical architecture associated with that instance of sequel server now we also now have user databases these the rest

of these besides the system databases with the exceptional database snapshots all of these here are user databases or databases that we've created we're going to be focusing on utilizing for the most

part in this session or in each session for this class the adventureworks 2012 database if you've worked with the database before seek previous versions of this by any chance you've probably

heard the term adventure work so there's a flavor of it that you can use now if your seeker demos you like hey I'd really like to explore with this you can actually go up and download

adventureworks 2012 and install it in your venture works 2012 if you're running sequel Server 2008 you can install it for there for that as well so if you're looking at

this like this would be pretty cool to be able to have this play environment you can do solve this explore look for adventure work and download the flavor for your particular version of that

sequel server that you're running and you can be able to use that that information and I think it's also important to point out that what you just showed very briefly in the initial

view of sequel server management studio it showed that there were some objects and I know you were going to talk about that later but every database system even Microsoft Access has something

similar you can open Microsoft Access you can look at the databases that are there in that case you'll see one at a time you can see the things that are in that database and there are sample

databases for Microsoft Access just like there's adventureworks for microsoft sequel server so if you wanted to try this as you're following along you could load up Microsoft Access you can find I

don't remember what samples ship with access is a nice way it's still March 1 or whatever or if it's still there I haven't open up access and a lot yeah but there are sample databases and

there's open source databases and you can see what sort of you can you can explore the relationships and products and categories and things like that are a pretty common thing that you'll find

in all of these simples yeah that's a great point so we're going to be working on sequel server management studio as our Google your graphical user interface for sequel server but yeah that's a

great point Pete I mean it's like okay well well I'm used to I'm used to still out spreadsheet right now now what's my next baby step my next baby step would be anyway maybe I try you know access

next get the idea how that works how the relationships work between the tables and then go up to you know you know upgrade to the sequel server environment and there is a sequel server Express

flavor available to so you may like hey I'm down with access I really want to go out and play with sequel server there's a sequel server Express version that you can download it's free so you can

download that and get most of the functionality that you'll see that we're working with even though we're working with the enterprise version so what we're going to do in this class here

that Express version will work for you and okay so over on the left-hand side we've got this here what we're going to be doing a lot of here as well as generating creating queries so up here I

can go in and execute queries in here so I can do a simple query this is the most basic query you can execute and look at the intelligence so it's trying to it's helping me along with this so if you're

not quite sure it's helping me along so production dot and then products if we did that right we'll find out in that object name so I can hover over it so it's alright so I'm not sure so we come

over to the left hand side we go to our adventure works we go into our tables here and you're going to see all the tables to all of these tables starting with human resources all the way down

here are all used for this adventureworks table or database excuse me so I was trying to use production picture and minister and here's my products and so it is an actual I have

an X on there it's I'm going to go ahead and then I'm going to hover over it again and you're going to see it still saying invalid product name now there's two ways what I

haven't done is I haven't identified what database I want to use so they're up if I look up over here right now it's using the master database when I connect it automatically connects me to the

master database that's one of the system databases I don't want to work with that one so I can do this too a couple ways I can come in here and drop this down and find my adventureworks

now when I hover over this you're going to see oh now we got what we you know the item eight working with now if you like to to make sure you know what you're doing another way to do this is

to preface this with the use statement and so I could do this as well stead of having to drop this down especially if I'm going to want to write out as much of this up as possible I could make that

part of the command and add in this the text use adventureworks 2012 so we're talking about what database we want to look at now I want to perform this command here so we're going to go ahead

and highlight this and this is kind of cool because if I I have several statements in there and I want to execute just one of those statements I can do so by highlighting the text that

I want to execute and then hit the execute button or hit the f5 key so we'll hit execute up here and we get a bunch of data so here's all my rows of content with the columns and like I said

this is the most basic the easiest syntax that you can write is select asterisk which means give me all the columns from wherever I want to review the retrieve the content from so we'll

be spending some time in here again I wanted to kind of show you that this is the the object Explorer and we have the details are going to be showing up here we're going to spend most of the time in

the new query window executing queries that allow us to retrieve or insert or update content but this is a just a little bit of an introduction so sneak preview of module four it's a sneak

preview of module form we're going to do a ton more in here in fact I'm probably going to resize this so you can see some of the commands here that we'll be working with but

you're going to see for this database here adventureworks we have tables we're going to introduce these views synonyms fact I said that right the first time is good program it no I spoke to a quick

programmability storage and security so all of these are per database we're going to talk a bit later on in security so we'll get that a little bit more information in detail with that all

right anything else we should show all we're in here just as an intro you think a peek you know I had a question for you if you wanted to go back to that you you said a couple of things that I think we

should clarify okay um use the word schema can you talk just briefly about what you meant by schema so really what happens is when you create a a product a table or of you it has to be associated

with an owner of that so they're using the word owner or schema is where it resides and who has access to that and our that is stored in that database so the schema or the owner of that item is

use this is kind of this is a two-part name so you actually provide the schema and the the object name you can actually go up to more up to four parts we won't get into again here yet actually we

won't get into in this specific class so it just identifies where inside that database that content or that object exists so it sort of references kind of the definition of what that object is

yeah exactly okay got it right um and then I think there's one other thing that we should talk about because we're not talking about it okay um well in my example of

the spreadsheet we talked about that I have all these rows and all these columns and it's getting kind of unwieldly to manage and you've introduced this concept of a relational

database that has tables that have used rows and columns that relate to records and properties and that whole that whole genre of sort of information and it we kind of refer to that as data modelling

right and we're actually not covering data modelling in this session but it's a really important topic that you guys will want to go read more about that is sort of the technique for how you decide

what to do with that giant spreadsheet of information and break it down into individual parts and we're going to reference that sort of thing along the way but I just want to point out that

how you actually do data modeling and how you do the design of a relational database is not specifically covered in this yeah and you're right there's entire

courses on that and that there's a there's a lot to that piece that database design is huge before you even roll out and you know any data so taking that very seriously and understanding

what you're doing there will be really beneficial for you as you're looking at taking your ginormous spreadsheet and trying to get it into a relational and we'll will allude to some of those

things as we go along but I just wanted to call out that REM we're going to talking about the mechanics of what a database is and how the pieces of it work but the next thing you'll want to

do when you actually create it is you'll need to understand that data and then do that sort of analysis and design of what the data model needs to be so we'll see some of that along the way I just wanted

to point out that's an opportunity for further study yeah perfect yeah and it's a very very important part of the the process here all right so I don't think there's anything else in here we'll be

coming back we'll be spending a lot of time inside of these these are SSMS sequel server management studio here so let me go back here oh that's why I didn't want to do this right there and

so back into our slides here but when I go here it's where we were in PowerPoint yeah we're using that guy okay so that's just a brief introduction to the sequel

server management studio we're going to kind of wrap up this module with just a little bit of a review remember a database is kind of taking your ginormous Excel spreadsheet and just

creating multiple tables and set up relationships all what we're going to talk about but it's simply a way a different way to organize that content it's not only different it's going to be

better you're going to see that we can generate reports we'll just say or retrieve content and slice and dice that content ways that we could not do in an Excel spreadsheet Mike Microsoft sequel

server access my sequel all examples that we can use for that we talked about the idea of a database management system or a DBMS so you're going to hear that term a DBMS or our DBMS which is a

relational database management system and really it's just a plethora of applications that allow you to perform different tasks associated with that database for you know we talked about

right now we looked at the database engine I mean there's reporting services out there there's analysis services out there nothing that we're going to get into but in our DBMS has several

components that allow you to manage that content that's in your database your database server or servers most likely you're going to have multiple servers whether they be virtual or physical are

used for hosting the content managing the databases and making sure we've got high availability we've got information that were readily available so that's managed using our DBMS on multiple

servers database servers most likely and then we're going to spend most of our time is in the sequel server management studio that's the interface that we're going to use inside a sequel server for

creating our databases and managing the content within the databases as well as managing content or managing the the logical infrastructure the rollout the deployment of sequel server so with that

said we're going to wrap up on this section here and we are going to go ahead and take a brief break here and then we're going to come back and continue in module 2