1 / 68

CS 435 Database Systems

CS 435 Database Systems. Chapter 1. An Overview of Database Management. What is a database?. “An electronic filing cabinet” “A repository for a collection of computerized data files” A collection of interrelated data. “Descriptions”--not definitions.

honora
Télécharger la présentation

CS 435 Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS 435Database Systems

  2. Chapter 1 An Overview of Database Management

  3. What is a database? “An electronic filing cabinet” “A repository for a collection of computerized data files” A collection of interrelated data. “Descriptions”--not definitions

  4. So, how is that different from a file?

  5. File processing systems Independent systems Each has its own definition of data Each has its own data formats

  6. File processing systems Independent systems. Each has its own definition of data. Each has its own data formats Faculty Data File Payroll System Reports Class Scheduling System Class Data File Reports Grade Posting System Student Data File Reports

  7. File processing systems Faculty Data File Payroll System Reports Class Scheduling System Class Data File Reports Problems of inconsistency. May need faculty member name in each file. May be recorded differently in each.

  8. Database systems A single data definition All data (potentially) accessible from each application Less paperwork exchange between applications

  9. Database systems A single data definition All data (potentially) accessible from each application Payroll System Reports Data Definition Class Scheduling System Database Management System Reports Faculty Data Class Data Student Data Grade Posting System Reports Less paperwork exchange between applications

  10. So, a database is a collection of "files," or at least a collection of data that would otherwise usually exist in multiple files.

  11. What is adatabase management system? The software that makes it possible for multiple applications and multiple users to access the same (single) set of data. The software that enables users to access and share the single set of integrated data without concern about files and file structure.

  12. What is a database system? The data The database software (database management system) The other software (applications) The hardware where the data and software reside (and execute) The users who use the system

  13. data Computer hardware

  14. software data users Computer hardware

  15. A database system is the collection of data, the software to provide access to that data, (and the hardware upon which the data and software reside and execute.) To that we can also add the users. They are also part of the "system."

  16. An example of a database and some "database operations.” (The CELLAR example) http://www.csupomona.edu/~hnriley/web_mysql/cellar.html

  17. Date’s “CELLAR” Example +-----+----------------+---------------+------+---------+-------+ | bin | wine | producer | year | bottles | ready | +-----+----------------+---------------+------+---------+-------+ | 2 | Chardonnay | Buena Vista | 2001 | 1 | 2003 | | 3 | Chardonnay | Geyser Peak | 2001 | 5 | 2003 | | 6 | Chardonnay | Simi | 2000 | 4 | 2002 | | 12 | Joh. Riesling | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | Ch. St. Jean | 2001 | 4 | 2003 | | 22 | Fume Blanc | Robt. Mondavi | 2000 | 2 | 2002 | | 30 | Gewurztraminer | Ch. St. Jean | 2002 | 3 | 2003 | | 43 | Cab. Sauvignon | Windsor | 1995 | 12 | 2004 | | 45 | Cab. Sauvignon | Geyser Peak | 1998 | 12 | 2006 | | 48 | Cab. Sauvignon | Robt. Mondavi | 1997 | 12 | 2008 | | 50 | Pinot Noir | Gary Farrell | 2000 | 3 | 2003 | | 51 | Pinot Noir | Fetzer | 1997 | 3 | 2004 | | 52 | Pinot Noir | Dehlinger | 1999 | 2 | 2002 | | 58 | Merlot | Clos du Bois | 1998 | 9 | 2004 | | 64 | Zinfandel | Cline | 1998 | 9 | 2007 | | 72 | Zinfandel | Rafanelli | 1999 | 2 | 2007 | +-----+----------------+---------------+------+---------+-------+

  18. Date’s “CELLAR” Example Retrieval: select wine, bin_num, producer from Cellar where ready = '2000' ; Result: +----------------+-----+--------------+ | wine | bin | producer | +----------------+-----+--------------+ | Cab. Sauvignon | 43 | Windsor | | Pinot Noir | 51 | Fetzer | | Merlot | 58 | Clos du Bois | +----------------+-----+--------------+ 3 rows in set (0.00 sec)

  19. Date’s “CELLAR” Example Inserting new data: insert into Cellar values (53, 'Pinot Noir', 'Saintsbury', 2003, 6, 2008);

  20. Date’s “CELLAR” Example Changing existing data: update Cellar set bottles = 4 where bin_num = 3; Deleting existing data: delete from cellar where bin_num = 2;

  21. CELLAR changed from 5 to 4 row for bin 2 deleted +-----+----------------+---------------+------+---------+-------+ | bin | wine | producer | year | bottles | ready | +-----+----------------+---------------+------+---------+-------+ | 3 | Chardonnay | Geyser Peak | 2001 | 4 | 2003 | | 6 | Chardonnay | Simi | 2000 | 4 | 2002 | | 12 | Joh. Riesling | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | Ch. St. Jean | 2001 | 4 | 2003 | | 22 | Fume Blanc | Robt. Mondavi | 2000 | 2 | 2002 | | 30 | Gewurztraminer | Ch. St. Jean | 2002 | 3 | 2003 | | 43 | Cab. Sauvignon | Windsor | 1995 | 12 | 2004 | | 45 | Cab. Sauvignon | Geyser Peak | 1998 | 12 | 2006 | | 48 | Cab. Sauvignon | Robt. Mondavi | 1997 | 12 | 2008 | | 50 | Pinot Noir | Gary Farrell | 2000 | 3 | 2003 | | 51 | Pinot Noir | Fetzer | 1997 | 3 | 2004 | | 52 | Pinot Noir | Dehlinger | 1999 | 2 | 2002 | | 58 | Merlot | Clos du Bois | 1998 | 9 | 2004 | | 64 | Zinfandel | Cline | 1998 | 9 | 2007 | | 72 | Zinfandel | Rafanelli | 1999 | 2 | 2007 | | 53 | Pinot Noir | Saintsbury | 2003 | 6 | 2008 | +-----+----------------+---------------+------+---------+-------+ new row inserted

  22. Note that the “CELLAR database" looks like a "table," and in fact,that is what it is. In particular it is a relational table, or just a "relation."

  23. Aside regarding tables… and, “looks like a table.”

  24. “Tables” +-----+----------------+---------------+------+---------+-------+ | bin | wine | producer | year | bottles | ready | +-----+----------------+---------------+------+---------+-------+ | 2 | Chardonnay | Buena Vista | 2001 | 1 | 2003 | | 3 | Chardonnay | Geyser Peak | 2001 | 5 | 2003 | | 6 | Chardonnay | Simi | 2000 | 4 | 2002 | | 12 | Joh. Riesling | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | Ch. St. Jean | 2001 | 4 | 2003 | | 22 | Fume Blanc | Robt. Mondavi | 2000 | 2 | 2002 | | 30 | Gewurztraminer | Ch. St. Jean | 2002 | 3 | 2003 | | 43 | Cab. Sauvignon | Windsor | 1995 | 12 | 2004 | | 45 | Cab. Sauvignon | Geyser Peak | 1998 | 12 | 2006 | | 48 | Cab. Sauvignon | Robt. Mondavi | 1997 | 12 | 2008 | | 50 | Pinot Noir | Gary Farrell | 2000 | 3 | 2003 | | 51 | Pinot Noir | Fetzer | 1997 | 3 | 2004 | | 52 | Pinot Noir | Dehlinger | 1999 | 2 | 2002 | | 58 | Merlot | Clos du Bois | 1998 | 9 | 2004 | | 64 | Zinfandel | Cline | 1998 | 9 | 2007 | | 72 | Zinfandel | Rafanelli | 1999 | 2 | 2007 | +-----+----------------+---------------+------+---------+-------+ column “headings” rows columns Columns are aligned: i.e., strings left justified numbers right justified

  25. +-----+----------------+---------------+------+---------+-------++-----+----------------+---------------+------+---------+-------+ | bin | wine | producer | year | bottles | ready | +-----+----------------+---------------+------+---------+-------+ | 2 | Chardonnay | Buena Vista | 2001 | 1 | 2003 | | 3 | Chardonnay | Geyser Peak | 2001 | 5 | 2003 | | 6 | Chardonnay | Simi | 2000 | 4 | 2002 | | 12 | Joh. Riesling | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | Ch. St. Jean | 2001 | 4 | 2003 | | 22 | Fume Blanc | Robt. Mondavi | 2000 | 2 | 2002 | | 30 | Gewurztraminer | Ch. St. Jean | 2002 | 3 | 2003 | | 43 | Cab. Sauvignon | Windsor | 1995 | 12 | 2004 | | 45 | Cab. Sauvignon | Geyser Peak | 1998 | 12 | 2006 | | 48 | Cab. Sauvignon | Robt. Mondavi | 1997 | 12 | 2008 | | 50 | Pinot Noir | Gary Farrell | 2000 | 3 | 2003 | | 51 | Pinot Noir | Fetzer | 1997 | 3 | 2004 | | 52 | Pinot Noir | Dehlinger | 1999 | 2 | 2002 | | 58 | Merlot | Clos du Bois | 1998 | 9 | 2004 | | 64 | Zinfandel | Cline | 1998 | 9 | 2007 | | 72 | Zinfandel | Rafanelli | 1999 | 2 | 2007 | +-----+----------------+---------------+------+---------+-------+ Separating lines provided by MySQL

  26. Separating lines provided by textbook publisher

  27. bin wine producer year bottles ready 2 Chardonnay Buena Vista 2001 1 2003 3 Chardonnay Geyser Peak 2001 5 2003 6 Chardonnay Simi 2000 4 2002 12 Joh. Riesling Jekel 2002 1 2003 21 Fume Blanc Ch. St. Jean 2001 4 2003 22 Fume Blanc Robt. Mondavi 2000 2 2002 30 Gewurztraminer Ch. St. Jean 2002 3 2003 43 Cab. Sauvignon Windsor 1995 12 2004 45 Cab. Sauvignon Geyser Peak 1998 12 2006 48 Cab. Sauvignon Robt. Mondavi 1997 12 2008 50 Pinot Noir Gary Farrell 2000 3 2003 51 Pinot Noir Fetzer 1997 3 2004 52 Pinot Noir Dehlinger 1999 2 2002 58 Merlot Clos du Bois 1998 9 2004 64 Zinfandel Cline 1998 9 2007 72 Zinfandel Rafanelli 1999 2 2007 No separating lines

  28. So, a database is usually said to consist of tables rather than files. The rows of the tables would be the "records" of a file. The columns of the table are the "fields" of those records.

  29. Note that the "database," the collection of tables, is a logical concept, a data structure.

  30. The database software (the database manager, the DBMS) provides the mapping of the logical database into one or more logical files, and ultimately into a physical representation on disk.

  31. Thus, there are stored files, stored records, and stored fields. (Sometimes the DBMS shares this mapping with the operating system)

  32. Parts +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P2 | Bolt | Green | 17.0 | Paris | | P3 | Screw | Blue | 17.0 | Rome | | P4 | Screw | Red | 14.0 | London | | P5 | Cam | Blue | 12.0 | Paris | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ Say this parts table is stored in the database as a file

  33. …and the table rows become records in the file, the columns fields within each record Stored database Two occurrences of the “part” stored record type. Other stored files P1 Nut Red 12.0 “Parts” stored file P2 Bolt Green 17.0 Stored field occurrences

  34. But, for example, the data for a part (a table row): P1 Nut Red 12.0 might be stored as two records: P1 Nut Red and P1 12.0

  35. The DBMS relieves the user of any concern about how the data is represented physically. Stored database Parts +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P2 | Bolt | Green | 17.0 | Paris | | P3 | Screw | Blue | 17.0 | Rome | | P4 | Screw | Red | 14.0 | London | | P5 | Cam | Blue | 12.0 | Paris | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ Other stored files DBMS P1 Nut Red 12.0 “Parts” stored file P2 Bolt Green 17.0 Data independence--the immunity of applications to change in physical representation.

  36. Look again at the CELLAR example to see how this table relates to other tables that might exist. http://www.csupomona.edu/~hnriley/web_mysql/cellar.html

  37. CELLAR +-----+----------------+---------------+------+---------+-------+ | bin | wine | producer | year | bottles | ready | +-----+----------------+---------------+------+---------+-------+ | 2 | Chardonnay | Buena Vista | 2001 | 1 | 2003 | | 3 | Chardonnay | Geyser Peak | 2001 | 5 | 2003 | | 6 | Chardonnay | Simi | 2000 | 4 | 2002 | | 12 | Joh. Riesling | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | Ch. St. Jean | 2001 | 4 | 2003 | | 22 | Fume Blanc | Robt. Mondavi | 2000 | 2 | 2002 | | 30 | Gewurztraminer | Ch. St. Jean | 2002 | 3 | 2003 | | 43 | Cab. Sauvignon | Windsor | 1995 | 12 | 2004 | | 45 | Cab. Sauvignon | Geyser Peak | 1998 | 12 | 2006 | | 48 | Cab. Sauvignon | Robt. Mondavi | 1997 | 12 | 2008 | | 50 | Pinot Noir | Gary Farrell | 2000 | 3 | 2003 | | 51 | Pinot Noir | Fetzer | 1997 | 3 | 2004 | | 52 | Pinot Noir | Dehlinger | 1999 | 2 | 2002 | | 58 | Merlot | Clos du Bois | 1998 | 9 | 2004 | | 64 | Zinfandel | Cline | 1998 | 9 | 2007 | | 72 | Zinfandel | Rafanelli | 1999 | 2 | 2007 | +-----+----------------+---------------+------+---------+-------+ Suppose we want to add some information about each wine.

  38. for example: +-----+----------------+-------|---------------+------+-----+-------+ | bin | wine | type | producer | year | qty | ready | +-----+----------------+-------|---------------+------+-----+-------+ | 2 | Chardonnay | white | Buena Vista | 2001 | 1 | 2003 | | 3 | Chardonnay | white | Geyser Peak | 2001 | 5 | 2003 | | 6 | Chardonnay | white | Simi | 2000 | 4 | 2002 | | 12 | Joh. Riesling | white | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | white |Ch. St. Jean | 2001 | 4 | 2003 | | 22 | Fume Blanc | white | Robt. Mondavi | 2000 | 2 | 2002 | | 30 | Gewurztraminer | white | Ch. St. Jean | 2002 | 3 | 2003 | | 43 | Cab. Sauvignon | red | Windsor | 1995 | 12 | 2004 | | 45 | Cab. Sauvignon | red | Geyser Peak | 1998 | 12 | 2006 | | 48 | Cab. Sauvignon | red | Robt. Mondavi | 1997 | 12 | 2008 | | 50 | Pinot Noir | red | Gary Farrell | 2000 | 3 | 2003 | | 51 | Pinot Noir | red | Fetzer | 1997 | 3 | 2004 | | 52 | Pinot Noir | red | Dehlinger | 1999 | 2 | 2002 | | 58 | Merlot | red | Clos du Bois | 1998 | 9 | 2004 | | 64 | Zinfandel | red | Cline | 1998 | 9 | 2007 | | 72 | Zinfandel | red | Rafanelli | 1999 | 2 | 2007 | +-----+----------------+-------|---------------+------+-----+-------+ Chardonnay is white--3 times Pinot Noir is red--3 times “redundancies”

  39. So, more tables--for example: Wine: Name, Type, Description, Characteristic Producer: Name, Area, Appellation

  40. Wines +----------------+-----------+------------------+---------------------+ | wine_name | wine_type | wine_description | wine_characteristic | +----------------+-----------+------------------+---------------------+ | Chardonnay | white | dry | buttery | | Joh. Riesling | white | semi-sweet | fruity | | Fume Blanc | white | dry | smoky | | Gewurztraminer | white | semi-sweet | spicy | | Cab. Sauvignon | red | dry | oaky | | Pinot Noir | red | dry | fruity | | Merlot | red | dry | plummy | | Zinfandel | red | dry | spicy | +----------------+-----------+------------------+---------------------+

  41. Note that this gives us the ability to describe a wine as "Red" in one place, rather than adding it to the CELLAR table and repeating it each time that wine appears. This eliminates "redundancy."

  42. Similarly, Producers +--------------+-----------------+-------------+ | name | area | appellation | +--------------+-----------------+-------------+ | Fetzer | Hopland | Mendocino | | Gary Farrell | Russian River V | Sonoma | | Geyser Peak | Alexander Valle | Sonoma | | Jekel | Arroyo Seco | Monterey | | . | . | . | | . | . | . | | . | . | . | | . | etc. | . | +--------------+-----------------+-------------+

  43. Thus the database, (the collection of tables) is "integrated," i.e., the entirety of the data is formed by use of all of the tables.

  44. Cellar +-----+----------------+---------------+------+---------+-------+ | bin | wine | producer | year | bottles | ready | +-----+----------------+---------------+------+---------+-------+ | 2 | Chardonnay | Buena Vista | 2001 | 1 | 2003 | | 3 | Chardonnay | Geyser Peak | 2001 | 5 | 2003 | | 6 | Chardonnay | Simi | 2000 | 4 | 2002 | | 12 | Joh. Riesling | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | Ch. St. Jean | 2001 | 4 | 2003 | | 22 | Fume Blanc | Robt. Mondavi | 2000 | 2 | 2002 | | 30 | Gewurztraminer | Ch. St. Jean | 2002 | 3 | 2003 | | 43 | Cab. Sauvignon | Windsor | 1995 | 12 | 2004 | | 45 | Cab. Sauvignon | Geyser Peak | 1998 | 12 | 2006 | | 48 | Cab. Sauvignon | Robt. Mondavi | 1997 | 12 | 2008 | | 50 | Pinot Noir | Gary Farrell | 2000 | 3 | 2003 | | 51 | Pinot Noir | Fetzer | 1997 | 3 | 2004 | | 52 | Pinot Noir | Dehlinger | 1999 | 2 | 2002 | | 58 | Merlot | Clos du Bois | 1998 | 9 | 2004 | | 64 | Zinfandel | Cline | 1998 | 9 | 2007 | | 72 | Zinfandel | Rafanelli | 1999 | 2 | 2007 | +-----+----------------+---------------+------+---------+-------+ The database: a collection of "files," or at least a collection of data that would otherwise usually exist in multiple files. Wines +----------------+-----------+------------------+---------------------+ | wine_name | wine_type | wine_description | wine_characteristic | +----------------+-----------+------------------+---------------------+ | Chardonnay | white | dry | buttery | | Joh. Riesling | white | semi-sweet | fruity | | Fume Blanc | white | dry | smoky | | Gewurztraminer | white | semi-sweet | spicy | | Cab. Sauvignon | red | dry | oaky | | Pinot Noir | red | dry | fruity | | Merlot | red | dry | plummy | | Zinfandel | red | dry | spicy | +----------------+-----------+------------------+---------------------+ Producers +--------------+-----------------+-------------+ | name | area | appellation | +--------------+-----------------+-------------+ | Fetzer | Hopland | Mendocino | | Gary Farrell | Russian River V | Sonoma | | Geyser Peak | Alexander Valle | Sonoma | | Jekel | Arroyo Seco | Monterey | | . | . | . |

  45. And, we can add Maps to the wineries Photographs of wineries, wine bottles Recordings of our own “tasting notes” Etc.

  46. The data can also be "shared," by programs or by users. (Single-user vs. multi-user systems.)

  47. Persistent vs. Transient data

  48. The persistent data. Once put into the database, it stays there until explicitly removed. Payroll System Reports Data Definition Class Scheduling System Database Management System Reports Faculty Data Class Data Student Data Grade Posting System Reports Transient or ephemeral data. Input, output, intermediate results.

  49. Another definition: A database is a collection of persistent data that is used by the application systems of some given enterprise.

More Related