| Now that you have a grasp of the database fundamentals, let's get started building your first database. First, you'll develop one in Microsoft Access 2000, and then you'll move to Microsoft SQL Server 2000, where you'll see the many similarities (and differences!) between these two database systems. You'll be using Access for most of these examples because it's easier to learn. SQL Server is much more common in real-world situations that require database systems, though, so there's a short discussion on it. You'll use the database you build here in subsequent lessons, so let's design something useful. For starters, create a user table for storing the following information: First name Last name Address City State ZIP code Phone number This is a typical user database and can be applied to many different applications. To start your database in Access, run Access and select New from the File menu. You should see something similar to Figure 8.4. Select Blank Database, name it banking.mdb, and save it in the c:\ASPNET\data directory. (You can use any directory you choose just be aware that the examples in this book will refer to this directory.) Figure 8.4. Creating a new, blank database.
You should see the following options: Create table in Design view, Create table by using wizard, and Create table by entering data. Choose Design view and start entering your columns. Enter FirstName in the Field Name column, and select Text in the Data Type column. Optionally, you can enter a description for this field, such as "User's first name." Enter the other fields described in the previous listing, and you should end up with something similar to Figure 8.5. Figure 8.5. Enter the field information for the table.
Note Note that the available options may be different depending on the version of Access you're using.
Finally, add one more column named UserID, which will be your identity column. Assign it the data type Autonumber and right-click on the black arrow next to its name. Select Primary Key from the menu and notice how the key symbol appears next to the name. Next, simply save the table by choosing File, Save from the menu. Save the table as tblUsers. Choose File, Close from menus to close tblUsers. Tip If you want to define a primary key over more than one column, select each column by holding down the Ctrl key, and right-click to select Primary Key from the menu.
That's all there is to it! You can now add data simply by double-clicking on the tblUsers option that appears on your menu. Notice that when you input data, you don't need to enter a value for the UserID column. It inserts and increments a number automatically, as shown in Figure 8.6. Figure 8.6. Enter data into your new table.
Table 8.1 lists the various data types provided in Access 2000. Similar data types are present in all database systems, although the exact names may be different. Table 8.1. Access 2000 Data Types | Data Type | Description | | Autonumber | An automatically incremented integer used to uniquely identify rows in a table. | | Currency | Used to store monetary data. | | Date/Time | Used to store date and time values. Access 2000 can store dates between the years 100 and 9999. | | Memo | Used to store up to 65,535 alphanumeric characters. | | Number | Used to store numeric values. | | Text | Used to store up to 255 alphanumeric characters. | | Yes/No | Used for columns that can only have one of two values (similar to bit or boolean types). | Things are a bit more complicated in SQL Server 2000, but the procedure is essentially the same. Access will be appropriate for your needs, but typically, SQL Server is used for larger databases that require more security and stability. Now let's create tblUser in Microsoft SQL Server 2000. Start by opening up Enterprise Manager from the SQL Server 2000 menu group in your Start menu. If everything is set up properly, you should see a Windows Explorer like navigation system, with Microsoft SQL Servers at the top and the name of your server a couple levels below. Expand the databases folder, as shown in Figure 8.7, and select New Database from the Action menu. Figure 8.7. Expand the databases folder and select New Database from the Action menu.
Enter the name Banking in the dialog box that pops up. For now, simply accept the default options under the General tab and click OK. SQL Server will create the database and transaction log for you. You should now see Banking under the Databases folder expand this tab and select the Tables node. You'll see that SQL Server has already inserted quite a few tables for you. These are system tables used by SQL Server to keep track of the items you place in your database. Ignore these for now and select New Table from the Action menu. You get a matrix similar to that in Access. Enter the information again, as shown in Figure 8.8, except use the varchar data type instead of Text (which has a different meaning in SQL Server). Figure 8.8. Enter field names and data types.
Notice that SQL Server provides many more data types than Access did. SQL Server is simply a more powerful application and allows developers to specify the database options with much greater granularity. SQL Server doesn't require you to initially set up a primary key but let's define one now. Create a new column with the name UserID and set it to a data type of int. In the Columns panel below, select Yes for the Identity property (see Figure 8.9). Then right-click this field and select Set Primary Key. Next, save this table as tblUsers and close this design view by clicking the x in the upper-right corner. Figure 8.9. Set the identity field and primary key.
To enter data into your new table, right-click the table and select Open Table, Return All Rows. This will present you with a tabular data entry mechanism similar to that of Access. Enter a few names and addresses for fun. (Don't enter anything in the UserID field.) Click the exclamation point (Run) on the menu to autoincrement the UserIDs. Close the new table by clicking x in the upper-right corner. | Do | Don't | | Do use SQL Server if you're developing a large, performance-critical database solution that requires advanced database functionality. | Don't use SQL Server for very simple databases with only a few tables. It introduces complications that might not be necessary. | |
| 
