cs 101 – access notes databases (microsoft access) 4 parts of a database database design –try to...
TRANSCRIPT
![Page 1: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/1.jpg)
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.
![Page 2: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/2.jpg)
Access vs. Excel
• Excel emphasizes numbers
• Access emphasizes relationships
• some overlap
• Access is perfect for finding common info from 2 separate tables
![Page 3: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/3.jpg)
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
![Page 4: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/4.jpg)
Database format
Usually four parts:
• Tables (at least 1 is required)
• Queries
• Forms
• Reports
![Page 5: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/5.jpg)
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!
![Page 6: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/6.jpg)
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?
![Page 7: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/7.jpg)
Relieve tedium
• Suppose we want to maintain info on orders.
• Better to have two tables than one!
Order# Name City State Product Qty.
101 J. Miller Miami FL Laptop 5
102 G. Novak Tampa FL Printer 3
103 G. Novak Tampa FL DVD-RW 50
104 G. Novak Tampa FL Keyboard 1
105 J. Miller Miami FL Lamp 4
106 A. Bik San Diego CA Chair 6
107 T. Lee Buffalo NY Fan 3
![Page 8: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/8.jpg)
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
![Page 9: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/9.jpg)
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
![Page 10: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/10.jpg)
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?
![Page 11: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/11.jpg)
Relationships
![Page 12: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/12.jpg)
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!
![Page 13: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/13.jpg)
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.
![Page 14: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/14.jpg)
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!
![Page 15: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/15.jpg)
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:
Name
Bob
Mary
Ken
City
Miami
Pittsburgh
![Page 16: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/16.jpg)
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.
![Page 17: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/17.jpg)
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 BobPittsburgh Mary
Ken
![Page 18: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/18.jpg)
One-to-One
• Can be useful if some information is confidential.
• What if we didn’t have any relationship?
Empl # Name Position
101 Smith Welder
102 Jones carpenter
Empl # Salary
102 18,000
101 17,000
![Page 19: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/19.jpg)
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 informationEmployee 102’s information
– Let’s look at another example.
![Page 20: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/20.jpg)
• What happens when we “join” these 1-1 tables?
First name Last name Position City
Bob Fuller accountant Chicago
Bob Daniels cashier Greenville
Bob Daniels accountant Indianapolis
Alice Andrews gardener Greenville
First name Last name Salary Birthday
Bob Daniels 51,000 7/1/67
Elena Carlson 21,000 9/1/89
Alice Andrews 81,000 8/1/78
![Page 21: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/21.jpg)
First Last Position City Salary Birthday
Bob Daniels cashier Greenville 51,000 7/1/67
Bob Daniels accountant Indianapolis 51,000 7/1/67
Alice Andrews gardener Greenville 81,000 8/1/78
First name Last name Position City
Bob Fuller accountant Chicago
Bob Daniels cashier Greenville
Bob Daniels accountant Indianapolis
Alice Andrews gardener Greenville
First name Last name Salary Birthday
Bob Daniels 51,000 7/1/67
Elena Carlson 21,000 9/1/89
Alice Andrews 81,000 8/1/78
![Page 22: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/22.jpg)
One-many Relationships• Referential integrity
– Keep related records consistent
– Cascade delete: allow deletion of “one”
– Cascade update: allow update of “one”• For example, changing someone’s CustomerID.
![Page 23: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/23.jpg)
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.
![Page 24: CS 101 – Access notes Databases (Microsoft Access) 4 parts of a database database design –Try to understand the ideas behind database design, not just](https://reader035.vdocuments.us/reader035/viewer/2022062518/56649edd5503460f94bee225/html5/thumbnails/24.jpg)
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!