1 / 17

Database Management Systems

Database Management Systems . Introduction. In the Beginning…. Customer. Program 1. Program-Data Dependence. DATA DIVISION. FILE SECTION. FD EMP-FILE LABEL RECORDS ARE OMITTED. 01 EMP-RECORD. 05 EMP-NUMBER PIC 9(4). 05 EMP-LASTNAME PIC X(11).

cicely
Télécharger la présentation

Database Management 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. Database Management Systems Introduction

  2. In the Beginning… Customer Program 1

  3. Program-Data Dependence DATA DIVISION. FILE SECTION. FD EMP-FILE LABEL RECORDS ARE OMITTED. 01 EMP-RECORD. 05 EMP-NUMBER PIC 9(4). 05 EMP-LASTNAME PIC X(11). 05 EMP-FIRSTNAME PIC X(11). 05 EMP-SEX PIC X(1). 05 EMP-DEPTID PIC X(4). 05 EMP-SALARY PIC 9(8).

  4. File System Model Cust Program 1 Program 2 Sales Program 3 Program 4 Inv Program 5

  5. Problems with File System Model • Changes to file structure or file location effect many programs causing high maintenance costs. • Data in various and sometimes proprietary data formats. • FileIndexes were easily corrupted if not open during data entry, updates, or deletes. • All Data validation was completely dependent on all application programs. • All Data security was completely dependent on all application programs. • Efficient Multi-application / multi-user access to the same file(s) required strict adherence to agreed upon locking strategies. • Integrated backup and recovery of hundreds of data files is difficult to control. • Tendency for redundant data to enter various data files.

  6. The Solution: DBMS File 1 Program 1 DBMS Program 2 Cust SQL Program 3 Sales Inv Program 4 File X Program 5 Changes to file structure or file location are transparent to application programs. Maintenance costs drop dramatically.

  7. The Solution: DBMS Q & R tools File 1 Program 1 DBMS Program 2 Cust SQL Program 3 Sales Inv Program 4 File X Program 5 All data is available through a standard SQL interface and related, industry standard query and reporting tools.

  8. The Solution: DBMS File 1 Program 1 DBMS Program 2 Index Index Cust SQL Program 3 Index Sales Inv Program 4 Index File X Program 5 AllIndexes are automatically opened and updated during insert, update, and delete operations eliminating the primary cause of index corruption.

  9. The Solution: DBMS File 1 Program 1 DBMS Constraints Program 2 Cust SQL Program 3 Sales Inv Program 4 File X Program 5 All Data validation rules are defined within the DBMS and enforced independently of application program logic.

  10. The Solution: DBMS File 1 Program 1 DBMS Program 2 Users Cust SQL Program 3 Sales Grants Inv Program 4 File X Program 5 Primary responsibility for Data security is now handled by the DBMS providing user based security down to the attribute level.

  11. The Solution: DBMS File 1 Program 1 DBMS Program 2 Cust SQL Program 3 Sales • Locks • Rollbacks • Commits • Transactions Inv Program 4 File X Program 5 All aspects of multi-user access are handled by the DBMS.

  12. The Solution: DBMS Backup Recovery File 1 Program 1 DBMS Program 2 Recovery Cust Log Files SQL Program 3 Sales Inv Program 4 File X Program 5 A comprehensive, integrated solution to backup and recovery is provided.

  13. DBA The Solution: DBMS File 1 Schema Program 1 DBMS Program 2 Cust SQL Program 3 Sales Inv Program 4 File X Program 5 A single normalized conceptual model of all data managed by a database administrator (DBA) eliminating redundant and therefore inconsistent data.

  14. Summary of DBMS Solutions • Changes to file structure or file location are transparent to application programs. Maintenance costs drop dramatically. • All data is available through a standard SQL interface and related, industry standard query and reporting tools. • AllIndexes are automatically opened and updated during insert, update, and delete operations eliminating the primary cause of index corruption. • All Data validation rules are defined within the DBMS and enforced independently of application program logic. • Primary responsibility for Data security is now handled by the DBMS providing user based security down to the attribute level. • All aspects of multi-user access are handled by the DBMS. • A comprehensive, integrated solution to backup and recovery is provided. • A single conceptual model of all data managed by a DBA.

  15. Relational Database Management Systems Key Constructs

  16. Relations and Tuples Employees Table EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 Jones Mark M ITR 555-1087 45000 25 Smith Sara F FINC 555-2222 49000 26 Billings David M ACTG 555-4356 42000 31 Dance Ivanna F ACTG 444-4887 60000 32 Jones Mary F ITR 555-8745 70000 35 Barker Bob M ACTG 555-6565 44000 36 Woods Robin M ITR 555-9812 90000 37 Jones Mary F FINC 555-1234 56000

  17. Retrieving Info from Multiple Tables Programs Table Employees Table EMPID LANGUAGE PROFICIENCY EMPID LNAME FNAME SEX DEPT PHONE SALARY 23 COBOL GOOD 23 Jones Mark M ITR 555-1087 45000 23 JAVA GOOD 25 Smith Sara F FINC 555-2222 55000 23 SQL EXCELLENT 26 Billings David M ACTG 555-4356 42000 31 SQL EXCELLENT 31 Dance Ivanna F ACTG 444-4887 60000 32 JAVA EXCELLENT 32 Jones Mary F ITR 555-8745 70000 32 SQL GOOD 35 Barker Bob M ACTG 555-6565 44000 32 VB GOOD 36 Woods Robin M ITR 555-9812 90000 32 COBOL EXCELLENT 37 Jones Mary F FINC 555-1234 56000 36 VB EXCELLENT 36 SQL EXCELLENT 36 JAVA EXCELLENT Languages Table 37 COBOL GOOD 37 SQL FAIR NAME FULLNAME COBOL COmmon Business Oriented Language JAVA JAVA SQL Structured Query Language VB Visual Basic

More Related