Video: What is a database?

Presenter:Bob Tabor (from LearnVisualStudio.NET)

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?


Click here to go to original video page

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
Is a part video lesson series title SQL Server 2005 express edition for beginners writings bought a brown would learn Visual Studio net and of course the next eight lessons will start the absolute basics and a grass to point him you can build data disturbing websites and applications on your and while this series requires no priests databases probably should have some experience with programming one which like Visual Basic or seashore and also most examples utilize visual Web developer 2005 express edition to create ASP net two websites has already placed at the time to download and watch those videos also available on Microsoft s website to start off with the basic somewhere began with a quick example and whether you realize it or not almost every webpage of any significance on the Internet today is database driven in one name identity
Minute 1
Is driven I simply mean that many websites retrieve data from a database and merger with an HTML template to produce an HTML page that s consumable by your web browser or the data that is collected from all webpages forms are saved to a database of resent what critical tissues example that really go through later in this video and the other videos in the series that demonstrate how an ASP net webpage interacts with the SQL Server 2005 express edition database so as to be of the CD ROMs can take fictitious e mail address and then click the submit button and you can see that I did a page Bacchus is thanks to be receiving your next newsletter shortly know what happened between the point we click the submit button and to the point we got this confirmation page while what s happening behind the scenes is that that data is being sent across the Internet
Minute 2
And the end of 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 over the also show how using visual Web developer 2005 express edition rebel look quickly create a little Adnan page that allows us to retrieve data from a webpage and allows us to interact with it in some meaningful way so in this particular case you concede that we are able to change the e mail address you 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 were identified that this person came from particular address to try to prevent fraud we can also office user up by selecting this a little check box and clicking update can cancel that operation rename and delete this record by clicking on the delete link but could you believe that only crave it relatively less than two minutes
Minute 3
Create this administration application using the tools available in visual Web developer 2005 exhibition is take a few moments to briefly talk about what to cover the next eight lessons and then move on and start discovering what databases are for the remainder of this lesson so was firstly really talk conceptually what a database is and why you should prefer to use it rather than other options like text files or cheats and so on when he builder applications and lessons to them through a talk about the major database objects that are used within relational database management system and there wasn t forwarding to talk about how to create tables in a relational sort way we ll talk about relational database theory and its 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 relation
Minute 4
Will style of database creation and lessons five and six were going to thoroughly explore the structured query language for SQL as it s known sometimes called SQL and regarding user to retrieve data from our database will learn how to sort that data how to group it and aggregated is also insert data in your database how to update it a deleted and so on using nothing more than the special programming language called SQL or structured query language in less than seven really thought about security and network connectivity there are some unique features of the express edition and SQL Server 2005 are going to tell you of how that makes writing secure applications easier using the express edition tools will 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
Minute 5
Start talking about is actually what a database really is and there s nothing magical about a database is simply just one or more files at work together to store data in a very structured sort way or 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 data accurate will talk more about this constant data integrity throughout all the lessons now some databases are simply files and then all the 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 all multiple applications and users concurrently but also to up a
Minute 6
Out for her deceased management and maintenance of that data in that tends to be one of the biggest focuses of working with databases is how we manage it how we maintain it so that it s its 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 applications will build a more specific because at a very high level that s what it does but there s 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 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 will talk about all these things in the upcoming less
Minute 7
But just keep in mind the SQL Server 2005 express edition is a complete toolset and not just the files themselves that store the data and also the management tools in the server tools that kind of 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 in my guessing yourself while this complexity why couldn t the store data in flat text files or even potential is a store them like you ve been doing in your small organization using spreadsheets were the case will be and so certainly there is a layer of complexity has added to creating applications by using the on database like SQL Server however it actually makes things a lot easier when you really sit down and think about some of the weaknesses
Minute 8
Spreadsheets to manage your data for example if you were to have a spreadsheet or even a text file of data that contains for example on 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 each will record all of the individuals purchases on a separate a row of a spreadsheet you ll see that first of all there s a lot of redundant information the address information city state information for example much be redundant across all multiple rows no bats could be a problem especially in light of the fact that if they purchase three months from now some of their information may change they may have moved there they may no longer work for the company that they once did and so
Minute 9
Over time some of the data you collect about this individual might become outdated you might have a hard time are in a figuring out which of these particular rows is the most recent row that describes accurately describes this particular individual in my spreadsheet not only that but with all that redundant data being saved each time 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 course of a number of years certainly their knees be a better way more efficient way to manage that data and although for example Microsoft Excel is very adept at allowing you to a filter in search through the data in your spreadsheet some more complex searches might not be enabled due to a way that the data structure Solis reviews this Paris language and less
Minute 10
And so five and six and think you ll Driesell reopens all the possibilities all flexibility and it was not available to you when you re using Excel spreadsheets to manager data and other things like protecting data from malicious and on authorized uses during way to do that with a text file as fully not with a spreadsheets and other easily hobbled protecting the data from becoming corrupt well there again if the file becomes corrupt your really auto block butts on as you probably can tell as I m leading you into this discussion of a relational database management system which often see sure to RDBMS will take care of many of these issues for you in SQL Server 2005 express edition is certainly no exception that takes care a lot of the heavy lifting of managing your data so he asked the result of an overhead compared to the simplicity of text files and spreadsheets however the flexibility and
Minute 11
The security of using RDBMS system will really outweigh the the disadvantages of having to learn a whole new syntax only way of thinking and so on so often not use the term relational several times in reference to databases was any know what all relational databases will relational is a type of database not all databases are relational but is probably the most popular type of database that used commercially today relational databases were conceived out back in the 1970s by an IBM researcher who s been caught as the father of relational database theory and they were popularized in the 19 EDT as some of his theories begin to find their way into commercial tools because wanted us all to basic problems that a exist with databases up to that point in time were all the data was handled by each huge mainframe computers and the store
Minute 12
Bid and very expensive so personally wanted to reduce the footprint of the data and therefore reducing the amount of space on those big reel to reel tapes that each database record required and so hopefully we ll reduce how expensive data was to store and he also wanted to enforce data integrity of data integrity takes several forms but were basically referring to the accuracy and the correctness all the data that were storing a to accomplish this cost suggested that if you break a one long row of data fields into several smaller records each record being saved into a table that describes a specific subset of the data is collected you can improve the data in the integrity of the data first off he is suggested that you have defined what type of data you want to store and that would prevent scenarios were he had like storing or character data in a
Minute 13
Build that should only be storing numeric values sold by enforcing strong data types it makes the data instantly more correct and therefore most maintain the integrity of the data but then also by storing a data entity in just one place in one record you can eliminate the possibility of having multiple versions of the same data in your database therefore improving the integrity of the data are reducing the possibility that one of the versions of the data is incorrect also the benefit of breaking up the data in the smaller related tables of data is they who rely on the software that manages the interaction with the data to protect it from deletions away and deleting a record would mean that it broken important relationship that had with other data that was related to so you ensure data integrity by disallowing any potentially harmful to link the data in the database and probably most importantly it was bad by splitting up the
Minute 14
And the smaller chunks you could retrieve data much more quickly than in the past so SQL Server 2005 express edition is a relational database mean that you get the advantages of relational databases and more a leading to safer cleaner data that s stored by your application now some of it to make sense don t worry in the next some lessons were in a 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 a relational data there is also certain services that we 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 systems so that software will help support our efforts in a number of ways for example
Minute 15
Permissions on a user by user basis so the top of the 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 ll be there whenever you actually needed also the ability to replicate the data across a number of fellow of servers so that if one server goes down that there can be some clustering some failover or and more importantly that you can allow multiple applications to have their own copy of the data in the end it all stays updated also some basic ad hoc reporting capabilities the ability to write transact SQL the variance of a load of SQL that SQL Server allows you to use and be able to retrieve data without having to own create special applications just pull certain piece of a
Minute 16
Nation down so the ability to write and retrieve write SQL statements and retrieve data in 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 then it s it s it s serving the data doling it out as requested so smooth on a more practical topic and that is where databases fit into the overall architecture of replication in your first inning started only create pro civil applications be deployed to a single machine on you that SQL Server 2005 express edition surly supports this you can deploy a the actual database files to a folder of your ASP net 2 0 application and database but what is actually going on behind the scenes to facilitate the access of that data so for example the very top
Minute 17
Is requested to some attacks in Asia WW something com and it can be your petit a particular Web server if your Web server is running Windows 2000 and Windows 2003 or some along those lines that Internet information services going to a pet to retrieve that request and determine what to do with it now if the request has on a certain file extension like aspx and it knows that it is going to depend on the ASP net runtime engine to interpret and handle this request upon receiving this is redundant runtime engine will basically load up your webpage and will execute execute the commands that are stored within the assembly that s generated whenever your webpages run for the first time that assembly will rely on class
Minute 18
Is that are within the framework class Library the STL and which in turn go to a special subset of classes called PDO net which will talk about in future lessons and 80 oh net is responsible for communicating with your database whether to save information and retrieve information obsolete or update or where the case might be hands if four example information should be returned back to your application then that will be integrated in through ASP net about replacing the programmatic fields of information with HTML returning a Jack the Ripper Internet information services and back out to the original request for all of the information now just understand that I m really oversimplifying this just so you can kind of understand the flow of information that comes into brutal webpage requests and gets all we do
Minute 19
And to the point worth collecting data from SQL Server and then retrieving data integrating it back in the webpage and spitting it out is a lot more to it than that but from an overall perspective I think that s a pretty high level overview of what s going on behind the scenes honestly SQL Server 2005 express edition was created with a single server or 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 more enterprise class and here are multiple server scenario where you have a number of computers that are handling the initial load the initial request from the internets and these servers know how to serve up the HTML that s generated by your applications you may have another bank of computers that are only job is to to serve
Minute 20
Beatitude adds to that top tier that interface to your application and these know how it would force business rules that are unique to your particular company and then finally may have one or more computers ever responsible for serving up data to that middle tier and so these might run a a professional or enterprise version of SQL Server and to do so on the very expensive multiprocessor computer simply with the redundant drives a cluster to provide failover services and so on and while some icons of civil talk about in the next few lessons will call prepare you for that inform and really that s a whole other set of concerns and services and tools that
Minute 21
Will be made available to you from some higher end versions SQL Server but you 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 a professional enterprise versions of SQL Server 2005 will become actually very easy for you so we just looked at where SQL Server fit into an implementation perspective but whereas SQL Server 2005 express edition fit in from a tools perspective will first of all you probably noted if you ve already worked with the Visual Basic see sharper is the net tools you found that SQL Server 2005 express edition is based right into the toolset you have basic creation and management capabilities directly from that Visual Studio IDE is common across all the tools
Minute 22
Very long or watch the other videos that we created a learn Visual Studio net you ve noticed that there s a lot of built in functionality between for example ASP net 2 0 and SQL Server 2005 express edition by this merely dragging and dropping some represents representations of tables from the visual tools on your designer surface that it creates a lot of code while 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 in the underlying data store in this case SQL Server 2005 but that also adds real lookouts in several lessons in the series there is an additional full that is available for download and that is SQL Server management studio express and it allows for some more advanced management and administrative
Minute 23
Banality and we will spend of video number seven lesson number seven talking about the features that are included in SQL Server manages to express including know where you can download it how to install and so forth now you re building your applications using for simple visual Web developer 2005 express edition in your doing what we talk about just a moment ago order dragging and dropping tables for a sample from and from the database view onto your designer surface there s ashes and things going on behind the scenes in order to enable the functionality that handshake between your application and the database and has been noted when you look at the implementation perspective of a single server scenario we talked about a subset of framework class Library called ATO net that really facilitates the ability for your application to talk to the underlying data store so figure
Minute 24
At thatsubset eightyoh net as the ridge between your application and the underlying database and a whodunit handles a lot of the heavy lifting for you it handles for sample the initial handshake with the database making sure they can find it out in the physical location on your hard drive that would expect to find it and making sure that it can open up and that you have the correct permissions to to interact with that database and then taking care of some of the the execution of of commands like inserts updates deletes and so forth and also the execution of queries to retrieve and filter through data and so forth and then it also provides some additional utility insomuch that allows you to interact with with code objects in Visual Basic or see shark that closely mirrors the table structure the underlying data objects how your data stored in the database
Minute 25
Effect in some cases you don t have to think about the data being stored as fields of information or even as a database but the strongly typed jury will interact with with the use of these entities these ideas by their real names allowing you to think about things and much more object oriented sort of way and that brings us to be in a first lesson please understand if there were things here that didn t make sense that s fine were to cover all of the details about what a table is what what column is how we create tables and columns within our database is how we create databases how do we query data from those tables and how do we insert data into them using visual tools and using this special programming wings called SQL all those topics were to convert here in the next few lessons so stay tuned if you enjoy this video and the rest of his
Minute 26
New series please encourage you to visit www two net for over 500 screen can be used as like this one on all topics related to net and clean seashore Visual Basic ASP net more thank you