Cis 338 vb net databases
1 / 25

CIS 338: VB.NET Databases - PowerPoint PPT Presentation

  • Updated 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

Related searches for CIS 338: VB.NET Databases

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
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 l.jpg

CIS 338: VB.NET & Databases

Dr. Ralph D. Westfall

November, 2009

What is a database l.jpg
What Is a Database?

  • shared collection of logically related data

  • designed to meet needs of multiple users

    McFadden & Hofer, 1994

Database advantages l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg

  • 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 l.jpg
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 l.jpg
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 l.jpg

  • 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 l.jpg
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 l.jpg

  • 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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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