html5-img
1 / 25

CIS 338: VB.NET & Databases

CIS 338: VB.NET & Databases. Dr. Ralph D. Westfall November, 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

mike_john
Download Presentation

CIS 338: VB.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 338: VB.NET & Databases Dr. Ralph D. Westfall November, 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 e.g., field lengths • integration of data • data sharing • easier application development • scalability

  4. Database 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. Database - Disadvantages • uses more resources than file-based systems • hardware, software • requires specialized personnel • potential data access conflicts • operations vs. data mining • potential organizational conflicts • incompatibilities between database systems

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. Specialized Normalized Tables • "parent-child tables" • boss is an employee, but so are people working for her • all have the same attribute fields, including a field that has ID of each one's boss • "lookup table" • zip, city, state • zip code "determines" city and state • those can come from a table keyed by zip code • Cerritos mail used to be addressed to Artesia

  13. 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

  14. Denormalization - 2 • "do not store data in a table if it can be calculated from other data" (in 3NF) • 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)

  15. 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)

  16. Creating a Database or Table • using SQL commands in VB.NET • using VB.NET Server Explorer • using "3rd party" programs • similar to Server Explorer approach or more sophisticated • using the database itself

  17. 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)

  18. 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.

  19. 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. • SQL Server 2008 is the software that runs SQL Server database • the "engine" in Access 2003 is Jet 4.0 • but Access can read/write SS 2008

  20. Create SQL Server Express Edition DB with Server Explorer • to create SQL Server Express Edition database (optional for Project 2) • install SQL Server Express 2008 • use Start>Control Panel>System> Computer Name and then copy the name • stop at the dot if it has a domain name • View>Server Explorer in .NET

  21. SSExpress with Server Explorer • right-click Data Connections>Create New SQL Server Database>paste in the Computer Name followed by \SQLEXPRESS • be sure Use Windows Authentication is selected>type something in New database name>OK

  22. SSExpress with Server Explorer - 2 • expand the Data Connections icon • 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

  23. Loading File Data into SSExpress • can create a SQL Server Express database as in previous slides • can read file and parse data into fields as with Project 1 • can then use SQL commands and .NET objects to load the file data into an SSExpress database

  24. Upsizing Access to MSDE • in Access 2003 • File>New • New>New [on right]>Project using new… • select (folder/)file name for new .adp file and click Create • type in [computer name]\SQLEXPRESS • click Use Trusted Connection>Next>Finish

  25. Upsizing Access - 2 • either double click Create table in Design view • OR File>Get External Data>Import to pull in data from a database or file • need to fix Column Names, Data Types after file data gets loaded, just as you would do with an Access database

More Related