MIDTERM NOTES (LEC).pdf

CHAPTER 1 Data admin is the person in the DB environment that is Advantages and Disadvantages of DBMS responsible for the management of the data resources. * Advantages:...

CHAPTER 1 Data admin is the person in the DB environment that is Advantages and Disadvantages of DBMS responsible for the management of the data resources. * Advantages: Database Administrator (DBA) is responsible for the physical realization of the database Business rules are the constraints that are defined in the database Components of the DBMS Environment * Disadvantages: Abbreviations and their Meanings - DBMS (Database Management Systems) - DBTG (Data Base Task Group) Disadvantages of File System - IDS (Integrated Data Store) - Data Redundancy and Inconsistency - IMS (Information Management System) - Difficulty in Accessing Data - CODASYL (Conference on Data Systems - Data Isolation Languages) - Atomicity Issues - ANSI (American National Standards Institute) - Concurrent Access Anomalies - GUAM (Generalized Update Access Method) - Security Problems - DDL (Data Definition Language) - DML (Data Manipulation Language) System Catalog / data dictionary / data directory - OODBMS (Object-Oriented Database - is a repository of information describing the Management System) data in the database, also known as metadata. - ORDBMS (Object-Relational Database Management System) For standardization, the DBTG specified three distinct languages: Hierarchy of Data Organization (1) Schema DDL – enables the DBA to define the schema 1. BIT – smallest unit of information (2) Subschema DDL – allows the application programs to 2. BYTES – collection of 8 bits / 1 character define the parts of the database they require 3. DATA ITEMS – collection of related characters (3) DML – to manipulate the data 4. RECORDS – collection of related data items 5. FILE – collection of related records History 6. DATABASE – collection of related files * 1960s onwards - file-based system 7. INFORMATION SYSTEMS – collection of related * mid-1960s – hierarchical and network models databases * 1970 – relational data model 8. CLOUD COMPUTING – collection of related * 1970s – prototype RDBMSs developed information systems * 1976 – Entity-Relationship (ER) model proposed * 1979 – commercial RDBMSs appear * 1980s – commercial relational DBMS products (DB2 and SQL/DS) * 1987 – ISO SQL standard * 1990s – appearance of OODBMS and ORDBMS * 1990s – data warehousing systems appear * mid-1990s – web–database integration * 1998 – XML  CHAPTER 2 Database Languages Data sublanguage: The Three-Level ANSI-SPARC Architecture - Data Definition Language (DDL) - used to specify ANSI-SPARC (American National Standards Institute, the database schema Standards Planning and Requirements Committee) - Data Manipulation Language (DML) - used to both read and update the database The DBTG recognized the need for a two-level approach with a system view (SCHEMA) and user view The part of a DML that involves data retrieval is called a (SUBSCHEMAS) query language * External level – users’ view. This level describes that 2 Types of DML part of the database that is relevant to each user. (1) Procedural * Conceptual level – community view. This level - A language that allows the user to tell the system what describes WHAT data is stored in the database and the data is needed and exactly how to retrieve the data relationships among the data. - specify HOW the output of a DML statement is to be * Internal level – physical representation of the obtained database on the computer. This level describes HOW the data is stored in the database. (2) Nonprocedural - A language that allows the user to state what data is needed rather than how it is to be retrieved - describe only WHAT output is to be obtained Nonprocedural languages are also called declarative languages. Data Models and Conceptual Modeling * Data Model - an integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization Schemas, Mappings, and Instances * Database Schema – the overall description of the DB * A data model can be thought of as comprising 3 components: * Conceptual/Internal Mapping (1) structural part - consists of a set of rules according - This mapping enables the DBMS to find the to which databases can be constructed actual record or combination of records in (2) manipulative part - defines the types of operation physical storage that constitute a logical record that are allowed on the data in the conceptual schema (3) set of integrity constraints - ensures that the data is accurate * External/Conceptual Mapping - This mapping enables the DBMS to map names The purpose of a data model is to represent data and to in the user’s view to the relevant part of the make the data understandable. conceptual schema. 3 Categories of Data Models * Database Instance - the data in the database at any (1) Object-based – use concepts such as entities, particular point in time attributes, and relationships. * The schema is sometimes called the intension of the (2) Record-based – the database consists of a number database; an instance is called an extension (or state) of of fixed-format records, possibly of differing types. the database. * There are 3 principal types of record-based logical data model: the relational data model, the network data model, and the hierarchical data model. (3) Physical – describe how data is stored in the Database System Development Lifecycle computer, representing information such as record structures, record orderings, and access paths. * Conceptual modeling or conceptual database design is the process of constructing a model of the information use in an enterprise that is independent of implementation details. * Conceptual models are also referred to as “logical models” Functions of a DBMS - Data storage, retrieval, and update - A user-accessible catalog - Transaction support - Concurrency control services - Recovery services - Authorization services - Support for data communication - Integrity services - Services to promote data independence - Utility services CHAPTER 10 The Information Systems Lifecycle * Data – one of the most important resources of the organization that must be treated with respect, just like all ither organizational resources. 2 major sources of data The Database Design Phase (1) Internal data – data that have been collected from * Conceptual database design – the process of within the organization. constructing a database model of the data used in the (2) External data – data that have been collected from organization/enterprise that is independent of all outside of the organization. physical considerations. The goal of this phase is to produce a conceptual schema for the database that is independent from any specific DBMS. **Note: there is more to read about each of the stages of DSDLC sa book (pg.350-372) kamo lang niya bahala basa kay mo taas nang docu if i add nako hehe  CHAPTER 12 Types of Attributes * Simple attribute – single component with an Entity–Relationship Modeling independent existence. * ER modeling is a top-down approach to database * Composite attribute – multiple components, each design that begins by identifying the important data with an independent existence called entities and relationships between the data that * Derived attribute – a value that is derivable from the must be represented in the model. We then add more value of a related attribute details, such as the information we want to hold about * Multi-valued attribute – holds multiple values for each the entities and relationships called attributes and any occurrence of an entity type constraints on the entities, relationships, and attributes The Entity-Relationship (ER) Database Model Entity types What is ERD? – group of objects with the same properties, which - A graphical representation that shows the are identified by the enterprise as having an relationship among people, objects, places, independent existence. concept, or events, within an information – basic concept of the ER model is the entity type system. - Created based on 3 basic concepts: entities, Entity occurrence – a uniquely identifiable object of an attributes, relationships entity type. History of ER model Strong entity type – not existence-dependent on some * 1970s – Peter Chen was credited with developing ER other entity type. modeling for database design Weak entity type – existence-dependent on some other * 1976 – a seminal paper titled “The Entity-Relationship entity type. Model: Toward a Unified View of data” was published by P. Chen Relationship types * 1960s and 1970s – Charles Bachman developed a type – set of meaningful associations among entity types. of Data Structure Diagram (Bachman Diagram) – Brown published works on real-world Relationship occurrence – a uniquely identifiable systems modeling association that includes one occurrence from each – James Martin added ERD refinements participating entity type. The works of Chen, Bachman, Brown, Martin and others Degree of a relationship type – the number of also contributed to the development of the Unified participating entity types in a relationship. Modeling Language (UML), widely used in software * Participants – the entities involved in a particular design. relationship type * Degree – the number of participants in a relationship type Diagram Symbols and Notations 1. Rectangle – strong entity Recursive relationship – a relationship type in which 2. Double rectangle – weak entity the same entity type participates more than once in 3. Oval – attribute different roles. 4. Double oval – multi-valued attribute 5. Broken oval – derived/computed attribute Attributes and Domains 6. Rhombus – strong relationship Attribute – a property of an entity or a relationship 7. Double rhombus – weak relationship type. 8. Lines – connect entities Attribute domain – the set of allowable values for one or more attributes. Cardinality Notations 3 Forms of Database Anomalies (1) Insertion Anomaly – inability to add data into the DB due to the absence of other data (2) Deletion Anomaly – removal of data from the DB that will result into removal of the other data or the entire record (3) Update Anomaly – any changes made to the data that result into inconsistencies of the other data Functional dependencies - describes the relationship between attributes CHAPTER 14 Determinant – data that identifies the other data and made that record unique from other records Normalization Dependent – data that is being identified - A technique for producing a set of relations with desirable properties, given the data 3 Types of Functional Dependency requirements of an enterprise. (1) Full Functional – dependent column(s) can be identified and retrieved uniquely from the table Purpose of normalization using the determinant column - minimize/reduce data redundancy - minimize/reduce the number of (2) Partial Functional – dependent column(s) can be attributes/columns identified and retrieved uniquely from the table - prevent DB anomalies using the determinants or part of it (candidate - simplify DB queries columns) History of Normalization (3) Transitive Functional – dependent column(s) * 1970 – 1st concept of normalization (1NF) proposed by has an indirect relationship between other Edgar F. Codd) dependent columns. * 1971 – 2NF and 3NF * 1977 – Ronald Fagin introduced 4NF * 1979 – 5NF by Fagin * 1981 – domain key normal form (DKNF) by Fagin * 1982 – elementary key normal form (EKNF) by Carlo Zaniolo * 2012 – essential tuple normal form (ETNF) by R. Fagin, Hugh Darwen, and Chris J. Date Data Redundancy and Databases Anomalies Data redundancy is allowed in the database in the (1) foreign key column of some tables (2) columns without functional dependencies of the other columns Functional dependencies – describes the relationship between attributes  MIDTERM COVERAGE (LAB) CHAPTER 1 * Advantages and disadvantages of DBMS * Disadvantages of File System * System Catalog * Schema ddl, subschema ddl, dml * DBTG, IDS, IMS, CODASYL, ANSI, GUAM * History of DBMS, memorize the dates * OODBMS and ORDBMS * Declarative language CHAPTER 2 * Database Design * Mapping Conceptual to Logical Design * Entity-Relationship (ER) Modeling * Normalization