Question

This is for database system design. Will give thumbs up! Thanks

Q10.6    What activities are encompassed by database administration?

Q10.7    Give several examples of database system maintenance activities.

Q10.8    What is a materialized view?

Q10.9    What is a data dictionary?

Q10.10  List actions on which access privileges can be assigned.

Answer 1

Q10.6 What activities are encompassed by database administration?

Ans Systems database focus on the physical aspects of database administration such as DBMS installation, configuration, patching, upgrades, backups, restores, refreshes, performance optimization, maintenance and disaster recovery.

The role may include capacity planning, installation, configuration, database design, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery.

Q10.7    Give several examples of database system maintenance activities.

Ans Database Maintenance program has four main categories:

1. Index Defragmentation
2. Log File Maintenance
3. File/Data Compaction
4. Integrity Check

1. Index Defragmentation:

SQL databases contain objects called ‘indexes’ that work a lot like the Dewey Decimal system of a library. Each table inside of your Office Tools Professional database contains at least one indexed column that we use when we need to search for a specific value. Indexes store a sorted and partitioned copy of that data in an ultra-fast section of memory, so when ask SQL server for some specific data, the database first scans the indexes for ‘Joe Demo’ to get all the row numbers for Joe’s time cards, then retrieves just those records for the application. Without an index, the database would have to read each and every single record in the table to find all the records. A well-designed index can improve the performance of a query by 1,000 fold or more.

One of the things that happens to indexes are that they get fragmented, just like your hard disk does. So rather than the index being in one continuous part of memory, it is divided into numerous different parts (sometimes THOUSANDS of parts) and stored wherever there is space. And just like when your computer’s hard disk is fragmented, your database becomes extremely lethargic. In order to fix this fragmentation, our maintenance plan evaluates each index for it’s size, use, and fragmentation level and will either perform a INDEX_REORGANIZE, an INDEX_REBUILD_ONLINE, or even an INDEX_REBUILD_OFFLINE depending how bad the index is.

2. Log File Maintenance

SQL Databases contain a ‘Log File’ that keeps track of every single transaction that happens in the database. Using these log files, you can actually restore a database to the state it was at any point in time before. So if something bad happens to your database (either corruption, accidentally, or maliciously), you can recover your data as it was the instant before the event happened. So the Log files are extremely important parts of the system, and they require their own special maintenance as well.

Log files automatically grow as you add more and more data to your database. Each time the file grows, it creates a ‘Virtual Log File’ (VLF) inside that it uses to store data. Using these VLFs the SQL Server is able to find a single operation in the log file much, much faster than if it had to scan the entire log file. The downside to this is that if there are too many VLFs, the time it takes to insert data into the log file can actually increase, which harms performance. Part of our maintenance routine is to scan these VLFs and see if they are hurting performance. If the maintenance routine determines that they are, then we adjust several factors of the log file, including how large each ‘increment’ is when the log file grows. We can then compact these VLFs together to regain our lost performance (and without losing any log data).

3. File/Data Compaction

As you use your SQL database, the file size both grows and shrinks. Each time the file needs more space, it grows by a certain increment. And as data is added to the database (as a chunk), it is saved into any spot that has enough memory to store that chunk. Unfortunately, the data is not always stored close to other data from the same table. The data itself becomes fragmented, just like the indexes we talked about before. In order to resolve this issue, we run what is called a ‘Data Compaction’, which reorganizes the file by grouping all associated data together. This not only groups data together, but it can also free up space inside the file as well, which can then be reclaimed by the operating system as free disk space on your hard disc.

4. Integrity Check

Over time, your database will go through numerous changes. Data will be added and removed, tables will be added, modified, and removed. The database’s overall schema will change. Indexes will be added, rebuild, deleted, and re-created. Data columns will be added, modified, and removed. The database will go through thousands upon thousands of changes just as a natural course of its lifespan. But each change, no matter how small, has the potential to introduce corruption into the database. Indexes can have corrupt pages, table can have bad records, the schema can contain inaccurate references. These different types of corruption can cause anything from simple performance issues to complete schema failure and catastrophic data loss. But this is where the Integrity Check comes into play. The Database Integrity Check examines and analyzes the database in its entirety, and can detect and repair most any corruption it comes across. The database integrity check should be run on a regular, reoccurring, weekly schedule. The Integrity Check is your best weapon to prevent catastrophic data loss. WorkSpace has a built in Integrity Check that any OTP user with Admin rights can run on demand. If any corruption was detected or repaired, WorkSpace will immediately inform the user of the details, severity, and actions taken. In nearly all cases, that is where the corruption will end, and there will be no need for further action.

Q10.8    What is a materialized view?

Ans: A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables or detail tables .

A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data

Q10.9    What is a data dictionary?

Ans: A data dictionary is a file or a set of files that contains a database's metadata. The data dictionary contains records about other objects in the database, such as data ownership, data relationships to other objects, and other data. The data dictionary is a crucial component of any relational database.

Q10.10  List actions on which access privileges can be assigned.

Ans: ACCOUNTING_DISPLAY

ADD_MULTI_ITEMS

ADD_SPECIAL_REQUEST

RESTRICT_INV

CHANGEREQBU

ALLOW_HOLD

MULTI_COMMENTS