Database Management System

Unit V : Database Management System Introduction to Databases A database is an organized collection of structured information or data that is stored electronically in a computer system. Databases are managed by Database Management System (DBMS) which allow users to Create, retrieve , up...

Unit V : Database Management System Introduction to Databases A database is an organized collection of structured information or data that is stored electronically in a computer system. Databases are managed by Database Management System (DBMS) which allow users to Create, retrieve , update, and delete the data efficiently We can think of database as an electronic filing system. Traditional paper-based filing system can be slow, inflexible, prone to errors Database servers as a centralized repository for storing, managing and retrieving data enabling users and applications to efficiently access and manipulate information. Database are integral to almost every industry, enabling streamlined operations, data-driven decision making and improved customer experience. Database Cont.. Example of databases in management sectors are: Customer Databases: stores to track customer purchases, preferences and personal details Financial Records: Bank and financial institution maintain databases that record transactions, account balances , loan information Employee Records Databases: Used by HR departments to store detail employee information such as Personal details, employement history, job performance, and benefit, Inentory Management Database: Utilized by retailers, warehouse, and manufacturers to track stock levels, order histories and supplier details. Databases have become a importance of modern computing, enabling the efficient, secure and reliable management of data across various industries. Evolution of Databases The shift from paper based systems to digital database began in the mid20th century and has evolved through several stages of innovation 1960s- The birth of Digital Databases Introduction of Hierarchical and Network System(IMS), first widly adopted database system Databases organized data in tree-like structure 1970s- The Relational Model – introduced E.F. Codd Data storage by organizing data into tables (Tables) Flexiblity in querying data using Structured Query Language (SQL) 1980s- 1990 – Rise of Commericial Database Management System(DBMS) and Advanced (ADBMS)- Oracle, Microsoft SQL Server, IBM DB2 Feature like Integrity, security, multi-user access, Transaction management Golden age of lunch when huge application uses this DBMS 2000s to Present: NoSQL and Big Data : 21st century MongoDB, Cansandra, and CouchDB , Cater to the demands of big data, handling unstructured informaiton, Supporting operations such Social media , e-commerece and real-time analytics Ensuring better performance along with large volume of data Importance of Data in Business Decision making Data plays a pivotal role in the process , as data-driven decision making has emerged. Can improve decision accuracy, reduce risks and enhance operational efficency Data to identify Opportunity, track consumer behaviour, opimize processes Example in Marketing Targeted Adertising : Facebook, Instagram, Tiktok, Google uses data analytics to deliver perosnalized ads by analyzing user browsing histroy, interests and demographics. Create tailored marketing campaigns that increase customer engagement and conversions Example in Finance: Credit Risk Assessment: Predictive analytics to assess the risk of lending by analyzing historical credit data, guiding decisions on loan approvals and interest rate Real-world Success Story : Netflix and Youtube Netflix – analysing viewing patters to offer personalized content, recommendation Youtube – uses behaviour data such as watch history and engagement metrics, personalized video recommendation and optimize content strategis Benefits and Challenges Benefits Improving Effiency Gaining Customer Insights Predictive Analysis Challenges Volume Accuracy Decision-Making Biases Data Privacy and Compaliance Resistance to change Skill Gap Type of Data : Structured vs. Unstructured Data and Information Data Collection of raw facts, figures or observation that by itself does not convey any meaningful information Number, characters, symbol or even media files Example of Data : Ram Sharma Kathmandu, Pooja Information Shrama , Bhaktapur Processed, organized and structured data that Example of information: After processing the raw holdes meaning and can data be used for decision making S.No. Name, Address Relationship between data 1. Ram Sharma, Kathmandu points and easily presented and interpreted 2. Pooja Sharma, Bhktapur 3, Surya Bista, Pokhara Structured Data vs Unstructured Data Structured data – any data that is highly organized Easily searchable, stored in a fixed format or schema It resided in relational database or spreadsheet Characteristics : Predefined format Data is organized in fixed format- Each field has a specific purpose and data type (Text, number, date, Boolean- true/false) Easy to search Can be easily search using standard algorithm Queries can be performed quickly and efficiently Consistency All data in a structured format follows the same pattern Consistency makes it easier to analyze and process Example : Organized Schema - index types of Follow well-defined schema or data model, Ensures that each field has specified type and purpose- duplication, information, data type mismatch - Highly Scalability Employee detail on Performance should not degrade after allowing large volume of data excel sheet, Customer information in a Unstructured Lack a predefined format, making it complex to store and analyze using traditional database Often comes in Large volume, text-heavy documents Example : Social media posts, content from email, videos and images any sources Characteristics No Predefined format: doesn’t follow a specific structure or format Difficult to search: struggle to find and get on promt request High volume: large quantities, generated from srouces like social media, emails and multimedia content. Varity : come with many forms, from text document to multimedia files Advanced Processing Needs Database Management System (DBMS) A DBMS is a Software system that facilitates the creation, management, manipulation of Databases. Acts as an interface between end users, applications and database itself, Ensures data is stored, organized, retrieved, update and delete Provides a systematic way to ensure its integrity, security and consistency. Functions of DBMS: Data Storage, Retrieval and management Data Security: User authentication and authorization – read, write and delete Data Integrity and Consistently: remain accurate and consistence, preventing discrepancies Backup and Recovery : regular saving database copies, safety and restroing Concurrency Control: multiple users without data inconsistency Data Dictionary management : Transaction Management: transaction are completed successfully or non at all, maintain integrity Advantages, Disadvantage DisAdvantages Advantages Complexity : Improved Data Sharing Complex, requiring specialized Data sharing among users and application Increased Data Security knowlede and training Advanced security measure, helping Cost: organization comply with regulation and safeguard information Initial setup, licensing and ongoing Reduced Data Redendency maintenance Minimize data duplication by centralizing data storagee, leading to more efficient data Peformance Overhead management and reduced storage costs Large volume of transaction ro data Better Data Integration Data Vunnerability To integrate data from different sources, providing unified view of information for Offer security but target for comprehensive analysis Cyberattacks Enhanced Data Management Dependency on the DBMS Vendor Streamline data handling processes, improving efficiency in data entry, retrieval Dependent on the specific DBMS and reporting vendor, making it difficult to switch or integrate with new technologies  Components Types and Component Data Actual infrmation stores on Relational DBMS- RDBMS database Common type Hardware MySQL, Oracle, Microsoft SQL serer Physical devices Hard Drive Object-Oriented DBMS Network storage Stores data as objects, methods and attributes Software ObjectDB, Versant DBMS software itself and Document DBMS: any related application Client, main application Operation happened on Document Database Access Document format like JSON, XML MongoDB, CouchBase Language Language used to access Key-value: key-value pair and manipulate the data Redis, Amazon Dynamodb SQL Graph DBMS Database Engine Core software component Represented on database Query Processor Neo4j, Amazon Neptune Data Dictonary Column-oriented Report Writer Stores data tables as section of columns rather than Tools for creating formatted rows. report Apache Cassandra, HBase Relation Database (Tables, Queries, Reports) Relation Database Management System(RDBMS), designed To store and manage data in structured format using tables that interlinked through keys and relationship Ensure data intrigrity, reduce redundancy,and use of primary and foreign keys Tables A table is a relational database is a collection of related data organized into rows and columns. Each table represents a specific entity, rows (records) and columns (Fields) Columns (Fields): Define attributes or characteristics of the entity. Each column has a specific data type (e.g. text, integer, date, boolean) Rows (Records): Contains the data for each instance of the entity. Each row holds a unique set of information Establish Relationship: Tables are linked through keys(e.g PK and FK) Primary Key(PK): A column or combination of columns that uniquely identifies each row in the table (e.g. CustomerID, OrderID) Foreign Key (FK) : a column that links one table to another by referencing the primary key of the related table Example in Next Slide yytgghgg Queries- Structured Query Language (SQL) Queries are commands used to interact with relation database. Allows users to retrieve, manipulate and update data stored in tables. Standard language for querying relational databases is SQL. SELECT : Retrieves data from one or more table Ex: SELECT Name, Department from Employees Ex : SELECT * FROM Sales where amount > 1000; Ex : SELECT * FROM Salesh where date BETWEEN ‘2025-01-01’ AND ‘ 2025-01-31’; INSERT : Add new records to a table Ex: INSERT INTO Sales (date, amount) VALUES (‘2025-01-01’,10000); UPDATE : Modifies existing records in table Ex: Update Sales SET amount=200 where date =‘2025-01-01’ DELETE : Removes records from table Ex: DELETE FROM Sales WHERE amount < 50; Ex: DELETE FROM Salesh WHERE date=‘2025-02-02’; ` Reports and Data Visualization Reports present data in a structured format, summarizing key information to assist decision- making processes Often includes table, charts and other visual elements to highlight important metrics and trends Data Visulaization takes reports further by converting raw data into interactive charts, graphs, and dashboards. Visual tools make it easier to interpret complex data and identify insights, enhanching the ability to make data-driven decision Importance of Reports and visualization Identify trends and pattern Communicate insights in an understandable format  Monthly Sales Report - October 2024 250 Total Amount (RS) 200 Customer Order Total Amount Name Order ID Date City (RS) 150 Ramesh 10/10/202 Kathmand 100 Khadka 101 4u 150 12/10/202 50 Kartika Dhital 102 4 Lalitpur 200 0 13/10/202 Kathmandu Lalitpur Pokhara Kathmandu Bhaktapur 10/10/2024 12/10/2024 13/10/2024 13/10/2024 15/10/2024 Aarati Sapkota 103 4 Pokhara 120 101 102 103 105 104 13/10/202 Kathmand Ramesh Kartika Dhital Aarati Sita Karki Asmita Sita Karki 105 4u 200 Khadka Sapkota Katiwada Asmita 15/10/202 Katiwada 104 4 Bhaktapur 180 Total Sales 850 Introduction to SQL(Structured Query Language) SQL is a standard language used for interacting with relational database. Standardized programming language used for managing and manipulating relational databases. Key Benefit of SQL: Ease of Use : SQL uses simple command , easy to learn and apply Efficient Data Retrieval : Can retrieve large amounts of data quickly Flexibility: Flexible language can use many table at once SQL command are divided into several categories 1. Data Definition Language (DDL): define and modify the structure of database object itself CREATE, ALTER, DROP, TRUNCATE 2. DATA Manipulation Language (DML): manipulate data within database SELECT, INSERT, UPDATE, DELETE 3. Data Control Language (DCL) : Control access to data within the database. GRANT, REVOKE 4. Transaction Control Language (TCL): to manage the changes made by DML statement COMMIT, ROLLBACK, SAVEPOINT Basics of Data Storage and Retrieval Understanding how data is stored and retrieval in database. knowledge help in designing efficient database (table, columns, relations), writing queries and troubleshooting performance issues. Data Storage Physical Storage Hard drives(HDDS), Solid-state-drives(SSDs) Files and Pages Database organizes data into the files, which are further divided into pages. A page is the smallest unit of data transfer between disk and memory Tables and Indexes Relational databases, data is logically organized into tables Record storage Within table, data is stored as records (or rows) Data Types Different data type – integers, text, dates Compression Many databases use compression technique to reduce storage space Also helps to improve performance by reducing I/O operation Data Retrieval The process of accessing and retrieving store data from a database,typically using querying. Select specific columns and rows. Join tables based on relationship Apply filters and aggregations for more refined results. Retrival includes Query processing Parsing, optimization, execution Access Methods Table scan, row scan , other db level scan Buffer Management Intermediate storage management Query optimization Best execution plan for every query’s result Concurrency control Multiple users are accessing database simultaneously Transaction management Series of database operation are treated as a single unit of work Introduction to Big Data and Its Business Applications Years of information age, BIG-DATA, How organization collect, analyze and utilize vast amount of information. BIG-DATA Big Data – extremly, complex data sets – can not be easily managed, processed or analyzed using traditional data processing applications. Massive volume of data processed and analyzed After that can reveal patterns, trends and associations, especially relating to human behaviour and interactions. Characteristics of BIG DATA Volume Velocity Variety Source of Big Data Social Media: Facebook, Twitter, Instagram Internet of Things (IoT): Smar home , Smart devices – ring, watch, glass Transactional Data : organization’s purchase history, business generates data Machine Data: Data generated by industrial equipment, sensors in vichicles etc Biometrics : data from facial reognition, genetics, handwriting , retinal scans Human-Generated Data: email, survey, voice recording, conversation Business Application of Big Data Big Data is applied across various business sectors to improve decision-making, optimize operations ehance customer experience Big Data applications in business Retail- Company use Big-Data to analyze customer buying patterns, trailor marketing strategies, optimize inventory management Anticipate demad and streamline supply chains, Finance: Fraud detection, risk management, customer insights After analysing transaction pattern, historical data, identified anomalies in ream-time Healthcare: predict potential health risks, optimize treatment plan, Manufacturing : predict for maintenance, sensors collect all information Marketing : Analyze customer behaviour and prefereces Highly targeted adverting campaign, Transportation and Logistics : route optimization, traffic forecasting, DHL and FedEx use data from GPS, weather and traffic pattern to optimize delivery route Telecommunication : Network optmization, customer churn prediction and personalized service offerings, Analyzing customer usage patterns and feedback Data Security and Ethical Considerations Data Security involves practices and technologies to protect senstive information from unauthorized access and cracks, ensuring confidententility, integrity, and availability throughout its lifecycle. Can protect valuable data while ensuring that they comply ethical standards that promotes Fairness, privacy and social responsiblity Ethical considerations focus on the moral responsibilities associated with data collection and usage, emphasizing privacy rights, informed consent, and data security. It ensures that individuals' personal information is handled with transparency and accountability, preventing misuse or unauthorized access. Additionally, ethical data practices involve fairness, accuracy, and avoiding bias, especially in decision-making processes that impact individuals. Organizations must also comply with legal and regulatory frameworks to maintain trust and integrity in data handling. Data Security and Ethical Consideration contd. Data Security Ethical Consideration Importance of Computer Ethics Confidentiality ethics plays a vital role in ensuring that Integrity technology or process guides for ethics in Availability Data management Protecting Personal and Commerical Common Data Security Information Preventing Cybercrimes Threats Ensuring Accessibility Malware Promoting Fair Business practices Encourage fair competition, prevent Phishing expolitation or manipulation SQL injection Fostering Moral and Social Values Ethical standards for technology use, Man-in-the-Middle Attacks computer ethics ep reinformce social values like trust, accountability and respect Assignment Imagine a new business application for Big Data and describe its potential impact. Explain the importance and potential benefit for the people of Nepal by applying data, Information, Data analytics and Big Data Technlogies. Explain with example and with its potential impact for economic development