title | author | date | output |
---|---|---|---|
Postgresql autodiller db |
Sergei Parshin |
January, 29, 2022 |
html_document |
The repository includes a dsigned PostgreSQL database for the Auto diller. It is education project from the Database design course which was started from scratch based on Business requirements.
The project includes:
- Descriptions and diagrams of the design phase in Miro
- ERD (Entity Relationship Diagram) created in pgAdmin
- DDL (Data Definition Language) commands to initialize database itself and related functions, procedures and trigers
- DML (Data Manipulation Language) commands to fill the database and simulate database operations
- Common comands to container operations (based on Makefile) and use database itself (based on functions, views)
Stack: PostgreSQL (DDL, DML, ERD), pgAdmin, docker, docker-compose, bash, makefile, git
Prerequirements: docker
Optional: docker-compose, pgAdmin
Folders:
db
- files to initialize database.IincludesDockerfile
fordocker-compose.yml
andddl
(Data Definition Language) files.dml
- Data Manipulation Language files/commands. Includesinit_insert.sql
to fill database with initial values andsimulate_operations.sql
to simulate manipulation with the database.info
- stores any additional database-related files. E.g. ERD (Entity Relationship Diagram) for the database.
Files:
Makefile
- the file containing shell commands with set of tasks to be executed. The file could be execute as a whole or partly. Read more.docker-compose.yml
- one (out of 2) way to build the project. Apply toDockerfile
indb
. To use docker-compose it is required to install docker and docker-compose.Dockerfile-init
- second (out of 2) way to build the project. It is required to install docker.init_docker_named_volumes.sh
- bash script to create named volumes which going to be used indocker-compose.yml
.
There are two ways to launch the project:
- dockerfile:
- Install docker
- Clone/download the repository
- Open the project with
Makefile
, runmake launch_dockerfile_db
to build the database and rundml
scripts - Connect to
http:\\localhost:5433
orhttp:\\YOUR_SERVER_IP:5433
using pgAdmin, bash or other methods
- docker-compose:
- Install docker
- Install docker-compose
- Clone/download the repository
- Open the project with
Makefile
, runmake launch_docker_compose_db
to build the database and rundml
scripts and mountdocker volumes
- Connect to
http:\\localhost:5433
orhttp:\\YOUR_SERVER_IP:5433
using pgAdmin, bash or other methods
Insert commands (optional, but desired):
- Fill database by default data
make init_insert_db
- Fill operational tables/simulate database operations
make simulate_operations_db
To rebuild containers use make rebuild_dockerfile_db
or make rebuild_docker_compose_db
debenting on the way of project launching in p.3.
- Views
-- VIEW for client service statistics (tables sales.clients, sales.clients_services, sales.services)
SELECT * FROM sales.sales_client_service_statistics_view;
-- VIEW for finance.contracts with employee name and surname, car model and client
SELECT * FROM finance.contracts_view;
-- VIEW for operations.operations whith operation names and employee names
SELECT * FROM operations.operations_view ORDER BY timelog DESC;
-- VIEW for observe car statuses using reviewed_cars and related names
SELECT * FROM sales.reviewed_cars_view ORDER BY car_id DESC;
-- VIEW for cars in test drive
SELECT * FROM sales.view_future_test_drives_view ORDER BY car_id DESC;
- Functions
-- FUNCTION to observe car test drives
SELECT * FROM sales.observe_car_test_drives(
car_search := 3,
time_from := '2021-11-24 15:00:00+00')
-- FUNCTION to observe employee test drives
SELECT * FROM sales.observe_employee_test_drives(
employee_search_name := 'Antoine',
employee_search_surname := 'Davis',
time_from := '2021-11-24 15:00:00+00')
-- FUNCTION to observe employee contracts
SELECT * FROM finance.observe_employee_contracts(
employee_search_name := 'Adler',
employee_search_surname := 'Smith',
time_from := '2021-11-24 15:00:00+00')
-- FUNCTION to observe employee operations
SELECT * FROM sales.observe_employee_operations(
employee_search_name := 'Adler',
employee_search_surname := 'Smith',
time_from := '2021-11-24 15:00:00+00')
ORDER BY timelog;
- Add
restart
option inMakefile
with saved database data/volumes. - Fix timestamps for contracts (see
finance.contracts_view
) and for operations (seeoperations.operations_view
). Now timestaps are filled byNOW()
. - Add descrition for pgAdmin installation.
In terms of any questions please 📧 to the owner Sergei Parshin: