Lesson 3 - Object Model PostgreSQL

19/11/2023 - 7 phút

#Database #PostgreSQL #RDBMS Concepts #Object Model #ORDBMS #Object Model PostgreSQL #PostgreSQL DBA

What is an Object Model?

PostgreSQL is an object-relational database management system (ORDBMS). This means it combines features of both relational databases (RDBMS) and object-oriented databases (OODBMS). The object model in PostgreSQL provides features such as user-defined data types, inheritance, and polymorphism, enhancing its capabilities beyond a typical SQL-based RDBMS.

Database

A database is a collection of organized data that is stored and managed to provide information or support specific business activities. Databases are commonly used to store, retrieve, and manage data efficiently.

Creating a Database

To create a database, you can use the SQL CREATE DATABASE command or take advantage of PostgreSQL utilities like createdb. Here is an example of the SQL CREATE DATABASE command:

CREATE DATABASE HUMANRESOURCESMANAGEMENT;

Database Management

PostgreSQL provides several SQL commands and utilities for database management, including:

Listing databases: Use the \l command in the psql command-line interface, or execute the SQL statement SELECT name FROM pg_database;.
Switching databases: Use the \connect or \c command, followed by the database name in the psql command-line interface.
Renaming a database: Use the SQL statement ALTER DATABASE old_name RENAME TO new_name;.
Deleting a database: Use the SQL statement DROP DATABASE database_name; or the dropdb utility. Be careful when deleting a database, as it will permanently delete all of its data and objects.

Database Attributes

Each PostgreSQL database has several attributes that you can configure to adjust its behavior and performance, such as:

Encoding: Determines the character encoding used in the database. By default, PostgreSQL uses encoding similar to the server operating system (for example, UTF-8 on most Unix-based systems).
Collation: Determines the string sorting rules in the database. By default, PostgreSQL uses the default sorting rules of the server operating system.
Tablespaces: Controls where database files are stored on the file system. By default, PostgreSQL uses the server default tablespace. You can create additional tablespaces to store data on different disks or file systems, to improve performance or for backup purposes.

GitHub Copilot: Here is the English translation of your selected text:

Table

In PostgreSQL, “Tables” are the basic data structure used to store and manage data. A table in PostgreSQL is a collection of rows and columns, where rows represent data records, and each column contains data for a specific field.

Example :

Creating a Table

CREATE TABLE employees (
   id SERIAL PRIMARY KEY,
   name VARCHAR(100),
   position VARCHAR(50),
   salary DECIMAL(10, 2),
   hire_date DATE
);

In this example, an employees table is created with the columns id, name, position, salary, and hire_date.

Adding data to the table

INSERT INTO employees (name, position, salary, hire_date)
VALUES ('John Doe', 'Software Engineer', 75000.00, '2021-01-15');

This code adds a row to the employees table.

Querying data from the table

SELECT * FROM employees;

This code queries all rows from the employees table.

User-Defined Data Types

User-Defined Data Types in databases are a feature that allows users to create custom data types based on their specific needs. Instead of using standard data types available in the database management system (DBMS), users can define distinct and more complex data types to store data in the way they want.

Applications of User-Defined Data Types include:

Creating data types that align with the data model of a specific application.
Reducing dependence on standard data types and creating custom data types that help manage data more effectively.
Increasing the clarity and self-description of the database by using meaningful custom data type names in the data model.

For example, suppose you are developing a library management application and want to store information about books in the database. Instead of using standard data types like string or integer for book information fields, you could create a custom data type Book that includes the following fields:

Title: String data type.
Author: String data type.
PublicationYear: Integer data type.
ISBN: String data type.
Genre: String data type.

You can define the Book data type in PostgreSQL as follows:

CREATE TYPE Book AS (
    Title       text,
    Author      text,
    PublicationYear integer,
    ISBN        text,
    Genre       text
);

Then, when you create a table to store book information, you can use the Book data type for a specific field:

CREATE TABLE Library (
    BookID serial PRIMARY KEY,
    BookInfo Book
);

When adding data to the Library table, you can use the Book data type to represent book information naturally and clearly:

INSERT INTO Library (BookInfo)
VALUES (
    ('The Great Gatsby', 'F. Scott Fitzgerald', 1925, '978-0-7432-7356-5', 'Novel')
);

Using User-Defined Data Types makes your database easier to read and manage, while providing consistency in your data model.

Inheritance

In PostgreSQL, Inheritance is a feature that allows you to create a new data structure by inheriting from an existing table or data type. This feature allows you to create a linkage between tables or data types, enabling shared common information and optimizing data structure.

For example, consider a situation where you have a project management application and you want to track information about different types of jobs such as Development Jobs, Testing Jobs, and Deployment Jobs. Each of these job types has some unique information, but they also share some common information, such as Job Title, Job Description, Assignee, and Deadline.

You can use the inheritance feature to create a base data type called Job and then create child data types for each job type:

-- Create the base data type ***Job***
CREATE TABLE Job (
    JobID serial PRIMARY KEY,
    Title text,
    Description text,
    Assignee text,
    Deadline date
);

-- Create the child data type ***DevelopmentJob*** inheriting from ***Job***
CREATE TABLE DevelopmentJob (
    TechStack text,
    EstimatedHours integer
) INHERITS (Job);

-- Create the child data type ***TestingJob*** inheriting from ***Job***
CREATE TABLE TestingJob (
    TestCases text[],
    TestEnvironment text
) INHERITS (Job);

-- Create the child data type ***DeploymentJob*** inheriting from ***Job***
CREATE TABLE DeploymentJob (
    TargetServer text,
    DeploymentScript text
) INHERITS (Job);

When you use this inheritance feature, you can add data for each job type independently and query information about all jobs as the base Job. Any changes you make in the base Job data type will also affect all child data types.

For example, you can add a development job as follows:

INSERT INTO DevelopmentJob (Title, Description, Assignee, Deadline, TechStack, EstimatedHours)
VALUES ('Develop Feature A', 'Develop Feature A for the application', 'John Doe', '2023-12-31', 'React', 40);

This will create a new record in both the Job and DevelopmentJob tables simultaneously.

Polymorphism

Polymorphism is the ability of a data object or function to perform different actions based on the type of object or input parameters. This means you can use the same method or function to manipulate different objects naturally, regardless of the object type.

For example, consider a situation where you have a database storing information about different types of animals, including Dogs and Cats. Each type of animal has some unique attributes such as Name, Age, and Sound. You want to perform a function to emit the sound of the animal without needing to know specifically whether it’s a Dog or a Cat.

In SQL language, you can use polymorphism to accomplish this. Below is an example of how you can implement polymorphism in PostgreSQL:

-- Create a table to store animal information
CREATE TABLE Animals (
    AnimalID serial PRIMARY KEY,
    Name text,
    Age integer,
    Sound text
);

-- Create two records for Dog and Cat
INSERT INTO Animals (Name, Age, Sound)
VALUES ('Fido', 3, 'Woof');

INSERT INTO Animals (Name, Age, Sound)
VALUES ('Whiskers', 2, 'Meow');

-- Create a polymorphic function to emit the sound of the animal
CREATE OR REPLACE FUNCTION MakeSound(animal Animals) RETURNS text AS $$
BEGIN
    RETURN animal.Sound;
END;
$$ LANGUAGE plpgsql;

In this example, we created a table Animals to store animal information and added two records for Dog and Cat. Then, we created a function MakeSound that can perform polymorphism on the Animals object. This function returns the sound of the animal, and it can be used with any Animals object without needing to know the specific type of that animal.

Example of using the MakeSound function with both Dog and Cat animals:

SELECT Name, Age, MakeSound(Animals) AS AnimalSound FROM Animals;

The result will return a list of animals with their names, ages, and sounds, and the MakeSound function has been applied polymorphically to return the exact sound for each animal without needing to know in advance the type of that animal.