FDBMS unit 1_NOTES.pdf

Database Management System is a software or technology used to manage data from adatabase. Some popular databases are MySQL, Oracle, MongoDB, etc. DBMS provides many operations e.g. creating a database, Storing in the database,updating an existing database,...

Database Management System is a software or technology used to manage data from adatabase. Some popular databases are MySQL, Oracle, MongoDB, etc. DBMS provides many operations e.g. creating a database, Storing in the database,updating an existing database, delete from the database. DBMS s a system that enables you to store, modify andretrieve data in an organized way. It also provides security to the database. In this Database Management System tutorial you'll learn basic to advanced topics like ER model, Relational Model, Relation Algebra, Normalization, File Organization, etc. 20:06.. l.a1903 Key Features of DBMS Data modeling: A DBMS provides tools for creating and modifying data models, which define the structure and relationships of the data in a database. Data storage and retrieval: A DBMS is responsible for storing and retrieving data from the database, and can provide various methods for searching and querying the data. Concurrency control: A DBMS provides mechanisms for controlling concurrent access to the database, to ensure that multiple users can access the data without conflicting with each other. Data integrity and security: A DBMS provides tools for enforcing data integrityand security constraints, such as constraints on the values of data and access controls that restrict who can access the data. Backup and recovery: A DBMS provides mechanisms for backing up and recovering the data in the event of a system failure. DBMS can be claccified into two types: A RelationaDataba Management System (RD Open In AppRelational  20:06.. ls1 90) a)Aptitude Engineering Mathematics Discrete Ma Data integrity and security: A DBMS provides tools for enforcing data integrityand security constraints, such as constraints on the values of data and access controls that restrict who can access the data. Backup and recovery: A DBMS provides mechanisms for backing up and recovering the data in the event of a system failure. DBMS can be classified into two types: Relational Database Management System (RDBMS)and Non-Relational Database Management System (NoSQL or Non-sQL) RDBMS: Data is organized in the form of tables and each table has a set of rows and columns. The data are related to each otherthrough primary and foreign keys. NoSQL: Data is organized in the form of key-value pairs, documents, graphs, or column-based. These are designed to handle large-scale, high performance scenarios. A database Open In Appterrelated View of Data Database systems are made-up of complex data structures. To ease the user interaction with database, the developers hide internal irrelevant details from users and provides abstract view of data to users. This process of hiding irrelevant details from User is called Data Abstraction. Abstraction is one of the main features of database systems. Levels of abstraction Physical level (Internal level): 0 This is the lowest level of data abstraction. Itdescribes how data is actually stored in database. You can get the complex data structure details at this level. Logical level (Conceptual level): 0 This is the middle level of 3-level data abstraction architecture. 0 It describes what data is stored in database and what relationships exist among those data. View level (External level): 0 Highest level of data abstraction. 0 Thislevel describes only part of the entire database ie. it describes the user interaction with database system via application programs that hide details of data types. Three Levels of data abstraction At view level, user just interact with system with the help of GUl and enter the details at the screen, they are view level not aware of how the data is stored and what data is stored; such details are hidden from them. view 1 view 2 view a At the logical level these records can be described as fields and attributes along with their data types, their relationship among each other can be logically implemented. lagical level The programmers generally work at this level because they are aware of such things about database systems. physical At physical level these records can be described as blocks level of storage (bytes,gigabytes, terabytes etc.) in memory. These details are often hidden from the programmers.  Customer Manager External View1 View2 View AC Name AC No Amount AC Name Type Logical / / AC_No Numeric(15) Conceptu AC_Name Character(20) al View Amount Numeric(15) Type Character(10) Physical/ Stored-acc length =60 Internal Account # type-bytes(15) View Type offset(10) Schemas and Instances e Schemas and Instance are similar to types and variables in programming languages Schema:Overall design of a database is called the Schema For example:Given a schema shows therelationship between three tables: Course, Student and Section. The diagram only shows the design of the database, it doesn't show the data present in those tables. Schema is only a structural view (design) of a database as shown in the diagram below. Course Student CouseJd Studentd Coursenome Studentnome Deportment CourseJd Schema Section Studentd Sectiond CourseJd Schemas types... Schema is of three types: 0 Physical schema: t describesdatabase design at physical level i.e. physical structure of database how the data stored in blocks ofstorage is described at this level. 0 logical schema: It describes database design at logical level i.e. logical structure of database Programmers and Database Administrators work at this level, at this level data can be described as certain typesof data records gets stored in data structures, however the internaldetails such asimplementation of data structure is hidden at this level (available at physical level). 0 Sub-schema (or view schema): It describes different views of database This generally describes end user interaction with database systems. Data Models in DBMS Data Model gives us an idea that how the finalsystem will look like after its complete implementation. A Data Model in DBMS, is the conceptof tools that are developed to summarize the description of the database. It defines how the logical structure of a database is modeled Data Model A is collection of conceptual tools for describing: Data Data relationships Data semantics and Consistency constraints It describes the design of a database at each level of data abstraction. It defines how data is connected to each other and how they are processed & stored inside the system 50. G Types of Data Models Data Models Older models: 2. 3. 4. - Hierarchical Relational Entity Object-based Semi-structured Model Data Model Relationship (ER) Data Model Data Model Data Model - Network Model (Wefocus in this course) (Mainly for database design) (Object-oriented and (XML) Object-relational)  Relational Model Most widely used model by commercial data processing applications It uses collection of tables for representing data and the relationships among those data Data is stored in tables called Relations Each table is a group of column and rows, where column represets attribute of an entity and rows represents records (or tuples),. D Attribute or field: Each solumn in a relation is called an attribute. The values of the attribute should be from the samë domain. Example: we have different attributes of the student like Student_ld, Student_Name, Student_Age, etc. o Tuple or Record Each the relation called tuple. A tuple defines row in a collection of attribute values. So each row in a relation contains unique values. Example: each row has allthe information aboutany specific individual like the first row has information aboutstudentAshish. This model was initially described by Edgar F. Codd, in 1969. 2. Entity-Relationship (ER) Model ER Model is a high-level data model diagram ER model describes the structureof a database with the help of a diagram, which is known as Entity Relationship Diagram (ER Diagram). An ER model is a design or blueprint of a database that can later be implemented as adatabase. It is based on the notion of real-world entities and relationships among them ER diagram has the following three components: a Entities: Entity is a real-world thing or object. It can be a person, place, or even a concept. Example: Teachers, Students, Course, Building, Department, etc are some of the entities of a School Management System. D Attributes: An entity contains a real-world property called attribute. This is the characteristics of that attribute. Example: The entity teacherhas the property ike teacher id, name,salary, age,etc. Relationship: Relationship tells how two attributes are related. Example: Teacher works for a department.  Figures Symbols Represents Entities in Rectangle ER Model Attributes in Ellipse ER Model Diamond Relationships among Entities Attributes to Entities and Line Entity Sets with Other RelationshipTypes Multi-Valued Double Ellipse Attributes Double Rectangle Weak Entity  Dob Doj D No (Name (Address Works_in DEPARTMENT Location) Name EMPLOYEE Since ld Manager Has Gender P No Works on Hours Name PROJECT (Location) kDependent o DEPENDENT (Relationship ER DIAGRAM OF A COMPANY (D_name) Gender o Here the hardware means the physical part of the DBMS. Here the hardware includes output devices like a printer, monitor, etc., and storage devices like a hard disk. o Software is the main component of the DBMS. Software is defined as the collection of programs that are used to instruct the computer about its work. The software consists of a set of procedures, programs, and routines associated with the computer system's operation and performance. Also, we can say that computer software is a set of instructions that is used to instruct the computer hardware for the operation of the computers. o The term data means the collection of any raw fact stored in the database. Here the data are any type of raw material from which meaningful information is generated. o The procedure is a type of general instruction or guidelines for the the use of DBMS. This instruction includes how to set up the database, how to install the database, how to log in and log out of the database, how to manage the database, how to take backup of the database, and how to generate the report of the database. 5.Database Access Language Database Access Language is a simple language that allows Users to write Commands to perform the desired operations on the data that is stored in the database. 2. Data Manipulation Language(DML): It is used to accesS a database. The DML provides the statements to retrieve, modify, insert and delete the data from the database. The following commands serve as the base for all DML commands: o INSERT UPDATE DELETE o LOCK o CALL o EXPLAIN PLAN 6. People The people who control and manage the databases and perform different types of operations on the database in the DBMS. o The people include database administrator, software developer, and End-user. DBA( Daab ase Adml ris trator) GA DBA is an indiidual peson or a gus t þemons with an one oY more dakabases So hat he/She Can Conbel ond the he and use bese databases, duign duk Funciens 4 an DBA db 0 Dgjiming (onceftual Schema. sauthenticoation auttozalion in Backuy and Selet ) Uses Access  ADatabase Administrator (DBA)is ar individual or person responsible for controlling, maintaining, coordinating, and operating a database management system. Managing, securing, and taking care of the database systems is a prime responsibility. They are responsible and in charge of authorizing access to the database, coordinating, capacity, planning, installation, and monitoring uses,and acquiring and gathering software and hardware resources as and when needed. Their role also varies from configuration, database design, migration, security, troubleshooting, backup,and data recovery. Database administration is a major and key function in any firm or organization that is relying on one or more databases. They are overall commanders of the Database system. Advantages of DBMS There are some following reasons to learn DBMS: Organizing and management of data: DBMS helps in managing large amounts of data in an organized manner. It provides features like create, edit, delete, and read. Data Security: DBMS provides Security tothe data from the unauthorized person. Improved decision-making: From stored data in the database we can generate graphs, reports, and many visualizations which helps in decision making. Consistency: In a traditional database model allthings are manual or inconsistent, but DBMS enables to automation of the operations by queries. DIsaavantages oT UBIVIS Complexity: DBMS can be hard to design, implement, and manage, needing specialized knowledge. Cost: High setup costs, including hardware, software, and skilled personnel, can be expensive. Ongoing maintenance adds to the cost. Performance Overhead: DBMS might slow down simple tasks due to their extra features and general-purpose nature. Security Risks: Centralizing data can create security risks. If the system is hacked, all data couldbe compromised. Resource Intensive: DBMS need a lot of memory, storage, and processing power, which can be costly. Data Integrity lssues: Complex systems can lead to data integrity problems if not managed well.