clients client_id client_name cities city_id city_name branches branch_id branch_name city_id technicians technician_id technician_name categories category_id category_name equipment equipment_id equipment_name category_id service_types service_type_id service_name work_orders workorder_id order_number client_id technician_id equipment_id branch_id service_type_id service_date hours cost
City │ └── Branch │ └── WorkOrder │ ├── Client ├── Technician ├── Equipment └── ServiceType
Equipment │ Category
Cada tabla tiene una PK (id). La tabla work_orders tendrá como FK: client_id technician_id equipment_id branch_id service_type_id
Carga los datos Importa primero: cities branches clients technicians categories equipment service_types work_orders
Paso 1. Crear la base de datos CREATE DATABASE bd_eymi_quintero_tuClan; Luego conéctate a esa base de datos. Paso 2. Crear las tablas Empieza por las que no dependen de nadie. Ciudades CREATE TABLE riwi_cities ( city_id SERIAL PRIMARY KEY, city_name VARCHAR(100) UNIQUE NOT NULL ); Clientes CREATE TABLE riwi_clients ( client_id SERIAL PRIMARY KEY, client_name VARCHAR(100) UNIQUE NOT NULL ); Técnicos CREATE TABLE riwi_technicians ( technician_id SERIAL PRIMARY KEY, technician_name VARCHAR(100) UNIQUE NOT NULL ); Categorías CREATE TABLE riwi_categories ( category_id SERIAL PRIMARY KEY, category_name VARCHAR(100) UNIQUE NOT NULL ); Tipos de servicio CREATE TABLE riwi_service_types ( service_type_id SERIAL PRIMARY KEY, service_name VARCHAR(100) UNIQUE NOT NULL ); Paso 3. Crear las tablas que dependen de otras Sedes CREATE TABLE riwi_branches ( branch_id SERIAL PRIMARY KEY, branch_name VARCHAR(100) NOT NULL, city_id INT NOT NULL, FOREIGN KEY (city_id) REFERENCES riwi_cities(city_id) ); Equipos CREATE TABLE riwi_equipment ( equipment_id SERIAL PRIMARY KEY, equipment_name VARCHAR(100) NOT NULL, category_id INT NOT NULL, FOREIGN KEY (category_id) REFERENCES riwi_categories(category_id) ); Paso 4. Crear la tabla principal CREATE TABLE riwi_work_orders (
workorder_id SERIAL PRIMARY KEY,
order_number VARCHAR(50) UNIQUE NOT NULL,
client_id INT NOT NULL,
technician_id INT NOT NULL,
equipment_id INT NOT NULL,
branch_id INT NOT NULL,
service_type_id INT NOT NULL,
service_date DATE,
hours NUMERIC(5,2),
cost NUMERIC(10,2),
FOREIGN KEY (client_id)
REFERENCES riwi_clients(client_id),
FOREIGN KEY (technician_id)
REFERENCES riwi_technicians(technician_id),
FOREIGN KEY (equipment_id)
REFERENCES riwi_equipment(equipment_id),
FOREIGN KEY (branch_id)
REFERENCES riwi_branches(branch_id),
FOREIGN KEY (service_type_id)
REFERENCES riwi_service_types(service_type_id)
); Paso 5. Importar los CSV Importa primero: riwi_cities riwi_clients riwi_technicians riwi_categories riwi_service_types riwi_branches riwi_equipment riwi_work_orders Paso 6. Las consultas Órdenes por técnico SELECT t.technician_name, COUNT() AS total_orders FROM riwi_work_orders w JOIN riwi_technicians t ON w.technician_id=t.technician_id GROUP BY t.technician_name; Servicios por ciudad SELECT c.city_name, COUNT() AS total_services FROM riwi_work_orders w JOIN riwi_branches b ON w.branch_id=b.branch_id JOIN riwi_cities c ON b.city_id=c.city_id GROUP BY c.city_name; Servicios por tipo SELECT s.service_name, COUNT() AS total FROM riwi_work_orders w JOIN riwi_service_types s ON w.service_type_id=s.service_type_id GROUP BY s.service_name; Equipos con más mantenimientos SELECT e.equipment_name, COUNT() AS total FROM riwi_work_orders w JOIN riwi_equipment e ON w.equipment_id=e.equipment_id GROUP BY e.equipment_name ORDER BY total DESC; Clientes con más órdenes SELECT c.client_name, COUNT() AS total FROM riwi_work_orders w JOIN riwi_clients c ON w.client_id=c.client_id GROUP BY c.client_name ORDER BY total DESC; Órdenes por sede SELECT b.branch_name, COUNT() AS total FROM riwi_work_orders w JOIN riwi_branches b ON w.branch_id=b.branch_id GROUP BY b.branch_name;
En el panel izquierdo, expande Servers. Expande tu servidor (normalmente dice PostgreSQL). Haz clic derecho sobre Databases → Create → Database.... Escribe el nombre, por ejemplo: bd_eymi_quintero_tuClan Pulsa Save. Luego haz clic sobre esa base de datos y abre Query Tool (icono o clic derecho → Query Tool). A partir de ahí ya puedes ejecutar los CREATE TABLE.
CREATE TABLE riwi_branches(
branch_id SERIAL PRIMARY KEY,
branch_name VARCHAR(100) NOT NULL,
city_id INT NOT NULL,
FOREIGN KEY (city_id) REFERENCES riwi_cities(city_id)
); ¿Qué significa FOREIGN KEY? Es una llave foránea. Le dice a PostgreSQL: "El valor de city_id debe existir en la tabla riwi_cities." Por ejemplo: riwi_cities city_id city_name 1 Bogotá 2 Medellín Entonces en riwi_branches solo puedes usar 1 o 2, no un 99 si no existe. PASO 8. Crear la tabla de equipos Cada equipo pertenece a una categoría. CREATE TABLE riwi_equipment(
equipment_id SERIAL PRIMARY KEY,
equipment_name VARCHAR(100) NOT NULL,
category_id INT NOT NULL,
FOREIGN KEY (category_id) REFERENCES riwi_categories(category_id)
); PASO 9. Crear la tabla más importante: órdenes de servicio Esta tabla une todo. Cada orden tiene: un cliente, un técnico, un equipo, una sede, un tipo de servicio, una fecha, unas horas, un costo. Por eso lleva varias llaves foráneas. CREATE TABLE riwi_work_orders(
workorder_id SERIAL PRIMARY KEY,
order_number VARCHAR(50) UNIQUE NOT NULL,
client_id INT NOT NULL,
technician_id INT NOT NULL,
equipment_id INT NOT NULL,
branch_id INT NOT NULL,
service_type_id INT NOT NULL,
service_date DATE,
hours NUMERIC(5,2),
cost NUMERIC(10,2),
FOREIGN KEY (client_id) REFERENCES riwi_clients(client_id),
FOREIGN KEY (technician_id) REFERENCES riwi_technicians(technician_id),
FOREIGN KEY (equipment_id) REFERENCES riwi_equipment(equipment_id),
FOREIGN KEY (branch_id) REFERENCES riwi_branches(branch_id),
FOREIGN KEY (service_type_id) REFERENCES riwi_service_types(service_type_id)
); ¿Qué acabas de construir? Tu base de datos ya tiene esta estructura: Cities │ ▼ Branches │ ▼ Work Orders ▲ ▲ ▲ ▲ │ │ │ │ Clients Technicians Equipment Service Types ▲ │ Categories Eso ya representa un modelo relacional normalizado, como pide el enunciado. � Enunciado prueba de desempeño bases de datos - Jornada Intermedia.docx Ahora necesito que hagas esto: Ejecuta esas 3 tablas. Si todas dicen "Query returned successfully", dime: "Ya quedaron las 8 tablas."
This project was developed as part of the Relational Databases module. The objective is to transform an Excel file with redundant and inconsistent information into a normalized relational database.
The database stores information about clients, technicians, equipment, service types, branches, cities, and work orders, ensuring data integrity and reducing redundancy.
- PostgreSQL
- pgAdmin 4
- SQL
- Microsoft Excel
- Draw.io (Entity Relationship Diagram)
PostgreSQL
The original Excel file contained duplicated and inconsistent information.
The normalization process was carried out as follows:
- Atomic values were verified.
- Repeating groups were eliminated.
- Data was separated into independent tables.
- Redundant information was removed.
- Transitive dependencies were eliminated.
- Foreign keys were implemented to establish relationships between tables.
The database contains the following tables:
- riwi_clients
- riwi_cities
- riwi_branches
- riwi_technicians
- riwi_categories
- riwi_equipment
- riwi_service_types
- riwi_work_orders
Relationships between tables are implemented using Primary Keys and Foreign Keys.
The Entity Relationship Diagram (ERD) is included in the project folder.
- Create the database in PostgreSQL.
- Execute the DDL script.
- Verify that all tables were created successfully.
- Prepare the CSV files.
- Import each CSV file into its corresponding table using pgAdmin.
- Verify that all records were imported correctly.
The project includes the following SQL queries:
- Number of work orders handled by each technician.
- Service history by city.
- Total services by service type.
- Equipment with the highest number of maintenance services.
- Clients with the highest number of work orders.
- Number of work orders managed by each branch.
Full Name: Eymi Quintero
Clan: (Write your clan name here)