Database Lecture Notes Review

Flashcards covering database concepts, relational database models, data modeling, ER diagrams, normalization, data security, transactions, concurrency, schema levels, integrity, and database administration.

Data

Computers store data, and data can be numeric, text, Boolean, etc., but has no intrinsic meaning to a human.

Information

Data that has been interpreted and put into a context that gives it meaning.

Information System (IS)

Any combination of information technology and people's activities using that technology to support operations, management, and decision-making.

Database

A group of components that interact to produce information, and are a component within an information system.

Need for Databases

The process of saving huge amounts of data and sharing data.

Relational Database

A type of database that stores and provides access to data points that are related to one another, based on the relational model, representing data in tables or relations.

Primary Key

Each row in a table is a record/tuple with a unique ID.

Attributes

The columns/fields of the table that hold attributes of the data.

DataBase Management System (DBMS)

System software for creating and managing databases, providing users and programmers with a systematic way to create, retrieve, update and manage data.

Relational Database Management System (RDBMS)

A database management system (DBMS) that is based on the relational model. The data is stored in tables divided into fields.

Data Dictionary

An inventory of data elements in a database or data model with detailed description of its format, data ownership, relationships, meaning, source, and usage.

Data Definition Language (DDL)

A syntax similar to a computer programming language used to define databases and modify data, allowing to create (CREATE), remove (DELETE or DROP), or modify (UPDATE) tables.

Database Model

A type of data model that determines the logical structure of a database, dictating how data can be stored, organized, and manipulated.

Entity Relationship Diagram (ERD)

Diagram that displays the relationship of entity sets stored in a database, explaining the logical structure of databases based on entities, attributes, and relationships.

Cardinality

Defines the numerical attributes of the relationship between two entities or entity sets, such as one-to-one, one-to-many, many-to-one, and many-to-many.

One-to-One Relationship

When a single instance of an entity is associated with a single instance of another entity.

One-to-Many Relationship

When a single instance of an entity is associated with more than one instance of another entity.

Many-to-One Relationship

When more than one instance of an entity is associated with a single instance of another entity.

Many-to-Many Relationship

When more than one instance of an entity is associated with more than one instance of another entity.

Data Validation

Checking the input data to ensure it conforms with the data requirements of the system to avoid data errors.

Data Verification

Ensuring the user types in what he or she intends, in other words, to make sure the user does not make a mistake when inputting data.

Access Rights

The allowance to the user or process to access the set of objects, controlled by the use of PINs and passwords, granting different types of access like read-only, read, and write.

Audit trails

Records of who accessed data and what changes are made.

Data Locking

Locking protocols used in database management systems as a means of concurrency control.

Encryption

An encryption algorithm transforms data within a database from a readable state into a ciphertext of unreadable characters; usable information can be retrieved with a key generated by the algorithm.

Database Transaction

A logical unit of work that is independently executed for data retrieval or updates and must exhibit atomicity, consistency, isolation, and durability (ACID) properties.

Transaction States

The states through which a transaction goes during its lifetime, indicating the current state of the transaction and how further processing will be done.

Concurrency

Operating or occurring at the same time; running parallel

Atomicity

An Atomicity example of both T1 and T2 being done or neither being done.

Consistency

Ensuring the database is consistent before and after the transaction.

Query Function

A request for data or information from a database table or combination of tables, used for filtering information to display specified data.

Update Function

Functions used to modify the existing records in a table.

Schema

The organization of data as a blueprint of how the database is constructed.

Conceptual Schema

Describes the structure of the whole database, including what data are to be stored and what relationship exists among those data.

Logical Schema

The conceptual schema describes the structure of the whole database where Data are to be stored and the relation that exists within these data

Physical Schema

Has an internal schema which describes the physical storage structure of the database; describes how the database is actually stored in computer memory using low-level data structures in detail.

Table

Equivalent to relation/file.

Record

Equivalent to tuple/row.

Field

Equivalent to attribute/column.

Normalization

Reorganizing data in a database so that there is no redundancy of data and data dependencies are logical.

First Normal Form (1NF)

Each cell (data item) must contain only a single (atomic) value, that cannot be broken down any further; every column (field) must be uniquely named; each row/record is unique and has a primary key.

Second Normal Form (2NF)

Tables must be in 1NF and non-key attributes must depend on every part of the primary key (full functional dependency). Partial dependencies are NOT allowed.

Third Normal Form (3NF)

Tables must be in 2NF and there are no non-key attributes that depend on another non-key attribute (no transitive functional dependencies).

Referential Integrity

Refers to the relationship between tables where a primary key from one table appears in another table as a foreign key.

Database Administrator

Database administrators (DBAs) use specialized software to store and organize data. The role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery.

End-User Interaction with a Database

End-user interaction with a database is pre-defined by their role in the company, based on which they will have a given ‘view’ of the database.

DBMS is a highly complex system with hundreds of transactions being executed every second. The durability and robustness of a DBMS depends on its complex architecture and its underlying hardware and system software.

Crash Recovery

CheckPoint

Checkpoints are used in database systems to ensure consistent and reliable recovery. They involve creating a snapshot of the database at a specific point in time

Page Shadowing

Page shadowing is a technique used to provide redundancy and improve fault tolerance in database systems. It involves maintaining a duplicate copy or shadow of each database page on separate physical storage.

Log Files

Log files are crucial components of database systems that record all modifications made to the database. They serve as a reliable and sequential record of database transactions, including updates, inserts, and deletes.

Integrated DBMS

An IDBMS provides a centralized control mechanism for managing multiple databases, ensuring consistency across different systems and applications.

Stock control

Stock databases are used to track inventory levels, product information, and transaction details.

Police Records

Law enforcement agencies utilize databases to manage and store police records, including criminal histories, incident reports, and arrest records.

Health Records

Electronic Health Record (EHR) systems utilize databases to store and manage patient health information, medical histories, treatments, and test results.

Employee Data

Human Resources departments rely on databases to manage employee data, including personal information, employment history, job roles, salaries, benefits, and performance records

Secure Data Transmission:

Use secure communication protocols, such as HTTPS or VPNs, to encrypt data during transmission

Data Volume

Large database systems handle vast amounts of data, which can present challenges in terms of storage, processing, and retrieval

The Data Protection Act

controls how your personal information is used by organisations, businesses or the government.

The Computer Misuse Act

protect computer users against wilful attacks and theft of information

The Computer Misuse Act

designed to protect computer users against wilful attacks and theft of information

Open to Interrogation

allowing external parties or users to access the database to retrieve specific information or to input specific information.

Data Matching

the process of comparing two pieces of data by directly matching them against each other, to find any data belonging to the same entity, meaning duplication

Data Mining

the process of searching through large data sets to find patterns and relationships in the data.