1 / 69

BMIS 289: Spring 2002 Gonzaga University

BMIS 289: Spring 2002 Gonzaga University Class # 09 Introduction to Databases & ASP Agenda Program 7 Database Basics MS Data Access Model Introduction To MS Access ADO Object Model Basic ADO & ASP Example Program 8 Overview Program 7 This program was a text file processor:

paul2
Download Presentation

BMIS 289: Spring 2002 Gonzaga University

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BMIS 289: Spring 2002Gonzaga University Class # 09 Introduction to Databases & ASP

  2. Agenda • Program 7 • Database Basics • MS Data Access Model • Introduction To MS Access • ADO Object Model • Basic ADO & ASP Example • Program 8 Overview BMIS 289: Spring 2002

  3. Program 7 • This program was a text file processor: • Individual dat files are used to input employee records. • Format of dat files: • <name>,<job title>,<hire date>,<salary> • User selects dat file by browsing from HTML file selector: • NOTE: in the case of this program we are assuming the user of this program is browsing off the server, otherwise the ASP code cannot read the dat file’s contents. BMIS 289: Spring 2002

  4. Program 7 • Once we have a path to a dat file we can begin processing it. • We use the TextStream object to read the contents of the dat file: • The ReadLine method of the TextStream object returns an individual line from a text file as a string. • We then need to parse out the individual data in each string. BMIS 289: Spring 2002

  5. Program 7 • Parsing information out of a string: • We know the format of the string (i.e., there are 4 pieces of information in each line delimited by commas). • The easiest way to get at each element of information is to use the Split function. • The Split function takes a string and delimiter as arguments. • It returns an array containing each individual element in the string, based on the delimiter. BMIS 289: Spring 2002

  6. Program 7 • Computing number of days employee has been with the company: • One of the pieces of information in the dat file records (individual lines of information) is the date the employee was hired. • A separate ASP file was provided to you that has one function in it: DateDiffDays. • This ASP file must be “included” in program 7’s processing page in order to access that function. BMIS 289: Spring 2002

  7. Program 7 • The main processing loop will perform these steps: • ReadLine from TextStream. • Parse information out of line using Split into an array. • Output employee information. • Calculate number of days employee has been with company using DateDiffDays. • Increment employee counter. BMIS 289: Spring 2002

  8. Intro To Databases • The database is a fundamental information management concept. • A database is nothing more than a collection of related information (like a table or a list). • Nearly every major program ever written uses a database of some form or another, it is that critical. • Databases let us, as programmers, create, access, and manipulate large collections of information in a quick and efficient manner. BMIS 289: Spring 2002

  9. Intro To Databases Cont. • From the basic database concept the idea of a relational database was developed. • A relational database is “set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables.” • The Structured Query Language (SQL) is used to access and manipulate relational databases. • One of the most popular relational database management systems out there is Microsoft Access, which we will use in this class. BMIS 289: Spring 2002

  10. Why Use Databases? • Quite simply, databases help us organize pieces of information. • Databases pull together extraneous bits of data and force humans to organize it into some meaningful relationship. • For programmers, they provide efficient, convenient access to structured collections of data. BMIS 289: Spring 2002

  11. Databases In Web Applications • E-Commerce: • Products • Customers • Logins • Intranets • Time sheets • Expense reports • Sales information • Misc • Music collections • Wedding guest lists • Personal finances BMIS 289: Spring 2002

  12. Basic Database Elements • Tables • Records • Fields • Keys BMIS 289: Spring 2002

  13. Table • A table is an individual collection of information within a database. • Each table should represent a single idea, object, or thing. • For example, a “Employee” table should not include information about the company’s quarterly earnings. • A database is made up of one or more tables. BMIS 289: Spring 2002

  14. Record • Think of a record as a single row in a table. • If you are a student at Gonzaga then you have one record in the Students table of the GU database. • Just as a table has many records, a record has many bits of information associated with it. • Those bits of record information are fields. BMIS 289: Spring 2002

  15. Field • Each individual piece of information in a record is one field. • If a record is considered a row, then a field is a column, or, in each instance of a record, a cell. • Every record contains one instance of each field. • A field is defined by its name and its data type. • For example, a user name field might be called “Uname” and have a string data type. • In code, Fields are synonymous with variables. BMIS 289: Spring 2002

  16. Pulling It All Together Table Record Field BMIS 289: Spring 2002

  17. Keys • One problem we have with databases is how can we differentiate between records? • For example, there may be more than one employee in a given company named John Smith. • The answer is to use Keys. • A key is some sort of identification number/string that is unique to a particular record. • A key that uniquely identifies a record is called a primary key. • No two records may have the same primary key within a given table. BMIS 289: Spring 2002

  18. Foreign Keys • In some cases, primary keys of one table can be used in other tables to link data between records (a.k.a., create relationships among data). • For example, an e-commerce web site may have a Customer and Purchase table. • Customers and Purchases are uniquely identified via primary keys called CustID and PurchID respectively. • The Purchase table would contain a CustID field that would only accept values from the Customer table. • Thus the field Purchase.CustID would allow us to trace an individual purchase back to a particular customer. BMIS 289: Spring 2002

  19. Illustrating Foreign Keys Link with a Product table BMIS 289: Spring 2002

  20. Microsoft Data Access Model • Microsoft is a proponent of a technology it calls Universal Data Access (UDA) • UDA is a common way of accessing data stored in various formats, like Microsoft Access, Oracle, and even Microsoft Word. • Microsoft calls these heterogeneous data sources “data stores.” • The nice thing about the UDA concept is it allows us to access a wide array of data stores using a single programming interface. • So the code to fetch a record from Oracle residing on Unix is (for the most part) the same as it is for Microsoft Access running on Windows 98. BMIS 289: Spring 2002

  21. Microsoft Data Access Model • ODBC • Open Database Connectivity • An earlier Microsoft attempt at UDA, it worked on many different database packages but that was it. Application ODBC Oracle SQLServer Access BMIS 289: Spring 2002

  22. Microsoft Data Access Model • OLE-DB • Microsoft’s next attempt at UDA. • It is a lower level interface that provides access to more than just databases (i.e., data stores) • It is faster and more efficient than ODBC. Application OLE-DB ODBC Oracle Access Excel MSExchange ODBCData BMIS 289: Spring 2002

  23. Microsoft Data Access Model • Data Provider vs. Data Consumer • Microsoft thinks of data access in terms of providers and consumers. • Data Provider • Something that provides data • For example, OLE-DB • And in OLE-DB there are various providers for specific data store systems. • Data Consumer • Something that uses data • For example, ASP pages BMIS 289: Spring 2002

  24. Microsoft Data Access Model • ADO • ActiveX Data Objects • ADO is an application programming interface between a data consumer and OLE-DB (the provider) • It has a set of objects which encapsulate several common actions we can take on databases. • ASP & ADO are not the same • Many people tend to think of ASP & ADO as one and the same. • They are not. • ADO is a separate technology that can be used in ASP. • It can also be used in Visual Basic or a C++ windows application, to name a few. BMIS 289: Spring 2002

  25. Microsoft Data Access Model • MDAC • Microsoft Data Access Components (MDAC) • ADO is part of the MDAC, which is Microsoft’s free suite of data access components for use by programmers. • You can obtain the latest versions at www.microsoft.com/data (and our class web site) • NOTE: the most current version (as of this class) of MDAC is 2.7. At the time of your books writing it was 2.5. BMIS 289: Spring 2002

  26. Database Software • As we have mentioned, there are various software packages available that provide relational database functionality. • There are four major database packages we will briefly examine: • Microsoft Access • Microsoft SQL Server • Oracle • MySQL BMIS 289: Spring 2002

  27. Microsoft Access • Access is what is known as a desktop database. • It is designed to be user friendly and run on individual client machines, rather than act as a backend on a server. • It has many features for making the creation of custom queries, reports, and even applications based upon its data very easy. • Access can act like a backend database but it really was not designed to be one. BMIS 289: Spring 2002

  28. Microsoft SQL Server • A powerful database system that is designed to run as a backend database on a server. • SQL server can hold a lot of data and has numerous features that make it more faster, robust, and secure than Microsoft Access. • It is more expensive than Microsoft Access and not as easy to use or maintain. • For very large applications that have many users SQL Server is more appropriate than Access. BMIS 289: Spring 2002

  29. Oracle • Oracle was one of the first “enterprise strength” relational databases. • It competes with large relational database packages like SQL Server and MySQL. • Oracle is extremely powerful with the ability to handle very large volumes of data at a fast rate. • Generally considered to be more complex and less user friendly than SQL Server. BMIS 289: Spring 2002

  30. MySQL • MySQL is an enterprise relational database like Oracle and SQL Server that runs primarily on the Linux platform. • Though it can run on Windows NT. • MySQL is open source and free for public use. • There are commercial licensed versions that can be purchased. • MySQL is powerful but can be difficult to use. BMIS 289: Spring 2002

  31. MSDE vs. Access • In the book (starting on pg. 472) the authors discuss the option of using MSDE over Access. • MSDE is a light-weight version of Microsoft’s industrial strength database product: SQL Server. • We will use Access for all our database projects in this class. • If you are interested in exploring more about MSDE and SQL Server, however, you may follow some of the examples in the book. BMIS 289: Spring 2002

  32. Break BMIS 289: Spring 2002

  33. Access 2000 Tutorial • Creating a new Database • Creating Tables • Defining Fields • Entering Data BMIS 289: Spring 2002

  34. Creating A New Database • Start Access. A wizard screen will appear. Select “Blank Access Database” and click “OK”. • Enter a name for thedatabase file. BMIS 289: Spring 2002

  35. Creating A New Table • With “Tables” selected in the database window double-click “Create Table In Design View” Database Window BMIS 289: Spring 2002

  36. Creating A Table • The window that appears is a sheet for defining fields within the table. This view is known as the “design view.” • Enter the field name, select its data type and set any special properties for the field. Fields Field Properties BMIS 289: Spring 2002

  37. Creating A Table – Field Types • A field can have several data types in Access. Here are the more common ones: • Autonumber: used primarily for record ids. Numeric values automatically generated by Access. For example the first records would have a value of 1, the next 2, the next 3 and so on. • Text: used for textual data. Has a max size limit of 255. • Number: numeric values. • Yes/No: a boolean field (the only possible values are true/false). • Date/Time: used to hold dates and/or times. BMIS 289: Spring 2002

  38. Create A Table – Field Type Properties • The different field data types have properties associated with them that extend their meaning. Here are the more common ones for some: • Number: • Field Size – the type of number this field can hold (ex: long, integer, double) • Decimal Places – the amount of precision on the number (usually left at auto, which lets FieldSize determine the precision). • Text • Field Size – the maximum number of characters allowed. Can be no more than 255. BMIS 289: Spring 2002

  39. Create A Table – Field Type Common Properties • Many field data types have common properties: • Required: A yes/no value. If “yes”, Access generates an error if either a user or ADO attempts to create a record without providing data for this field. • Default Value: the value a new record has when it is initially created. • Indexed: a yes/no value. If “yes,” Access indexes all the values in this field, making performance faster but increasing database size. BMIS 289: Spring 2002

  40. Create A Table – Primary Keys • To define the primary key for a table follow these steps: • In Design View select the field that will be the table’s primary key. • Right-click on the field and select Primary Key BMIS 289: Spring 2002

  41. Create A Table – Save It • When you are done defining a new table’s fields click the Save icon on the main toolbar. • When prompted, enter a name for the table. BMIS 289: Spring 2002

  42. Create A Table – Define A Foreign Key • Follow these steps to create a foreign key reference in a table. • Remember, two tables with their own primary keys are required to create a foreign key in one of the tables, so ensure you have two tables. • In the table that will have the foreign key type in the field’s name. • For the field’s data type select Lookup Wizard. BMIS 289: Spring 2002

  43. Create A Table – Define A Foreign Key (cont) • A wizard starts that lets you define the foreign key. • Select “I want the lookup column to lookup the values in a table or query.” • Click Next. • Select the name of the table that this table will link to on the next screen. • Click Next. BMIS 289: Spring 2002

  44. Create A Table – Define A Foreign Key (cont) • In the left hand column select the field that will be the foreign key in this table. • Click  to move the field name to the right hand column. • Click Next. BMIS 289: Spring 2002

  45. Create A Table – Define A Foreign Key (cont) • In the next screen click Finish (there is no need to continue on with the Wizard at this point) and Access will create the foreign key relationship. • When Access gives you this prompt say Yes: BMIS 289: Spring 2002

  46. Entering Data • To manually enter data double click the table name from the database window. • A data sheet appears showing all the table’s data. • The last row in the data sheet represents a new record. Click inside any blank field to begin entering data. • When you have finished entering data click outside of the row and Access will attempt to create the new record. • If there are any errors Access will tell you about them before permanently writing the record to the database. BMIS 289: Spring 2002

  47. Create A Table – Add Records • The “pencil icon” in the extreme left edge of a record means it is being edited: • When the focus moves from that record any changes to the record are written to the database: Record is being edited Record is not being edited BMIS 289: Spring 2002

  48. ADO Object Model • Primary ADO Objects: • Connection: the actual link between the web page and a database. • Command: allows you to run commands against a database. • Recordset: contains all the records returned from a specific action on a database. BMIS 289: Spring 2002

  49. A Simple ASP Page w/Database • Based upon example starting on page 475 in the textbook. • Download class 9 sample code from class web site. • DisplayAllMovies.asp BMIS 289: Spring 2002

  50. A Simple ASP Page w/Database • The DisplayAllMovies.asp example has three basic steps for displaying all the titles out of the “Movies” table: • Connect to the database • Display the data • Close the connection BMIS 289: Spring 2002

More Related