ics151-hour13

Transcript

1. ICS 151 Introduction to Databases Instructor: William McDaniel Albritton Slides based on Sams Teach Yourself ASP.NET 4 in 24 Hours, Complete Starter Kit by Scott Mitchell
2. What is a Database?  Basically, a database is a place to store data  Specifically, a database is collection of structured information that can be efficiently accessed and modified  A database has four types of operations 1. Retrieve data from the database 2. Insert data into the database 3. Modify data in the database 4. Delete data from the database
3. Setting up the Database  Before we use a database to store data, we have to set up the database with these steps: 1. Install the database software 2. Create the database file 3. Define the structure of the data in the database
4. Kinds of Databases  Commercial databases for large companies  Microsoft SQL Server  Oracle  IBM’s DB2  Microsoft Access  IBM’s Informix  Free databases for amateur developers  PostgreSQL  MySQL  SQLite  Microsoft SQL Server 2008 Express Edition
5. Database Structure  Data is structured in a database by putting the data into tables  Each table has a unique name  Tables are arranged into columns and rows  Each column is used to store one attribute of the data  Columns are also referred to as fields  Each row is used to store a complete data item  Rows are also referred to as records
6. Database Example  For example, let’s make a database to store information about customers  Name of database: CUSTOMERS  Customer attributes: name of customer, phone number, zip code, and date of first purchase  Customer attributes are columns in database: Name Phone Zip FirstPurchase
7. Database Example  Here is the CUSTOMERS table with 5 records for the 5 customers Name Phone Zip FirstPurchase Misae Nohara 808-333-4444 96816 January 10, 2005 Satsuki Kusakabe 808-222-5555 96819 April 22, 2001 Hattori Hanzo 808-111-2222 96812 June 7, 2002 Kosaku Shima 808-555-7777 96817 July 1, 2007 Chihiro Ogino 808-888-9999 96819 October 3, 2003
8. Data Types  Just like the variables in Visual Basic, each column of data has a specific data type  Name column has data type nvarchar(50)  Phone column has data type nvarchar(12)  Zip column has data type nvarchar(5)  FirstPurchase column has data type date  To see a complete list of SQL Server Data Types and Ranges for the columns (fields), see the Data Types and Update 2008 links on the class web page
9. Data Types  Common data types for database columns (fields) Column data type Description Visual Basic parallel nvarchar(n) A string of up to n characters in length String int A whole number Integer bit 0 (zero) or 1 (one) Boolean datetime Year, month, day and time DateTime money Monetary value Decimal float Floating-point number Decimal
10. Data Types  What is the difference between string data types nvarchar, nchar, varchar, and char?  var is short for “variable length”  nvarchar(20) will take up only the amount of space that is needed, up to 20 characters  nchar(20) or char(20) will always take up 20 characters, whether the word is shorter or not  n is short of “Unicode”  Unicode has a much greater range of characters than ASCII characters
11. Unicode vs. ASCII  Modern character standard now uses Unicode to encode all the languages in the world with up to 32 bits  See the Unicode link on the class webpage to see a really cool chart of all the languages in the world!  Older character standard gives each character a numeric code called an ASCII code (American Standard Code for Information Interchange) with 8 bits  See the ASCII link on the class website for an example
12. Null Value  If data for a particular row (record) and column (field, attribute) is missing, the Null value is used  Null is a marker for “nothing” or “unknown” value  For example, if customers Hattori Hanzo and Chihiro Ogino do not have phones, Null values are entered Name Phone Zip FirstPurchase Misae Nohara 808-333-4444 96816 January 10, 2005 Satsuki Kusakabe 808-222-5555 96819 April 22, 2001 Hattori Hanzo Null 96812 June 7, 2002 Kosaku Shima 808-555-7777 96817 July 1, 2007 Chihiro Ogino Null 96819 October 3, 2003
13. Primary Key Column  One of the columns (fields, attributes) in a table is the primary key columns  The primary key uniquely identifies each record (row) in a database  Usually a column of type int with unique values  Typically has the name TableNameID  Can be auto-incremented (automatic counter), so that the value is always unique and automatically set when a new record is added to the table
14. Changes to Table  Here is the CUSTOMERS table with the primary key column with auto-increment CustomerID Name Phone Zip FirstPurchase 1 Misae Nohara 808-333-4444 96816 January 10, 2005 2 Satsuki Kusakabe 808-222-5555 96819 April 22, 2001 3 Hattori Hanzo Null 96812 June 7, 2002 4 Kosaku Shima 808-555-7777 96817 July 1, 2007 5 Chihiro Ogino Null 96819 October 3, 2003
15. Create a New Database  Steps for creating a database with SQL server 1. Create a new website with Visual Web Developer 2. In the Solution Explorer window, right-click on the name of the website 3. Click Add New Item 4. Click SQL Server Database 5. Change the name to MyDatabase.mdf 6. You should see the Database Explorer window, if not, click View > Other Windows > Database Explorer 7. When it asks to create the App_Data folder, click Yes
16. Book Collection  Let’s make a database to store information about your book collection  Here are the book attributes, which will be the columns (fields) of the database table: 1. Title of type nvarchar(50), no Nulls 2. Author of type nvarchar(50), no Nulls 3. Year of type int, no Nulls 4. Price of type money, no Nulls 5. LastRead of type datetime, Nulls allowed 6. Pages of type int, no Nulls
17. Create a New Table  Steps for creating a database table 1. In the Database Explorer window, under MyDatabase.mdf, right click on Tables 2. Click Add New Table 3. Fill in the Column Name, Data Type, and Allow Nulls checkbox for all the attributes
18. Primary Keys  In order to access, update, or delete a record, each record (row) must have a unique identity  Usually this is done by using an auto-increment primary key column (field)
19. Add a Primary Key  To add an auto-increment primary key column: 1. Right click on the 1st row (1st column in database) 2. Click Insert Column 3. Column name is BookID 4. Data type is int 5. Allow no Nulls 6. Click the 1st row (1st column in database) 7. Click the primary key icon (a yellow key just above the table)
20. Add a Primary Key  To add an auto-increment primary key column (continued): 8. Click the row again 9. Go to the Column Properties window just below the table 10. Expand the Identify Specification property by clicking on the plus sign (+) 11. For the (Is Identity) property, change the value to Yes 12. Click Save to save the file 13. Change the table name to BOOKS
21. Change & Save Database  To get rid of error message 1. Tools 2. Options 3. Show all settings 4. Database tools (expand it) 5. Table and database designers 6. Prevent Saving Changes that require table recreatoin (unclick)
22. Add Data to the Table  Steps to add data to the table 1. Go to the Database Explorer window 2. Expand the Tables folder, by clicking on the plus sign (+) 3. Right click on the Books table 4. Click Show Table Data 5. Click on the cell in the 1st row, BookID column 6. Use the Tab key to more to the other fields (columns) 7. Type in the data for the 5 books listed in the slides 8. Add your own 10 books (so 15 total records) 9. Use real data (do not makeup book titles, etc.)
23. Entering Table Data  The data has to be the correct data type  If the column does not accept Null values, then you have to put in a value  If the column does accept Null values, then you can leave it blank or fill it in  The red exclamation point just means that the cell’s value has changed  Once you tab to the next record, then the previous record is written to the database, and the red exclamation points disappear
24. BOOKS Table  Create your BOOKS table with the same column titles as the table below, plus 10 more records of your own BookID Title Author Year Price LastRead Pages 1 Alice's Adventures in Wonderland Lewis Carroll 1865 3.95 NULL 240 2 The Grapes of Wrath John Steinbeck 1939 13.29 10/31/1983 672 3 Uncle Tom's Cabin Harriet Beecher Stowe 1852 39.99 NULL 384 4 Animal Farm George Orwell 1945 9.99 4/1/1986 176 5 Gulliver's Travels Jonathan Swift 1726 8.00 NULL 336