Database Management PDF

The purpose of Information management is to DATABASE MANAGEMENT provide the right information to the right person at the right time at th...

The purpose of Information management is to DATABASE MANAGEMENT provide the right information to the right person at the right time at the right place in In today’s world, data is abundant, global and the right format at the right price. everywhere and prevalent, persistent. From birth to death, we consume data. The trail of DATA MANAGEMENT data starts with the birth certificate and Data Management typically requires the use continues all the way to store enormous of a computer database. amounts of data. A database is a shared, integrated computer structure that stores the collection of the DATA following: Data is a representation of facts or concepts End user data is the raw facts of interest (raw facts), in a formalized manner suitable to the end user. for communication, interpretation, or Metadata, or data about data, through processing by human or by automatic means. which the end user data is integrated It refers to facts and figures. and managed. INFORMATION Example of Metadata for a Digital Photograph Information refers to the knowledge derived as This metadata provides valuable context and a result of processing the data. It is useful to information about the photograph, making it any individuals because it will serve as their easier to manage, organize, and understand basis on their decision for the future of the image's content and attributes. organizations they belong to. FILE SYSTEM MANAGEMENT In the past, File Systems were composed of a Management in information management collection of file folders with proper tags, kept refers to the organized and strategic handling in a filing cabinet, and organized based on of information and data within an their logical relationships. As file systems grew, organization. It involves the planning, data processing (DP) specialists emerged to controlling, organizing, and directing of convert from a manual to a computerized information resources to achieve specific system. It has been useful in data goals and objectives. management but has become obsolete. Drawbacks of File Systems: DATA MANAGEMENT Data redundancy and inconsistency Is a discipline that focuses on the Difficulty in accessing data proper generation, storage and Data Isolation retrieval of data. Integrity Problems Data Management is a core activity of Atomicity Problems many businesses, government agency, Concurrent-Access Anomalies service organization, or charity. Security Problems INFORMATION MANAGEMENT DATABASE SYSTEM Information management is the process by Database System is a collection of inter which relevant information is provided to related data and a set of programs that allow decision-makers in a timely manner users to access and modify these data. (Davis 1997). COMPONENTS OF DATABASE SYSTEM Disadvantages of DBMS 1. Data 1. Complexity 2. Hardware 2. Skilled resources 3. Software 3. Performance tuning a. Operating Systems 4. Database failure b. Application Programs and 5. Cost Utilities Software 6. Additional hardware cost c. DBMS 7. Frequent upgrades 4. Procedures 5. People a. Administrator DATA MODEL & ARCHITECTURE b. Designer DATA MODEL c. End user A data model-a collection of concepts that i. Casual can be used to describe the structure of a ii. Naïve database-provides the necessary means to iii. Sophisticated achieve this abstraction. iv. Stand-alone users Examples: Relational, Hierarchical, Networked, Object-Oriented, XML DATABASE MANAGEMENT SYSTEM (DBMS) A database management system (DBMS) is a CATEGORIES OF DATA MODEL collection of programs that manages the High-level or conceptual data models database structure and controls access to the -provide concepts that are close to the data stored in the database. way many users perceive data. Conceptual data models use concepts DBS FUNCTIONS such as entities, attributes, and 1. Data Dictionary Management relationships. An entity represents a 2. Data Storage Management real-world object or concept, such as 3. Data Transformation and Presentation an employee or a project that is 4. Security Management described in the database. 5. Multi-User Access Control low-level or physical data models 6. Backup And Recovery Management -provide concepts that describe the 7. Data Integrity Management details of how data is stored in the 8. Database Access Languages and computer. Concepts provided by low- Application Programming Interface level data models are generally meant 9. Database Communication Interfaces for computer specialists, not for typical end users. ADVANTAGES OF DBMS 1. Improved data sharing SCHEMAS AND INSTANCES 2. Improved data security The term "SCHEMA" refers to the organization 3. Better data integration of data as a blueprint of how the database is 4. Minimized data inconsistency constructed (divided into database tables in 5. Improved data access the case of relational databases). A displayed 6. Improved decision making schema is called a schema diagram. 7. Increased end-user productivity The data in the database at a particular Due to Physical independence, any of the moment in time is called a database state or below change will not affect the conceptual snapshot. It is also called the current set of layer. occurrences or instances in the database. In a Using a new storage device like Hard given database state, each schema construct Drive or Magnetic Tapes has its own current set of instances. Modifying the file organization For example, the STUDENT construct will technique in the Database contain the set of individual student Switching to different data structures. entities (records) as its instances. Changing the access method. Modifying indexes. Levels of Schema Changes to compression techniques or hashing algorithms. Change of Location of Database from say C drive to D Drive Due to Logical independence, any of the below change will not affect the external layer. (EXTERNAL LEVEL) Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of existing application programs Merging two records into one Breaking an existing record into two or more records DBMS Architecture: 1-Tier, 2-Tier & 3-Tier (CONCEPTUAL LEVEL) Single Tier Architecture The simplest of Database Architecture are 1 tier where the Client, Server, and Database all (PHYSICAL LEVEL) reside on the same machine. 2-tier Architecture An application interface which is called DATA INDEPENDENCE ODBC (Open Database Connectivity) an API Logical data independence which allows the client-side program to call is the capacity to change the conceptual the DBMS. Today most of the DBMS offers ODBC schema without having to change external drivers for their DBMS. schemas or application programs. 1. Presentation layer runs on a client (PC, Physical data independence Mobile, Tablet, etc.) is the capacity to change the internal 2. Data is stored on a Server. schema without having to change the 3-tier Architecture conceptual schema. Hence, the external 3-tier schema is an extension of the 2-tier schemas need not be changed as well. architecture. 3-tier architecture has following layers: 1. Presentation layer (your PC, Tablet, Mobile, etc.) 2. Application layer (server) 3. Database Server TYPES OF DBMS LANGUAGES DATA MODEL Data Definition Language (DDL) A data model is a relatively simple DDL is used for specifying the database representation, usually graphical, of more schema. It is used for creating tables, schema, complex real-world data structures. In general indexes, constraints, etc. in database terms, a model is an abstraction of a more To create the database instance = CREATE complex real- world object or event. To alter the structure of database – ALTER A model’s main function is to help you To drop database instances and objects understand the complexities of the real-world from database such as tables – DROP environment. To delete tables in a database instance – A data model represents data structures TRUNCATE and their characteristics, relations, To rename database instances – RENAME constraints, transformations, and other To Comment – COMMENT constructs with the purpose of supporting a specific problem domain. Data Manipulation Language (DML) DML is used for accessing and manipulating TYPES OF DATA MODELS data in a database. 1. Flat File Model To read records from table(s) – SELECT The flat (or table) model consists of a single, To insert record(s) into table(s) – INSERT two- dimensional array of data elements, Update the data in table(s) – UPDATE where all members of a given column are Delete records from the table(s) – DELETE assumed to be similar values, and all members of a row are assumed to be related Transaction Control Language (TCL) to one another. The changes in the database that we made The information stored in a flat file is using DML commands are either performed or generally alphanumeric with little or no roll backed using TCL additional formatting. The structure of a flat To persist the changes made by DML file is based on a uniform format as defined by commands in database – COMMIT the type and character lengths described by To rollback the changes made to the the columns. database – ROLLBACK A flat-file database is a simple two- dimensional repository of like data. 2. Hierarchical Model In hierarchical model, data is organized into a treelike structure with each record is having DATA MODEL one parent record and many children. The main drawback of this model is that it can Database design focuses on how the have only one to many relationships between database structure will be used to store and nodes. manage end-user data. It implies a single parent for each record. Data modeling, is the first step in designing a database, refers to the process of creating a This structure is very efficient to describe specific data model for a determined problem many relationships in the real world; recipes, domain. table of contents, ordering of paragraphs/verses, any nested and sorted information.  ENTITY RELATIONSHIP DIAGRAM 3. Network Model An ER diagram shows the relationship The network model expands upon the among entity sets. hierarchical structure, allowing many-to- many relationships in a tree-like structure that An entity set is a group of similar entities, allows multiple parents. and these entities can have attributes. In The network model organizes data using terms of DBMS, an entity is a table or attribute two fundamental concepts, called records of a table in database, so by showing and sets. Records contain fields. Sets define relationship among tables and one-to-many relationships between their attributes. records. Network Model is same as hierarchical ER diagram shows the complete logical model except that it has graph-like structure structure of a database. rather than a tree-based structure. Unlike hierarchical model, this model allows An ER diagram shows the relationship among each record to have more than one parent entity sets. record. Entity-relationship (er) model is based on the 4. Object-Oriented database Models notion of real- world entities and relationships In the 1990s, the object-oriented among them. While formulating real-world programming paradigm was applied to scenario into the database model, the er database technology, creating a new model creates entity set, relationship set, database model known as object databases. general attributes and constraints. This aims to avoid the object-relational impedance mismatch - the overhead of Er model is best used for the conceptual converting information between its design of a database. Er model is based on representation in the database (for example entities and their attributes. as rows in tables) and its representation in the Relationships among entities. application program (typically as objects). Object databases also introduce the key Entity ideas of object programming, such as An entity in an ER Model is a real-world entity encapsulation and polymorphism, into the having properties called attributes. Every world of databases. attribute is defined by its set of values called domain. 5. Entity-Relationship Model For example, in a school database, a An Entity–relationship model (ER model) student is considered as an entity. describes the structure of a database with the Student has various attributes like help of a diagram, which is known as Entity name, age, class, etc. Relationship Diagram (ERDiagram). An ER model is a design or blueprint of a Relationship database that can later be implemented as a The logical association among entities is database. The main components of E-R model called relationship. are: entity set and relationship set. Relationships are mapped with entities in various ways. Mapping cardinalities define the number of associations between two entities.  ER diagrams will be used to represent the external views. A specific representation of an external view is known as an external schema. Conceptual Model It represents a global view of the entire database by the entire organization. 6. Relational Model Also known as a conceptual schema, it is The relational model was introduced by E.F. the basis for the identification and high-level Codd in 1970 as a way to make database description of the main data objects. management systems more independent of any particular application. Internal Model Three key terms are used extensively in It is the representation of the database as relational database models: "seen" by the DBMS. relations, attributes, and It requires the designer to match the domains. conceptual model's characteristics and constraints to those of the selected First, the ordering of columns is immaterial in implementation model. a table. Second, there can't be identical tuples or rows in a table. And third, each tuple will Internal schema depicts a specific contain a single value for each of its attributes. representation of an internal model, using the database constructs supported by the chosen A relational database contains multiple database. tables, each similar to the one in the "flat" database model. Physical Model operates at the lowest level of abstraction, One of the strengths of the relational model is describing the way data is saved on storage that, in principle, any value occurring in two media such as magnetic, solid state, or optical different records (belonging to the same table media. The physical model requires the or to different tables), implies a relationship definition of both the physical storage devices among those two records. and the (physical) access methods required to reach the data within those storage DEGREES OF ABSTRACTION devices, making it both software and Data hiding is called as data abstraction. hardware dependent. Database Management System tries to hide details of how the data is stored and maintained, implementation details of the database and complexity of the database. Only a part of the database is shown to a user. External Model It is the end user's view of the data environment. It refers to people who use the application programs to manipulate the data and generate Information.  Data integrity is usually imposed during the RELATIONAL DATABASE MODEL database design phase through the use of RELATIONAL MODEL (RM) standard procedures and rules. It is represents the database as a collection of maintained through the use of various error relations. A relation is nothing but a table of checking methods and validation procedures values. Every row in the table represents a collection of related data values. These rows in Data integrity is enforced in both the table denote a real-world entity or hierarchical and relational database models. relationship. The following three integrity constraints are used in a relational database structure to RELATIONAL DATABASE KEYS achieve data integrity: Database key is an attribute or set of attributes which help you uniquely identify a record or row of data in a relation (table). INTEGRITY CONSTRAINTS Entity Integrity - The entity integrity constraint Super A super key is a group of single or states that primary key value can't be null. multiple keys which identifies rows in a table. This is because the primary key value is used Candidate is a set of attributes that uniquely to identify individual rows in relation and if the identify tuples in a table. Candidate Key is a primary key has a null value, then we can't super key with no repeated attributes. identify those rows. Primary is a column or group of columns in a A table can contain a null value other than the table that uniquely identify every row in that primary key field. table. Domain Integrity - Domain constraints can be Foreign is a column that creates a relationship defined as the definition of a valid set of values between two tables. The purpose of Foreign for an attribute. keys is to maintain data integrity and allow navigation between two different instances of The data type of domain includes string, an entity. character, integer, time, date, currency, etc. The value of the attribute must be available in Composite A composite key is a combination the corresponding domain. of two or more columns that uniquely identify a row within a database table. While each Referential Integrity - A referential integrity column may not be unique on its own, their constraint is specified between two tables. combined values are guaranteed to be unique across the table. Referential integrity ensures that the values for a set of attributes in one relation must also appear the same for the particular set attributes in another relation. INTEGRITY RULES Data integrity is the overall completeness, accuracy and consistency of data. This can be indicated by the absence of alteration between two instances or between two updates of a data record, meaning data is intact and unchanged. RELATIONAL SET OPERATORS 6. PRODUCT yields all possible pairs of rows The data in relational tables are of limited from two tables—also known as the value unless the data can be manipulated to Cartesian product. Therefore, if one table generate useful information. has six rows and the other table has three rows, the PRODUCT yields a list composed Relational algebra defines the theoretical way of 6 × 3 = 18 rows. of manipulating table contents using the eight 7. JOIN allows information to be combined relational operators: SELECT, PROJECT, JOIN, from two or more tables. JOIN is the real INTERSECT, UNION, DIFFERENCE, PRODUCT, and power behind the relational database, DIVIDE. allowing the use of independent tables linked by common attributes. The relational operators have the property of a. INNER JOIN An inner join includes only closure; that is, the use of relational algebra those tuples with matching attributes operators on existing relations (tables) and the rest are discarded in the produces new relations. There is no need to resulting relation. examine the mathematical definitions, properties, and characteristics of those b. OUTER JOIN to use outer joins to include relational algebra operators. all the tuples from the participating relations in the resulting relation. There 1. SELECT, also known as RESTRICT, yields are three kinds of outer joins − left outer values for all rows found in a table that join, right outer join, and full outer join. satisfy a given condition. i. LEFT OUTER JOIN All the tuples from 2. PROJECT yields all values for selected the Left relation, R, are included in attributes. In other words, PROJECT yields a the resulting relation. If there are vertical subset of a table. tuples in R without any matching 3. UNION combines all rows from two tables, tuple in the Right relation S, then the excluding duplicate rows. The tables must S-attributes of the resulting relation have the same attribute characteristics are made NULL. (the columns and domains must be ii. RIGHT OUTER JOIN All the tuples compatible) to be used in the UNION. When from the Right relation, S, are two or more tables share the same number included in the resulting relation. If of columns, and when their corresponding there are tuples in S without any columns share the same (or compatible) matching tuple in R, then the R- domains, they are said to be union attributes of resulting relation are compatible. made NULL. 4. INTERSECT yields only the rows that appear iii. FULL OUTER JOIN All the tuples from in both tables. As was true in the case of the Right relation, S, are included in UNION, the tables must be union- the resulting relation. If there are compatible to yield valid results. tuples in S without any matching For example, you cannot use INTERSECT if tuple in R, then the R-attributes of one of the attributes is numeric and one is resulting relation are made NULL. character-based. 5. DIFFERENCE yields all rows in one table that are not found in the other table; that is, it subtracts one table from the other. As was true in the case of UNION, the tables must be union-compatible to yield valid results. DATA DICTIONARY The data dictionary provides a detailed description of all tables found within the user/designer-created database. Thus, the data dictionary contains at least all of the attribute names and characteristics for each table in the system. In short, the data dictionary contains metadata—data about data. RELATIONSHIPS WITHINTHE RELATIONAL DATABASE one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N or M:M).