SQL Server and Oracle Database Administration

Triggers are dependent objects on the table they are created for. When a table is dropped, all associated triggers are automatically dropped as well because they cannot exist without the table. This is standard database behavior across most RDBMS platforms.

To calculate the average of a column, you can either use the built-in AVG() aggregate function directly or divide the sum of the values using SUM() by the count of the values using COUNT(). Both methods produce the correct average.

The Rollup function is the standard method for calculating subtotals and aggregated values in data analysis tools, databases (especially MongoDB with $rollup), and spreadsheet applications. While other functions like SUBTOTAL exist, Rollup is the most widely applicable approach for hierarchical aggregation. Option D cannot be correct since SUBTOT and SUBSUM are not standard function names.

In Oracle Data Guard standby databases, the ARCH (archiver) process is responsible for managing archived redo log files. It registers the logfiles in the standby database's archive destination. RFS (Remote File Server) receives redo data, MRP (Managed Recovery Process) applies it, and LGWR writes redo in the primary - but ARCH handles logfile registration.

When a query needs data that's not in the buffer cache, the server process reads it from the datafile on disk into the database buffer cache in memory. DBWR writes dirty buffers to disk (opposite direction), CKPT is the checkpoint process, and PMON monitors processes - none read data files into memory. This is fundamental Oracle memory architecture.

PMON (Process Monitor) dynamically registers the instance with the listener when there's no static registration in listener.ora. When you start the listener with 'lsnrctl start', it starts successfully, and PMON automatically registers the running instance with it. This is dynamic registration - the instance will be registered despite no listener.ora entry. CJQn is the job queue process, irrelevant here.

If the database listener is terminated, existing connections continue to function unimpeded. However, new remote connections cannot be established because the listener is responsible for receiving and handshaking incoming connection requests.

Redo log files in Oracle cannot be resized dynamically while the database is running. Instead, administrators must add new log file groups of the desired larger size and drop the old, smaller groups once they are no longer active, making the statement false.

SQL Server 2005 was codenamed 'Yukon' during its development. This internal name is used by Microsoft to identify projects before release. Aegan was never used, Kilimanjaro was SQL Server 2008's codename, and MSSQL is the product name not a codename.

The Simple recovery model does not support log backups - it automatically truncates the transaction log and minimally logs most operations. Full and Bulk-Logged recovery models both support log backups to enable point-in-time recovery.

tempdb is a system database in SQL Server that is recreated every time the SQL Server service restarts. It stores temporary objects and is always empty when SQL Server starts. The master database contains system metadata, msdb stores SQL Agent jobs and other service information, and MSSQLSystemResource is a read-only database containing system object definitions - none of these are flushed on restart.

The syslogins system table in the master database stores all SQL Server login accounts, which represent server-level authentication. sysusers is a database-level table containing database users and roles, not server logins. There is no system table called sysserverusers in SQL Server.

sp_procoption is a system stored procedure that configures a stored procedure to execute automatically when SQL Server starts. Only stored procedures in the master database that are owned by dbo can be marked for startup execution. sp_serverstartup and sp_autoexecute are not valid SQL Server system procedures.

The msdb database is used by SQL Server Agent to store job information, schedules, and operational history. The sysjobs table is actually a table within the msdb database, not a database itself. master stores system-level configuration, and model is a template database used when creating new databases.