Access

1. Access A Relational Database Management System

2. Database • A database is a collection of data that’s related to a particular topic • A database management system is a system that stores and retrieves information in a database Prof. Leighton

3. Imagine that you've been hired to replace the retiring office manager. She knows where everything is kept in the office, but her system is somewhat antiquated.

4. Organization of the Company’s Data Prof. Leighton

5. Computerize the Data • Create one large database • What or who should be the basic unit for a record? • Customers? • Brown, customer info, product 1, supplier info, product 2.. • Smith, customer info, product 1, supplier info, product 2.. • Products? • Product 1, supplier info, Brown’s info, Smith’s info.. • Product 2, supplier info, Brown’s info, Smith’s info.. • Suppliers? • Supplier 1 info, product 1, Smith’s info, Brown’s info.. • Supplier 2 info, product 1, Smith’s info, Brown’s info.. Prof. Leighton

6. RDBMS: Relational Database Management System • Has several small tables • Each piece of information is stored only in one place • Saves storage space • Makes updating easier • Can create new tables for new information • Stores information about the relationship among the tables Prof. Leighton

7. Structure of a RDBMS • Table is a collection of data about a particular subject • Data are presented in rows called recordsand in columns called fields • Record is the basic unit of observation, also called an entity (customer, supplier, product, firm, state) • Field is a category of information • Database is a collection of tables Prof. Leighton

8. Table for Customers Name and so forth Table for Producers Name and so forth Table for products Description of product Table for orders Items ordered Office Database Links between the tables Prof. Leighton

9. USA Database • We have info about • Regions of the USA • States • Cities • Election Districts within Cities Prof. Leighton

10. Organizing a USA Database • Table one contains regional information • Table two contains information about the 50 states • Table three contains information about particular cities in each state • Table four contains election district information about each city Prof. Leighton

11. State Table Prof. Leighton

12. City Table Prof. Leighton

13. How Do We Link the Tables? • Tables must be connected through a common field • Possibilities: • State name (with clients or firms, name may not be unique) • State ID • State ID • Primary key in the state table • Foreign key in the city table Prof. Leighton

14. A Common Field for the Two Tables Prof. Leighton

15. The Common Field Is Unique in the State Table Prof. Leighton

16. The Common Field May Appear Many Times in the City Table Prof. Leighton

17. The Relation between Region and State • There are four regions in the USA • Each region contains a number of states • Create a Region table • Region ID identifies each region • Primary key in Region table • Create a State Table • State ID identifies each state • Region ID identifies the state’s region • Foreign key in State table Prof. Leighton

18. Primary Key

19. Foreign Key

21. Summary • RDBMS are organized as a collection of tables • Allow organizations to handle massive amounts of data • Access database • 1 gigabyte • 32,768 tables Prof. Leighton