Database Systems and Operations

Database Systems

• A database system comprises a database and DBMS software.
• A database is a collection of related data, regardless of size or complexity.
• Databases represent real-world aspects and are designed with a specific purpose.
• DBMS (Database Management System) is a computerized system for creating and maintaining databases.
• DBMS facilitates defining, constructing, manipulating, sharing, protecting, and maintaining databases

Database Operations

• Defining a database involves specifying data types, structures, constraints, and metadata.
• Constructing a database refers to storing data on a storage medium.
• Manipulating a database includes querying, updating, and generating reports.
• Sharing a database allows multiple users and programs to access it simultaneously.
• Protecting a database involves safeguarding against crashes and unauthorized access.
• Maintaining a database means evolving it as requirements change over time.
• Examples of queries include retrieving and listing data, while updates involve creating, changing, and entering data.

Steps of Making a Database

• The database creation process involves requirements specification and analysis.
• Followed by conceptual design using an ER model.
• Then logical design implemented in a relational DBMS.
• Finally, physical design implemented

Traditional File Processing Issues

• Traditional file processing leads to wasted storage space.
• Traditional file processing leads to redundant efforts to maintain common data leading to inconsistencies.
• Traditional file processing involves defining and implementing files separately.

Characteristics of the Database Approach

• Database systems are self-describing in nature.
• Database systems provide insulation between programs, data, and data abstraction.
• Database systems support multiple views of the data.
• The database approach enables sharing data and multiuser transaction processing.
• A catalog is used to store the schema in the database approach.

Metadata

• Metadata describes the database, including the structure of each file.
• Metadata includes the type and storage format of each data item.
• Metadata describes the constraints on the data.
• NoSQL systems do not require metadata.
• A data model creates a conceptual representation of data to describe the structure of a database.
• DBMS must enforce transaction properties such as isolation and atomicity.

Database Users

• Database users include: Database Administrators, Database Designers, End Users, System Analysts, and Application Programmers (Software Engineers).
• Administrators authorize access, coordinate, monitor use.
• Administrators acquire resources and solve security and response time issues.
• Designers identify stored data, choose structures, communicate with users and develop database views.

End Users

• End users access databases for querying, updating, and generating reports.
• Casual end users are managers using sophisticated queries.
• Naive end users are parametric, such as bank tellers or social media users.
• Sophisticated end users include engineers, scientists, and business analysts.
• Standalone users utilize ready-made program packages.
• System analysts determine end-user needs and develop specifications for standard transactions.
• Application programmers implement, test, debug, document, and maintain these specifications.

Database Structure

• Database structure includes data types.
• Also includes relationships
• And includes constraints

Data Models

• High-level/conceptual data models exist.
• Representational/implementation data models exist.
• Low-level/physical data models exist.
• Self-describing data models exist.
• Conceptual data models use concepts like entities (Employee), attributes (Employee’s name), and relationships (Works-on).
• Representational (record-based) data models example is the relational data model.
• Physical data models represent record formats, orderings, and access paths like indexing or hashing.
• Self-describing data models combine data description with data values, using XML or NoSQL for big data

Schema

• Each object in the schema is a schema construct.
• Data in a database at a moment in time is a database state/snapshot/current set of occurrences or instances.
• Database schema (intension) has an empty state for its database state (extension).
• DBMS helps ensure every database state is valid.
• Threeschema architecture achieves and visualizes database approach characteristics.
• It explains data independence.
• With the threeschema architecture, data extracted from a stored database must be reformatted to match a user’s external view.
• Mapping helps transform requests and results between levels.

Data Independence

• Logical data independence involves the capacity to change the conceptual schema.
• In logical data independence you dont have to change external schemas or application programs.
• Physical data independence involves the capacity to change the internal schema.
• With physical data independance, you dont have to change the conceptual schema.
• Data independence occurs if a schema change at one level doesn't require a change at the next higher level; only the mapping between levels changes.

SQL

• SQL includes Data Definition Language (DDL) for creating, dropping, and altering tables.
• DDL defines both conceptual and external schemas.
• SQL includes View Definition Language (VDL) for creating and dropping views.
• SQL includes Data Manipulation Language (DML) for deleting, inserting, selecting, and updating data.

Centralized and Client/Server Architectures for DBMSs

• Centralized architectures utilize mainframe computers and terminals.
• Centralized architectures utilize PCs, workstations, and mobile devices also.
• Basic client/server architectures connect PCs/workstations to a file server.
• Machines may be client sites only (mobile devices), dedicated servers, or have both functionalities.
• A server in client/server architecture provides services to client machines.
• DBMS architectures use a two-tier or three-tier framework on the client/server framework.

Two-Tier Client/Server Architectures for DBMSs

• The client side includes the user interface and application programs.
• The server side includes query and transaction functionality.
• Open Database Connectivity (ODBC) provides a connection between client-side programs and DBMS.
• Java Database Connectivity (JDBC) is a related standard for Java.
• Two-tier architecture software components are distributed over client and server.
• Benefits of the two-tier architecture include simplicity and compatibility with existing systems.

Three-Tier and n-Tier Architectures

• Three-tier architecture adds an intermediate layer between the server and client to run apps.
• Three-tier architecture improves database security and stores business rules in the intermediate layer.
• The three-tier architecture extends to n-Tier, where each tier can run independently on an appropriate processor or OS.

Classifying DBMS

• DBMSs are classified according to the data model they are based on: relational, object, NoSQL, XML.
• Classified by number of users: single or multi user
• Classified by number of sites: centralized or distributed.
• Classified by cost: Free or sold in modules/with license.
• Data warehousing and mining features often incur extra costs.
• Classified by purpose: general or special purpose.

Big Data Systems

• Big data systems use Key-value (unique key with value), Document (JSON-based).
• Big data systems use Graph (objects as nodes, relations as edges)
• Big data systems use Column-based (columns of rows clustered on disk pages).
• XML model is a standard for web data exchange using hierarchical tree structures.
• XML data is elements with tags and nesting capabilities.

Entity-Relationship (ER) Model

• Entity-Relationship (ER) model describes data as entities, relationships, and attributes.
• It is a high-level conceptual data model.
• ER diagrams are diagrammatic notations associated with the ER model.
• The first step of ER is Requirements Collection and analysis parallel with functional requirements.
• Conceptual schema is a concise description of data used to communicate with users.
• The last step is the physical design phase and internal storage structures parallel with application program design and transactions implementation.

Key Concepts

• Entity is a thing or object with an independent existence, either physical or conceptual.
• Attribute is a property that describes an entity, and can be simple vs composite.
• Attributes include single-valued vs multivalued and stored vs derived types.
• Simple/Atomic attributes are not divisible.
• Composite attributes are divided into smaller subparts, forming a hierarchy.
• Single-valued attributes have one value for an entity.
• Multivalued attributes have multiple values.
• Stored attributes are stored in databases.
• Derived attributes are derived from other attributes or entities.
• Complex attributes are nested composite and multivalued attributes.
• A null value indicates an unknown or missing attribute value.
• Entity type defines a collection of entities with the same attributes.
• An entity set/entity collection is the collection of all entities.

Keys in ER Diagrams

• Key attributes have distinct values for each entity (uniquely).
• Composite keys use several attributes together to form a key, which must be minimal.
• Weak entity types lack keys and relate to specific identifying entities with total participation constraints.
• Attribute domain indicates a set of values valid for an specific attribute.
• ER diagrams represent relationship types as diamond-shaped boxes.
• Recursive relationships/self-referencing relationships relate the same entity type in distinct roles.
• Structural constraints include cardinality ratio and participation constraints, indicating relationships.

Cardinality Ratio

• Cardinality ratio for a binary relationship specifies the maximum number of relationship instances and entity can participate in.
• Cardinality ratio relationships are one-to-one (1:1).
• Cardinality ratio relationships are one-to-many (1:N) or Many-to-one (N:1).
• Cardinality ratio relationships are many-to-many (M:N).
• Participation constraints specify the minimum number of relationship instances each entity.
• Total participation of the entity is displayed as a double line
• Partial participation of the entity is represented by a single line.

Primary Keys

• A primary key is a unique identifier for an entity and cannot be null.
• An entity can contain a composite primary key.
• Degree of relationship: number of entities that participate in this relationship.
• Degree of relationship can be recursive, binary, or ternary.
• Cardinality Ratio: maximum number of relationships.
• Cardinality Ratio: Relationships can exist between entities as one to many, one to one, or many to many.
• Participation: the minimum number of relationships that can relate entities with each other.
• Participation is optional (partial participation) and mandatory (total participation).

Associative Entity

• All relations are many to many with associative entities.
• Associative Entities should have attributes.
• Associative Entities are not necessary to have unique identifiers.
• Preferred is to make arrows beside associative entities.

Supertype and Subtype

• Subtype entities inherit values of all attributes of the supertype.
• An instance of a subtype is also an instance of the supertype.
• Relationships at the supertype level indicate that all subtypes will participate in the relationship.

Generalization and Specialization

• Generalization is a bottom-up approach, combining specific entities into a more abstract supertype such as car and truck combining to vehicle.
• Specialization is a top-down approach, dividing a general entity type into subtypes, such as person dividing into employee, volunteer, or donor.

Constraints in Supertype

• Disjoint Rule: An instance of the supertype can be only ONE of the subtypes
• Overlap Rule: An instance of the supertype could be more than one of the subtypes.

Completeness Constraint

• Total Specialization Rule: Yes (double line)
• Partial Specialization Rule: No (single line).

Constraints in Supertype/Subtype

• Discriminators are attributes of the supertype whose values determine the target subtype(s).
• Disjoint Attributes are simplewith a alternative value to indicate the possible subtypes.
• Overlapping Attributes are composite attributes which subparts pertain to different subtypes.
• Each subpart contains a boolean value to indicate whether or not the instance belongs to the associated subtype.