The first thing to understand is that SSE is a Windows service running in the background rather than an application. This means that it's there, waiting for work to do. That's why it can be called up from a menu selection as though it was part of VWD. You can also install multiple instances of SSE.
Click Here to display the illustration
Click the Back button on your browser to return
The default instance is named SQLEXPRESS and you refer to it using both the name of the instance and the computer name:
<computer name>\<instance name>
For example, MYCOMPUTER\SQLEXPRESS. You can substitute a dot (".") for the computer name if you want to refer to the local machine. This is the way we do it in the example below. One of the main places that you will refer to the database is in the connection string used to connect with the database. Just as an example, that will look something like this:
Before going further, just a word about SQL, the Structured Query Language. It would be nice if there was this totally standard language that you could use with any database at any time. Unfortunately, the world's not nice like that. Different vendors have slightly different versions of SQL. The version that we use with SSE is called Transact-SQL (T-SQL). There's isn't a big difference between T-SQL and other versions, but it's not exactly the same as the version of SQL that might be used by a different vendor.
An example of a T-SQL (and also just plain SQL) command would be:
SELECT EmployeeName FROM Employee WHERE SSN=123456789
We'll discuss coding T-SQL a bit more later. First, let's discuss what a database is.
When you "design a database" it means that you decide what the tables are, what the columns are in those tables, and how they relate to each other. You might also see the word "normalize." That's a process of optomizing that design so the database is as fast and efficient as possible. Database design is a big subject and it's way out of the scope of this tutorial so we won't go into it here.
One element in the SQL statement above is the table name: Employee. A database is made of tables that all relate to each other in some way (even if that 'relationship' is 'none' -- just being in the same database is a relationship of sorts). EmployeeName and SSN are columns in the table, sometimes called attributes. SELECT, FROM, and WHERE are T-SQL keywords.
Because database programming is a complex subject (and this series isn't primarily about databases), the database we will create and use will be about as simple as it can get. But to give you a head start in more complex databases, here's a whirlwind description of them.
The word relational means that the tables are related to each other using key values. One classic example that you see in a lot of training is a "company" that has many "employees".
If the "company" is just a branch of many (like, for example, Starbucks) then each one will probably have a unique CompanyID code. That can be used as the "key" for the "company" table. The "company" table will also have a lot of data fields (usually called "columns") like Name, PrincipalAddress, CentralPhoneNumber and so forth. The "employee" table might have an "EmployeeID" key and columns like EmployeeName, JobTitle, and so forth. The relationship between the "company" table and the "employee" table will be one-to-many. That is, one company will have many employees. And the "employee" table will have one field, called a foreign key, that is used to establish that relationship. In the "employee" table, a field, usually named the same as the one in the "company" table and in this case CompanyID, will be the same as the key in one row of the "company" table. This establishes the relationship and tells a program which company the employee works for.
But, again, since our goal is to demonstrate a "from the ground up" use of databases, the one we will create will only have one table and two columns (one for the key and another for data).
What is this CRUD?
You might see the acronym CRUD as you learn about databases. It refers to the four operations that can be done on databases: Create, Retrieve, Update and Delete. And you might also see DDL and DML. These acronyms refer to Data Definition Language and Data Manipulation Language. DDL is used to create the actual databases and tables themselves. DML is used to read and update the data in them. The Select statement shown earlier is DML.
Since there are other ways to create the databases and tables -- SSE includes tools for the things that you will be interested in doing -- and also since it tends to be a job turned over to specialists, especially in big shops, you might never actually use DDL, but your programs will be filled with DML. DML consists of the four operations, SELECT, INSERT, UPDATE and DELETE.
There is one other type of T-SQL called DCL or Data Control Language. This is almost always done by administrators rather than programmers since it's used primarily for security.