NoSQL: Building Databases in Practice

Not Only SQL

Heads Up

• Whenever possible, projects and deliverables developed during the course will be made publicly accessible.

• The course emphasizes practical, hands-on experience with real datasets to simulate professional consulting scenarios in the fields of Data Analysis and Data Mining for partner organizations and institutions affiliated with the university.

• All activities and materials will strictly adhere to the academic and ethical guidelines of PUC-SP. Any content not authorized for public disclosure will remain confidential and securely stored in private repositories.

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 Dr. 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

MongoDB Compass - GUI for 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.

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

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

Practical Examples

DDL Data Definition Language

Select Commands using Oracle

sql 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

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

Create Table

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

ALTER TABLE

sql ALTER TABLE Person ADD Telefone VARCHAR(15);

Drop Table

sql DROP TABLE Person;

DML Data Manipulation Language

Insert Data

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

Update Data

sql UPDATE Person SET Age = 31 WHERE ID = 1;

DQL Data Query Language

Select Data

sql SELECT Name, Email FROM Person WHERE Age > 25;

Advanced SQL Concepts

Primary Key Example

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

Check Constraint Example

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

Identity Property (SQL Server)

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

NoSQL MongoDB Example

Creating a Collection and Inserting Documents

```sql // Inserting a single document db.usuarios.insertOne({ name: "Joo 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

```sql // 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

```sql // Update user age db.usuarios.updateOne( { name: "Joo 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)

sql -- 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) );

sql -- 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 );

sql -- 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 );

sql -- 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

sql 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

sql 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

sql 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

sql 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:

sql 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 DBeavers 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 | | | IdUser | int | FK (User) | | IdType | int | FK (Type) | | IdPriority | int | FK (Priority) | | DataCreation | date | | | Date_Validity | date | |

Category

| Column | Type | Key | |--------|---------|-----| | Id | int | PK | | Name | varchar | |

Category_Content

| Column | Type | Key | |-------------|------|----------------------| | IdContent | int | PK, FK (Content) | | IdCategory | int | PK, FK (Category) |

Exhibition

| Column | Type | Key | |------------|------|----------------------| | IdContent | int | PK, FK (Content) | | IdScreen | int | PK, FK (Screen) | | DataStart | date | PK | | DataEnd | date | |

5. SQL DDL Code

```sql 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 AUTOINCREMENT, Nome VARCHAR(100) NOT NULL, Lastname 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 AUTOINCREMENT, Title VARCHAR(255) NOT NULL, Description VARCHAR(255), Path VARCHAR(255), IdUser INT, IdType INT, IdPriority INT, DataCreation DATE, DateValidity DATE, FOREIGN KEY (IdUser) REFERENCES User(Id), FOREIGN KEY (IdType) 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 CategoryContent ( IdContent INT, IdCategory INT, PRIMARY KEY (IdContent, IdCategory), FOREIGN KEY (IdContent) REFERENCES Content(Id), FOREIGN KEY (Id_Category) REFERENCES Category(Id) );

CREATE TABLE Exhibition ( IdContent INT, IdScreen INT, DataStart DATE, DataEnd DATE, PRIMARY KEY (IdContent, IdScreen, DataStart), FOREIGN KEY (IdContent) 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.