Fundamentals of Database Lecture 1

FUNDAMENTALS OF DATABASE Prepared by: Joey Arellano, MAEd, MSIT, MSCS-CAR, Dhum(hc), DPed(hc)  What is Data? refers to raw facts, figures, and details collected from various sources, which can be processed to generate useful information.  Examples o...

FUNDAMENTALS OF DATABASE Prepared by: Joey Arellano, MAEd, MSIT, MSCS-CAR, Dhum(hc), DPed(hc)  What is Data? refers to raw facts, figures, and details collected from various sources, which can be processed to generate useful information.  Examples of Data 1.Textual Data 2.Numerical Data 3.Audio Data 4.Visual Data 5.Meta Data  What is Information?  Is data that have been processed, organized, or structured data that has been interpreted and presented in a context that gives it meaning and relevance.  Characteristics of Information 1.Processed Data – derived from data 2.Meaningful – it gives meaning/understandable 3.Contextual – compare/trends and patterns/provide reasons 4.Relevance – bases for course of actions/decisions  Database? A database is any collection of related data in a format that can be easily accessed (Digital) is a persistent, logically coherent collection of inherently meaningful data, relevant to some aspects of the real world.  Database Examples Education: Class roster (Student Information System) Healthcare: Hospital patients records Literature (published articles in a certain field) Telecommunications: ICMS, Billing, phone directory Homeowners association list Scientific Research Data Repository: google scholar Weather Data Repository Legal Case Management Database Online Learning Management System Database Supply Chain Management Database Music Streaming Service Database: Customer Support Ticketing System Database Government Databases (SSS, Phil-Health, Pag-Ibig, DSWD, BIR, etc.) Video Streaming Service Database Geographic Information System (GIS) Database Human Resources Database Library Catalog Database Retail and E-Commerce  Important Terms "Raw" facts that have little meaning unless they have been organized in some logical manner. The smallest piece of data is a single character, it requires one byte of Data computer storage A character or group of characters (alphabetic or numeric) that has a specific meaning Field (it may define a telephone number, a birth date, a customer name, and so on) A logically connected set of one or more fields that describes something like a person, Record place, or thing, and so on File A collection of related records Tables are the basic building blocks of a database, consisting of rows (records) and columns (fields).  Overview of DBMS  Database Management System (DBMS) is a software that manages databases.  It provides an interface for interacting with data and ensures the integrity, security, and consistency of the data. 1 0  A DBMS system stores data, in such a way which is easier to retrieve, manipulate and helps to produce information.  Characteristics of Modern DBMS  Real-world entity: Modern DBMS are more realistic and uses real world entities to design its architecture. It uses the behavior and attributes too. For example, a school database may use student as entity and their age as their attribute.  Relation-based tables: DBMS allows entities and relations among them to form as tables. This eases the concept of data saving. A user can understand the architecture of database just by looking at table names etc.  Isolation of data and application: A database system is entirely different than its data. Where database is said to active entity, data is said to be passive one on which the database works and organizes. DBMS also stores metadata which is data about data, to ease its own process.  Less redundancy: DBMS follows rules of normalization, which splits a relation when any of its attributes is having redundancy in values. Following normalization, which itself is a mathematically rich and scientific process, make the entire database to contain as less redundancy as possible.  Consistency: DBMS always enjoy the state on consistency where the previous form of data storing applications like file processing does not guarantee this. Consistency is a state where every relation in database remains consistent. There exist methods and techniques, which can detect attempt of leaving database in inconsistent state.  Query Language: DBMS is equipped with query language, which makes it more efficient to retrieve and manipulate data. A user can apply as many and different filtering options, as he or she wants. Traditionally it was not possible where file-processing system was used.  ACID Properties: DBMS follows the concepts for ACID properties, which stands for Atomicity, Consistency, Isolation and Durability. These concepts are applied on transactions, which manipulate data in database. ACID properties maintains database in healthy state in multi-transactional environment and in case of failure.  Multiuser and Concurrent Access: DBMS support multi-user environment and allows them to access and manipulate data in parallel. Though there are restrictions on transactions when they attempt to handle same data item, but users are always unaware of them.  Multiple views: DBMS offers multiples views for different users. A user who is in sales department will have a different view of database than a person working in production department. This enables user to have a concentrate view of database according to their requirements.  Security: DBMS offers methods to impose constraints while entering data into database and retrieving data at later stage. - DBMS offers many different levels of security features, which enables multiple users to have different view with different features.  What Does A DBMS Do? Database management systems provide several functions in addition to simple file management: allow concurrency – multiple transactions control security - protection maintain data integrity – accuracy, consistency and reliability provide for backup and recovery – for protection of data control redundancy – avoid duplication allow data independence – provide non-procedural query language – specific data to retrieve  Who Interacts with a DBMS? Many different individuals are involved with a database management system: systems analysts database designers database administrators application developers users  3 Major Involve in DBMS DBMS is used by various users for various purposes. Some may involve in retrieving data and some may involve in backing it up.  Administrators: A bunch of users maintain the DBMS and are responsible for administrating the database. They are responsible to look after its usage and by whom it should be used. - They create users access and apply limitation to maintain isolation and force security. Administrators also look after DBMS resources like system license, software application and tools required and other hardware related maintenance.  Designer: This is the group of people who actually works on designing part of database. The actual database is started with requirement analysis followed by a good designing process. They people keep a close watch on what data should be kept and in what format. They identify and design the whole set of entities, relations, constraints and views.  End Users: This group contains the persons who actually take advantage of database system. End users can be just viewers who pay attention to the logs or market rates or end users can be as sophisticated as business analysts who take the most of it. Database System 3-Tier Architecture 1. Database (Back-End) Tier: At this tier, only database resides. Database along with its query processing languages sits in layer-3 of 3-tier architecture. It also contains all relations and their constraints. 2. Application (Middle) Tier: At this tier the application server and program, which access database, resides. For a user this application tier works as abstracted view of database. Users are unaware of any existence of database beyond application. For database-tier, application tier is the user of it. - Database tier is not aware of any other user beyond application tier. This tier works as mediator between the two. 3. User (Front-End)) Tier: An end user sits on this tier. From a user’s aspect this tier is everything. He/she doesn't know about any existence or form of database beyond this layer. At this layer multiple views of database can be provided by the application. All views are generated by applications, which reside in application tier.  Five Major Parts of Database System 1. Hardware: The computer 2. Software: The operating system, DBMS. applications and utilities 3. People: Systems administrator, database administrators, programmers, end users 4. Procedures The instructions and rules that govern the design and use of the : database 5. Data: The collection of facts stored in the database, includes metadata Components of a Database System  Database System Architecture Basic Architecture  Database Engine: Manages data storage, retrieval, and manipulation.  Database Schema: Defines the structure of the database, including tables, fields, and relationships.  Query Processor: Interprets and executes SQL queries.  Transaction Manager: Manages transactions to ensure data consistency and integrity.  Storage Manager: Handles the physical storage of data on disk. Example Architecture: In MySQL, the architecture includes:  InnoDB Storage Engine: Handles transactions and ensures data integrity.  SQL Interface: Provides a way for users to interact with the 3 database using SQL queries. 0