Database Design

Certain principles guide the database design process. The first principle is that duplicate information (also called redundant data) is bad, because it wastes space and increases the likelihood of errors and inconsistencies. The second principle is that the correctness and completeness of information is important. If your database contains incorrect information, any reports that pull information from the database will also contain incorrect information. As a result, any decisions you make that are based on those reports will then be misinformed.

A good database design is, therefore, one that:

- Divides your information into subject-based tables to reduce redundant data.

- Provides Access with the information it requires to join the information in the tables together as needed.

- Ensure the accuracy and integrity of your information.

- Accommodates your data processing and reporting needs.
 

The Database Design Process:

The design process consists of the following steps:

1. Determine the purpose of your database
 
This helps prepare you for the remaining steps.

2. Find and organize the information required
 
Gather all of the types of information you might want to record in the database, such as product name and order number.
 
3. Divide the information into tables
 
Divide your information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.

4. Turn information items into columns
 
Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. For example, an Employees table might include fields such as Last Name and Hire Date.

5. Specify primary keys
 
Choose each table’s primary key. The primary key is a column that is used to uniquely identify each row. An example might be Product ID or Order ID.

6. Set up the table relationships
 
Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.

7. Refine your design
 
Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed.

8. Apply the normalization rules
 
Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.

Surrogate key

A surrogate key is a substitution for the natural primary key.

It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.

Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.

It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.

Some tables have columns such as AIRPORT_NAME or CITY_NAME which are stated as the primary keys (according to the business users) but ,not only can these change, indexing on a numerical value is probably better and you could consider creating a surrogate key called, say, AIRPORT_ID. This would be internal to the system and as far as the client is concerned you may display only the AIRPORT_NAME.

A surrogate may also be called a

• surrogate key,
• entity identifier,
• system-generated key,
• database sequence number,
• synthetic key,
• technical key or
• arbitrary unique identifier.

Data Dictionary Tables and Views

Some important VIEWS that we frequently query in Oracle database as a DBA.

When DBA_.. then Datadictionary Table and V$..then it is Datadictionary View. 

DATABASE_PROPERTIES - List most of the mejor properties of any database

DBA_TABLESPACES - Complete information about tablespaces.

DBA_DATA_FILES - Complete information of the files present in the Tablespaces.

DBA_TEMP_FILES - File's information of temporary tablespace.

DBA_EXTENTS - To check the extents for a given segment.

DBA_SEGMENTS - View to get number of extents and blocks allocated to a Segment.

DBA_FREE_SPACE - Displays free extents in tablespace.

DBA_TABLES - All the information about tables can be obtained here.

DBA_OBJECTS - All the information about objects in table can be Obtained here.

DBA_INDEXES - Provides information about the indexes.

DBA_IND_COLUMNS - Provides information about the indexed columns.

V$OBJECT_USAGE - Provides information on the usage of Indexes and Tables.

DBA_CONSTRAINTS - To obtain name, type and status of all constraints.

DBA_CONS_COLUMNS- To obtain the columns in the constraints on table.

DBA_USERS - To obtain information about account status, default Tablespace for users.

DBA_PROPERTIES - View to display passwd profile information.

DBA_TS_QUOTAS - Amount of space a user can use in tablespaces.

DBA_SYS_PRIVS - Lists system privileges granted to users and roles.

V$SESSION_PRIVS - Lists the privileges that are currently available to user.

DBA_TAB_PRIVS - Lists all grants and owners of all Tables in the database.

DBA_COL_PRIVS - Lists all grants and owners of all columns in the database.

DBA_ROLES - All roles that exist in the database.

DBA_ROLES_PRIVS - Roles granted to users and roles.

DBA_SYS_PRIVS   - System privileges granted to users and roles.

DBA_UNUSED_COL_TABS - To identify tables with unused columns.

DBA_PARTIAL_DROP_TABS - To identify tables that have partially Completed DROP columns operations.

DBA_ROLLBACK_SEGS - To obtain information about all the undo segments In the database.

Information about undo segments that are offline can be seen only in this view.

V$CONTROLFILE - Lists the names and status of the control files.

V$DATABASE - Contains database information from the control files.

V$VERSION - Version numbers of core library components in oracle server.

V$INSTANCE - Displays the state of the current instance.

V$DATAFILE - Contains data file information from control file.

V$TABLESPACE - Displays tablespace information from the control file.

V$LOGFILE - Displays each redo log group, member and status of each Member.

V$LOG - Same as above.

V$THREAD - To display the current redo log group, the number of online redo log groups and current sequence number.

V$PARAMETER - Lists parameters and values currently in effect for the Session also status and location of all parameters.

V$SESSION - Lists session information for each current session.

V$SPPARAMETER - Lists the contents of SPFILE.

V$SGA - Contains summary information on SGA.

V$FIXED_TABLE - To find list of data dictionary views.

V$ROLLSTAT - Views to obtain the statistics of the undo Segments currently used by the instance.

V$ROLLNAME - same as above

V$ROLE_SYS_PRIVS - System privileges granted to roles.

V$ROLE_TAB_PRIVS - Object privileges granted to roles.

V$SESSION_ROLES  - Roles that the user currently has enabled.

V$ROLE_ROL_PRIVS - Roles that are granted to roles.

PL/SQL Objects

Anonymous Block: A block of PL/SQL code that is not stored in the database, but instead is embedded in a form, web page, or SQL script.
Procedure: A block of PL/SQL code that is stored in the database and performs a specific action.
Function: A block of PL/SQL code that is stored in the database and returns a value when called in a SQL statement.
Package: A collection of related procedures and/or functions that perform related functions.
Trigger: A block of PL/SQL code that runs whenever an INSERT, UPDATE, or DELETE activity occurs on a table. Can also be defined to run when certain database events occur.

Datablock, Extent and Segment

A segment is defined as any entity that consumes physical storage space within the database.

Each Oracle segment is made up of contiguous chunks of storage space in the database called extents. Every segment must have at least one extent, but can have as many as 2 billion extents.

Each extent is itself made up of a collection of smaller chunks of space called Oracle database blocks. The minimum size of an extent is five database blocks.

The default size of these database blocks is set at database creation, but Oracle 10g db can use multiple block sizes within one db. The common database block sizes are 2KB, 4KB, 8KB, and 16KB.

Each database block is in turn composed of one or more operating system blocks.

SQL Limitations

-SQL does not have very good mechanisms for condition testing, which would allow a SQL statement to execute if a given condition is true, but not execute if the conditionis false.
-SQL also lacks looping capabilities, the ability to perform a specific SQL action for a specified number of times before stopping.
-Finally, SQL does not offer any exception-handling capabilities;
-all errors raised by SQL statements are returned directly to the user.

Background processes

DBWN:

Writes changed datablocks from the database buffer cache back to the datafiles. Modified blocks not yet written to the datafiles are called dirty datablocks . If multiple database writers are configured (by setting initialization parameters), each process is given the name DBWn , where n is either an integer from 0 to 9 or a letter from a to j. The maximum number of database writers allowed in a single Oracle instance is 20.

The DBWn background process writes to the datafiles whenever one of the following events occurs:

--A user’s Server Process has searched too long for a free buffer when reading a buffer into the Buffer Cache.
--The number of modified and committed, but unwritten, buffers in the Database Buffer Cache is too large.
--At a database Checkpoint event. See Chapters 10 and 11 for information on checkpoints.
--The instance is shut down using any method other than a shutdown abort.
--A tablespace is placed into backup mode.
--A tablespace is taken offline to make it unavailable or changed to READ ONLY.
--A segment is dropped.

Log writer (LGWR):

Log writer Transfers data from the redo log buffer cache to the redo log files. A database has a set number of redo log files, which work cyclically. As one log file becomes full, the LGWR will switch to a new, empty log file. Once the LGWR has filled the last redo log file, it will switch back to the first log file and begin reusing them. The LGWR records redo log files frequently, when the following events occur:

• Every three seconds.
• Every time a user commits a transaction.
• When the redo log buffer becomes one-third full.
• When the redo log buffer reaches 1 MB.

Checkpoint process (CKPT):

Synchronizes all database files. Every change made to the data is given a System Change Number (SCN). CKPT updates the headers of all datafiles, control files, and redo logs with the latest SCN and a date and time stamp. In the case of failure, recovery brings all database files up to date by tracking the SCNs in the file headers.

Process monitor (PMON):
Tracks all the other background processes. If a user process fails, PMON handles the cleanup by deallocating memory back to the SGA.

System monitor (SMON):
Handles instance recovery on instance startup, cleans up temporary segments, and consolidates free space in the datafiles.

Dirty datablocks

Writes changed datablocks from the database buffer cache back to the datafiles. Modified blocks not yet written to the datafiles are called dirty datablocks .

PGA

A program global area (PGA) is a memory region that contains data and control information for a server process. It is a nonshared memory created by Oracle when a server process is started.

SGA

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:

DB_CACHE_SIZE - The size of the cache of standard blocks.
LOG_BUFFER - The number of bytes allocated for the redo log buffer.
SHARED_POOL_SIZE - The size in bytes of the area devoted to shared SQL and PL/SQL statements.
LARGE_POOL_SIZE - The size of the large pool; the default is 0.
JAVA_POOL_SIZE - The size of the Java pool.

Shared pool:
The shared pool contains shared memory constructs, such as shared SQL areas, cached SQL and PL/SQL statements. It is called the shared pool because its contents are shared by all database users. The shared pool contains the library cache, the dictionary cache, and an area for control structures and character sets.

Java pool:
Optional SGA component that caches the most recently used Java objects and application code.

Large pool:
Optional SGA component that caches data for large operations, such as Shared Server or Parallel Execution.

Streams pool:
Optional SGA component that caches data from queued message requests.

Dictionary:
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.
The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache , also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).
The other area in memory to hold dictionary data is the library cache.
All Oracle user processes share these two caches for access to data dictionary information.


Library cache:
Acts as a warehouse for database commands. When a user issues a command, Oracle looks in the library cache to see if a duplicate statement exists. If it does, the new statement is discarded, and the stored statement is used instead. This approach saves processing time.