cis 338 vb net databases
Download
Skip this Video
Download Presentation
CIS 338: VB.NET & Databases

Loading in 2 Seconds...

play fullscreen
1 / 25

CIS 338: VB.NET & Databases - PowerPoint PPT Presentation


  • 292 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CIS 338: VB.NET & Databases' - mike_john


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
cis 338 vb net databases

CIS 338: VB.NET & Databases

Dr. Ralph D. Westfall

November, 2009

what is a database
What Is a Database?
  • shared collection of logically related data
  • designed to meet needs of multiple users

McFadden & Hofer, 1994

database advantages
Database Advantages
  • less redundant than separate application files
  • consistency of data e.g., field lengths
  • integration of data
  • data sharing
  • easier application development
  • scalability
database advantages 2
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
database advantages 3
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
database disadvantages
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
database design
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
key fields
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

rules for tables in a database
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
normalization
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
normalization 2
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

specialized normalized tables
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
denormalization
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
denormalization 2
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)
indexes
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)

creating a database or table
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
creating a database in access
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)
create database in access 2
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.
sql server and express
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
create sql server express edition db with server explorer
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
ssexpress with server explorer
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
ssexpress with server explorer 2
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
loading file data into ssexpress
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
upsizing access to msde
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
upsizing access 2
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
ad