Video: How Do I: Create a Database?

Presenter:Beth Massi


SQL Server beginners will see how to create a database through Visual Studio using the Server Explorer. They will see how to define tables, columns, and relationships and how to add stored procedures.

Click here to go to original video page


Minute 0
I mean is that messy and welcome to the Windows forms of her data video series in this Thursday hour to walk to create a SQL Server database through Visual Studio s visual tools besides Visual Studio you ll need to install SQL Server is a free edition of Visual Basic and the free edition of SQL Server that you can install and use the database through Visual Studio going to use the server far you access the server Explorer window by selecting view from the Main menu and selecting server Explorer if you re using Visual Studio express editions this window is called the database Explorer okay with having a database if they were going to do is right click on data connections and choose create new SQL Server database enabled my SQL express database server is local SQL express in what he said
Minute 1
Going to be in order management system with customers products and orders so I m going to name the database OMS which stands for order management system sounds good okay enough of it is that defining our tables so that you were an ad is called a customer for an expanded database may click on tables and add new table is open up the table designer the first column and add a customer ID type integer and were not allowed also because ID is going to be a primary key is to end up a key is a value that can be used to identify unique when you re in your table and we have these kids who really knows across tables for so many years it is called an identity column in the column is used to automatically number of field based on the previous fields values
Minute 2
Existing rows like an auto increments so let s see what I scroll down here at the identity of specification and identity within a city gas to we had to do to indicate that this is the primary key of the table used to make the column and set primary key is to use the toolbar button that again removes the primary key in the next and add his last name and really make this a large 58 were not going to allow nulls so we think the column cannot be no basically just saying that it s a required field of those who reject updating or inserting this customer that is her last name column is specified so that at a few more fields here on a first name
Minute 3
I address you see him and he couldn t stay to make this a charge to are you really do this is because a polygamist at a store that letters state codes we know that there is a fixed to character and length as opposed to the other field and there was a large 50 by John 25 that included a variable length strings in this ZIP code that s what I make this a bar chart 10 are sealed and add to our table is called modified
Minute 4
Is to be a timestamp is a nice answer automatically generated unique binary numbers within your table on what is this is a mechanism for version stamping roads and a customer table with just eight bytes okay now and save our table the first thing they do is type name in the property sheet customer and able to split safe to say that we see a customer table shows up under the tables and here I ll are fields and enter some data into this table threat grows down the designer in the back of her to a customer may click and you see show table data so let s add a couple rows of data to our customer table specify the last name first name
Minute 5
Address city state zip code and villages that there are On the columns in this route and what it means is the data hasn t been committed to the database yet once he will not throw the database will check the constraints we set up on the fields so we specify that the last name is a required field size you can see moved off the route everything s fine suicide in example where we don t specify a last name field and move you ll see that the database reports an error cannot insert the value null into calm last name case let s fix this out throughout the realm and everything is fine but hey Cinelli have some data in a customer table the next it would add is the product table 3 to go right click on table of
Minute 6
And add a table at it was just a lookup table we use the values our first surfaced in a product that he could be an integer points if it s going to be a primary key or can also have it be an identity column and right click set the primary key I thought it has a name and a description and let us enter the name product in the property sheet and click save a lazy product shall appear in the list of tables so the next table organ at is the orders table survey to come and right click add new table
Minute 7
You name it orders you are not aiming at naming an order is because order is a key word in SQL Server sort of have an orders ID which is to be our primary key for him to make an identity field right click set the primary key field is in and be the customer ID is to be a relation to the customer table because the customer has many orders this is called a foreign key the way we set this up the right click select relationships select add and then we specify what the primary key table in his customer and the foreign key table is orders here and we select a customer
Minute 8
Shield soleus this relationship between customer and order while we re doing is setting up referential integrity and what this means is that the customer ID field here in the order table must have values that are inside the customer table customer ID so if we try to delete a customer that has orders in this case the database will not allow it we could also set up a relationship to cascade deletes so that when we delete a customer the orders are automatically deleted the idea here is that we re never going to have orders that net I do not have a customer so the database maintains the integrity of the data when we set up these relationships that s why it s very important to make sure you set up your referential integrity between your tables properly date another field in the order date will be a daytime field
Minute 9
And shifting his press aide now warns us that the following tables will be modified customer and orders this is because we set up the relationship click yes and Nelly got our order table in Alaska or can add to the system is order details and this is a child of order and added a table this time or get a call at order detail in a primary key order detail ID going to be an identity specification yes identity field in the market array click and set the primary key
Minute 10
Slight customer is a parenting orders orders as a parent of the order detail s organ at order ID here as a foreign key into this table so right click and select relationships in network and add the relationship between the orders table in the order detail table related on the order ID came close but also going to have a foreign key into the product table because within a select products as the line items is the order details of the order so again right click select relationships and add this target is like product product ID
Minute 11
Related on product ID in the order detail next Dilbert in hand is a quantity and wouldn t make this a required field the organ and make you have a default of one and a price you need in a Soviet money case now because they are table it again warns us about the changes are making to relationships in a stable yes and LTR order detail table here in the lists are database tables today now that we have our database tables defined and we could start develop application by what he wanted at a stored procedure toward a base this down here we can see we have stored procedures don t have anything defined here
Minute 12
What is the procedure anyways what s the procedure is similar to a method or function and Visual Basic users a way of executing code tea SQL code by name so instead of embedding SQL strings inside of our VB code we can call stored procedures instead of the building multiuser client applications are high throughput applications or maybe you require finer granularity of security on your data that s when you probably are going to use a stored procedure you also may want to just improve manageability across several applications that use the same queries like in an ice to store procedures can be a good idea at the time to sell to say that embedding CC could go directly in your VB code isn t it is wrong it s perfectly fine to do that especially if you are developing simple single user apps or you are using Visual Studio plans to manage that generated code but safe for this example will be using a multiuser or be creating a multiuser application and will have
Minute 13
Security requirements on NetWare connections will not be able to insert update or delete data directly by issuing those statements instead all we want to do is allow select and execute permissions set to create the stored procedures you may click here on stored procedures and then we select add new stored procedure so that is the editor window displays a template for us to create procedure or what already have some snippets to find sort of first create our delete procedure so this is called to lead customer and this procedures very simple all it s going to do in the past and the customer ID and it s just going to delete that customer row where that customer ID matches what we pass so we had to do now is press safe and noticed that it changes to alter procedure because if we were to change this procedure again and press say they would apply those changes into the database so it s managing that for us here
Minute 14
A7 next procedure in the act is our insert to go back to her server Explorer right click on stored procedure at new stored procedure this time I m going to use the snippet per insert that you notice is a little different where passing in all the parameters all of the columns and then we had to output parameters customer ID and modify as you recall customer IDs are primary key and the database automatically generates an identity as we set it up as an identity field and same with the timestamp value is generated on the row in our modified couple sodas procedure all it s doing is inserting the customer route for the values we passed if that did not produce an error in which his reselect in the ID in the modify the degenerate columns when we do the insert as where customer ID equals identity means that this identity is the identity that was assigned
Minute 15
This call that we were sure to get the identity that was me that was used when its insert statement executed so what I say here and you ll notice a change to alter procedure bouquets of the last procedure I won at is that it be the update to a comeback right click add in a stored procedure and now I m going to use the update but here the update is similar to insert except this time we don t need to get the customer ID back and we just need to return to modify column back to that the only thing that s changing only do the update statement tonight 11 update and in the where clause on the update were making sure that we re updating the current customer that we passed in and were checking at the modified value hasn t changed now if the modified value has changed that means another user edited the record while we weren t looking and we don t want to allow that for this application
Minute 16
Because there could be some data that is relied on each other and the user will not have seen the changes made to the record while they were editing it sort safer just to say that we couldn t update this and let the user reselect the row and try again so I m doing here is if there was an air without a raise an error concurrency violation on table customer with a select save this and now he got all of our delete insert and update for our customer said how would we use video net to send updates to beats and inserts we can use the stored procedures instead of inventing the SQL code directly into our BB code secret had stored procedures to insert update and delete the rest of the tables in the database using this exact same pattern a gay Allergan creating our order management system database we created all the tables and fields set the relations between the state
Minute 17
Post added to stored procedures the next video will talk about how to create your application and connect to this database like to think very much for your time if you want you can check out the Visual Basic developer Center for any updates we might have these videos and authors can check out the VB team blog in my blog base again