🛢️ NoSQL: Building Databases in Practice

Not Only SQL

About No-SQL Repo

Developed during the 3rd semester of the Data Science and Humanistic Artificial Intelligence undergraduate program at PUC-SP (2025)

Under the guidance of Professor Doctor Daniel Gatti.

➢➢ NoSQL Presentation

Online Tools

➢➣ Oracle SQL LIVE

➣➢ BRM Model Web

➣➢ Redis Database

➣➢ Free Sql Database

➣➢ Kepler Geolocation

➣➢ Mermaid FlowChart and Diagrams

➣➢ Lucidchart - Diagramming Application

➣➢ draw.io - Diagramming

Dowloaded Tools

➣➢ PostgreSQL

➣➢ MongoDB

➣➢ DBeaver - in association with MySQL Workbench, both locally and remotely.

➣➢ MySQL Workbench - both locally and remotely.

Books

➢ Get the Book Database Systems – Navathe, 6th Edition

➣ Get the Book Database System Concepts (5th ed.) - Abraham Silberschatz, Henry F. Korth, S. Sudarshan

📖 Overview

This project explores the fundamentals and practical applications of NoSQL (Not Only SQL) databases, showcasing the construction and management of databases using various database systems, including:

➢ MySQL - Certificate
➢ SQL Server - Certificate
➢ T-SQL - Certificate
➢ Redis MongoDB - Certificate
➢ SQL on Linux - Certificate
➢ Oracle - Certificate

Through this comprehensive guide, you will understand how to define, manipulate, and query data using SQL and NoSQL techniques, alongside practical examples.

Entity Relationship Conceptual Modeling

➢ WorKClass Example

🗄️ SQL Language Breakdown

The SQL language is divided into three main components:

1. Data Definition Language (DDL)
   • Defines database schema and structures.
   • Examples: CREATE TABLE, ALTER TABLE, DROP TABLE.

2. Data Manipulation Language (DML)
   • Manages data within schema objects.
   • Examples: INSERT, UPDATE, DELETE.

3. Data Query Language (DQL)
   • Retrieves data from databases.
   • Example: SELECT.

✍️ Practical Examples

📋 DDL – Data Definition Language

Select Commands using Oracle

select*from HR. COUNTRIES
select COUNTRY_NAME from HR.COUNTRIES 
select COUNTRY_ID from HR.COUNTRIES 
select REGION_ID from HR.COUNTRIES 
select*from hr.REGIONS

SELECT country_id, country_name, region_name
FROM HR.COUNTRIES, HR.REGIONS
Where hr.COUNTRIES.region_id = HR.regions.REGION_ID

Create Table

CREATE TABLE Person (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Email VARCHAR(150) UNIQUE
);

ALTER TABLE

ALTER TABLE Person ADD Telefone VARCHAR(15);

Drop Table

DROP TABLE Person;

🛠️ DML – Data Manipulation Language

Insert Data

INSERT INTO Person (ID, Name, Age, Email)
VALUES (1, 'Maria Silva', 30, 'maria.silva@example.com');

Update Data

UPDATE Person
SET Age = 31
WHERE ID = 1;

🔍 DQL – Data Query Language

Select Data

SELECT Name, Email
FROM Person
WHERE Age > 25;

⚙️ Advanced SQL Concepts

🔑 Primary Key Example

CREATE TABLE Sale (
NumCliente INT NOT NULL IDENTITY(1,1),
CPF INT NOT NULL,
CONSTRAINT pkClient PRIMARY KEY (NumClient))

✅ Check Constraint Example

ALTER TABLE Person
ADD CONSTRAINT ckIdade CHECK (Age <= 100);

🔄 Identity Property (SQL Server)

CREATE TABLE Produtos (
    ProdutoID INT IDENTITY(1,1) PRIMARY KEY,
    NomeProduto VARCHAR(100) NOT NULL
);

🍃 NoSQL – MongoDB Example

📦 Creating a Collection and Inserting Documents

// Inserting a single document
db.usuarios.insertOne({
    name: "João Silva",
    age: 28,
    email: "joao.silva@example.com"
});

// Inserting multiple documents
db.usuarios.insertMany([
    { name: "Ana Souza", age: 24, email: "ana.souza@example.com" },
    { name: "Carlos Lima", age: 35, email: "carlos.lima@example.com" }
]);

🔍 Querying Documents

// Find users older than 25
db.usuarios.find({ age: { $gt: 25 } });

// Find user by email
db.usuarios.findOne({ email: "ana.souza@example.com" });

⚡ Updating and Deleting Documents

// Update user age
db.usuarios.updateOne(
    { name: "João Silva" },
    { $set: { idade: 29 } }
);

// Delete user
db.usuarios.deleteOne({ name: "Carlos Lima" });

📈 Project Stakeholder:

- Briefing:

• The head of architecture of a company needs to manage the creation of projects within the company.
• Your company was asked to create a database to store project data. In this request,
• Your company must deliver a report with the following elements: A list of requirements List of stakeholders Conceptual Model Logical Model Physical Model SQL Code Creation of the template on livesql.oracle.com. Search and define: Stakeholders:

This project provides a complete overview and SQL scripts for managing a project database including projects, activities, stakeholders, and objectives.

1. Requirements

• Store project data: Project name, description, start date, end date, status.
• Track activities: Activity name, description, start date, end date, associated project.
• [Stakeholder management: Stakeholder name, role, associated projects.
• Objectives](): Objectives for each project.
• Schedule tracking: Timelines for projects and activities.

2. Stakeholders

• Head of Architecture: Oversees project creation and management.
• Project Managers: Manage projects and activities.
• Team Members: Execute activities.
• Clients: Provide requirements and feedback.

3. Definitions

• Stakeholders: Individuals or groups involved or affected by the project.
• Project: Temporary endeavor to create a unique product, service, or result.
• Activity: Task(s) performed as part of a project.
• Objective: Specific result a project aims to achieve.
• Schedule: Timeline for completing project activities.

4. Conceptual Model

• Entities: Project, Activity, Stakeholder, Objective.
• Relationships:
  • A project has multiple activities.
  • A project has multiple stakeholders.
  • A project has multiple objectives.

5. Logical Model (Tables)

Table	Columns
Projects	ProjectID, Name, Description, StartDate, EndDate, Status
Activities	ActivityID, Name, Description, StartDate, EndDate, ProjectID
Stakeholders	StakeholderID, Name, Role, ProjectID
Objectives	ObjectiveID, Description, ProjectID

6. Physical Model (SQL Schema)

-- Create Projects table
CREATE TABLE Projects (
ProjectID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Description VARCHAR2(500),
StartDate DATE,
EndDate DATE,
Status VARCHAR2(50)
);

-- Create Activities table
CREATE TABLE Activities (
ActivityID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Description VARCHAR2(500),
StartDate DATE,
EndDate DATE,
ProjectID NUMBER NOT NULL,
CONSTRAINT fk_activities_project FOREIGN KEY (ProjectID)
REFERENCES Projects(ProjectID)
ON DELETE CASCADE
);

-- Create Stakeholders table
CREATE TABLE Stakeholders (
StakeholderID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Role VARCHAR2(50),
ProjectID NUMBER NOT NULL,
CONSTRAINT fk_stakeholders_project FOREIGN KEY (ProjectID)
REFERENCES Projects(ProjectID)
ON DELETE CASCADE
);

-- Create Objectives table
CREATE TABLE Objectives (
ObjectiveID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
Description VARCHAR2(500) NOT NULL,
ProjectID NUMBER NOT NULL,
CONSTRAINT fk_objectives_project FOREIGN KEY (ProjectID)
REFERENCES Projects(ProjectID)
ON DELETE CASCADE
);

7. Example Queries

➢ List all activities with their associated project details

SELECT
a.ActivityID,
a.Name AS ActivityName,
a.Description AS ActivityDescription,
a.StartDate AS ActivityStartDate,
a.EndDate AS ActivityEndDate,
p.Name AS ProjectName,
p.Description AS ProjectDescription
FROM
Activities a
JOIN
Projects p ON a.ProjectID = p.ProjectID;

➢ List all stakeholders and their associated projects

SELECT
s.StakeholderID,
s.Name AS StakeholderName,
s.Role AS StakeholderRole,
p.Name AS ProjectName,
p.Description AS ProjectDescription
FROM
Stakeholders s
JOIN
Projects p ON s.ProjectID = p.ProjectID;

➢ List all objectives with project details

SELECT
o.ObjectiveID,
o.Description AS ObjectiveDescription,
p.Name AS ProjectName,
p.Description AS ProjectDescription
FROM
Objectives o
JOIN
Projects p ON o.ProjectID = p.ProjectID;

➢ List activities, stakeholders, and objectives for a specific project

SELECT
p.ProjectID,
p.Name AS ProjectName,
a.Name AS ActivityName,
s.Name AS StakeholderName,
o.Description AS ObjectiveDescription
FROM
Projects p
LEFT JOIN
Activities a ON p.ProjectID = a.ProjectID
LEFT JOIN
Stakeholders s ON p.ProjectID = s.ProjectID
LEFT JOIN
Objectives o ON p.ProjectID = o.ProjectID
WHERE
p.ProjectID = 1; -- Replace "1" with desired ProjectID

8. Drop Tables (Oracle Live SQL)

To delete tables safely respecting foreign key constraints, drop in this order:

DROP TABLE Objectives CASCADE CONSTRAINTS;
DROP TABLE Stakeholders CASCADE CONSTRAINTS;
DROP TABLE Activities CASCADE CONSTRAINTS;
DROP TABLE Projects CASCADE CONSTRAINTS;

• Note: CASCADE CONSTRAINTS automatically removes dependent foreign keys.
• Always drop child tables before parent tables to avoid constraint errors.

9. How to Execute on Oracle Live SQL

1. Visit Oracle Live SQL.
2. Log in or create an account.
3. Copy and paste the SQL code blocks above into the worksheet.
4. Run the scripts to create tables, insert data, query, or drop tables.

🎥 Video Wall SQL Project

💙 Acknowledgements

Special thanks to Professor Daniel Gatti for guidance throughout this project.

This project provides a complete database model for managing digital content on video walls, using MySQL and DBeaver. Here you will find the conceptual and logical models, Markdown documentation for all tables, and ready-to-use SQL DDL code.

🔗 Access the MySQL Code with Integrated Physical Model

🔗 Access here link for Conceptual and Logical Diagrams in the Lucid.app editor

1. How to Use in DBeaver

• Connect to your MySQL server.
• Open DBeaver and create a new database connection.
• Open the SQL Editor: Right-click your database > SQL Editor > New SQL Script.
• Paste and run the DDL script below to create the tables.
• Use DBeaver’s ER Diagram tool to visualize the schema.
• Refer to the diagrams and Markdown tables below for documentation.

2. Conceptual Model

The conceptual model provides a high-level overview of the main entities and their relationships in the Video Wall project.

➢ Main Entities:

• Screen: Represents each display unit in the video wall system.
• Exhibition: Links content to screens, defining what is shown and when.
• Content: Digital media or information to be displayed.
• Priority: Classification of content importance.
• User: The person or system responsible for uploading or managing content.
• Type: The type or format of the content.
• Category_Content: Associative entity connecting content to categories.
• Category: Thematic grouping for content.

3. Logical Model

The logical model details the tables, columns, and relationships as they will be implemented in MySQL.

4. Physical Model

The physical model specifies how the logical data model will be implemented in the chosen database management system (DBMS), such as MySQL. It translates entities and relationships from the logical model into actual database tables, columns, and constraints, including technical details needed for deployment and performance optimization.

Key features of the physical model include:

• Table Structures: Each entity from the logical model becomes a table, with attributes mapped to columns.
• Data Types: Every column is assigned a specific data type (e.g., INT, VARCHAR, DATE) according to the DBMS requirements[1][3][4].
• Constraints: Primary keys, foreign keys, unique constraints, and not-null constraints are defined to enforce data integrity[1][3][5].
• Indexes: Indexes are added to columns to improve query performance.
• Relationships: Foreign key constraints establish and enforce relationships between tables.
• Database-specific Features: The model may include additional objects such as views, triggers, stored procedures, and partitioning, tailored to the specific DBMS.
• Storage and Performance Considerations: Specifications for storage allocation, partitioning, and clustering may be included to optimize data access and management.

This model is typically developed by database administrators and developers, and serves as the blueprint for generating the Data Definition Language (DDL) scripts used to create the actual database schema on the server. Once implemented, changes to the physical model can be complex, especially after data has been inserted, making careful planning essential.

In summary, the physical model is the concrete, technical realization of the logical model, ready for deployment in a production environment, ensuring that all business requirements are met with optimal performance and data integrity.

5. Database Tables (Markdown)

Screen

Column	Type	Key
Id	int	PK
Localization	varchar

Priority

Column	Type	Key
Id	int	PK
Classification	varchar

User

Column	Type	Key
Id	int	PK
Nome	varchar
Last_name	varchar
email	varchar

Type

Column	Type	Key
Id	int	PK
Type	varchar

Content

Column	Type	Key
Id	int	PK
Title	varchar
Description	varchar
Path	varchar
Id_User	int	FK (User)
Id_Type	int	FK (Type)
Id_Priority	int	FK (Priority)
Data_Creation	date
Date_Validity	date

Category

Column	Type	Key
Id	int	PK
Name	varchar

Category_Content

Column	Type	Key
Id_Content	int	PK, FK (Content)
Id_Category	int	PK, FK (Category)

Exhibition

Column	Type	Key
Id_Content	int	PK, FK (Content)
Id_Screen	int	PK, FK (Screen)
Data_Start	date	PK
Data_End	date

5. SQL DDL Code

CREATE TABLE Screen (
Id INT PRIMARY KEY AUTO_INCREMENT,
Localization VARCHAR(255) NOT NULL
);

CREATE TABLE Priority (
Id INT PRIMARY KEY AUTO_INCREMENT,
Classification VARCHAR(50) NOT NULL
);

CREATE TABLE User (
Id INT PRIMARY KEY AUTO_INCREMENT,
Nome VARCHAR(100) NOT NULL,
Last_name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL
);

CREATE TABLE Type (
Id INT PRIMARY KEY AUTO_INCREMENT,
Type VARCHAR(50) NOT NULL
);

CREATE TABLE Content (
Id INT PRIMARY KEY AUTO_INCREMENT,
Title VARCHAR(255) NOT NULL,
Description VARCHAR(255),
Path VARCHAR(255),
Id_User INT,
Id_Type INT,
Id_Priority INT,
Data_Creation DATE,
Date_Validity DATE,
FOREIGN KEY (Id_User) REFERENCES User(Id),
FOREIGN KEY (Id_Type) REFERENCES Type(Id),
FOREIGN KEY (Id_Priority) REFERENCES Priority(Id)
);

CREATE TABLE Category (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL
);

CREATE TABLE Category_Content (
Id_Content INT,
Id_Category INT,
PRIMARY KEY (Id_Content, Id_Category),
FOREIGN KEY (Id_Content) REFERENCES Content(Id),
FOREIGN KEY (Id_Category) REFERENCES Category(Id)
);

CREATE TABLE Exhibition (
Id_Content INT,
Id_Screen INT,
Data_Start DATE,
Data_End DATE,
PRIMARY KEY (Id_Content, Id_Screen, Data_Start),
FOREIGN KEY (Id_Content) REFERENCES Content(Id),
FOREIGN KEY (Id_Screen) REFERENCES Screen(Id)
);

6. Summary

• All tables and relationships from the logical model are included.
• SQL code is complete and ready for use in MySQL.
• Markdown tables provide clear documentation for each table.
• Diagrams help visualize both the conceptual and logical structure.

Feel Free to Reach Out:

💌 Email Me

🛸๋ My Contacts Hub

────────────── ⊹🔭๋ ──────────────

➣➢➤ Back to Top

Copyright 2025 Mindful-AI-Assistants. Code released under the MIT license.