Lesson 03 - Database Management Systems.pdf

5/19/2024 Database Management Systems Dilhani Abeykoon Department of Marketing Faculty of Management and Finance...

5/19/2024 Database Management Systems Dilhani Abeykoon Department of Marketing Faculty of Management and Finance 1 University of Ruhuna CONTENTS ✓ Paper Based Systems ✓ What is DBMS ✓ Database vs. DBMS ✓ Characteristics of DBMS ✓ Advantages and Disadvantages of DBMS ✓ Components of Database ✓ Database Architecture ✓ RDBMS ✓ Exercises Lecturer - Dilhani Abeykoon 2 1  5/19/2024 Paper Based Systems 3 Paper Based Systems ▪ For ages, businesses have been following a paper-based document management model. Decades ago, the documents may not have been as detailed and the document management systems not as elaborate as now; but they still were systems. ▪ Easy handling: As paper is physically tangible and visible, Such documents can be stored systematically in files, folders, cabinets etc. ▪ Reproduction: You can copy a document by using a photocopier machine Lecturer - Dilhani Abeykoon 4 2  5/19/2024 Paper based vs. electronic DBMS Lecturer - Dilhani Abeykoon 5 Cont. ▪ Storage space ▪ Recurring cost of supplies ▪ Limited mobility ▪ Editing messy ▪ Collaboration difficult ▪ Vulnerable to damage Lecturer - Dilhani Abeykoon 6 3  5/19/2024 What is DBMS 7 Database Management System Database management system is a software which is used to manage the database. For example: MySQL, Oracle, etc. are a very popular commercial database which is used in different applications Lecturer - Dilhani Abeykoon 8 4  5/19/2024 Database & DBMS Lecturer - Dilhani Abeykoon 9 Popular DBMS Software MySQL Microsoft Access Oracle PostgreSQL dBASE FoxPro SQLite IBM DB2 LibreOffice Base MariaDB Microsoft SQL Server etc. Lecturer - Dilhani Abeykoon 10 5  5/19/2024 Why Use DBMS? ▪ To develop software applications in less time ▪ Data independence and efficient use of data ▪ For uniform data administration ▪ For data integrity and security ▪ For concurrent access to data, and data recovery from crashes ▪ To use user-friendly declarative query language Lecturer - Dilhani Abeykoon 11 Application of DBMS Lecturer - Dilhani Abeykoon 12 6  5/19/2024 Types of Databases ▪ Numeric and Textual Databases ▪ Multimedia Databases (Picture, Audio, Video clips, Voice, Sound, messages) ▪ Geographic Information Systems GIS (maps, weather data, satellite images) Lecturer - Dilhani Abeykoon 13 University Database ▪ The STUDENT file stores data of each student ▪ The COURSE file stores contain data on each course. ▪ The SECTION stores the information about sections in a particular course. ▪ The GRADE file stores the grades which students receive in the various sections ▪ The TUTOR file contains information about each professor. Lecturer - Dilhani Abeykoon 14 7  5/19/2024 University Database Example Lecturer - Dilhani Abeykoon 15 Advantages of DBMS ▪ Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database ▪ Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users ▪ Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system. Lecturer - Dilhani Abeykoon 16 8  5/19/2024 Cont. ▪ Reduce time: It reduces development time and maintenance need ▪ Backup: It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required ▪ Multiple user interface: It provides different types of user interfaces like graphical user interfaces, application program interfaces Lecturer - Dilhani Abeykoon 17 Disadvantages of DBMS ▪ Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS software ▪ Size: It occupies a large space of disks and large memory to run them efficiently ▪ Complexity: Database system creates additional complexity and requirements ▪ Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever. Lecturer - Dilhani Abeykoon 18 9  5/19/2024 Users in a DBMS Environment Lecturer - Dilhani Abeykoon 19 Cont. Lecturer - Dilhani Abeykoon 20 10  5/19/2024 Components of Database 21 Components of Database ▪ The database management system can be divided into five major components, they are: ✓ Hardware ✓ Software ✓ Data ✓ Procedures ✓ Database Access Language Lecturer - Dilhani Abeykoon 22 11  5/19/2024 Cont. Lecturer - Dilhani Abeykoon 23 Cont. Lecturer - Dilhani Abeykoon 24 12  5/19/2024 Hardware ▪ Computer, hard disks, I/O channels for data, and any other physical component involved before any data is successfully stored into the memory ▪ When we run Oracle or MySQL on our personal computer, then our computer's Hard Disk, our Keyboard using which we type in all the commands, our computer's RAM, ROM all become a part of the DBMS hardware Lecturer - Dilhani Abeykoon 25 Software ▪ This is the main component, as this is the program which controls everything ▪ Provides us with an easy-to-use interface to store, access and update data ▪ The DBMS software is capable of understanding the Database Access Language and interpret it into actual database commands to execute them on the DB Lecturer - Dilhani Abeykoon 26 13  5/19/2024 Data ▪ Data is that resource, for which DBMS was designed. The motive behind the creation of DBMS was to store and utilize data. ▪ In a typical Database, the user saved Data is present and meta data is stored. ▪ Metadata is data about the data. This is information stored by the DBMS to better understand the data stored in it. ▪ For example: When I store my Name in a database, the DBMS will store when the name was stored in the database, what is the size of the name, is it stored as related data to some other data, or is it independent, all this information is metadata. Lecturer - Dilhani Abeykoon 27 Procedures ▪ Procedures refer to general instructions to use a database management system. This includes procedures to setup and install a DBMS, To login and logout of DBMS software, to manage databases, to take backups, generating reports etc. Lecturer - Dilhani Abeykoon 28 14  5/19/2024 Database Access Language ▪ Database Access Language is a simple language designed to write commands to access, insert, update and delete data stored in any database. Lecturer - Dilhani Abeykoon 29 Example Lecturer - Dilhani Abeykoon 30 15  5/19/2024 Example Lecturer - Dilhani Abeykoon 31 Database Architecture 32 16  5/19/2024 Types of DBMS Architecture ▪ There are mainly three types of DBMS architecture: ✓ One Tier Architecture (Single Tier Architecture) ✓ Two Tier Architecture ✓ Three Tier Architecture Lecturer - Dilhani Abeykoon 33 1-Tier Architecture ▪ 1 Tier Architecture in DBMS is the simplest architecture of Database in which the client, server, and Database all reside on the same machine ▪ A simple one tier architecture example would be anytime you install a Database in your system and access it to practice SQL queries ▪ But such architecture is rarely used in production Lecturer - Dilhani Abeykoon 34 17  5/19/2024 Cont. Lecturer - Dilhani Abeykoon 35 2-Tier Architecture ▪ A 2 Tier Architecture in DBMS is a Database architecture where the presentation layer runs on a client (PC, Mobile, Tablet, etc.), and data is stored on a server called the second tier ▪ Two tier architecture provides added security to the DBMS as it is not exposed to the end-user directly. It also provides direct and faster communication ▪ Example: A Contact Management System created using MS- Access Lecturer - Dilhani Abeykoon 36 18  5/19/2024 2-Tier Architecture Lecturer - Dilhani Abeykoon 37 3-Tier Architecture ▪ A 3 Tier Architecture in DBMS is the most popular client server architecture in DBMS in which the development and maintenance of functional processes, logic, data access, data storage, and user interface is done independently as separate modules ▪ Three Tier architecture contains a presentation layer, an application layer, and a database server Lecturer - Dilhani Abeykoon 38 19  5/19/2024 3-Tier Architecture ▪ 3-Tier database Architecture design is an extension of the 2-tier client-server architecture. A 3-tier architecture has the following layers: 1. Presentation layer (your PC, Tablet, Mobile, etc.) 2. Application layer (server) 3. Database Server ▪ Example: Any large website on the internet Lecturer - Dilhani Abeykoon 39 3-Tier Architecture Lecturer - Dilhani Abeykoon 40 20  5/19/2024 RDBMS 41 Types of Databases Lecturer - Dilhani Abeykoon 42 21  5/19/2024 What is RDBMS ▪ RDBMS stands for Relational Database Management Systems. ▪ All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS. 43 Lecturer - Dilhani Abeykoon How it works ▪ Data is represented in terms of tuples (rows) in RDBMS ▪ Relational database is most commonly used database. It contains number of tables and each table has its own primary key ▪ Due to a collection of organized set of tables, data can be accessed easily in RDBMS Lecturer - Dilhani Abeykoon 44 22  5/19/2024 What is table? ▪ The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data. ▪ A table is the simplest example of data storage in RDBMS Lecturer - Dilhani Abeykoon 45 What is field? What is row or record? What is column? Lecturer - Dilhani Abeykoon 46 23  5/19/2024 Cont. Lecturer - Dilhani Abeykoon 47 Cont. Lecturer - Dilhani Abeykoon 48 24  5/19/2024 Cont. Lecturer - Dilhani Abeykoon 49 What is primary key? ▪ A primary key constrain is a column or group of columns that uniquely identifies every row in the table of the relational database management system. It cannot be a duplicate, meaning the same value should not appear more than once in the table. ▪ A table can have more than one primary key. Lecturer - Dilhani Abeykoon 50 25  5/19/2024 Why use Primary Key? ▪ The main aim of the primary key is to identify each and every record in the database table ▪ You can use a primary key when you do not allow someone to enter null values Lecturer - Dilhani Abeykoon 51 Example: Primary Key Lecturer - Dilhani Abeykoon 52 26  5/19/2024 What is Foreign Key? ▪ A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key. A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. Lecturer - Dilhani Abeykoon 53 Why use Foreign Key? ▪ Foreign keys help you to migrate entities using a primary key from the parent table. ▪ A foreign key enables you to link two or more tables together. Lecturer - Dilhani Abeykoon 54 27  5/19/2024 Example: Foreign Key 55 Lecturer - Dilhani Abeykoon Cont. Lecturer - Dilhani Abeykoon 56 28  5/19/2024 Cont. Lecturer - Dilhani Abeykoon 57 Cont. 58 Lecturer - Dilhani Abeykoon 29  5/19/2024 Insert Operation Update Operation Lecturer - Dilhani Abeykoon 59 Delete Operation Select Operation Lecturer - Dilhani Abeykoon 60 30  5/19/2024 Examples Lecturer - Dilhani Abeykoon 61 Exercise 01 Nimal has developed a database about his Internet Cafe. He has proposed following two tables in the database. Customer –store customer details Customer(Customer_Id, NIC_No, Name, Address, Gender, Telephone) Usage-store the details of day to day internet access by each customer Usage(Usage_Id, Customer_Id, Usage_Date, Start_Time, Duration, Charges, Computer_No) Which field would be most suitable as primary keys in each table. CUSTOMER – USAGE – Why is this field suitable? Lecturer - Dilhani Abeykoon 62 31  5/19/2024 Exercise 02 63 Lecturer - Dilhani Abeykoon Cont. ✓ Identify Primary key for each table ✓ Build relationship among tables ✓ Explain, how to get following details for given JourneyID as “6252” - Customer Name, Telephone No, Vehicle No and Type of the Vehicle Lecturer - Dilhani Abeykoon 64 32  5/19/2024 Exercise 03 I. You have to maintain a database of information about employees. II. Each employee is assigned to a department and identified by a unique employee number. III. Departments are identified by the department number and the name. IV. The employee number and other details should be stored in the database. ▪ Propose suitable tables for above database with their attributes ▪ Identify suitable primary keys and relationships of tables Lecturer - Dilhani Abeykoon 65 Lecturer - Dilhani Abeykoon 66 33