Database Fundamentals for Beginners | Database Tutorial

welcome to database fundamentals I'm here today with Brian alderman and we're gonna 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 alderman 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 gonna be discussing but just kind of give you 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 gonna be spending some time you're gonna see some when we get in the image some in 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 and 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 gonna tell us how to spell sq a little bit later that's a primary goal don't understand I'm actually the only real expectation we have for this audience is that they want to learn what a database is very good 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 gonna 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 gonna go from there in to how you actually create and use them what kinds of things are in the database we're gonna 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 gonna be really super fun we're going to talk about DML which is data manipulation language we're gonna 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 demos 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 gonna 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 of what's on the stout shelves what's in the stockroom so they go 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 a 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 tickets 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 it's 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 with and 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 it 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 well we have a different way to do it we've got 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 no Pete what's the first ring but you remember the first time you use an Excel spreadsheet and you thought it's the coolest thing because you could 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've added 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 and so you can look at your library in chronological order I do that right so I 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 outfit eyes 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 cherán and converted into a database and put it in the table this is an example just a brief example of a database table that's as I mentioned it's a collection of rows and columns it allows us to organize content we're gonna focus on specifically we're gonna focus on 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 up to any information and keeping its information maintained so we're gonna take an Excel spreadsheet and we're gonna 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 stuff everything stored with 80 columns across in a single row we're gonna have maybes fewer columns but we're gonna have 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 gonna introduce the concept of primary keys and referential integrity and foreign keys and 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 an environment that allows us to query that information and only store the information that we need a 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 here called products or in the production schema and we've got 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 gonna 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 gonna 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 per 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 it's just going disco is not a not subcategory of jazz okay okay I'm categories 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 a 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 gonna as we go through these sessions we want to make sure our view 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 system that was a it's a really it's on several programs that are used that allows the date for us to store the data and 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 from 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 was a little bit of alphabet soup here we have an our 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 our DMS 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 our 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 simple 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're 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've alluded to these key things all the way up to a database server that's dedicated for a professional environment an enterprise application that runs in incidents 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 gonna 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 gonna the rest of the session is gonna 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 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 oh my dreams I have because I'm a designer database design definitely alright so yeah we are gonna keep it on a small scale as far as what we're gonna 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 in 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 gonna go in to 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 gonna see that I've got Microsoft sequel Server 2012 you can see I've got SharePoint installed I'm gonna go ahead and expand 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 gonna spend the rest of today and is the sequel server management studio so we're gonna open this up and when it comes up and asked us to connect to the server so we'll go ahead and perform that connection and it brings us and it brings us into the left-hand side we have what's called an object Explorer and the right-hand side we have the object Explorer details so whenever we click on in 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 at the top level I don't have multiple instances if I had I could use the same tool for managing multiple installations a sequel server we're going to focus on just one instance here now in there the biggest the next big thing that we work with our databases so if we expand databases you're gonna 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 gonna see we have these four system databases we're not going to get into the detail of those but every instance of sequel server has these four databases that are used to manage the infrastructure and 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 exception of database snapshots all of these here are user databases or databases that we've created we're gonna be focusing on utilizing for the most part in this session or in these sessions for this class the adventureworks 2012 database if you've worked with a 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 feels 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 just explore look for adventure work and download the flavor for your particular version of 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 but 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 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 it's still marked when you are in a forever if if it's still there I'd open up access in a while 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 gonna be working on sequel server management studio as our guru your graphical user interface for sequel server but yeah that's a great point Pete I mean it's like okay well I'm used to I'm used to throw 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 of 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 done 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 gonna do in this class here that Express version will work for you okay so over on the left hand side we've got this year what we're gonna be doing a lot of here as well as generating or 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 intellisense 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 about 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 gonna see all the tables - 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 think you're in Lister and here's my products and so it is an actual I have an exon there and so I'm gonna go ahead and then I'm gonna hover over it again and you're gonna 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 so when I connect it automatically connects me to the master database so that's one of the system databases I don't want to work with that one so I can do this okay a couple ways I can come in here and drop this down and find my adventureworks now what I hover over this you're gonna see oh now we got what would you know the item that you're working with now if you like 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 if especially if I'm whenever 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 gonna go ahead and highlight this and this is kind of cool because if 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 oestrus which means give me all the columns from wherever I want to review a 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 I'm gonna be showing up here we're gonna 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 it's a sneak preview of module 4 it's a sneak preview of module 4 we're gonna do a ton more in here in fact I'm probably gonna resize this so you can see some of the commands here that we'll be working with but you're gonna see for this database here adventureworks we have tables we're gonna introduce these views synonyms of the fact I said that right the first time it's good program but I spoke too quick programmability storage and security so all of these are per database we're gonna 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 over in here just as an inch are you like a peak you know I had a question for you if you wanted to go back to that you you've said a couple of things that I think we should clarify okay um you use the word schema can you talk just briefly about what you meant by schema so really what happens is we create a a product at able 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 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 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 and then I think there's one other thing that we should talk about because we're not talking about it okay well in my example of the spreadsheet we talked about that I have all of these rows and all of 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 gonna reference that sort of thing along the way but I just want to point out that how you actually do data modelling and how you do the design of a relational database is not specifically covered in this session 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 will will allude to some of those things as we go along but I just wanted to call out that we're gonna talk kind 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 the the process here alright 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 are these 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 we're gonna go here and it's where we were in PowerPoint all right yeah we're using that guy okay so that's just a brief introduction to the sequel server management studio we're gonna 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 gonna be better you're gonna see that we can generate reports we'll just say or retrieve content and slice and dice that content in ways that we could not do in an Excel spreadsheet mike-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 gonna 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 gonna 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 gonna 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 gonna wrap up on this section here and we are going to go ahead and take a brief break here and then we're gonna come back and continue in module two welcome back to database fundamentals this is module two of database fundamentals and it is about relational concepts in this module we're going to talk about referential integrity and normalizing normalization which is another big chop indefinitely that's right and I think we're gonna get into a couple of other small topics around constraint we show you I'm going to introduce constraints that it's at this point so we have an idea as to you know how they'll help with the referential integrity piece of it but this is model two of a five module course if you've skipped module one we talked about some of the core concepts about databases and we'll be going on into other fireworks demos later on about how you create objects in your database and how you get information in and out of your database and then a little bit about a minute administration of your database but now we're gonna get into some other relational concepts so not quite data modelling yet but we're gonna talk about sort of the concepts that are the fundamental pieces you'd need to know before you can actually go and create a database well you brought up a good point in the previous session where we talked about the idea of data modelling so this really focuses on that aspect of data modeling in the sense okay I've got my a ginormous Excel spreadsheet you guys have got me convinced I want to move it into Access or sequel server how do I go about doing that well how do I decide what do I take into consideration when I'm actually going to design that model that we want to use inside a sequel server access so what we're going to talk about here is what's called normalization and we're gonna look at what referential integrity and constraints pretty much what we've already discussed here so the idea of normalization is really it's kind of it helps us decide how we're gonna build our our tables out for our content what this does for us is it allows us to use a strategy that's been designed to determine what content is going to be stored where so normalization is the process of organizing that data in a database within tables and establishing relationships between those tables we hinted to that in the previous session the process is used to also help eliminate redundant data so we go back to our DVDs we had talked about that we have you know hundreds of DVDs and if we have a John raw of Jaws I might have that title in there 50 times 500 times it depends how many you know DVDs I have our jobs related so the idea of that is just kind of a bit overwhelming so if you look at a spreadsheet that's got thousands of rows and 30 40 50 columns across if you look at it you're gonna look and see a lot of redundancy and one of the things that helps with normalization or a normalization helps with I should say is the fact that we can eliminate that redundancy by default there's five normalization forms as you can see here one is eliminating repeating groups all right eliminating redundant data is number two in the third levels eliminate columns that are not dependent on the key will introduce what that means in just a little bit four and five are isolated independent multiple relationships and semantically related multiple relationships we're not gonna drill under that was at all I just want to show out there if you look up normalization you're gonna see five levels most databases are designed to three and that's what we're gonna focus on here so the first model we talked about we're not going to talk about data modeling and now he's showing me that there five normal forms of normalization and we're gonna talk about just the first couple of them so this is another like this is a huge school of thought around what normalization means and you go really really academic but yeah we're gonna keep it simple and talk about those first couple yes but it's another opportunity for further study yeah this could be ours and another whole entire class but you guys have a normalization for like three days exactly alright so first notarization how do we how do we work with our content the first normal form means the data is in an entity format which means on all of these conditions have been met we've eliminated any repeating groups and by the way we have an example of this so you can see this with like okay it looks good in print and tax book what does this mean well we're gonna demonstrate this for you create a separate table for each sort of related data what we talked about that whether CDs and we're gonna create a table that instead of having stored in Excel we're gonna create a table for you know for our D armed artist and maybe for agendas and for the different different types of tables and then we need to kind of tie those together and we're gonna identify each set of data within the table we're gonna tie that using what's called a primary key and we're going to introduce that as well we're not gonna use multiple fields in a single table to store similar data that's pretty much what a spreadsheet does for us we don't want to do that we want to avoid that so we're gonna set up this relationships between the tables to allow us to create that and we do this with this is just introduced in the first normal form our second normal form ensures each attribute or each column describes the entity so okay if we go back to our our CD our CD is gonna be we're gonna have a role for CDs we want to make sure that every entity or every column in there is directly associated with that CD so it's gonna be and we're gonna actually tie these tables together we're gonna be able to reference one table or CD table to our general table by using a form with a foreign key and again we'll promise you we'll show you what that means here records should not depend on anything other than the primary key that's in that row or that table and we'll and again we'll expand on that and we can create what are called composite keith's composite keys in order to make something unique I may have to use multiple columns so we could and more explain that as so we could create what's called composite keys to make sure that we have the uniqueness in those in those rows third normal form checks for what are called transitive dependencies the eliminate fields that do not depend on that key so I might have extraneous information and in there that's not relevant and I want to put that in a separate table so I may do that anyway as part of the third normalization of the third normal form when I'm massaging that data to make sure it's all in that third normal form we're going to make sure there's no no columns in there that aren't directly related to that CD all right and again we'll show you that here in general if the contents of a group of fields apply to more than a single record those require a separate table so again just conceptually alright Brian you've got me convinced we want to take our Deaver CDs when we want to put them in to take them out of spreadsheet put them in a database how do I do that so we're gonna look at how we're gonna address that using this third normal form as I said there's a couple other ones there's a fourth one called in the fourth normal form called you know bcnf so I don't to try to pronounce the names we're not gonna worry about those there's a slight risk of not having the perfect design but it shouldn't affect the functionality if we don't implement the fourth and fifth normal forms and that's why we're not even address those at this point year alright let's take out of this table here this table here is kind of like a spreadsheet at this point but we've got unnormalized table we've got a table here that has student student number to be more precise advisor the advisors room number where we can find that individual and then for student 10:22 they have a class 101 - 0 7 class number twos 1 4 3 - 0 1 1 5 9 - 0 - this is an enormous table at this point so what we do is over the next few slides we're going to take that content and we're gonna normalize that content we're gonna do to implement first normal form first then second and then third and you'll see what we have for a result of that well if you remember correctly the first normal form as it says here is no repeating groups should be in that table so notice what we've done here we've taken class 1 2 & 3 which was a repeating entity and we've our group and what we've done is that now we're gonna have student so now we have student we have student 1 0 to 2 with the same adviser with the same adviser room in the same class number so you can see we got rid of the repeating groups but now we've got some redundancy we're gonna address that in the next normal form in the second normal form so we've taken our unnormalized data and we've added some additional rows to it and we've consolidated the number of columns that are in this table all right so we've taken that content and we now have no repeating groups because we've applied the first normal form process now as we move into the second normal form we're gonna eliminate eliminate redundant data so now what we have to introduce we've taken that one table and we've created two tables the table now is called students so for student 1 0 2 to his advisor or her advisor is Jones and that advisor can be found in room 412 student 4 1 2 3 has a different adviser she can be found in room 2 1 6 so that's a separate table now if we go to registrations cuz we still would can't just drop the information about the registrations we created a second table for the student number student number 1 0 2 2 it's gonna be attending class 1 0 1 - 0 7 as well as 143 - 0 1 as well as 159 - 0 - that's a pretty late low don't you think only three classes come on dude don't be a slacker here anyway and then student number 4 1 2 3 also taking three classes so we've taken the content we've split them out now after first normal form we've introduced a second table that's going to allow us to avoid or eliminate that redundant data let's take a tour in next level or third normal form eliminate data not depend it on the key let me go back just one second here we're gonna see here that and the student we have 1 0 - 2 we have with the class number but we have multiple rows and you'll see the students that was under British registration excuse me the students we have student advisor and advisors room so if we go to our third normal form we've got faculty we've got student number 1 0 2 2 their adviser is Jones now remember if I go back I'm gonna flip back one more time when I look at this I that student number I've got Jones and I'm good the advisors room is 4 12 well the main item of the main column and into your student number that advisor room is not too correctly associated with a student number so it does not belong there if we're gonna normalize our content hence what we do here is we introduce a third table now we're gonna have a student my rows are off a little bit I'm gonna have our student which is 10022 their advisor is Jones now I want to figure out where's Jones where the room number is for Jones I'm gonna jump over to the the table called faculty and that faculty table is going to have the room number and also we're gonna include some additional information about mr. Jones and that's the department number that they belong to all right so again no redundancy and every table or every row in both these tables so far are directly associated with student number so we've got student number the direct Association advisor the advisor the direct Association is the room number in the department number now we go to our registrations table and we have student number one zero two two that's gonna be attending class one zero one zero seven one zero two two will also be depending 143 - zero one and as you can see the it rolls down that way so we applied the third normal form by looking at these because now we have our student number as one table and it's mapping to the faculty table using this adviser name and it's also mapping to a student table using the student name so we've taken this soup red sheet or this unnormalized table and then we've created three different tables out and we've applied the third normal form first normal form second third second normal form in third no form to get that to what's called third normal form in the normalization process you you may have just blown my mind a little bit can we go back oh that's all them slides and look at the original way back here so here's our original yeah so I just want to just talk through that one more time we've got student information we've got the adviser information we've got the advisors room and we've got the schedule for each student in one table that's the unnormalized table slide and you just kind of took us through the three levels of normalization and we ended up with a student table an adviser table can you go on to the next slide there it's a li and our registration table right so the the student the faculty and the registration so we took that one unnormalized sort of spreadsheet like exactly yeah and you broke it up using these basic principles of normalization into three discrete entities of information and just summarize that for me so we went from one big table to three different ones why is that gonna be valuable to me in my database well when you see it so what now when we see what when we start retrieving this content so first off we're not gonna have a bunch of but if you go back to our CDs we're not gonna have a bunch of jazz in there a hundred thousand times or fifty times we're gonna eliminate a lot of redundant data so now if I was if I normalize an Excel spreadsheet that a thousand rows and eighty columns and was able to normalize it in database tables I'm gonna have less data because gonna be stored in there because I'm not gonna have jazz in there 500 times I'm gonna have jazz in there once it's gonna be a one table one time and I'm gonna reference that table every time I have a CD in there I want to reference that table say yeah jazz associates too you know general ID one two three and I'll reference that so I'm actually creating a cleaner environment less redundant data and all my columns that are associated or within a table the new tables are all relevant to each other they're all associated with that with that primary column we're gonna talk about that but what that means is just a few moments we've already introduced a term the primary key and we're gonna actually roll that out and show you how we do that just a few moments here so we've kind of cleaned it up we've got more tables you think well I went from one table three but now when I get ready to retrieve it we need at module four and I want to be able to slice and dice this information it's gonna be so much easier for us to do so then what we could could do in an unknown wise data great table Thanks yeah that looks really good great thanks all right referential integrity so this process that we just talked about to so let's just review a little bit on the normalization if you're thinking about where do I start you can use the third normal form for a second third normal form to try to look at the content the job in your in your spreadsheet or in your unnormalized data table and start it's like alright how can I create this environment this object modeling that we were talking about earlier to create this environment that allows us to have this data and implement that cleaner environment by using the up to the third normal form referential integrity is what we use to get those tables to talk to each other so now I've got three tables Brian it sounds like it was a lot easier to have one table because they were all in one spot granted it may have been easier for that piece but what we're gonna be able to do with this content when you eat on a slice and dice it was we're gonna see in a couple modules is really blows away the idea what we can do now with our when we're in an Excel spreadsheet and we go to the top and we can click the column header and sort of ascending versus descending we're gonna have a lot more we can do with that so that's that's the good news referential integrity is getting those columns those tables to talk to each other so this includes or you'll see this reference a referential integrity is our I it's a concept to ensure that you have relationships between the tables that we just created are I can ensure that the data is clean and make sure that the data that we're adding is valid data so not only have we separated content out we've now created an environment that's going to enforce that when I'm adding data it's going to be valid data and we're gonna do that by introducing what are called primary keys and foreign keys so if we go back to let's go back I'm gonna go back a couple slides here if we go back to here our student number is a primary key we'll say and what I can do is I can have it our result I'm sorry as a foreign key and my bat again my registration is has a student number on it and it also has a students over here in this table here so we're both using one zero two two so we're do this we're going to reference one table to the other table one table will have the primary key in one table will have the foreign key so the foreign key is used to validate the content that are the value that I'm adding is a valid valid value kind of sounds a little bit redundant but it's not so if I'm using the students my registration when I go to register if you look at 10:02 two when I enter in if I type in one zero two one it's gonna be popover because that's a foreign key over the students here it's gonna jump over there and say there is no one zero two one so not only does it enforce the referential integrity it enforces that we're adding valid content by making sure a student at 1:02 to exist or student 1 0 to 1 exist before it allows me to add content so not are we gaining the normalization of having the ability to slice and dice it's actually performing data integrity by ensuring that I'm adding content that's that's valuable so if there was no student 1 0 2 1 and I try to add content into registration it's gonna say dude I can't work with you because there's no student 1 0 2 1 but being there is a student a student 1 0 2 2 when they register for classes it's gonna check to see student 1 0 2 2 exist in the students table it's gonna also check to see who the adviser is and if there's information that we need about the adviser we're going to be able to write query select statements to be able to retrieve that content so that's the idea of primary keys and foreign keys and allowing us to be able to reference the content between tables that's that referential integrity to get us the ability to kind of double check or cross-check with it with the tables that are involved to make sure it's the content that we're adding is valid and to make sure that we're able to retrieve this content as we as we move on down the road and we look at some of the some of those select statements now we'll talk more about this we start looking at the keys but with these keys involved we can only have one primary key on a table and there may be other cross-checks that I want to perform to make sure they're valid so we can also apply what's called a unique comes constraint or a unique key constraint what this does for us it allows us to not only just do a cross-check on an individual column with it that's associated or a composite key associated primary key but also I can do cross checks on what are called unique and not that what that does is enforce uniqueness for us triggers can be used to enforce referential and referential integrity as well they require some code and a trigger example would be every time I insert a column I mean it may be as simple as send an email to the advisor saying that there's a new student that you that's been assigned to you so it could be that simple when a certain action takes place that trigger performs tasks or multiple tasks it could be as simple as sending an email to the advisor saying student now 1:05 7 has been assigned so they receive an email saying that they have another student that may show up that their door are looking for some assistance when it comes to the performing the advisor role so that's referential integrity anything you want to add to that I kind of just blew my mind to get them back up I got a good knock you went all the way to triggers and what happens with respect to referential integrity when something is added to a table you can evoke a trigger which invokes functionality and I'm still thinking about my CD library I'm trying to imagine how that's going to come into play so that's another super advanced topic we're gonna we're gonna get a little bit in the next couple of modules we're going to talk about some of the functionality you can do in a database management system where you can actually invoke some of the code some programmatic code on an event or a trigger on a database but I think I think we're gonna get into a demo here in a minute where we'll be able to summarize some of this but I think normalization pretty complicated because I went from a whole bunch of columns in a single thing to a whole bunch of smaller pieces that have fewer column thanks I kind of understand that even though we're not going to talk about creating tables like like actually creating a table we're gonna talk about the next module thanks well now we're conceptually I get the idea that I take this unnormalized collection of data which is my CD library or your original example of the inventory was a good one with a whole bunch of products and all bunch of metadata or columns about the product on a shelf at a grocery store right I can see how I might break that down by brand or I might break that down by category category seems to be a common threat across all of these yeah thank you pretty good right but it gives us a logical way to put things together so that they're in discrete pieces where I'm gonna be able to I think use them and a smart way to answer different questions about my data that I have a really hard time with in this sort of unnormalized spreadsheet like view exactly right okay good yeah I'm tracking you good recap definitely yeah yeah but yet rigors seriously triggers is like 300 level yes keep it on the low will keep it yeah I just want to introduce the fact there are there are ways there tasks including triggers that can be used to enforce referential integrity get that deep in this class but they are out there for us alright so methods for enforcing referential integrity we just introduced primary key foreign key unique constraint indexes we haven't talked about yet and triggers here's the idea of a composite key so I may have a column I may have a table that for instance let's say we have a table that contains driver's license and if I go in and my driver's license for Arizona is 1 BR 1 BR 5 4 9 if a my remember that reference to something that's an old TV show anyways if my driver's license is BR 5 4 9 and I live in Arizona and Pete's driver's license is also BR 5 for 9 how can we make that a unique key in a table we can't by itself but what we can do is include another column called issuing state so now my composite key would be BR 549 a Z his would be BR 5 4 9 W a so now those two together makes that data unique okay so that's what a composite key the primary key has to be unique it may not be possible by doing it with that one column so we may have to use multiple columns if we do is more than our two columns or more it's considered a composite key alright so if you see that because sometimes you might see where it says something about having a primary key is like well it's got three columns well in order for to enforce uniqueness we need three columns so that's what the that's what that's alluding to when you see that composite key option here all right so let's go say it's still staying with referential integrity we're talking about constraint now here's that primary key constraint and I just mentioned this a little bit already kind of jumping I had a primary key is on one or more columns it's required to provide the uniqueness and in a row in a table that helps enforce the referential integrity often it'll be referred to from another table using a foreign key but a primary key is an attribute or a set of at root attributes that uniquely identifies that row so if it's a set of attributes its that composite primary key that we talked about a the table can only have one primary key on it not one column but one primary key that could be a single column or multiple column and a column that participates in this composite primary key or individual primary key cannot allow null values which means content has to be out of there so I mean I can't just get bio and I'm adding content so if you're gonna make a call and part of a primary key constraint then it requires content being added to that and you can enforce that when you're creating the columns and we'll see that a little bit later on so a primary key is used for that the foreign key is used to reference that content so the foreign key is a column or a combination of columns so if the primary key uses two states two columns that we did with the license number and then state code while the foreign key referencing that has to use that dimension to reference it as well so it references it as every unique constraint that's created over there for that primary key I'm like a fake primary key and foreign key can't have no values so be careful on how those two are implemented because they're implement is slightly different but you're gonna see that a primary key is going to be accessed by the reference by a foreign key so it's going to allow us to enforce that referential or data integrity by making sure that we have valid content when we're adding our content into our tables so this is the diagram we saw earlier and what I did and we all we've done here is we introduced earlier on that we had two foreign key which are the blue items and we have the primary key which is the red items so now what happens is when I add a new product into the products table over here when I type in a value for product subcategory ID it's gonna jump over to the products subcategory table and make sure that this prime adjustment foreign key references a primary key here now inside of this table is like oh by the way this is a subcategory if I'm going to add it when I add an item to this this product subcategory it needs to make sure there's a a major category at the top category so it's going to check this table down here so see how there's all sorts of cross-checking that's going on as I'm adding content when I add an item here it's going to double check to make sure that some valid item here when I add item here it's going to make sure it's a valid item here this product category ID here is a foreign key that references the product category ID in the front in the product category table so all this cross-checking is making sure that what I'm adding this content it's all valid content that I'm adding and these tables are tied together the relationship is built between these tables using these keys can we talk through that one more time sure let's go look at that slide so we have three tables so I'm gonna invent it I'm gonna go back to your grocery store example here for a minute we've got a spreadsheet full of all the products with categories and subcategories and it's in one thing right and I went through the exercise of denormalizing my data which led me to creating these three tables and then with the referential integrity concepts there are these specific relationships that include primary keys and foreign keys in these tables that kind of manage the relationships between these tables correct right so let's just go I'm gonna give you one example and you can I'm gonna try and talk through this you can correct me if I'm wrong alright so in my grocery store we sell strawberries okay and so in this example and you've got a whole bunch of columns I'm not gonna worry too much about them but strawberry is the product it's got some product ID which is unique and I can see there's a key next to it so that's the primary key and the product table is a product ID so we'll just say strawberries number one okay product ID one name is strawberry it's got some product number that's involved in my inventory system its color is typically red those kinds of things exist about the product strawberry right and then the product category which is the on the right side of the slide at the bottom we're gonna say that there's a product category called produce nice right and the name is produce and it will have a product category ID of I don't know maybe produce is 25 okay right so the subcategory of produce for my strawberry is going to be fruit right or berries or something like that right sounds good for what memories fruit strawberries the fruit and the tomatoes that tomatoes that weird one is I get confused mate oh I think it's a fruit I think it we're going to the Holy Father into yeah so subcategory of fruit has its own primary key for fruit but then has a foreign key relationship to the category which is pros correct and then back on my product side when I list the category the subcategory specifically they're a fruit it's gonna have a foreign key over to the primary key in the product subcategory table exactly so I've got this this list of stuff that includes all of my products and I started with a spreadsheet but if I want to get it into a database to denormalize it which means I need to think about all the redundant data and how to simplify it down and then I have to figure out how to have relationships between these tables and include primary keys and foreign keys so that I can keep my integrity my data all together so that I can uniquely reference things like the category and subcategory and the product name and those kinds of things yeah do I have it explanation okay that's Lou yeah so those guys make sure the data is being added is valid data that's good it's what we want to make sure again we take take it our denormalize table or you know unnormalized table we've normalized it it requires some additional tables we got to get those tables to talk to each other they need the relationships no primary key and foreign I can start to see where we're going now because in my spreadsheet I have fruit listed in one column for everything that has a row that is a fruit mate so I've got strawberries and bananas and everything just says fruit right but I don't have any idea how many categories I have because fruit is listed 25 times a pack and you know crackers just listed 25 times and I don't know but if I do it this way and I break my data up in these tables then I can just like look at how many categories I have because it's only listed one time right in the category table that's back the normalization we got eliminated that rendered redundant data actually okay I can totally see how there's gonna be a lot easier for me to use yes and we start and we start retrieving its content in another module to the way we can slice and dice and generate information compared to our spreadsheets is gonna be phenomenal now now I'm really curious how many genres of music I have in my collection and I want to know how many records I have by a particular band yeah yeah and right now in my spreadsheet it's really hard to get that kind of animation yeah you can filter even sort it and stuff like that yeah but then I have to get I have to count each row exactly okay definitely alright so hopefully that helps clear up the idea of taking a a table with normalizing it using a third normal form taking the table normalizing it and now okay we've got all these tables I gotta get those tables to talk to each other so we use primary keys and foreign keys to create relationships between those tables so they can talk to each other and I'm able to validate content and I'm able to seize or you'll see shortly to retrieve content using table of content that's stored or day that's stored in multiple tables I I'll be able to retrieve that content so we're gonna be able to go in and move that alright do we want to try to do a little ad hoc let's do it like we should what I think we should work on my CD library alright right now okay let's do this so I need to come out of this screen so I can go into one of these deals here so in this case you're gonna be my database administrator I'm gonna tell you about my data and I'm gonna try and guess what sort of normalized form and things yeah I need to do but let's see if we use CDs you said right we're gonna do the CD library and let's see if we can create just the basic structures that we need for what I think the tables are gonna become later on okay and I'll talk you through sort of like the grocery store example but I have this this huge spreadsheet and it's got all of the CDs that I've got all the albums okay right so I need and it's got a whole bunch of columns in it like artist and genre and the date that I got and like there's there's probably 25 because I keep track of a variety of different ways that I like to sort them cool so but let's just start with those three it sounds like now we're talking about I need something to manage the CDs but I don't want the artists listed redundantly right because now that I know about normalization I want to eliminate the redundancy so I probably need something to track artists okay so let's add a table here so let's start with the first one let's just start with the seasonal out of table I'm just gonna throw square all I do is square boxes here some girl factor I for some reason let me get rid of no fail here alright this is this is sort of the fundamentals of database design using PowerPoint yeah pretty much exactly which we don't necessarily have to do this right there are lots of tools actually that do this in fact Visio has some great designers for doing relationship modeling and things like that but there's no static simple here yeah we're just we're gonna call this one this first time was well I think we can call this CDs CDs okay although CD nobody buys CDs anymore right it's digital so maybe we should call it albums there you go that's a good that's reversal name let's call it albums okay good that we were modern and ready for yeah now some people you may have change what's going on better that way so now we're gonna go ahead and do this we're gonna have to add another table cuz you said you wanted to yeah I think the the question that I want to answer about my data is how many categories are how many genres okay do I have so I think I can kind of detect that since an album has a genre I'm one of the things that I want to learn is how many genres I have I think the one of the possible things that we're gonna need here is a genre a table alright let's see here I can't get this guy here can you actually make that bigger do want to just maximize your power point well sure yeah well maybe you can make that slide thing smaller too and we get these box a little bigger gotcha how about no I can see it good thanks yep good call look at me I'm sitting right in front of me right this Center so that's gonna be albums and that one's probably gonna be genres alright I figure out here I just fill a lot of this thing here no this is really just a chance for you to demonstrate your power my lack of I lack of skills and power where all posterity alright so in here we're gonna add it's gonna need I think you're just clicking type alright just click the box just select it yeah I'm just start typing that's gonna be Chandra except now I think you've got white text on a white background yeah you can't see that really I can see that perfectly clear I don't know what to buy off because it was originally blue and you made it white and now there you go okay great so that's John rrah the other one is album and then I want one more for artist oh yeah because I think it'd be really interesting to figure out how many albums I have from a particular artist who's your favorite artist really something I you know what I've been a drummer for as long as I can remember walking so most of my favorite bands have really awesome drummers the last band I saw live was the Dave Matthews Band has that answer and I have a whole bunch of his records in my collection so ok that might be our example all right and this could be art and that's gonna be artists so I've got this spreadsheet it's got all my albums in it I've got a whole bunch of metadata including artists and genre and the date that I acquired it but now with normalization I can break those things down into these tables so that I can look things up like the list of artists the list of genres but now we have to do the relationship between them so thank let's just type in these boxes then I need primary key for everything right right so that I can give it a unique identity so just click on the box and what would we call the primary key for the album table probably like album ID it's not easiest one I know I keeps defaulting to white text it's because we tricked you and you change the color of the box the first time but that's fine good so that's gonna be album ID and it's that that's my primary key for that table can you just maybe put in parentheses that's primary yeah that's great so album IDs over there and then we probably need primary keys for the other ones also right yeah just put it after artist just hit return there so let me get jump back in here there we go right there look yeah see this guy here doesn't like you need to get your insertion point on it there it goes there you go artist ok this PK is gonna be artist ID probably all right sounds good I'm getting something a theme here I think we're gonna probably talk about some naming conventions and stuff like that in the next module but just conceptually here I have a primary key there and then I've got another one there for genre I'm gonna guess this is gonna be John or idea that's looks consistent to me I spell that correctly I don't know yeah that's right ok good that's good enough for the example anyway yeah so primary keys gives us unique identity for all these things but now I need to have a relationship between them so that I actually can do the lookup things that I want like how many albums do I have that are in the Jazz genre right right so how would I make a relationship between genre and album so we need we need album ID to be able to access genre to check for for that album ID select check for genre and conversely I probably want a John rrah ID in my album table correct right so get typed in me so which one do I want that's actually a really good question do I want an album ID in my genre or do I want a xian'er ID in my album you want genre ID and as a as a foreign key and albums we're gonna reference the primary key in the jet and nashandra table got it so that becomes a foreign key in the album table right and then we don't have to draw the lines between them like you showed us on the other slide but there's honor ID in the album table is the same genre ID so if if I have Dave Matthews Band live at the gorge which is a which is complicated because it's a three CD elbow all at one album we're not gonna go there no how many CDs are in that thing but if I go to the Dave Matthews Band album then it'll have a genre of rock but it'll have a genre ID in the album table and the genre ID will map to the genre named in his honor table correct so that I can say how many albums do I have that a rock I can look up by rock and I can find Dave Matthews is that roughly what's going on here that somebody actually add a name here so yeah genre name is probably good idea yeah so we do we don't have to do the whole data model yeah and I think we've got a couple of examples now but tying that first example of that giant spreadsheet we've got products we've got categories now we've got our CD library we've got albums and genres and artists and I imagine just to complete this thought we'd have a foreign key in the album table that is the artist so that not only I could not only can I look up my albums by genre but I could look them up by artist exactly so suddenly my complicated spreadsheet that has you know however many hundred rows and it becomes broken down in a normalized relational model with the referential integrity so that I can look things up between them in a really simple way and we're just totally foreshadowing where we're headed in modules 3 & 4 where we're gonna talk about how you actually create this in a database look at you trying to draw the lines in PowerPoint that's a very adventurous yes yeah I know right so we're gonna do we're in module 3 we're gonna talk about how you actually create these in a database we're gonna use sequel server as our example correct and then in module 4 we're finally gonna get to the query fundamentals about how I can do the lookup and do really cool stuff using the T sequel language yeah that's cool I think that's the cool part which is very little really comes together like you're used to being in your Excel spreadsheets like I've got limited functionality by creating this and doing this thick performance data modeling the normalization process gender creating these tables creating the relationships between these tables when you start looking at some of the complex select statements not real complex but some of the a slightly advanced select statement you're gonna see the power of why you would want to do this especially if you have lots of rows and content let's go on to the summary slide and then I think we'll wrap up all right we'll get on to module 3 so we talked about normalization it's been talked about member there's five normal five forms and we talked about five normal forms for that let me go a big screen here there we go and first first one second one and third one we're worried about those the most that's going to give you you know you're just going to eliminate repeating repeating groups eliminate redundant data and it's going to eliminate any content that's in a table that's not relevant has to be associated directly associated with that key so we're gonna do that first and we're not going to worry about the fourth and fifth normal form reference when Terry we're talking about that that's what ensures the data that we're adding is valid we saw that with it with the cross rep cross checking when I add content when I have an album ID for instance it goes out to make sure it's a valid album ID if we go back to the student it goes out with sure we have a valid student or we have a valid advisor tools we can use for our front row integrity we showed you a quick demonstration ad hoc kind of worked okay primary key and foreign key constraints again if you want uniqueness and other columns besides the primary key you can use a unique constraint and we'll look at indexes a little bit more down the road and we just did a brief explanation of triggers just so you know you have that option that's out there this is a brief explanation of those different types of constraints that we have available or ways for us to enforce referential integrity and we could again we download primary key foreign key unique constraints we'll probably very popular when you're trying to enforce uniqueness and you've already used your primary key for a table and a unique index is going to be created for you when you create a unique constraint in fact we'll talk a little bit about indexes not a whole lot about that and then we introduced the idea of triggers can be actually used for kind of introducing some code that might help with enforcing referential integrity by ensuring that someone else did you really mean to delete this particular item so you can actually use a trigger for that as well so what we looked at here was more conceptual we looked at the idea of normalization we talked about that we looked at introducing constraints and we attempted to draw out a diagram and how you might take your your album collection I like that better than just CD or DVDs and use something like a database product like sequel server or even access to take it out of the spreadsheet and give you that extra flexibility and start introducing the idea of relational databases and tables within those databases so we'll be back and we're going to continue on with our conversation in just a little bit and we'll continue on and continue discussing discussing what we're gonna do and how we're gonna really utilize the app the options of the referential integrity that we've just introduced and we're gonna get more hands-on with what we can do is I'm gonna finally create a database yeah we're gonna started creating some stuff now that we've been kind of setting up with what we want to do it we're gonna dig into kind of roll up our sleeves and start doing it welcome back to database fundamentals this is the beginning of module 3 database fundamentals course module 3 is gonna be a fun one we're actually going to talk here in in this module about creating databases and database objects so in multi creates something fine we finally actually get to do something real instead of all this theoretical stuff that we've been doing but let's just review in module 1 we talked about sort of core concepts of databases some terminology things that we've covered then in module 2 we talked about what are we talking about to talk about referential integrity and normalization and sort of a little bit of modeling but not quite modeling we didn't get into the some of the details of modeling but we talked about referential integrity normalization a little bit about constraints and now here finally module 3 we're actually going to create our first database yeah we've only looked at diagrams so far except for your quick demo of sequel server management studio we're going to create databases we're gonna create database objects you're gonna tell us what kind of objects we're gonna create things like that and then in 4 & 5 we're gonna head on coming up we're going to talk about how we actually used those objects with this T sequel language and module 4 and then administration in module 5 so some real work some real real letters now let's create I can't wait to see how this is gonna go so yeah creating databases and database objects first from theirs we're going to talk about data types because before we can create object specifically a table we need to understand what types of data we're going to store in there and that's really huge about that attunes to understand so we've kind of talked about creating different columns we've talked about creating the artist ID and the album ID well we need to identify what's what type of data is gonna be stored in each of these columns so it's not as easy as it is and it's L we just throw a column out the error and say yep just throw whatever you want that data in that column we actually have to think about this helps with the data integrity we have a think about the type of data we're going to store there we're gonna start number x' in there we're gonna store money is that it gonna be a date field it's gonna be a character field we have all sorts of options so we're gonna introduce data types first you have to an idea to have an idea as to the type of data that you're going to store that's something you need to do and think about and that data modeling phase that Peter brought up earlier so we look at data types so look at database objects somebody's gonna mentions just a few then the most commonly used objects and then we'll look at DDL data definition statements so statements are used for defining new objects so we'll introduce the statements that are available are associated with DDL statements and then we'll use a couple of little statements just to show you how they're how they all work together with us so data types is going to be our first category that we're going to talk about and in here we're gonna begin with an understanding what that is so we've talked about rows and we've talked about columns well the columns are what we use to identify that we assign a data type to which identifies the type of data that was going to be allowed or stored in that particular column you have similar data types there might be a couple different data types that are so more the idea is I'll use the data type that has a larger range of values just to make sure that you have a better chance of increased precision and you make sure that you don't have an overflow error by not being I'm not having a data type large enough to be able to store the content some exact numeric data types are int and tiny int and these are probably the most commonly used data types because they're storing numeric information and then we have approximate numeric types these include Precision's that you indicated by a letter P and that gives us the total number of digits decimal digits that can be stored both to the left and to the the decimal point so all of this has to be considered when you're trying to decide what I'm gonna be what columns am I going to create for my new table for instance and what type of date of my story in there when we talked about these the album ID it's normally gonna be most likely going to be a numeric type value description or title or artist that might be more of a character field so these are things that we need to think about the other thing we need to think about is what about how are we going to use these forms these these cut these columns or data types for instance if I go out and I create a data type of a character field and I put a number in there I'm not going to be able to use that in a calculation I can't multiply in a character field it has to be a numeric field for that that operation to take place so there are some things that we need to be aware of when we're working with our data types we have what are called unicode data types you'll see that often these provide storage for international characters so non Unicode data types are pretty much specific to the USA and the New England are not new English data types or characters but if you want to bring in other languages native languages we have to use the Unicode data types unicode in unicode datatypes create more or take more storage in the database itself but you have to be careful about that but those two things are a couple concerns that we have to think about we're looking at data types now there are several categories that are provided with with us with sequel Server 2012 and I mentioned already we have an exact numerix we have big into me a bit we have decimal into money approximate float and reals date/time I mentioned that one character fields unicode character strings until you get that letter and before that means it's unicode that provides support for the international languages binary strings and we have some unique datatypes other data types like a timestamp or unique identifiers some of these are actually a taking care of our managed by the by sequel server so it actually time stamp rinses it stamps takes a system time it stamps that or applies that to the record as you're applying that and we have a couple other law data types and large object data types which will see for storing images or specifying the maximum amount of that we want to allow in a particular field so data types are you have to understand why how we're gonna use them and understand they're gonna enforce data integrity for us by if I Prince its if I have a int or a tiny ant I'm not going to put the letter A in there because it's not the character field so enforces data integrity but with the use of by enforcing the information that's being supplied it also controls what we can do with that content or those values once they've been added so we have to make sure when we're going through this that we think through that as we're creating our our data types here so here's a couple common ones we've got money so if you just or currency and you can control these most of these have options where you can decide how many decimal points that you want to the right or the left of them we have int used to store whole numbers if we there's a folks re is int short for something short int yes int is short for integer thank you very very kindly yes I see I just assumed but I blue right that's right couple times so the tiny int is tiny integer under that wha it's a year that we see here is integer big int as big integer so some of those short names I'll try to be more aware of that thank you for catching me on that but let's I also want to back up before you finish this slide you've gone to a list of the sort of kinds of data types numeric and and non numeric and then you've gone into some specific data types that are actual keywords and sequel server so two things I wanted to say number one it's specific to the database system that you're using so we right you're talking about sequel server which is important that's what our demo is going to be but there's a different shorter list of data type choices in Access and in other database systems right right and they're very similar but but that's one of the ways that the choice of what database you use what database engine you're gonna use I that's one of the deciding factors is if you need unique identifiers or some of the really advanced data types you might need to use sequel server because it may not be available in Access or a smaller database engine yeah both like you said most of the relational database products associate with the I suppose the standard for databases so if you own in one database to jump over in London a brand new what data types but Mike a Microsoft sequel server as other products have some unique variations of what is available or supported will say via ISO so great I so what is that stand for again international standards of organization international centers yes yeah that's right Jimmy question there we go so it's alright so we have int short for integer we have float that's often the use if you need a more precision we've got more in the scientific community and then after we're looking for dates or times or times it stamps we have a date/time field as well so those are few of the variation of those types of data get them more of the a couple other ones our char sort for character and anything can go in a character field so we can put numbers and alphabet in there so it's a alphanumeric characters can be stored there variable character so it's a char which is short for character has a fixed length so if I do a char 20 it's going to allocate 20 spots for 20 bytes I believe it might cermets I can't remember we're about 20 something's Jarvis's so it's gonna allocate that number now if I want to use for there's gonna be 16 unused so it's a fixed length a bar chart variable character length if I put 20 in there and I store four bits of information then the other 16 bits it's only going to allocate what is needed to store the content that's in that type of field so that's the difference between those two another data type is a bit sometimes you'll see is a boolean this is often just gonna return a 0 which is false or a 1 which is true so maybe as simple is you know if I want to go out and check is the today's does the date field equal today's date it returns yes or a 1 if it doesn't you know we can have some program programmable code or programmatic code that allows us to go out and perform some some other tasks we can do some calculations we can do calculations within within the fields themself just using the date and the data that the data types that we're creating we can also do use something like the date time offset we're gonna actually determine the difference between a couple dates or the offset of those dates so some of those some of those some of that functions available to us here's an example of some of the different data types in the storage for instance as you can see the int short for integer can store a number 4 - 2 billion 147 million 480 2648 - 2 + that should be + 2 billion 147 490 mm dot dot 647 so it's 2 to the 31st if you look at it that way you know char for instance fixed length non Unicode data any value from 1 to 8,000 characters the bit we talked about already and the date/time offset we talked about already so these are again things that we have to consider when we're creating our new tables when we're trying to create our relationships between those tables what are we gonna use the data for we could just easily create everything in char or varchar to save space but when we do that now we have issues if we try to if we need a date a timestamp or if we want to try to perform a calculation we'll have some restrictions with that so we have to think through this when we're going through the data modeling and designing or dabe our tables for for our database so these are some examples of the size of the of the information and what's stored here now we may have a situation where we created a data type on a field and we want to perform a calculation on it well we can do what my sequel server will do is what's called an implicit conversion so there's implicit conversion Sanders explicit conversions where I need to do the conversion myself I need to use either cast or convert either those commands allows me to take any of these data types and convert it I don't see any most data types and convert it to a different data type so if I want to be able to perform a calculation as long as the number was in a character field and I want to convert it to you know an int 2 so I can perform a calculation we can't do some data type conversions as part of our program and we're doing some programs and using some of the different objects inside a sequel server but the best thing to do so if you can is to create the data types with the appropriative create the columns with the appropriate data types taking the consideration the type of content that our data is going to be stored in there and any type of calculations you may be performing with that data once it's actually stored in there there's an example here in the slide of caste you know dollar one five 7.27 as of varchar' so it's gonna convert that and now store that as a varchar' and be able to work with that using that new data type here but again not everything supported for instance an end char cannot be converted to an image data type most everything is you're staying within the common data types you're pretty safe but if you're gonna get into the some of more unique ones like image those you have to be a little more cautious about so we talked about just we introduced the ISO the caste and to convert can both be used for doing an explicit conversion the caste as you can see it's recommended you use that because it doesn't here - ISO now Microsoft or other products have decided to create another conflate or that called convert and they can do so and still take advantage of that and still use it it just gives it a little bit of a different variation so data types you have to understand those before you create your objects we're now ready to create our objects here's our table we looked at earlier already collection of rows and columns we have our employee ID maybe that can't that idea has a tiny int or an int datatype last name is a varchar' first names of our char department might be of our char with different links maybe we have a varchar' 34 lastname varchar' 24 a first name department of our char 25 we defined that when we create these and the employee ID we decide we're gonna go above a certain number if we are when you decide if it's a tiny int or an int or any of the other types that will hold a numeric value for us now in addition to tables which we're going to use the most often we have what's called a view a view is a virtual table and it consists of columns from either one table or multiple tables that we've that we've pulled in and we're gonna see how one of these are created a views not really an object that's stored inside of the database it's more of a query so whatever when I create and when I go out to use of you it's actually going to go out and generate that information on demand so it's not going to populate the content in the view when I create it it's going to take the Select statement we'll say and it's going to use that to to populate and display the information that I'm looking for when I'm trying to create a view here and some examples of some system views that are provided by Microsoft that are part of the sequel server world stored procedures these are just a group of transact SQL statements that been compiled and saved so they can be used multiple times so I may go in and run the same same command multiple times if I'm doing so what it may may be easier is to create a stored procedure that I can reuse and every time I want to perform a certain task and in this case here we're looking at you know the number of items in inventory going back to be talking about what we talked about earlier in a grocery store for instance I want to find out how many items are on hand well I can strike the store procedure and having the store instead of having a stored procedure hold not being flexible enough to be able to be run against multiple products I can actually have parameters passed in so I could supply a parameter to go look for you know strawberries in the produce department by supplying parameters the next time I can run it and have to go look for you know steaks and the meat department so I can create a stored procedure that can be run multiple times over and over again it actually gets compiled which means it's going to run a little bit faster be a little bit more efficient and we can pass parameters to it so I can run it and you reuse it multiple times and be able to get results back based on the parameters that I passed into it so this is just a brief little example of how we did use that and now to pass parameters into a store procedure we can create UDF's or user defined functions these are routines that also can take parameters it completes a specific operation and returns result of that operation and there's a few different types and we'll see these as a couple of these at least we'll see as we're going through some of our demonstrations between this module the next module but we have a scalar function which is going to return a single value so we're gonna see for instance I want to know the average cost of something so it can return that information to me we have a table value it returns a date a table datatype we're not going to see that when I just listing a few things out here and we have a we have system functions which are functions that are provided by C server in this case here and we can use those in any of our any of our statements that we're writing for instance so we can use that pretty much anywhere we want to return any system type information so a few different types of functions that we have available to us and pretty much for this class we're gonna just focus on the scalar function and what we can what kind of information can be returned with that here as I introduced the stored procedures and the functions they sounded similar but here's an example of the difference is in order for me to execute a stored procedure I use the execute or exe C statement I can't use join stored procedures I can't join multiple procedures together I can't use a stored procedure for modifying sequel server configuration of it and I can't use statements such as a get date in a stored procedure now use defined functions allows you to perform some of these tasks I can call from within another sequel statement so I can use I can call a function from within another sequel statement I can have multiple UD apps working with each other I cannot be used for sequel server configuration stored procedures can the user-defined functions cannot and if there's a problem with the user-defined function it's gonna stop the function and it's gonna no longer continue on where a stored procedure depending on what kind of error checking you've done will continue to perform so just a few high-level differences between stored procedures and usually defined functions as you're creating these these different objects now one of the things that we often do is we create objects is we have to have a naming convention now is it a couple of the common ones are listed here the Pascal case is where we use the first letter of the identifier in the first letter of every subsequent subsequent concatenated word is capitalized so for instance you see employee table they'll be an employee and the T in the table is is is capitalized that personally this is my preference I use Pascal cases you're gonna see with the scripts and some of the content that I'm gonna be showing you in this mondo next module I'm totally about POSCO case camel case and as you can see the difference is with the exception of the first word in the descriptor the identifier if the first word is lowercase and then every other word every concatenated word is now as capitalized so the let the first letter is capitalized so whatever your choice is what are your preference is mine as I shared a couple times already as Pascal case but whatever you going to use be consistent so when you're starting to rename your objects if you decide you use Pascal case or camel case use the same naming convention for everything that you're doing so you don't get confused as to why used at one place and something different another place and try to keep that as a standard within your organization so you know you've got that consistency when you're creating these objects that we're going to be creating here okay we're gonna go in and create a database there's a couple days ways we can do this we're going to show you both ways we can do it inside of sequel server management studio we're gonna jump over there and we can also do this inside using a transact SQL statement well show you how both of these work for us here so let me jump out and go into mine this is pretty exciting this our first actual database yeah we've only seen pictures of databases oh yeah and the pictures weren't that well the quick one you showed us but we didn't actually it wasn't yeah yeah well a database can be created very easily I mean it's kind of like I showed you this statement here is probably being the easiest statement you can write select aspirins from production dot product that's a pretty straightforward data statement but somebody had to do the creation of the database and then they had to create production dot product yeah that's that whole email that's the table so they went through their spreadsheet and figured out what tables they needed they and we're gonna once we create our database we're gonna go and we're gonna talk about creating a table where we're gonna use all that stuff you just taught us about the data types perfect them and then this is this is actually the foreshadow to the next model where we'll learn all sorts of different ways to write queries that get the data out of the data yes exactly right yeah and that's the big thing I mean I mean all the upfront work that we're spending time on if you talk about P is is real it's not it's kind of it seems like there's a lot of upfront work and getting the tables and getting the foreign keys and a primary key constraints but once that's all done the big bang for the buck we're in see in the next module you're gonna see how much easier is a more efficient it is for us to be able to retrieve that content and slice and dice it in several different ways so so let's create an eat the easiest way to create a database is go in here to the sequel server management studio and over here where it says databases I'm gonna right-click and do new database and here's a name right here so I'm gonna call this um I'm gonna call this class demo DB now quite honestly I can stop I can right here find my mouse come back mouse there it is I spin off in the really it was it over on your screen I don't know lost it on my screen so I'm gonna go ahead and this is how simple it is from inside of here now there's options so we can change it but I want to show you how easy it is create a database I just could right so I just give it a name alright I'm gonna click OK and if you look over to the left-hand side here we've got a new a new item here called class demo database that's how easy it is create a database in here mm-hmm now was it efficient and I do everything I could have should have done to make it make you know as optimal as possible absolutely not but I wanted to show you if you want to do something today say hey create my first database open up sequel server management studio if you have access to it remember sequel server Express is out there if you want to dabble with this and then go in right click database give it a name click OK and go out and have a because you just created your first database and you're into the sequel server world at that point again a lot more we can do to make it a little bit more complex let's say we it's like we like to type we were going to keep practice our typing skills I'm gonna I'm gonna actually go out I created a script file the other day and I'm going to open up a script file I'm gonna talk a little bit more about scripts I'm going to open this up here we should talk while you're doing that we should probably you've referenced this thing called T sequel can you just explain what you mean by T sequel T sequel is that Canon golf when you tee up it's not like that at all ok so that was I do know that much but T sequel is transact-sql it's Microsoft's flavor we talked a little bit about ISO standards just a couple of minutes ago so whether you're using sequel server or using your my sequel or if you're using access everybody they all have a different flavor of statements that you're going to use they're all very similar we talked about that their own most of everyone all then won't hear the ISOs ISO standard I know it sounds redundant but saying ISO sounds weird by itself so most of all all adhere to that and then they'll have all of them most of them will have variations they might like for instance we had there was we had the cast but we have a variation of which was convert or what so we can have variations of the different commands but T sequel is transact sequel and that's Microsoft sequel servers flavor of issuing or writing commands as well as what we're gonna do so when the even doing that select statement that was a T sequel statement creating a database as we're about to do is a T sequel so anything about that we do pretty much anything that we do even inside of a stored procedure inside of a function it's ways for us to query or manage or manipulate data inside of sequel server that clear does that make sense yeah that's better it cuz you're about to go ahead and open that file yeah which we're gonna use for the demo which is full of these T sequel exams so the cool thing is what I've done to kind of - so you guys can see if you trust me Pete was watching me type yesterday he fell asleep because I'm fat fingering stuff I don't have the proper quote so I forget a quote or all right not too many quotes or it was just always make a mistake so what I've done to avoid the pain for you folks is I set up these queries and I'll show you how to execute them and I'll explain what else every one of that means as we're going through them so I just showed you an easy way as easy as it gets to create a database I go in a sequel server manager studio I right click on databases I do new I click I got a name I click OK done I create a new database now if you like to practice your typing skills here's another way we could have done that all right a little bit more complex but let's walk through this this is a transact SQL statement this is going to create a database called sales and then on and we're gonna give it a logical name called sales underscore dot and whenever you create a database it gets two files that are created on the drive and I'm happy to store these on the sequel to on the C Drive and my sales dot MDF file is created and that's gonna store my content and the size of that is going to be 10 the max I'll let that size of that file go is 50 if it needs to stop and grow grow them I call baby increments five megabytes at a time so what this does for me I create this file that's gonna store my content the size is gonna be ten if I'm at nine I try to add a four megabyte file I'm short nine and for 30 I'm short three megabytes so what this does is that got Auto growth on so it's going to grow it's gonna stop it's gonna grow by five megabytes now it'll let me to continue to add that content the bad thing is it's going to keep doing that every time I need to add content so different story but we can actually change these and set these to be a bit more appropriate values but I think specifically you showed us you could right-click and do create database and you alluded to a whole page full of options that I could have set ya in your script is using the create T sequel statement to create a database that's exactly specifying a variety of those options in code so you can do it with the graphical user interface perfect or you do with code there just a different way to create the database yeah I just showed you the easiest way to create the database was right click BAM you create the database you're exactly right if I everyone in there look at options and I looked at files I could change that so the the size equal 10 the max size equal 50 I could specify the file name the file location I just wanted to show you just to give you the opportunity to create a database that was it quickest easiest way to do that and I think this is an opportunity to call it we've got a bunch of training about sequel server specifically and in the database administration course that we have you can actually learn all about the difference yeah ways to create a database for optimal growth and minimal size and those were absolutely yeah that you're calling out in your script yeah op the optimizing databases again this is the DB fundamentals so trying not to go too deep well Pete's get keeping me on track with that because I start digging deep and next thing I know my well the concept the concept is sound right in access we can create a database by doing file new because it's more of it it's a desktop it's not a database server product like sequel server and in my suit code there's a way to create a new database and they all have these kinds of parameters about how big should it be and what's what's the file gonna be on disk and those sorts of things but it's the concept is it's a simple create statement sequel server uses this query language called T sequel that you can use create or you can just right-click and sequel server management studio to create database yeah so it's only expensive you get paid by the hour you'll want to learn type these in if you get paid you know it's just getting that work done show us that it ruins no I don't believe I don't I want to see it happen because you did one with the graphical user face right so this is that I also want to see you've got a whole long page of scripts there I can't wait this one's a small one compare the next module so this is the the other out the MDF the data file it also uses something called an LD @ file which is a transaction log again all the most relate and I'll say most relational databases use a uses we'll call the transaction log style so every data modification not retrieval by modification is stored in a transaction log file in this case here in sequel server stored a transaction log file and then it's written eventually it's written into the MDF file so it's just a temporary location it's strictly there it's primarily they don't say for data disaster recovery or data recovery so we need to supply that it built that for me when I use the right-click new database it promise you if I go out there that content store there I didn't get to specify where it went like I can here though that was stuck in this and the default location which is way deep in the C Drive somewhere this I've created this to show you that you can specify where you want these files in with the name of these files when you're using this command alright let's see if this thing works now the cool thing is it's back in the query in the query analyzer a query up window here it will only execute content that you highlight it so you notice I've got some commands I don't want these to run down here yet so I'm gonna highlight this content up here and I'm gonna go ahead now and I'm gonna choose the execute up here or I can press f5 so either way will work and it's gonna go out it's going to execute this command hopefully it will execute this command and create a new database called sales all right I'm gonna head good highlight it I'm gonna press f5 my keyboard it comes back and tells me come in completed successfully well if I go over to databases was it called sales I don't see it here so let me right click and do a refresh and here's my new database called sales and if I want to look at the properties of this real quick and it's been real quick you'll see just go with files for instance here's my tan in my five and use my autograph and again as Pete point I could have done all this when I was due in the window when I was going through the graphical user interface I chose to show you just a quick this way for you to create a database and that was it's a quickest way and this is the this is another way if you get paid by the hour to create a database is to write scripts the cool thing about scripts to write them you know I'm making fun about getting paid for it but scripts are beautiful in the sense they're reusable so that's another reason you may want to script some of this content here all right let's see if I get this window back I don't want this window so big here let's get this back to elevate okay so we have a database out there now remember I talked about the use option I need to add a table to that so the idea that we talked about just previously was hey when you create a new table you need to understand the type of content you're going to store in there so this table I'm gonna create is called products one we're gonna call we're gonna have filled in there called product ID we're using an integer value alright so I can store whole numbers in there we're gonna have product name varchar' 20 we're gonna unit price using the money datatype and we're going to a product description using varchar' 50 remember if it's char it's it's ever that if that was a char 50 verses of our char even if it uses 10 characters it's going to allocate space for 50 so it's a fixed length where a bar char said only it says only allocate whatever you need to store the content so this product descriptions only 10 characters it's good as storage for 10 not 50 so there's not lot so it won't be as much unused space so what we're gonna tell it to do is use the database we just created called sales and create this new table in there so if I go over to our tables over to left right now you're gonna see you have system tables file tables those are just tables loaded by default let's create our new table here so I'm gonna go in now I'm gonna highlight this because I can just highlight what I want and I'm gonna execute this and command completed successfully let's do a double check over here let's go back to sales and do a refresh and go back to our tables and you'll see we have a new table here called DBA dot products one and if I expand on that you'll see our columns and you'll see the four columns that we specified here we're also created there so easy enough now we've created a database we've talked about what data types are we've decided that type of content we're gonna store in that database we're gonna store in its products one table and we've created a table now that will allow us to to be able to enter content in there we're going to talk about doing that a little bit later on though remember we're talking about right now we're talking about a data definition or DDL statement so we're talking about creating creating objects and modifying objects so a created database we added a table to that database something else we can do and so notice when I created a database I use to create statement I created a table I use the create statement I create a view I create a stored procedure whatever I create if it's an object inside of the sequel server I use the create statement so it makes it simple for me if I want to change the database or I want to change a table or a procedure or a view I use the alter statement so I create a database called sales I want to change that Nate database name to sales forecast by the way that little green line that says right there when I'm doing that's a comment so anything that's got - - and it highlights it notice the color coding by the way the blue is saying these are actual statements these are the fields these are values that are supplied by my mike rizzo server the - - says this ignore everything to the right of that it's just a comment so you're gonna have to document people always document late but they always document their what's going on here so this is a reminder to me oh I want to change the database I can highlight it and try to execute it nothing is going to execute because it's a comment but we're gonna alter the database called sales we're gonna call it sales forecast we'll hit execute up here all right the database name sales forecast has been set let's refresh and we'll go over here we'll see now our database now and sales forecast so now we've changed the name of our database now if I wanted to delete the database I've been asking Microsoft for years to change this from drop to toast I like the command toast I want to toast a database I want to toast a table it's still not toast you can't write a story procedure to do that though you can't write a store pitch you then do the same thing that's dropped and I've done that before but we won't go there I just witnessed a with standard stuff here so I want to drop the database or I want to drop at a or I want a drop of you I use this statement right here toast the database we're gonna highlight this so remember over here we got sales forecast we're gonna execute who cannot drop because it's currently news I must be connected to it somewhere modifying in toasted database let's see here I know another way we can do this come over to the right and I can do a delete here now what's happened is I have a connection to it so forgive that failed I'm gonna come over here is a connection to it so my guess is if I do okay here it's in progress it's already what taken too long it's gonna come back it should give me this pretty much the same type of deal it can't do it and they gonna take this long or else I would have done this piece it's gonna come back but I I'm almost positive it's because there's a connection established to it and so what I'm gonna do okay now it fails drop failed for database let me do this let me click this guy right here close connection and doing okay hopefully that will do it you know why they don't call it toast by the way why is because toasting the database would make it more delicious oh that's - usually rottenness that's true that is true all right we got rid of our database if you look over here we got rid of it here I couldn't do the drop database there is part there are parameters you can supply with that to drop existing connection so we could have done it that way but because I own establish a connection to it it wouldn't let me drop that database so it looks like a lot of the things that you can do with the user interface and sequel server you can also just write script to you in scripts we're gonna talk a little bit more about scripts this is kind of an introduction to them because I want to bring these in so you didn't watch don't watch me fattening your everything but scripts are really powerful especially if there's redundant tasks that you're performing and let's say I wanted to hand this off to Pete so we can go out Creek the same database he like just sent him this script he can pop it into his sequel server management studio he runs it he's got the same database with the tables in the alteration of it if he wants to so it's easy enough to redo information or toss that we're performing here alright let's jump back out into our slide deck it's not where I wanted to be here drop this guy this way no yes am I going too far here I'm coming away out I know what I need to do I'm coming out of my connection I need admit about that here we go and let's go back into our power points but so now get closed we're in what module three yep yeah all right come on network with me there we are all right so that's creating a database class demo dB that's one we created using the interface and the one that we created that we're going to create we also created one using a script and we deleted that one actually after we have added a table to it DDL statement it's kind of what we just did so we actually did to create on the database alright and we did to create the table remind me what DDL stands for data definition language so this one so we talked today of manipulation language earlier where we do the inserts you updates and deletes actually know both oh yeah we do that next so we did a DDL as a data definition we're actually creating objects or altering objects or dropping objects so so that's a create creates new objects alters modifies them and drops and toasts any any objects and that's for database all the way down to views or stored procedures or anything like that so the create statement you saw it's used for database table default any of these can use the create statement here's that example that I supplied for you already this is what I showed you for creating that database and the table here's the create new table and this is the one I demoed for you I think I did the demo first and now I'm showing you the content that's pretty funny well in the wrong order I supposed to create the database just inside the GUI we were both excited about actually I know I want you to do something to break I want to do something scripts or with you know queries so this is what we just saw if you look at here it's not all color coding all that cool stuff but it worked you saw that it worked and altering the statement as you saw it took the sales and change it to sales forecast and drop statement whether it's a database or a table view roll whatever it is it's simple for DD eyelids create alter and drop if you understand those three that's the bulk of what you're gonna need to understand for DD outs when your data definition language statements to be able to manage the objects to inside a sequel server this is where I supposed to do in a demo that we just did so I did a dual demo but this is where let's go back and actually talk about the graphical user interface and scripts show show me the cool feature and sequel server here about how I can how I can get a script I want to script here yeah we can do that easy enough so what we could do one of my favorite parts of sequel server because you can kind of cheat because the the lesson here is that there's a graphical user interface way to do things and there's a script way to do things but you don't actually need to memorize how these scripts are created because sequel server has this great feature that that Brian is going to show us yeah so the cool thing what you're talking about is I created this database using the GUI now I didn't populate anything in there anything yet but what I could do is after I create an object using the graphical user interface I can come in here if I can grab it here and I can go into script database eyes and I can I can have it generate that create statement for me so like that's creates a man I use to create the sales database I can take this in any object whether it be a view a store procedure a table and that can have it generate this by creating using the create two now the other thing is is the drop and create two this is kind of cool because what it does it adds at the top of the script it adds to it I'm just go ahead and kick this off to the clipboard hopefully that'll open up for me let me do a file so I'm not gonna find it then we'll call this class demo so you even down here I use the pascal case script alright let's go save it as a SQL file that's my mouse again come back so I'll open that up for you just a few moments in fact what I'll do is don't do it just one sec here I'm gonna go over here I'm gonna go into here up into it uh it did not save it to the desktop that's all I know I didn't catch the Python IDE I always save at the desktop because I'll lose it so I'm gonna do it again I think you can just when you go into the script database can't you just do drop and create to a new query editor window and it'll just open it right up yeah okay we'll just do it that way I just wanna get same it out and then you can save this file this is just a document system where you can save the SQL file yeah and this is cool cuz look at that alright said use master cuz as to update this content master database this is another way for comments do a forward slash asterisk and then you close it out with a Astra's four slash over here so let me just could you just blew my mind again I just want to make sure we talk through what just happened you right clicked on the database correct you said drop and create into the new query window generate scripts drop and create two and then you said new creator window and sequel server generated this whole file full of stuff that starts with the use and then it says drop the database class demo DB so if you wanted to make changes to your database structure or say a table that you did you could just use the generate script command exactly you'd get a window like this you could make the changes that you wanted and then you could run the commands here to make the the alter or modify or create changes that you wanted yeah maybe I don't like to you know like you said it's gonna drop the database that's alright so you can run this multiple times they create it up not quite the way I want it create it not you know and come back and change it I don't like the location I can change a location here and run it and created again got it multiple times and the cool thing is it scripted I can create it I like it it looks good Pete likes it I'll send it off to him he can execute it inside of his sequel server so I just kind of blew up your whole logic about getting paid by the hour and writing scripts cuz you just showed me how quick it is yeah to generate a whole bunch of scripts that I can just go modify so your manager is gonna like that right it's here I'm saying you know my manager is gonna be like what really I'm all about getting it done faster no I am - definitely and here's some other alter database statement so generates a bunch of items for you that these are all of the parameters we're not going to worry about but the idea is we can generate script again I could have been a table it could be a view anything that you'll have four objects right click generate script for and there's often they'll have the drop you I always put the drop there that way in the event it already exists and won't produce and without that drop if I tried to run this it's gonna blow up stay the duck the object already exists so to avoid that you can just actually go out and drop that the other thing I wanted to point out is I think this is a really great way to learn how sequel server is yeah he's doing things because sometimes you can page through the graphical user interface to discover all the options but you can also if you like to read the the script code a lot of people are into the sort of programming aspect of databases you can use this generate script command and actually get a big file of T sequel yeah and then you'll discover all kinds of features that sequel server has as you parse through that script and see what's in there yeah good good example that too is how if you want to write your first or procedure like you know it's like I know Brian said you get passed parameters there's a ton of stored procedures coming out of the box right click generate a script you get that for a basis now and you can use that for credit for correct writing your own your own stored procedures down alright so is that all you wanted to do in here yep ok good yep I think it's I think it's about time to wrap up the videos definitely so let's see where we are we covered a tremendous amount of ground a lot of time we went from fundamentals to suddenly we have our own database with a table and we didn't really create all of the the details yeah of the table just yet but we're on our way to doing yeah yeah so we got to talk about data types with different ones there how to create a database both the sequel SSMS you create alter and drop objects pascal case or camel case you choose one of the two all right make sure that you do that don't leave it don't don't mix them up you already know my preference i won't go in there again you know we should do before we wrap is we should talk through let's just go back to the original example that we use let's go to the menus the grocery store one or the the CD library or CD library one so the album one we started with the spreadsheet my giant spreadsheet of all of the records and then we did some normalization on that we decided that we were going to start with three tables and albums table an artist table in a genre table and what we learned in this module is that to really actually create those tables we need data types for those things right right and in let's just talk through the album one if it has an album ID as a primary key then what would you think a data type would be for that that would probably be an int integer an integer because we know that integer goes up to two thirty two thousand seven sixty so for my database that has a few let's say hundred albums in it that's probably a limit that's appropriate so I'd make a choice for the data type for my primary key because I don't think I'll ever go over 32,000 albums and that yeah actually I think I just I think that's a tiny emphasis tiny enter small and yeah so I get two doors like huge and the point is you'll think about the data types that you have and you'll you'll think about the data that you're gonna put into that table and you make an appropriate data type decision based on the data that's gonna be in that table so album name we need to decide what we think the longest possible name of an album they is and then make a data type decision based on the data so this is more of that data modeling stuff we're not going to give you lots of guidance about how to do that but these are the kinds of questions that you'll have to think about when you're actually creating you'll have to first understand your data and then understand that there are data types to make choices about that when you create the table you'll be able to make a smart choice about what data type to use for those things so I just wanted to kind of reiterate that that's how we hooked it together from the first couple of modules to now what we've just learned about would create the database we know it's going to a bunch of tables the tables are going to have these data types these fields with different data types that are appropriate for the data that's in them right exactly and then the next model going to look at some of the awesome commands that we add to be able to go and actually grab this data and slice and dice the information so all right so this wraps up and training the database and the objects we're gonna look forward to taking a peek at the how we manage that data now let's let's do it okay thanks welcome back to database fundamentals this is module four level four now yeah it's gone by really quickly it is yeah but it's been fun we we started out with kind of the very conceptual module one where we looked at some of the core concepts to creating a database or two databases and why we need them we started with our big spreadsheet in module two we talked about relational concepts like normalization and referential integrity and we just got out of the module three where we learned how to actually create databases and create tables and that that was something that we called DDL data definition language yeah exactly and now here we are module four which is it even has DML in the title which is data manipulation language curse alright manipulation language I was getting confused about these acronyms yeah DM l and DD l and d CL which we're going to look at later but in in this model we're gonna actually take a look at finally using data we're gonna take a look at we're gonna start I think with how you actually use the Select statement which kind of the bread and butter of querying a database and then we're going to talk about some of the other DML statements for doing inserts will actually look at how we got the data into the database in the first place and how to do updates and deletes as well but take take it away I can't wait this is this is probably the big module for the course and I know that you've got all sorts of cool demos for us yeah the cool thing about this is we talked about the importance of data types and being alike we've been saying the Excel spreadsheet was nice if you got just a few rows you weren't worried about slicing and dicing your data but if you get it into these tables inside the database and with these relationships defined this is where you can really the magic happens by being able to use the Select statement it's that simple it's really using a DML select statement so we're gonna talk about that here specifically we're gonna start talking about the Select and then we're gonna talk insert update and delete grow briefly there let skipped a few slides at you so selects david here we go here with this it's a it's used to retrieve rows and columns from the table I showed you the most basic select statement or select asterisk which means all columns from and whatever table name that's as easy as it gets so with that said though you may not want all columns maybe want specific columns that were what we're gonna do is we're gonna introduce different ways for us to really start slicing and dicing that the information because right now if I do a select asterisk from a table name to me I'm doing what I call on table dump I'm just dumping every row and every column and so I might as well have a spreadsheet it's not quite that bad but but what this does is now I've got two choices I can choose what columns I want to display and I can choose what rows I want to display columns are chosen by instead of doing the select asterisk you specify the column names instead of doing not supplying a where clause you supply a where clause and that introduces what term what what the criteria is on those rows that you that has to be met for those rows so now you're narrowing your results down both in width and and and in height because now we're getting only certain columns and only certain rows because of what we supplied this is an example of a it's still a fairly simple select statement but now we've taken it down and we've supplied three column names instead of the asterisk and instead of dumping everything out from the employee table we said we're business entity ideas less than or equal 50 so we're you actually supplying a where clause they're saying I want to see everything from 1 to 50 without business entity is gonna 1 to 50 and this is what I want to see I want to see job title and I want to see gender so that's what we're doing we're specifying the actual columns and we're specifying the rows the rows are driven by the where clause the columns are specified right after the Select clause as to what columns that you want to see so this is taken from a simple select statement - now I'm getting a little bit more picky about what I want returned to me I can get picky or I can add multiple multiple criteria to the the where clause so notice in here now I'm still doing a select on these three options business NTID job title and vacation hours now from the table still have that now my where clause where job title equalled designer engineer and oh by the way gender has to equal F and oh by the way their hire date has to be greater than or equal to January 1st or mm so now I'm really narrowing down what's returned to me in my result set I'm specifying 3 bits of criteria they all have to at this each record that's going to be returned has to me they have to be a design engineer they have to be gender female and they have to be an have been hired later than January 1st mm so I'm getting very specific can you do that in Excel I can't I was actually just thinking I can't I don't know I don't know Excel guru but I'm pretty sure you can't even do this no and what's awesome is that right away here in module 4 you've solved the thing that I was wondering about all the way back with my spreadsheet which is how am I gonna figure out how many jazz albums I have okay right or which are the jazz albums of my collection you just showed me with this criteria this where clause they on the Select statement with all the referential integrity that we've got I just need a reference where genre equals jazz and I'm gonna get just those jazz records out of my ink or your disco I know you keep talking guys but I still think you get us it's a very few guys don't think disco know as I think there's a lot there's a good time for that so the next one instead of using an AM clause we're using an or clause here yeah select business identity entity job title vacation hours from the same table where vacation hours is greater than 80 or so I could be anybody has more than 80 hours and available to them or their business entity ID is less than 50 so pretty much I'm gonna get everybody one through fifty and then anybody else that has more vacation hours than 80 hours they're also going to be displayed so now I've increased the number of rows compared in comparison the other one so my n Clause is it has to meet all three criteria my or says it's going to meet either of these criteria and I'll get that returned to me okay by the way I'm gonna demo all these for you and we're just gonna walk through them so you understand what they're all about and we'll see these all in action in just a little bit the between clause instead of specifying the ends or if I want to specify between a certain date range or between a certain this case here vacation hours very very similar syntax select the column names from the what from the employee table where vacation hours is between 75 and 100 now I could have done this a different I could have said where vacation hours is greater than or equal to 75 and vacation hours is less than an equal to 100 that would have worked but this is between this makes a little bit simpler so now I'm going to say if the vacation hours between 75 and 100 I want to see those rows so it's just another way for us to source license dice the data that's just being displayed now the bad thing is right the light right now is we're getting data dumped out to us but we want to be might want some sort of order to it what we're gonna do now is sort the result set using the order by clause so what we want to do is we're gonna take the same content with the same command we had in a previous slide select business ID job title in vacation hours from employees where vacation hours between 75 and hundred and order the vacation hours order the content that's displayed to me by vacation hours so the default is ascending so anybody who had 75 hours it's going to show up first on the list 76 77 all the way down to 100 so I'm gonna actually specify how I want that content returned to me you can reverse that I use the same exact command on the bottom to slide here and on the reverse so all I did is I added this des C is short for descending so now I'm stead of seeing 75 at the top of the display down to 100 I'm gonna flip that and see 100 down to 75 so it gives me an opportunity to sort and decide what order I want to sort them and it doesn't have to be vacation hours it could be by day it can be by alpha alphabetically we can choose what column that we want to order that by so now we're getting some more we're getting some order don't pun intended to the how the contents displayed to me it's not just dumping the data out because it met the criteria we're telling it how we want to see that data displayed to us using this order by clause so it gives us a little bit more control of how that content is displayed so that's saying how we want to grab content what if there's content we don't want to see we've got the not clots so what this is I'm gonna grab this three items business entity ID job title and gender four columns from the employee table we're not gender equal M kind of weird we just say if we're not gender but we're not gender equal m so when I run that I should only see gender equal F because I don't want to see I'm telling it I don't want to see gender equal M's I just want to see items where now I could have expanded on this I could have said we're not gender equal m and title equal design engineer or something like that so again I can expand on some of these commands more so than what we're showing here I'm just showing you different flavors and the power of relational daheia table in a database that we didn't have in our spreadsheet because now we can we have control over what data we want to see how we want to see it what data we don't want to see and I really want to be more restrictive on I want my disco do you know CDs I would that's got celebrate on it or something like that from a certain from a certain artist do you have that one I'm just curious it was just just curious you're bound and determined to get me to disclose which disco what's discos are in my collection we'll find out we've got you're gonna be a little time okay the Union Clause what if I had contact that's in two different tables yeah we can do that so right now what we've been looking at we've been looking at all right you're telling me I can grab content from a table what about five content from two tables so this is what I can do here as long as the columns are matte so as it says up here it allows you to combine the rows returned from multiple select statements into a single result set so we're doing select business entity ID job title and hire date we're selecting that from the employee table where we're still supplying a job type where our where clause where job title equal design engineer now we want a union that we want to I don't want to use the word merge because there's actual statement merging we're gonna add we're gonna take that content and we're gonna have content from the second select statement again the columns have to match business entity ID job title hire date from the employee table where hire date between 2005 January 1st and 2005 and December 31st of 31 January to December 31st 2005 these dates are weird for me I'm not used to having the Year first so now we're gonna Union we're gonna take two select statements we're gonna populate the content into one result set but there's actually criteria from two different statements and we're gonna return the same column so we'll see how we can take have a statement that returns columns based on two separate will pretty much two separate where clauses and two separate select statements this could be a different table if I could go I could go out do select business entity ID job title hire date from Human Resources dot X employees if I wanted to where hire date or a fire date was a certain date you can do that as well as long as the columns match up you can read that content from different different tables here so this is the Union cause and again we'll look at we will see how how that operates for us here the except in the intersect clause so the except Clause returns distinct values distinct means unique in the sense that I don't want any repetitive data so now I'm saying okay so if I did a search I wanted to Sage I want to do a search of my of my CDs where you know gender IDs will equal disco and then I've got the same I've got the same artist I may I want I may not want to see the same artist I want to see all by disco albums I think that's probably out 100's do you have it's it's easy hundreds I think so so anyway so if but I don't want to see the same artists each each time so I want distinct I want unique artists that would do the general disco so what I do is select product ID from the product table accept and the select product ID from production dot work order so it's gonna be turned both of them for me alright but anything that's matched it's not going to be turn that so it's not going to be turn anything so that's that's a match it's going to just do it's going to do the distinct items the intersect clause down here returns any distinct values returned by both the query on the left and on the right so what this one's gonna do the left being the first query by the way they determine as well as left and right it's the first query it's left on the intersect statement let's put it that way so this is going to say returned so the first one up top let me go back there that look from the left we're there are not found in the right query so they accept is return distinct values from the the products table that are not found in the work order table this one down here says returned by both but the individual information distinct values again not everything but this thing by both information that's distinct in the left and the right sides of the intersect operand so anything that's distinct and in product and work order will show up in that second one alright and again I'll show you a result side of that as well but it's just ways for me to to be able to slice and dice that content that we just learned impossible to do with a thousand or so rows in a in a spreadsheet well by being about being able to create relational tables and that's right database were able to manage that data much easier the joining clause is it's a little bit more advanced we're just going to introduce the concept of a join here there's a few different flavors of joins but it allows me to use content from two different tables so an inner join uses a comparison operator to match rows from two tables based on a value that exists in both tables so product ID will go to product ID I could go to do a join a product ID from one table and do a search or select on a product ID from another table and with an inner join it's gonna look for matches on that product ID and give me that content okay so it's a match outer joins if it's a left outer join versus a right versus a fall but if it's a left outer join it's gonna include rows from one or both tables even if they don't have matching values so if it's a left outer to left join outer join it's gonna give me everything in that first table all right we'll say it's a product from the products table and then any match from that second table so that's what that's what we'll do for that so if it was a right outer join to be everything from the right table and only the matches from the left table cross joins is also I think this is also no cross join is gonna return it all roles from the left table and all rows from the right table so it's pretty much a table dump from both of them unless you supply a where clause you just fly a where Clause then it'll narrow down what's being returned otherwise give me everything for the left hand will give me everything from the right table it's kind of a good way to generate a bunch of table content into a temporary table and you can use that for testing content or testing other statements that you want because you want to generate 30 40 thousand rows of content so joins allow you to combine related data from multiple tables and we're gonna kind of leave it at that it's more of an advanced topic but just want to let you know because we're creating multiple tables doesn't mean I can't grab data from multiple tables I could certainly do that and do that using one of these different types of joins that we can look at and you can maybe experiment with this seems like it's a great opportunity to plug another Microsoft Virtual Academy session that you're doing we're actually recording it tomorrow that's a whole session about the T sequel korean-language that you're doing with to be a stern storm yeah that's gonna go really deep into these topics so keep your eye out for more content on Microsoft Virtual Academy if you want to go deep on the query yeah so some of these that we're just doing a high-level approach on the fundamentals this is querying sequel server query Microsoft sequel server 2012 that's gonna be recorded it'll be out shortly on Virtual Academy and you'll be able to review that over Tobias tonight so great point thanks for bringing that up reminding me because you kind of blew my mind with joins are complicated on their own and then you win it inner joins and outer joins and it gets super complicated for the purpose of my CD library it's way out of scope yeah yeah but I can see where I'm gonna want to go get more information about that when I start like hooking up to the Xbox Live music service and figuring out which music I have that's also on the streaming service things like that once I get really into my application that I'm gonna build yeah okay I'm waiting for that I was Monday I'm gonna be your tester on this yeah I'm you send me a SQL run this this is an example of an hour they're called aggregates and what we're done here is I don't want all the tables I need some average information I want I need some some some aggregate information is aggregated so what this does is provide you summary of data so this example does a select count distinct sales order as unique orders so what this eyes does it just gives it a different column header just want to throw that ID in there for you so select count so it's just going to do a count of all distinct sales order ID so if I got a sales order ID of one two three it's only gonna count and I got ten line items in that it's gonna count at once that's that unique sales order ID then I'll by the way for that sales order I want the average unit price not sorry not for that sales order in addition to that sales order the sales orders I want an average unit price I want to know the minimum order quantity that was order than any of the items in that sales order detail table and I want to know the max line total so what's the maximum amount one item or one line item what's charge what was the maximum amount what the sale on one line item so this is taking content so dumping all the detail out it's actually providing you summarize a summarization of this content and showing you how this content would be displayed and what's your event or what's your op max and what your average was and what the total number of Records are for a particular sales order ID so again I will demonstrate that for you so you have an idea so what that looks like those are really really powerful um for instance Pete may want to go in and say well how many disco CDs do I have or you know album sometimes called albums he could go and he just do a count worst you could do select count distinct gent from the genrih FB where it would I would be and we're tight were named equal disco and so it'd give me a total number of items no actually wouldn't do at that table we'd have to do it naturally how the album album tables got the distinct count yeah give all the disc arms and then we truly know where just because he doesn't want to tell us but we'll see that one in action just a little bit here how about right now in fact I'm pretty sure for Christmas you're gonna get me the complete works of Donna Summer anyway and then the whole I wasn't sure what I was gonna do now but I don't know just going on I might already have that well let me know else I don't buy it again for you I'd hate to do that okay let's go back into management studio here and since I'm asked them over my desktop here I'm gonna open up another query you know all the students really wish that you had my CD library data for all of these oh we're gonna go find out you know like who did celebrate sadly I think we're gonna use adventureworks now aren't we is that we're not we're still yeah we're yeah I'm sorry we're staying with a bit we're gonna stick with it veg say can you just give me a quick overview one more time for the people who are just joining us yes adventureworks tell me about the data because I think the samples we've been using make a lot of sense cuz it's pretty easy to wrap your head around a music library or in a grocery store but what's adventureworks and what kind of data is in there before we get too deep into these samples that's great great and so adventureworks is a sample database that's provided that you can download install if you want to test this in your sequel server world and it provides us sample data and as you can see here i'm just gonna open up the tables it's got Wow Human Resources can employee Department so they've already already done the normal they've already done all the relationships for you it's got an entire area two four sixes and Human Resources a dozen or so on person notice they have a person address that's separate than address type they have a contact type they have a password they have a person and a person phones then they have the person phone type I mean they really normalizes data to the nth degree and then the production all this information about production counter or a product category product photo product product product photo not quite sure what that would be product reviews scrap reasons reasons they got rid of content or a product and then we go and we've got purchasing information all right we got product vendor vendor order detail vendor order header I mean it's just all been totally normalized and there's how you see but it doesn't does in the tables in here it's interesting because adventureworks is a huge enterprise database and the hypothetical company adventureworks sells sort of adventure products but they have data in their database that manage the the business of running adventure work so all the people and HR stuff yeah but it's also got the product catalog and things like that saying all of the sales and it's all in that one database which is how they chose to do the the structure and we can we can say that adventureworks is a little bit contrived so that it makes a really concise sample that people can use night but there's lots and lots of data in there split up into a variety of different sort of business data that we that we're going to use in the sample and so I've just had someone chat into us and say that the longest album title ever is 89 words long words I thought of ICH 89 words that we would need a full character I mean I would know we have to figure out the character the average length of words I don't think we have enough information really yeah how we create that field but it's information like that you me support the 89 words I would have never guessed that so if the adventureworks product catalog had album titles in it then the product name field would need a datatype to accommodate an 89 word name yes exactly but stuff like that you have to think about while you're actually designing your database because I would have never guessed that I'd have probably gone 50 60 characters a hundred max oh I got a hundred max so now I'm just out of it off the cuff and I'm thinking you know that's not anywhere near enough so well thanks for chatting that and that was great information so now if you could find out who did celebrates because Pete won't tell us that'd be awesome Kool and the gang well that was quick that one came in somebody's got that in your collection okay all right adventure works out of four we're back into adventure works thanks for reminding us to kind of just show what it looks like a someone joined us here late a little bit later on we talked about this table dock remember this is pretty straightforward we do a select asterisk and again this is all scripted so I can just cut I'd not cut paste I can highlight actually it's notice up here I'm adding the wrong interface or database so i'm gonna grab use adventureworks remember i try to get in the habit of just using or supplying the use adventureworks only so I know that I'm always in the right database you saw a couple times is doing a demo it didn't works like Oh wrong database so if you can include that on most of your statements especially if you're bebop in between the two different a couple different databases it'll be helpful for you so let's do our basic statement we're gonna go ahead and execute this baby and you're gonna see we got pretty much a table top but you got business entity national ID I'll scroll over here trying to get you dizzy I'll scroll slower and this is pretty much everything there notice there's 290 rows if you can see way down the bottom there's 290 rows that were retrieved from that table called employee all right and you can see you saw all the columns as I scrolled from the left to the right I'm gonna scroll back so we just pretty much did a date-date it up now again you've seen all these slides so all these slides are now we're gonna show you how these these slides are containing the the the text on the statements on there now we're going to show you how they work so then we went from the table dump to say now let's decide what wrote what columns we want and how we want we're going to throw a where clause in there so we're going to execute that one now so now we're going to see business entity idea equal 50 or less and three columns instead of all of the column so we'll execute this one here's our business entity ID here's a job title and here's our gender now if we scroll down we should see call a business entity ID one through fifty and I just hit bottom and you'll see over here whoops fifty rows and we just got important information into us three hundred and sixty characters for that eighty nine word title so are we would have to have a varchar' have to be a large R because that's pretty huge Camille everything else a three hundred sixty characters but I'm gonna thunk out of n105 had no idea yeah it doesn't celebrate you celebrate that all right so that's that's cool now we've narrowed down the rows and the columns let's go in here with our multiple where clauses this is where we had where they got the same sort we had two columns here a third one of vacation hours from the employee table now still job title equal design engineer and gender equal F and hired a is greater than January first two thousand so let's see what kind of content this will return to us and like I said it's okay if you highlight the the comment it's just gonna ignore that anyway and you'll see we have two people who meets the criteria that our design engineers have vacation hours of wouldn't you say oh we didn't care about that this is entity is five and that was supposed to be I could have changed this one a little bit well the where the where clause is showing you what you got back we just didn't include the columns yeah and the where clause and that's pretty interesting as queries go yeah because mhm because we can assume since we have and gender equals F that everybody there has F so we didn't really need to return as a row yeah query there's a call we could do it let's do it let's on the fly he what changes to hire date and let's change it to sneaky gender you want to see it anyway just to prove that your query works yes gender okay business entity ID know what we want job done we don't care about this let's get this guy out of here and we'll get the title all right let's try run this baby again so job Dino is designs near gender is female and hired on to six and to 18 to 60 mm to right and we would have we would have kept the employee ID in there just so that we could figure out who is so that we could go and use that information and replicate exactly yep there we go so we could find out much other because that's the key in the employee table that is now how do I find out their name this is where it would be helpful to for me to now I remember this is gonna be a primary key called business entity ID somewhere out there and we can probably go see it real quickly tables under Human Resources we're coming from employee we'll probably maybe person person person why don't we go look at the fields that are in the employee table to person person person dot person so I would map that business entity ID but hang on a minute you're jumping ahead though all we're doing is we're looking in the employee table how do I know it's not there oh I thought I just showed you that if we open that right let's just reiterate here in the employee table so I want to just stress the the foreign key that you're implying here okay great so here's this is there is nothing in here there's job title but the name of the person is not there so how do we get from there to person so we get there from there to the person we put into one of those joins and then that would map to and we won't get into that that would use that business emptied a business entity ID that would map to this guy over here for this five and fifteen got it and we could grab the first name and last name for there as well got it so it's both the primary key and has a foreign key relationship to the other table yeah got it yeah cool stuff all right so we got created with that one on the Flies good what's the time we'll do this one now this is that multiple where parameters with the or so this is the end so this is where we want vacation hours is greater than 80 or business identity idea is less than 50 so now we're just kind of deciding I forget one of the listing sit so there we go a little dashes so now we've got these folks here business entity ID job title what we're looking for vacation hours greater than 80 or business into the ideas less than 50 so notice this one here doesn't meet the criteria of vacation hours is greater than 80 but the business entity ID is less than 50 so we still get that returned to us no I thought that I do it the other way yeah and here's a between oh yeah that's like so here's the between clause that we talked it out now we want vacation hours between 75 and 100 so this meant to different criteria we got rows returned to us this is gonna say we're gonna get the same content we're gonna get content returned to us we're vacation hours is between 75 and 100 so these are the people that need to go on vacation before they believe know why I'm not listed in powers I should have a vacation so notice we have ninety nine eighty eighty two seventy nine ninety eight but there's no order I mean the only thing by default it kind of dumped it out by business identity ID but that's just you can't count on that I mean it may do it pretty consistently but if you really want it to be displayed in a certain order you're gonna want to use the order by option so I'm gonna use that same command I just used up here but I'm gonna order it by vacation hours instead and by default it's gonna be ascending so around that see now instead of a story of my business entity ID it's sorting it by seventy five vacation hours seventy eight so nine eighty six all the way down up to ninety nine vacation hours so now it's like I'm controlling how in the contents being sorted remember I said we could go in reverse order or descending I'm taking the same exact command I'm adding the de SC on the end of it for descending and it'll execute it now the ninety nine start up at the top and they go down in the 70s in the bottom if I scroll down alright so we can control that and it tells you how many rows by the way if you're not quite sure there's there's always down here it tells you the number of rows if you quickly jump over here it tells you how many rows so if you're curious in some of these commands like how many rows did that affect jump over the messages or if you happen to peek at it it happens to be down in the right-hand side it'll show you how many rows are affected and there's some commands coming up I think we're gonna hit thousands that might work so it'd be good to know all right using the not Clause we showed you this is where I want to see anybody where their gender is not male from the employee table we'll execute that so they have 84 rows for the employee table and then we're gonna go in and we're gonna do a union you said where we can get content from two different tables or to do different options so we're gonna combine data from multiple tables it's actually the same table what we're gonna do this we're gonna have business entity job title and hire date same is down here two different select statements with the Union and mill from the employee table we're job title a design engineer in the hire date between 2005 January 1st 2005 and January 31st 2005 now what's a way we could have done that differently did I really need to use a union I could have with a where job title equal design engineer I'm trying to do a union or hire date between 2005 one you know one one 2005 12 31 to the 5 I could have done that in a single statement unions are probably a little bit more powerful if you go to different tables it's difficult to find two tables that have repetitive columns in them though you might have a choice you might have like an employee happens to be a customer so there is important there as a job as a first name last name maybe city but it's sometimes difficult if you've done all the normalization to find columns that are that don't match the actual the date of the values in there but they match the the columns required for this this K here business entity ID job title hire date listen use first name last name and city I could probably find that in a couple of different tables and then just join those that way as well here's the returning distinctive values from the left query not found in the right query it will execute this one so product ID so it's search product table and it starts to work ordered a table and it found 266 rows let me click this guy here that had unique values in them between the two tables alright I could have added more information as far as you know what table came from and other information about the product maybe description but just to show you that we can grab this is going to be the intersect versus the except this should be bigger so we're gonna highlight that we're doing the same thing but we swapped out it except for intersect and we got only at 238 rows actually so three days he just Inc now he's returned by both queries so what was this one back here oh these are ones not found so these are really just turned in from one query and these are from both queries you think there have been more of that one what was this one this is an example because I'm not familiar with the work order data that when you're looking at the results the only really way the only real way to know if it's working is if you really have a fundamental understanding of the data that's in those tables so that you can logically deduce if you're getting results that makes sense yeah yeah that Nate data modeling part you're talking about understanding the data types why they're there what to expect from those data type so exactly this is oh this is the adventureworks unless you've studied it and if you're writing the query and you don't know then you have to go find the person who owns the data that's in there right to say this is what I look I got Zac is this what you expected or not what should I expect from right yep perfect here's my aggregate function sample we've got the count of distinct sales orders unit price order quantity average unit price sorry minute minimum order quantity and maximum line total so we're gonna go ahead run that it's all aggregate functions so we're gonna get one line unique orders 30 1465 average unit price is 465 dollars and some odd minimum order quantity is one I would hope so otherwise it wouldn't be an order and the maximum line total and one of all the items in the sales or a detail is 27,000 893 so hang on let's dig into this for one second you did a select count of distinct sale orders mm-hmm and you added a bunch of aggregate functions right so basically in one T sequel statement that is one two three five lines of code can you summarize all of the sales in my in my system yeah try that in Excel that's pretty amazing I'd say that's really cool I mean I know right now that the I was price but we have we have this these are unique orders let me just do 31,000 orders that's that's unique order so let's take this baby out let's just grab every order so let's take that and run that now now it's 120 1000 orders and the average unit price of each order of the order all of the olive oil 21,000 $365 know what they're selling but I like it and still the max that's not gonna change this probably change because we have more call up more rows in there yep let's talk again just a just removing this tank dropped it was at 38,000 rows and something we put up to 120,000 rows just by removing that one claw so that distinct cost is really really important I just want to showcase so we're gonna do now this is inserting data what we'll do now is go back to PowerPoint this what we're gonna do so don't get to demo happy again I keep doing it all right let's go back in so those are select statements so some of the stuff we just showed you it would be impossible to do with with Excel and it's just all we had to do is that it's the upfront work it's the data modeling is setting up the relationships once you have that done it's just so much easier for you to slice and dice the data so that's the selecting that's again that's just a piece of the Select statement that weird that we're looking at here so and there's more on getting that querying sequel server Microsoft sequel server 2012 class we're gonna dig much deeper and those joins and a bunch of other items tomorrow or gonna record that alright we need to add data so in order to add data we can use the insert statement so we do an insert into and then we supply the table name alright in the schema the values alright so I'm going to add in two now notice I'm not supplying any columns I'm gonna just say if I know this if I understand the columns that are in there I don't need to put the column names in there so I'm gonna say insert into production unit measure value the first field is going to be ft the second field is going to be feet and the third field is going to be a date okay now what this is going to do again I'll go back out execute it's going to add a row into the unit measure now if I want to add three rows I could go in something somewhere insert into production unit measure here's the first one if I change only change it in the inscript and here's another one so I'm adding a square feet I'm adding we're trying to change I can't do that I'm gonna add yards and when I had cubic yards my my script is slightly different so when I go out and show you that this is gonna be slightly different I changed it because I wanted to show you something because right now I'm hard coding a date that's really kind of unrealistic what I'm gonna show you the script is why don't I just throw in today's date so I'm gonna change I change this a little bit so it's going to actually go out retrieve the system date and populate that instead of this hard-coded date so that's how I can add content insert into either its individual row or multiple rows using insert into if I have a flat file just a file on top txt file for instance and I want to add content from that I can use what's called a bulk insert and there's a specified format specific format you need to use but I'm just thinking what if you have an Excel spreadsheet you dump it out to a flat file with common delimited can I get that into sequel server you can and the easiest ways to do that is to help sequel server understand the format of how that data is stored in the deck and the text file using a format file and then using a bulk insert command I'll take all the rows in that in that file flat file and move them into a table it's all I want to say about that but it's another way to add a bunch of data or if you've exported data from somewhere else you need to get it into sequel server that's gonna be your choice for doing so it'll be a lot faster again let's get paid by the hour where you want to do individual insert in two's go ahead and do it that way but a bulkinsert will be much more efficient all right the update statement we're gonna update sales person again I change this query slightly to stay with our theme what we were working with but update you specify the table set is the key word here set bonus equals 6,000 commission percentage equal 10 and sales quota equal note so it would what this would do it would set those values on those three fields where sales salesperson business entity equal to 89 that's a huge problem if you don't put a where clause on an update state if that where Clause wasn't there what would happen just now everybody would have gotten a bonus everybody would have gotten a six thousand dollar bonus a commission rate of 10% and in sales quota would have doesn't set to no that means they don't really have to work that to me was what I call an RPE anybody heard of an RPE that's and I teach the admin class for a SharePoint and sequel server and RPE is a resume producing event we want to avoid resume producing events I think is everybody getting a six thousand dollar bonus executing this command cloud without a where clause could be an RPE so we want to be careful with those all right delete statement delete from the table we're a production unit measure name equal feet I get if I did a delete from without a where clause what do they just do deleted everything in the table wrong I toasted everything in the table because I want to use the toes command but yes you're right I deleted everything in the table so when using updates and deletes where clause or want where clauses they don't word I know they're essential because you can really mess up the data if you don't do that if you don't supply a where clause a delete statement without it where Clause will cause all roles to be deleted that's my point I'm making there and I got slightly different jobs commands and the in the query it's anything but you know this okay okay so let's go take a look at this now let's go back into these statements here and we're gonna pick up where we left off first off I want you to see what we have for information and unit measure so I'm just going to query that kind of do a table dump and what I want you to notice is that we don't have anything in there for F 2 and F 3 but no no no square foot square feet cubic foot no square miles all right it's nothing in there for miles for M 3 so I just want to show you that now what we're going to do is we're going to insert a row of information and I'm going to insert this one and notice what I also do differently instead of hard-coding the date I'm going to insert it into this unit measure ft and feet and then get date it's going to populate today's date in there instead of a hard-coded date so execute that one row was affected I'll go back and query that table in just a moment remember I could do multiple statements so here's that 1ft - so remember ft now should be in there ft 2 m 2 and Y 2 are being added now notice I intentionally call this square foot in case you're thinking as I do don't you know how to talk that's a that's on purpose I'm going to execute that three rows affected let's go back and query that all right we're gonna f5 this now we're gonna look for ft and our ft - I'll just scroll down here here's my ft I just added here's my ft to do a square foot I added on purpose that was wrong why did I do that because I want to show you the update statement we're gonna change we're gonna do update product measure or product production dot unit measure we're going to set name to square feet name is right here instead of foot we're a production unit measure dot unit measure code equal ft 2 so this Deuter right here should end up with square feet so we're gonna execute this guy all right one row affected and then when I go back and execute this command to find out what's out there see if we have a square feet instead of a square foot moving down ft2 and notice now it updated just that one column again without this where clause every item in here would have been square feet so we got to be careful about that we can delete a row to lead from again we need that where Clause we're gonna delete the ft you'll see it's right there simple statement delete from the table and then you specify that criteria execute that will read re query see if ft is gone and you'll see that ft should be gone here and you'll also see so I didn't point that out to you the first time ft is gone you'll see now that square feet has today's date instead of that 2008 hard-coded date it actually won that grout out and grab the system date and time and populated that for us alright so that's that's the DML statements other than the the select statement that's the other items inserting just i've just a brief introduction to end turning updating and deleting content inside of sequel server we get just a couple more items so we're going to introduce we're not going to go in-depth on these but these are indexes and triggers I briefly mentioned these triggers already indexes we talked about we have either clustered or non-clustered index the big thing about those is you can only have one clustered index indexes are very powerful notice the first line allow you to speed up the retrieval of data stored within a table review okay that's the primary reason we add an index is to speed up the retrieval there's a bit of overhead involved with inserting content if you have indexes turned on especially a clustered index because it physically sorts if I have in a clustered index on the last name every time I add another last name and there's going to shuffle that date around to make sure it's alphabetized or if it's an ID it fits in that ID in that row so they're used to speed up your retrieval of content we'll just leave it at that and we have clustering index we have non-clustered index you can only have one clustered index because the data can only be stored one way physically in one order and we have any number of several several of non-clustered indexes and remember I talked about the unique index if you're looking for uniqueness on columns you want to enforce uniqueness primary keys you can only have one per table your unique index would provide you that that functionality creating a trigger we got triggers are used to enforce a business rule and then we'll just wrap this up yeah I got one more quick little demo on the trigger so you know what that does you don't think I'm just making up that word it's nothing it was your horse I know Roy wouldn't let me was it Roy Roy Rogers what's that trigger whose horse nose trigger how old I am okay so we got this all this content here all this trigger is doing its called insert success this isn't doing much except for I just wanted to show you what a trigger does this is a DML trigger this is saying whenever anything gets inserted into the unit measure it's called raise an error even though it's not an error and that's just the term they use it's called raising error and it's just saying I just want to get a quick little message saying this was added remember this could be something like hey when you do insert an item here update an item over there talk about it for referential integrity or what if you change the last name and employ because someone gets married well if we change the last name there well we need to employ ups also update it Annie a payroll table so I can have a trigger that fires off when you do an update on this table also update this table in this table so that's how they'd be helpful in referential integrity we're gonna execute this I didn't get my creat sugar I'm product after insert oh that creates it alright and we're going to test it and I don't have a command to test it so just let's see we're gonna insist we on do an update so where's our update statement up here this guy right here will change this guy to square feets just so we know who changed it and we'll execute this guy here just don't even get update production you gonna measure should've gotten just a baby a little command oh I did insert okay we need to an insert that was an update let's do this so we're gonna change his trigger instead of after insert I did to some purpose you know what we could do we're gonna change us to update instead and then we're gonna go up change this to square footage that's not how you spell Oh foot edge feet two feet footage fukuda just cool to teach I don't think that's where footage now we'll do one more update on this baby right here execute that naming go square footage where near f2 I not grab that last quote maybe I didn't let me see I bet you probably didn't well I still get my trigger though oh the trigger was already created okay so you know what went wrong there is I had a trigger created for insert that's already on the table all idea is try to change this I create another trigger for update which probably failed because it's already one out there should have gotten an error on that so I would all this would have dotted down here said a one row effected it would have said unit measure is successfully added it's not pretty but it's a way to let you know a better way for using triggers in the real world for referential integrity I make an update on the last name column in the employee table it needs to be updated in three other tables that trigger fires and it updates it and accounts payable and everywhere else that can last name needs to be updated alright cool module on select statements and working with select statements and all the cool things for slicing and dicing the content we went through all that with you we talked about the different arguments the between the knot we saw all of these in action except for join just wanna introduce those to you and then we talked about DML commands insert bulkinsert update delete we saw these in action we looked a little bit that the clustered index and the non-clustered index nothing to be too concerned about in the unique index and then we talked about the use of triggers and now triggers can be used for enforcing referential integrity if I make a change in one table it can go out and update changes in another table in our next section we are going to talk about the fundamentals of database management more of a if you're a DBA I'm going to talk about securing objects we're also gonna talk about backing up restoring content because that's one of the big tasks that you'll perform as a database administrator well hello we are at module 5 the database fundamentals course on Microsoft Virtual Academy dot-com Wow so if you've been with us for the first four modules already we started back with core concepts of what a database is and why they're important we went on from there to a second module that was about normalization normalization conceptual and referential integrity kind of a little bit of design fundamentals yeah then in module 3 we did how we actually create databases and the objects that live inside of them and then we just got out of module four where we learned all kinds of things about using data manipulation language to get data out and then we talked about insert update and delete to manipulate the data that's in a database which is a lot of information to cover in just these four modules but in module five we're going to just wrap the course with a little bit of information about how to do basic administration and this is very specific to sequel server which we've been using as the example database management system for the course and you're gonna help us understand kind of a little bit of security fundamentals and some data management backing up and restore type functionality for keeping our database administered nicely nicely yes that's a good way to put it exactly what we're gonna do is one of the things we've talked about is how much data we can store inside a sequel server how we want to the design that data and how to add the data and retrieve the data but now we need to protect that data so we're going to look at how sequel server implements a security if infrastructure and the other pieces we're gonna look at in this module is making sure that that database the data is backed up regularly in the event that something happens you need to recover that data so we're gonna give it a to the basics on doing backups and restores so securing the data and then backing up and restoring that data in the event that you need to do so so we're going to talk about that in this module we're gonna begin with sequel server security really that when you look at that there's three components of security we have what are called secure Buhl's which is the server the database and any object let's store procedures and the tables and the views that we looked at we also use the term principles the principles are the individuals or the groups whether it be windows groups or individuals logging into to enter sequel server that are granted access to sequel server and then the last piece are permissions what type of permissions are we going to give someone to access the content stored in a table or stored in a in a view so it's the secure that the secure moles the principles and the permissions and this graphic here shows you kind of a high level of how that works you've got the principles over on the left-hand side and you have windows levels security because you're gonna have two types of secured you can have windows f9k you can have what's called mix mode which is a combination and windows authentication and sequel server logons so the windows level we can actually use Windows Group a domain login or a local login at the sequel server level we have fixed or usually defined server roles we'll introduce those and we have a sequel server login so first off you have to log into sequel server before you get access to the database before you can access objects in the database so it's kind of a three tiered approach that we're using here at the database level we have fixed database roles not server roles anymore but database roles we also have user defined database roles that we can create we're going to talk to those and then once you get inside the database you've got these items tables views functions procedures different information that we want to protect so to me I always like to introduce this as a three tiered approach give the individuals access to sequel server give them access to the databases because they don't need to get access to every database give them access to the databases that require access to or that contain objects they require access to and then give them access to the particular objects and I can give them different types of access I can give them just the ability to query information from a table or to be able to update information in a table as an example so the three tiers are accessing sequel accessing the database and accessing the objects within those databases and we talked about the different terms available for us the permissions the secured Buhl's and the principles for doing so here's our three-tier approach we need to login it can be a sequel server login there could be a pass-through login using windows authentication to get access to sequel server we then need to give you a user account in the database that you need access to and lastly we need to apply permissions to the user account which oh by the way this map to the login that's at the sequel server level level so we have our sequel server login we can map a database user to that login in every database that you need access to and then we decide within that database what permissions that you are going to get access or what objects you're gonna get access to and what permissions you have with those with that for with that object we have a couple different types of authentication we have windows authentication or pass-through with n occation so it takes your credentials that you use when you're logged into the domain and it uses those to authenticate you and give you permissions to log in the sequel server we also have a group be in a window security group and that security group if you're a member of that security group if that security group don't as been applied given granted permissions you're gonna have access for that and so that's windows authentication we have what's called mixed mode which is a combination of windows and sequel logins so if you don't have a windows authentication a way to Pat to or log in we can create a login for you inside a sequel server this is great for individuals like vendors that are external the organization that don't get authenticated by an Active Directory environment or Windows environment so we have to give them a sequel login and what we can do with these logins is we use these logins to map them to either the server roles fixed or user defined or the database roles fixed or user defined and that's what's going to decide what permissions you get inside the database that you get access to now by default sequel server provides several different server fixed server roles to define few the key ones are sis admin you get full party rights to do whatever you want you can uninstall sequel server so you've got to be really really careful about giving that one out the DB creator allows you to manage databases so I can create a database and manage that entire database that's another fixed server role security I'm in allows you to manage who gets access to sequel server who gets you know access to the logins can manage the logins so these three up just three of a several that are out there are available to you out-of-the-box sequel server 2012 introduces user defined server roles these roles may not meet your needs for what you want your users to do so you can actually create user defined server roles that have special permissions depending on what you want the users to build to perform once they connect a sequel server what you do is when you create the login you identify what roles you want the individuals to be in and that's going to determine what permissions they get so you have your fixed database roles I'm sorry your fixed server roles and then you also have your fixed database roles and within that that's where we start talking about so we've talked a little bit about connecting to sequel server using a login from Windows or sequel but we also have look at securing sequel server databases and the objects within those databases and as we I've just briefly mentioned once we get access to tier one which is the sequel server level tier 2 is access to the database level or whatever principle you want them to be access I can map any of my login so it'd be a sequel login or windows login to different databases depending what they need access to once I give them access to the database I can define what roles whether they be usually defined or data based fixed database roles I can define which roles I want them to be applied to and there's a special role that's out there people always see it so I want to bring it up if you happen to manage permissions there's a special role called public you can't delete it you can't modify it you can't move people in and out of it it's just there and so what happens if I happen to attach or try to connect to a database it automatically once I get permissions to that it automatically allows me populates me into that public role so nothing nothing no management it's actually used if you want to connect to a database and let's say you want to print something it kind of gives you some of the default permissions you've had you would have or walk inside of a sequel server the fixed database roles just a few of these to mention these are the fixed ones DB owner which means within that database member we add DB creator that was up to fixed that's a server level they can do anything you want they have full party rights DB owner has full party rights for that one database so if I'm a DB owner for sales database and Peet's DB owner for the adventure works database I can't do anything in his database he can't do any in my database unless he gives me permission so your DB owner for a specific database or specific databases that you have permissions to another popular role for the fixed database roles is a DB DB data reader so anyone who's in that role can read any data inside that database so it's kind of like granting read access to everything in the database and another one is DB data writer for individuals who actually want to be able to perform those inserts and updates and deletes that we walk through in the previous module so those are available to us as a few of the fixed database roles somewhere to fix server roles we can create user defined database roles it is highly recommended for us to create fixed are used to find server roles and use defined database roles because it gives us a more granular a way to provide more granular permissions to our users we have more control over what our users are getting for permissions permissions if we use our user defined roles versus the the box define a fixed database roles or server roles so either way well however you decide to do it use these roles whether it be the use defined or fixed roles to provide permissions to the users inside of sequel server let's just jump out row briefly here and we're gonna jump back into our base our sequel management here and so remember that first year I said we need to get into security so we've been talking about databases primarily in here under security we have a thing called logins the logins is where you want to be able to map or users to give them access to sequel server it's pretty straightforward of course there's transact SQL statements for this but we're gonna do the windows way because we don't get paid by a four by the hour so we're gonna go to create a new date a new login called contoso Linda and then we're gonna go at default database they're gonna map to get my mouse back disappeared on me I don't what screen it's on is it on yours over there really I do have a mouse and yours there is okay so I'm gonna say they map soon as when this person logs in Linda logs and she maps with venture works 2012 I can actually go up to this the fixed server roles these are the ones that I introduced to you already I'm gonna trust that she can take care of security so I'm gonna make her part of security admin here alright now I could also go into the user mappings here at this point and here I'm gonna say I'm gonna give her access because although I said a water default database to be adventureworks I'm not even giving her access to do that yet so what I need to do is check this box and say for adventureworks Linda has permissions to access that's that second tier that we're providing and being and then I'm gonna make her DB owner it picked up the fact that she had security admin by default it added DB owner for a fixed database role I can change that to just be a DB data reader if I wanted to so when you create the log in which is mapping your windows in this case here mapping your windows authentication login to sequel server it provides you the capability to also at that point decide what databases she's going to get access to I can even go down and secure holes here and in here I can actually search inside databases permissions at this point I'm gonna do that a little bit separately I just wanted to show you at this point here what I can do is first off map her account her windows account to sequel server gives her that first tier access secondly and assign the what server role permissions she gets thirdly assign what day to day she gets access to so I can go ahead and do that and I'll just go ahead and click OK on that and I'm gonna get that failed no just a comments name is missing entered for select in two statements let's see here so maybe I probably need to do this let me try that one no great failed for login so we're gonna already exist ok so let's see if that's true let me go over to adventureworks the users who start over under security over here users oh she is already there all right so we're gonna toast that right here we're gonna delete it and okay alright now let's go back to try that again so I was yelling because I was trying to add her to to adventureworks as a user and that was she was already in there as a user so let's go back to our login box oh you fell quit yelling at me try another ok still thinks it's there let me do it quick refresh refresh usually helps with this okay I don't want to create a diagram I promise let me refresh this let me go check security and users not to be found anymore all right let me try one more time here see if it'll add er this time here if not I'm going to switch to a different database if it yellow this time all right so I'm gonna do is I'm gonna give our access to our class demo database instead meaning it still thinks she has access to that let me check everything I've done here I go back at the general contoso Linda server roles security admin user mapping will give her access to class demo and room and we're just going to click OK hmm that's what already exists all right so maybe it's it even further maybe let's go back to here refresh all right let me delete it from here let me try this one more time it seemed to see her and security logins already so let's try this one more time here so let's check again contoso and server roles security admin class demo okay that time it worked for some reason it's still hotter and logins let me just refresh okay she shows up there and then we go to the class demo database that we created we'll go into security there and you'll see under users that she has access to there now so using a quick pretty easy and straightforward the interface where I can create an account windows account map it to sequel server give her access to whatever server roles whatever she can be able do at the server level which was security permissions and then give her access to databases in whatever database or whatever permissions she'll have within those databases so we should just talk about what just happened sure in context of the rest of the course that we've been through when we started out really simply with the CD library example that might just be a single-use database well we got up to something like adventure and that in that case you wouldn't necessarily need elaborate administration now if you were an access you might grant it to one other user but you might want to lock it down so that lots of people didn't have access to your library in the case of the adventureworks it's an enterprise class database with all kinds of different tables in there to manage different parts of the application in that case you want to think about the security model and the administration of the security to provide sort of the least privilege for the users so that people can't go get to any of the data that they may not have the business access to see and that's kind of what's happening here with respect to the Security's your your thinking about the business use of the data you're thinking about the users that are going to get into that data you're trying to create a security model that that provides that sort of business rule for who can get into the data that's exactly right so yeah just so the the event that you want to protect that data you've got ways to protect that data like you said if we're just worried about our you know our albums may not want you to be too worried about that but this is just a more of a heads up as to what you're going to be or you would be concerned about if this was in a bigger environment that's exactly what we're doing there's a guest account that's created every database it's and what how this is used is if let's say I want to give everyone access to this database it's gonna be read only database I want to give everyone access to that I can do that by just making them just enabling the guest giving them access to sequel server and it's being they have access to the database to sequel server if I haven't specifically give them access to the database it will allow them to come in as a guest now by default guest doesn't have anything for permissions so you'd have to assign permissions to that guest account it's enabled by default you can disable it if you want to disable it using this command here revoke connect from guest and that will prevent people from being added into that so if you're in a real high secure environment we don't anybody seeing anything well let's you give them specific permissions you want to be careful about that guest account because it is enabled by default and is available for use by default if someone happens to be create a login didn't deny their permissions to a database they do by default have permissions to whatever the guest account has in there so they can at least connect to it and be able to establish that connection then we manage the permissions it's supposed to be two lines three lines here we may as permissions using three commands and these commands here our grant so I want to grant the select permission on a particular table to Pete so I would do grant select on table XYZ to Pete and then I could do revoke so my revoke is going to reverse the last permission that I provided to an individual the reason I'm saying that why I jumped down to revoke and over deny is because I could do it an explicit denied to peek on a particular table and how I get take that deny away is to revoke it so it's got always the revoke is always going to reverse the last permission that you apply either a grant or a deny now why would I use a deny deny is available if I want to explicitly say that I don't want Pete in looking at this content now Pete can come in through an individual account you can log in a sequel server that way but Pete's also a part of five windows groups as well if I do an explicit deny it doesn't matter if he's part of five hundred windows groups I've explicitly said do not let this person into this but this database or look at this object so your grant permissions you apply permissions using grant you revoke them I put them with what I call a true neutral mode I haven't said you can touch it but I haven't said you can't touch it by using deny so you grant and then you revoke or you deny as the anybody bloke so you we use these these terms or these statements to manage our permissions in sequel server specifically get the object level now what I can apply for permissions I can grant select - for Pete so you can come in and view content I could grant select and insert so you can view content and add content i could grant select and delete he can grant and delete content so I could be very granular as to what permissions the individual or the group can have on a particular object inside of inside a sequel server and we're talking about the DRI data referential integrity that's the reference when we use that for the foreign key back to the primary key we talked about earlier and the execute is for executing stored procedures that's a short term for that is exe C instead so those are different types of permissions that we can apply to tables or stored procedures or views depending on the type of object will determine the type of permission that we apply to to those objects but they're managed using the the the grant the deny and the revoke it's that things is that enough you think - I think that's great and the Wayne this is another that we've said this several times during the course is another opportunity for a lot more further study we have an entire course just on database administration that goes in-depth on sort of the theory for database security as well as all of the implementation details for how you set it up yeah I think the thing we wanted to do is just make sure you're comfortable with the idea that you know we're not just because you created these database and tables you do have to be aware of the fact we can lock these down we can secure them not worried about if it's just our artist collection but if we are if we're in a more of an enterprise level or bigger environment where we are concerned about security there are ways to use and I would I call a three-tiered approach to sequel to the database to the objects that we can control what now what the or your users get access to so just a more of a high level overview now one of the other things that we have to be concerned with is protecting our data because one of the if you are a DBA or aspiring to be a DBA one of the things what are probably the biggest things that you're responsible for is protecting the data and making sure it's available in the event I have a power failure you lose on disk drive anything any possible possible situation where you might lose lose data so what we're going to just do is briefly introduce just so you have an idea because often we get questions like okay Brian I put all this data in there and I'm gonna store it in there but what happens if I lose that data ok lab like what happens if I lose my Excel spreadsheet well then we have backup and restore capabilities inside of a sequel server we're gonna use the GUI to keep it simple but we can write scripts we can script this all out so that way we can run these at night and the advantage of running writing scripts so the GUI I'm about to show you again if you get paid by the hour you could set the alarm at 2 o'clock get up at 2 o'clock in morning you log in and you perform a backup using the graphical user interface and SSMS but if you don't get paid by the hour you get a write a script that performs the same task I'm about to for perform and trigger it to kick off at a certain time so it's running while you're sleeping or just getting home depending what you're doing in your night life so let's go take a look at the database backup it's exit is to perform in the event that you need to restore that content somebody may accidentally delete some content or we may accidentally a hard drive may crash and the content that we were I was on that hard drive gets lost we need to be able to recover that content so what we can do is use a database backup that will allow us to restore that content now we can only restore it depending on your and we won't even get close to you being able to explain all this but we can only restore the content as good as the last backup so if you do backups once a week and it's on Sunday night and you do the system crash or you lose a a hard drive on Friday you've lost a week's worth of data so there's a whole strategy in place on what how many backups and how often and the different types of backups that you need to be aware of and there's really just three key backups that can come up with a decent strategy for you those are full backup where you backup everything regardless of has changed we could do a full backup every night if we wanted to if I got 30 gigs of content if I do a full backup every night and it takes three hours when only 10 percent of that content changed maybe I don't want to do a full backup maybe I want to do a differential backup differential back up I also call it a cumulative back up it backs up the content that aren't that changed so again we did a full backup on Sunday it backs up all the changes from Monday I do a differential on Tuesday it backs up to changes from Monday and from Tuesday I do a differential on Wednesday it's cumulative that's what I'm saying that Monday Tuesday and Wednesday are all backed up the reason that we like doing differential backups is because when you get ready to restore content we want to minimize the downtime if I have one full database backup that I have to restore and one differential backup that I have to restore it's going to shorten the time it takes to recover and that's gonna be a good thing so differential backup is a cumulative backup that keeps track of all the changes since the last time that we did a full backup an incremental backup is also a transaction log those are used synonymously so an incremental slash transaction log backup let's go back to our scenario Monday night we did a full backup Monday we did a transaction log backup not a differential Tuesday we did transaction log backup Wednesday we did a transaction log backup now if I lose content on Thursday what do I have to do I have to restore the full backup from Sunday the transaction log from Monday then the transaction log from Tuesday then the transaction log from Wednesday could take me a lot longer to perform the two stores of those transaction logs so differentials are helpful some people do full backups will say once a week differentials every night incrementals or transaction logs every hour during the day your strategy will do depend the bottom line is you always have to answer the question how much data can you afford to lose if the answer is none you can be very particular about what type of backups that you perform so you can recover that content in the event that you need to just to let you know these are the three primary backup types that you need to be familiar with there are others that we're not going to get into detail so if you're looking around at this cuz you're gonna be excited about sequel server when we finish today if you look around at this there are other types of backups that you can perform take a peek at those but these are the three primary ones that you'll be using here so it's going to backup a database just to show you how easy it is to do this because we're gonna use the GUI and back in a sequel management studio let's back up and see what's in class demo closed demo nothing let's go I thought we created a table in there to be not over there well we created that one in sales let's go and create let's do this let's do a new table in class demo data click that so I showed you how to create a table using transact-sql you can also create a table inside the GUI it will happen so we're gonna call this column one real definitive as to what it is here's my data type the default is end char 10 and we allow those so not sure you were gonna start creating the CD library database artists will do the artists you can do the artist table interesting no do the artist 1 yeah I know I didn't want to get over to I keep losing my mouse I didn't wanna get over to the disco one you know the general so artists what we had well we would have artist ID actually artist ID and probably artist name maybe yeah artist name would be good probably a picture no artists name I need you to do notice I'm gonna use that Pascal birthday birthday I'm gonna keep giving you stop so all right so as many as you want let's change this because now we found out these guys me crazy with names we need to join this back to an int for artist ID because that'll be your key look at us sort of reviewing everything we just did yeah this is gonna be our int there and we're not gonna allow those and let's do one more here or each birthday maybe birthday cuz I'm sure that you and Donna Summer have the same birthday yeah well we got two different schools together so and once that'll give us chance to use I just have no idea what my most just disappears every once in a while as soon as I get my mother there there's it just disappeared something out of the van it's alright and we've got date but this is cool this is a third data type in this table yeah so we have an int and varchar' on a date the bad thing is we're gonna toast it anyways but not really but alright so we have a new table here it's gonna say this table out and we're gonna call this artist and I feel I think we should have done a generous or I could put it just type the word disco in and we we talked about all week all day alright so we have a new table so we'll come over here in our tables will refresh it we see our new table here all right now the reason I did that because I'm gonna back this up I'm gonna go to tasks and I'm gonna use the backup option and it's gonna back up a class demo and by default it dumps it way down here and you know we're gonna get rid of that I've got another easier directory to remember to chase it down afterwards and we're gonna back this up to it's always you know I wouldn't say it I was gonna make a joke but someone really doesn't understand it might think I'm serious and I'm like no well we're gonna back up sequel backups and we're going to call this class demo DB it'll use the dot bak extension so I'll just let that go I'll let that go there comes back so we're gonna back it up to there and we'll go ahead and do it okay and then we'll click off one okay and this one and the backup database class demo completed successfully all right cool so that's done and we know in there that we should have the DB l o2 DB old artist now there's a slide on restores I'm gonna stay in the demo but let's pretend that somehow this DVL artist gets toasted you've been waiting to toast something for the entire class I know I actually do I think you should do that I know I should have actually what I should have done is preparing written s daughter for political toasts yeah I mean it weird actually had at once equal command for drop yeah I'm gonna definitely write that so I'm going to delete this this could be somebody accidentally deletes it or they run that delete command I told you that without a where clause or an update maybe just that the day is bad because someone we haven't update without a where clause in this faster way to get it back is to go to a backup so I'm gonna go ahead and toast this and we doing refresh over here in class demo you'll see under tables now the tables gone gone alright toast alright so how do we cover it class demo go back to task and we have the restore option and database and we're just gonna stick EEP this the simple method it knows what's been backed up it's telling me right now the only thing I have for you is a full backup if there were differentials and incrementals it would list them all out it would make sure I restore it in proper order wouldn't let me jump around and stuff because that'd be badness so it knows we backed it up here's the date and the time for the backups we know it's good and then see here I'm just gonna leave it just at this at this attempt to restore and during the restore here it's actually what it's doing it's actually looking at the information that's in the backup and determine exactly where that needs to go to do that restore and looks like it's okay well how can you test we will back see if our tables there let's do a refresh okay just because it's not showing up still not showing up sometimes the Refresh has to go all the way at the top here let me do that one let's see what am i doing this guy here I'm still not there all right let's try another restore cuz that didn't seem to pick it up so I did the backup I toasted it she'll be there alright let me try it one more time alright tasks restore database you know what it didn't come back up and say it was restored it should come up and say it was restored and I didn't see that pop up box so let me try this again so we've got this check the backup is still on disk so it should be totally fun right correct it's listed right there this ends right here it should say restore completed successfully and I didn't see that last time and it really shouldn't take that long because there's really no data in there I still think this might be a ploy so you can create those genres table yeah I really want to type in disk out now see it's not it's restored anything failed alright let's try this let's do the verified backup we're just real quick that's okay all right then we do cancel this let me try it one more time tasks restore our database um class demo DB is the database take a base there database full backup we just did see if there's any options I'm not trying to move it is this similar is there somebody connected to it is that that problem um it maybe let me try this cuz it's internet and just mainly des and a single user let's try that just in case it is cuz should be a nice right there that's what it was because I was still connected to you were still connected to it yeah I thought we were gonna have to do some tap dancing or something - yeah entertain the audience while that was happening yes seriously let's see if it work though it's gonna be sure it's over here yeah there we go yeah I was still connected to it you're right so it didn't get it was it needed sync it need to be in single user mode so I did that on purpose you guys can see it's not all peaches and cream to try to do a restore there can be issues even though it looks pretty straightforward there's little things that connections are out there already established you need to make sure brings a during the restore it brings it into certain mode so it could've actually has exclusive rights of being able to do recovery which is why there's a whole course that has like three modules on backup and restore energy limitations yeah disaster recovery and I mentioned the term earlier about our Pease resume producing events dr is gonna be most likely reason you have to generate a new resume because just a not enough the backups weren't done or people who don't performing a backup and they didn't realize the backup wasn't work which is actually pretty cool because we've gone from the very beginning of the day we went from a spreadsheet which you can have a backup strategy for you can copy it to multiple locations you can do cloud backup for a single file and things like that we've going all the way to I need to take a class and just how to do like a strategy for creating incremental transactional backups for my enterprise class database yeah I mean the backups is a class and backup to class on data modeling or designing your database would be another one once you get the database design and out there what's the best way what kind of queries can I perform add content update content to it so all sorts of options that we've been been through today and a high-level overview but just it's the real world for what it needs to be it needs to happen so we did so very similar to backups we had the complete the differential restore we would perform those in order and once we did those in order that would make it easier for us to get that complete database back up get the differential backup and then get the transaction log a backup restored an already did the restore because I was already out there so objects that we need to secure three-tier approach giving them access to sequel server whether it be through Windows authentication or a sequel login if it's someone external to this the sequel world we introduced the three terms of secure walls principles and permissions the second area was giving them permissions to the databases we saw that we can do that as we create the login and the third one was giving them permissions to the objects using the grant revoke and deny permissions to to manage those permissions so login database user and then permissions to the objects or the ways that we're going to control our information or security permissions to our information and with that said Pete I think like you said we started out with a basic whole spreadsheet this morning and now we've gone through it's like how we can create a relational database with tables and enforce referential integrity by using primary key constraints and foreign key constraints and we talked about how we can protect the data by implementing security now we can use the Select statements for retrieving the content and slicing and dicing at several different ways different ways for adding content deleting content and updating content and then we wrapped up by talking about ways for securing that content and protecting that content by performing backups and restores so anything you like to add I think I think we've nailed it I think we covered everything we wanted and we don't you just don't know what disco albums you had sold no I was just all that I not a mystery he won't follow me on Twitter and ass we did learn though that there is was at 360 character title name out there so that was going to totally redesign 60 word though it's 89 words David yeah totally redesigned star playing before your data yeah exactly know your data thanks very much folks thanks for watching thank you

Channel: Extern Code

Views: 12,584

Rating: 4.9469028 out of 5

Keywords: database fundamentals, introduction to core database concepts, database management system, online courses, database fundamentals video, database fundamentals mta, database fundamentals certification, database fundamentals microsoft virtual academy, database fundamentals tutorial, database fundamentals for beginners, database fundamentals exam 98-364, database, database tutorial, database normalization, database administrator, relational databases

Id: sIi7IfiTIsA

Length: 208min 52sec (12532 seconds)

Published: Fri May 17 2019