Terms

DBMS: Database Management System, software that manages organization, storage, and retrieval of data from at Database. AKA a Database Server.

RDBMS: Relational Database Management System. A DBMS that is based on the Relational Model. This includes the most common database software.

Database: contains the physical data and data structure. Comprised of a series of tables.
Note: it’s not uncommon for the term database to be used interchangeably to mean either the database itself or the database engine/server.

Database Engine: the software providing access to the database. Examples include MS SQL Server, Oracle, PostgeSQL, MySQL, MS Jet Engine (used in Access). AKA a Database Server.

Data Model: is the conceptual description of the problem space. Essentially defining what information needs to be stored within the database and how. Does not involve the physical implementation of this storage. 

Relational Data Model: one of the most popular approaches to modeling data, it defines a set of rules on how data can be represented, protected, and manipulated. Based on storing data in two dimensional tables and creating relationships between data stored in different tables. Commonly used in systems that must track frequently changing data.

Dimensional Data Model: a form of data modeling used when dealing with large quantities of historic or infrequently changing data. Designed to make it possible, and efficient, to run ad hoc queries. 

Database Schema (or Schema): is the physical layout of the conceptual data model as it can be implemented within a DBMS. 

SQL: a language used for sending to or accessing data stored within a database. Frequently said to stand for Structured Query Language and often pronounced “sequel”. 
Technically speaking, however, according to ANSI the standards body responsible for SQL, 
SQL does not stand for anything and is pronounced “ess que ell”.

Relation: conceptual name for a table.

Attribute: conceptual name for a field (the column of a table).

Tuple: conceptual name for a record (the row of a table).

Note: different RDBMS will use variations on this terminology. E.g. some may call a physical table column a field or an attribute. It’s not necessary a fixed separation between conceptual and physical.

Installing the AdventureWorks Database

AndventureWorks is a full-blown relational database you can use for testing and experimenting. We’ll be using it to learn a lot of the various SQL queries over the coming weeks. Try to use it only with select statements, not insert or update. This will keep it intact throughout the semester.

To see if you have AW installed launch SQL Server Management Studio (similar to Enterprise Manager in SQL Server 2000):

Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio 

SMS will startup.

In the left window, expand the Databases listing as you see below:

If you do not see AdventureWorks you’ll need to do the following:

1)	Download the Installer from: imm.sheridanc.on.ca/ad/
2)	Run the installer. If it won’t allow you to install to the default folder then pick a new location on your personal drive. REMEMBER THIS LOCATION.
3)	In Server Management Studio click the New Query button on the top left.
4)	Copy and Paste the text indicated on the AD page where you got the files from.
5)	Note: if you changed the install path in the installer you’ll need to change the pasted text to use your path.
6)	Click (!)Execute to add the database.
7)	Hope that it all worked correctly!

Accessing Data in a Database using SELECT

To access data stored within a database you create queries. While other query languages exist, the most common one is SQL. SQL is used by all the major database systems currently available.

By convention SQL keywords are written in uppercase while the user defined words are written in lowercase.

To retrieve information from a DB you use the SQL SELECT statement.
(the “1)” in front is a reference number for in-class use and is NOT part of the SQL query)

1)	SELECT * FROM Person.Address

Would tell the system to retrieve all columns (the asterisk) from a table called Person.Address.
Note: the format of this table name is Schema.TableName. We will covers Schema’s later.
For now know that if it starts with dbo.* you don’t need to include the schema name. 
E.g. dbo.ContactList could be accessed with just SELECT * FROM ContactList

Note: this assumes that a database containing the table customers is currently in use. This can be done either interactively through SMS or with the SQL USE statement:

	USE AdventureWorks

To select only certain columns:

	SELECT column1, column2 FROM customers

2)	SELECT City, PostalCode FROM Person.Address

To order the data alphabetically by City:

3)	SELECT City, PostalCode FROM Person.Address ORDER BY City

By default this will output in ascending order. To do the same as above but in descending order:

4)	SELECT City, PostalCode FROM Person.Address ORDER BY City DESC

If you wish to specifically indicate ascending order:
5)	SELECT City, PostalCode FROM Person.Address ORDER BY City ASC
This will create the same output as example 3.

To order a group of information that already within a sorted block:
6)	SELECT City, PostalCode, ModifiedDate 
FROM Person.Address 
ORDER BY City ASC, ModifiedDate DESC

This will order the information first by City in ascending order. Then within that order the ModifiedDate appears in descending order.

Note: look at the way #6 has been split across multiple lines. This is an established convention for writing longer queries. It makes is much easier to read and modify. Please use this format.

Specifying Data to be Accessed

So far these queries allow us to select what attributes (fields/columns) of the relation (table) we are interested in. However they will return all the tuples (records/rows) stored within that table. Tables can easily contain millions of rows. Usually you are only interested in a subset of that data at any given time.

To select on a subset of the data you can use the WHERE clause to define what criteria you are searching for. Only records that match the WHERE clause will be returned.

To search of customers who work for AbbyCorp:

7)	SELECT * FROM Person.Address WHERE City = 'Toronto'
(please note the use of ‘single’ quotes)

WHERE can be used with other commands to build more complex queries:

8)	SELECT * FROM Person.Address 
WHERE City = 'Toronto' 
ORDER BY AddressID

With WHERE you can compare using the following operators:

> 	greater than
	<	less than
	>= 	great than or equal to
	<=	less than or equal to
	<>	not equal to
	=	equal to

9)	SELECT City, StateProvinceID, PostalCode 
FROM Person.Address 
WHERE StateProvinceID > 50

Using BOOLEAN Logic in a Query

Boolean logic revolves around statements that can be either TRUE or FALSE (often represented by 1 or 0).

Boolean logic is the basis of branching in computer programming, using IF statements.

	if (userPass = “sys49152”) then
	do something here
end if  			(an if statement in basic style syntax)

	if (userPass == “sys49152”) 
{
		do something here;
	} 				(an if statement in C style syntax)

Boolean operators allow additional conditions to the IF statement. 

The Boolean operators of interest to us at the moment are:
	AND	if a AND b are true then the statement is true
	OR	if a OR b are true then the statement is true
	NOT	if a is not true, then the statement is true (we’ll come back to this one)

10)	SELECT * FROM Sales.SpecialOffer

11)	SELECT * FROM Sales.SpecialOffer 
WHERE Type = 'Discontinued Product' 
AND Category = 'Reseller'

12)	SELECT * FROM Sales.SpecialOffer 
WHERE Type = 'Discontinued Product' 
OR Type = 'Excess Inventory'

Selecting values from a range:

13) 	SELECT * FROM Sales.SpecialOffer 
WHERE DiscountPct 
BETWEEN 0.15 AND 0.38

Using Not to negate the range selected by between:

14)	SELECT * FROM Sales.SpecialOffer 
WHERE DiscountPct 
NOT BETWEEN 0.15 AND 0.38

Selecting from Multiple Tables

We’ll examine Joins in the coming weeks, but below is one method of combining data from multiple tables.

15)
SELECT HumanResources.Employee.EmployeeID,
HumanResources.Employee.Title,
Person.Contact.FirstName,
Person.Contact.LastName
FROM HumanResources.Employee,
Person.Contact
WHERE HumanResources.Employee.EmployeeID = 5 
AND Person.Contact.ContactID = 5

Doing It Yourself

Using the above examples try to create queries that will:

1)	List the name of all the vendors that have a credit rating of 1.

2)	List the name and Credit Rating of all vendors that have Preferred Vendor Status and a credit rating less than one.

3)	List all the products that have a Reorder Point range of 1 to 100.

4)	List all the products names and ID’s that have a stock less than 500, cost more than $500 and less than $1,400, and is a Finished Good.

5)	Try to make a query that selects data from two different tables of your choosing.