Documentation Index
Fetch the complete documentation index at: https://mintlify.com/kenri89/PROYECTO_UTP_AED_1_1/llms.txt
Use this file to discover all available pages before exploring further.
The application uses a SQL Server database named iestp_peru_japon. All tables and seed data are created by executing schema.sql against a running SQL Server instance. Foreign key constraints enforce referential integrity: Matriculas and Solicitudes both reference Estudiantes(carnet), and Matriculas also references Cursos(codigo). The usuarios table is independent — it is used for Administrador and Secretaria login via the usuarios table directly, while student authentication falls back to the TSV-based CuentasEstudiantes mechanism.
Full Schema DDL
The complete schema.sql script is reproduced below. Run it once against a SQL Server instance that your config.properties can reach.
-- schema.sql
-- Execute against SQL Server
CREATE DATABASE iestp_peru_japon;
GO
USE iestp_peru_japon;
GO
CREATE TABLE usuarios (
id INT IDENTITY(1,1) PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
rol VARCHAR(20) NOT NULL DEFAULT 'Administrador'
);
GO
CREATE TABLE Cursos (
codigo VARCHAR(20) PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
creditos INT NOT NULL,
semestre INT NOT NULL
);
GO
CREATE TABLE Estudiantes (
carnet VARCHAR(20) PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
carrera VARCHAR(100) NOT NULL,
username VARCHAR(50) NULL
);
GO
CREATE TABLE Matriculas (
id INT IDENTITY(1,1) PRIMARY KEY,
carnet VARCHAR(20) NOT NULL,
codigo_curso VARCHAR(20) NOT NULL,
fecha_registro DATETIME DEFAULT GETDATE(),
FOREIGN KEY (carnet) REFERENCES Estudiantes(carnet),
FOREIGN KEY (codigo_curso) REFERENCES Cursos(codigo)
);
GO
CREATE TABLE Solicitudes (
id_solicitud INT IDENTITY(1,1) PRIMARY KEY,
carnet_estudiante VARCHAR(20) NOT NULL,
descripcion VARCHAR(500),
fecha_creacion DATETIME DEFAULT GETDATE(),
atendida BIT DEFAULT 0,
fecha_atencion DATETIME NULL,
FOREIGN KEY (carnet_estudiante) REFERENCES Estudiantes(carnet)
);
GO
-- Default seed accounts
INSERT INTO usuarios (username, password, rol) VALUES ('admin', '1234', 'Administrador');
INSERT INTO usuarios (username, password, rol) VALUES ('secretaria', '1234', 'Secretaria');
INSERT INTO usuarios (username, password, rol) VALUES ('estudiante', '1234', 'Estudiante');
GO
Table Reference
usuarios
Stores login credentials for Administrador and Secretaria roles. The application queries this table via UsuarioDAO on every login attempt. If the database is unavailable, UsuarioDAO falls back to hardcoded defaults with the same usernames and passwords as the seed rows.
| Column | Type | Constraints | Notes |
|---|
id | INT IDENTITY(1,1) | PRIMARY KEY | Auto-incremented surrogate key |
username | VARCHAR(50) | NOT NULL, UNIQUE | Case-insensitive match in queries (LOWER()) |
password | VARCHAR(100) | NOT NULL | Plain text — see warning below |
rol | VARCHAR(20) | NOT NULL, DEFAULT 'Administrador' | 'Administrador', 'Secretaria', or 'Estudiante' |
Cursos
Holds the full course catalogue. codigo is the natural primary key (e.g. "AED101") used as a foreign key target by Matriculas.
| Column | Type | Constraints | Notes |
|---|
codigo | VARCHAR(20) | PRIMARY KEY | Course code; referenced by Matriculas.codigo_curso |
nombre | VARCHAR(100) | NOT NULL | Descriptive course title |
creditos | INT | NOT NULL | Number of academic credits |
semestre | INT | NOT NULL | Semester placement (1–10) |
Estudiantes
Holds the student registry. carnet is the natural primary key (student registration number) referenced by both Matriculas and Solicitudes.
| Column | Type | Constraints | Notes |
|---|
carnet | VARCHAR(20) | PRIMARY KEY | Student registration number; referenced by Matriculas and Solicitudes |
nombre | VARCHAR(100) | NOT NULL | Full student name |
carrera | VARCHAR(100) | NOT NULL | Degree programme (e.g. "Computación") |
username | VARCHAR(50) | NULL | Optional link to a student login identity; not actively used by UsuarioDAO |
Matriculas
Records enrollment relationships between students and courses. The surrogate id avoids a composite primary key, but the meaningful uniqueness constraint is the (carnet, codigo_curso) pair, which is enforced at the application layer before any INSERT.
| Column | Type | Constraints | Notes |
|---|
id | INT IDENTITY(1,1) | PRIMARY KEY | Surrogate auto-increment key |
carnet | VARCHAR(20) | NOT NULL, FK | References Estudiantes(carnet) |
codigo_curso | VARCHAR(20) | NOT NULL, FK | References Cursos(codigo) |
fecha_registro | DATETIME | DEFAULT GETDATE() | Automatically set to the server timestamp on INSERT |
Solicitudes
Stores academic requests submitted by students (e.g. grade reviews, certificate requests). Each solicitud belongs to exactly one student and carries an atendida flag to track whether it has been processed.
| Column | Type | Constraints | Notes |
|---|
id_solicitud | INT IDENTITY(1,1) | PRIMARY KEY | Surrogate auto-increment key |
carnet_estudiante | VARCHAR(20) | NOT NULL, FK | References Estudiantes(carnet) |
descripcion | VARCHAR(500) | NULL | Free-text description of the request |
fecha_creacion | DATETIME | DEFAULT GETDATE() | Automatically set to the server timestamp on INSERT |
atendida | BIT | DEFAULT 0 | 0 = pending, 1 = attended |
fecha_atencion | DATETIME | NULL | Set when the request is marked as attended |
The Java Solicitud model has a tipo field (request type), and the TSV
persistence file solicitudes.tsv includes tipo as its second column.
However, the Solicitudes table in schema.sql does not have a tipo
column — the field only exists in the in-memory model and the local TSV backup.
If you need to persist tipo in SQL Server, add a tipo VARCHAR(50) column
to the Solicitudes table and update the INSERT/SELECT statements accordingly.
Entity–Relationship Summary
Matriculas relationships
Matriculas.carnet → Estudiantes(carnet)
Matriculas.codigo_curso → Cursos(codigo)A student can be enrolled in many courses; a course can have many students enrolled. The relationship is many-to-many, resolved through Matriculas as the join table.
Solicitudes relationships
Solicitudes.carnet_estudiante → Estudiantes(carnet)A student can have many requests. usuarios is standalone — there is no foreign key between usuarios and Estudiantes. Student login is handled through CuentasEstudiantes (TSV), not through the usuarios table.
Seed Data
Three default accounts are inserted by schema.sql. These match the hardcoded fallback credentials in UsuarioDAO.autenticarFallback() so that the application works with or without a live database connection.
INSERT INTO usuarios (username, password, rol) VALUES ('admin', '1234', 'Administrador');
INSERT INTO usuarios (username, password, rol) VALUES ('secretaria', '1234', 'Secretaria');
INSERT INTO usuarios (username, password, rol) VALUES ('estudiante', '1234', 'Estudiante');
Connection Setup
Before running schema.sql, ensure config.properties (on the classpath) points to your SQL Server instance. The default values compiled into ConexionSQL are:
db.server=localhost
db.port=1433
db.name=iestp_peru_japon
db.user=sa
db.password=1234
db.encrypt=true
db.trustCertificate=true
db.server and db.port accordingly and redeploy the JAR so that the updated config.properties is included on the classpath.
The password column in usuarios stores credentials as plain text. The
schema and seed data are designed for academic demonstration only. Do not
store real user credentials in this schema without first adding a proper
password-hashing mechanism (e.g. BCrypt).