250 likes | 372 Views
CS 101 – Access notes. Databases (Microsoft Access) 4 parts of a database database design Try to understand the ideas behind database design, not just the mechanics. Access vs. Excel. Excel emphasizes numbers Access emphasizes relationships some overlap
E N D
CS 101 – Access notes Databases (Microsoft Access) • 4 parts of a database • database design • Try to understand the ideas behind database design, not just the mechanics.
Access vs. Excel • Excel emphasizes numbers • Access emphasizes relationships • some overlap • Access is perfect for finding common info from 2 separate tables
Purpose of Access • It’s a “database management system” • Record keeping for a business • Requires a plan • one file, with many components • Keep track of … so that: • Find cross-references • Generate reports
Database format Usually four parts: • Tables (at least 1 is required) • Queries • Forms • Reports
Navigating a table • A database table looks like a spreadsheet! • Record = row • Field = column • Two ways to view: • Design view, to start • Datasheet view, to enter data • Primary Key!
Example questions • In a stock portfolio: • Which stocks have lost money? • When did I buy those stocks? • Which class is easier: CS 22 or CS 25? • Check grades in each class • Check grades for students who’ve taken both • Among customers who bought a new generator last year, what have they bought since?
Relieve tedium • Suppose we want to maintain info on orders. • Better to have two tables than one!
Need multiple tables • Ex. Librarian wants to contact graduating seniors who have overdue books. • Need multiple tables: • Student info table • Student #, name, year • Book table • ISBN, title, author, … • Transaction or “check out” table • Transaction #, ISBN, student #, Due date
Relationships • When you have 2+ tables, there is almost always a relationship • They share one field in common. • Can you tell what it is? • Ex. Customers & Orders • Ex. Students & Class roster • Ex. Publishers & books
Animal hospital • Keep track of customers, pets, visits • Each gets its own table • What fields for each table? • Relationships • What else does a database need?
Fields in your table • Anticipate questions • Age? store birth date • GPA? store credits and quality points • What year? store date of admission • Store data in its smallest parts (e.g. address) • Calculated fields don’t belong in table!
Table review • We want to “manage” information: • Organize, insert, delete, retrieve • To organize we… • Create a table (or “set”, “class”) consisting of records (or “objects”) each having fields (or “attributes”) • Usually we’ll want 2+ related tables.
Fields in your table • Anticipate questions • Age? store birth date • GPA? store credits and quality points • What year? store date of admission • Store data in its smallest parts (e.g. address) • Calculated fields don’t belong in table!
Queries • Usually we ask about info from 2+ tables. • By default, a query will perform an operation called a Cartesian Product, which gives all possible combinations. • Ex. Name and City tables:
Cartesian Product • Given 2 sets, find all possible ordered pairs. • Analogously for more than 2 sets. • Great example: choosing a menu. • Appetizer • Entrée • Dessert • Unfortunately, most DB queries are not like this! We get too many results.
Relationships • We want to tell Access that there is a relationship between the tables, so we can create meaningful query. • One-to-many is most common • “Each city has one or more employees.” • Now, query will return 3 results instead of 6: Miami Bob Pittsburgh Mary Ken
One-to-One • Can be useful if some information is confidential. • What if we didn’t have any relationship?
1-1 Query • When you combine tables that have a 1-1 relationship: Access will look for fields that are the same, and use this as a filter. • In previous example, we’ll have 2 results instead of 4. Employee 101’s information Employee 102’s information • Let’s look at another example.
Referential integrity Keep related records consistent Cascade delete: allow deletion of “one” Cascade update: allow update of “one” For example, changing someone’s CustomerID. One-many Relationships
Many-to-many • Ex. Customers to products • Implement as 2 one-to-one • “Order details” table • Think of possible queries based on the 5 tables given in handout.
Other queries Besides ordinary “select” queries: • Total – special case: also do subtotals • Parameter – prompt user to tailor the result • Action – modify underlying table • Make, delete from, append to, update • Crosstab – 2-D subtotal • Ex. $, by species and month!