Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

What is a Database?

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
[SQL Server 2005 Express Edition for Beginners] [01. What is a Database?] Hello, welcome to this 8-part video lesson series titled SQL Server 2005 Express Edition for Beginners. My name is Bob Taper. I'm with LearnVisualStudio.net. During the course of the next 8 lessons we'll start with the absolute basics and progress to the point where you can build database-driven websites and applications on your own. While this series requires no previous experience with databases, you probably should have some experience with a programming language like Visual Basic or C#, and also, most of the examples utilize Visual Web Developer 2005 Express Edition to create ASP.NET 2.0 websites, so if you haven't done so already, please take the time to download and watch those videos also available on Microsoft's website. I want to start off with the basics. I'm going to begin with a quick example. Whether you realize it or not, almost every webpage of any significance on the Internet today is database driven. Now, what do I mean by database driven? I simply mean that many websites retrieve data from a database and merge it with an HTML template to produce an HTML page that's consumable by your web browser, or the data that's collected from a web page's forms are saved to a database. For example, I created a little fictitious example that we're going to go through later in this video and the other videos in this series that demonstrate how an ASP.NET webpage interacts with the SQL Server 2005 Express Edition database. As you'll be able to see here, I'm going to take a fictitious email address and then click my Submit button, and you can see that I get a page back that says, "Thank you! You'll be receiving your next newsletter shortly." What happened between the point where we clicked the submit button and to the point where we got this confirmation page? Well, what's happening behind the scenes is that data is being sent across the Internet, and then the web server is collecting the data and saving it into a data source, in this case, a SQL Server 2005 Express Edition database. Now, in this particular example we're inserting records into a database, but we can also show how using Visual Web Developer 2005 Express Edition we're able to quickly create a little admin page that allows us to retrieve data from a webpage and allows us to interact with it in some meaningful way. In this particular case, you can see that we are able to change the email address. We can change the date and the time that it was entered into the database, and even the IP address that we secretly collected behind the scenes in order to identify that this person came from a particular address to try to prevent some fraud. We can also opt this user out by selecting this little checkbox and then clicking Update. We can cancel that operation. We can even delete this record by clicking on the delete link. But could you believe that it only took me less than 2 minutes to create this administration application using the tools available in Visual Web Developer 2005 Express Edition? Let's take a few moments to briefly talk about what we're going to cover in the next 8 lessons and then move on and start discovering what databases are for the remainder of this lesson. In this first video, we're going to talk conceptually what a database is and why you should prefer to use it rather than other options like text files, spreadsheets and so on when you build your applications. In lessons 2 and 3 we're going to talk about the major database objects that are used within a relational database management system. And then in lesson 4, we're going to talk about how to create tables in a relational sort of way. We'll talk about relational database theory and how to take a rather flat database structure that you might be used to if you're already creating Excel spreadsheets and so on and how to translate them into a more relational style of database creation. In lessons 5 and 6 we're going to thoroughly explore the structured query language, or SQL, as it's known, sometimes called "sequel," and we're going to use it to retrieve data from our database. We'll learn how to sort that data, how to group it and aggregate it as well as how to insert data into our database, how to update it, how to delete it, and so on using nothing more than this special programming language called SQL, or structured query language. In lesson 7, we're going to talk about security and network connectivity. There are some unique features of the Express Edition of SQL Server 2005. We're going to tell you how that makes writing secure applications easier using the Express Edition tools, but we'll also demonstrate how to open up your database so that your application can be distributed across many client computers, each one accessing the same data in your database. As we begin, let's start talking about conceptually what a database really is, and there is nothing magical about a database. It's simply 1 or more files that work together to store data in a very structured sort of way. They are optimized so that when you retrieve data out of the database that it can retrieve it quickly and efficiently, but also the organization of those files help to keep the data accurate, and we'll talk more about this concept of data integrity throughout all of the lessons. Now, some databases are simply files, and then other databases require that there be some server software that manages the connection to those files to regulate the users that are trying to access those files. And the purpose of that software is not only to make it accessible to multiple applications and users concurrently but also to allow for the safe management and maintenance of that data, and that tends to be one of the biggest focuses of working with databases is how do we manage it? How do we maintain it so that it's accurate and that we maintain data integrity? And so SQL Server 2005 Express Edition is a free application that allows you to create and manage databases for your applications. We'll be a little bit more specific, because at a very high level that's what it does, but there are actually many different pieces to SQL Server 2005 Express Edition that are used in various scenarios. Some applications work behind the scenes to ensure that multiple users can access the data at the same time while other applications are more of a management nature that allow you to add tables, columns and things of that nature and allow you to select data from the database, and we'll talk about all these things in the upcoming lessons. But keep in mind that SQL Server 2005 Express Edition is a complete tool set and not just the files themselves that store the data but then also the management tools and the server tools that sit on top of the file structure in order to help you use and manage your database. Now, if this is your first exposure to databases, relational databases specifically, you might be asking yourself why all this complexity? Why couldn't we store data in flat text files? Or even potentially store them like you've been doing in your small organization using spreadsheets or whatever the case might be. And so certainly there is a layer of complexity that's added to creating applications by using a database like SQL Server. However, it actually makes things a lot easier when you really sit down and think about some of the weaknesses of using simple, flat text files or spreadsheets to manage your data. For example, if you were to have a spreadsheet or even a text file of data that contained, for example, customer data, every time the customer purchases something from your company we have a complete record of their name, address, city, state, zip, along with the purchases that they made, and if you were to think about storing each record of an individual's purchases on a separate row of a spreadsheet you'll see that, first of all, there's a lot of redundant information. The address information, the city, state information, for example, might be redundant across multiple rows. Now, that could be a problem, especially in light of the fact that if they purchase 3 months from now some of their information may change. They may have moved. They may no longer work for the company that they once did, and so over time some of the data that you collect about this individual might become outdated. You might have a hard time figuring out which of these particular rows is the most recent row that accurately describes this particular individual in my spreadsheet. Not only that, but with all of that redundant data being saved each time in a different row it does increase the file size. Now, for a simple application that may not be a problem, but when you're saving tens of thousands of rows of data over the course of a number of years certainly there needs to be a better way, a more efficient way to manage that data. And although, for example, Microsoft Excel is very adept at allowing you to filter and search through the data in your spreadsheet, some more complex searches might not be enabled due to the way that the data is structured. Once we review the structure query language in lessons 5 and 6, I think your eyes will be open to all the possibilities, all the flexibility that was not available to you when you were using Excel spreadsheets to manage your data, and then other things like protecting data from malicious and unauthorized use. Is there any way to do that with a text file? Absolutely not. With a spreadsheet? Not very easily. How about protecting the data from becoming corrupt? Well, there again, if the file became corrupt, you're really out of luck, but as you probably can tell as I'm leading you into this discussion a relational database management system, which you'll often see shortened to RDBMS, will take care of many of these issues for you, and the SQL Server 2005 Express Edition is certainly no exception to that. It takes care of a lot of the heavy lifting of managing your data. So yes, there is a little bit of an overhead compared to the simplicity of text files and spreadsheets. However, the flexibility and the security of using an RDBMS system will really outweigh the disadvantages of having to learn a whole new syntax, a whole new way of thinking and so on. Up until now I've used the term relational several times in reference to databases. But what does that mean? What are relational databases? Relational is a type of database. Not all databases are relational. But it's probably the most popular type of database that's used commercially today. Relational databases were conceived of back in the 1970s by an IBM researcher who was named Codd who is the father of relational database theory. And they were popularized in the 1980s as some of his theories began to find their way into commercial tools. But Codd wanted to solve 2 basic problems that existed with databases up to that point in time where all the data was handled by these huge mainframe computers, and the storage capacity was limited and very expensive. First of all, he wanted to reduce the footprint of the data, therefore reducing the amount of space on those big reel-to-reel tapes that each database record required and so hopefully would reduce how expensive data was to store. And he also wanted to enforce data integrity. Data integrity takes several forms. But we're basically referring to the accuracy and the correctness of the data that we're storing. And to accomplish this, Codd suggested that if you break up 1 long row of data fields into several smaller records, each record being saved into a table that describes a specific subset of the data that's collected, you could improve the integrity of the data. First off, he suggested that you had to find what type of data you wanted to store, and that would prevent scenarios where you had string or character data in a field that should only be storing numeric values. So by enforcing strong data types, it makes the data instantly more correct and therefore helps maintain the integrity of the data. But then also by storing a data entity in just 1 place in 1 record you can eliminate the possibility of having multiple versions of the same data in your database, therefore improving the integrity of the data by reducing the possibility that one of the versions of the data is incorrect. Also another benefit of breaking up the data into smaller related tables of data is that you can rely on the software that managed the interaction with the data to protect it from deletions when deleting a record would mean that it broke an important relationship it had with other data that it was related to. You ensure data integrity by disallowing any potentially harmful deletes of data in the database. And probably most importantly was that by splitting up the data into smaller chunks you could retrieve data much more quickly than in the past. SQL Server 2005 Express Edition is a relational database, meaning that you get the advantages of relational databases and more leading to safer, cleaner data that's stored by your application. Now, if some of that didn't make sense, don't worry. In the next 7 lessons we're going to cover enough tangible examples that some of this theory will become very obvious to you. And while there are some inherent advantages to working with relational data, there are also certain services that we've come to expect from the software that sits on top of the actual file structure that holds the data, and we commonly refer to that as the relational database management system. That software will help support our efforts in a number of ways. For example, allowing multi-user access and also controlling access and permissions on a user by user basis. The topic of security authentication and authorization to perform certain tasks is managed by an RDBMS. Basic administrative tasks like backing up and restoring the data, making sure that it will be there whenever you actually need it. Also the ability to replicate the data across a number of servers so that if one server goes down that there can be some clustering, some failover. Or more importantly, that you can allow multiple applications to have their own copy of the data and it all stays updated. Also some basic ad-hoc reporting capabilities, the ability to write Transact-SQL, the variant of SQL that SQL Server allows you to use and be able to retrieve data without having to create special applications just to pull certain pieces of information down. The ability to write SQL statements and retrieve data on an ad-hoc basis is another important service of an RDBMS. And finally, just making the data available. Making sure that it's up and running and that it's serving the data, doling it out as requested. Let's move on to a more practical topic, and that is where do databases fit in to the overall architecture of your application? When you're first getting started, you're probably going to create fairly simple applications that will be deployed to a single machine. SQL Server 2005 Express Edition certainly supports this. You can deploy the actual database files to a folder of your ASP.NET 2.0 application. But what is actually going on behind the scenes to facilitate the access of that data? For example, at the very top here you can see where a network webpage is requested. Somebody types in http://www.something.com, and they come to your particular web server. And if your web server is running Windows 2000, Windows 2003 or something along those lines, the Internet information service is going to retrieve that request and determine what to do with it. Now, if the request has a certain file extension, like .aspx, then it knows that it is going to depend on the ASP.NET runtime engine to interpret and handle this request. Upon receiving this, the ASP.NET runtime engine will basically load up your webpage and will execute the commands that are stored within the assembly that's generated whenever your web page is run for the first time. That assembly will rely on classes that are within the framework class library, the FCL, which in turn go to a special subset of classes called ADO.NET, which we'll talk about in future lessons, and ADO.NET is responsible for communicating with your database, whether to save information in, retrieve information out, delete or update or whatever the case might be. And if, for example, information should be returned back to your application then that will be integrated in through ASP.NET, replacing the programmatic fields of information with HTML, returning it back through Internet information services and back out to the original requester of the information. Now, just understand that I'm really oversimplifying this just so you can understand the flow of information that comes in through a webpage request and gets all the way down to the point where it's collecting data from SQL Server and then retrieving that, integrating it back into the webpage and spitting it out. There's a lot more to it than that, but from an overall perspective, I think that that's a pretty good high level overview of what's going on behind the scenes. Honestly, SQL Server 2005 Express Edition was created with a single server scenario where you're not expecting hundreds of thousands or millions of requests per day. If you are expecting to handle that kind of load you may need to turn to a more enterprise class, N-tier multiple server scenario where you have a number of computers that are handling the initial load, the initial request, from the Internet, and these servers know how to serve up the HTML that's generated by your application. You may have another bank of computers that their only job is to serve up data to that top tier, that interface tier application. And these know how to enforce business rules that are unique to your particular company, and then finally, may have 1 or more computers that are responsible for serving up data to that middle tier. And so these might run a professional or enterprise version of SQL Server and do so on very expensive multi-processor computers complete with redundant drives clustered to provide failover services and so on. And while some of the concepts that we'll talk about in the next few lessons will help prepare you for that environment really that's a whole other set of concerns and services and tools that will be made available to you from some of the higher end versions of SQL Server. But you've got to start somewhere, and as you become more proficient with SQL Server 2005 Express Edition you'll find that transitioning into a better understanding of the more professional enterprise versions of SQL Server 2005 will become actually very easy for you. We just looked at where SQL Server fit into an implementation perspective, but where does SQL Server 2005 Express Edition fit in from a tools perspective? First of all, as you probably noted if you've already worked with Visual Basics, C# or ASP.NET tools, you found that SQL Server 2005 Express Edition is baked right into the tool set. You have basic creation and management capabilities directly from that Visual Studio IDE that's common across all the tools. And if you've worked with those tools for very long or watched the other videos that we created at LearnVisualStudio.net, you've noticed that there is a lot of built-in functionality between, for example, ASP.NET 2.0 and SQL Server 2005 Express Edition by merely dragging and dropping some of the representations of tables from the visual tools on to your designer surface that it creates a lot of code, a lot of links behind the scenes for you and enables a lot of functionality very quickly. And so that visual drag and drop paradigm allows for some very easy interaction between your applications and the underlying data store, in this case, SQL Server 2005. But then also as we're going to look at in several of the lessons in this series, there's an additional tool that is available for download, and that is SQL Server Management Studio Express, and it allows for some more advanced management and administrative functionality, and we will spend lesson 7 talking about the features that are included in SQL Server Management Studio Express, including where you can download it, how to install it, and so forth. Now, when you're building your applications using, for example, Visual Web Developer 2005 Express Edition, and you're doing what we talked about just a moment ago where you're dragging and dropping tables, for example, from the database view onto your designer surface there's actually some things going on behind the scenes in order to enable the functionality, that handshake between your application and the database. And as we noted when we looked at the implementation perspective of that single server scenario we talked about a subset of framework class library called ADO.NET that really facilitates the ability for your application to talk to the underlying data store. So think of that subset, the ADO.NET as the bridge between your application and the underlying database. And ADO.NET handles a lot of the heavy lifting for you. It handles, for example, the initial handshake with the database, making sure that it can find it in the physical location on your hard drive that it would expect to find it in and making sure that it can open up and that you have the correct permissions to interact with that database. And then taking care of some of the execution of commands like inserts, updates, deletes and so forth and then also the execution of queries to retrieve and filter through data and so forth insomuch that it allows you to interact with code objects in Visual Basic or C# that closely mirror the table structure, the underlying data objects, how your data is stored in the database. In fact, in some cases you don't even have to think about the data being stored as fields of information or even as a database, but they're strongly typed. You're able to interact with these entities, these ideas by their real names, allowing you to think about things in a much more object oriented sort of way. And that brings us to the end of the first lesson. Please understand if there were things here that didn't make sense, that's fine. We're going to cover all of the details about what a table is, what a column is, how do we create tables and columns within our databases, how do we create databases, how do we query data from those tables, and how do we insert data into them using visual tools and using the special programming language called SQL. All those topics we're going to cover here in the next few lessons, so stay tuned. If you enjoyed this video and the rest of this video series, I please encourage you to visit www.LearnVisualStudio.net for over 500 screen-cam videos just like this one on all topics related to .NET, including C#, Visual Basic, ASP.NET and more.

Video Details

Duration: 26 minutes and 15 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 5
Posted by: neudesicasp on Sep 26, 2013

This introduction covers the questions and terminology SQL Server beginners need to create and manage databases, such as: What is a SQL Server database? How is a SQL Server database different from an Excel spreadsheet? What do SQL Server databases do in my website or software applications? What's SQL Server Express' relation to Visual Studio Express? What do terms like "ADO.NET" mean?

Caption and Translate

    Sign In/Register for Dotsub to translate this video.