DBMS CH1 PDF

Database Management Systems (DBMS) GTU # 3130703 Unit-1 Database System Architecture  Looping Outline Introduction of DBMS Applications of DBMS Advantages of DBMS Three levels ANSI SPARC database system Data Ab...

Database Management Systems (DBMS) GTU # 3130703 Unit-1 Database System Architecture  Looping Outline Introduction of DBMS Applications of DBMS Advantages of DBMS Three levels ANSI SPARC database system Data Abstraction in DBMS Mappings and data independence Database users and DBA Database system architecture Introduction to DBMS Section - 1 What is Database Management System (DBMS)?  Data - Fact that can be recorded or stored  e.g. Person Name, Age, Gender and Weight etc.  Database - Collection of logically related data  e.g. Books Database in Library, Student Database in University etc.  Management - Manipulation, Searching and Security of data  e.g. Viewing result in GTU website, Searching exam papers in GTU website etc.  System - Programs or tools used to manage database  e.g. SQL Server Studio Express, Oracle etc.  DBMS - A Database Management System is a software for creating and managing databases.  Database Management System (DBMS) is a software designed to define, manipulate, retrieve and manage data in a database.  e.g. MS SQL Server, Oracle, My SQL, SQLite, MongoDB etc. Applications of DBMS Section - 2 Applications of DBMS  DBMS is a computerized record-keeping system.  DBMS is required where ever data need to be stored.  E-Commerce (Flikart, Amazon, Shopclues, eBay etc...)  Online Television Streaming (Hotstar, Amazon Prime etc...)  Social Media (WhatsApp, Facebook, Twitter, LinkedIn etc...)  Banking & Insurance  Airline & Railway  Universities and Colleges/Schools  Library Management System  Human Resource Department  Hospitals and Medical Stores  Government Organizations Exerci Write down any five applications of DBMS se other than above. Advantages of DBMS Section - 3 Reduce data redundancy (duplication) Computer Civil Addres Mobil Subje Addres Mobil Subje Emp_Name Emp_Name s e ct s e ct Prof. Ajay Prof. Ajay Rajkot 1234 PPS Rajkot 1234 PPS Shah Shah Database management Same data is stored system can remove such at data redundancy by four different storing data centrally. places. Addres Mobil Subje Addres Mobil Subje Emp_Name Emp_Name s e ct s e ct Prof. Ajay Prof. Ajay Rajkot 1234 PPS Rajkot 1234 PPS Shah Shah Electrical Mechanical Remove data inconsistency Computer Civil Addres Mobil Subje Addres Mobil Subje Emp_Name Emp_Name s e ct s e ct Prof. Ajay 6789 Prof. Ajay Rajkot 1234 PPS Rajkot 1234 PPS Shah Shah Same data having Database different state management system (values) can keep data in consistent state. Mobile no is changed Addres Mobil Subje Addres Mobil Subje Emp_Name Emp_Name s e ct s e ct Prof. Ajay 6789 Prof. Ajay Rajkot 1234 PPS Rajkot 1234 PPS Shah Shah Electrical Mechanical Data isolation  Data are scattered in various files. File -  Files may be in different formats. 1 Addres Mobil Subje  Difficult to retrieve the appropriate data.Emp_Name Prof. Ajay s e ct Rajkot 1234 PPS Shah DBMS allow us to access (retrieve) File - appropriate data easily. 2 Emp_Name Post Salary Load Data isolation is a property that determines Prof. Ajay Lectur 50,00 15 Shah er 0 when and how changes made by one operation become visible to other concurrent File - users and systems. 3 Teachin Knowled Rati This issue occurs in a concurrency situation. Emp_Name g ge ng Prof. Ajay Excellen Good 9 Shah t Guaranteed atomicity  Atomicity: Either transaction execute 0% or 100%. Sum of both account before transfer is 3000 Person Person A Transfer 500 B Account Account A Step 1 : Debit 500 from B Bal : Account A Bal : Sum of both Transactio 2000 Step 2Sum of both : Credit 500 into 1000 account is n is failed account 2500 Account B after transfer is so 3000 inconsistent Allow to implement integrity constraints Addres Subje Emp_Name Mobile_No s ct Prof. Ajay 98765432 Rajkot Should contain PPS exact 10 Shah 10 digits Student_Na Branch Backlog SPI me Nirav Patel Rajkot 0 8.5 Should be between 0 to 10 DBMS allows us to implement such business rules in our database.. Sharing of data among multiple users Want to access Computer Civil Addres Mobil Subje Addres Mobil Subje Emp_Name Emp_Name s e ct s e ct Prof. Ajay Prof. Ajay Rajkot 1234 PPS Rajkot 1234 PPS Shah Shah Want to Database management access system allows more than one user to access same data simultaneously. Addres Mobil Subje Addres Mobil Subje Emp_Name Emp_Name s e ct s e ct Prof. Ajay Prof. Ajay Rajkot 1234 PPS Rajkot 1234 PPS Shah Shah Electrical Mechanical Restricting unauthorized access to data File - 1 Addres Mobil Subje Emp_Name s e ct Prof. Ajay Rajkot 1234 PPS Shah Wants to Faculty File - access of 2 Emp_Name Post Salary Load other college Prof. Ajay Lectur 50,00 15 Wants Shah er 0 to Darsha File - access n 3 Teachin Knowled Rati Faculty Emp_Name g ge ng Prof. Ajay Excellen Good 9 Shah t DBMS prevents unauthorized user to access data. Providing backup and recovery services Provides facilities to backup and restore the database in case of failure. Advantages of DBMS (Summary)  Reduce data redundancy (duplication)  Avoids unnecessary duplication of data by storing data centrally.  Remove data inconsistency  By eliminating redundancy, data inconsistency can be removed.  Data isolation  A user can easily retrieve proper data as per his/her requirement.  Guaranteed atomicity  Either transaction executes 0% or 100%. Advantages of DBMS (Summary)  Allow implementing integrity constraints  Business rules can be implemented such as do not allow to store amount less than Rs. 0 in balance.  Sharing of data among multiple users  More than one users can access same data at the same time.  Restricting unauthorized access to data  A user can only access data which is authorized to him/her.  Providing backup and recovery services  Can take a regular auto or manual backup and use it to restore the database if it corrupts. Basic Terms Section - 4 Basic terms  Data  Data is raw, unorganized facts that need to be processed.  Example: Marks of students  Student_1 = 50/100, Student_2 = 25/100.  Information  When data is processed, organized, structured or presented in a given context so as to make it useful, it is called information.  Example: Result of students (Pass or Fail)  Student_1 = Pass, Student_2 = Fail. Basic terms (cont…)  Metadata  Metadata is data about data.  Data such as table name, column name, data type, authorized user and user access privileges for any table is called metadata for that table. Facul ty Addres Subje Emp_Name Mobile_No s ct Prof. Ajay 98765432 Rajkot PPS Shah 10  Metadata of above table is:  Table name such as Faculty  Column name such as Emp_Name, Address, Mobile_No, Subject  Datatype such as Varchar, Decimal  Access privileges such as Read, Write (Update) Basic terms (cont…)  Data dictionary  A data dictionary is an information repository which contains metadata. Table Name – Faculty Column Name – EmpName, Address, Mob, Subject, Salary Datatype – Varchar, Decimal Access Privileges – Read, Write (Update)  Data warehouse  A data warehouse is an information repository which stores data. Facul ty Addres Subje Emp_Name Mobile_No s ct Prof. Ajay 98765432 Rajkot PPS Shah 10 Prof. Ajay 01234567 Surat DBMS Patel 89 Exerci Why data dictionary and data warehouse are stored in the se different places? Basic terms (cont…)  Field  A field is a character or group of characters that have a specific meaning.  E.g, the value of Emp_Name, Address, Mobile_No etc are all fields of Faculty table. Facul ty Addres Subje Emp_Name Mobile_No Fields s ct Prof. Ajay 98765432 Prof. Ajay Rajkot 9876543 Rajkot PPS Shah 10 Shah 210 Prof. Ajay  Record 01234567 / Tuple Patel Surat 89 DBMS  A record is a collection of logically related fields.  E.g, the collection of fields (Emp_Name, Address, Mobile_No, Subject) forms a record for the Faculty. Prof. Ajay 98765432 Record / Rajkot PPS Shah 10 Tuple Prof. Ajay 01234567 Surat DBMS Patel 89 3 Levels ANSI SPARC Database System Section - 5 3 Levels ANSI SPARC Database System User User User 1 2 3 How data are View View 1 View 2 View 3 viewed by each Level users? Conceptua What data are stored Logic l and al Level What relationships Level exist? How the data are Internal Physi actually stored on Level cal storage devices? Level Database 3 Levels ANSI SPARC Database System  Internal level (Physical level)  It describes how a data is stored on the storage device.  Deals with physical storage of data.  Structure of records on disk - files, pages, blocks and indexes and ordering of records  Internal view is described by the internal schema.  Conceptual level (Logical level)  What data are stored and what relationships exist among those data?  It hides low level complexities of physical storage.  For Example, STUDENT database may contain STUDENT and COURSE tables which will be visible to users but users are unaware about their storage.  Database administrator works at this level to determine what data to keep in the database. 3 Levels ANSI SPARC Database System  External level (View level)  It describes only part of the entire database that an end user concern or how data are viewed by each user.  Different user needs different views of the database, so there can be many views in a view level abstraction of the database. Used by end users and application programmers.  End users need to access only part of the database rather than the entire database. 3 Levels ANSI SPARC Database System: Example We are storing student information in a student table. User User User User just interact with system with the help of 1 2 3 GUI. View View 1 View 2 View 3 Users are not aware of how and what the data Level is stored. Records can be described as fields and attributes along with their data types, their Conceptua relationship among each other can be l Logic logically implemented. Level al Programmers generally work at this level. Level Records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in Internal Physi memory. Level cal These details are often hidden from the Level programmers. Database Data Abstraction in DBMS  Database systems are made-up of complex data structures.  To ease the user interaction with database, the developers hide internal irrelevant details from users.  This process of hiding irrelevant details from user is called data abstraction. Mapping and Data Independence Want to access some data User User User 1 2 3 View View 1 View 2 View 3 Level Request Process of transforming requests and results Conceptua Logic between the three levels is called mapping. l al Level Level Ability to modify a schema definition in one level without affecting a schema definition in Internal Physi the next higher level. Level cal Level Result Database Types of Data Independence  Physical Data Independence  Physical Data Independence is the ability to modify the physical schema without requiring any change in logical (conceptual) schema and application programs.  Modifications at the internal levels are occasionally necessary to improve performance.  Possible modifications at internal levels are changes in file structures, compression techniques, hashing algorithms, storage devices, etc.  Logical Data Independence  Logical data independence is the ability to modify the conceptual schema without requiring any change in application programs.  Modification at the logical levels is necessary whenever the logical structure of the database is changed.  Application programs are heavily dependent on logical structures of the data they access. So any change in logical structure also requires programs to change. Types of Database Users Section - 6 Types of Database Users  Naive Users (End Users)  Unsophisticated users who have zero knowledge of database system  End user interacts to database via sophisticated software or tools  e.g. Clerk in bank  Application Programmers  Programmers who write software using tools such as Java,.Net, PHP etc…  e.g. Software developers  Sophisticated Users  Interact with database system without using an application program  Use query tools like SQL  e.g. Analyst  Specialized Users (DBA)  User write specialized database applications program  Use administration tools  e.g. Database Administrator Role of DBA (Database Administrator) Section - 7 Role of DBA  Schema Definition  DBA defines the logical schema of the database.  Storage Structure and Access Method Definition  DBA decides how the data is to be represented in the database & how to access it.  Defining Security and Integrity Constraints  DBA decides on various security and integrity constraints.  Granting of Authorization for Data Access  DBA determines which user needs access to which part of the database.  Liaison with Users  DBA provide necessary data to the user. Role of DBA  Assisting Application Programmer  DBA provides assistance to application programmers to develop application programs.  Monitoring Performance  DBA ensures that better performance is maintained by making a change in the physical or logical schema if required.  Backup and Recovery  DBA backing up the database on some storage devices such as DVD, CD or magnetic tape or remote servers and recover the system in case of failures, such as flood or virus attack from this backup. Database System Architecture Section - 8 Database System Architecture Application Sophisticate Database Naive programme d administrato user writ r user r uses uses uses Applicati e Applicati on Query Administrati on interface tool on tool program s Translates Interprets Compiler DDL DML DDL DML statements and interpret Executes Deals low statements with queries into low level linker DML er intolevel a set of execution of Application instructions compiler instructions DDL tables andquery DML program that the Query and generated containing statements by object code evaluation evaluation organizer Query DML compiler. metadata engine engine processor understands File Authorization Transactio Buffer manage and integrity n manager r manager manager Storage Manages Fetches data allocation of manager Provides Preserves Checks the from disk storage space on disk interface atomicity and authority of to memory storage for Indice Data between low- To provide being usedfaster userscontrols to access s dictionary Disk storage level data stored concurrency data and access to data Dat Statistical To To store statistical and application store integrity To storeitems user data a data information about the program or metadata constraints data queries Questions asked 1. List and explain the advantages of DBMS over file based system. OR Explain disadvantages of files based system. 2. Draw and explain 3 level architecture of DBMS. 3. List and explain different categories/types of database users. 4. List and explain different tasks/roles/functions/duties of DBA (Database Administrator). 5. Explain DBMS architecture with block diagram. OR Explain Database System architecture with block diagram. Data Models What is a Database Models?  A database model is a type of data model that defines the logical structure of a database.  It determine how data can be stored, accessed and updated in a database management system.  The most popular example of a database model is the relational model, which uses a table-based format. Type of Database Models Hierarchical Model Network Model Entity-relationship Model Relational Model Object-oriented database Model Hierarchical Model  The hierarchical model organizes data into a tree-like structure, where each record has a single parent or root. Departme nt Student Professor  The hierarchy starts from the Root data, and expands like a tree, adding child nodes to the parent nodes.  In hierarchical model, data is organized into tree-like structure with one-to-many relationship between two different types of data, for example, one department can have many professors and many students. Network Model  This is an extension of the hierarchical model, allowing many-to- many relationships in a tree-like structure that allows multiple parents. A B C D E F Entity-relationship Model  In this database model, relationships are created by dividing object of interest into entity and its characteristics into attributes. Attribut es RollNo Name Relationsh BookN Name o ip Student Issue Book Branch Sem Entities Author Price Relational Model  In this model, data is organized in two-dimensional tables and the relationship is maintained by storing a common attribute. Rn Student_Na Ag SubI Subject_Na Teache o me e D me r 10 1 DBMS Doshi Raj Patel 20 1 Foreign Foreign 2 DS Vyash 10 Key Meet Key Shah 21 2 ResI Rn SubI Mark D o D s 10 1 1 80 1 10 2 2 85 1 10 3 1 75 2 Object-oriented database Model  This data model is another method of representing real world objects.  It considers each object in the world as objects and isolates it from each other.  It groups its related functionalities together and allows inheriting its functionality to other related sub-groups. Questions asked [E-R diagrams] 1. Draw E-R diagram for Bank Management System. 2. Define E-R diagram. Draw an E-R diagram for Library Management System. Assume relevant entities and attributes for the given system. 3. Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents. 4. Design a generalization–specialization hierarchy for a motor-vehicle sales company. The company sells motorcycles, passenger cars, vans, and buses. Justify your placement of attributes at each level of the hierarchy. Explain why they should not be placed at a higher or lower level. Questions asked [E-R diagrams and Database] 1. Design a database for an airline. The database must keep track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routing of future flights. Your design should include an E-R diagram, a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints. 2. Design a database for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted. Your design should include an E-R diagram, a set of relational schemas, and a list of constraints, including primary- key and foreign-key constraints.