What is database structure?

A database structure is the blueprint that defines how data is arranged ,organized, stored, accessed, and managed within a database. It’s the underlying framework that ensures efficient data handling, minimizes redundancy, and promotes data integrity. Within a database, data are grouped into tables, each of which consists of rows and columns, like in a spreadsheet.

The structure of a database consists of a set of key components:

• Tables: The core building blocks, each representing a distinct entity or category of data (e.g., customers, products, orders). A table contains all the fields, attributes and records for a type of entity. A database will most probably contain more than one table.
• Columns: Attributes or characteristics within a table, holding specific data types like text, numbers, dates, or even images (e.g., customer_id, product_name, order_date). Column headings are known as fields. Each field contains a different attribute. For every table, a unit of data is entered into each field. It’s also known as a column value. Each column has a data type. For example, the “agent_name” column has a data type of text, and the “commission” column has a numeric data type.
• Data Types: Data classifications indicating the format and values allowed in a column (e.g., int, varchar, date, blob).
• Constraints: Rules that govern data integrity and consistency, commonly including:
  • Primary Key: A unique identifier for each table row, ensuring no duplicates (e.g., customer_id).
  • Foreign Key: A column referencing a primary key in another table, establishing relationships (e.g., order_id referencing customer_id in the Orders table).
  • NOT NULL: Enforces a value in a column (e.g., customer_name cannot be null).
  • UNIQUE: Prevents duplicate values (except for primary keys) within a column.
• Relationships: Connections between tables, often modeled using foreign keys. They define how entities are linked (e.g., an Order belongs to a specific Customer).

This image shows the basic structural elements of a database table.

Data types are also a way of classifying data values or column values. Different kinds of data values or column values require different amounts of memory to store them. Different operations can be performed on those column values based on their datatypes.

Some common data types used in databases are:

• Numeric data types such as INT, TINYINT, BIGINT, FLOAT and REAL. 
• Date and time data types such as DATE, TIME and DATETIME. 
• Character and string data types such as CHAR and VARCHAR.
• Binary data types such as BINARY and VARBINARY. 
• And miscellaneous data types such as:
  • Character Large Object (CLOB), for storing a large block of text in some form of text encoding.  
  • and Binary Large Object (BLOB), for storing a collection of binary data such as images. 

Logical database structure

The logical database structure refers to how data is organized and represented within a database system at a conceptual level. It focuses on the logical relationships between data elements, without concern for the physical implementation details such as storage mechanisms or indexing strategies. The logical structure defines the database schema, which outlines the structure of the database and the relationships between its components.

Key components of the logical database structure include:

1. Entities and Attributes:
   • Entities represent the main data objects or concepts in the database, such as customers, orders, products, etc.
   • Attributes define the characteristics or properties of entities. Each attribute represents a specific piece of information about an entity.
   • Entities and attributes are defined in the database schema using entity-relationship diagrams (ERDs) or similar modeling techniques.
2. Relationships:
   • Relationships define how entities are related to each other within the database.
   • Relationships are represented by lines connecting entities in ERDs, with labels indicating the nature of the relationship (e.g., one-to-many, many-to-many).
   • Relationships enforce data integrity and define the rules for data manipulation and navigation within the database.
3. Keys:
   • Keys are used to uniquely identify instances of entities within the database.
   • Primary keys uniquely identify each record or row in a table and serve as the main identifier for the entity.
   • Foreign keys establish relationships between tables by referencing the primary key of another table.
4. Constraints:
   • Constraints define rules and conditions that data must adhere to within the database.
   • Common constraints include primary key constraints (ensuring uniqueness), foreign key constraints (enforcing referential integrity), and check constraints (validating data values).
5. Normalization:
   • Normalization is the process of organizing data in a database to minimize redundancy and dependency.
   • It involves decomposing larger tables into smaller, related tables to reduce data duplication and improve data integrity.
6. Views:
   • Views are virtual tables that are based on the results of SQL queries.
   • They provide a way to present data from one or more tables in a customized format without altering the underlying data.

Overall, the logical database structure provides a conceptual framework for understanding the organization and relationships of data within the database. It serves as the foundation for designing and implementing the physical database structure, which determines how data is stored and accessed on disk.

Managing MySQL databases involves creating, maintaining, and accessing databases, as well as managing user permissions with grants. Below is a comprehensive guide on how to perform these tasks.

Creating a MySQL Database

Step 1: Access MySQL

You can access MySQL through the command line, MySQL Workbench, or any other MySQL client. Here, we’ll use the command line.

mysql -u root -p

You will be prompted to enter your MySQL root password.

Step 2: Create a Database

CREATE DATABASE mydatabase;

Maintaining a MySQL Database

Viewing Databases

SHOW DATABASES;

Using a Database

USE mydatabase;

Creating Tables

CREATE TABLE mytable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

Inserting Data

INSERT INTO mytable (name, age) VALUES ('John Doe', 30);

Viewing Tables

SHOW TABLES;

Selecting Data

SELECT * FROM mytable;

Accessing MySQL Database

You can access the MySQL database from various programming languages (e.g., Python, PHP, Java) using their respective MySQL connectors. Here’s an example in Python using mysql-connector-python:

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="mydatabase"
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM mytable")

for row in cursor.fetchall():
    print(row)

conn.close()

Understanding Schema and Tablespaces

Schema

A schema is a logical container for database objects such as tables, views, indexes, stored procedures, and functions. It helps in organizing and managing these objects in a database.

• MySQL: In MySQL, the terms “database” and “schema” are used interchangeably.
• Oracle: In Oracle, a schema is a collection of database objects associated with a particular user. Each user owns a single schema.
• SQL Server: In SQL Server, a schema is a distinct namespace to hold database objects, which can be owned by multiple users.

Tablespace

A tablespace is a storage location where the actual data for database objects is stored. Tablespaces help in managing the physical storage of data.

• MySQL: Uses a default tablespace for InnoDB tables and supports custom tablespaces.
• Oracle: Strongly relies on tablespaces for managing data storage.
• SQL Server: Uses files and filegroups to manage storage, similar to tablespaces.

MySQL

Creating a Schema

CREATE DATABASE mydatabase;

Using a Schema

USE mydatabase;

Creating a Custom Tablespace

CREATE TABLESPACE mytablespace
ADD DATAFILE 'mytablespace.ibd'
ENGINE=InnoDB;

Viewing Tablespaces

To view tablespaces in MySQL, you can query the information_schema:

SELECT * FROM information_schema.FILES WHERE TABLESPACE_NAME IS NOT NULL;

Oracle SQL

Creating a Schema

A schema in Oracle is automatically created when you create a user.

CREATE USER myuser IDENTIFIED BY mypassword;

Granting Privileges to the User

GRANT CONNECT, RESOURCE TO myuser;

Creating a Tablespace

CREATE TABLESPACE mytablespace
DATAFILE 'mytablespace.dbf' SIZE 50M
AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

Viewing Tablespaces

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

Creating a Table in a Specific Tablespace

CREATE TABLE mytable (
    id NUMBER,
    name VARCHAR2(50)
) TABLESPACE mytablespace;

SQL Server

Creating a Schema

CREATE SCHEMA myschema;

Creating a Table in a Specific Schema

CREATE TABLE myschema.mytable (
    id INT,
    name NVARCHAR(50)
);

Creating a Tablespace Equivalent (Filegroup)

ALTER DATABASE mydatabase
ADD FILEGROUP myfilegroup;

Adding a Data File to the Filegroup

ALTER DATABASE mydatabase
ADD FILE (
    NAME = myfile,
    FILENAME = 'C:\mydatabase\myfile.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
) TO FILEGROUP myfilegroup;

Viewing Filegroups

SELECT * FROM sys.filegroups;

Summary

• Schema: Logical container for database objects.
• Tablespace: Physical storage location for data.
• MySQL: Schemas and databases are the same, uses default and custom tablespaces.
• Oracle: Schemas are user-specific, strong reliance on tablespaces.
• SQL Server: Uses schemas for object management and filegroups for storage management.

Practical Examples

MySQL

Create Schema (Database)

CREATE DATABASE testdb; USE testdb;

Create Custom Tablespace

CREATE TABLESPACE testspace ADD DATAFILE 'testspace.ibd' ENGINE=InnoDB;

View Tablespaces

SELECT * FROM information_schema.FILES WHERE TABLESPACE_NAME IS NOT NULL;

Oracle

Create User (Schema)

CREATE USER testuser IDENTIFIED BY password; GRANT CONNECT, RESOURCE TO testuser;

Create Tablespace

CREATE TABLESPACE testspace DATAFILE 'testspace.dbf' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

View Tablespaces

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

SQL Server

1. Create Schema CREATE SCHEMA testschema;
2. Create Filegroup ALTER DATABASE testdb ADD FILEGROUP testgroup;
3. Add Data File ALTER DATABASE testdb ADD FILE ( NAME = testfile, FILENAME = 'C:\testdb\testfile.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP testgroup;
4. View Filegroups SELECT * FROM sys.filegroups;

These commands provide a foundational understanding of managing schemas and tablespaces in MySQL, Oracle, and SQL Server.

Managing Grants in MySQL

Grants in MySQL are used to control access to databases, schemas, tables, and other database objects.

Granting Privileges

Database Level

Grant all privileges on a database:

GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'host';

Grant specific privileges on a database:

GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'username'@'host';

Schema Level (MySQL does not have explicit schema-level privileges)

Since MySQL uses databases to organize schemas, you grant privileges at the database level.

Table Level

Grant all privileges on a table:

GRANT ALL PRIVILEGES ON mydatabase.mytable TO 'username'@'host';

Grant specific privileges on a table:

GRANT SELECT, INSERT ON mydatabase.mytable TO 'username'@'host';

View Level

Grant all privileges on a view:

GRANT ALL PRIVILEGES ON mydatabase.myview TO 'username'@'host';

Grant specific privileges on a view:

GRANT SELECT ON mydatabase.myview TO 'username'@'host';

Revoking Privileges

Revoke all privileges on a database:

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'username'@'host';

Revoke specific privileges on a table:

REVOKE SELECT, INSERT ON mydatabase.mytable FROM 'username'@'host';

Showing Grants

Show all grants for a user:

SHOW GRANTS FOR 'username'@'host';

Best Practices for Maintaining MySQL Databases

1. Regular Backups: Use mysqldump or other backup tools to regularly back up your databases.
2. Monitoring: Use tools like MySQL Enterprise Monitor or open-source solutions to monitor database performance.
3. Indexes: Optimize your queries by using indexes appropriately.
4. User Management: Regularly review and update user privileges to ensure least privilege access.
5. Maintenance: Regularly perform database maintenance tasks like optimizing tables and checking for integrity.

Summary

• Creating Databases: Use CREATE DATABASE to create a new database.
• Maintaining Databases: Use SQL commands to create tables, insert data, and manage the database.
• Accessing Databases: Use MySQL connectors in various programming languages to access and interact with the database.
• Managing Grants: Use GRANT, REVOKE, and SHOW GRANTS to manage user permissions at the database, table, and view levels.
• Best Practices: Regular backups, monitoring, proper indexing, user management, and regular maintenance are key to maintaining a healthy database.

Database Entities and Attributes

Understanding entities and attributes is fundamental in database design. They are the building blocks of a database schema and help in structuring data logically and efficiently.

Entities

An entity in a database is any object or concept that can have data stored about it. Entities are typically mapped to tables in a relational database.

• Examples of Entities:
  • Person: Represented by a Person table.
  • Product: Represented by a Product table.
  • Order: Represented by an Order table.

Attributes

Attributes are the properties or characteristics of an entity. In a relational database, attributes are represented by columns in a table.

• Examples of Attributes:
  • For a Person entity: PersonID, FirstName, LastName, DateOfBirth.
  • For a Product entity: ProductID, ProductName, Price, Category.
  • For an Order entity: OrderID, OrderDate, CustomerID, TotalAmount.

Example: E-Commerce Database

Let’s design a simple e-commerce database schema to illustrate entities and attributes.

Entities and Their Attributes

Customer Entity

Entities and Their Attributes

Customer Entity

• Attributes:
  • CustomerID: Unique identifier for the customer.FirstName: First name of the customer.LastName: Last name of the customer.Email: Email address of the customer.Phone: Phone number of the customer.

CREATE TABLE Customer ( CustomerID INT PRIMARY KEY AUTO_INCREMENT, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(100), Phone VARCHAR(15) );

Product Entity
Attributes:
ProductID: Unique identifier for the product.ProductName: Name of the product.Price: Price of the product.Category: Category to which the product belongs.StockQuantity: Number of items in stock.
CREATE TABLE Product ( ProductID INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR(100), Price DECIMAL(10, 2), Category VARCHAR(50), StockQuantity INT );

Order Entity
Attributes:
OrderID: Unique identifier for the order.OrderDate: Date when the order was placed.CustomerID: Identifier of the customer who placed the order.TotalAmount: Total amount of the order.
CREATE TABLE `Order` ( OrderID INT PRIMARY KEY AUTO_INCREMENT, OrderDate DATE, CustomerID INT, TotalAmount DECIMAL(10, 2), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) );

OrderItem Entity
Attributes:
OrderItemID: Unique identifier for the order item.OrderID: Identifier of the order.ProductID: Identifier of the product.Quantity: Quantity of the product in the order.Price: Price of the product in the order.
CREATE TABLE OrderItem ( OrderItemID INT PRIMARY KEY AUTO_INCREMENT, OrderID INT, ProductID INT, Quantity INT, Price DECIMAL(10, 2), FOREIGN KEY (OrderID) REFERENCES `Order`(OrderID), FOREIGN KEY (ProductID) REFERENCES Product(ProductID) );

Relationships Between Entities

• Customer and Order: One-to-Many relationship (One customer can place multiple orders).
• Order and OrderItem: One-to-Many relationship (One order can contain multiple order items).
• Product and OrderItem: Many-to-Many relationship (A product can be part of multiple order items and an order item can include multiple products).

Normalization

Normalization is the process of organizing data to minimize redundancy and improve data integrity. The above schema demonstrates normalization:

• Each table represents one entity.
• Relationships are established using foreign keys.
• Data redundancy is minimized.

Example Queries

Insert Data

INSERT INTO Customer (FirstName, LastName, Email, Phone) VALUES ('John', 'Doe', 'john.doe@example.com', '123-456-7890'); INSERT INTO Product (ProductName, Price, Category, StockQuantity) VALUES ('Laptop', 999.99, 'Electronics', 50); INSERT INTO `Order` (OrderDate, CustomerID, TotalAmount) VALUES ('2024-07-12', 1, 999.99); INSERT INTO OrderItem (OrderID, ProductID, Quantity, Price) VALUES (1, 1, 1, 999.99);

Retrieve Data

• Get all orders for a customer

SELECT * FROM `Order` WHERE CustomerID = 1;

• Get all products in an order

SELECT Product.ProductName, OrderItem.Quantity, OrderItem.Price FROM OrderItem JOIN Product ON OrderItem.ProductID = Product.ProductID WHERE OrderItem.OrderID = 1;

Update Data

• Update the stock quantity of a product

UPDATE Product SET StockQuantity = StockQuantity - 1 WHERE ProductID = 1;

Delete Data

• Delete a customer

DELETE FROM Customer WHERE CustomerID = 1;

• Delete an order

DELETE FROM `Order` WHERE OrderID = 1;

Summary

• Entities: Represent objects or concepts, mapped to tables.
• Attributes: Properties of entities, represented as columns.
• Relationships: Define how entities are related, enforced by foreign keys.
• Normalization: Organizing data to reduce redundancy and improve integrity.