Database Systems: Chapter 1

CHAPTER 1: DATABASE SYSTEMS

• Data are raw, unprocessed facts that need formatting for storage, processing, and presentation.
• Data is the foundation of information and bedrock of knowledge.
• Knowledge is familiarity, awareness, and understanding of information applied to an environment, with new knowledge deriving from old.
• Information comes from processing raw data to reveal its meaning, requiring context to reveal its meaning.
• Data management is the discipline around generation, storage, and retrieval of data.
• A database is a shared, integrated computer structure housing a collection of end-user data and metadata.
• End-user data are the raw facts of interest to the end user.
• Metadata is data about data, integrating and managing the end-user data.
• Database management system (DBMS) is a collection of programs for managing database structure and controlling data access, serving as an intermediary between user and database.
• Database structure is stores as a file collection, accessible only through the DBMS.

DBMS Advantages

• Improved data sharing among users.
• Improved data security measures.
• Better data integration across the system.
• Minimized data inconsistency, which occurs when different versions of the same data appear.
• Improved data access through queries, which are specific requests to the DBMS for data manipulation.
• A query is a question.
• Ad hoc query is a spur-of-the-moment question.
• The DBMS returns a query result set to the application.
• Improved decision making due to data quality, which promotes accuracy, validity, and timeliness.
• Increased end-user productivity with efficient data management.

TYPES OF DATABASES

• Single-user database supports one user at a time.
• A single-user database on a personal computer is a desktop database.
• Multiuser database supports multiple concurrent users.
• A workgroup database supports a relatively small number of users (under 50) within a specific department.
• An enterprise database supports the entire organization with many users (hundreds) across departments.
• Centralized database stores data at a single site.
• Distributed database has data distributed across multiple sites.
• Cloud database is created and maintained using cloud data services.
• General-purpose databases contain a wide variety of data for multiple disciplines.
• Discipline-specific databases contain data focused on specific subjects.
• Operational database (OLTP) supports day-to-day company operations.
• Analytical database stores historical data and business metrics for strategic decisions.
• Data warehouse is a specialized database format optimized for decision support.
• Data warehouse contains historical data obtained from operational and external databases.
• Online analytical processing (OLAP) tools provide an advanced data analysis environment.
• Business intelligence describes a comprehensive approach to capture/process data to support business decision making.

Structured, Unstructured, and Semistructured data

• Databases are classified by the degree to which the data is structured.
• Unstructured data exists in its original raw format.
• Unstructured data format doesn't lend itself to processing to yield information.
• Structured data results from formatting unstructured data to facilitate storage, use, and information generation.
• Users apply structure (format) based on the type of processing desired.
• Semistructured data has been already processed to some extent
• Extensible Markup Language (XML) is used to represent and manipulate data elements in textual format.
• XML database supports the storage and management of semistructured XML data.

Database Design

• Database Design refers to designing the database structure to store and manage end-user data
• Careful design of database structure required to meet all user requirements
• Database design is a crucial aspect in all database work.
• Data processing (DP) specialist creates a computer-based system to track data and produce reports.
• Problems with file system processing:
• Lengthy development times.
• Difficulty of getting quick answers.
• Complex system administration.
• Lack of security and limited data sharing.
• Extensive programming.
• File system exhibits structural dependence
• Structural dependence means access to a file depends on its structure.
• Structural independence means file structure can be changed without affecting application access.
• Data redundancy occurs when the same data is stored unnecessarily in different places.
• Islands of information is the term for scattered data locations.
• Uncontrolled data redundancy leads to poor data security and inconsistency.
• Data Anomalies develop when required changes in redundant data are not made correctly.
• Common data anomalies:
• Update anomalies.
• Insertion anomalies.
• Deletion anomalies.

Components of a Database System

• Data modeling is a common method for documenting database designs.

• Design documentation facilitates communication among designers, users, and developers.

• Database system is an organization of components defining and regulating data collection, storage, management, and use.

• Components of database system: hardware, software, people, procedures, and data.

• Hardware includes the physical devices like computers, storage, printers, and network devices.

• Software includes operating system software, DBMS software, and application programs.

• Operating system software manages all hardware components.

• DBMS software manages the database within the system.

• Application/utility software is used to access/manipulate data in the DBMS and manage the computer environment.

• People includes all users of the database system.

• System administrators oversee the database system's operations.

• Database administrators (DBAs) manage the DBMS and ensure the database is functioning correctly.

• Database designers design the database structure.

• System analysts/programmers design and implement applications.

• End users use application programs for daily operations.

• Procedures are the instructions and rules governing database design and use.

• Data covers the collection of facts stored in the database.

DBMS Functions

• Data dictionary management: DBMS stores definitions of data elements and their relationships (metadata) in a data dictionary.
  • The DBMS uses the data dictionary to look up data component structures and relationships.
• Data storage management: DBMS creates and manages complex structures for data storage.
  • Performance tuning relates to activities improving storage and access speed.
• Data transformation and presentation: DBMS transforms entered data to conform to required structures.
• Security management: DBMS creates a security system for user security and data privacy.
• Multiuser access control: DBMS uses algorithms for concurrent database access without compromising integrity.
• Backup and recovery management: DBMS provides backup and recovery to ensure data safety and integrity.
• Data integrity management: DBMS enforces integrity rules, minimizing redundancy and maximizing consistency.
• Database access languages and application programming interfaces: DBMS provides data access through a query language.
  • Query language is a nonprocedural language to specify what must be done without specifying how.
  • Structured Query Language (SQL) is the standard query language supported by most DBMS vendors.
• Database communication interfaces: Current DBMS accepts end-user requests via multiple network environments.
• Database System Disadvantages:
• Increased costs.
• Management complexity.
• Maintaining currency.
• Vendor dependence.
• Frequent upgrade/replacement cycles.

Complex Data Base Technologies:

• Very Large Databases (VLDB)
• Big Data databases
• In-memory databases
• Cloud databases

CHAPTER 2: DATA MODELS

• Data Modeling is the process of creating a specific data model for a determined problem domain.
• Problem domain is a clearly defined area within the real-world environment that is systematically addressed.
• Data Model is a relatively simple representation (usually graphical) of complex real-world data structures.
• Database Model is the implementation of a data model in a database system.
• An implementation-ready data model components: description of data structure, enforceable rules, data manipulation methodology.
• Importance of Data Models:
• Facilitates communication among the designer, applications programmer, and the end-user.
• Fosters improved understanding of organization the database design is developed.

Data Model Basic Building Blocks:

• Entity is a person, place, thing, or event about which data will be collected and stored.
• Attribute is a characteristic of an entity.
• Relationship describes an association among entities:
  • One-to-many (1:M or 1..*) relationship.
  • Many-to-many (M:N or ..) relationship.
  • One-to-one (1:1 or 1..1) relationship.
• Constraint is a restriction placed on the data.

Business Rules

• Business Rule is a brief, precise, and unambiguous description of an organization’s policy, procedure, or principle.

• Properly written business rules are used to define entities, attributes, relationships, and constraints.

• Business rules derived from a detailed description of an organization's operations help create and enforce actions.

• Business rules set the stage for proper identification of entities, attributes, relationships, and constraints.

• Entity names should be descriptive of objects in the business environment, using familiar terminology.

• Attribute names should also be descriptive of the data they represent.

Standard Database Concepts

• Schema is the conceptual organization of the entire database as viewed by the database administrator.
• Subschema defines the portion of the database "seen" by application programs producing information.
• Data Manipulation Language (DML) defines data management environment.
• Schema data definition language (DDL) enables database administrator to define schema components.

Hierarchical and Network Models:

• Hierarchical Model was developed in the 1960s to manage large amounts of data, containing levels or segments.
• Segment is the equivalent of a file system's record type.
• Network Model was created to represent complex relationships and improve performance.

Relational Model:

• Relational model describes data manipulation constructs based on advanced mathematical concepts.
• Relation (table) is a matrix composed of intersecting rows and columns.
• Each row is called a tuple.
• Each column represents an attribute.
• Relational Database Management System (RDBMS) performs basic functions provided by hierarchical and network DBMS, with additional functions that make the relational data model easier to understand.
• Relational diagram is a representation of the relational database's entities, attributes, and relationships.
• Relational table stores a collection of related entities.

SQL-Based Relational Database Application components:

• End-user interface

• Collection of tables stored in the database.

• The ERM is a widely accepted standard for data modeling

• Entity relationship diagram (ERD) uses graphical representations to model database components.

• ER Model is based on the following components:

• Entity is data will be collected and stored.

• Each row in table is known as entity instance or entity occurrence in the ER model.

• Each entity consists of a set of attributes the describes characteristics of the entity.

• Relationships is associations among data.

• The ER model uses connectivity to label relationship types.

• The different relationship types using ER Notations: Chen and Crow's Foot.

Object-Oriented (OO) Model:

• Object-oriented data model (OODM) contains both data and their relationships in an object.
• OODM is the basis for the object-oriented database management system (OODBMS).
• The facts within the object are given greater meaning.
• OODM is said to be a semantic data model as semantic indicates meaning.
• Subsequent OODM integrates all operations on an object, such as changing data values.
• The 00 data model is based on the following components:
  • Object is abstraction of a real-world entity.
  • Attributes describe properties,
  • Objects are grouped in classes.
  • Classes is collection of similar objects with shared structute (attributes).
  • Methods define the object's behavior through actions.
• Classes are organized in a class hiearchy with only one parent
• Inhertiance is the ability of an object within the class hierarchy to inheret from the classes above.
• Object-oriented data models are typically depicted UML class diagrams.
• UML is a language based on 00 concepts
• UML class diagrams are used to represent data and their relationships within the larger UML object-oriented system's modeling language.

Object/Relational and XML

• Extended relational data model (ERDM): Adds many OO model features to relational structure.
• A DBMS based on ERDM described as a object/relational database management system (O/R DBMS).
• Extensible Markup Language (XML): Emerges as the standard for effective data exchange.
• Big Data refers to innovative ways to manage and derive insight from large amounts of web/sensor-generated data.

Characteristics of Big Data databases

• Volume: Amounts of data being stored.
• Velocity: Speed with which data grows; need to process these data quickly.
• Variety: Data being collected comes in multiple different data formats.

Most Frequently used Big Data Technologies:

• Hadoop
  • Hadoop Distributed File System (HDFS)
    • Uses 3 types of nodes: name node, data node and client node.
• MapReduce
• NoSQL
• NoSQL Databases address specific challenges of the Big Data era to a new generation of databases.
• NoSQL databases are not based on the relational model.

General Characteristics of NoSQL Databases:

• Not based on the relational model and SQL.
• Support distributed database architectures.
• Provide high scalability, high availability, and fault tolerance.
• Support very large amounts of sparse data.
• Geared toward performance rather than transaction consistency.
• Key-value data model's structure comprises two data elements: a key and a value, where each key has a corresponding value or set of values (attribute-value or associative data model).
• NoSQL supports distributed database architecture.
• NoSQL supports very large amounts of sparse data, suited for handling high volumes, particularly for sparse data.
• NoSQL provides high scalability, high availability, and fault tolerance.
• Most NoSQL databases are geared toward performance rather than transaction consistency.
• -Some NoSQL databases provide a feature called eventual consistency
• Key-value representation is is composed of key - value -Each represents attribute of one entity
  • The "value" column represents an entity's value.

Model describes end users’ view of data environment.

• External Model: Specific External schema that describes the models components.
• Conceptual global vies: Known as design task for the creation of conceptual data implemented in DBMS, the independence means of the physical tasks.
• Conceptual schema creates the designs. Internal hardware and is software used by the DBM software. Means it used physical aspects in the data management (software). Means not using hardware software used.
• Physical Independence (software)

CHAPTER 3: THE RELATIONAL DATA BASE MODEL

• Important characteristics include a 2D structure of rows and columns, each with a single entity occurence, each table having unique data format with at least one value.

• Every table needs to specify characteristics range of values to be considered by the DBMS

• Types of data to be supported by a DBMS include numeric, character, data and logical

• Domain relates to column's range of possible values

• The primary key (PK) is an attribute that uniquely identifies any given row Keys are attributed that determine values that relates

• Determination involves valuing one attribute that determines the value of another

• Functional dependence means that value of one/more that an attribute value that determines one or more values

  • The one that refers to functional dependencies is full that that attributes are necessary for the relationships

• A Determinant is the key, the attribute. The key

Types of keys:

• Composite key: that is composed of more attributes. an attributes part of a key is called attribute key

• Superkey: A key is uniquely identifies in any row . In other words a superkey determines every attribute in the row.

• Candidate key that has minimal super which has necessary values(attributes). It's a has full dependency

• There are two types of integrity to make sure it functions: the primary key (PK) needs requirements

  • All the values required in the primary key (PK) need to be unique -No attribute can contain a null -* A null* is absence of any data value, and can't be allowed -A null represents any of the following: Unknown missing attributes

• Relates to integrity and validity constraints

• There relational algebraic expression includes: select resticted. Project union.

• Forks keys are used to ensure referential, every reference as a variety entity.

• • The integrity is valid*.

• A Secondary key is is used to strictly retrieve the purposes, There relational operations have closures, the existence of realtion algebra that produce relation.

• Select , Refered: to retrieve use table as input. The values retrieve.

• Project retrieves all values based selected attributes. The values retrieved in orders.

• Union combines 2 tables in 1 table that excludes row duplicates. The tables need to share compatible to each other in characterisitcs.

• Intersect has two tables share common attribute

• • The Intersect shares only if both types share common attributes.

Algebraic Expressions.

• Difference - Table that are found. The subtracts that table.

• Product* expression creates pairs of rows for the tables used, to be known as Cartesian Product

• Join* expression retrieves the information and combined with two or more tables as well.

• Natural join*: links a table with common attributes that share as well The join uses is an equijoin , table uses an equalty operation: inner joins and outter joins

• Dive expresses 2 tables and the divisor that used to call.

• data dictionary is description of all tables by the User

• • Design*- because designs the data
• • System catalog* can be described system data

• Homynms have meanings that difference or simililarities

• • Synonym* as a opposite and different describes

• Relationships within the 1:N expression , is one on many : The most used expression.

• 1:1 expression* should be one entity be linked

• M:N relation In relations cannot be used, only for specific relations. in the expression with other

• The EXPRESSION CANNOT BE LINK IN THIS MODEL

• 1 to many (1:n) Is to implement from the primary key of the table/model , the primary key becomes a forks key

• expression in N/m that cannot direct , expression and is to connect 1:m relationship

Has: The bridge entity allows inheritence to easily add a multi relationship to another

• Controlling redundent. -Foreign key is crucial in controlling to ensure connect -The real test ensure more keys instead store Elimination by an attribute
• Index access rows to each other
• Indexes keys: as ordered of each keys with rows
• Unique uses with *index key * has The Rules, is a data expression related
• Each expressions of access
• **