3 / 12

1. 3 / 12 CHAPTER Databases MIS105 Lec14 Irfan Ahmed Ilyas

2. Lecture Objectives • Integrated File Approach for saving data • Benefits/ Drawback • Database Accessing Approach • File based Programs • DBMS based access • Introducing DBMS • Working of a basic DBMS • Main DBMS Features • Users in a DBMS Environment Prepared By: Irfan Ilyas

3. …Database Implementation Approaches • Integrated File Approach • Data is scattered among many different data files • Each file is saving data relevant to its subject only • Files are linked together through data pointers (common field values) • The integrated collection of these data files is normally contained in one single disk file (database file) • Disadvantage: Cross-referencing across the files is needed for detailed reporting • Benefit: Data duplication is minimized which removes the updation anomalies Prepared By: Irfan Ilyas

4. Integrated File Database Example • A Student Grade Database • STUDENT – saving only student personal information • GRADE_REPORT – saving only grade information about student courses • The process which decompose data optimally among data files is called Normalization. Prepared By: Irfan Ilyas

5. …Integrated File Database Example • For detailed information, a cross-referencing activity is needed • While reading GRADE_REPORT, other student details can be found by using the data pointer (StudentNumber) in the STUDENT file • Data updation anomalies are removed • Addition Anomaly: While adding a new student’s grade in GRADE_REPORT file, no need to check for any data consistency . • Deletion Anomaly: Deleting all grades for any student, doesn’t delete the student information from STUDENT file. • Modification Anomaly: A student’s attribute change in STUDENT file (like Name) doesn’t need to be reflected in his grade records in GRADE_REPORT file (key attribute is an exception). Prepared By: Irfan Ilyas

6. Accessing a Database • Directly Accessing the database files • Programs which directly work upon database files in order to • handle data maintenance, like creating, defining, updating data, in files • carry out the end-user job. • Any high level language like C++, Java etc. can be used to write such programs • Disadvantages • Structural Dependence • These programs are very sensitive to the data file record structures • A small change in the structure of any file’s record needs to change all the programs working over it. • End users are always dependant on programmers for even a simple access to the database. Prepared By: Irfan Ilyas

7. Disk Storage Disk Storage Disk Storage 1. File Based Application Programs Accounting End User Registration End User Other End Users… Accounting Application Registration Application Any other application…. Prepared By: Irfan Ilyas

8. …Accessing a Database 2. Using DBMS Software • DBMS Software is a collection of general purpose programs that enables users to create and maintain an integrated file database • Separates the job of data maintenance from end-user use of stored data • Available as a packaged software, called General Purpose Database Management System (DBMS) • DBMS Main Features • Data Structural Independence • Different end-user applications can access database on disk through DBMS • DBMS makes the applications unaffected with any structural change happened in database • DBMS uses control information data (Data Dictionary) to inform the application programs about the structural details of the database files • Data Dictionary is updated automatically by DBMS in case of any structural change done on the database Prepared By: Irfan Ilyas Chapter 11 (p268)

9. A Database File on disk End User Application S/W DBMS Software End User A Database Example Database’s Data Dictionary Example: Data Dictionary DBMS User Prepared By: Irfan Ilyas

10. .. 2. Using DBMS Software • .. DBMS Main Features • Data Query Language • DBMS lets its users/ application programs to access database by making use of some query language • A query language is a set of standardized commands to perform different database maintenance activities • SQL (Structured Query Language) is the most widely used. • Examples of SQL commands are • CREATE TABLE – to create new data tables (files) • SELECT – to display selected data records • UPDATE – to modify selected data records • … and many others. Chapter 11 (p295) Prepared By: Irfan Ilyas

11. Details of DBMS Components • Database Engine • Controls all the data management activities (like database creation, retrieval, validation, manipulation etc.) • DBMS Interface Objects • Easy to use graphical interfaces (menus, buttons, wizards etc.) to access the underlying database • Each of these objects are interacting with database engine in the background through SQL commands Prepared By: Irfan Ilyas

12. A Look to Database Management Systems • Create, modify, gain access to databases • Subsystems • DBMS engine • Data definition subsystem • Data dictionary • Data manipulation subsystem • SQL • Application generation subsystem • Data administration subsystem • Database administrators Chapter 11, pg294 Prepared By: Irfan Ilyas

13. Database Engine Data Definition Subsystem Database Creation Subsystem Database Security Subsystem A Working DBMS DBMS Package Programmers Database Application Programming Interface End User S Q L C O M M A N D S Interface Objects Database User/ Operator Disk Storage Integrated File Database Prepared By: Irfan Ilyas

14. Database Personnel Chapter 11 (p276) • End Users • Requires access to the database for their jobs • Perform querying, updating and reporting on the stored database • Usually provided with a user friendly application program interface • Database Designers • Identifying the logical/ structural database picture for an environment • Database Administrators • Controlling security, structural updates and performance issues for a corporate database • System Analysts and Application Programmers • Responsible for generating easy to use application programs for the end user requirements Prepared By: Irfan Ilyas

15. Users in a DBMS Environment End User Database Designer Database Administrator Information System Application Program(s) DBMS Modules Data Definition Module Database Creation Module Data Manipulation Module Prepared By: Irfan Ilyas

16. Prepared By: Irfan Ilyas