1 / 31

CIS 451: ASP.NET & Databases

CIS 451: ASP.NET & Databases. Dr. Ralph D. Westfall February, 2009. What Is a Database?. "shared collection of logically related data" "designed to meet needs of multiple users" McFadden & Hofer, 1994. Database Advantages. less redundant than separate application files

rozalia-meszaros
Download Presentation

CIS 451: ASP.NET & Databases

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. CIS 451: ASP.NET & Databases Dr. Ralph D. Westfall February, 2009

  2. What Is a Database? • "shared collection of logically related data" • "designed to meet needs of multiple users" • McFadden & Hofer, 1994

  3. Database Advantages • less redundant than separate application files • consistency of data • integration of data • data sharing • easier application development • scalability e.g., Oracle product line

  4. Databases Advantages - 2 • better security, privacy, integrity controls • standardization e.g., field names • data accessibility and responsiveness • data independence • reduced program maintenance • e.g., centralized validation

  5. Database Advantages - 3 • more efficient technology than most "third generation" application programs • powerful code to access items, search and sort • data storage optimization (disk, memory) • efficient updating: add, change, delete

  6. Databases - Disadvantages • use more resources than file-based systems • hardware, software • require specialized personnel • potential data access conflicts • operations vs. data mining • potential organizational conflicts • incompatibilities between database systems

  7. Universal Data Access • ODBC: open database connectivity • standard API for accessing relational databases • API = application programming interface • "drivers" for Access, dBase, DB2, Excel, text, etc. • Microsoft is a leading ODBC backer and ships its own version with Windows

  8. Universal Data Access - 2 • OLE-DB: DB object linking & embedding • accesses relational databases through ODBC • accesses other data also • files, spreadsheets, e-mail, video, voice, images, web pages, object databases, etc. • data is accessed "in place" (no need to load into a database)

  9. OLE-DB Concepts • data store = "persistent" collection of data • data provider = gets data from data store • OLE-DB "driver" for database, email data, etc. • data consumer = application that uses data • ADO.NET (ActiveX Data Object) interfaces to data store through OLE-DB

  10. ADO.NET • ADO stands for ActiveX Data Objects • part of Microsoft's OLEDB strategy to enable programs to almost any kind of data on a computer • provides objects to easily connect to a variety of data sources • databases, mail servers, http servers, Excel documents, etc.

  11. Using Databases with Internet • web browser interface very easy to use • compatible with all kinds of hardware • Internet is global • low cost delivery to large # of users • multimedia capabilities • ASP.NET can access data through ADO and OLE-DB for web applications

  12. ADO.NET Model Objects • Connection: to a specific file or server • Command: holds/runs SQL/other text to query/update data, call stored procedures, etc. • DataReader: high-speed, limited function interface between database and application

  13. ADO.NET Model Objects - 2 • Data Adapter • uses SQL commands to transfer data back and forth between the application and the database • DataSet • not connected to database • but data can be changed and uploaded • contains one or more DataTables • can "join" multiple tables in DataSet

  14. ADO.NET Model Objects - 3 • DataTables have the following: • DataRowCollection - rows • DataColumnCollection - fields • DataView - use this if need to sort data that is used as a datasource for an object e.g., a datagrid

  15. Database Design • determine functional requirements • identify data required for applications • organize the data • tables (usually normalized) • relationships between tables • identify business rules (middle tier) • design queries and applications

  16. Key Fields • each row in a table is uniquely identified by one or more key field(s) ID# Name City 322 Karim Pomona Dr#Patient# Appointment 397 0246875 10/24/00 2 PM 'key field(s) underlined

  17. Rules for Tables in a Database • all items in each table should relate to one main topic or subject, on a one-to-one basis • employees: SS#, Name, Address, Hourly Pay • paychecks: SS#, Date, Pay Amount • tables should be "normalized" (2NF or more) • no repeated information • no fields that may be intentionally empty • no fields with data that can be calculated (derived) from other fields

  18. Normalization • splitting tables to avoid problems • avoid duplicated data and empty cells • avoid problems: add, change or delete data SID Name GWE ClassQuarter Grade 322 Ana No C101 F99 pass 322 Ana No C234 S00 A • use 2 tables instead • 1-SID, Name, GWE; 2-SID, Class, Quarter, Grade

  19. Normalization - 2 • library has borrowers, books, circulation • put circulation in borrowers file? • repeating patron data for every checked out book Borrower#, Name, Address, Book# • or repeating (often empty) columns Borrower#, Name, Address, Bk#1, Bk#2, etc. • normalize: separate borrowers, circulation Book #, Title, Author, Year, etc. Patron#, Name, Address, etc. Patron#, Book#, Due Date

  20. Specialized Normalized Tables • "parent-child tables" • building: name, address, land area • floor: #, common (non-rentable) area • room: #, square feet • parent table has family name, address • family member table has first names, DOB, gender • "lookup table" • zip, city, state • zip code "determines" city and state • data storage only needs zip • Cerritos mail used to be addressed to Artesia

  21. Denormalization • sometimes more efficient to not completely normalize all tables • less normalization can increase efficiency and possibly reduce number of tables • e.g., 1 customer table with fields for 2 voice phone numbers • only need to update 1 table • don't need to do a join to get all phone #s

  22. Denormalization - 2 • "do not store data in a table if it can be calculated from other data" • e.g., total field (in a separate table) • but with a total field don't need to total a large # of records every time need a total • but do need extra code to update totals when records are added, changed or deleted (as in a "getter" in OO code)

  23. Indexes • data is added to database in random order • more efficient processing for searches and sorting (less efficient for adding data) • index table has sort order (stores position) DataFile OrderIndex/Order Jose (1) 1 (4) Phuong (2) 2 (3) Gunther(3) 3 (1) Ahmed (4) 4 (2)

  24. Creating a Database or Table • using SQL commands with ADO in VB.NET • using VB.NET Server Explorer • using SQL Server Management Studio • using "3rd party" programs • similar to Server Explorer approach or more sophisticated

  25. Creating a Database in Access • Access 2003 new database options • Design View • user needs to type in Field Name, select Data Type, input Field Size and other properties • wizard that provides common templates • entering data manually • uses default settings (like a spreadsheet)

  26. Create Database in Access - 2 • could use File>Get External Data>Import to pull in data from a file, other databases, spreadsheets, etc. • could go into a new table, or be added to an existing table • need to set field properties when bringing in data to a new table e.g., Field Name, Data Type, etc.

  27. SQL Server and Express • Microsoft's SQL Server is designed to handle very large scale applications • more security, transaction logging , stored procedures, very many users, etc. • MSDE (Microsoft Data Engine) is the software that runs SQL Server database • the "engine" in Access is Jet 4.0 (currently) • but Access can read/write MSDE databases

  28. Create SQL Server Express DB • install SSE 2008 • load external data as with SQL Server Management Studio

  29. Using Server Explorer with SSE • View>Server Explorer in VB.NET • click Servers>SQL Servers • right click server that ends with \NETSDK • click (expand) new database • right click Tables>New Table

  30. Using Server Explorer to Create SQL Server Express Database • add fields to master • type in Column Name • type/select Type, Length, etc. • File>Close or right click tab>Close after enter last field • File>Save Table1 and replace name in Choose Name input box

  31. Stored Procedures • code to manipulate database (retrieve data, add, change, delete) stored in database • like subroutine, can use in multiple programs (but not with Access database) • less storage: once instead of multiple copies • easier to update than multiple copies • stored queries run faster inside database • easier to migrate to other platforms or scale up to larger volumes

More Related