Relational Databases Flat databases and spreadsheets Relational databases

Relational Databases • “Flat” databases and spreadsheets • Relational databases • Uses – Record keeping – Queries – Reports – Online uses • Examples: MS Access, Fox. Pro Lars Perner, Instructor 1

“Flat” Databases • Database or spreadsheet contains variables (e. g. , columns) of info on cases (e. g. , columns, people) • It is possible to store data – Find statistics (e. g. , average balance) – Identify critical cases (e. g. , customers past due) • May contain redundant data if more than one case exists for each customer Lars Perner, Instructor 2

Variable types (much as in spreadsheets) • • • Text Numeric Date Logical (true-false) Memo (notes) Lars Perner, Instructor 3

Relational Databases • Different databases are linked to each other CUSTOMERS Name, address… ORDERS EMPLOYEES INVENTORY COMISSIONS Lars Perner, Instructor 4

Linking Databases • Two databases are set up—one may contain constant info once and the other separate transactions – Database with constant info only needs to be updated once • “Linked” through ID variable (e. g. , ID number, employee number, part number, account number) Lars Perner, Instructor 5

Uses of Databases • Data storage • Reports—e. g. , WEB SITE LISTINGS – Employee commissions – “Hot” selling items – Catalogs INVENTORY DATABASE SORTING SCHEME WEB SEARCH ENGINE CATALOG (PRINTED) Lars Perner, Instructor 6

Outputs • Queries – Identify cases meeting set criteria • E. g. , more than 6 purchases over last two years • Purchases totaling over $100, 000 • Has not purchased in the last 6 months • Bought particular product • Reports – Customer purchase record – Commissions earned by salesperson for the month or year – Top 100 customers in terms of dollars spent Lars Perner, Instructor 7