1 / 23

CIS 338: VB.NET & Databases

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

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.


Presentation Transcript

  1. CIS 338: VB.NET & Databases Dr. Ralph D. Westfall May, 2011

  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: Id#, SS#, Name, Address, Pay • paychecks: CheckID#, ID#, Date, 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 Notes

  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 (like a "getter" in OO code)

  15. Creating a Database or Table • using SQL commands in VB.NET • using VB.NET Server Explorer • using "3rd party" programs or "shells" • similar to Server Explorer approach or more sophisticated • SQL Server Management Studio, for example

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

  17. Create Database in Access - 2 • could use External Data>Import>More 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.

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

  19. Create SQL Server 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

  20. SSExpress with Server Explorer • right-click Data Connections>Create New SQL Server Database>paste in the (local)\SQLEXPRESS • be sure Use Windows Authentication is selected>type something in New database name>OK

  21. SSExpress with Server Explorer - 2 • expand the Data Connections icon • right-click Tables>Add New Table • type in Column Names • 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

  22. File Location Issues • default location of new file (my machine) c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA • could right-click database name>Publish to provider to relocate • could also use SQL Server Management Studio to manipulate databases created within Visual Studio

  23. Convert Access to SQL Server • in Access 2007 • Database Tools>Move Data>SQL Server icon>Create new database>Next • type in (local)\SQLEXPRESS, check Use Trusted Connection>[type new file name]>Next>[select tables]>Next>Next> No application changes>Next>Finish • goes into default location (previous slide)

More Related