CIS 338: VB.NET & Databases

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)