Database W1 PDF

# DATABASE ## WEEK 1 Mahmoud Abd-elgalel ## Introduction to Database Systems ### Week1: Introduction and Basic Concepts ### Agenda - Basic Concepts - Introduction to Database Systems - Application Areas of DBMS - Examples of DBMS - DBMS Functionality - Database Characteristics ## Basic Concepts -...

# DATABASE ## WEEK 1 Mahmoud Abd-elgalel ## Introduction to Database Systems ### Week1: Introduction and Basic Concepts ### Agenda - Basic Concepts - Introduction to Database Systems - Application Areas of DBMS - Examples of DBMS - DBMS Functionality - Database Characteristics ## Basic Concepts - In computing, **data** is translating information into an efficient form as known facts to be processed, recorded, and stored by a computer system. - This information may have the form of text documents, images, audio, or other data types. - In other words, information is data that has been processed into a implicit meaningful form, e.g., Student Information. **Figure 1:** Transforming data into information * **INPUT** -> **PROCESSING** -> **OUTPUT** * **Users** * **Database** * **Data** ->**Transformation** -> **Information** * **STORAGE** - A **database** is an organized collection of related sets of data that are managed to enable the user to view the complete collection or any logical subset of the collection as a single unit. - **Database Management System (DBMS)** is a software package/system to create and maintain a computerized database. - A database system is considered as a computerized record-keeping system, including the DBMS software and the data itself. **Figure 2:** Database, DBMS, and database Systems * **Database System** with a **Database** inside. * **App** with **API** connecting to **DBMS-API** * **DBMS-API** connecting to **App** * **User** ## Introduction to Database Systems - The database is a kind of repository for collecting computerized data files and allow users to perform a variety of operations, such as: - adding new files to the database - inserting data into existing files - retrieving data from existing files - changing data in existing files - deleting data in existing files - removing existing files from the database ## Application Areas of DBMS - **Airlines**: For reservations and schedule information. - **Banking**: For customer information, accounts loans and banking transactions. - **Universities**: For student information, course registrations and grades. - **Credit card transactions**: For purchases on credit cards and generation of monthly statements. - **Telecommunications**: For keeping records of calls, generating monthly bills, maintaining balances on prepaid calling cards and storing information about the communication networks. - **Sales**: For customer, product and purchase information. - **Manufacturing**: For management of supply chain and for tracking production of items in factories, inventories of items in warehouses/stores and orders for items. - **Human Resources**: For information about employees, salaries, payroll taxes and benefits and for generation of paychecks. - **Web based services**: For taking web users feedback, responses, resource sharing, etc. ## Examples of DBMS - **MySQL Database**: It was founded in the year 1995. MySQL was acquired by Sun Microsystems in 2008 and the sun Microsystems was acquired by Oracle after two years. MySQL is one of the largest open-source companies in the world and is popular due to its high level of efficiency, reliability and cost. - **MS-Access**: It was developed by Microsoft and this computer-based application is used to form as well as create the databases on the desktop of the computers. It can be used for personal work and for small scale business that required a database. - **Oracle database**: It is the fourth relational database management system which is developed by Oracle Corporation. Oracle database is useful for storing a large amount of data, especially by large organizations. - **Microsoft SQL Server**: It is an RDBMS from Microsoft company which creates computer databases for MS- Windows. Based on customer's requirements, Microsoft has developed various versions of SQL Server. ## DBMS Functionality - Define a database in terms of data types, structures, and constraints. - Construct or Load the Database on a secondary storage medium. - Manipulating the database querying, generating reports, insertions, deletions, and modifications to its content. - Concurrent Processing and Sharing by a set of users and programs while keeping the data's validity and consistency. - **Other features:** - Protection or Security measures to prevent unauthorized access. - Active processing to take internal actions on data. - Presentation and Visualization of data. ## University Database Example - **Mini-world** are some parts of the real world in which data is stored in a database. - **Mini-world for the example** is part of the UNIVERSITY environment. - **Some mini-world entities:** - STUDENTS - COURSES - SECTIONS (of COURSES) - (academic) DEPARTMENTs - INSTRUCTORS - **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. ## Library Loan Database Example - **Figure 3** shows the database structure and sample data for a database that maintains information concerning borrowers, books, loans, and reservations in a library loan system. **Figure 3:** Sample data in relational form for Library Loan Database | **BORROWER** | **LOAN** | **BOOK** | **RESERVATION** | |---|---|---|---| | **BNUM** | **BNUM** | **BOOK#** | **BOOK#** | | 30202 | 30202 | 00001 | 0001 | | 30203 | 30202 | 00002 | 0002 | | 30204 | | 00003 | | | **BNAME** | **DATE-DUE-BACK** | **TITLE** | **BNUM** | | Ahmed Aly | 30/10/2005 | Database Systems | 30203 | | Aly Fathy | 30/10/2005 | Data Models| 30204| | Sherif Ahmed | | SQL | | | **ADDRESS** | | **AUTHOR** | **RESERVATION-DATE** | | Cairo | | C.J. Date | 30/10/2005 | | Cairo | | C.J. Bloggs | 10/11/2005 | | Giza | | | | | **STATUS** | | **PUBLISHER** | | | ug | | XYZ | | | pig | | ABC | | | staff | | XYZ | | | | | **YEAR** | | | | | 2001 | | | | | 2000 | | | | | 2001 | | | | | **PRICE** | | | | | 50 | | | | | 50| | | | | 40 | | | | | **SHELF** | | | | | H1 | | | | | H1 | | | | | H2 | | ## Database Characteristics - **Self-describing nature of a database system** - A DBMS catalog stores the description of the database. - This description is called **meta-data**, which allows the DBMS software to work with different databases. - **Insulation between programs and data** - Called **program-data independence**, that allows changing data storage structures and operations without having to change the DBMS access programs. - **Data Abstraction** - A **data model** is used to hide storage details and present the users with a conceptual view of the database. - **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. - **Sharing of data and multiuser transaction processing** - Allowing a set of **concurrent users** to **retrieve** and to **update** the database. - **Concurrency control** within the DBMS guarantees that each **transaction** is correctly executed or completely aborted. - **(OLTP) Online Transaction Processing** is a major part of database applications. ## Agenda - Traditional File Systems - Database Approach - Advantages of Using DBMS ## Traditional File Systems - **Before the existence of computer:** - The manual file system was used to maintain the data and records. - Files were used to store the data and it also makes it easier to find any sort of information from the files. - **To overcome the problems of the manual file system:** - Traditional File System was introduced which was entirely a computer-based system where all information were stored in different files on the computer. - It also stores the whole data in a manner that it becomes easy for the departments of the large industries have their different files, but it forms redundancy of the data. ### Traditional File Systems - The data of the students stored in three different files such as: - a separate data file for Library, - the second one is for examinations, and - the last one is for registration. - All these files create **duplicate data** such as the student number, their name, and phone numbers. **Figure:** Example of college data * **LIBRARY** with **LIBRARY APPLICATIONS** and **Registration Data Files** * **EXAMINATIONS** with **EXAMINATION APPLICATIONS** and **Registration Data Files** * **REGISTRATION** with **REGISTRATION APPLICATIONS** and **Registration Data Files** - **Figure: Example of library loan system** * **Reservation Data** * **Reservation Program** * **Loan Data** * **Loan Program** * **Overdue Loan Program** * **Reservation File** * **Loan File** * **Book File** * **Borrower File** * **Overdue Letters** ## Traditional File Systems - **Advantages:** - Simple functionality that fits the needs of small businesses and home users. - Less expensive. - **Disadvantages:** - Does not support multi-User access. - Limited to small organizations. - Limited functionality (i.e. no support for complicated transactions, recovery, etc.). - Decentralization of data. - Redundancy and Integrity issues. ## Database Approach - All data is stored in a single database. - The database will be physically stored in a set of files, but users and applications do not need to know anything about the physical storage. - Relationships between sets of data are represented in the database. - The description of database, including relationships is stored with actual data; this to enable the database management system (DBMS) to retrieve information from the database and to store new data in the database. - The applications do not directly access the database; instead, they pass requests to the DBMS to retrieve or store data. **Figure:** Example of library loan database system * **Reservation Data** * **Reservation Program** * **Loan Data** * **Loan Program** * **Overdue Loan Program** * **Overdue Letters** * **Borrower File** * **Data Base Management System (DBMS)** * **Library Database** * **Reservation** * **LOAN** * **Book** ## Traditional File Approach VS. Database Approach - In simple terms, a File Management System (FMS) is a Database Management System that allows access to single files or tables at a time. - FMS's accommodate flat files that have no relation to other files. - The FMS was the predecessor for the Database Management System (DBMS), which allows access to multiple files or tables at a time. **Figure:** FMS vs. BBMS * **FMS** * **Database** * **DBMS (True)** * **Database** ## Advantages of Using DBMS - **Controlling redundancy** in data storage and in development and maintenance efforts. - **Sharing of data** among multiple users. - **Restricting unauthorized access** to data. - **Providing Storage Structures** for efficient Query Processing. - **Data independence** provides an abstract view of the data that hides the details data representation and storage. - **Efficient Data Access** where variety of techniques are used to store and retrieve data. - **Data Integrity** we can ensure data integrity if the data is always enforced through integrity constraint. - **Data administration** deals with the modeling of the data and treats data as an organizational resource. - **Concurrent Access and crash recovery** ensures concurrent access of the data in such a way that the data is being accessed by only one user a time. Also protects the system from crashes. - **Reduced Application Development time** supports all the important functions that are common to many applications. ## Database Users - Users may be divided into: - **Actors on the Scene:** those who use and control the content, and - **Workers Behind the Scene:** those who enable the database to be developed and the DBMS software to be designed and implemented.