Video: Understanding Database Tables and Records

Presenter:Bob Tabor (from LearnVisualStudio.NET)

SQL Server beginners will learn about tables and definitions of data types, properties, keys, etc. in this second video. Find out how table rows, columns, and fields interrelate and whether columns can be empty.


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
In this video lesson were to talk about some of the building blocks of relational databases mainly tables columns and records will also demonstrate how to create a database using the other express edition tools like visual Web developer 2005 express edition and show how to define a table and then will add and modify data to our new table for Lessig in this lesson talking about what a table is conceptually and we note in the previous lesson that databases are not magic is simply our computer files that are optimized to store data in a structured way and the way that databases are organized and structured is through the use of tables 1 database can contain one or more tables it might help to think about a database at first like an Excel spreadsheet in so much of the spreadsheet may contain one or more worksheets and each worksheet then has one or more columns
Minute 1
And one or more rows of the similarities really and they are so make the mistake of overstating this analogy for table is defined when you define one or more columns and then give those collected columns and name a table name each instance of a set of columns as defined in the table is referred to as a record or as a row so tables are made up of columns each table has one or more columns and each column really has three distinct pieces of information find about it first of all the column has a name it also defines what the type of data will be that we stored in that column so for example we have to define if we expect character data stream data to be saved in a given column or in numbers or dates for or currency and so on and to
Minute 2
Rope from the previous lesson usually two purposes for this first of all it enables data integrity and we ll hear a lot about that proper manner of our lessons but as we said the previous lesson by virtue of the fact that any given column can only store one particular type of data means that the data that actually does a get to be stored in that particular field of information for a given row will be reliable and will be of the correct data type that will limit eight all the other possibilities and then the second purpose of this serves is to allocate the proper amount of space in the physical file off for that particular piece of information so numerical data dates and currency different types of us dream characters will be different sizes and so we need to allocate the proper amount of space within the file system for a fact if
Minute 3
Third bit of data to be stored so that it can be stored in a very optimized sort of way for retrieval and in the third thing that defines a column is a special properties and there s a number of different special properties will talk about a few of them in this lesson and a couple others as we continue on with any things like whether or not the column will automatically be numbered as new rows are added to to the particular table or can this particular column be left empty is this a key fields in relating tables other tables and others actually a few more are properties that we can set as well but it s the moral the story is that a table is made up of columns and columns are composed of a name a datatype and any other special properties that makes this unique and a well suited
Minute 4
Board purpose so once you to find all the columns for particular table then you can begin to add information to that table each a new instance of the columns that collectively comprise the table is referred to as a roll or record or as I ve noted here it might be referred to as a couple in some situations so conceptually what our rows will the thing of the spreadsheet again to go back to the analogy to use a few moments ago you may have at the very top in the first row defined in column a and for example in an Excel spreadsheet on a symptom of an order ID in them and in column be first name in column sees last name and so on in each individual row after the first row would be an instance of that
Minute 5
Next row you have an IDE hour order ID to and first names Stephen Lessing Twersky and so on each row is another instance I guess you could say all of of order ID first name last name so one now for those of you who have worked through some of the object oriented terminology you can think of this in terms of sort of a class of an object winter stand that a class is merely a definition is a blueprint and that you can create new instances of the class which are called objects you can have many objects that are created from one instance as defined in your source code similarly you might have many rows that are all instances all of your your table definition of all the columns that define your table so let s add to a lift to the terminology will been talk about fields and if you can see here in the
Minute 6
Part of the slide the field of data is a single value in a row you can kind of think of it again to borrow from the analogy the spreadsheet is the intersection between a row and column of the use of the term feels kind of tricky because in different texts in different websites he referred to a field can sometimes be referred to the column but that s not how we use it in these videos you typically will see this referred to as one bit of data one at one intersection between a relevant column OKs list of quick review just because the terminology is so important and were referring in and getting started with databases first of all a table is a collection of columns once you ve defined your columns than it is a collection of data that are all instances of those columns you define the
Minute 7
Owns to hold certain bits of information each row of data is an instance of all the collection of columns that are defined within a table and finally a field of information is the intersection of the road a column or in other words just one piece of information that comprises a complete row of data Sonata printed conceptual understanding what database is what tables are held to comprise a columns and rows and so forth let s go ahead and put that knowledge to practical use you see that we will no visual Web developer 2005 express edition in our intent here is to take a cool sample project called SQL lessons or two and added database to it so I wanted due to begin with is go to my toolbar and select add new item and they ll pop open the add new item dialog box all want to select
Minute 8
Is from the templates and then change the name from database of India to something like my sample you name it anything you want to enter click the add button doesn t pop open a little message box letting us know that there s a special folder within ASP net general applications that dumb you should generally try to utilize whatever you re building what applications with visual Web developer you want to put your new database in this folder and we re going to go ahead and respond yes to that question and you ll see that the database Explorer now pops open the switch back to the solution Explorer and we can see that we have an app data folder with now are new my sample MDF file and supporting file my sample_log LVF will toggle log files in more depth in lesson number seven was a war about those for right now but notice was we created our new database that is popped open the database Explorer and
Minute 9
Can see here that we have a little data icon with little ex threw it this simply means that we haven t yet connected we haven t a basically a hash handshake with the database having begun speaking with the database we can simply do that by selecting refresh and now we can look at all the tables all the diagrams of abuse in all the major objects that are stored in the database d this point we ve only talk about tables and grew to focus mainly on tables for the remainder of this lesson series on a right click tables and select add in the table because currently our database is empty element main area of visual Web developer 2005 express edition you ll see that we have a column in an area where we can begin to define the columns to dedicate the datatypes in some special properties about
Minute 10
On this particular example on the crate a simple customer database customer table and we give the first column in the name customer ID and this will be a column that has a unique number associate with each row so that we can easily identify each column so that we have two John Smith s for example will be able to identify John Smith number two as opposed to John Smith number 47 at lease in the context of our database we also been will have to define a datatype typically customer IDs are of type integer stillness scroll up and and then the next piece of information want to fill out is whether or not this field should allow null values should it will ever be empty unless they know it should never be empty song would you select the check box for that particular column now this point I can I have some options I can set some of their column properties which we
Minute 11
A wanted you in this particular case or since I do find enough information I can save table number one currently tabled one being the default name since we haven t given it a name yet by selecting the save button before I save it however I did wanted to find some affirmation about it because seasons were still selected in our little gridlike area cut customer ID that we can see some of the properties that were able to define here we boarded a fun whether or not to allow nulls are empty fields we were given a name we ve already defined as datatype or some other information that we can fill in as well now one of the things I will talk about a few moments as an identity specification will comes after that at that time they say they will want to do is collect a first name in this particular case will want to store character values wheel wants to a love is to be variable archon and expanding size field saw
Minute 12
Select far car 50 the char means characters to 50 means the maximum length of this will be allowed to be in the far means that it can expand its variable if your first lead name is only two characters long that will only create enough space to store just to characters if however your name is 49 characters long that it will expand out all the way to to meet that need but this is the one way that you can save a littlein your database by allowing a far charge will talk about the available data types in just a few moments should we allow nulls is still probably thought was going to fund the next field last name toolmakers of our car 50 as well I get too concerned about what these datatypes are will talk will go to the minute but was cut talk about lets create another type of loan column lets say
Minute 13
Us know things of that nature and you want maybe we ll want to change from our charter 50 and we know that we want to store a lot more dead in this feels only change the link to potentially 200 characters hopefully nobody s addresses lower than 200 characters if we were to try to store data that was longer than 200 characters in length and it would be a cut off on the very end this is sometimes referred to as truncation no ideally I create so one for city state and zip codes exist in nature list is a little bit different into a customer since field and will want to store how long this customer has been a has been in our database so well wanted to his selects a daytime field solicit ahead and select daytime is her choice there will allow this to be no and address of the novel
Minute 14
And then finally we may want some information like the number of orders so here m s score of a numeric values will we don t need is to store up a lot of numeric values is probably no chance at any given customers to store you know hundreds of thousands worth a or purchase hundreds of thousands of orders from us so we may want to make that a smaller value in our database something like maybe a tiny indoor small and we ll talk about we also may want to store the amount of money that has been spent with our store by this particular customer and so we may want to store some data like that and will make that of type money now in a select see table 1 and only give a name to our table and recalled his customers and click okay once I stated I can
Minute 15
Look over my database Explorer and see that I have a new table call customers I can even a drill down into customers and see all of the columns that I ve defined while I have the the definition of you open I can change information about my database or rather my table I couldn t delete columns for example saw the selected given column and I m going to select and delete column and you can see by selecting in right clicking to get this context menu I do other things like insert columns in certain spaces and also of make some changes to some of the properties which we ll talk about later now if I were to do that and I had data that populated his table and I selected I wanted to leave the column it would delete a lot of data as well so that could be very dangerous operation nonetheless you have that ability to redefine the
Minute 16
Pressure the table on the fly in some cases the database will allow you to do that in other cases doing this would be so destructive that it would violate data integrity or rather the special rules that were set up to prevent you from deleting important information out of the database now that I made that change only to begin select save customers from our toolbar notice we do have that one of our columns I disappeared so now I have a table to find how do I add and remove data from the stable school enclosed this from the main area will enclose that as well as I wanted to open up his table and start inserting data into a list selects show table data by right clicking on the table selecting from the context menu show table data in the main area will see some new looks for them much like again in an Excel spreadsheet this time were
Minute 17
Be allowed to see all the columns in the very top row and you ll see this kind of great out of what is known we can t edit those per se but in this next area the row that has a little star next to it these are values that we can modify bustled putting our cursory and an beginning to type for example I can put the number three for customer IT and I can use as a first name Bob and use the tags you my keyboard to tab over to the next fill the Reformation and so on those each time I do that a little a little of a exclamation mark icon pops open it simply says that the cell is changeless again the change hasn t been committed to the database original debt is known as is let us know that the changes that we made so far this first row data have not been saved to the database if we were to shut it down at this point by selecting the close buttons you either from this view or the entire application that
Minute 18
Data would not be saved in your database table but as we go along and start making changes here see and we had our tab button on her keyboard one last time noticed that were given a little bit of an error that there must have the problem that we created in the reasonable in Laos to save is that the data was an incorrect format was going to click the OK button to review the data that we put in now the way that I typed in the data obviously he will was not in the correct format I honestly missed a six in there somewhere so that stopped me from entering the data here is where data integrity comes and we can t enter data from the year 200 we had to enter data into the year 2006 now we ve got that change were to go ahead and tell her keyboard
Minute 19
Items and this time we successfully save the row data into our database table and as you can see now that we ve moved on to a new row listed underneath our original now just to prove that this actually did say that our tables were to close down this view and go back to customers and select show table data once again and when it opens back up will see our row of data customer ID3 is indeed still in our database table so we have successfully added a new a row of data to the table that we defined and we can continue to do this with an other relatives do that for fun because Rimini makes and changes your own
Minute 20
In token of snow we have two rows of data in our database lets say for example that we from this view wanted to change some information about a customer for example we want to go back to a customer since the old and noticed as using the little icon here I can resize the field to display all the data or to cover some of the desktop I can also move to the beginning of the field me in the field using the home in the end buttons on the keyboard or the arrow keys arrow back and forth between each individual characters but what if I want to change the customer since and make it from March 1 2006 was the first time that this customer order from me unnoticed when it attacked him I keyboard that I once again give a little icon the cells changed in the changes not been propagated at this point in time until we move our cursor off of this row we also see they were in edit mode because of little pencil necks
Minute 21
Row that were currently editing however far were to continue to hit attack him I keyboard notice that now that changes been propagated back in our database table that we successfully save that change so here were able to edit and update a given row of data simply by navigating similarly to how we would do an Excel spreadsheet we can delete an entire row data by selecting it with our mouse cursor on a given row and then we can use the delete key on her keyboard to delete it and it says you re about to delete one row click yes to permanently delete these rows you won t be able on to change its sorting click yes to that and now its gone customer ID number three Bob Tabor will no longer be in our database and must manually type in again a peso to briefly review will rebel accomplish and maybe expound on a little bit
Minute 22
Been a new database create a table with columns and then we were able to insert new records are rows of data into that table retrieve the data back out through the table data view will topple that a moment where we ll update the data and delete the values in the database and the way that we did dad is the first method as prescribed here using the tablet table data view within our on visual Web developer 2005 express edition toolset however there s other ways to interact with our data the other way would be through an ad hoc SQL statement and really demonstrate how to do that in less number five and then also by building applications like we demonstrate in the first lesson and how will I will expound on that in lesson number eight using Io net now you often will see terminology that the term crowd used in association with
Minute 23
We just did in regards to rows of data records of data and crowd stands for create read update delete all the major operations that you can perform on data within your cables so sometimes when he saw what what operations are talking about your basic fundamental means that you do with the data you create new rows you can retrieve those rows and look at them and you can then update those rows and new data and then delete those rows from your target table okay so is that includes video we did talk about how to create a database how to on a crake tables and how do you create columns for tables and in how to perform the basic crud operations as we just learned about about one thing we didn t talk about and will be covered in the next video at length is some of the data that the properties of the columns on
Minute 24
Click our table name and go into open to the definition we largely ignored the column properties that were at the very bottom here we didn t make any significant changes to them other than the noble the datatype and things of that nature in the next video will talk more about of the other properties of columns as well as the datatypes themselves and the other constraints that we can put it on our of fields in order to restrict and sell them improve the data integrity of the data that gets inserted in updated in our database anything joy this video in this series as it s moving along please visit www two net for over 500 screen cam tutorials just like this one on all topics related to net including C sharp Visual Basic ASP net and more thinking