Database Fundamentals Lecture 1 PDF

Database Fundamentals (CS2231) Lecture 1 Introduction to Databases 1 st Trimester 1446 1 Outline ◼ Basic Definitions ◼ File-based System vs. Database Approach. ◼ Advantages and disadvantages of Using the Database Approach ◼ Functions of DBMS. ◼ Exampl...

Database Fundamentals (CS2231) Lecture 1 Introduction to Databases 1 st Trimester 1446 1 Outline ◼ Basic Definitions ◼ File-based System vs. Database Approach. ◼ Advantages and disadvantages of Using the Database Approach ◼ Functions of DBMS. ◼ Example of a Database (UNIVERSITY) ◼ Main Characteristics of the Database Approach ◼ Types of Database Users 2 Basic Definitions Database: ◦ A collection of related data. Data: ◦ Known facts that can be recorded and have an implicit meaning. Metadata: ◦ Data that describes data Mini-world: ◦ Some part of the real world about which data is stored in a database. ◦ For example, student grades and transcripts at a university. 3 Sample Uses of Database Systems Buying products cashier Borrow books from a library icon nav_logo Rent cars from Expedia Dollar Rent A Car - Online car rental rates, quotes, and reservations! … 4 Traditional Methods File-based System: ◦ File system was an early attempt to computerize the manual filing system. ◦ A file system is a method for storing and organizing computer files and the data to make it easy to find and access. 5 Characteristics of File Processing System ◦ It is a group of files storing data of an organization. ◦ Each file is independent from one another. ◦ Each file is called flat file. ◦ Each file contained and processed information for one specific aspect, for example, student_info, course_info… etc. ◦ Files are designed and accessed by using application programs written in programming languages such as COBOL, C, C++, etc…. 6 Flat File o A flat file is a file containing records that have no structured interrelationship. 7 File-Based Systems Suppose you want to do the following things in your university: 1. Register courses. 2. Pay tuition. 3. Borrow books from the library. 8 File-Based System (An Example) File Entry Definition Report Handling File Entry Definition Report Handling File Entry Definition Report Library Handling Librarian Library file programs 9 Limitations of File-Based System Data Redundancy : often the same data is duplicated in two or more files. Assume the same data is repeated in two or more files. If change is made to data in one file, it is required that change be made to the data in the other file as well. If this is not done, it will lead to multiple different values for same data field. For example, we are managing the data of a university where a student is enrolled for two courses, the same student details in such case will be stored twice, which will take more storage than needed. Data redundancy often leads to higher storage space and time to enter the data more than once. 10 Limitations of File-Based System Data inconsistency: Data redundancy leads to data inconsistency. o For example, a student is enrolled for two courses and we have student address stored twice. o Student requests to change his address, if the address is changed at one place and not on all the records then this can lead to data inconsistency. Program-Data Dependence: Any change in data organization or format requires a change in all the programs associated with those files. 11 Limitations of File-Based System Limited Data Sharing: ◦ No centralized control of data Lengthy Development Times. More Program Maintenance Cost: ◦ 80% of information systems budget Low Data Security. Poor Data Modelling of Real World. 12 Old File Processing Systems (Another Example) Duplicate Data 13 Database Approach In order to remove all the above limitations of the File Based Approach, a new approach was required that must be more effective known as Database approach. Database: ◦ A collection of similar records with relationships between the records. e.g. University database, Bank database etc. Database Management System (DBMS): ◦ A software system that enables users to define, create, maintain, and control access to the database. 14 Database Approach Examples of DBMS: Database System: ◦ The DBMS software together with the data itself. Sometimes, the applications are also included. 15 Database Management System (DBMS) When programs access data in a database they are basically going through the DBMS. Databases Insert/update Enter Programmer /delete Metadata data/query / User Application DBMS program Information Query Data result/data 16 A Simplified DB System Environment 17 Database System (An Example) Entry Report Database Entry class, accounts, & Report Library data Entry Report Library Librarian programs 18 Advantages of the Database Approach ▪ Control of data redundancy: all data items are integrated with a minimum amount of duplication. ▪ Program-data independence: definition of data is separated from the application programs. ▪ Improved data sharing: the database is owned and accessed by the entire organization ▪ Data consistency: eliminating or controlling redundancy reduces the risk of inconsistencies occurring. 19 Advantages of the Database Approach ▪ Increased application development productivity ▪ Enforcement of standards ▪ Improved data accessibility and responsiveness: due to data integration and query languages like SQL ▪ Improved security. 20 Disadvantages of the Database Approach Up-front costs: ◦ Installation Management Cost and Complexity ◦ Conversion Costs Ongoing costs: ◦ Requires New, Specialized Personnel ◦ Need for Explicit Backup and Recovery 21 Functions of DBMS 1. Data Dictionary Management : ◦ Data Dictionary is where the DBMS stores definitions of the data elements and their relationships (metadata). ◦ The DBMS uses this function to look up the required data component structures and relationships. 22 Functions of DBMS 2. Data Storage Management: o The function is used for the storage of data and any related data entry forms or screen definitions, report definitions, data validation rules, procedural code, and structures that can handle video and picture formats. o Users do not need to know how data is stored or manipulated. 23 Functions of DBMS 3. Data Transformation and Presentation: o This function exists to transform any data entered into required data structures. 4. Security Management: o Security management sets rules that determine specific users that are allowed to access the database. o Users are given a username and password to access the database. This function also sets restraints. 24 Functions of DBMS 5. Multiuser Access Control: o Data integrity and data consistency are the basis of this function. o Multiuser access control is a very useful tool in a DBMS, it enables multiple users to access the database simultaneously without affecting the integrity of the database. 25 Functions of DBMS 6. Backup and Recovery Management: o Backup and recovery are used whenever there is potential outside threats to a database. For example, if there is a power outage, recovery management is how long it takes to recover the database after the outage. o Backup management refers to the data safety and integrity. 26 Functions of DBMS 7. Data Integrity Management : o The DBMS enforces these rules to reduce things such as data redundancy, which is when data is stored in more than one place unnecessarily, and maximizing data consistency, making sure database is returning correct/same answer each time for same question asked. 27  Functions of DBMS 8. Database Access Languages and Application Programming Interfaces: o A query language is a nonprocedural language. e.g. SQL (structured query language). o SQL is the most common query language supported by the majority of DBMS. 28 Functions of DBMS 9. Database Communication Interfaces: o This refers to how a DBMS can accept different end user requests through different network environments. e.g. internet. o A DBMS can provide access to the database using the Internet through Web Browsers (Mozilla Firefox, Google Chrome). 29 Example of a Database Mini-world for the example: ◦ Part of a UNIVERSITY environment. Some mini-world entities: ◦ STUDENTs ◦ COURSEs ◦ SECTIONs (of COURSEs) ◦ (academic) DEPARTMENTs ◦ INSTRUCTORs 30 Example of a Database (cont’d) Some mini-world relationships: ◦ SECTIONs are of specific COURSEs ◦ STUDENTs take SECTIONs ◦ COURSEs have prerequisite COURSEs ◦ INSTRUCTORs teach SECTIONs ◦ COURSEs are offered by DEPARTMENTs ◦ STUDENTs major in DEPARTMENTs Note: The above entities and relationships are typically expressed in a conceptual data model, such as the ENTITY-RELATIONSHIP data model 31 Example of a Database (cont’d) 32 Characteristics of the Database Approach Self-describing nature of a database system: ◦ A DBMS catalog stores the description of a particular database (e.g. data structures, types, and constraints) ◦ The description is called meta-data. ◦ This allows the DBMS software to work with different database applications. Insulation between programs and data: ◦ Called program-data independence. ◦ Allows changing data structures and storage organization without having to change the DBMS access programs. 33 Example of a Simplified Database Catalog 34 Characteristics of the Database Approach Data Abstraction: ◦ A data model is used to hide storage details and present the users with a conceptual view of the database. ◦ Programs refer to the data model constructs rather than data storage details Support of multiple views of the data: ◦ Each user may see a different view of the database, which describes only the data of interest to that user. 35 Characteristics of the Database Approach Sharing of data and multi-user transaction processing: ◦ Allowing a set of concurrent users to retrieve from and to update the database. ◦ Concurrency control within the DBMS guarantees that each transaction is correctly executed or aborted ◦ Recovery subsystem ensures each completed transaction has its effect permanently recorded in the database ◦ OLTP (Online Transaction Processing) is a major part of database applications. This allows hundreds of concurrent transactions to execute per second. 36 Types of Database Users ◼ End-users: Use the database for queries, reports, and updating the database content. For example, student ,teacher or any employee are the end users of the university database. ◼ Database Designers: Responsible for defining database structure, constraints, and transactions; communicate with users to understand their needs. 37 Types of Database Users ◼ Database administrators: Responsible for authorizing/controlling access to the database; coordinating and monitoring databas use; acquiring software and hardware resources; and monitoring efficiency of operations. 38 Conclusion Database System: it contains the DBMS software together with the data itself. DBMS stands for database management system. The database approach has many advantages when it comes to storing data compared to traditional File-based System. Types of Database Users: End-users, Database Designers, Database administrators. 39