1/30
Comprehensive practice flashcards for CSM297 covering Introduction to Databases, Characteristics of the Database Approach, Architecture, Schemas, Transactions, and Conceptual ER Data Modelling.
According to the lecture notes, what is the distinction between data and information?
Data consists of recorded facts, values, observations, or symbols (such as scores: 65, 80, 75, 90), whereas information is data that has been processed, organised, or interpreted to convey meaning (such as a calculated class average of 77.5).
What is the definition of a database?
A database is an organised collection of logically related data that is stored and managed according to structure and rules so it can be accessed, retrieved, updated, and maintained in a consistent way, rather than an arbitrary pile of files.
What is a Database Management System (DBMS), and what are five examples named in the notes?
A DBMS is software used to define, create, store, retrieve, update, and control access to a database. Examples include MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite.
What are typical functions performed by a DBMS?
Defining database structures, data types, and constraints; inserting, retrieving, updating, and deleting records; managing storage and access paths; controlling user permissions and access; enforcing integrity rules; coordinating concurrent multi-user access; and providing backup and recovery facilities.
How does a database differ from a database system?
A database is the organised collection of stored data itself, whereas a database system encompasses the complete environment: the database, the DBMS software, supporting hardware, applications (such as a registration portal), and users.
What is metadata, and how does the STUDENT table illustrate it?
Metadata is 'data about data' that describes the structure, meaning, properties, or organisation of data. In the STUDENT table, stating that StudentID is an integer identifier that must be unique is metadata, whereas the actual value 1001 is data.
What is the DBMS catalog (or data dictionary)?
A collection of metadata maintained by the DBMS that describes database objects and properties (tables, columns, views, indexes, constraints, users, and privileges), allowing the DBMS to validate requests and administrators to manage objects.
What are the primary problems associated with traditional file processing?
Data redundancy, data inconsistency, data isolation, difficult data access, integrity problems, security problems, concurrent-access problems, and backup and recovery difficulties.
What makes a database system 'self-describing'?
It stores not only the operational data itself but also a complete definition of its structure, relationships, and constraints in the DBMS catalog (metadata).
What is program-data independence?
The ability to change aspects of data definition or organisation (such as adding a column) without requiring changes to every application program that accesses the data.
What is data abstraction in the context of database systems?
The suppression or hiding of low-level implementation and physical storage details (such as disk blocks and indexes) from users, presenting them instead with a conceptual or view-level representation of the data.
What is a database view, and what is its purpose?
A view is a specific representation of data designed for a particular user, role, or purpose (e.g., students seeing only their own grades), making the system easier to use and helping restrict access to unnecessary data.
What is a database transaction?
A logical unit of database work consisting of one or more operations (e.g., transferring GHS 100 by debiting one account and crediting another) that must be executed as a single, indivisible unit.
What do the four ACID properties stand for, and what does each mean?
Atomicity (the transaction is all-or-nothing), Consistency (a successful transaction preserves all integrity rules), Isolation (concurrent transactions execute without prohibited interference), and Durability (committed changes persist despite system failures).
What are four common concurrency problems that can arise without proper DBMS concurrency control?
Lost update (one transaction overwrites another's change), dirty read (reading uncommitted changes), non-repeatable read (rereading a row and finding it modified by another committed transaction), and inconsistent analysis (reading mixed or inconsistent data during another transaction's update).
Who introduced the relational model, and in what year?
Edgar F. Codd introduced the relational model in 1970.
How do hierarchical and network database models organise data compared to the relational model?
Hierarchical databases organise records in parent-child tree structures, network databases link records using navigational graph relationships, and the relational model represents data as relations (tables).
What is a data model?
A collection of concepts used to describe the structure, relationships, meanings, and constraints of a database to help translate real-world requirements into an implementable design.
What are the three categories of data models described in Week 3?
Conceptual data models (high-level, user-oriented concepts like entities and relationships), representational/logical data models (structures DBMSs implement, like relational tables), and physical data models (internal storage organisation, indexes, and access paths).
What is the difference between a database schema and a database instance (or database state)?
A database schema is the defined structure and rules (the blueprint, changing infrequently), whereas a database instance or state is the actual data stored in the database at a specific moment in time.
What defines a 'valid database state'?
A state that satisfies all defined database constraints and integrity rules (for example, ensuring no two student records share the same unique StudentID).
What are the three levels of the three-schema architecture?
External schema (view level for individual users/groups), conceptual schema (logical level describing the organisation's overall entities, relationships, and constraints), and internal schema (physical level describing physical storage structures, indexes, and access paths).
What is the difference between physical data independence and logical data independence?
Physical data independence is the ability to change the internal schema (e.g., adding an index) without altering the conceptual schema, whereas logical data independence is the ability to change the conceptual schema (e.g., adding a new entity) without altering external schemas or application programs.
What are advantages and disadvantages of a centralised DBMS architecture?
Advantages include coordinated administration, shared records, and centralised control. Disadvantages include central-system bottlenecks, complete dependence on network connectivity, and service disruption if the central server fails.
How does a two-tier client/server architecture operate, and what is a key disadvantage?
The client communicates directly with the database server, running the user interface and some application logic. Disadvantages include client maintenance overhead, increased security risks from direct database exposure, and heavy server load with many clients.
What are the roles of the three tiers in a three-tier architecture?
Presentation tier (handles user interface and user interaction), application tier (executes business rules, validation, and processing), and data tier (manages DBMS storage and database operations).
What characterises an n-tier architecture?
It expands beyond three tiers into multiple specialised services (such as separating presentation, authentication, API processing, payment services, reporting, and database services), enabling separation of concerns and independent scaling at the cost of higher operational complexity.
Can centralised architecture and client/server architecture be combined?
Yes, they are not mutually exclusive; a centrally managed database can be accessed across a network via a client/server application architecture.
Why is data modelling performed prior to database implementation?
It clarifies requirements, makes business rules and relationships explicit, serves as a blueprint for construction, and identifies missing or ambiguous requirements early.
What are the three foundational concepts of the Entity-Relationship (ER) model?
Entity, attribute, and relationship.
In the ER model, what is an entity?
A distinguishable real-world object, person, place, event, or concept about which data is stored.