This is a guide on databases and procedures.

Check out Audible on Amazon and listen to the newest books!

A database is a structured collection of data that is organized to facilitate storage, retrieval, and manipulation of information. The table structure of any database consists of columns, rows, and values, and this fundamental structure applies to virtually every database management system, whether it is relational or not.

Databases rely on tables to store data, and this table-based approach is the essence of data storage in any kind of database system. Every table has this structure: columns, also known as fields; rows, also known as records; and values, which are also referred to as cells.

These components represent the actual data itself. Columns are arranged in a vertical format and provide a description of any one single component of the table. For example, if a table were storing information about people, one might see columns for first name, last name, street address, city, state, and so on.

Each individual field represents one of those collections of information. For example, first name would be an example of a column, and what would be seen in that column would be nothing but first names, each name corresponding to a particular person. In essence, it is the collection of fields that make up what is referred to as a row. So if first name, last name, street, city, state, and so on are put together for one person, that generates a single row on the table or a single record. Rows are arranged horizontally.

The value or cell is simply the intersection of any given field or column and its associated row or record. Again, if that cell were last name, something like Smith might be seen in that value or cell. It is worth noting that there are several cells that have been left empty. While this is a bit overly simplistic, it is indicative of the fact that not every cell has to have a value. In some cases, it is perfectly acceptable to leave the value blank, and that is what is referred to as a null value.

RDBMS Fundamentals

In a relational database management system, one can define an additional column known as the primary key that would uniquely identify the record as a whole. This can be something as simple as an ID, some kind of number, for example, which never duplicates, to make sure that the collection of values as a whole remains unique.

With the primary key, there can be only one per table. Since it is the job of the field to uniquely identify each record, only one primary key can exist. It also does not allow null values, so on any column where a primary key is defined, no null values are allowed.

However, in some cases, a primary key can include more than one column. In other words, to maintain uniqueness, it might take more than the ID field. Perhaps an ID and some kind of description are needed, for example, and the two of them together generate the unique value, which is referred to as a composite key. A foreign key is a primary key value but from a different table. If, for example, there is a customer table, then the customer ID might uniquely identify the customer. But then that customer might generate an order.

The order would be in a separate table, but the customer ID can be used to identify who owns this particular record. This is what is referred to as referential integrity. The primary-foreign key relationships are what maintain that integrity and make sure that an order cannot be created for a customer who does not exist. That is what is meant by referential integrity. Foreign keys absolutely allow duplicates because one customer might make several hundreds of purchases or several orders.

So absolutely, the same value can continue to be added over and over again for every order or purchase that customer makes. A unique key is only to make sure that there are unique values in one particular column. So if there were something like a credit card number, for example, that should not really ever duplicate, a unique key could be placed on that field, and it would make sure that there are no duplicates in that particular column.

A unique key does allow a single null value because that is not duplicated. One can have one left empty, but no other ones can be left empty because that would be considered to be a duplicate. There can be as many unique columns per table as desired. It really depends on the structure of the table.

Finally, with relationship types, one-to-many means that there is a single record in one table that has many related records in another table. Using the customer example, there might be a single customer who has generated many purchases.

That is a classic example of a one-to-many relationship, and it is, in fact, the most common database relationship type. It is that primary-foreign key relationship that maintains the integrity of those records. Customer 1 generated order number 1, customer 1 also generated order number 2, and so on.

There could be a one-to-one relationship type, and this is perfectly acceptable too, wherein there is only a single record in one table and it only has a single related record in another table. It is not as common, but it is perfectly acceptable, and it is also maintained by the primary and foreign key relationship. A many-to-many relationship typically indicates a problem with the design of the entities. It is possible to maintain many-to-many relationships, but it is difficult, and it makes data retrieval a little bit more complicated.

Generally, what it ends up resulting in is the fact that there could be many records in one table that have many matching records in another table, and it makes it difficult to know what records are, in fact, related to each other. Quite often, what will be seen here is a resolution using what is known as an intersection table, which sits in between the two conflicting tables, if you will, to try to resolve the problem.

Entity Relationship Diagrams

The first thing that will be noticed when seeing an ERD for the first time is that it is very similar in appearance to flow charts. ERDs, in fact, do use similar shapes and line connectors. But ERDs are not flow charts. Flow charts map out single steps in a process, while ERDs visualize the way information is broken out in databases.

They use standardized shapes to describe entities, relationships, and attributes. ERDs actually demonstrate the relationship between data and databases, and that is important in planning or structuring databases. In ERDs, reference is made to entities, and entities refers to data categories that are found in databases. For example, an entity could be a person's first name or last name, street address, city, state, or country.

It could be any one of those. Entities must have unique attributes in ERDs, the same way data in a database has to have unique attributes. For example, if there are two categories in a database, say, home address and business address, they do not share the same attributes. Even if it was a home-based business and shares the same address as the home address, the business address entry in a database is going to have separate and unique fields from the home address entry because not everybody has a business whose address is the same as their home address, and that data simply cannot be shared.

When searching out examples of entity relationship diagrams, a lot of different-looking examples will probably be found. ERDs sort of have a standard notation, but they vary in the types of connectors they use and other methods because ERD really is not a standard in the sense of an ECMAScript or HTML. Different people, over the years, modified it for their own purposes and several alternatives stuck. There are seven types of shapes on the screen, namely, rectangle, double-lined rectangle, diamond, double-lined diamond, oval, double-lined oval, and dotted oval.

Entity relationship diagrams use shapes to describe the relationship between data and attributes, and there are three basic shapes: the rectangle, which always represents an entity; the diamond, which always represents relationships; and ovals, which represent attributes.

Entities and attributes can have variations. In the weak entity example, where the rectangle has double lines, weak entity refers to entities that cannot be identified by their own attributes and must be associated with another entity. This might be thought of as a parent-child relationship.

In other words, weak entities do not have a primary key and require a foreign key in order for the association to be made. A shape for associative entities may also be come across. It is not demonstrated here, but associative entities are represented by a rectangle with a diamond inside it, and they have an association with one or many types of entities. In the attributes column, the top one, the oval with a single outline, references key attributes that uniquely identify an entity.

The attribute customer ID, for example, could be the key attribute for a customer record. Multivalued attributes have a double outline and could have multiple values. For example, if there were an employee database with an attribute called skills, skills could be broken out into soft skills, hard skills, writing skills, speaking skills, and so on.

Derived attributes have a dashed outline and are attributes that are derived from, in other words, related to other attributes. For example, the attribute age could be derived from the attribute birth date because age can be calculated using the birth date. The relationship shapes are left for the last because here is where everything gets linked together.

In the relationship column, the diamond with the single outline represents the relationship. Now relationship simply means what links entities on the left to attributes on the right. So if there were a database with customer as the entity and then an attribute called invoice, then the relationship might be pays because customers pay invoices. Weak relationships are simply the relationship between weak entities and their parent.

So the shapes have been seen. Now let's turn to connectors which join the shapes together. They describe the association between tables using the entity relationship and attribute shapes. Now there are more than the ones being looked at here. There is a list of connectors shown on screen, namely, one to one, one, one and only one, many, one or many, zero or one, and zero, one, or many.

So these are just some of the common shapes found in entity relationship diagrams. Actually this notation is commonly referred to as crow's feet notation because of the way the connectors resemble crows' feet, and it is just one of several different notation types. But it is the most common notation type, so it will be seen more than any other.

As can be seen, the relationships are described by the standard relationships found in databases – one-to-one, one-to-many, and so on. In addition to connectors, a numerical or letter notation will often be seen next to connectors that indicate cardinality.

Cardinality refers to the number of occurrences of the relationship between entities, and it could be a number, as said, or M might be seen, for many. It really depends on the cardinality. And then there is the ordinality relationships. Now ordinality refers to whether a relationship is imperative, that is mandatory, or whether it is optional.

So for example, one, one-to-many, one or more, one-and-only-one, and so on are mandatory because there will always be at least one relationship between tables, while zero or one, and zero, one, or many are optional because they offer the option of having no relationship that is zero.

Creating Entity Relationship Diagrams

So let's take a look at how Entity Relationship diagrams, or ERDs, are created. In this basic example, there are the three main shapes used in ERDs. The rectangle represents the entity. The diamond represents the relationship, and the oval represents the attribute.

They are connected using lines, and this is the most fundamental aspect of ERDs. Just like flow charts, everything in an ERD is broken up into single components. In flow charts, every shape represents a logical step. And the metaphor is similar in ERDs except that we are not talking about programming instructions but rather the basic relationship between data in a database. So how do we translate that into real-world examples?

Well let's start with an empty relationship, one entity, one relationship, one attribute. Another relationship diagram with one rectangle, one diamond, one oval shape reflects on the screen. And in the real world, you define the entity – in this case, author. Rectangle box is filled with Author. And what do authors produce? Well books.

Oval box is filled with Books. Authors write books, Diamond box is filled with writes, and that is the relationship between this entity and this attribute. So that is one basic example. But the diagram is incomplete because we need to define the relationship between the entity and the attribute. And so we use the notation that describes relationships.

On the left-hand side, by the entity author, the single vertical stroke indicates one. And that is because we will assume that there is only one author that writes a book. The cardinality or the numerical relationship between objects in an Entity Relationship diagram is indicated by that single stroke. And it is always closest to the entity or attribute being identified. So on the right hand side, by the book attribute, we identify the cardinality of the attribute.

One author could write one book or many books. So the single vertical stroke, which indicates one, and the crow's feet, which indicate many, tells us that there is a one or many relationship here. Let's take a look at another. In this diagram, we have a client entity and a purchase attribute. The relationship between client and purchase is makes. Clients make purchases. But in this example, we have a many relationship with the client entity because usually a table is going to be populated with many different clients.

And next to the purchase attribute, there is a circle, which means 0, and the crow's feet, which indicate many. That notation is zero, one, or many because even if there is a client in the database, merely being a client does not mean you have to make purchases. Now it could be one or many.

There are no rules about ERDs that say you cannot. But this is just trying to demonstrate how the relationship can be defined by the shapes in ERDs. Now keep in mind that ERDs will always look much more complex than this and resemble flow charts in many ways.

But in essence, ERDs are constructed from relationships, just like these examples, joined by connector lines to indicate the relationship between data. Finally, it is important to note that while the examples shown use action words or verbs to define relationships, it does not always have to be verbs. Authors write books, and, yes, clients make purchases. And in most cases, the relationship, which is really an action of some sort, will use verbs. But because there are no rules that require you to use verbs, you do have the flexibility to use other ways of defining relationships.

Introduction to Database Normalization

If you do some research on the process of database normalization, you will probably encounter quite a lot of documentation and possibly variance in agreement over what database normalization should be. But there are some fairly consistent basic best practices that might be adopted when determining what databases will look like in terms of their design.

So the goal of normalization is to achieve an optimal database design. That is pretty self-explanatory, but the benefits of getting a database into a normalized state are clear. As for the benefits of normalization, they should be extensible, which just means that they can continue to evolve. More tables, new records, new objects can come and go, but it is clearly in a changeable state.

They should be intuitive, meaning databases should clearly make sense not just to users but also to those who are administering the databases as well. They should have no modification anomalies; the idea that any changes that might occur to the database environment will produce anomalies that may harm the way the database is performing.

Then there is support for a wide query range. The ability to extract information very quickly and easily with a very logical flow and be able to locate any piece of information anywhere in a database is important. And once a database is in a normalized state, typically that results in better performance.

So while the goal of normalization is not necessarily to achieve good performance, it ends up happening as a side effect of the main goal of optimal database design. Well-normalized databases are simply going to perform better because there are fewer redundant records. There is less information in the database, and the relationships between the tables are effectively designed.

Normalization Rules

The other component of first normal form is to have some sort of what is known as a primary key value; in this case, a single column in the table that is capable of uniquely identifying the entire record. So in the right-hand table, it can be seen that the first column is employee ID and each number is unique to each employee. There is another table displayed on the screen which is divided into three columns.

The columns are headed as Employee ID, Name, and Department. It guarantees uniqueness because it cannot be duplicated in other records. It is impossible to create another employee entity in the same table with a duplicate employee ID. Once there is an employee number 1, there could never be another employee number 1.

The value will never be duplicated or reused even if that original one is deleted. So that is one main component of the first normal form. And the other one is to reduce the redundant information. So two address fields are being seen here – Address1 and Address2. And what might be possible is to pull that information out of this table and create a separate address table and then connect the employee to an address using a relationship.

So on the right-hand side is only the ID and then the Name and department. And then the addresses can be linked to each person. And notice that in the normalized table, there are two employees with the same Name. Steve Jones shows up twice. But they are in different departments, and even more notable is that we know they are different employees because they have different employee ID numbers.

It is entirely possible that there are two Steve Jones and one works in Sales and one in Marketing. But they have different ID numbers, and that is what distinguishes them from each other.

The second normal form attempts to identify any kind of field which may cause you to still have to enter information redundantly. The table on screen is divided into four columns: Name, Department, Skill, and Address1.

In next four rows, there are corresponding values filled in all the cells. So in this example, what is being seen is Steve Jones, but this is the same Steve Jones in both cases – same name, department, and address. This is the same person. So with the addition of a Skill field in the table, it has required entering all the other information for Steve Jones twice because he has two skills – Sales and Customer Service.

And right away, when that is seen, it is known that a new table is required because there is really no point in entering Steve Jones, Sales, 13 The Oaks, twice. So a second Skill can just be added. There should be another table that can contain just the skills, and then the employee can be related to the skill because what this also results in is a classic many-to-many relationship because any employee can have more than one skill.

But any one skill could be applied to more than one employee. What is desired is a one-to-many or even a one-to-one relationship wherein one employee could have many skills but if that collection of skills is looked at, it can always relate back to that person, Steve Jones.

In the third normal form, in this case, what is being seen, and a different name will probably be found for this, but it is a process known as functional dependence. The table on screen divided into four columns that are headed by Order Number, Customer ID, Customer Name, and Date. The next three rows are filled with corresponding values.

In other words, any field that shows up in any table depends on the primary key field for it to make any sense. So in this example an Order Number is being seen, which indicates that this is a table to track orders. And of course, one of the main things needed to know about an order is who owns that order, who made the order, and that can be indicated by using the CustomerID. So Order Number 1 belongs to customer 123.

That makes sense. Information about the order is needed. However, the name of the company really is not necessary. It does not make a lot of sense to look at the value of Company1 and relate it back to an order number. So in other words, the CustomerName, in this case, is not dependant on the Order Number for it to make any sense.

So this should be broken out into another table. So here the CustomerName is seen relating back to the CustomerID, and that makes sense. CustomerID 123 is Company1. So CustomerName depends on the CustomerID, not just the order number. And that is third normal form, and the CustomerName information is just pulled out and stuck in another table.

In the fourth normal form, there is an issue similar to the second normal form in that there is a field that is causing redundant information to be entered. The table on screen shows the data of three employees. The table is divided into four columns, namely, Employee ID, Name, Skill, and Language.

Two employees have same skill and language values. In the second normal form example, it was the skill. But what is had in this example are multiple fields causing the same problem. There should be two separate tables here to accommodate for the skill and language fields.

By way of example, imagine if the company had all its employees working in sales and all employees were capable of speaking both English and Spanish, then two records for each employee would have to be had, one for English and one for Spanish. They would all have to be entered twice. It is the classic many-to-many relationship, and it is the same with the Skill field.

Any employee could have multiple skills. So the solution is the same as discussed previously. One record for each employee is needed, understanding that any employee can have multiple skills and multiple languages. So these are broken out into separate tables.

And then these are the tables that relate them together, employees related to their skills and employees related to their language. So that gets to fourth normal form, and again it is similar to second normal form except that here there are two instances causing the same problem.

This is the Boyce-Codd Normal Form, sometimes referred to as the three-and-a-half normal form. It operates, kind of, the other way, if you will. What is being seen here are two different tables, one with rate and a TrainRoute and another with a rate and Departure.

And what is happening here is that they are being consolidated back into a single table. But as can be seen, in the consolidated table, there is a bit of an issue because the TrainRoute is listed three times. Now if that was a primary key, then that is not allowed.

The value of 1 cannot be entered multiple times if it is a primary key. But what can be done is create a second column and create a multiple or composite primary key. So it strives to locate fields that are known as candidate fields for the unique key. And by combining the two values of the route number and the Departure time, then the two of them together do still generate unique values - one at 8:00 o'clock, one at 12:00 o'clock, one at 4:00 o'clock.

Those are all collectively different values. So in certain instances, that would be acceptable to create composite primary keys and it requires a combination of both fields before the uniqueness of any given record can be guaranteed.

So overall, the table structure should just be assessed and a sensible structure should be strived to be created out of those fields. And it should be made sure that some of the problems of redundancy and functional dependence and unique values have been eliminated.

Introduction to SQL

In declarative programming, abstractions are used in programming code to tell a computer how to do something. Call this library and add 1 100 times while I go off and do something else. So it really refers to how a computer handles its instructions. And in declarative languages like SQL, the system can be trained, so to speak, and that term is used very loosely, to process things in the background while work continues on something else.

That is a pretty broad generalization, but it is important to understand the difference between declarative and imperative programming. SQL is used to access and manipulate data in relational database management systems. So its reason for existing is to work with data.

And when data creation, management, manipulation, and processing are discussed, a discussion cannot really be had without talking about SQL. SQL was originally developed in the 1970s at IBM. And by the end of the 1970s, Oracle, while the company was not called Oracle at the time, built their first RDBMS based on an early version of SQL.

SQL is standardized under both ANSI, the American National Standards Institute, and ISO, the International Standards Organization. This means that SQL has a general direction in its current form and forward direction, meaning that as one moves from system to system, the paradigm and basic set of commands will remain the same. However, and this is important, migrating from SQL system to SQL system is not always a simple procedure.

And while there is a standard foundation for the SQL language under the standardization bodies, differences will be encountered as one moves to different systems. SQL is found in many technologies, and these are only a few of the common instances of SQL today. Oracle, SAP, Sybase, SQL Server, Microsoft Access, and MySQL are all technologies that leverage SQL.

Note by the way, and it is worth mentioning, that MySQL is the accepted way of saying it, as opposed to MySequal, although one can probably get away with saying it. It never hurts to know the lingo when speaking with other programming professionals.

SQL Statements

There are some rules to pay attention to when writing SQL statements. First, SQL is case insensitive, meaning that it does not require commands to be written in a particular case.

If a SQL command is typed with a starting capital letter and the remaining characters in lower case, or if a command is written all in caps or all in lowercase, SQL does not care. However, SQL commands will be seen written in all caps in this video simply to make things easier to discern from non-SQL commands.

And if further information on SQL syntax and writing SQL statements is searched out, they will often be seen differentiated by uppercase characters for the very same reason. SQL uses common language statements, meaning that the commands used are plain English and sensible.

For instance, to create a database, one types, create database; simple as that. SQL is a standardized language, meaning that the standards organizations, ANSI and ISO, have published basic requirements for all instances of SQL regardless of the software vendor.

So Oracle, MySQL, SQL Server will all have the same basic set of core commands. They may have their own specific implementations with software-specific statements, but the core commands that will be discussed here are part of the standard and therefore present in any compliant SQL implementation.

Here are some examples of common SQL commands. Under database creation, there is CREATE DATABASE, ALTER DATABASE, DROP DATABASE, and USE as some examples of database commands.

Then there are the common database manipulation and maintenance commands like SELECT, INSERT, UPDATE, DELETE, and REPLACE. And then there are table-specific commands like CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, and DROP INDEX. And all of these represent some of the most common commands that will be used when working with SQL.

SQL also uses operators just like every other language, and the main arithmetic operators do not really need to be discussed because they are just like other languages. But comparison operators need a tiny bit of explanation. The table on screen is divided into three columns that are headed as Operator, Function, and Example. Here they are, and it can be seen just how similar these are to other languages like C#, Java, Visual Basic, and so on.

But note that the not equal to operator has two possible implementations, both standard notation in different programming languages. SQL accepts the double angle brackets, and most SQL implementations that are known also accept the exclamation followed by the equal sign, which is more familiar to those who write code in C-like languages and ECMAScript languages.

Furthermore, at the bottom of the table, two operators can be seen that test for inequality, but they are a bit different. In both cases, there is an exclamation followed by an angled bracket, one operator facing left and one facing right. These mean not less than and not greater than. So they are logically the opposite of the less than or equal to and greater than or equal to operators. So not less than equates to greater than or equal to, and not greater than equates to less than or equal to.

SQL uses many logical operators, and this is just a representation of the main commands that will be used when working with SQL. The table on screen consists of Operators, their functions and examples. These are the plain language commands referred to.

So logically, writing SQL statements, kind of, makes sense, and that is the whole point about working with this language. So for example, in the first operator ALL, all values can be compared. And the example in the right-hand column is pretty self-explanatory. SELECT ALL FROM customers will select all records in a table called customers. With the AND operator, something like SELECT customerid AND customername can be used.

So that will select both fields – logical AND. And these allow writing plain English commands that give immense control over database interactions. BETWEEN for example, allows selecting a range of values between given values. And LIKE allows searching patterns. The example for LIKE, SELECT total FROM sales WHERE total LIKE 100, will select all the instances in the total field from the sales table and return all the values that match 100.

When planning on working in SQL, it is recommended to become familiar with as much of the syntax as possible. While these examples are fairly straightforward, actual SQL statements are entirely capable of employing some pretty complicated logic.

Finally, wildcards need to be discussed. Wildcards can be used to broaden search parameters by using special characters that take the place of multiple combinations. Now before getting to the wildcards, this needs to be mentioned because what is about to be told about wildcards could possibly cause a bit of confusion.

The asterisk, which is a universal wildcard character in pretty much every computer application, is used in SQL to select all the fields in a table. So in the example here, SELECT * FROM sales will select all the fields in the table called sales. Now to the standard wildcards, which are used with the LIKE operator. SQL accepts two and sometimes three different wildcards.

The % sign allows selecting zero or more characters. And here is why the asterisk was discussed first. In Access, the asterisk can also be used as a wildcard. So it could be a bit confusing to someone learning SQL for the first time. But the asterisk works both ways in Access meaning it can be used in the aforementioned manner to select all fields of the table as well as to select characters.

And the underscore is used to select a single character. So here are examples of usage for both wildcards. In this example using the % sign, SELECT FROM sales WHERE customerid LIKE '1%', 0 or more characters after the number 1 will be returned for customer IDs.

So possible results would be 1, 1 with any single digit after it, 1 with any two digits after it, and so on. Here is an example using the underscore – SELECT FROM names WHERE firstname LIKE '_ed'. Because the underscore will return any single character, possible results would be 'Ted' and 'Ned' but not 'Fred' because there are two characters, F and r, in front of that string. There is another usage of wildcards in SQL, and they are referred to as charlist wildcards.

They use brackets followed by the % symbol. And they can be used to select multiple characters, ranges of characters, or strings. So here are some examples of how the syntax can be used. In this example, SELECT FROM sales WHERE customerid like 1 to 5 encased in brackets with the % sign afterward. Now this will return all the customer IDs that start with 1, 2, 3, 4, or 5. And in this example, SELECT FROM names WHERE lastname like ade in brackets would return all names beginning with an a, a d, or an e.

And notice how different characters can be strung together without having to separate them with commas – ade. Finally, these two examples demonstrate how logical NOT can be used to search strings using charlist wildcards. In the first example, the exclamation mark is used in front of the characters. And in the second example, NOT LIKE is used instead. In both examples, the result would be the same, returning all the last names that do not begin with an a, d or an e.

Using SQL

A web browser is opened, and the home page for a server, a web server, has been navigated to. Localhost or 127.0.0.1 can be seen right there. And for this example Apache Server is going to be used.

So WampServer is being used, which is Apache, MySQL, and PHP. But other web servers can certainly be used; for example, IIS, which is Internet Information Services. It comes bundled with Windows. And from there IIS could be used with SQL Server or MySQL could be used. But if MySQL is used with IIS, just remember, it has to be installed manually.

It is a little bit different than actually having it come bundled with the system. So there are options here, but the processes that are about to be shown are pretty much the same regardless of the flavor of SQL that is being used. The interface is going to look different. Maybe some of the commands might be a little bit different or some of the features that are going to be shown. But pretty much the metaphor is the same.

So let's go ahead and take a look. Down here under Tools, phpmyadmin can be seen. I'll go ahead and click that. And that takes me to the console for phpMyAdmin. Now notice here that there are some databases.

These are the default databases that are loaded in phpMyAdmin with MySQL. And if I go ahead and click here [Running along the top of the page are various tabs like Databases, SQL, Status, Users, and Export. The Databases tab is clicked.] under Databases, those databases can be seen right here.

From here, new databases can actually be created and other operations can be performed. So let's start by creating a new database. I'll go ahead and click there, give it a name, and there is a choice of the type of databases. Lots and lots and lots of options here.

By default, normally the Collation is just gone with. But that database can certainly be created depending on needs with all those selections. So I'll go ahead and click Create. And there it is, and notice how employees is right there [in the left-hand side panel] and right here as well. [in the lists of databases.]

And I can click on either of these to see my database. Now keep in mind that it is empty right now, and from here, tables could start to be created. That is great. So I can go ahead and add those. But in a real world, databases tend to be quite long. And what I would like to do at this point is actually import an existing file rather than having to create it manually here.

So if there was, say, Access, Microsoft Access or Excel, that file could actually be converted from Excel or Access into a SQL file or even a comma separated values file, a csv file. But it is nice to be able to actually have that file created as SQL. And there are some extensions that can be added to Excel in order to do that, or some tools online can actually be used to convert the file to an actual SQL file. So let's go ahead and take a look.

I'll go down here. [The Windows Explorer is opened in which the db folder is already opened.] There is our file. It is called employees.sql. Now I am going to open it, [The file is right-clicked and Edit with Notepad plus plus is chosen from the drop-down list.] and there is the file.

Now the thing is it is a plain text file. It is comma separated, but it has actually been created with an online converter that will add the SQL headers. CREATE TABLE Employees and all the various fields can be seen here. And then there is the data right there. So that is great. It is easier that way because then a straight import can just be gone ahead and done.

So let's go ahead and Close that and take a look. I'll click on Import, and from here, I can Browse for my file and then choose the character set and all the other options. I am just going to stick with the defaults because it is just a plain text file. It has not gone through any sort of exotic changes.

The import can be chosen to be interrupted. The Format can be chosen. In this case, SQL is going to be gone with because it is known what a SQL file is. But there is the option of CSV or XML, and the import selections that are made when those are chosen may be different from what is being looked at right now. And then if a compatibility mode needs to be chosen, that can be gone to and the type of SQL that has been used to create it can be chosen.

I am just going to go ahead with NONE because it is just a pretty clean SQL file. And then the checkbox here for Do not use AUTO_INCREMENT for zero values. So that is just a way of dealing with zero values. I am going to leave all those settings as they are. Click Go.

Okay, so what happened here – and this is great – first a message with a checkmark is received telling us that the import was successful and 32 queries were executed for employees.sql. And the information here can be seen, just an idea of what was brought in.

And then under employees, [in the left-hand side panel] notice that two new tables have been created. A New table is right here, and the employees table is right there. And I go ahead and click that and see all the data that I just imported.

And I can even expand that [employees table] and look at the Columns. [The drop-down list under columns is expanded.] So something like Account_Name can be clicked on, for example, or First_Name and essentially the various fields in the database can be navigated through. But let's go back to the main database right there. Make sure it is selected.

And notice, by the way, how when that was done, a SQL command was actually executed for us; so SELECT * FROM 'employees'. That is the name of the database. It has been executed, and a chance to see that is given. So that is really nice. But let's go ahead and take a look at running SQL commands manually. So there is a SQL tab right here.

I'll go ahead and click that. And notice how that has actually been added for us. So the command is already there when the link here was actually clicked, and that will be auto entered. Now I am going to go ahead and just delete that – Backspace – and I am going to show some SQL commands. So we will start with something simple. I want to show how all the system variables can be shown.

So show variables where variable_name like, and then inside quotation marks, %dir. And then I will put a semicolon in there. Now when that is entered, notice how the code is color coded for us. So code hinting is actually received in this particular interface for MySQL. And that is really great because that lets us know what is actually a variable, what is actually a valid SQL command, what is actually strings – in this case, inside quotation marks right there with dir.

So when that is done, go ahead and click Go. And that will show the actual system variables for this instance of MySQL. And what that, essentially, means is the various paths. So we have our base directory, character sets directory, and so on. And we know what those paths are. So that is great. Let's go ahead and just click the Edit link here.

And when that is done in MySQL, it is going to bring up a separate editor, which is nice. Commands can just sort of be typed in and what happens can be seen. So I am going to start by actually just doing something like show databases; and then once that is done, click Go. And that is going to show the various databases here in the results. And another message is received here saying the SQL query has been executed successfully. And there is our employees database right there. Let's try something else.

Okay, so let's go ahead and close this. And what I am going to do now is go back to the SQL console. Now let's try something. Let's go ahead and type in a command right here. Now notice that there are Columns over here where the different columns that are in the database can be seen. So, selecting one of these would actually allow that to be entered in the SQL command. And down here, there are some buttons for adding commands. And all these do is to expedite the process of typing. So SQL often requires a lot of coding to type commands, and that just helps in speeding things up. So let's go ahead and try something different.

Okay, so at this point, all I want to do is to show how a query can be added here on a database. And right now 1 is in there. If that is used, it is going to show everything in the database. But what I can do is I will just backspace over that 1 and then double-click on Last_Name.

Now notice how that has been entered here. And just type a space here. Sometimes a couple have to be typed before it actually appears. Okay, so what I want to do now is just use our field, Last_Name, and I will use like. So we can actually do a search using the wildcard.

So I will use b%. And what that will do is that will search the database called employees using the Last_Name field and then finding anything in there in the Last_Name that begins with a B. So if I click Go now, there you go. There is the result. So as can be seen, working with SQL can be pretty fun actually. It is really enjoyable because it is a great interface.

It is very powerful. It is very sleek in the way that it works. And certainly having all these functions built into phpMyAdmin with MySQL are very useful. And once again, regardless of the instance of SQL that is being used and what web server, the experience is going to be somewhat the same. And certainly the commands that I showed you are going to be the same. And that is a basic understanding of how you work with SQL.

Stored Procedures

So what are stored procedures? Well, a stored procedure is an object that is stored in the database in the form of one or more saved SQL statements. Stored procedures can include just about any SQL statement.

There are a few limitations, but if the code can be created using standard SQL, chances are a stored procedure can be created from that code. This creates reusable code for performing administrative tasks or manipulating data. There is no real defined rule of what can or should be done with stored procedures.

So if it is thought that it can be done, chances are it probably can. So stored procedures can use any kind of code. As for the uses and benefits, stored procedures are commonly used for data validation or performing repetitive administrative tasks. Something that is found to be done over and over again is a very good example of a reason to use a stored procedure.

But they can certainly be used to check data to see if it adheres to certain sets of rules, creating user, setting permissions, all sorts of regular administrative tasks. Stored procedures do support the use of both input and output parameters. An incoming argument or parameter can be supplied to a stored procedure when it is called. And similarly, it can produce something that can be returned to the calling user.

Those are the input and output parameters. Stored procedures are only compiled once and then do not need to be recompiled every time they are called. So they perform better than ad hoc statements. They reduce the network traffic. Stored procedures run entirely in the database engine and need only be called by an application. So if a custom front end is designed, for example, and a stored procedure is being looked to be executed, the statement simply has to be issued, call it.

That is it. The code that is contained within the procedure does not have to be submitted. And the stored procedure runs entirely on the server. Stored procedures can encapsulate complex business logic that can include any kind of checks and validations, things like IF statements, WHILE loops, and other programmatic functionality. So all that complex logic can be made sure to be tested and validated before a stored procedure is saved. And they can greatly enhance security. Because of the fact that all that is ever done is call the stored procedure and all the code is saved within the stored procedure, it definitely increases security.

The only permission that is required is the ability to call the stored procedure. So it prevents what is known as SQL injection, which is a known type of attack using a front-end form or application to submit malicious code to a server that could do harm. Since stored procedures do not accept that type of approach because they are stored on a server and run locally on a server, the only thing that can be done is call it and that enhances security.

As far as limitations go, there is only one main limitation. You cannot select from a stored procedure. A stored procedure that produces a result set might be had, and that is perfectly fine. But what cannot be issued is a SELECT from and then supply the name of a stored procedure even if that stored procedure produces a result set. But stored procedures do accept incoming arguments.

So what a result set might look like can still be manipulated. In most cases, stored procedures are not used to retrieve data. They certainly can, but they are more for administrative tasks and possibly inserting data but generally not for retrieving it.

Creating Stored Procedures

Okay, now the first thing to do here is open up a SQL Prompt. Now for this example, MySQL is being used. It can be seen right there, and it is being used with WampServer. So it is located in this directory. But yours might be in a different directory depending on the flavor of SQL that is being used.

MySQL is being used. But IIS with MySQL could be being used or more commonly IIS with SQL Server. And the location is different, but the procedure, once the Command Prompt is entered, is effectively the same. So I am going to open the bin directory which is where the mysql Command Prompt is located – right there, just to show it. Now I will go back because what I want to do here is hold down Shift, right-click, and choose Open command window here. And there I am.

I am in the Command Prompt in that directory. [The code on screen goes like this: C colon forward slash wamp forward slash bin forward slash mysql forward slash mysql fifteen dot six dot seventeen forward slash bin greater than sign.] Now here is how it works. We want to run the MySQL command, so it is mysql. And then I will use the u switch and then root for user and press Enter.

And now we are in the mysql Command Prompt. It can be seen right there, and we have got some helpers here to tell us what we need to do in case we need some help or to shell out of the input statement. So the first thing I want to do is show the databases that are loaded for root. Semicolon, and there we go. We have some databases. And the one I am interested in is employees.

So let's change to that – use employees; and the database has changed. Now we can use select all from employees to see what is in the database. It can be seen it is a bunch of employee information in there. And what I want to do now is create our stored procedure. And the stored procedure will actually search the database and return the first name and last name, actually the last name and the first name, in that order, of the male employees, just the male employees.

So we start with this – delimiter //. Now this is a very common thing to do. What we are doing is we are changing the delimiter. That is the character that is at the end of every SQL statement. And typically by default, it is a semicolon. I want to change that temporarily because I am going to be using this // delimiter to actually end the creation of our stored procedure.

But inside the stored procedure, we want that semicolon not to be confused as ending the procedure but rather something that is going to be passed off to the server. So we want to pass it off the server. So temporarily, we change the delimiter. And I will press Enter, and now it is changed. Now here is how I create a stored procedure – create procedure, and you give it a name – maleemp, like that, male employees. And then in parentheses, we want to pass a parameter. Now you have got three choices for passing parameters with stored procedures – in, out, and in out.

Now in, essentially, passes information off to the server. And if you use out, it passes information of the parameter back to the user, and in out is bidirectional. I want to use in, and then I am going to create my parameter, con, and then charr(50). Sure I get two parenthesis there, there we go.

Now what we are doing here is we are saying this is a char variable that can contain up to 50 characters, and that gives me enough room to make sure that the names, both the first name and last name, are passed off to that stored procedure. Press Enter. Now when I do that, we have started to create the stored procedure and you can see the prompt has changed to a right arrow. That tells me I am inside the creation of the stored procedure.

The first thing you always type is begin and Enter. And then we will go ahead and put our command, select last_name, first_name from employees. So we are taking the last_name field and the first_name field, separated by a comma, from the employees database. And I will press Enter; where gender – and that is the name of the field for gender - ='Male'; and there we go. Now notice how I have used the semicolon there.

And that is what I was talking about, the idea that you want this to be passed off to the server when the stored procedure is called and not shell out of our existing or our current stored procedure creation. So I will go ahead and press Enter. And then we end it. So that is it. And there is where I use the temporary delimiter and with double slashes. And that will tell the mysql Command Prompt that we are finished creating the stored procedure and save it.

Now keep in mind that between begin and end, you can put as many commands in there as you want. This is the stored procedure right here, these two lines. But you can put in all sorts of different code and methods for very complex stored procedures. This is just a simple example.

I will go ahead and press Enter. And we get a prompt back telling us that the query was OK, and it will give us some information on what was affected by our stored procedure. Okay, we have created that stored procedure. Now let's go ahead and change the delimiter back to semicolon and press Enter.

Okay, that is great. Now at this point, what we want to do is call that stored procedure. It is saved, and it is available to us now, and it can be called at any time. It is called maleemp like that. So the way you call it is just to do this – call maleemp. And what we want to do here is put in our parameter. Remember, we used that in to pass a parameter off.

And so it will create something here. I will just call it names – you can call it whatever you want – and put in a semicolon and press Enter. And there you go. So the stored procedure has been called, and it is showing us the last_name and the first_name but the last_name and the first_name only of the male employees in the database.

So stored procedures are really a great way of being able to create code, very complex sets of code that can be passed off to a server and query a database and then bring that information back to the user who called the stored procedure.

As you can see, it is pretty straightforward. And as I mentioned, even though I am using MySQL, the method for creating stored procedures is pretty much the same regardless of what flavor of SQL you are using.

Database Connection Methods

Data can be represented in different ways, and the two most common types of data files that can be used when accessing data programmatically will be discussed. The first type, the flat file database, is called "flat" because it uses one line of text per record and is typically read entirely into memory before it is parsed or processed.

The columns or fields are separated by delimiter, a special character that is used to separate the fields. Usually the delimiter is a comma, but tabs are sometimes used to separate the fields. Commas are usually advisable because tabs are not a viewable character in many applications.

If a text file is opened in Notepad, for example, the tab will not be seen and lots of spaces will be seen in between the fields. Commas are straightforward, and it is easier to look at the text database and know where the fields are separated. Usually comma separated files will be seen with the .csv extension for comma separated values. For example, an Excel database can be saved as a CSV file, and that is an easy way to get a database into a format that can be accessed programmatically.

Here is an example of the way a comma separated database is structured. To demonstrate this, there is a table headed with First Name comma Last Name comma DOB comma Gender comma Location comma ZIP comma Account Name. Next row contains the example that goes like Cody comma Blackwell comma one November fifty six comma Male comma Ireland comma comma cody underscore blackwell at sign hotmail dot com.

Next row contains the example that goes like Raj comma Chawanda comma twelve February eighty nine comma Male comma US comma MA zero two onw onw three comma raj underscore chawanda at sign hotmail dot com. Note how every field is separated by a comma, even the header row. In a plain text file, each row or record would be separated by carriage return, so multiple lines of values separated by commas.

Now this is just to clarify what a flat file database looks like when it is processed. This is how the file is created using one record per row with the fields separated by commas. Note by the way how in the first record, Cody, Blackwell's record, that after his country of residence, Ireland, there are actually double commas. Even null fields, that is fields that contain no data, still have to have a comma. So when double commas are seen, it is known it is a null field.

Another way of putting that is that this database has seven fields – First_Name, Last_Name, Date Of Birth, Gender, Location, ZIP code, and e-mail. So there have to be six commas in every single record. You do not have to have seven because the last delimiter in a record is the carriage return. The point is that even if the data in a particular field is a null value, it still has to be delimited. So this is what the data logically looks like when it is processed, a recognizable table of data broken out by rows and columns.

The other main data type – and it is really one you will probably use most often – is XML, Extensible Markup Language. XML is very popular for several reasons. First like HTML, it uses a recognizable markup with tags that look very much like HTML tags. XML has loose rules in the sense that you can create anything you want in an XML file to suit your needs. It is very flexible. Tags can be called whatever you want. There is no list of tags like an HTML.

Rather the tag names and the structure you create can be whatever you want. However, there has to be a tight structure in XML. What I mean by that is that XML has to have opening and closing tags, and everything has to be properly nested just like HTML. And you can store data and metadata in XML files. So they are perfect for programming. So in this XML example of an invoice, you can see how an XML file is laid out. And it is just a plain text file that contains whatever you need – a single customer invoice in this example. But an XML file could easily contain hundreds or thousands of invoices if you wanted.

Connecting to Data Stores

Okay, so I have a C# project here, and I have also got an XML file. It is right there. And it is just a single invoice. Now keep in mind that this conceivably could be tens, hundreds, thousands of invoices all in a single file if we wanted to.

The procedure is roughly the same for accessing this data programmatically. So how do you do that? The code on screen goes like using system semicolon, using system dot collections dot generic semicolon, using system dot linq semicolon, using system dot text semicolon.

Well the first thing we have to do is actually add this line, using System.Xml, just like that with a semicolon. Now that will allow us to access the system libraries for XML procedures, one of them being the ability to access an XML file and read it. And so let's go ahead and create our code.

Rest of the code consists of following lines: namespace underscore 99693, open curly braces class program, open curly brace static void main open parenthesis string open square bracket close square bracket args close parenthesis. Open curly brace. A line of code is added here.

Console dot WriteLine open parenthesis open quote Press Enter to exit period close quote close parenthesis, semicolon. console dot ReadLine open parenthesis close parenthesis, semicolon close curly brace, close curly brace, close curly brace. Code ends. Inside the main method, I will add a couple of lines there and then type this, using, and then in parentheses, XmlReader, and then reader.

Now reader is the name of the object we are creating; and then XmlReader.Create; in parentheses, the name of the file – in this case, invoice.xml. Okay, so far so good. But where does invoice.xml come in? Well we actually need to add it to the project. So let's go ahead and do that.

First thing I will do is Copy all this data. This is the quickest way rather than having to type it in. Right-click on a project. Choose Add, and in the fly-out, choose New Item and then XML File, and give it a Name. I will call it invoice.xml and click Add. Now notice how it has been added to the Solution, and here is the file right here.

And I will go ahead and just Ctrl+V to paste everything in, and there is our XML file. Let's go ahead and just press Save there. Okay, so there is invoice.xml, and there it is right there, being referenced. So we created that new object. Now what I want to do here – and I actually put a semicolon in when I should not have – is create braces. Alright, then actually instead of that, I should have put that – so a closing parenthesis. Now let's go ahead and create a new line. What we want to do here is actually create a method or a procedure – I guess is a better way of putting it – for pulling that data out of the XML file. And I am going to use a while statement.

So while is really good for this because what it will do is it will go ahead and read the entire file until the end and we can use the switch statement to actually choose what data we are going to pull out of it. So I am going to use this reader – remember, that is the object we created using XMLReader – .Read with a capital R. And we need opening and closing parentheses and another one there. And then put in my braces for the while statement.

Okay, now inside here, we are going to start to read the values in the XML file. And for this one, all I want to do – let's go ahead and take a look at this invoice file again – all I want to do is pick some of the values here. So just maybe I want to pick Date and Invoice Number and the Name of the contact and the Company. So it is like we are actually reading a report or reading an invoice and just creating a report based on some of the values. And that is the nice thing about doing this programmatically.

You can just choose some of the values, not all of it, and that is the whole point about being able to access data stores. So what we will do is this, we will create our switch statement; so switch reader.Name, just like that. Create our braces. Now reader.Name is actually going to look at the name, the information that is contained within the tags. One more time, the idea here is that we could actually pull the information of the actual tags themselves or the information that is contained within them.

And we use .Name to just pull the information from the actual data that is within the XML tags. So the first case statement will be this – case "Date". So the first item we want to pull is Date and I will make sure do that right. There we go. And I will create an if statement. So if reader.Read, open close in that, and then put in our braces for the if statement, Console.WriteLine. And here what we want to do – I will put in a little bit of text here, Date – we are pulling the object that we have come to, in this case, the data object of the Date value that was found inside the XML file and using the 0 inside braces to represent that in our output and then reader.Value, just like that.

Okay, great. So one another thing is worth mentioning. Notice how I use reader.Name here and reader.Value here. Well reader.Name is going to look for Date. That is the important part. I mentioned that we are actually pulling data, but I should have mentioned that what we are doing here is we are looking for the actual tag, which is the field, the data field, and pulling that data. And then we are using the .Value to pull the actual information inside that tag. So we are getting the actual data now. So that is, kind of, an important thing to point out because we need to check here to see have we found a tag that is called Date – right here, reader.Name, right there – have we found a tag called Date. And then if we have, then display the value in that tag, the data inside that tag. Okay, finally, I will put in the break statement.

And there we go. Okay, that is pretty complete. Now rather than having to retype all that, I will just right-click this and Copy it, add line, and then I am going to paste it three more times, once, twice, three times. And then we can just change these values. So here we are going to go with Number, and this will be Invoice number, just like that. And here we will go with Name, and then here we can go Contact Name, something like that just to differentiate it. And then here, finally, it will be Company, and we will change the output here to Company, just like that.

Okay, I think that looks pretty good. Now the thing is that when we run this right now we are not going to get the result we want. I will go ahead and show you why. Okay, so I try to Debug it, but the debugger is not going to find that XML file even though we created it right here.

That will be available once we package up the application and send it out. Somebody else can use it. But for the moment, when you are debugging, just remember that what you need to do here is take your invoice XML file right there, and I will just go ahead and right-click that and Copy it. And then open the project folder. You will find another project folder with the same name inside.

And then inside the bin folder, under Debug, in the Debug folder, just go ahead and Paste that XML file. And so what happens is when we run the debugger, the debugger is going to see that XML file right there and not have to go searching for it or pass back an error telling us that it cannot find it. So now when I run it, okay, so there you go. So what happened here was we got the feedback that we wanted.

It looks like it needs a little bit tweaking there in terms of what is being represented. But the point is that you can use this method of being able to create that XML object and then use the XmlReader to read into the data file and pull out just what you need. And that is how you access XML data stores with Visual C# and Visual Studio.