Unit-1 Database System Concepts PDF

Unit-1 Database System Concepts Presented By Mrs. Neha Vachani Lecturer (Computer Engg) Contents ▶ Introduction to Database ▶ Data, Database, Database Management Systems ▶ Advantages of DBMS over file processing system ▶ Applications of DBMS ▶ Da...

Unit-1 Database System Concepts Presented By Mrs. Neha Vachani Lecturer (Computer Engg) Contents ▶ Introduction to Database ▶ Data, Database, Database Management Systems ▶ Advantages of DBMS over file processing system ▶ Applications of DBMS ▶ Database Terms ▶ Data independence ▶ Components of DBMS ▶ Overall structure of DBMS ▶ Database Users ▶ Data Modeling: Relational , Hierarchical ,Network Model Learning Objectives By the end of the session Students will be able to: ▶ Define and Explain Basic concepts of DBMS ▶ Describe File Processing System ▶ Differentiate between File Processing System and DBMS ▶ Identify the applications of DBMS ▶ Describe the concepts of data abstraction and data independence ▶ Describe architectures of DBMS ▶ Explain different components of DBMS ▶ Describe different Models of DBMS  Data and Information ▶ Data is a set of values collected through observations. It is a set of raw, unprocessed, unorganized random facts which do not have much significance ▶ Information is a processed data which is organized and carries certain meaning ▶ When data is processed, analyzed and interpreted by some data management tool, it becomes information Database and Database Management System ▶ Database: Set of inter related data is called database.It is a repository of logically related and similar data ▶ An organized collection of related information so that it can easily be accessed, managed and updated ▶ Eg. Airline Database ,Student Database , Railways Timetable etc ▶ DBMS : DBMS stands for Database Management System ▶ DBMS is a software system for creating, organizing and managing the database ▶ It provides an environment to the user to perform operations on the database for creation, insertion, deletion, updating and retrieval of data. ▶ Eg. SQL sever, Mysql , Oracle. File Processing System ▶ File system is basically a way of arranging the files in a storage medium like hard disk ▶ File systems consists of different files which are grouped into directories ▶ The directories further contain other folders and files ▶ File system performs basic operations like management, file naming, giving access rules etc Drawbacks of using file systems ▶ Data redundancy and inconsistency Multiple file formats, duplication of information in different files ▶ Difficulty in accessing data Need to write a new program to carry out each new task ▶ Data isolation multiple files and formats ▶ Integrity problems Difficult to add new constraints or change existing ones Advantages of DBMS ▶ Very less or No data redundancy ▶ Efficient data access ▶ Data integrity ▶ Data security ▶ Data administration ▶ Concurrent access and crash recovery Applications of Database Management System(DBMS) ▶ Banking: For Transactions ▶ Airlines/Railways: Reservations, schedules ▶ Universities/Colleges: Registration, Results ▶ Sales: Customers, Products, Purchases ▶ Online retailers: Order placing and tracking ▶ Manufacturing: Production, Inventory, Orders ▶ Hospital: Doctors, Patients, Billing, Staff etc  Data Abstraction ▶Data Abstraction is a process of hiding unwanted or irrelevant details from the end user. It provides a different view and helps in achieving data independence which is used to enhance the security of data. The database systems consist of complicated data structures and relations. For users to access the data easily, these complications are kept hidden, and only the relevant part of the database is made accessible to the users through data abstraction. Three Levels of Abstraction: ▶ 1. Physical or Internal Level It is the lowest level of abstraction for DBMS which defines how the data is actually stored, it defines data-structures to store data and access methods used by the database. Actually, it is decided by developers or database application programmers how to store the data in the database. So, overall, the entire database is described in this level that is physical or internal level. It is a very complex level to understand. For example, customer's information is stored in tables and data is stored in the form of blocks of storage such as bytes, gigabytes etc.  Data Abstraction 2. Logical or Conceptual Level: Logical level is the intermediate level or next higher level. It describes what data is stored in the database and what relationship exists among those data. It tries to describe the entire or whole data because it describes what tables to be created and what are the links among those tables that are created. It is less complex than the physical level. Logical level is used by developers or database administrators (DBA). So, overall, the logical level contains tables (fields and attributes) and relationships among table attributes. 3. View or External Level: It is the highest level. In view level, there are different levels of views and every view only defines a part of the entire data. It also simplifies interaction with the user and it provides many views or multiple views of the same database. View level can be used by all users (all levels' users). This level is the least complex and easy to understand. For example, a user can interact with a system using GUI that is view level and can enter details at GUI or screen and the user does not know how data is stored and what data is stored, this detail is hidden from the user Data Independence ▶ Data independence can be defined as property of dbms to modify the schema at one level of the database system without altering the schema at the next higher level. Two types of Data Independence ▶ Logical data independence : refers to property of dbms to change the conceptual schema without having to change the external schema. Logical data independence occurs at the user interface level ▶ Physical data independence : refers to property of dbms to change the internal schema without having to change the logical (conceptual) schema. Physical data independence occurs at the logical interface level. Database Terms ▶ Data Dictionary : A data dictionary contains metadata i.e data about the database. The data dictionary is very important as it contains information such as what is in the database, who is allowed to access it, where is the database physically stored etc ▶ Instance :The data which is stored in the database at a particular moment of time is called an instance of the database. ▶ Schema : The overall design of a database is called schema A database schema is the skeleton structure of the database. It represents the logical view of the entire database. Components of Database System ▶ 1. Users: People who interact with the database Application Programmers End users Data Administrator ▶ 2. Software: Lies between the stored data and the users DBMS Application Software User Interface ▶ 3. Hardware: Physical device on which database resides Computers , Hard Drives, Cables, etc ▶ 4. Data: Numbers, characters, pictures. Functions of DBA(Database Administrator) ▶ DBA is a person in the organization who controls the design and the use of the database ▶ Functions of DBA ▶ 1. Schema Definition ▶ 2. Storage Structure and Access Method Definition ▶ 3. Assisting Application Programmers ▶ 4. Physical Organization Modification ▶ 5. Approving Data Access: ▶ 6. Monitoring Performance: ▶ 7. Backup and Recovery  DBMS Architecture ▶ The design of a DBMS depends on its architecture.The simplest Database Architecture is ▶ 1 tier Architecture : where the Client, Server, and Database all reside on the same machine. ▶ Anytime you install a DB in your system and access it to practice SQL queries it is 1 tier architecture. But such architecture is rarely used in production.  DBMS Architecture ▶ Two-tier architecture : A two-tier architecture is a database architecture where ▶ Presentation layer runs on a client (PC, Mobile, Tablet, etc) ▶ Data is stored on a Server ▶ An application interface API which allows the client-side program to call the DBMS. ▶ 2 tier architecture provides added security to the DBMS as it is not exposed to the end user directly.  DBMS Architecture ▶ 3-tier Architecture: It is an extension of the 2-tier architecture. 3-tier architecture has following layers 1. Presentation layer (your PC, Tablet, Mobile, etc.) 2. Application layer (server) 3. Database Server ▶ This DBMS architecture contains an Application layer between the user and the DBMS, which is responsible for communicating the user's request to the DBMS system and send the response from the DBMS to the user ▶ Three tier architecture is the most popular DBMS architecture. Overall Structure of DBMS  Overall Structure of DBMS ▶ The database system is divided into three components: ▶ Query Processor ▶ Storage Manager ▶ Disk Storage. These are explained as follows ▶ 1. Query Processor : It interprets the requests (queries) received from end user via an application program into instructions. It also executes the user request which is received from the DML compiler. Query Processor contains the following components – DML Compiler – It processes the DML statements into low level instruction (machine language), so that they can be executed DDL Interpreter – It processes the DDL statements into a set of table containing meta data Embedded DML Pre-compiler – It processes DML statements embedded in an application program into procedural calls Query Optimizer – It optimize and executes the instruction generated by DML Compiler.  Overall Structure of DBMS ▶ 2. Storage Manager : Storage Manager is a program that provides an interface between the data stored in the database and the queries received. It is also known as Database Control System. It maintains the consistency and integrity of the database by applying the constraints and executes the DCL statements. It is responsible for updating, storing, deleting, and retrieving data in the database. It contains the following components – ▶ Authorization Manager – It ensures role-based access control, i.e,. checks whether the particular person is privileged to perform the requested operation or not. ▶ Integrity Manager – It checks the integrity constraints when the database is modified.  Overall Structure of DBMS ▶ Transaction Manager – It controls concurrent access by performing the operations in a scheduled way. Thus, it ensures that the database remains in the consistent state before and after the execution of a transaction. ▶ File Manager – It manages the file space and the data structure used to represent information in the database. ▶ Buffer Manager – It is responsible for cache memory and the transfer of data between the secondary storage and main memory Overall Structure of DBMS ▶ 3. Disk Storage : It contains the following components ▶ Data Files – It stores the data. ▶ Data Dictionary – It contains the information about the structure of any database object. It is the repository of information that governs the metadata. ▶ Indices – It provides faster retrieval of data item.  Data Models ▶ A Database model defines the logical design and structure of a database and defines how data will be stored, accessed and updated in a database management system. ▶ Hierarchical Model ▶ Network Model ▶ Relational Model ▶ Object Oriented Model  Hierarchical Model ▶ This database model organises data into a tree-like-structure, with a single root, to which all the other data is linked. The hierarchy starts from the Root data, and expands like a tree, adding child nodes to the parent nodes. ▶ In this model, a child node will only have a single parent node. ▶ This model efficiently describes many real-world relationships like index of a book, recipes etc. ▶ In hierarchical model, data is organised into tree-like structure with one one-to-many relationship between two different types of data  Network Model ▶ It is an extension of hierarchical model in which data is represented in graph-like structure ▶ A child record may have more than one parent ▶ Data is organized in records which are connected using links ▶ Each record is collection of fields (attributes)  Relational Model ▶ Simplest and widely used data Model ▶ Data is represented in the form of relations or tables ▶ Relational Model was proposed by E.F.Codd in 1970 ▶ Table/relation is a set of rows and columns Key concepts of Relational model ▶ Attribute: Each relation is defined in terms of some properties each of which is known as attribute ▶ Domain of an attribute: The possible values an attribute can take in a relation is called its domain. ▶ Tuple: Each row of a relation is known as tuple ▶ NULL values: Values of some attribute for some tuple may be unknown, missing or undefined which are represented by NULL ▶ Degree : The number of attributes of the relation is known as degree of relation References : Database System concepts by Abraham Silberschtz, Henry Korth& S. Sudarshan, Tata McGraw Hill International https://www.tutorialspoint.com