Database Management System PDF

management, and use of data CHAPTER I: DATABASE SYSTEM within a database environment. Data Five Components of a Database System: - Raw facts...

management, and use of data CHAPTER I: DATABASE SYSTEM within a database environment. Data Five Components of a Database System: - Raw facts Hardware - Have yet to be processed Software People Information Procedure - Processed raw data to something Data that is useful to the user. Database Management System Knowledge (DBMS) - The body of information and facts - A collection of programs that about a specific subject. manages the database structure and controls access to the data Why do we need databases? stored in the database. - Databases are needed to store and - A database resembles a very manage data that is vast and well-organized electronic filing varied. cabinet in which powerful software (DBMS) helps manage the cabinet’s Database contents. - A shared, integrated computer structure that houses a collection Advantage of a Database Management of related data. System: - A database contains two types of Improved data sharing data: Improved data security - End-user data (raw facts) Better data integration - Metadata Minimized data inconsistency Improved data access Metadata Improved decision making - Data about data; data about data Increased end-user productivity characteristics and relationships. - Stores information such as the Disadvantage of a Database Management name of each data element, the System: type of values (numeric, dates, or Increased costs text) stored on each data element, Management complexity and whether the data element can Maintaining currency be left empty. Vendor dependence Frequent upgrade/replacement Database System cycles - It refers to an organization of components that define and regulate the collection, storage, Database Management System ○ A database located at a Functions: single site. Data dictionary management Distributed Database Data storage management ○ A logically related database Data transformation and that is stored in two or more presentation physically independent Security Management sites. Multi-user access control Cloud Database Backup and recovery management ○ A database that is created Data integrity management and maintained using cloud Database access languages and services, such as Microsoft application programming Azure or Amazon AWS. interfaces Database communication Types of Databases - Type of data stored: interfaces General-purpose Database ○ A database that contains a Types of Databases - Number of Users: wide variety of data used in Single-user Database multiple disciplines. ○ A database that supports Discipline-Specific Database only one user at a time. ○ A database that contains Desktop Database data focused on specific ○ A single-user database that subject areas. runs on a personal computer. Types of Databases - Data Usage: Operational Database Multi-user Database ○ Also known as Transactional ○ A database that supports Database, OLTP Database, multiple concurrent users. Production Database Workgroup Database ○ A database designed ○ A multi-user database that primarily to support a usually supports fewer than company’s day-to-day 50 users or is used for a operations. specific department in an Analytical Database organization. ○ A database focused Enterprise Database primarily on storing ○ The overall company data historical data and business representation, which metrics used for tactical or provides support for present strategic decision making. and expected future needs. Unstructured Data Types of Databases - Location: - Data that exists in its original and Centralized Database raw state (in the format in which it was collected).  ○ Situation: You need to add a Structured Data new agent in an already - Data that has been formatted to existing customer file, in facilitate storage, use, and order to do that you have to information generation. first create a dummy account to reflect the new Database Design addition. - It refers to the activities that focus ○ The potential for creating on the design of the database data inconsistencies would structure that will be used to store be great. and manage end-user data. Deletion Anomalies - A database that meets all user ○ Situation: deleting a couple requirements does not just happen, customer profiles will cause its structure must be designed the deletion of an agent’s carefully. data. ○ This is not desirable. Data Redundancy - It exists when the same data is Database vs. Spreadsheet stored unnecessarily at different - Spreadsheet allows for the places. manipulation of data in a tabular - Poor data security format but it does not support even - Data inconsistency the most basic data functionality - Data-entry errors such as support for - Data integrity problems self-documentation through metadata, enforcement of data Data Anomaly types to ensure data consistency It develops when not all of the within a column, defined required changes in the redundant relationships among tables, or data are made successfully. constraints to ensure consistency of data. Update Anomalies ○ Situation: Agent Leah F. Hahn has a new phone number and wants to renew it in each and all of her CUSTOMER file records. ○ In a large file system, such a change might occur in hundreds or thousands of records. The potential for data inconsistencies is great. Insertion Anomalies CHAPTER II: DATA MODELS that relationships are bidirectional; that is, they go both ways. Data Modeling - How many instances of B - It is the first step in designing a are related to one instance database. of A? - Refers to the process of creating a specific data model for a - How many instances of A are determined problem domain. related to one instance of B? Data Model Naming Conventions - It is a relatively simple - Entity names should be descriptive representation. of the data represented by that - Usually a graphical representation attribute, and objects in the of more complex real-world data business environment and use structures. terminology that is familiar to the users. Attribute - It is also a good practice to prefix - It is a characteristic of an entity or the name of an attribute with the object. An attribute has a name and name or abbreviation of the entity a data type. in which it occurs. - For example, in the Types of Relationships: CUSTOMER entity, the customer’s credit limit may One-to-Many (1:M or 1..*) be called relationship CUS_CREDIT_LIMIT. The CUS Many-to-Many (M:N or *..*) indicates that the attribute relationship is descriptive of the One-to-One (1:1 or 1..1) relationship CUSTOMER entity, while CREDIT_LIMIT makes it easy Constraint to recognize the data that - A restriction placed on data, usually will be contained in the expressed in the form of rules. attribute. Business Rule Relational Model - A description of a policy, procedure, - Developed by E. F. Codd of IBM in 1970, or principle within an organization. the relational model is based on - As a general rule, a noun in a mathematical set theory and business rule will translate into an represents data as independent entity in the model, and a verb relations. (active or passive) that associates - Each relation (table) is the nouns will translate into a conceptually represented as a relationship among the entities. two-dimensional structure of - To properly identify the type of intersecting rows and columns. relationship, you should consider  - The relations are related to each and the relationships between other through the sharing of those entities. common entity characteristics (values in columns). Entity Table (relation) - It is a person, place, thing, or event - A logical construct perceived to be about which data can be stored. a two-dimensional structure - An entity is represented in the ERD composed of intersecting rows by a rectangle, also known as an (entities) and columns (attributes) entity box. that represents an entity set in the - The name of the entity, a noun, is relational model. written in the center of the - The relational model also describes rectangle. The entity name is a precise set of data manipulation generally written in capital letters constructs based on advanced and in singular form. mathematical concepts. - Each entity consists of a set of - Tables are related to each other attributes that describes particular through the sharing of a common characteristics of the entity. attribute (a value in a column). Entity Relationship Model (ERM) Tuple - A data model that describes - In the relational model, a table row relationships (1:1, 1:M, and M:N) among entities at the conceptual Relational Database Management level with the help of ER diagrams. System (RDBMS) - A collection of programs that Relationship manages a relational database. The - An association between entities. RDBMS software translates a user’s - It describes associations among logical requests (queries) into data. Most relationships describe commands that physically locate associations between two entities. and retrieve the requested data. - The ER model uses the term connectivity to label the Relational Diagram relationship types. The name of the - It is a representation of the relationship is usually an active or relational database’s entities, the passive verb. attributes within those entities, ER Notations: Chen Notation Class Diagram Notation Crow’s Foot Notation Entity Relationship Diagram (ERD) - A diagram that depicts an entity-relationship model’s entities, attributes, and relations. External Model - The application programmer’s view of the data environment. Given its business focus, an external model works with a global database schema data subset. External Schema selected DBMS and is - The specific representation of an therefore software external view; the end user’s view of dependent. the data environment. Conceptual Schema Conceptual Model - A representation of the conceptual It represents a global view of the model, usually expressed entire database by the entire graphically. organization. That is, the conceptual model integrates all Internal Model external views (entities, In database modeling, a level of relationships, constraints, and data abstraction that adapts the processes) into a single global view conceptual model to a specific of the data in the enterprise. DBMS model for implementation. The internal model is the representation of a database as “seen” by the DBMS. In other words, the internal model requires a designer to match the conceptual model’s characteristics and constraints to those of the selected implementation model. Software Independence ○ A property of any model or application that does not depend on the software used to implement it. Hardware Independence ○ A condition in which a model does not depend on the hardware used in the model’s implementation. Therefore, changes in the Logical Independence hardware will have no effect ○ A condition in which the on the database design at internal model can be the conceptual level. changed without affecting Logical Design the conceptual model. ○ A stage in the design phase that matches the Internal Schema conceptual design to the - A representation of an internal requirements of the model using the database  constructs supported by the 8. Each table must have an attribute chosen database. or combination of attributes that uniquely identifies each row. Physical Model - A model in which physical Key characteristics such as location, - One or more attributes that path, and format are described for determine other attributes. the data. The physical model is both hardware and software Types of Keys: dependent. Primary key (PK) ○ In the relational model, an Physical Independence identifier composed of one - A condition in which the physical or more attributes that model can be changed without uniquely identifies a row. affecting the internal model. Composite Key ○ A multiple-attribute key. Key Attribute ○ An attribute that is part of a CHAPTER III: THE RELATIONAL primary key. DATABASE MODEL Superkey ○ An attribute or attributes Characteristics of a Relational Table: that uniquely identify each 1. A table is perceived as a entity in a table. two-dimensional structure Candidate Key composed of rows and columns. ○ A minimal superkey is a key 2. Each table row (tuple) represents a that does not contain a single entity occurrence within the subset of attributes that is entity set. itself a superkey. 3. Each table column represents an Foreign Key (FK) attribute, and each column has a ○ An attribute or attributes in distinct name. one table whose values 4. Each intersection of a row and must match the primary key column represents a single data in another table or whose value. values must be null. 5. All values in a column must Null conform to the same data format. ○ The absence of an attribute 6. Each column has a specific range value. Note that a null is not of values known as the attribute a blank. domain. Entity Integrity 7. The order of the rows and columns ○ The property of a relational is immaterial to the DBMS. table that guarantees each entity has a unique value in  a primary key and that the Full Functional Dependence key has no null values. - A condition in which an attribute is Referential Integrity functionally dependent on a ○ A condition by which a composite key but not on any dependent table’s foreign subset of the key. key must have either a null entry or a matching entry in INTEGRITY RULES the related table. DEPENDENCIES Determination - The role of a key. In the context of a Flags database table, the statement “A - Special codes implemented by determines B” indicates that designers to trigger a required knowing the value of attribute A response, alert end users to means that the value of attribute B specified conditions, or encode can be looked up. values. - Flags may be used to prevent nulls Functional Dependence by bringing attention to the - Within a relation R, an attribute B is absence of a value in a table. functionally dependent on an attribute A if and only if a given Relational Algebra attribute A’s value determines A set of mathematical principles exactly one attribute B’s value. that form the basis for - The relationship “B is dependent on manipulating relational table A” is equivalent to “A determines B” contents; the eight main functions and is written as A → B. are: ○ SELECT Determinant ○ PROJECT - Any attribute in a specific row ○ JOIN whose value directly determines ○ INTERSECT other values in that row. ○ UNION ○ DIFFERENCE Dependent ○ PRODUCT - An attribute whose value is ○ DIVIDE determined by another attribute.. Relvar PROJECT - Short for relation variable, a - In relational algebra, an operator variable that holds a relation. A used to select a subset of columns. relvar is a container (variable) for - Denoted by the Greek letter pi (π). holding relation data, not the Codd used the lowercase π in his relation itself. original article on the relational model, and that is what we use Closure here. - A property of relational operators - Pi is followed by the list of that permits the use of relational attributes to be returned as algebra operators on existing subscripts and then the relation tables (relations) to produce new listed in parentheses. relations. - 𝜋𝐴(𝑅) - 𝜋𝐼𝑇𝐸𝑀_𝐷𝐸𝑆𝐶,𝑃𝑅𝐼𝐶𝐸(𝐼𝑇𝐸𝑀𝑆) SELECT - In relational algebra, an operator used to select a subset of rows. - Also known as RESTRICT. - Denoted by the lowercase Greek letter sigma (σ). - Sigma is followed by the condition to be evaluated (called a predicate) as a subscript, and then the relation is listed in parentheses. - 𝜎𝑐(𝑅) - 𝜎𝑃𝑅𝐼𝐶𝐸