Transcript nov11
CS 101 – Nov. 11 • Finish Database concepts – 1-1 relationship – 1-many relationship – Many-to-many relationship • Review 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. Publishers & books One-to-One Empl # Name Position Empl # Salary 101 Smith Welder 102 18,000 102 Jones carpenter 101 17,000 • 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. • What happens when we “join” these 1-1 tables? First name Bob Bob Bob Alice First name Bob Elena Alice Last name Fuller Daniels Daniels Andrews Position accountant cashier accountant gardener Last name Salary Daniels 51,000 Carlson 21,000 Andrews 81,000 City Chicago Greenville Indianapolis Greenville Birthday 7/1/67 9/1/89 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 First Last Bob Daniels Bob Daniels Position City Salary cashier Greenville 51,000 accountant Indianapolis 51,000 Alice Andrews gardener Greenville 81,000 Birthday 7/1/67 7/1/67 8/1/78 Example 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. 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. Outline for test • Excel – text functions, scenarios, solver, goal seek (Chapter 5) • Problem solving / software – Look at contents of Python folder • Text software • Database – Tables, relationships, queries, reports • Problem solving/software – – – – – Steps in solving problems Types of languages & statements Variables Example algorithms & programs: search, sum, sort Kinds of bugs & how to fix them • Operating systems – – – – Various responsibilities Scheduling File system issues 4 ways to search for data