In Part 2, we built a consultant utilization report end to end through the SQLcl MCP server. Part 3 turns the same MCP toward the data architect's view of the database. We will extend the schema with two new tables, then generate a schema data model out of the data dictionary with a single natural English prompt with no external diagramming tool.
Extending the Schema
Before asking Claude Code (Claude) for a diagram, I wanted a richer schema to diagram. Part 2 ended with a single timesheet table, CONSULTANT_TIME.
I gave Claude Code one short prompt:
❯ let's create the projects and project_assignments tables, let's make sure that we have the "row who columns".A few seconds later, the DDL came back with the expected results. The PROJECTS table models each engagement with a budget, a lifecycle status, and foreign keys into the DEPARTMENTS and EMPLOYEES tables. The PROJECT_ASSIGNMENTS table bridges employees to projects over time, with an allocation percentage. A unique key prevents the same employee from starting on the same project twice on the same day.
CREATE TABLE selectai_user.projects (
project_id NUMBER(6,0),
project_name VARCHAR2(80) NOT NULL,
department_id NUMBER(4,0) NOT NULL,
manager_id NUMBER(6,0),
start_date DATE NOT NULL,
end_date DATE,
budget NUMBER(12,2),
status VARCHAR2(12) NOT NULL,
created_by VARCHAR2(30) DEFAULT USER NOT NULL,
creation_date DATE DEFAULT SYSDATE NOT NULL,
last_updated_by VARCHAR2(30) DEFAULT USER NOT NULL,
last_update_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT pk_projects PRIMARY KEY (project_id),
CONSTRAINT ck_proj_dates CHECK (end_date IS NULL OR end_date >= start_date),
CONSTRAINT ck_proj_status CHECK (status IN ('PROPOSED','ACTIVE','ON_HOLD','CLOSED','CANCELLED')),
CONSTRAINT fk_proj_dept FOREIGN KEY (department_id) REFERENCES selectai_user.departments (department_id),
CONSTRAINT fk_proj_manager FOREIGN KEY (manager_id) REFERENCES selectai_user.employees (employee_id)
);
CREATE TABLE selectai_user.project_assignments (
assignment_id NUMBER(8,0),
employee_id NUMBER(6,0) NOT NULL,
project_id NUMBER(6,0) NOT NULL,
role VARCHAR2(40) NOT NULL,
allocation_pct NUMBER(5,2) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
created_by VARCHAR2(30) DEFAULT USER NOT NULL,
creation_date DATE DEFAULT SYSDATE NOT NULL,
last_updated_by VARCHAR2(30) DEFAULT USER NOT NULL,
last_update_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT pk_project_assignments PRIMARY KEY (assignment_id),
CONSTRAINT uk_pa_unique UNIQUE (employee_id, project_id, start_date),
CONSTRAINT ck_pa_alloc CHECK (allocation_pct BETWEEN 0 AND 100),
CONSTRAINT ck_pa_dates CHECK (end_date IS NULL OR end_date >= start_date),
CONSTRAINT fk_pa_emp FOREIGN KEY (employee_id) REFERENCES selectai_user.employees (employee_id),
CONSTRAINT fk_pa_proj FOREIGN KEY (project_id) REFERENCES selectai_user.projects (project_id)
);The quoted phrase "row who columns" in the prompt did the heavy lifting. Without a single mention of Oracle E-Business Suite (EBS) by name, Claude knew what I was referring to and added the CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE columns with USER and SYSDATE default values. Every row now records who touched it and when, without a single table level trigger.
Documenting the Schema
Before asking for a diagram, I asked Claude Code to document the two new tables at the table level and column level.
❯ now let's add comments to the Projects and Project Assignments tables and columns. Show the generated DDL before executing.Claude came back with twenty six COMMENT ON statements, one per table plus one per column across both tables. Here's a small sample output of what Claude generated:
COMMENT ON TABLE selectai_user.projects IS
'Projects delivered by a department, led by an employee, with a budget and lifecycle status.';
COMMENT ON COLUMN selectai_user.projects.project_id IS 'Surrogate primary key for the project.';
COMMENT ON COLUMN selectai_user.projects.department_id IS 'Owning department. Foreign key to DEPARTMENTS.';
COMMENT ON COLUMN selectai_user.projects.status IS 'Lifecycle state. One of PROPOSED, ACTIVE, ON_HOLD, CLOSED, CANCELLED.';
COMMENT ON COLUMN selectai_user.projects.budget IS 'Approved project budget in USD. Nullable if the project is unfunded.';
COMMENT ON TABLE selectai_user.project_assignments IS
'Bridge table mapping employees to projects over time, including the role held and percentage of their week allocated.';
COMMENT ON COLUMN selectai_user.project_assignments.assignment_id IS 'Surrogate primary key for the assignment row.';
COMMENT ON COLUMN selectai_user.project_assignments.role IS 'Role the employee holds on this project, for example Lead, Architect, or Engineer.';
COMMENT ON COLUMN selectai_user.project_assignments.allocation_pct IS 'Percentage of the employee''s billable capacity allocated to this project. Must be between 0 and 100.';After I approved the DDL, Claude ran all twenty six statements. Claude executed a single query to provide the expected output:
SELECT table_name, column_name, comments
FROM user_col_comments
WHERE table_name IN ('PROJECTS','PROJECT_ASSIGNMENTS')
AND comments IS NOT NULL;Two tables and twenty three columns came back with descriptions. Jointly with AI, we documented the two new tables, so our analysts can consume them. Another added benefit is that downstream tools like SQL Developer, Select AI, JSON catalog exports, and any future MCP server can benefit from having that context.
AI Review of the Design via MCP
With the two tables created and documented, the next thing I wanted was a second set of eyes on the design before moving on. A design review is the kind of task MCP unlocks. Claude Code has a connection to the data dictionary, so it is able to audit the structure of a table without me having to write queries.
❯ Review the new tables on the selectai schema and tell me
what is wrong or missing.Claude Code walked the data dictionary and came back with a pass or fail audit across the dimensions that matter for a production table: primary key, foreign keys, FK column indexes, NOT NULL coverage, check constraints, type alignment with parent tables, comments, and the WHO column audit pattern.
PROJECTS
| Check | Status | Detail |
|---|---|---|
| Primary key | Pass | PK_PROJECTS on PROJECT_ID, NUMBER(6,0). |
| Foreign keys declared | Pass | FK_PROJ_DEPT → DEPARTMENTS, FK_PROJ_MANAGER → EMPLOYEES. |
| FK column indexes | Fail | DEPARTMENT_ID and MANAGER_ID have no covering index. |
| NOT NULL coverage | Pass | Required columns and the four WHO columns are NOT NULL. |
| CHECK constraints | Pass | CK_PROJ_STATUS on five lifecycle values, CK_PROJ_DATES on end/start ordering. |
| Type alignment with parents | Pass | DEPARTMENT_ID and MANAGER_ID match their parent column types. |
| Comments | Pass | Table comment and all twelve column comments present. |
| WHO columns and trigger | Pass | Defaults of USER and SYSDATE, plus TRG_PROJECTS_WHO refreshing on update. |
PROJECT_ASSIGNMENTS
| Check | Status | Detail |
|---|---|---|
| Primary key | Pass | PK_PROJECT_ASSIGNMENTS on ASSIGNMENT_ID, NUMBER(8,0). |
| Foreign keys declared | Pass | FK_PA_EMP → EMPLOYEES, FK_PA_PROJ → PROJECTS. |
| FK column indexes | Partial fail | EMPLOYEE_ID is covered by UK_PA_UNIQUE as its leading column. PROJECT_ID sits at position two of the same unique key, which does not satisfy the leading column rule. |
| NOT NULL coverage | Pass | Every non-END_DATE column is NOT NULL. |
| CHECK constraints | Pass | CK_PA_ALLOC on 0 to 100, CK_PA_DATES on end/start ordering. |
| Unique key | Pass | UK_PA_UNIQUE (EMPLOYEE_ID, PROJECT_ID, START_DATE). |
| Comments | Pass | Table comment and all eleven column comments present. |
| WHO columns and trigger | Pass | Defaults plus TRG_PROJECT_ASSIGNMENTS_WHO refreshing on update. |
The design passed most of the checks. The one clear gap was foreign key index coverage. Three FK columns had no covering index: PROJECTS.DEPARTMENT_ID, PROJECTS.MANAGER_ID, and PROJECT_ASSIGNMENTS.PROJECT_ID. Left alone, a parent update or delete would block the whole child table while Oracle did a scan.
Indexing the Foreign Keys
The missing FK index finding was the one actionable gap from the review. Oracle has a well known behavior where unindexed foreign keys can cause locking issues when the parent table is updated or deleted. As a general rule, I want my FK columns backed by an index, whether on their own or as the leading column of a composite key. So I asked Claude Code to create indexes on FK columns:
❯ Create indexes on all the FK columns on the projects and
project assignments table so that we do not encounter
locking conditions. We can always remove indexes later.
We have a standard of making unused indexes invisible and
remove them systematically. First create the DDL for me
to review.Claude Code did not just dump four CREATE INDEX statements. It inspected DBA_INDEXES and DBA_IND_COLUMNS first, then came back with a coverage audit.
| FK | Column | Covered by existing index? |
|---|---|---|
FK_PROJ_DEPT |
PROJECTS.DEPARTMENT_ID |
No. Needs an index. |
FK_PROJ_MANAGER |
PROJECTS.MANAGER_ID |
No. Needs an index. |
FK_PA_EMP |
PROJECT_ASSIGNMENTS.EMPLOYEE_ID |
Yes. Leading column of UK_PA_UNIQUE. No separate index needed. |
FK_PA_PROJ |
PROJECT_ASSIGNMENTS.PROJECT_ID |
No. Sits at position two of UK_PA_UNIQUE, so it does not satisfy the leading-column rule. |
The recommendation came back as three indexes rather than four, and the reasoning was worth focusing on. The fourth FK column, PROJECT_ASSIGNMENTS.EMPLOYEE_ID, was already covered by the composite unique key I had asked for earlier in the schema.
CREATE INDEX selectai_user.idx_proj_dept
ON selectai_user.projects (department_id);
CREATE INDEX selectai_user.idx_proj_manager
ON selectai_user.projects (manager_id);
CREATE INDEX selectai_user.idx_pa_proj
ON selectai_user.project_assignments (project_id);I approved the three indexes to be created. Claude Code ran each of the CREATE INDEX statements and then it verified that they were created by querying the USER_INDEXES view.
SELECT table_name, index_name, uniqueness, status, visibility
FROM user_indexes
WHERE table_name IN ('PROJECTS','PROJECT_ASSIGNMENTS')
ORDER BY table_name, index_name;TABLE_NAME INDEX_NAME UNIQUENESS STATUS VISIBILITY
------------------- ------------------------ ----------- ------- ----------
PROJECTS IDX_PROJ_DEPT NONUNIQUE VALID VISIBLE
PROJECTS IDX_PROJ_MANAGER NONUNIQUE VALID VISIBLE
PROJECTS PK_PROJECTS UNIQUE VALID VISIBLE
PROJECT_ASSIGNMENTS IDX_PA_PROJ NONUNIQUE VALID VISIBLE
PROJECT_ASSIGNMENTS PK_PROJECT_ASSIGNMENTS UNIQUE VALID VISIBLE
PROJECT_ASSIGNMENTS UK_PA_UNIQUE UNIQUE VALID VISIBLEThe output confirmed that all three new indexes are in place, each marked VALID and VISIBLE. If any of them turn out to be unused later, our standard is to flip them to INVISIBLE first, monitor for a release cycle, and drop them only after nothing complains. An invisible index keeps the definition in the dictionary while taking the optimizer out of the picture, which lets us roll back quickly if a plan regresses.
Review the CONSULTANT_TIME Table Against Our Standards
The two new tables came out of the review looking clean. To see what Claude Code's review process looks like when it finds real problems, I turned it on CONSULTANT_TIME. This is the flat timesheet export from Part 2, and it predates every standard we now apply to new tables.
❯ Review the consultant_time table and tell me if it conforms
to our standards.| Check | Status | Detail |
|---|---|---|
| Primary key | Fail | No PK declared. Timesheet rows have no surrogate identifier. |
| Foreign keys | Fail | EMPLOYEE, CLIENT_NAME, SUPPLIER, and PROJECT are free text with no reference tables. Joins back into the HR domain require string matching. |
| Indexes | Fail | None. |
| NOT NULL coverage | Fail | Every column is nullable, including ENTRY_DATE and EMPLOYEE, which should both be required. |
| CHECK constraints | Fail | None. BILLABLE is VARCHAR2(12) with no constraint on its value. |
| WHO columns | Fail | No CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, or LAST_UPDATE_DATE. No audit trail. |
| Comments | Fail | No table comment and no column comments. |
| Naming consistency | Fail | EMPLOYEE and PROJECT are used as column names rather than as foreign key columns (EMPLOYEE_ID, PROJECT_ID). |
| Data type choices | Partial | ENTRY_DATE and HOURS are sensible. BILLABLE should be CHAR(1) with a CHECK, or a proper Boolean. |
This is what happens when operational data lands in the schema before a governance pass gets scheduled. Bringing CONSULTANT_TIME into conformance is its own project: a surrogate primary key, foreign keys into the new PROJECTS table and into new CLIENTS and SUPPLIERS tables, a CHECK constraint on BILLABLE, the four WHO columns, and table and column comments.
Asking MCP for an ER Model
This is the part that blew my mind when I first started working with MCP. The data was in and the table was counted. The next natural DBA question is "what does the rest of this schema look like?" So I asked.
❯ Show me an ER model of the selectai schema.There was no SQL to write by hand and no need to join user_tables to user_tab_columns to user_constraints to user_cons_columns to stitch a picture back together. Claude Code called the schema-information MCP tool, walked the data dictionary through SQLcl, and came back with this.
╔══════════════════════════════════════════════════════════════════════════════╗
║ SELECTAI_USER — ER MODEL ║
╚══════════════════════════════════════════════════════════════════════════════╝
┌────────────────────┐
│ REGIONS │
│ PK REGION_ID │
│ REGION_NAME │
└─────────┬──────────┘
│ 1:N
▼
┌────────────────────┐
│ COUNTRIES │
│ PK COUNTRY_ID │
│ FK REGION_ID │
│ COUNTRY_NAME │
└─────────┬──────────┘
│ 1:N
▼
┌────────────────────┐
│ LOCATIONS │
│ PK LOCATION_ID │
│ FK COUNTRY_ID │
│ CITY │
│ STATE_PROVINCE │
│ STREET_ADDRESS │
│ POSTAL_CODE │
└─────────┬──────────┘
│ 1:N
▼
┌────────────────────┐
│ DEPARTMENTS │
│ PK DEPARTMENT_ID │
│ FK LOCATION_ID │
│ FK MANAGER_ID * │
│ DEPARTMENT_NAME│
└────┬──────────┬────┘
│ 1:N │ 1:N
▼ ▼
┌──────────┐ ┌─────────────────────┐ ┌───────────────────┐
│ JOBS │ │ EMPLOYEES │ │ PROJECTS │
│──────────│ │─────────────────────│ │───────────────────│
│ PK JOB_ID│◀──────────│ PK EMPLOYEE_ID │◀─────│ FK MANAGER_ID │
│ TITLE │ 1:N │ FK DEPARTMENT_ID │ 1:N │ PK PROJECT_ID │
│ MIN │ │ FK JOB_ID │ │ FK DEPARTMENT_ID │
│ MAX │ │ FK MANAGER_ID ↺ │ │ PROJECT_NAME │
└─────┬────┘ │ FIRST_NAME │ │ START_DATE │
│ 1:N │ LAST_NAME │ │ END_DATE │
│ │ EMAIL (UQ) │ │ BUDGET │
│ │ HIRE_DATE │ │ STATUS (CHK) │
│ │ SALARY (>0 CHK) │ │ WHO x4 │
│ │ COMMISSION_PCT │ └─────────┬─────────┘
│ └──┬───────────────┬──┘ │ 1:N
│ │ 1:N │ 1:N │
│ ▼ ▼ ▼
│ ┌──────────────────┐ ┌────────────────────────────────┐
└────────▶│ JOB_HISTORY │ │ PROJECT_ASSIGNMENTS │
1:N │──────────────────│ │────────────────────────────────│
│ PK EMPLOYEE_ID │ │ PK ASSIGNMENT_ID │
│ PK START_DATE │ │ FK EMPLOYEE_ID │
│ FK JOB_ID │ │ FK PROJECT_ID │
│ FK DEPARTMENT_ID│ │ UK (EMP, PROJ, START_DATE) │
│ END_DATE │ │ ROLE │
│ (end>start) │ │ ALLOCATION_PCT (0-100 CHK) │
└──────────────────┘ │ START_DATE │
│ END_DATE │
│ (end>start CHK) │
│ WHO x4 │
└────────────────────────────────┘
* DEPARTMENTS.MANAGER_ID references EMPLOYEES.EMPLOYEE_ID (department head).
↺ EMPLOYEES.MANAGER_ID is a self-reference for the employee's supervisor.
┌────────────────────────┐
│ CONSULTANT_TIME │ standalone, no FK
│────────────────────────│
│ ENTRY_DATE │
│ EMPLOYEE │
│ CLIENT_NAME │
│ PROJECT │
│ TASK │
│ HOURS │
│ SUPPLIER │
│ BILLABLE │
└────────────────────────┘The diagram captured every table, column, primary key, unique key, check constraint, and foreign key that tied the schema together, all pulled from the data dictionary in a single round trip.
This is where MCP stops feeling like a protocol and starts feeling like a superpower in the hands of the data architect. When you connect an agent to the data dictionary, it produces a diagram that would have taken a DBA fifteen minutes of SQL, a screenshot, and a whiteboard marker. Now we can complete this task with a single prompt.
Caveats
I do not recommend pointing AI directly to any production data or database. We need to discern how to use AI with the right guard rails and explicit guidelines. As you work with AI and MCP, you need to start with only select access to the database. What I recommend is to have AI generate code, you as the human loop in the middle review the code and deploy into production yourself. Unless you have a corporate account with Anthropic or with OpenAI, they are learning on your data. Never expose AI from your personal account. Lastly, a lot of companies are starting to deploy their own local LLM. This may be the approach that I would recommend for customers who cannot afford to sign a corporate agreement with the leading AI vendors.
Conclusion
In this blog post, we extended a schema, documented tables and columns with comments, audited the design with AI, and produced a schema ER diagram.
What you have seen here is just the tip of the iceberg of what MCP can do for a data architect. We did not touch PL/SQL generation, which is another area where MCP is particularly strong and could carry its own blog series.
In Part 4, we point MCP toward the database administrator's day. We will have MCP perform high-level health checks, orchestrate backups, and handle the operational work that keeps a production database alive.
Charles is an Oracle ACE Director, VMware vExpert, and author of over a dozen books in the Oracle ecosystem.