Introduction To Creating Tables With Sql

Introduction to Creating Tables with SQL

Background

We’ve talked before about how a relational database such as Microsoft SQL Server (MSSQL) runs on a special server. To make the server work well there are several interfaces which control its operations. Some are available only to the server or network admins which are responsible for keeping the server running. Others, such as the ones we are looking at today, are for the people who are responsible for the data on the servers. Management Studio is the primary tool that you can use to view, edit and manage the databases in the server. It’s expected that you’ve already gone through the exercises in Connecting to Class Servers in week 1. If you haven’t, go do that now.

Getting Started

  • Open Microsoft SQL Server Management Studio and connect to the comweb.uml.edu server.
  • In the left hand side of the screen you will see a panel called the Object Explorer (if you don’t see it, click on VIEW à Object Explorer or hit F8)
  • Expand the Database Node by clicking on the + next to it, if it isn’t open yet.
  • Find your Database in the list. It should correspond with your Blackboard Login.
  • Expand your Database to see the contents underneath. You should see something like this:

Figure 1: The Object Explorer in MSSQL Management Studio

  • We are going to be working almost exclusively in the folder named TABLES. Be VERY careful about changing anything in the other folders. It is possible to do some real damage to your database if you don’t know what you doing. You should not be able to access anyone else’s database and no one else should be able to access yours except IT administrators and the course instructors.

What are Tables?

In the 10 books in Excel project, we ended up with an Excel spreadsheet with 4 sheets, each being their own model. When we make the jump to move the project to a database we can look at our data in a very similar, yet slightly different manner.

In a relational database such as MSSQL, MYSQL, Oracle and others, data is stored in tables. At first glance these look similar to the sheets in Excel. When you view them, you will see rows and columns. The columns are called fields. The rows are called records. Just as in Excel, similar data (such as all the book titles) are stored in the same field as other titles. Each record (row) refers to one book. Beyond that there are many significant differences between Excel and a relational database.

First of all, you can’t hook up an Excel Spreadsheet to a web page. Let me amend that because, yes, technically, you can. However, you would never be able to operate a bookstore like the one we are proposing to run off of an Excel spreadsheet. It isn’t made for it. Databases are designed and optimized to store, retrieve and distribute large amounts of data quickly, accurately and efficiently. Excel is designed to store some data and provide tools to do functions on that data, create graphs, do “what if” scenarios and the like. In short, Excel is an app designed to use and manipulate data while a database is designed to distribute that data quickly on a massive scale.

In Excel, you can type any sort of data you choose into a cell. One minute it can be a string, another it can be a date, and then it can be a number. Excel isn’t locked into any strict data types. In Excel you can right click and choose Format Cells. This will bring up a window which will let you change what the data looks like. Inside the cell is a number or a string or a date but you can still type whatever you want into each cell.

In a database, as you will see in a few minutes, it is possible to set very strict rules as to what types of data get put into the fields and records. This is purposeful in order to protect the integrity of the data underneath it. If a web site with 10 people looking at it a month gets an error because its app was expecting a date and got a string, it’s not the end of the world. However, if bad data gets into the database running the stock market and it crashes, literally the economies of nations get impacted. The data in databases matters. On the one hand it’s 1s and 0s but on the other hand, those 1s and 0s impact the day to day lives of people, even if it’s only their mood if the web site is down.

One of the first parts of creating a database is to create the tables which are going to be used in it.

Creating Data Tables with SQL

We are going to create the four tables which we are going to use in the bookstore project. These data models might change as move through the weeks but this is the first pass at creating them. Let’s start with the Books table.

  1. Open the SQL window for your client
  1. In the SQL Window (or query builder or whatever it is called in your program) type, use databaseName (databaseName is the name of your database)
Download Markdown