Introduction to Databases

A database is a collection of information destined to make that information easy to view and exploit. To use our lessons, you must have access to a Microsoft SQL Server computer that allows you to create databases on the server. At the risk of going back and forth between Microsoft Access and Microsoft SQL Server, in our lessons, we will use only Microsoft SQL Server.

Practical Learning: Introducing Databases

1. Start Microsoft Visual Studio .NET
2. Position the mouse on Server Explorer to pop up its window
3. Expand the Servers node. If you see the name of the server that has your SQL Server installation, fine. If you don't see it but know that the server exists in the network, right-click the Servers node and click Add Server... In the Add Server dialog box, type the name of the server in the Computer text box
    
   If you have many servers, expand the one that has your SQL Server installation or the database you want to use. Then expand the SQL Servers node
4. To create a new database, right-click either the name of the server that has SQL Server or any node under it and click New Database
    
5. In the New Database Name text box of the Create Database dialog box, type CIC1
    
   
6. Select the necessary login system you prefer. If in doubt or hesitating, accept or click the Use Windows NT Integrated Security radio button and click OK

Deleting a Database

If you have created a database by mistake or simply don't need a particular database anymore, you can get rid of it. Make sure you never delete the databases that were installed along with Microsoft SQL server.

To delete a database in the MMC window, if the Databases folder is expanded in the left frame, you can right-click the undesired database and click Delete. In the right frame, you can also right-click the undesired database and click Delete.

Tables

Introduction

A table is an object that holds the data of a database. Because a table is the central and the most important part of a database, the information it holds must be meticulously organized. Therefore, to better manage its information, data of a table is arranged in a series of fields called cells, the same types of cells you would encounter on a spreadsheet application such as StarCalc, Corel Quattro Pro, or Microsoft Excel.

To use tables on a database, the table must belong to a database. SQL Server installs a few databases on its own, and you can use these databases to experiment with tables.

The information relevant to a database is stored in tables. This information is organized in columns and rows. A column holds a category of data that is common to all records. A row is called a record and holds all information that belongs to an entry of the table. To organize the information that a column holds, a table needs a series of details about each column. Two aspects are particularly important: a name and the type of data that a column should/must/can hold.

Practical Learning: Opening a Table in Visual Studio

1. In Server Explorer, expand the name of the server where you created the CIC1 database. Expand the pubs node, and expand Tables under it
2. Double-click jobs. Notice that it opens and displays in the code editor
    
   
3. Close the dbo.jobs tab

Columns and Object Names

The name of a column allows the database as a file to identify the column. The name of a column also will help you, the database developer, to identify that column. There are rules and suggestions you must or should follow when naming the columns of a table.

The name of a column:

• Can start with a letter, a digit, or an underscore
• Can include letters, digits, and spaces in any combination

After respecting these rules, you can add your own rules. In our lessons, the names of columns:

• Will start in uppercase: Examples are Address, City, or Code
• Will be made of one word. If the name is a combination of words such as first name, each component of the combined name will start in uppercase. Examples are FirstName, ZIPCode, or DateOfBirth

To specify the name of a column, in the Design View of a table, under the Column Name section, type the desired name, and press Enter.

Practical Learning: Setting Columns Names

1. In Server Explorer, expand the CIC1 node
2. To create a new table, right-click the Tables node and click New Table
3. As the cursor is blinking in the first empty field under the Column Name column, type AssetType

The Types of Data

After deciding on the name of a column, the database needs to know the kind of information the column will hold. Since there are various kinds of information a database can deal with, SQL Server provides a set of categories called data types. Therefore, you must specify the data type that is necessary for a particular column. The data types used on a table are the same as those we reviewed for the variables:

bit: The bit is the smallest data type of the SQL Server categories of columns. It is used for a field that would validate a piece of information as being true or false, On or Off, Yes or No, 1 or 0.

int: An integer, also called a natural number, or a whole number, is a number that can start with a + or a - sign and is made of digits. Between the digits, no character other than a digit is allowed. When the number starts with +, such as +44 or +8025, such a number is referred to as positive and you should omit the starting + sign; this means that the number should be written as 44 or 8025. Any number that starts with + or simply a digit is considered as greater than 0 or positive. A positive integer is also referred to (in the programming world) as unsigned. On the other hand, a number that starts with a - symbol is referred to as negative. If a column would hold numbers in the range of -2,147,483,648 to 2,147,483,647, set its data type as int.

tinyint: If you except to use very small numbers such as student's ages, or the number of pages of a brochure or newspaper, apply the tinyint data type to such a field. A column with the tinyint data number can hold positive numbers that range from 0 to 255.

smallint: The smallint data type follows the same rules and principles as the int data type except that it is used to store smaller numbers that would range between -32,768 and 32,767.

bigint: The bigint data type follows the same rules and principles as the int data type except that its field can hold numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

binary: The binary data type is used for a column that would hold hexadecimal numbers. Examples of hexadecimal numbers are 0x7238, 0xFA36, or 0xAA48D. Use the binary data type if all entries under the column would have the exact same length (or quantity). If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals.

numeric and decimal: In some columns, users will be asked to enter particular numbers referred to as decimals. A decimal number is a number that can have a period (or the character used as the decimal separator as set in the Control Panel) between the digits. An example would be 12.125 or 44.80. Like an integer, a decimal number can start with a + or just a digit, which would make it a positive number. A decimal number can also start with a - symbol, which would make it a negative number. If the number represents a fraction, a period between the digits specifies what portion of 1 was cut. If you anticipate such a number for a field, specify its data type as numeric or decimal (either decimal or numeric would produce the same effect in SQL Server).

float and real: A floating-point number is a fractional number, like the decimal type. Floating-point numbers can be used if you would allow the database engine to apply an approximation to the actual number that a field is supposed to carry.

money: As its name announces it, the money data type can be used on a column whose data would consist of currency values. A field with a money data type can hold positive or negative values from -922,337,203,685,477.5808 to +922,337,203,685,477.5807

smallmoney: While the money data type can be used for a field that would hold large quantities of currency values, the smallmoney data type can be applied for a column whose value cannot be lower than -214,748.3648 nor higher than 214,748.3647

char: A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a column to hold a fixed number of characters, such as the book shelf numbers of a library, use the char data type for such a column.

varchar: In the computer world, a string is a character or a combination of characters that are considered "as is" with regards to the scenario in which they are used. In a table, some columns are meant to hold such strings. One of the most significant characteristics of such a field is that data entered into it can change from one record to another. An example would be the first names of employees: people have different first names of different lengths. If a column will hold strings of different lengths, apply a varchar data type to it. The maximum length of text that a field of varchar type can hold is equivalent to 8 kilobytes.

text: The text data type can be applied to a field whose data would consist of ASCII characters. As opposed to a varchar type of field, a text type of field can hold text that is longer than 8 kilobytes.

nchar, nvarchar, and ntext: These three types follow the same rules as the char, varchar, and text respectively, except that they can be applied to columns that would hold international characters, that is, characters of languages other than US English. This is done following the rules of Unicode formats.

datetime: As its name suggests, a datetime data type is used for a column whose data would consist of date and/or time values. The entries must be valid date or time values but SQL Server allows a lot of flexibility, even to display a date in a non-traditional format. The date value of a datetime field can be comprised between January 1st, 1753 and December 31, 9999.

smalldatetime: The smalldatetime is an alternative to the datetime data type. It follows the same rules and principles as the datetime data type except that a date value must be comprised between January 1st, 1900 and June 6, 2079.

Practical Learning: Setting Data Types

1. Click the empty field under the Data Type column and notice that it is a combo box. Also notice that the char data type is selected as default
2. Click the arrow of the combo box, scroll down and select varchar from the list
3. Click the first empty field under AssetType and type Make
4. Set its Data Type also to varchar
5. Complete the table as follows:
    

   Column Name	Data Type
   AssetType	varchar
   Make	varchar
   Model	varchar
   DateAcquired	smalldatetime
   PurchasePrice	smallmoney
   Notes	text

The Length of Data

A database deals with various types of data, appropriate or not for certain fields. This means that you should take care of jobs behind the scenes as much as you can. One way you can do this is by controlling the amount of information that can be entered in a particular field. As various columns can hold different types of data, so can the same data type control its own mechanism of internal data entry. The length of data means different things to different fields. Columns that carry the same data type can have different lengths.

Bit Fields: We saw already that a bit column type is meant for one of two answers. The user is supposed to simply let the database know that the answer is yes or no, true or false, on or off, 1 or 0. Therefore, the only length of this field is 1.

Integers: The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

Decimal and Floating-Point Numbers: The Length specifies how many bytes the field can store.

Strings: The Length of a character or string column specifies the maximum number of characters that the field can hold.

In some circumstances, you will need to change or specify the length as it applies to a particular field. For example, since you should use the varchar data type for a string field whose content will change from one record to another, not all varchar columns need to have the same length. Although a First Name and a Book Title columns should use the varchar type, both columns would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both fields would use the same data type but different lengths. On the other hand, for columns of datetime and money data types, you should accept the default length suggested by the database. 

Practical Learning: Setting Data Types

1. Double-click the corresponding Length field for the AssetType to highlight it and type 20
2. Press the down arrow key and type 32 for the Length of the Make field
3. Press the down arrow key and type 40 for the Length of the Model:
    

   Column Name	Data Type	Length
   AssetType	varchar	20
   Make	varchar	32
   Model	varchar	40
   DateAcquired	smalldatetime
   PurchasePrice	smallmoney
   Notes	text

The Nullity of a Field

During data entry, users of your database will face fields that expect data. Sometimes, for one reason or another, data will not be available for a particular field. An example would be an MI (middle initial) field: some people have a middle initial, some others either don't have it or would not provide it to the user. This aspect can occur for any field of your table. Therefore, you should think of a way to deal with it.

A field is referred to as null when no data entry has been made to it:

• Saying that a field is null doesn't mean that it contains 0 because 0 is a value.
• Saying that a field is null doesn't mean that it is empty. A field being empty could mean that the user had deleted its content or that the field itself would not accept what the user was trying to enter into that field, but an empty field can have a value.

A field is referred to as null if there is no way of determining the value of its content (in reality, the computer, that is, the operating system, has its own internal mechanism of verifying the value of a field) or its value is simply unknown. As you can see, it is not a good idea to have a null field in your table. As a database developer, it is your responsibility to always know with certainty the value held by each field of your table. Remember that even if a field is empty, you should know what value it is holding because being empty could certainly mean that the field has a value.

To solve the problem of null values, SQL Server proposes one of two options: allow or not allow null values on a field. For a typical table, there are pieces of information that the user should make sure to enter; otherwise, her data entry would not be validated. To make sure the user always fills out a certain field before moving to the next field, you must make sure the field doesn't allow null values; this will ensure that you know that the field is holding a value and you can find out what that value is. This is enforced by clearing the Allow Nulls check box for a field. On the other hand, if the value of a field is not particularly important, for example if you don't intend to involve that value in an algebraic operation, you can allow the user to leave it null. This is done by checking the Allow Nulls check box for the field.

Practical Learning: Applying Fields Nullity

• To apply the nullity of fields, change the table as follows:
   

  Column Name	Data Type	Length	All Nulls
  AssetType	varchar	20	Clear
  Make	varchar	32	Clear
  Model	varchar	40
  DateAcquired	smalldatetime		Clear
  PurchasePrice	smallmoney
  Notes	text

Tables Names

While or after creating a table, you should save it. If you are freshly creating a table and decide to save it, you would be prompted to name it. The name of a table:

• Can be made of digits only. For example you can have a table called 148
• Can start with a digit, a letter, or an underscore
• Can be made of letters, digits, and spaces

Besides these rules, you can make up yours. On this tutorial, the name of a table

• will start with a letter in uppercase. Examples are Employees, Accounts
• if made of a combination of words, will have each component start in uppercase. Examples are BookCategories, CustomersBankAccounts.

Practical Learning: Naming a Table

1. To save your table, on the toolbar of the table, click the Save button
2. In the Choose Name dialog box, type CompanyAssets
    
   
3. Press Enter
4. After saving the table, close it

Tables Relationships and Data Integrity

Introduction

A relational database is a system in which information flows from one database object to another. For example, on a bank database, you can use one object to create accounts for customers and use another object to process transactions that the owners of bank accounts need. The reason is that the same customer may need to perform various transactions, regularly. Instead of creating a new account every time the customer wants to perform a new transaction, you can use one account as a reference and bring up this account whenever the customer wants to deposit or withdraw money.

Practical Learning: Introducing Relationships

1. In Server Explorer, under the CIC1 node, right-click the Tables node and click New -> Table
2. Specify the first Column Name as ContainerID, set its Data Type to int
3. Set the second Column Name to Container
4. Set its Data Type to varchar and click its Allow Nulls field
5. Save the table as Containers

The Primary Key

The transactions among various objects of a database should make sure information of one object is accessible to another object. The objects that hold information, as we have mentioned already, are the tables.

To manage the flow of information from one table (A) to another table (B), the table that holds the information, A, must make it available to other tables, such as B. There are two issues that must be dealt with:

1. Each record that a table (A) holds should/must be unique among all the other records of the same table (A). For example, if you create a list of bank accounts for different customers on a table, you should make sure that there is a unique (no duplicate) bank account number for each customer because each customer should have one and must have one account number. This ensures that there are no duplicate records on the table.
2. A table (A) that holds information must make it available to other tables (such as B). Two tables must not serve the same purpose. Once you have unique information on each table, one table can make its data available to other tables that need it.

These two problems are solved by specifying a particular column as the "key" of the table. Such a column is referred to as the primary key.

In a relational database, which is the case for most of the databases you will be creating in SQL Server, each table should have at least one primary key. As an example, a primary key on an Account table of a bank database can be set on a Bank Account field because each customer should have a unique bank account number. A table can also have more than one primary key if you judge it necessary.

Once you have decided that a table will have a primary key, you must decide what type of data that field will hold. If you are building a table that can use a known and obvious field as unique, an example would be the shelf number of a library, you can set its data type as char or varchar and make it a primary key. In many other cases, for example if you cannot decide on a particular field that would hold unique information, an example would be customers Contact Name, you should create your own unique field and make it the Primary Key constraint. Such a field should have an int data type.

To specify a primary key on a table, you create one column as the PRIMARY KEY constraint and there can be only one PRIMARY KEY constraint on a table. To do this in Enterprise Manager, create a column and specify its data type. Then, on the toolbar, click the Set Primary Key button .

To create a primary column using SQL, on the right side of the column definition, type PRIMARY KEY.

Practical Learning: Setting a Primary Key

1. To make the first column the Primary Key constraint, right-click ContainerID and click Set Set Primary Key
2. Notice that the Allows Nulls check mark is removed
   While the field is still selected, in the bottom section of the Design Table, set the Identity field to Yes and accept the Identity Seed and the Identity Increment to 1 each
3. Save the table and close it
4. In Server Explorer, under CIC1, double-click Containers
5. Under the Container column header, type Cone and press the down arrow key
6. Type Cup and press the down arrow key
7. Type Bowl and press Enter
8. Close the Containers table 
9. In Server Explorer, right-click the Tables node under CIC1 and click New Table
10. For the first Column Name, type FlavorID and press Tab
11. Set its Data Type to int
12. While the column still has focus, on the Table toolbar, click the Set Primary Key button . In the lower section of the table, set the Identity combo box to Yes
13. Set the second Column Name to Flavor and its Data Type to varchar. Set its Length to 30 and clear its Allow Nulls field
14. Set the third Column Name to Composition and its Data Type to Text
15. Save the table as Flavors and close it
16. In Server Explorer, under the Tables node under CIC1, double-click Flavors and fill it up with the following values:
     

    Flavor	Composition
    Vanilla	Vanilla Extract, Milk, Sugar, Flour, Egg Yolks, Ice Cream Freezer, Salt
    Cream of Cocoa	Dry Powdered Cocoa, Vanilla, Salt, Sugar, Cornstarch, Milk, Syrup
    Chocolate Chip	Cocoa, Unsweetened Chocolate, Whole Milk, Evaporated Milk, Vanilla, Sugar, Eggs, Salt
    Organic Strawberry	Strawberry, Vanilla, Almond, Sugar, All-Purpose Flour, Salt, Milk, Eggs
    Butter Pecan	Vanilla Extract, Toasted Chopped Pecan, Light Cream, Brown Sugar, Butter
    Banana Coconut	Shredded Coconut, Kosher Gelatin, Guava, Honey, Vanilla Soy Milk, Mashed Bananas

17. Close the Flavors table 
18. In Server Explorer, right-click the Tables node under CIC1 and click New Table
19. Set its first Column Name to IngredientID
20. Set its Data Type to int
21. Right-click the new column and click Set Primary Key button . In the lower section of the table, set the Identity combo box to Yes
22. Set the second Column Name to Ingredient and its Data Type to varchar. Also, clear its Allow Nulls
23. Close the table
24. When asked whether you want to save the table, click Yes
25. Type Ingredients and press Enter
26. In Server Explorer, under the Tables node of CIC1, double-click Ingredients
27. Under the Ingredient column header, type No Ingredient and press the down arrow key
28. Type Peanuts and press the down arrow key
29. Type M & M and press Enter twice
30. Type Cookies and press Enter
31. Close the Ingredients table 
32. In the Server Explorer window, under CIC1, under the Tables node, right-click CompanyAssets and click Design Table
33. To add a primary key to the CompanyAssets table, in the table, right-click AssetType and click InsertColumn
34. Type AssetID
35. Set its Data Type to int
36. Right-click AssetID and click Set Primary Key
37. In the lower section of the table, set its Identity to Yes
38. Save and close the table
39. In Server Explorer, double-click CompanyAssets to open it
40. Enter a few records as follows:
     

    AssetType	Make	Model	Date Acquired	Purchase Price	Notes
    Printer	HP	LaserJet 4200dtn	10/08/02	1950.95	B/W Printer used internally in the office
    Ice Maker	Scotcher	S-65G	10/08/2002	1850.95
    Computer	IBM	NetVista M42	10/08/2002	1035.00	Desktop computer used internally in the office
    Ice cream Mixer	Bobel Systems	BBL36	10/08/2002	18850.00
    POS System	DDP	AltaPOS 70DS	10/22/2002	950.55	Point of Sale System
    POS System	DDP	AltaPos 70DS	10/22/2002	950.55	Point of Sale System, mostly used for high sale shifts
    Ice Cream Freezer	Emery Thompson	2HSC-W	10/24/2002	14500.00	Machine used to quickly generate an Ice Cream for a customer
    Digital Camera	Olympus	C-50	11/06/2002	450.75	Used to take pictures of products on sale
    Laptop	Gateway	200XL	12/05/2002	2095.95	Business notebook
    Ice Cream Freezer	Technogel	BF-50	12/28/2002	18500.00

41. Close the dbo.CompanyAssets window

Foreign Keys

A foreign key is a column on a table whose data is coming from another table. As mentioned above for the primary key. Imagine you want to perform transactions for an ice cream shop. When a customer places an order, she must specify the type of container (cup, cone, or bowl which exist in their own table). To make this happen, you can create a table for orders. In that table, you can create a column that would represent containers so the user would not have to type the name of a container. The clerk would simply select one. Therefore, on the table for orders, the column that represents containers is called a foreign key.

To create a foreign key, the table you want to link to must have a PRIMARY KEY constraint. In the current table, you must add a column referred to as a FOREIGN KEY constraint. The new column in this table should have the same name as the primary key column of the other table.

Practical Learning: Creating Foreign Keys

1. In Server Explorer, right-click the Tables node under CIC1 and click New Table
2. Fill it up as follows:
    

   Column Name	Data Type	Length	Allow Nulls	Properties
   OrderID	int	4		Identity: Yes
   OrderDate	smalldatetime	4	Checked
   OrderTime	smalldatetime	4	Checked
   ContainerID	int	4		Default Value: 1
   FlavorID	int	4		Default Value: 1
   Scoops	smallint	2		Default Value: 1
   IngredientID	int	4		Default Value: 1

3. Right-click OrderID and click Set Primary Key
4. Save the table as Orders and leave it open in Design View

Tables Relationships

To help information flow from one table to another, there must be a relationship between both tables. One table that holds information would supply it to the other table. The table that holds data is considered the parent and must have a primary key column. The table that request the information must have a foreign key that corresponds to the other's primary key.

Practical Learning: Creating a Relationship

1. Right-click somewhere in the table and click Relationships... 
2. In the Relationships Property Pages, click the New button
3. In the Primary Key Table combo box, select Containers
4. In the first combo box of the grid under Containers, select ContainerID
5. In the Foreign Key Table combo box, Orders should be selected already. Otherwise, select it.
   Click the grid under Orders to reveal a combo box. In the combo box, select ContainerID
    
   
6. Click Close
7. Close the table. When asked to save, click Yes and Yes

Diagrams

A diagram is a window that visually displays the relationships among tables of a database. To create a diagram, use the Create Database Diagram Wizard.

Practical Learning: Creating a Diagram

1. In Server Explorer, under the CIC1 database, right-click Database Diagrams and click New Diagram
2. In the Tables page of the Add Table dialog box, click Containers and click the Add button
3. In the same way, click Flavors and click Add
4. Double-click Ingredients and double-click Orders
    
   
5. On the Add Table property sheet, click Close
6. To move a table, you drag its title bar.
   Position the Containers and the Ingredients tables to the left of Orders and position Flavors to the right of Orders
7. Click the gray box on the left of the IngredientID field in the Ingredients table.
   Drag that box right and drop it on the IngredientID field of the Orders table:
    
   
8. On the Create Relationship dialog box, make sure IngredientID is selected for the Ingredients Primary Key Table and that IngredientID is selected for the Orders Foreign Key Table
9. Click the Cascade Update Related Fields and the Cascade Delete Related Fields check boxes
    
   
10. Click OK
11. In the same way, drag the FlavorID row button from Flavors to the FlavorID field on Orders.
12. On the Create Relationship dialog box, make sure FlavorID is selected for the Flavors Primary Key Table and that FlavorID is selected for the Orders Foreign Key Table.
    Click the Cascade Update Related Fields and the Cascade Delete Related Fields check boxes
     
    
13. Click OK
     
    
14. Close the Diagram window
15. When asked whether you want to save the database diagram, click Yes
16. Type CustOrders and click OK
17. Click Yes to save the database