DBMS Quiz: Data Management Principles

INFS 329: Database Management

• Course covers database management topics, including an overview, benefits, and challenges.
• Overview focuses on database concepts, terminologies, applications, and limitations.
• Students will be able to understand database concepts and the historical development of database systems.
• Students will be able to discuss the benefits and challenges of database management systems for organizations and individuals.

Database Terminologies

• Data: Raw, unprocessed facts stored in a database.
• Metadata: Data about data, including field descriptions, file types, structure, and access rules.
• Information: Processed data that makes sense, presented as query results or reports.
• Null: A value representing missing or unknown data.
• Entity: A thing, person, place, or concept in a database.
• Attribute (Field): Characteristics of an entity.
• Record (Tuple): A set of attribute values for an entity.
• Key: An attribute or attribute combination that uniquely identifies a record.
  • Primary Key: Uniquely identifies a record in a table.
  • Foreign Key: A field in one table linked to a primary key in another table for relationships.

What is a Database?

• An organized collection of related data.
• A database represents an aspect of the real world, reflecting changes in that aspect.
• Designed for a specific purpose, used by someone.
• Examples include phonebooks, class registers, lists of registered students and items sold in a store.

Databases are Structured

• Data is categorized and structured into tables.

What is a File?

• A collection of records or documents about a single organization, person, or subject.

Query

• Querying a database means retrieving specific data.
• Example query: "Salesperson='Mary' AND Price>100"
• Database examples of items sold in a grocery shop can be cited.

More Terminologies

• Entity: The concept the table describes (e.g., customer, course).
• Attribute (Field): Items of information unique to an entity (e.g., Customer ID, name, address).
• Record (Tuple): A set of values for each characteristic of an entity (e.g., a row in a table).
• Key: Used to define a necessary item.
• Primary Key: Uniquely identifies a record.
• Foreign Key: A primary key from another table that specifies relationships.
• Relationship: Linking two or more tables; describes an association among entities.

Summary of some salient DB terminologies

• Column (attribute): Represents data characteristics (e.g., customer ID, name...).
• Row (tuple): A row in a table holds data for one entity.
• Primary key: A unique identifier for each record.

DB terminologies: Query

• SELECT * FROM customers;

What is a Database Management System (DBMS)?

• Software programs managing a database.
• Stored, retrieve, and otherwise manage databases for applications and users.
• Manages large amounts of data to optimise recording, updating, modifications, and securing data.

Database Management System Diagram

• The diagram shows the relationship between a DBMS, the database, and the users, and how data is stored and accessed.

Why Database (DB) & DBMS?

• Areas of database usage: Banking, airlines, universities, and credit card transactions; and sales.
• Functions of DBMS: data storage and retrieval, managing metadata, limiting data repetition, supporting simultaneous data access, providing transactions, backup, and security features, and enforcing business rules.
• Benefits: minimizing data redundancy, sharing data efficiently, enforcing data consistency and integrity, offering searching, security, privacy, and backup & recovery facilities.
• Drawbacks: complexity, size, performance, and costs of conversion.

Advantages of DBMS

• Minimize data redundancy: Data is stored only once, in a single database, in management systems, thus eliminating redundant data.
• Data consistency: Data remains consistent and is updated to all users immediately when modifying any database values.
• Data integrity: Data integrity means unification of so many files into a single file. In DBMSs data is stored in different tables that are linked to each other. Users can easily input data and associate it. DBMS allows maintaining data integrity to decrease data duplication and inconsistency.
• Search capability: Rapid data retrieval to meet user queries.
• Security: Data protection.
• Privacy: Extent of data access by users is determined by database administrators (DBA).
• Simplicity: Data can be viewed in a clear and simple manner.
• Backup and recovery: Automated backups and recovery of databases if there is a system failure during data processing.
• Development of new applications: Easy to develop new applications based on available data.
• Performance: Improved performance in some situations by accessing data more quickly.
• Maintenance cost: Lower maintenance cost.
• Standards: Enforcement of data standards facilitates data migrations and the interchange of data.
• Less chances of data loss. Data loss is limited due to security constraints imposed on the database.

Disadvantages of DBMS

• Complexity: The complexity of DBMS programs/software is due to the provision of its expected functionalities (this also requires more skilled professionals to maintain and implement).
• Size: The DBMS's extensive functionality and complexity leads to disk space and substantial memory requirements to run efficiently.
• Performance: Some systems may now run slower than previously, but this can be addressed by adjusting the system and DBA expertise.
• Costs of Conversion: Costly to convert existing systems to run on a newer DBMS.
• Additional Hardware costs: The larger storage demands of a DBMS might require the addition of further hardware.

DBMS Terminology Summary

• Entity: A thing (object) about which data is stored.
• Attribute (Field): Description of an entity.
• Key: Unique value to identify a record.
• Primary Key: Identifies a row in a table.
• Foreign Key: A primary key linking one table to another, for relationship.
• Record (Tuple): A row in the table.
• Relations: Tables in a database.

Activity 2.1

• Discuss the challenges and benefits of database management software.
• Identify any three (3) drawbacks of DBMS, discuss how they can be mitigated.

Data Storage & File Structures

• Several types of data storage exist, categorised by speed, cost, and reliability.
• Secondary storage devices (e.g., magnetic tapes, hard disks, optical disks, solid-state drives) are used from storing data permanently, even when power is switched off, unlike main memory.

File Organisation

• File is a collection of data, usually stored on disk (a logical entity).
• File organization is the methodology used to store data.
  • Heap file organization: Simplest, records ordered as they are inserted.
• Hash file organization: Records distributed randomly using a hash function to calculate addresses.
• Indexed sequential access method (ISAM): Sequential storage with extra indexes for quicker access.
• B+ tree organization: Efficient for large datasets with complex retrieval needs; internal nodes used for indexing and leaf nodes for data.
• Clustered tables organization: Tables stored together in a group where common columns share space.

File Access & File Structures

• Sequential access: Reading and writing data in the order it is stored.
• Direct access: Access to data records with specific record numbers.
• Stream access: Accessing data without specific record structures; sequential access to bytes addressed by a positive integer.

File systems

• A method to organize files on physical storage devices.
• Examples include Windows, macOS, Ubuntu, PlayStation, Xbox systems.
• Different file systems have different strengths and capabilities for handling data.

Database Development Process

• Enterprise data modeling (EDM): Creating an overall blueprint of organizational data.
• Systems Development Life Cycle (SDLC): An approach used to develop, maintain, and replace information systems with phases: Planning, Analysis, Design, Implementation, and Maintenance.
• Database Development Lifecycle (DBDLC): A life cycle specific for databases, with six phases: Database initial study, database design, implementation and loading, testing and evaluation, operation, and maintenance and evaluation.
• Prototyping: Creating a working model to gain user feedback and improve system design.

CASE Tools

• Computer-aided software engineering (CASE) Tools: Tools aiding in database design and application programming to enhance productivity.
• Enhance efficiency in designing databases; generate codes.
• Help in analysis, diagramming, and documentation of database models.
• Front-end CASE tools: focus on designing database models; back-end CASE tools help in generating database-related codes.

Data Models

• A representation of data objects, their characteristics, interactions, and types.
• Data models are important for database design and can be classified into various categories to satisfy different needs.
• Hierarchical, Network, Relational, Semantic, and Object-Oriented are the types.

Normalization

• The process of organizing data in a database to avoid redundant data and anomalies.
• Normalization works in a series of stages (normal forms; 1NF, 2NF, 3NF).
• 1NF ensures all columns are atomic (unbreakable into further parts).
• 2NF ensures no non-key attribute depends on only part of the primary key.
• 3NF removes transitive dependencies from non-key attributes.

Denormalization

• The process of adding redundancy to a database schema in order to improve performance (which might be slower when heavily normalized).
• Useful when frequent data retrieval queries are essential.

Deadlock

• A situation in a database where two or more processes are waiting indefinitely for each other to release a resource that they both need.
• Deadlocks can be detected via methods (like a timeout period) and resolved by either preempting or aborting a process.

Activity 8.1

• Explain what a deadlock is in the context of relational database management.
• Provide an example and explanation of a deadlock in a database.
• Explain Coffman's four conditions required to maintain a deadlock prevention strategy.

Test your knowledge on Database Management Systems (DBMS) and understand their advantages, functions, and performance improvements. This quiz covers key concepts including data loss prevention, application development, and file organization.