Security

Encryption: FIPS (Federal Information Processing Standard) or Common Criteria, two internationally recognized security standards.

Managing the RDBMS

A single logical operation on the data is called a transaction.

ACID:

• Atomicity requires that database modifications must follow an “all or nothing” rule.
• Consistency enforces that the database remains in a consistent state Example: referential integrity implemented by Propagation constraints.
• Isolation ensures that other operations cannot access data that has been modified during a transaction that has not yet completed. A dirty read means that a transaction is allowed to read, but cannot modify, the uncommitted data from another transaction.
• Durability is ability of the DBMS to recover the committed transaction updates against any kind of system failure. Usually, transaction logs are used for this.

Backing up a Database:

1. Physical backup
   1. Cold backup after shutting down the database. Shutting down period may be long.
   2. Hot backup while database is online. May not allow transaction log to play against a backup taken this way.
2. Logical backup

• • Can`t do point-in-time recovery.
  • Referential Integrity may be lost   as loading needs information from another table.
  • Database have to be offline.

Data dictionary is a set of tables that provides information about the database details, basically meta-data information.

Backup creates a second copy. Data recovery restores a copy of data from back up.

Transaction log (also database log or binary log) is a history of actions executed by a database management system to guarantee ACID properties over crashes or hardware failures.

Data Normalization

• Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.
• First Normal Form means a form with a primary key, no row-ordering or column ordering as an intrinsic and meaningful aspect of the view and no nullable fields.
• 1NF table is in Second Normal Form if and only if all its non-prime attributes are functionally dependent on the whole of a candidate key. Non-prime attributes do not form part to any candidate key. When 1NF table has no composite candidate keys it automatically becomes 2NF.
• 2NF table is in Third Normal Form if and only if all its non-prime attributes are directly dependent on every candidate key.
• Anomalies: Updation has occurred in linked table but foreign key in linking table has not been updated. This creates updation and insertion anomalies. Deletion has occurred in linked table but foreign key in linking table has not been deleted. This creates deletion anomaly.

Physical and Relational Data Model

• A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. One of these candidate keys is selected as the table primary key.
• Any candidate key which is not a primary key is called an alternate key.

Primary key is a unique identifier for a table.

1. Should be unique.
2. Should not hold null (undefined).
3. Should not change value for its lifetime.

If it has more than one attribute it is called composite key.

Foreign key is a primary key in a different table. Diagrammatically, a foreign key is depicted as a line with an arrow at one end.

Fields are the columns in databases. They can also be a piece of specific information from a record.

A table is a row and column arrangement of data.

Referential Integrity says that

1. We may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
2. Cascading update has to be implemented meaning when a record in the linked table changes, all foreign keys in the Employees table must be modified using a cascading update.
3. Cascading delete has to be implemented meaning when a record in the linked table changes, all records in the linking table must be deleted using a cascading delete.

MS SQL

• They are compiled. However, If the stored procedure is not in memory/cache, it is re-compiled at run-time before it is used again and put back into the cache. If you use the stored procedure often, you can consider it to always be compiled.

Logical Data Model

• An entity type is any type of object that we wish to store data about.
• A relationship type is a named association between entities.
• Relation – A data object defined by a set of attributes. For example, “employee” is a relation with various attributes that define the employee data object. “Relation” is also called “table”.
• Attribute – A kind of information that describes one aspect of a data object. For example, “age” is an attribute of a person, and “salary” is an attribute of an employee. “Attribute” is also called “column”.
• Tuple – An instance of a data object with specific values for all attributes of the relation. For example, one tuple of the “course” relation is the operating system course with “operating system” as the value of the “course name” attribute, and other values for other attributes. “Tuple” is also called “row” or “record”.

Programming

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database

Cursors provide a mechanism by which a database client iterates over the records in a database.

Scrollable and NonScrollable (Forward one step only at a time) cursors are there.

A stored procedure is a subroutine available to applications accessing a relational database system

Two Phase Commit is the process by which a relational database ensures that distributed transactions are performed in an orderly manner. In this system, transactions may be terminated by either committing them or rolling them back.

Data Model

View

View is nothing but parsed SQL statement which fetches record at the time of execution.

There are mainly two types of views:

1. Simple View.
2. Complex View.

Also

1. Updatable Views.
2. Read only Views.

Finally, we have Materialized Views.

View is used for the purposes as stated below:

1. Security.
2. Faster response.
3. Solve complex query.

Syntax is:

CREATE or REPLACE view ([olumn1] [column2]...)
AS
SELECT column1 column2...
FROM tablename
[WHERE condition]
[WITH read only] [WITH check option]

RDBMS

• RDBMS is Relational Database Management System.
• It is a relational model.
• It is introduced by E.F.Codd.

Advantages:

1. Redundancy reduced.
2. More adaptable for change.