Chapter 13 - Databases Flashcards
Why we use databases
Databases are a crucial component of modern computing and information management, serving various purposes and functions. Here are several reasons why we use databases:
Data Storage and Organization: Databases provide a structured and efficient way to store and organize vast amounts of data. They allow data to be stored in tables, rows, and columns, making it easy to retrieve and manage information.
Data Retrieval and Querying: Databases enable users to retrieve specific pieces of data or perform complex queries to filter and sort information based on various criteria. This is essential for data analysis, reporting, and decision-making.
Data Integrity and Consistency: Databases enforce rules and constraints to maintain data integrity, ensuring that the data stored remains accurate, consistent, and reliable. This includes enforcing data types, uniqueness constraints, and referential integrity.
Concurrency Control: In multi-user environments, databases manage concurrent access to data, preventing conflicts and ensuring that multiple users can access and modify data simultaneously without causing inconsistencies.
Data Security: Databases provide mechanisms for securing data, including access control, encryption, and auditing. This ensures that sensitive or private data is protected from unauthorized access and maintains compliance with privacy regulations.
Redundancy and Backup: Databases often incorporate features for data redundancy and backup, allowing organizations to recover lost or corrupted data and ensure business continuity in case of system failures or disasters.
Scalability: Databases are designed to scale as data and usage grow. This scalability can be achieved by distributing data across multiple servers (horizontal scaling) or by upgrading hardware (vertical scaling).
Efficient Data Processing: Databases optimize data retrieval and processing, allowing for faster access to information through the use of indexes, caching, and query optimization techniques.
Data Analysis and Reporting: Databases serve as a foundation for data analysis, business intelligence, and reporting tools. Data can be analyzed to derive insights, trends, and patterns that inform decision-making and strategy.
Integration with Applications: Databases provide an interface for applications to interact with stored data. Applications can read, write, and modify data in the database, facilitating seamless integration between software systems.
Data Backup and Recovery: Databases offer mechanisms for creating backups and restoring data to a previous state in case of accidental deletions, data corruption, or system failures.
Data Sharing and Collaboration: Databases allow multiple users and applications to share and collaborate on the same set of data, improving efficiency and collaboration within organizations.
Relational databases
common type of database management system, have distinctive features that contribute to their effectiveness in organizing and managing data. Here are some key features of relational databases:
Tabular Structure:
Data is organized in tables with rows (records) and columns (attributes/fields).
Each column represents a specific attribute or characteristic, and each row represents a unique record.
Data Integrity:
Relational databases enforce data integrity through constraints like primary keys, foreign keys, unique constraints, and check constraints.
Primary keys uniquely identify each record in a table, ensuring no duplicate rows.
Relationships:
Relationships define how data in one table is related to data in another table.
Common types of relationships include one-to-one, one-to-many, and many-to-many relationships.
Normalization:
Relational databases follow the principles of normalization to minimize redundancy and dependency by organizing data into multiple related tables.
This ensures efficient storage and reduces the risk of inconsistent data.
ACID Properties:
Relational databases maintain ACID (Atomicity, Consistency, Isolation, Durability) properties to ensure transactions are processed reliably and consistently.
Atomicity guarantees that each transaction is treated as a single unit, either entirely executed or rolled back in case of failure.
Consistency ensures the database remains in a valid state after each transaction.
Isolation ensures transactions are isolated from each other to prevent interference.
Durability ensures that once a transaction is committed, the changes are permanent and persist even in the event of system failures.
Database Table
In the context of databases, a table is a fundamental structure used to organize and store data in a relational database management system (RDBMS). It’s a two-dimensional representation, resembling a spreadsheet, consisting of rows and columns. Each table organizes related data into rows (also known as records or tuples) and columns (also known as fields or attributes). Here’s a breakdown of the components and aspects related to database tables:
Table Name:
Each table has a unique and descriptive name that reflects the kind of data it holds.
Columns (Fields/Attributes):
Columns represent specific properties or attributes related to the data being stored. Each column has a name and a data type (e.g., string, integer, date) that defines the type of data it can hold.
Examples of columns in a table might include “ID,” “Name,” “Age,” “Address,” etc.
Rows (Records/Tuples):
Rows represent individual data entries or records within the table. Each row contains values for each column, corresponding to a specific instance or record in the dataset.
For instance, a row in a table representing employees might include details such as employee ID, name, age, address, and so on.
Primary Key:
A primary key is a column (or a set of columns) in a table that uniquely identifies each row. It ensures that each record is distinct and facilitates efficient data retrieval.
The values in the primary key column(s) must be unique and cannot be null.
Foreign Key:
A foreign key is a column that establishes a relationship with another table. It refers to the primary key in another table and is used to link related information between tables.
Foreign keys help maintain referential integrity, enforcing relationships between tables.
Data types in a database table
Numeric Data Types:
Integer: Represents whole numbers (e.g., 1, 100, -42).
Decimal/numeric: Represents numbers with decimal points, allowing precise numeric representation (e.g., 3.14, -0.001).
Float/double: Represents floating-point numbers with decimal points (e.g., 3.14, -0.001).
Character Data Types:
Char: Fixed-length string data type (e.g., ‘John’ with padding to the specified length).
Varchar: Variable-length string data type (e.g., ‘Alice’, ‘123 Main St’).
Text: Variable-length string for longer text (e.g., paragraphs, large descriptions).
Date and Time Data Types:
Date: Represents a date without a specific time (e.g., ‘2023-10-17’).
Time: Represents a specific time of day (e.g., ‘14:30:00’).
Datetime/Timestamp: Represents both date and time (e.g., ‘2023-10-17 14:30:00’).
Boolean Data Type:
Boolean: Represents true or false values.
Binary Data Types:
Binary: Stores binary data (e.g., images, files) with a fixed length.
Varbinary: Stores variable-length binary data.
Large Object Data Types:
Blob (Binary Large Object): Stores large binary data (e.g., images, videos).
Clob (Character Large Object): Stores large text data (e.g., documents, text files).
JSON and XML Data Types:
JSON: Stores data in JSON format.
XML: Stores data in XML format.
Unique Identifier Data Type:
UUID (Universally Unique Identifier): Stores a universally unique identifier.
Spatial Data Types:
Geometry, Geography: Stores spatial data such as points, lines, and polygons.
Arrays and Collections (Depending on DBMS):
Some databases support array data types or collection data types to store multiple values in a single column.
Primary Key
A primary key is a unique identifier for a record in a database table. It’s a specific column or a set of columns that uniquely identify each row within a table. The primary key serves several important purposes:
Uniqueness: Each value in the primary key column(s) must be unique within the table. This uniqueness ensures that each record can be distinguished from all others.
Non-null: A primary key value cannot be null, meaning it must have a valid value for every record.
Indexing: Typically, a primary key is automatically indexed by the database management system (DBMS), allowing for quick access to specific rows based on the primary key value.
Relationships: The primary key is used to establish relationships with other tables. In a one-to-many relationship, the primary key of one table becomes a foreign key in another table.
Data Integrity: It enforces data integrity by preventing duplicate records and ensuring that each record can be uniquely identified.
Example:
In a “Customers” table, a column named “CustomerID” might be designated as the primary key. Each customer will have a unique CustomerID assigned to them.
Foreign Key
Foreign Key:
A foreign key is a column or set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables, representing the relationships in a relational database. Key points about foreign keys include:
References Primary Key: A foreign key in one table refers to the primary key in another table. This defines a relationship between the two tables.
Relationships: Foreign keys are used to define relationships between tables. For example, a foreign key in an “Orders” table may refer to the “CustomerID” (primary key) in the “Customers” table, indicating which customer placed the order.
Data Integrity: Foreign keys help enforce referential integrity, ensuring that relationships between tables remain consistent.
Maintaining Relationships: Changes to the primary key in the referenced table (e.g., deleting or updating a customer) will typically have consequences in the tables that reference it (e.g., deleting orders associated with that customer).
One-to-many relationships
A one-to-many relationship is a fundamental concept in relational databases that describes the relationship between two entities or tables, where one entity’s record can be associated with multiple records in another entity. This is a common and powerful way to model relationships between data in a database. Let’s break down the key aspects of a one-to-many relationship:
Entities or Tables:
In a one-to-many relationship, you have two entities or tables involved: a “parent” or “one” entity and a “child” or “many” entity.
Parent Entity (One-side):
The parent entity represents the side of the relationship where each record can be associated with multiple records in the other entity.
A single record in the parent entity can have many related records in the child entity.
Child Entity (Many-side):
The child entity represents the side of the relationship where each record can be associated with at most one record in the parent entity.
Each record in the child entity is linked to a single record in the parent entity.
Relationship Cardinality:
The “one-to-many” relationship is denoted as “1:N” or “1:M,” where “1” signifies the parent entity, and “N” (or “M”) signifies the potential multiple related records in the child entity.
Example:
A common example is the relationship between “Customers” and “Orders.” One customer can place multiple orders (one-to-many), but each order belongs to a single customer.
many-to-many realationships
A many-to-many relationship is a fundamental concept in relational databases that describes the relationship between two entities or tables, where multiple records in one entity can be associated with multiple records in another entity. This type of relationship is common and crucial for modeling complex relationships in a structured manner. Let’s delve into the key aspects of a many-to-many relationship:
Entities or Tables:
A many-to-many relationship involves two entities or tables, typically referred to as the “left” and “right” tables or the “first” and “second” tables.
Relationship Cardinality:
The “many-to-many” relationship is denoted as “M:N,” indicating that multiple records in one entity can be related to multiple records in another entity.
Example:
An example of a many-to-many relationship could be the relationship between “Students” and “Courses.” Each student can enroll in multiple courses, and each course can have multiple enrolled students.
Bridge Table (or Junction Table or Linking Table):
To implement a many-to-many relationship, a third table known as a “bridge table,” “junction table,” or “linking table” is introduced.
The bridge table holds the foreign keys from both entities and establishes the link between them.
Foreign Keys in Bridge Table:
In the bridge table, there are typically two foreign keys: one referencing the primary key of the left table and another referencing the primary key of the right table.
one-to-one relationship
A one-to-one relationship is a fundamental concept in relational databases, describing the relationship between two entities or tables, where each record in one entity is associated with at most one record in another entity. This kind of relationship is less common than one-to-many or many-to-many relationships but is still important in specific scenarios. Let’s explore the key aspects of a one-to-one relationship:
Entities or Tables:
A one-to-one relationship involves two entities or tables, often referred to as the “left” and “right” tables.
Relationship Cardinality:
The “one-to-one” relationship is denoted as “1:1,” indicating that each record in one entity is associated with at most one record in another entity.
Example:
An example of a one-to-one relationship could be the relationship between “Person” and “Passport.” Each person can have at most one passport, and each passport belongs to one person.
Foreign Key Implementation:
Typically, one of the tables will have a foreign key that references the primary key of the other table, establishing the one-to-one relationship.
For instance, in the “Person” and “Passport” example, the “Passport” table might have a foreign key that references the “PersonID” in the “Person” table.
Relationship Rules
In database design, relationships between tables are defined using certain rules or constraints. These rules help maintain the logical and structural integrity of the data. Common relationship rules include:
Cardinality:
Defines the number of occurrences in one entity that can be associated with the number of occurrences in another entity.
Common cardinalities include one-to-one (1:1), one-to-many (1:N), and many-to-many (M:N).
Multiplicity:
Specifies the number of instances or entities that can participate in a relationship.
It’s often described using minimum and maximum values (e.g., 1 to 1, 0 to many).
Optionality:
Determines whether a particular entity is required (mandatory) or optional in the relationship.
It’s expressed as either mandatory (required) or optional.
Degree:
Specifies the number of entities involved in the relationship.
Binary relationships involve two entities, while ternary relationships involve three entities, and so on.
Query for creating database
CREATE DATABASE analysis;
Query for creating table
Here are the primary features and components used in creating a table in SQL:
Table Name: Name given to the table being created.
Columns: These are the fields in the table. For each column, you define:
Column Name: Name of the column.
Data Type: Type of data the column can hold (e.g., INT, VARCHAR, DATE, etc.).
Constraints: Rules applied to the column’s data (e.g., NOT NULL, UNIQUE, DEFAULT value, etc.).
Primary Key: An essential column or a combination of columns that uniquely identify each row in the table. It ensures data integrity and uniqueness.
Foreign Key: A column or a set of columns that establish a link between data in two tables. It maintains referential integrity.
Constraints:
NOT NULL: Specifies that the column must have a value, and NULLs are not allowed.
UNIQUE: Ensures that all values in the column(s) are unique.
DEFAULT: Sets a default value for a column if no value is specified during insertion.
CHECK: Enforces a condition on the data being entered into the column.
INDEX: Improves the speed of data retrieval operations on the table.
UNIQUE INDEX: Similar to INDEX but ensures uniqueness.
Table Constraints:
PRIMARY KEY Constraint: Specifies the primary key for the table.
FOREIGN KEY Constraint: Defines a foreign key relationship between tables.
UNIQUE Constraint: Ensures the values in a column (or a group of columns) are unique.
CHECK Constraint: Imposes a condition on the data being entered into a column.
Here’s a basic example of creating a table in SQL:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50) NOT NULL,
DepartmentID INT,
CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
Inserting Rows into a Table
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (1, ‘John’, ‘Doe’, 101);
Basic select syntax
SELECT * FROM my_table;
