What is a Database?

By its simplest definition, a database is a collection of data that is arranged so it can be accessed, managed, and updated easily. You might recall that the term data refers a collection of unprocessed data.

The most popular type of database is the relational database. It’s the type of database that is frequently used in websites and is also the type of database you learn about in a CIS certificate or degree program. However, the relational database is not the only one. Other types exist, including flat-file, NoSQL, object-relational, and object-oriented databases, but these are less common in Internet applications.

A relational database has the notion of tables, where data is stored in rows and columns, much like a spreadsheet. Each row in a table contains the complete information about an item that is stored in the table, also known as a record. Each column, on the other hand, contains information about a specific property of the rows in the table, also known as a field.

The term “relational” refers to the way the different tables in the database can be related to each other. Instead of duplicating the same data over and over again, you store repeating data in its own table and then create a relationship between that table and other tables. Consider the table called Review in Figure 1.

As you can see in Figure 1, each review is assigned to a musical genre such as Pop, Indie Rock, or Techno. But what if you wanted to rename the genre Techno to something like Hardcore Techno? You would need to update all the rows that have this genre assigned. If you had other tables that stored a genre, you would need to visit those tables as well and make the changes manually.

A much better solution would be to use a separate table and call it Genre, for example. This table could store the name of a genre and an ID (a sequential number, for example) that uniquely identifies each genre. The Review table then has a relationship to the Genre table and stores only its ID instead of the entire name. The Genre table also has a SortOrder column, which is used in later examples. Figure 2 shows the model for this change.

With just the ID of the genre now stored in the Review table, it’s easy to rename a genre. All you need to do is change the name of the genre in the Genre table, and all tables with a relationship to that genre pick up the change automatically. In database terminology, both Id columns in this example are primary keys (identified by the lock icon) and are used to uniquely identify each row in the table. GenreId, on the other hand, is a foreign key which is used to link back to a primary or otherwise unique key in a table. Later in this chapter, you see how to create and make use of relationships in your relational database.

Different Kinds of Relational Databases

You can use many different kinds of databases in your ASP.NET projects, including Microsoft Access, SQL Server, Oracle, SQLite, and MySQL. However, the most commonly used database in ASP.NET websites is probably Microsoft SQL Server.

In the CSIS 786 course you will be learning how to use a Microsoft SQL Server, and in the CSIS 114A and CSIS 124A courses you will be learning how to use Oracle and MySQL database servers.

Note: Although these courses uses database servers from different vendors, the beauty of modern-day database servers is that they all support the ANSI 92 SQL standard database query language, the structured query language (SQL). Many of the query commands you learn for one database server type can be used to query any other SQL server type.

View information for downloading and installing the current version of Microsoft SQL Server Express w/LocalDB.

What is a Database Server?

Database servers are the final tier of Web development - the Database tier. In order to perform most of the required tasks to develop a modern-day dynamic Web application you need to have access to one or more databases stored on a database server, also known as a relational databse management system or RDBMS. Being able to use a database in your ASP.NET Web applications is just as critical as understanding HTML, CSS, and JavaScript: it’s almost impossible to build a modern, full-featured Web application without it.

Databases are useful because they enable you to store and retrieve data in a structured way. The biggest benefit of databases is that you can access them at run time from your Web application site, which means you are no longer limited to just the relatively static files you create at design time in Visual Studio. You can use a database to store reviews, musical genres, pictures, information about users (usernames, e-mail addresses, passwords, and so on), log information about who reads your reviews, news articles, and much more, and then access that data from your ASP.NET pages (Web Forms or MVC). This gives you great flexibility in the data you present, and the way you present it, enabling you to create highly dynamic Web applications that can adapt to your visitors’ preferences, to the content your site has to offer, or even to the roles or access rights that your users have.

In the later database tutorials, you see how to use SQL queries targeting a SQL Server database to retrieve and manipulate data in your student database. However, before you can write your first SQL statement, you need to know how to connect to your database. This tutorial shows you how to connect to your student SQL Server database.

Database Servers

• Microsoft SQL Server

• MySQL

• Oracle

Using SQL to Work with Database Servers and Data

To get data in and out of a database, you need to use the Structured Query Language (SQL) which is the de facto language for querying relational databases. Most all modern-day relational database management systems understand the SQL query language. A number of clear standards exist, with the most popular one being the ANSI 92 SQL standard. Besides the grammar that this standard supports, many database vendors have added their own extensions to the language, giving it a lot more flexibility and power on their own system; this comes at the cost of decreased interoperability with other systems.

For instance Microsoft version of SQL supports most of the grammar that has been defined in the ANSI 92 SQL standard and on top of this standard, Microsoft has added some proprietary extensions. Collectively, the two are referred to as T-SQL, or Transact SQL. Oracle's flavor of SQL is known as PL SQL. Again, many of the basic SQL commands you learn will be useful across all platforms, however when you get into the extensions you will see many similarities, but usually there will be a difference in syntax, usage, or commands.

To get data in and out of a database, you need to use As you learn how to retrieve and manipulate data in a database more in-depth you will see differences in these extensions that each vendor adds to their SQL server for performing more advanced, programattic, access to a database server and the data it contains.