图形化数据库管理
Database administration in GUI clients
Inspect schemas, fix data, manage users and run migrations in DB GUI clients.
20 条本项目编写的提案 · 分类 v1.1 · 新增基准运行 0 次。下方验证方式与部分得分规则是设计，不是执行结果。
- 典型应用
- DBeaver · pgAdmin · DataGrip · SSMS
- 小类 GUI 需求标签
HY· 混合
任务提案 · 20
展开查看完整原文与建议检查方式；两种界面均保留提案英文原文。
R03.01.01.02-T01 Create a read-only analyst role with default privileges in pgAdmin
原始指令
In pgAdmin 4 connected to the sandbox PostgreSQL 16 server, create login role 'analyst' (password from fixtures/secrets.env, connection limit 5, expires 2026-12-31) with read-only access to all current and future tables in schema sales of database 'erp', and no access to schema hr.
- 平台
mac· macOSwin· Windows- 应用
- pgAdmin
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击type· 文字与表单- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Grader queries pg_roles, has_table_privilege for every sales/hr table, and pg_default_acl; then creates a new sales table as owner and checks analyst can SELECT but not INSERT. Connection limit and expiry exact.
建议部分得分
30% role attributes; 30% current table grants; 25% default privileges; 15% hr denied
R03.01.01.02-T02 Restore a backup under a new name and check its integrity in SSMS
原始指令
In SSMS, restore fixtures/Sales_2025.bak onto the sandbox SQL Server as database Sales_QA, relocating data and log files to D:\qa\, set recovery model SIMPLE and compatibility level 160, then run DBCC CHECKDB and save its output to output/checkdb.txt.
- 平台
win· Windows- 应用
- SSMS · SQL Server
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击type· 文字与表单- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader queries sys.databases and sys.master_files: name, file paths, recovery model, compat level exact; row counts of 5 key tables equal the backup manifest. checkdb.txt contains '0 allocation errors and 0 consistency errors' for Sales_QA. Source database untouched.
建议部分得分
35% restore with relocated files; 25% settings; 20% data present; 20% CHECKDB output
R03.01.01.02-T03 Import a messy CSV into an existing table with DBeaver
原始指令
Using DBeaver's data import, load fixtures/vendors_2025.csv (semicolon-delimited, Windows-1252, dd.mm.yyyy dates, comma decimals) into PostgreSQL table purchasing.vendors, mapping columns per docs/mapping.md. Rows failing constraints must go to table purchasing.vendors_rejects instead of aborting.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- DBeaver · PostgreSQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击type· 文字与表单- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader compares purchasing.vendors to gold: 1,463 valid rows with correct dates, decimals and accented names; 37 rejects in vendors_rejects with original values. No duplicate inserts; existing 200 rows unchanged.
建议部分得分
40% valid rows exact; 20% encoding/locale parsing; 25% rejects captured; 15% existing data untouched
R03.01.01.02-T04 Add missing foreign keys from an EER diagram in MySQL Workbench
原始指令
Reverse-engineer the sandbox MySQL schema 'shop' into an EER model in MySQL Workbench, add the 9 relationships drawn in input/target_erd.png (with ON DELETE rules shown), then synchronize the model to the server. Orphan rows blocking a constraint must be moved to table shop.orphans first.
- 平台
mac· macOSwin· Windows- 应用
- MySQL Workbench · MySQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
G2· GUI 必要- GUI 需求 · 普通权限
HY· 混合- 交互原语
drag· 拖放click· 指向与点击vis· 视觉判断- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader reads information_schema.REFERENTIAL_CONSTRAINTS: 9 FKs with correct columns and delete rules. Orphans moved (counts match), no other data changed. Saved .mwb model contains the relationships. The mysql CLI and client drivers are not installed on the VM, and performance_schema connection attributes must show the FK DDL came from a MySQL Workbench session (program_name).
建议部分得分
50% FKs and delete rules; 25% orphans handled; 15% no other data changes; 10% model saved
R03.01.01.02-T05 Fix a slow query from its execution plan and pin the good plan
原始指令
Report query fixtures/q_orders.sql takes 40 s on SQL Server 'Sales'. In SSMS, inspect the actual execution plan, add at most one index that fixes it, then use Query Store to force the good plan for the parameterized version. Save the before/after .sqlplan files in output/.
- 平台
win· Windows- 应用
- SSMS · SQL Server
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
vis· 视觉判断click· 指向与点击code· 代码与命令- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader runs the query 5× with 3 parameter values: median ≤1 s each. sys.indexes shows ≤1 new index; sys.query_store_plan has is_forced_plan=1 for the query. Both .sqlplan files parse and the after plan shows an Index Seek on the new index.
建议部分得分
40% runtime target; 20% single index; 25% plan forced in Query Store; 15% plans saved
R03.01.01.02-T06 Rename a column safely across views and functions in DataGrip
原始指令
In DataGrip on PostgreSQL 'erp', rename column customers.cust_nm to customer_name and update all 7 dependent views, 3 SQL functions and 2 triggers so everything compiles and returns the same results.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- DataGrip · PostgreSQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击code· 代码与命令- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader: column renamed; all dependent objects valid; each view's output equals its pre-change snapshot (EXCEPT both ways empty); functions return identical results on 20 test calls. No objects dropped and recreated with lost grants (ACL compare).
建议部分得分
20% rename; 40% views equivalent; 25% functions/triggers; 15% grants preserved
R03.01.01.02-T07 Find and end the blocking session holding a lock
原始指令
Application users report the 'orders' page hangs. In pgAdmin's Dashboard and lock views, find the session that blocks the others, record its pid, query and lock in output/blocker.json, and terminate only that session.
- 平台
mac· macOSwin· Windows- 应用
- pgAdmin · PostgreSQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击vis· 视觉判断- 工作产物
WP08· 应用 / 系统状态- 条件
risk· 高风险 / 不可逆- 提案难度
- 3 / 5
建议验证方式
Grader's scenario has 1 root blocker and 4 waiters (one waiter also blocks others). blocker.json pid and relation must match the root; after action, root session gone and all 4 waiters' sessions still alive and completed. Terminating any other session fails.
建议部分得分
40% correct blocker identified; 40% only the blocker terminated; 20% waiters complete
R03.01.01.02-T08 Build an aggregation and schema validation in MongoDB Compass
原始指令
In MongoDB Compass connected to sandbox db 'support', build an aggregation on tickets that returns median resolution hours per product for 2025 (excluding reopened tickets) and save it as view 'resolution_2025'. Add a compound index for the pipeline and a JSON Schema validator requiring priority in P1–P4.
- 平台
mac· macOSwin· Windows- 应用
- MongoDB Compass · MongoDB
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击code· 代码与命令- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader reads system.views pipeline and runs the view: values equal gold ±0.01 h. Index exists and explain() shows IXSCAN for the pipeline's $match. Inserting priority 'P5' is rejected by the validator; existing docs untouched.
建议部分得分
45% view results; 20% index used; 25% validator; 10% data untouched
R03.01.01.02-T09 Compare and sync staging to production data for three tables
原始指令
Using DBeaver's data compare, compare tables pricing.products, pricing.discounts and pricing.tiers between the 'prod-copy' and 'staging' PostgreSQL connections. Generate a sync script that makes staging match prod-copy, save it to output/sync.sql, and apply it to staging only.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- DBeaver · PostgreSQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击vis· 视觉判断- 工作产物
WP08· 应用 / 系统状态- 条件
risk· 高风险 / 不可逆- 提案难度
- 4 / 5
建议验证方式
Grader: staging tables now equal prod-copy (row-level hash); prod-copy unchanged (checksum + pg_stat_user_tables n_tup_upd/ins/del unchanged). sync.sql re-applied to a fresh staging copy produces the same result.
建议部分得分
50% staging matches; 30% prod-copy untouched (gate); 20% script reproducible
R03.01.01.02-T10 Schedule a nightly maintenance job with alerts in SQL Server Agent
原始指令
In SSMS, create a SQL Server Agent job 'Nightly_Sales_Maint' that rebuilds indexes over 30% fragmentation, updates statistics and backs up Sales to D:\backup with a dated filename, scheduled daily at 01:30, emailing operator 'DBA Team' on failure via the configured Database Mail profile.
- 平台
win· Windows- 应用
- SSMS · SQL Server Agent
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击type· 文字与表单code· 代码与命令- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader reads msdb (sysjobs, sysjobsteps, sysschedules, sysoperators, sysnotifications): steps present in order, schedule exact, failure notification set. Starts the job: backup file created with today's date; fragmented index rebuilt (sys.dm_db_index_physical_stats below 5%).
建议部分得分
30% job steps; 20% schedule; 20% failure alert; 30% successful run effects
R03.01.01.02-T11 Repair mojibake in customer names
原始指令
214 rows in the sandbox MySQL table crm.customers show mojibake ('Müller', 'François') from a bad UTF-8/Latin-1 import. Using MySQL Workbench or DBeaver, identify the affected rows, fix them in place, and record ids in output/fixed_ids.txt. Correct rows must not change.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- MySQL Workbench · DBeaver · MySQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击code· 代码与命令vis· 视觉判断- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader: all 214 rows equal gold UTF-8 names; the 9,786 untouched rows unchanged (hash); fixed_ids.txt equals the affected set. Double-encoded fixes (e.g., 'Ã' remaining) count as failures per row.
建议部分得分
70% rows fixed (per row); 20% no collateral changes; 10% id list exact
R03.01.01.02-T12 Add a NOT NULL column to a 120M-row table without downtime
原始指令
On the sandbox PostgreSQL 16 'events' DB, add column region text NOT NULL DEFAULT 'unknown' to table events, backfill it from sessions in batches, and add an index on (region, ts), all while the provided load generator keeps writing. No lock may block writers for more than 1 s.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- pgAdmin · PostgreSQL
- 交互表面
desk· 桌面 GUIterm· 终端- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令click· 指向与点击- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader's load generator logs write latencies: max lock wait ≤1 s throughout. Final state: column NOT NULL with default, 100% of pre-existing rows backfilled correctly, index valid (indisvalid true, built CONCURRENTLY).
建议部分得分
40% no blocking >1 s; 30% backfill correct; 20% index valid; 10% constraint final
R03.01.01.02-T13 Set up tenant row-level security in SQL Server
原始指令
In SSMS for database 'SaaS', implement row-level security so users in role TenantUser only see and modify rows of their own tenant (tenant id from SESSION_CONTEXT) in 6 tables, while role SupportAdmin sees all. Demonstrate with EXECUTE AS tests saved to output/rls_tests.sql.
- 平台
win· Windows- 应用
- SSMS · SQL Server
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令click· 指向与点击- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader runs hidden tests as 3 tenant users and SupportAdmin: SELECT counts match per tenant; cross-tenant INSERT/UPDATE blocked (BLOCK predicates). sys.security_policies enabled with filter+block predicates on all 6 tables.
建议部分得分
40% filter predicates; 30% block predicates; 20% admin bypass; 10% agent tests run
R03.01.01.02-T14 Unlock an Oracle user and reset its password policy
原始指令
In Oracle SQL Developer connected as the sandbox admin, unlock account REPORTING, assign it to profile APP_SVC (password life unlimited, 5 failed logins), set a new password from fixtures/secrets.env, and grant roles RPT_READ and CONNECT only.
- 平台
mac· macOSwin· Windows- 应用
- Oracle SQL Developer · Oracle Database
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击type· 文字与表单- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Grader queries DBA_USERS (account_status OPEN, profile), DBA_PROFILES limits, DBA_ROLE_PRIVS (exactly 2 roles), and logs in with the new password.
建议部分得分
30% unlocked; 25% profile; 30% roles exact; 15% login works
R03.01.01.02-T15 Export an ER diagram and data dictionary for a schema
原始指令
In DBeaver, produce documentation for PostgreSQL schema 'billing' (23 tables): an ER diagram laid out without overlapping tables exported to output/billing_erd.png, and output/dictionary.csv listing table, column, type, nullable, default, PK/FK and column comment.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- DBeaver · PostgreSQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
G2· GUI 必要- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
drag· 拖放click· 指向与点击vis· 视觉判断- 工作产物
WP03· 视觉排版- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
dictionary.csv compared to information_schema/pg_catalog: exact rows. PNG: OCR finds all 23 table names; detected table boxes do not overlap (box IoU 0) and FK lines present (VLM check, 20%). psql, SchemaSpy and Graphviz are absent and the DB password lives only in DBeaver's encrypted connection store, so queries and the ERD must come from DBeaver.
建议部分得分
50% dictionary exact; 30% diagram complete and non-overlapping; 20% VLM layout check
R03.01.01.02-T16 Recover from an accidental DELETE with point-in-time recovery
原始指令
At 14:07 someone ran DELETE FROM payments WHERE status='pending' on the sandbox PostgreSQL primary (see fixtures/incident.md). Using the pgBackRest repository and WAL archive, recover the deleted rows to the moment before the delete and reinsert them into the live database without losing writes made after 14:07.
- 平台
lin· Linux- 应用
- PostgreSQL · pgBackRest · pgAdmin
- 交互表面
term· 终端desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令click· 指向与点击- 工作产物
WP08· 应用 / 系统状态- 条件
risk· 高风险 / 不可逆- 提案难度
- 5 / 5
建议验证方式
Grader: payments now equals gold (deleted rows restored, post-14:07 inserts/updates preserved). Live cluster never restored over (system identifier unchanged; uptime continuous). Restore performed on a separate PITR instance (log check).
建议部分得分
30% PITR instance at correct target time; 40% rows restored; 20% later writes preserved; 10% live cluster untouched
R03.01.01.02-T17 Encrypt a sensitive column with the Always Encrypted wizard
原始指令
In SSMS, use the Always Encrypted wizard to encrypt column dbo.Patients.SSN (deterministic) and dbo.Patients.Notes (randomized) in database 'Clinic' with a new column master key in the local Windows certificate store, then confirm the app query in fixtures/app_query.sql still works with column encryption enabled.
- 平台
win· Windows- 应用
- SSMS · SQL Server
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击- 工作产物
WP08· 应用 / 系统状态- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
Grader: sys.columns encryption_type 1 for SSN, 2 for Notes; CMK and CEK metadata present; raw SELECT without column encryption returns varbinary; with Column Encryption Setting=Enabled returns plaintext equal to original; row count unchanged.
建议部分得分
40% columns encrypted with correct types; 20% keys; 30% data round-trips; 10% app query works
R03.01.01.02-T18 Diff two schema versions and apply a migration in DataGrip
原始指令
Compare PostgreSQL databases 'app_v12' and 'app_v13' in DataGrip, review the differences, and generate a migration script that brings a fresh copy of v12 to v13 without dropping data in renamed columns. Apply it to 'app_dev' (a v12 copy) and save the script to output/migrate.sql.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- DataGrip · PostgreSQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击vis· 视觉判断code· 代码与命令- 工作产物
WP06· 代码 / 工程- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader: schema of app_dev equals app_v13 (pg_dump --schema-only normalized diff empty); data in 2 renamed columns preserved (not drop+add); migrate.sql reproduces the result on another v12 copy.
建议部分得分
40% schema equality; 35% renamed data preserved; 25% script reproducible
R03.01.01.02-T19 Find the top statements by latency in MySQL Workbench
原始指令
Using MySQL Workbench's Performance Reports on the sandbox MySQL 8 server under the provided load, identify the 3 statement digests with the highest total latency and the table each mostly scans. Write output/top3.json with digest text, total latency, and table.
- 平台
mac· macOSwin· Windows- 应用
- MySQL Workbench · MySQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击vis· 视觉判断- 工作产物
WP12· 答案 / 决策- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader compares to performance_schema.events_statements_summary_by_digest snapshot taken during the load window: same 3 digests in order; latency within 5%; tables match seeded slow queries.
建议部分得分
50% digests and order; 25% latency values; 25% tables
R03.01.01.02-T20 Migrate a MySQL database to PostgreSQL with DBeaver
原始指令
Migrate sandbox MySQL database 'legacy_crm' (41 tables, enums, tinyint booleans, zero dates, auto_increment) to PostgreSQL 'crm' using DBeaver's data transfer. Fix type mappings, sequences and constraints, then verify row counts and per-table checksums, recording them in output/verify.csv.
- 平台
mac· macOSwin· Windowslin· Linux- 应用
- DBeaver · MySQL · PostgreSQL
- 交互表面
desk· 桌面 GUI- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
click· 指向与点击code· 代码与命令vis· 视觉判断- 工作产物
WP08· 应用 / 系统状态- 条件
long· 长流程multiapp· 多应用- 提案难度
- 5 / 5
建议验证方式
Grader: 41 tables exist with mapped types per docs/type_map.md; row counts and normalized checksums equal source; sequences set to max(id)+1; FKs and unique constraints present; zero dates mapped to NULL. verify.csv matches grader's numbers.
建议部分得分
35% data equality; 20% type mapping; 35% sequences and constraints; 10% verification file
已有研究映射
以下沿用研究目录中的基准与小类映射，不是逐题标注、执行记录或产品背书。