Friday, April 16, 2010

What is functional dependency?

Functional dependency describes the concept that all other columns in a table must depend completely on the primary key column.
For example, the values in the CompanyName column and ContactName column depend completely on the value in the CustomerID column. In other words, if we know CustomerID, then we can tell the customer's company name and contact name. This reads like CustomerID determines CompanyName and ContactName.

  • The effect of achieving functional dependency is that each related set of data is put into its own table. In other words, each table only represents one subject.
It should be noted that functional dependencies are not limited to depending on a single column. We can have a combination of several columns determine other columns in the table.

Types of Relationship Combinations

Entities are often involved in a variety of relationships. Optional relationships are often affected by the existence of another relationship. Clarify the nature of two or more relationships concerned with a particular entity by one of the following combinations: inclusive OR (either or both)
exclusive OR (either, but not both)
AND (both must exist).
Inclusive OR An inclusive OR relationship indicates that entity A is related to either entity B or entity C or both B and C. Exclusive OR An exclusive OR relationship indicates that entity A is related to either entity B or entity C but not both B and C. AND An AND relationship indicates that entity A is related to both entity B and entity C.

SQL AVG() Function

The AVG() Function
The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax:
SELECT AVG(column_name) FROM table_name;
SQL AVG() Example
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the average value of the "OrderPrice" fields. We use the following SQL statement: SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
The result-set will look like this:
OrderAverage 950Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.
We use the following SQL statement:
SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
The result-set will look like this:
Customer Hansen Nilsen Jensen

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
UCASE() - Converts a field to upper case
LCASE() - Converts a field to lower case
MID() - Extract characters from a text field
LEN() - Returns the length of a text field
ROUND() - Rounds a numeric field to the number of decimals specified
NOW() - Returns the current system date and time
FORMAT() - Formats how a field is to be displayed

SQL Functions

SQL has many built-in functions for performing calculations on data
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
AVG() - Returns the average value
COUNT() - Returns the number of rows
FIRST() - Returns the first value
LAST() - Returns the last value
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum

SQL Dates

The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.
As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.
MySQL Date Functions
Function Description
NOW() Returns the current date and time
CURDATE() Returns the current date
CURTIME() Returns the current time
DATE() Extracts the date part of a date or date/time expression EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a specified time interval to a date
DATE_SUB() Subtracts a specified time interval from a date
DATEDIFF() Returns the number of days between two dates DATE_FORMAT() Displays date/time data in different formats

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.
SQL IN Syntax
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
IN Operator Example
The "Persons" table:
P_Id    | LastName    |FirstName        |Address             |City
1        Kumar Ravi   Navi mumbai   10 kamlapark        bombay
2          walia           Ashwin            5 shivajinagar         jaipur
3          gupta           prakash         1 manit                   bhopal

Now we want to select the persons with a last name equal to “Kumar" or “gupta" from the table above.
We use the following SELECT statement:
SELECT * FROM Persons WHERE LastName IN (‘Kumar',' gupta ');

The result-set will look like this:
P_Id       |LastName    | FirstName      | Address           |City
1             Kumar          RaviNavi         mumbai10         bombay
3             gupta            prakash           1 manit              bhopal

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

What are the difference between DDL, DML and DCL commands?

DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain.
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
CREATE,ALTER,DROP AND TRUNCATE ARE CALLED DDL COMMANDS. They are called Data Definition since they are used for defining the data. That is the structure of the data is known through these DDL commands.
DCL
Data Control Language (DCL) statements. Some examples: GRANT - gives user's access privileges to database.
REVOKE - withdraw access privileges given with the GRANT command.

Entity Sets

Eg:- All employee of bank etc.

Attribute Types

When we design ER-Diagram there are several types of attributes that may be required to be dealt . Few of them are Listed below.
Simple or Atomic:-
These attributes can’t be sub-divided further . In other words , the atomic attributes hold single value and not composed of any other attributes.
Example:-
RegNo , Age, Date_Of_Birth , Dept_No , Sex etc.
Composite:-
The attributes that can be sub-dived into somemore attributes are called as composite attributes.
For example,
If u wish to store the address of an employee ,one choice is to name the attribute as address .However , an address is generally composed of House number , Cross ,Main ,Street name ,Area ,City ,State ,Country , PinCode , etc.
Composite Example
Address
HouseNo Cross Main StreetName Area City State Country PinCode
Similarly , Attribute name need not be atomic . It can have multiple Values or sub-divided.
Single valued , Multi-valued , stored ,derived , nullEntity sets, Key , Domain, composite key
Single Valued attribute.
Multi-Valued attribute.
Stored attribute.
Derived attribute.
Null .

Attributes

The Properties of an entity can be described by what is known as attributes..
Example
The Employee entity may be described by the following attributes.
Name , Age ,Salary ,Sex ,Address ,Phone etc.
The following two examples Employee entity and Book entity are shown with their attributes and value.
Employee(Name ,Salary ,Age ,Address ,Phone )
Employee(‘Deepak’,3000,42,’253’,Rajajinager,’Bangalore’,’5567234’)
Book( BookId , ‘ Rdbms ‘, ’S.Nandagopalan’,160.00)

Entities

An Entity is anything that exits in a real world with an independent existence.
If you consider the Company Database ,an entity which comes to our mind is Employee. Similarly , if we think of the problem like Library , obviously book is an entity.
Examples:- Employees ,Car ,Book , Department.

Purpose of Database System/Drawbacks of using file systems

In the early days, database applications were built on top of file systems
Drawbacks of using file systems to store data:
Data redundancy and inconsistency.
Multiple file formats, duplication of information in different file Difficulty in accessing data
Need to write a new program to carry out each new task
Data isolation — multiple files and formats
Integrity problems
Integrity constraints (e.g. account balance > 0) become part of program code
Hard to add new constraints or change exist
Atomicity of updates
Failures may leave database in an inconsistent state with partial updates carried out
E.g. transfer of funds from one account to another should either complete or not happen at all
Concurrent access by multiple users
Concurrent accessed needed for performance
Uncontrolled concurrent accesses can lead to inconsistencies
E.g. two people reading a balance and updating it at the same time
Security problems
Database systems offer solutions to all the above problems

Data Definition Language (DDL) in oracle

Specification notation for defining the database schema
E.g. create table account ( account-number char(10), balance integer);
DDL compiler generates a set of tables stored in a data dictionary
Data dictionary contains metadata (i.e., data about data)
database schema
Data storage and definition language
language in which the storage structure and access methods used by the database system are specified
Usually an extension of the data definition language

Data Manipulation Language (DML) in oracle

Language for accessing and manipulating the data organized by the appropriate data model
DML also known as query language.
Two classes of languages
Procedural – user specifies what data is required and how to get those data
Nonprocedural – user specifies what data is required without specifying how to get those data
SQL is the most widely used query language

ADVANTAGES OF USING A DBMS

  • Controlling Redundancy.
  • Restricting Unauthorized Access(security).
  • Good GUI.
  • Enforcing Integrity Constraints.
  • Providing Backup and Recovery.
  • Efficient Data Access.
  • Application Development made easier.
  • Flexibility.
  • Reduced Application Development Time.

Difference between DBMS and a Database?

The database holds the records, fields, cells ..with data.
The DBMS is the "tools" used to manipulate the data in the database. An example of a DBMS is SQL Server or Oracle. The DBMS is the database engine itself. This is the core, the actual program that contains the logic to execute your SQL statements, performs that actual data access based upon these commands , whereas DS is linked with improving the speed of traversing and searching the data in main memory. As we all know that the processor can only process the data that is available in main memory. Therefore the different models used to organize data in main memory are collectively referred as DS.

What is DBMS?

Collection of programs that enables users to create and maintain a database or access data.
DBMS contains information about a particular enterprise.
DBMS provides an environment that is both convenient and efficient to use.
Database Applications:
Banking: all transactions
Airlines: reservations, schedules
Universities: registration, grades
Sales: customers, products, purchases
Manufacturing: production, inventory, orders, supply chain
Human resources: employee records, salaries, tax deductions
Databases touch almost all aspects of our lives.
Examples: Oracle,M.S Sql Server 2005,Mysql etc
Database
It is a Collections of related data items.
Here related data may represent name , telephone , number, address of a person or company etc. or it may be the account number ,name of the account holder and current balance in the banking system.
When collection of data is not properly related ,then it may not be called as a database.
Generally , there is no restriction on the size and complexity of the database.