Complied By:Pritish Acharya Technical trainer Arifin Pvt.

Ltd

WHAT IS A DATABASE AND A DATABASE SYSTEM?? A DATABASE IS DEFINED AS A COLLECTION OF DIFFERENT DATA WHICH CAN BE USED FOR ONE OR MULTIPLE APPLICATIONS WHERE AS THE COMPLETE DATABASE WITH ALL THE HARDWARE AND SOFTWARE IS CALLED A DATA BASE SYSTEM

Entity: Object, Concept or event (subject) Attribute: a Characteristic of an entity Row or Record: the specific characteristics of one entity Table: a collection of records Database: a collection of tables

WHAT ARE THE DIFFERENT ADVANTAGES OF DATABASE OVER FILE SYSTEM

REDUCED REDUNDANCY OF DATA HIGHER DATA SECURITY BETTER DATA INTIGRITY SHARING OF DATA CONTROLLED INCONSISTENCY ENFORCED STADARDS

WHAT ARE DIFFERENT LEVELS OF DATA ABSTRACTION INTERNAL LEVEL OR PHYSICAL LEVEL CONCEPTUAL LEVEL EXTERNAL LEVEL

WHAT ARE THE DIFFERENT DATA MODLES RELATIONAL MODEL HIERARCHICAL MODEL NETWORK MODEL

Step 1: Determine the entities involved and create a separate table for each type of entity (thing, concept, event, theme) and name it.(DATAMODLE CREATION) Step 2: Determine the Primary Key for each table.(ATRIBUTE MODLE CREATION) Step 3: Determine the properties for each entity (the non-key attributes).(BUSINESS RULES) Step 4: Determine the relationships among the entities.(PHYSICAL LAYER CREATION)

Identify Candidate Entities Identify Relationships Define Entities & Relationships Review Entity-Relationship Model

List Candidate Attributes for each Entity Add KEYS to model Attribute & Normalize Model Define Attributes Review Logical Model

Review & Verify Cardinalities Define Referential Integrity Identify Business Domains Identify Attribute Default Values

Select Target DBMS Name Tables & Columns Name & Define Indexes Define Columns Verify/Update Triggers Generate Reports & Document Design

Verify Entities & Definitions Verify Relationships & Definitions Verify Attributes & Definitions Verify Business Constraints Approve Schema Design

Refers to the most common Database Operations:

Create Update Read Delete

Operations occur at all levels: Tables, Records, Columns

PRIMARY KEY CANDIDATE KEY ALTERNATE KEY FOREIGN KEY REFERIANTIAL INTEGRITY

Referential integrity concerns two or more tables that are related. Example: IF table A contains a foreign key that matches the primary key of table B THEN values of this foreign key either match the value of the primary key for a row in table B or must be null.

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries.

UNION UNION ALL INTERSECT MINUS

All rows selected by either query. All rows selected by either query, including all duplicates. All distinct rows selected by both queries. All distinct rows selected by the first query but not the second.

What is a Relational Database?

A relational database stores all its data inside tables, and nothing more. All operations on data are done on the tables themselves or produces another tables as the result. You never see anything except for tables.

Normalization: a process for analyzing the design of a relational database

Database Design - Arrangement of attributes into entities

It permits the identification of potential problems in your database design Concepts related to Normalization:
KEYS and FUNCTIONAL DEPENDENCE

Sample Student Activities DB Table Poorly Designed

Non-unique records
John Smith

Test the Design by developing sample reports and queries

Created a unique ID for each Record in the Activities Table Required the creation of an ID look-up table for reporting (Students Table) Converted the FlatFile into a Relational Database

Wasted Space Redundant data entry What about taking a 3rd Activity? Query Difficulties trying to find all swimmers Data Inconsistencies - conflicting prices

Students table is fine Elimination of two columns and an Activities Table restructuring, Simplifies the Table BUT, we still have Redundant data (activity fees) and data insertion anomalies.

Problem: If student #219 transfers we lose all references to Golf and its price.

Modify the Design to ensure that every nonkey field is dependent on the whole key Creation of the Participants Table, corrects our problems and forms a union between 2 tables.

This is a Better Design!

Single Row Definition of the Three Normal Forms

1. All column values are atomic 2. All column values depends on the value of the primary key 3. No column value depends on the value of any other column except the primary key.

What are the values stored by any DBMS for DATE Datatype

century, year, month, date, hour, minute, and second.

Define Large Object (LOB) Datatypes

The built-in LOB datatypes BLOB, CLOB, and NCLOB (stored internally) and BFILE (stored externally), can store large and unstructured data such as text, image, video, and spatial data up to 4 gigabytes in size.

What is E-R model?

This data model is based on real world that consists of basic objects called entities and of relationship among these objects. Entities are described in a database by a set of attributes.

What is Relational Algebra?

It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

What is durability in DBMS?

Once the DBMS informs the user that a transaction has successfully completed, its effects should persist even if the system crashes before all its changes are reflected on disk. This property is called durability

Atomicity
Either all actions are carried out or none are. Users should not have to worry about the effect of incomplete transactions. DBMS ensures this by undoing the actions of incomplete transactions

What are the primitive operations common to all record management systems (DBMS/RDBMS)

Addition, deletion and modification.

What are the unary operations in Relational Algebra?

PROJECTION and SELECTION

CREATE TABLE SALESREP

Item definition expression(s)
{item, type, (width)}

DELETE table
WHERE expression

SELECT list FROM table WHERE condition list - a list of items or * for all items
WHERE - a logical expression limiting the number of records selected can be combined with Boolean logic: AND, OR, NOT ORDER may be used to format results

SET item = expression WHERE expression INSERT INTO table VALUES ..

What are the four Oracle system processes that must always be up and running for the database to be useable
The four Oracle system processes that must always be up and running for the database to be useable are DBWR (Database Writer) LGWR (Log Writer) SMON (System Monitor) PMON (Process Monitor).

Name the three major set of files on disk that compose a database in Oracle

Database files Control files Redo logs

What is ROWID
The ROWID is a unique database-wide physical address for every row on every table. Once assigned (when the row is first inserted into the database), it never changes until the row is deleted or the table is dropped.

What is database Trigger?

A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table.

The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted.

What is DBMS?
It is a collection of programs that enables user with the processes of defining, constructing and manipulating the database for various applications.

What is a Database system? The database and DBMS software together is called as Database system.

What are the Advantages of DBMS?

Redundancy is controlled. Unauthorised access is restricted. Providing multiple user interfaces. Enforcing integrity constraints. Providing backup and recovery.

What is extension and intension?

Extension - It is the number of tuples (records) present in a table at any instance. This is time dependent.

Intension - It is a constant value that gives the name, structure of table and the constraints laid on it.

Grid Computing
Applying the resources of many computers in a network to a single problem at the same time , usually a scientific or technical problem that requires a great number of computer processing cycles or access to large amounts of data. Grid computing uses software to divide and farm out pieces of a program to as many as several thousand computers.

New Features of Oracle

Flashback query (dbms_flashback.enable) - one can query data as it looked at some point in the past. This feature will allow users to correct wrongly committed transactions without contacting the DBA to do a database restore.

Ultra Search for searching databases, file systems, etc. The UltraSearch crawler fetch data and hand it to Oracle Text to be indexed. Scrolling cursor support - allows fetching backwards in a result set.
Dynamic Memory Management - Buffer Pools and shared pool can be resized onthe-fly. This eliminates the need to restart the database each time parameter changes were made. VI (Virtual Interface) protocol support, an alternative to TCP/IP, available for use with Oracle Net (SQL*Net). VI provides fast communications between components in a cluster.
Build in XML Developers Kit (XDK). New data types for XML (XMLType), URI's, etc. XML integrated with AQ.

Resumable backups and statements - suspend statement instead of rolling back immediately.

What is OLAP?
On-Line Analytical Processing Used for business intelligence applications Queries typically perform complex calculations, such as period-over-period analysis, top-N products, sales forecasting Usually held in dedicated multidimensional (MOLAP) databases, such as Oracle Express Server Preaggregated data for fast, predictable response times

Explain the difference between an explicit and an implicit lock.

Explicit Lock :- Lock is explicitly requested for a record or table. Implicit Lock :- Lock is implied but is not acquired

What is lock granularity?

Locking granularity refers to the size and hence the number of locks used to ensure the consistency of a database during multiple concurrent updates.

What is an ACID transaction

ACID means Atomic, Consistency, Isolation, Durability, So when any transaction happen it should be Atomic that is it should either be complete or fully incomplete. There should not be anything like Semi complete. The Database State should remain consistent after the completion of the transaction. If there are more than one Transaction then the transaction should be scheduled in such a fashion that they remain in Isolation of one another. Durability means that Once a transaction commits, its effects will persist even if there are system failures.