DataBase - RDBMS - Inshorts View

Divnesh BLOG

DBMS

Requirement	Description
Integrity	Data should be accurate e.g. my facebook profile should contain valid country name.
Availability	I should be able to access facebook and see my data at all times.
Security	Only my friends should be able to see my posts and no one else.
Independent of Application	I should be able to access the same data from my Android app as well as from web browser on my laptop.
Concurrent	All my friends should be able to see my posts at the same time.

Data is stored in flat files and can be accessed using any programming language. Limitations:

1. Dependency of program on physical structure of data

2. Complex process to retrieve data

3. Loss of data on concurrent access

4. Inability to give access based on record (Security)

5. Data redundancy

Database

DBMS is an interface between Database application and database.

A Database is a shared collection of logically related data and description of these data, designed to meet the information needs of an organization

A Database Management System is a software system that enables users to define, create, maintain, and control access to the database.(Collection of program to access data) Database Systems typically have high cost and they require high end hardware configurations.

An Application Program interacts with a database by issuing an appropriate request (typically a SQL statement)

Types of Database:

Hierarchical (Tree)
Network (Graph)
Relational (Tabular)
NoSql (Key-Value pair, Graph, document)

Relational Databases:

Store data in relations i.e. tables

Set of permitted value: Domain i.e., Dept (ICP, ETA, IVS)

Integrity Types	Definition	Enforced Through
Entity Integrity	Each table must have a column or a set of columns through which we can uniquely identify a row. These column(s) cannot have empty (null) values.	PRIMARY KEY
Domain Integrity	All attributes in a table must have a defined domain i.e. a finite set of values which have to be used. When we assign a data type to a column we limit the values that it can contain. In addition we can also have value restriction as per business rules e.g. Gender must be M or F.	DATA TYPES, CHECK CONSTRAINT
Referential Integrity	Every value of a column in a table must exist as a value of another column in a different (or the same) table.	FOREIGN KEY

Candidate Key

It is a minimal set of columns/attributes that can be used to uniquely identify a single tuple in a relation. Candidate Keys are determined during database design based on the underlying business rules of the database

Key	Rationale
EmployeeNo	This seems to be a good candidate key as companies usually issue a unique number for each employee.
AadharNo	This seems to be a good candidate key for a company based in India. However we have assumed that every employee has an Aadhar number which may not be true. In addition for a multinational firm with employees across the globe this will not work at all.
Name, DateOfBirth	This might work for a small firm of 10 people as a combination of Name and Date of Birth is likely to be unique.
Salary	This is not a good candidate as salary is generally paid uniformly to people at same level.
EmployeeNo, DateOfBirth	It is not a candidate key as EmployeeNo alone is unique. By definition only minimal set of attributes can be candidate key.

Primary key

It is the candidate key that is selected to uniquely identify a tuple in a relation.

Mandatory	Desired
must uniquely identify a tuple	should not change with time
must not allow NULL values	should have short size e.g. numeric data types

Key	Rationale
EmployeeNo	Good candidate as it is numeric, cannot be null and does not change with time.
AadharNo	It will be null for people who do not have Aadhar number. Hence it cannot be considered as primary key.
Name, DateOfBirth	Both Name and DateOfBirth cannot be null. However even if uniqueness is guranteed, it is not a good choice due to large size.

When two or more columns together identify the unique row then it's referred to as Composite Primary Key. The combination of Name and DateOfBirth if selected as a primary key would be a composite primary key.

Foreign Key

A foreign key is a set of one or more columns in the child table whose values are required to match with corresponding columns in the parent table. Foreign key establishes a relationship between these two tables. Foreign key columns on child tables must be primary key or unique on the parent table. The child table can contain NULL values.

Computer is the parent table with CompId as the primary key. Employee is the child table with Id as the primary key. If we want to allocate a maximum of one computer to an employee then CompId must be made the foreign key in the Employee table. It can only contain values that are present in Computer table or no values at all. We cannot allocate a computer that does not exist to an employee.

Additionally multiple rows in the child table can link to the same row of the parent table dependening upon the type of relationship.

ER model

It is a graphical representation of entities and their relationships which helps in understanding data independent of the actual database implementation.

Term	Definition	Examples
Entity	Real world objects which have an independent existence and about which we intend to collect data.	Employee, Computer
Attribute	A property that describes an entity.	Name, Salary

Relationships

Computer is allocated to an Employee.

An Employee works in a Department while the head of department (also an employee) manages a Department.

An Employee reports to another Employee.

Cardinality of relationship

It is the number of instances in one entity which is associated to the number of instances in another. For the relationship between Employee and Computer, ie., how many computers can be allocated to an employee, can computers be shared between employees, can employees exist without being allocated a computer etc. e.g. if 0 or 1 computer can be allocated to 0 or 1 employee then the cardinality of relationship between these two entities will be 1:1.

Cardinality of relationships are of three types:

1:1 one-to-one relationship

1: N. one-to-many relationship

M: N. many-to-many relationship

Crow foot notation

SQL:

DDL - structure of the database

Non integral data types

Scale is the number of digits allowed after the decimal point.

Precision is the total number of significant digits i.e. digits both before and after the decimal point. If Scale is not provided then NUMBER datatype can be used to store integral values.

Input	Data Type	Stored Value
121.79	NUMBER	121.79
121.79	NUMBER(3)	122
121.79	NUMBER(5,2)	121.79
121.79	NUMBER(6,1)	121.8
121.79	NUMBER(4,2)	error

Miscellanies data type

Data Type	Useful for
DATE	02-JUL-81
TIMESTAMP	20-JUL-69 08:18:00.000000 PM
CLOB (Character Large Object)	Storing large character based data which cannot be stored in VARCHAR2 due to its 4000 bytes size limit.
BLOB (Binary Large Object)	Storing large binary data like movies, images with size up to 4GB.

Operations:

Operator	Symbol	Result
Addition	+	20
Subtraction	-	10
Multiplication	*	75
Division	/	3
Equal to	=	false
Not equal to	<>	true
Greater than	>	true
Greater than equal to	>=	true
Less than	<	false
Less than equal to	<=	false
Range	BETWEEN AND	Salary BETWEEN 2500 AND 3000
List	IN (List of values)	Dept IN ('IVS', 'ETA', 'ICP')
String pattern matching	LIKE	SupplierId LIKE 'S%'
NULL Test	IS NULL	Bonus IS NULL
And	AND	Salary >= 30000 AND Dept = 'ETA'
Or	OR	Salary > 75000 OR Dept = 'ICP'
Not	NOT	Id NOT IN (2,3)

CREATE TABLE and DROP TABLE

Constraints

Data integrity in database systems is enforced through constraints. constraints are typically specified along with the CREATE TABLE statement. Composite constraints can only be specified as table level constraints. We can also specify DEFAULT value for a column. Oracle database does not consider DEFAULT as a constraint.

Constraint Type	Applies On	Constraint Type	Specified
Single Column Constraint	Single Column	Column Level Constraint	With Column definition
Composite Constraint	Multiple columns	Table Level Constraint	After Column definition

TYPES:

NOT NULL
PRIMARY KEY
CHECK
UNIQUE
FOREIGN KEY

NOT NULL Constraint

NOT NULL Constraint prevents a column from accepting NULL values. NOT NULL can only be applied as a column level constraint. Constraint name is optional and it can be specified by using CONSTRAINT keyword.

CREATE TABLE Student (

StudentId INTEGER CONSTRAINT Stud_SId_nn NOT NULL,

FName VARCHAR2(10) NOT NULL,

LName VARCHAR2(10));

DEFAULT

A column can be given the default value by using DEFAULT option. The data type of column and default expression must be the same. DEFAULT option can be provided for nullable as well as NOT NULL attributes. Oracle database does not consider DEFAULT as a constraint.

CREATE TABLE Student (

StudentId INTEGER,

FName VARCHAR2(10),

DOJ DATE DEFAULT SYSDATE);

PRIMARY KEY

It ensures that the column cannot contain NULL and duplicate values. We can have only one PRIMARY KEY in a table.

Ø Table level Constraint:

CREATE TABLE Student (

StudentId INTEGER,

FName VARCHAR2(10),

ContactNo NUMBER(10),

CONSTRAINT stud_sid_pk PRIMARY KEY (StudentId));

Ø Column level Constraint:

CREATE TABLE Student (

StudentId INTEGER CONSTRAINT stud_sid_pk PRIMARY KEY,

FName VARCHAR2(10),

ContactNo NUMBER(10));

CHECK

It is used to limit the values that can be specified for a column.

CREATE TABLE Student (

StudentId INTEGER,

FName VARCHAR2(10),

Gender CHAR(1) CONSTRAINT Stud_gender_ck1 CHECK(Gender IN('M', 'F')));

UNIQUE

To ensure that two rows in a table cannnot have same value in that column. Unlike Primary Key, UNIQUE constraint allows NULL values. A table can have many UNIQUE constraints.

CREATE TABLE Student (

StudentId INTEGER,

FName VARCHAR2(10),

ContactNo NUMBER(10) CONSTRAINT Stud_cno_uk UNIQUE);

Foreign key:

It will not allow to add the student if it is not present in student table.

CREATE TABLE Marks (

CourseId INTEGER,

StudentId INTEGER CONSTRAINT marks_sid_fk REFERENCES Student(StudentId),

MarksScored DECIMAL (5, 2));

Composite Primary key:

CREATE TABLE Marks(

CourseId INTEGER,

StudentId INTEGER CONSTRAINT marks_sid_fk REFERENCES Student(StudentId),

MarksScored DECIMAL(5,2),

CONSTRAINT marks_cid_pk PRIMARY KEY(CourseId, StudentId));

ALTER TABLE

It command through which the structure of existing table can be changed without any loss of data. ALTER table can also be used to rename a column, change data type of a column and add or remove constraints.

INSERT

INSERT INTO Employee VALUES (6, 'James Potter', '01-Jun-2014', 75000.00, 1000.00, 'ETA', 'PM', NULL, 1004);

SELECT

SELECT query allows us to retrieve data from one or more tables in a relational database.

Aliases

They are used to change column names in result. They must be provided immediately after an aliased column. Both are valid SQL

SELECT Id EmpId, EName EmpName, Salary FROM Employee OR

SELECT Id AS EmpId, EName AS EmpName, Salary FROM Employee

DISTINCT

If it is used with multiple columns then two rows are considered equal only if all their columns match.

Usage of DISTINCT can be avoided- cause Performance issue.

SELECT DISTINCT Dept, Manager FROM EMPLOYEE

Trailing spaces are ignored for CHAR data type

SELECT Id, EName, Designation FROM Employee WHERE Designation = 'PM '

LIKE

SELECT ID, ENAME FROM Employee WHERE ENAME LIKE '%m%'

Displays those rows where name contains second character as ‘a’ followed by any number of characters.

SELECT ID, ENAME FROM Employee WHERE ENAME LIKE '_a%'

ORDER OF EXECUTION:

UPDATE

Update statement is used to modify existing records in a single table in a relational database. The database system ensures that no constraints are violated during execution of an update statement. Any violation of constraints results in failure of the statement.

UPDATE Employee SET SALARY = SALARY * 1.3, BONUS = SALARY * 0.30 WHERE ID = 1 or DESIGNATION = 'SE' OR DEPT = 'ETA'

DELETE

It is used to delete records

TRUNCATE statement deletes all rows from the table as it does not support WHERE clause. TRUNCATE statement is a faster option compared to DELETE when you have to delete all rows from the table

ERROR CODES

Status Code	Message	Cause
ORA-00000	Successful Completion	The statement was executed successfully.
ORA-00001	Unique constraint violation	Trying to insert a duplicate value or Updating the key value column violating unique constraint.
ORA-00904	Invalid Identifier	The sql statement might be executed on an invalid column or missing column.
ORA-0913	Too Many Values	More values might be passed than expected. Usually happens in insert statement.

SQL Functions:

All functions return a single value.

Function	Single Row Function	Multi Row Function
Returns	Single Row	Single Row
Operates On	Single Row	Multiple Rows
Used in Clauses	SELECT, WHERE, ORDER BY and HAVING	SELECT, ORDER BY and HAVING clauses

Numeric functions

They are single row functions that accept a numeric value and return numeric output.

Name	Syntax	Function
ABS	ABS(value)	Returns absolute value of a number
ROUND	ROUND(value, digits)	Rounds the value to specified decimal digits
CEIL	CEIL(value)	Rounds up the fractional value to next integer
FLOOR	FLOOR(value)	Rounds down the fractional value to the lower integer

Character functions

It work on character strings and can return a character string or a numeric value.

Name	Syntax	Function
UPPER	UPPER(value)	Converts value to upper case
LOWER	LOWER(value)	Converts value in lower case
CONCAT	CONCAT(value1, value2)	Concatenates value1 and value2
LENGTH	LENGTH(value)	Returns the number of characters in value.

Substring functions

It is used to extract part of a string. It has the following syntax SUBSTR (value, start_position, length)

Conversion functions

Use conversion functions to convert data from one format to another.

Name	Syntax	Function
TO_CHAR	TO_CHAR(value, format)	Converts a number or a date to a string. Use this function for formatting dates and numbers.
TO_DATE	TO_DATE (value, format)	Converts a string to a date.
TO_NUMBER	TO_NUMBER (value, format)	Converts a string to a number.

Eg: SELECT MinTemp, TO_CHAR(MinTemp) DEF_FORMAT, TO_CHAR(MinTemp, '999.99') "FIXED_DIGITS", TO_CHAR(MinTemp, '9,9.99') "COMMA" FROM Weather;

DATE functions

Database provides functions to determine the current time and to perform date operations like adding a specific duration to a date, finding time difference between two dates etc.

Name	Syntax	Function
SYSDATE	SYSDATE	Returns current date of System i.e. the host on which database server is installed.
SYSTIMESTAMP	SYSTIMESTAMP	Returns current timestamp of the System.
ADD_MONTHS	ADD_MONTHS(date, n)	Adds n months to the given date.
MONTHS_BETWEEN	MONTHS_BETWEEN(date1,date2)	Finds difference between two dates in months

SELECT ABS (MONTHS_BETWEEN (ADD_MONTHS('09-FEB-2014',3),'09-Mar-2015'))"MONTHS" FROM DUAL

Aggregate functions

It operate on multiple rows to return a single row. Some aggregate functions operates only on numeric columns like

SUM (total),
AVG (average)

While others operate on all data types like

MIN (lowest value)
MAX (highest value) and
COUNT (number of rows).

All aggregate functions ignore NULL values except COUNT (*).

Name	Syntax	Function
NVL	NVL(value1, value2)	Substitute’s value1 by value2 if value1 is NULL. The data type of value1 and value2 must be same.
USER	USER	Returns the current logged in user

Eg: SELECT CITY, NVL(CITY, 'Not Available') NVL_CITY, MINTEMP, NVL(MINTEMP, 0.0) NVL_MINTEMP, NVL(TO_CHAR(MINTEMP), 'Not Available') NVL_MINTEMP2 from Weather;

CASE STATEMENT

· SELECT Id, EName, Designation, Salary,

CASE

WHEN Designation = 'SE' OR Designation = 'SSE' THEN TO_CHAR(Salary * 1.2)

WHEN Designation = 'PM' AND Salary >= 90000 THEN 'No hike'

ELSE TO_CHAR(Salary * 1.05 )

END New_Salary

FROM Employee;

· SELECT Id, EName, Designation, Salary,

CASE Designation

WHEN 'SE' THEN Salary * 1.2

WHEN 'SSE' THEN Salary * 1.1

ELSE Salary * 1.05

END New_Salary

FROM Employee;

Order By clause

is used to sort the result of a query in a particular order. We all know that data in a single column can be sorted in ascending or descending order.

We can also sort data by multiple columns. In such a case data is sorted on primary (first) column first. Sorting on secondary column happens only when multiple rows have the same value in the primary column. The sort order can be different for the two columns i.e. primary can be sorted in ascending and secondary in descending and vice-versa.

ORDER BY clause must be the last clause and can be used only in SELECT statement. Rows are sorted in ascending order if sort order is not specified. DESC should be used to sort the rows in descending order.

Number 2, 3 represents column index

GROUP BY

It groups the data from the table into different groups based on criteria provided and calculates the aggregate function for each group. Thus the result has 1 row for each group.

SELECT DEPT, SUM(Salary) FROM Employee GROUP BY DEPT HAVING SUM(Salary) > 90000;

Nested aggregate function cannot be used in SELECT clause without GROUP BY clause.

GROUP BY clause is mandatory while SELECT query having one aggregate function along with other columns

UNION SYNTAX

Use UNION and UNION ALL clause to combine results from two or more SELECT statements. The select statements may be from same or different tables. They must have same number of columns and their data types at same position in both the query must be compatible (either same or convertible through automatic conversion).

UNION removes all duplicates from the result. Two records are considered duplicates if values at corresponding positions of all their columns match.

Data Types in all queries in a UNION must match position wise

JOINS

How do we fetch data from multiple tables in a single query? Let us say we want to display employee id, employee name along with computer id, model of the computer allocated to the employee in a single tabular format

ID	ENAME	DEPT	COMPID
1	James Potter	ICP	1001
2	Ethan McCarty	ETA	NULL
3	Emily Rayner	ETA	1002
4	Jack Abraham	ETA	NULL
5	Ayaz Mohammad	ICP	1003

COMPID	MAKE	MODEL	MYEAR
1001	Dell	Vostro	2013
1002	Dell	Precision	2014
1003	Lenovo	Edge	2013
1004	Lenovo	Horizon	2014

CROSS Join

It is also referred to as Cartesian product. A CROSS join with m rows in table A and n rows in table B will always produce m * n rows. Essentially it combines each row from the first table with each row of the second table.

SELECT E.ID, E.ENAME, E.COMPID AS E_COMPID, C.COMPID, C.Model

FROM Employee E CROSS JOIN Computer C – produces m*n rows

ALGORITHM:

for each row r1 in Employee table
for each row r2 in Computer table
add combined row to Result

INNER Join

It matches the records from both tables based on the join predicate and returns only the matched rows.

SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL

FROM Employee E INNER JOIN Computer C ON E.COMPID = C.COMPID

ALGORITHM:

for each row r1 in Employee table

    for each row r2 in Computer table

        if r1.COMPID == r2.COMPID

            add combined row to Result

While using Inner Joins there can be situation where you want to filter rows based on some criteria

Option 1: Using a WHERE clause

SELECT Id, EName, E.CompId AS E_CompId, C. CompId AS C_CompId, Model

FROM Employee E INNER JOIN Computer C ON E.CompId = C.CompId WHERE Dept='ETA'

The query is evaluated using a two step process:

Step 1. Two tables are joined using join condition and resultset is evaluated

Step 2. Filter condition in WHERE clause is applied on all the rows of the resultset to give the final result

Option 2: Combining with the join condition using AND operator

SELECT Id, EName, E.CompId AS E_CompId, C.CompId AS C_CompId, Model

FROM Employee E INNER JOIN Computer C ON E.CompId = C.CompId AND Dept = 'ETA'

Here the query is evaluated in a single step as the filter condition is applied right at the time of join condition evaluation.

SELF Join

Represents join of a table with itself. In this example we use inner self join to retrive employee's manager name. The Cartesian product of Employee table with itself will contain 5 x 5 = 25 rows. However only three rows have manager matching id and these appears on the result

ID	ENAME	DOJ	SALARY	BONUS	DEPT	DESIGNATION	MANAGER
1	James Potter	01-JUN-14	75000	1000	ICP	PM	NULL
2	Ethan McCarty	01-FEB-14	90000	1200	ETA	PM	NULL
3	Emily Rayner	01-JAN-14	25000	100	ETA	SE	2
4	Jack Abraham	01-JUL-14	30000	NULL	ETA	SSE	2
5	Ayaz Mohammad	01-APR-14	40000	NULL	ICP	TA	1

SELECT EMP.ID EMPID, EMP.ENAME EMPNAME, MGR.ID MGRID, MGR.ENAME MGRNAME

FROM Employee EMP INNER JOIN Employee MGR ON EMP.MANAGER = MGR.ID

for each row r1 in Employee(EMP)

    for each row r2 in Employee(MGR)

        if r1.MANAGER == r2.ID

            add combined row to Result

LEFT OUTER Join

For tables A and B will always return all records from table A even if matching record is not found in table B as per the join condition. For records where match is found the result set is exactly same as the inner join result. However for non-matching records all columns from table B appear as NULL in the result

SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL

FROM Employee E LEFT OUTER JOIN Computer C ON E.COMPID = C.COMPID

for each row r1 in Employee table

    set matched_in_comp to false

    for each row r2 in Computer table

        if r1.COMPID == r2.COMPID

            add combined row to Result

            set matched_in_comp to true

    if matched_in_comp is false

        add Employee row to Result

RIGHT OUTER Join

For tables A and B will always return all records from table B even if matching record is not found in table A as per the join condition. Right outer join is the mirror image of left join. In fact it is rarely used because the same resultset can be obtained by using a left join and reversing the order of the tables in the query.

SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL

FROM Employee E RIGHT OUTER JOIN Computer C ON E.COMPID = C.COMPID

for each row r1 in Employee table

    for each row r2 in Computer table

        if r1.COMPID == r2.COMPID

            add combined row to Result

            set matched_in_emp to true

for each row r3 in Computer table

    if not matched_in_emp is true

    add computer row to Result

FULL OUTER Join

Combines the effect of both LEFT OUTER JOIN and the RIGHT OUTER JOIN. Full Outer Join between table A and table B returns matched as well as unmatched rows from both tables. For two tables with p and q rows, a 1:1 relationship and m matched rows the total number of rows in the resultset is m + (p - m) + (q - m) = p + q - m.

SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL

FROM Employee E FULL OUTER JOIN Computer C ON E.COMPID = C.COMPID

for each row r1 in Employee table

    set matched_in_comp to false

    for each row r2 in Computer table

        if r1.COMPID == r2.COMPID

            add combined row to Result

            set matched_in_comp to true

            set matched_in_emp to true

        if matched_in_comp is false

            add employee row to Result

for each row r3 in Computer table

    if not matched_in_comp is true

        add computer row to Result

Sub query

It is a query within a query. A subquery must be enclosed in brackets and can be used in SELECT, FROM, WHERE and HAVING clauses.

Independent subquery

In an independent subquery, the inner and outer query are independent of each other ie., the inner query has no reference to the outer query. Independent subquery are further classified into single row and multiple row types depending upon the number of rows returned.

Independent subquery can be replaced by join if it is used with IN clause to fetch foreign keys from another table.

A Join cannot be replaced by a subquery if it is using columns from both the tables in SELECT clause. Attributes from subquery tables cannot be accessed in the outer query.

A Subquery must be used if value of aggregate function is required in where clause.

subqueries can be used in

Field names in the SELECT statement
The FROM clause in the SELECT statement
The HAVING clause in the SELECT statement
The WHERE clause in SELECT as well as all DML statements

Correlated subquery

A Correlated subquery is one in which the inner query that depends upon the outer query for it's execution. Specifically it uses a column from one of the tables in the outer query. The inner query is executed iteratively for each selected row of the outer query. In case of independent subquery, the inner query just executes once.

SELECT EName, Dept, Salary FROM Employee E1

WHERE Salary > (SELECT AVG(Salary) FROM Employee E2 WHERE E2.Dept = E1.Dept)

for each row in outer query table

    substitute value in inner query

    execute inner query independently

    substitute inner query by calculated value

    if where clause condition is met

        add row to result

EXISTS keyword is used to check presence of rows in the subquery. The main query returns the row only if at least one row exists in the subquery. EXISTS clause follows short circuit logic i.e. the query calculation is terminated as soon as criteria is met. As a result it is generally faster than equivalent join statements.

SELECT CompId, Make, Model FROM Computer C WHERE EXISTS (SELECT 1 FROM Employee E WHERE E.CompId = C.CompId);

NOT EXISTS is opposite of EXISTS i.e. it is used to check absence of rows in the subquery. The main query returns the row only if at least no row exists in the subquery. It also uses short circuit logic and is hence faster.

Transaction is a logical unit of work containing one or more operations on a database. A transaction provides two important functions:

Ensures that all operations within a transaction happens in an atomic manner
Provides capability to undo the partial processing in the event of failure at any step

Database provides three statements for transactions

Statement	Description
Set transaction	Initiates the transaction
Commit	Successfully completes the transaction. Actions of a transaction cannot be rolled back after commit has been executed.
Rollback	Ends the transaction after undoing all the work performed after begin transaction statement.

SET TRANSACTION;

UPDATE Acct SET Balance = Balance – 100 WHERE AcctNo = 100;

COMMIT;

The AUTOCOMMIT property of a connection controls automatic issue of commit after the operation. AUTOCOMMIT can have ON or OFF values and it depends on the default setting of the client you are using to connect to the database.

Command	Description
SET AUTOCOMMIT ON	Changes the mode of connection to ON. In this mode COMMIT command is automatically issued after every SQL statement that alters the state of a database.
SET AUTOCOMMIT OFF	Changes the mode of connection to OFF. In this mode user is expected to provide an explicit COMMIT or ROLLBACK command to complete the transaction.
SHOW AUTOCOMMIT	Display the current state of AUTOCOMMIT property.

All transactions exhibit ACID properties

Property	Description
Atomicity	All operations within the transaction must all succeed or fail.
Consistency	A transaction always moves the database from one consistent state to another. Hence all integrity and data constraints must be satisfied.
Isolation	Transactions execute in isolation of each other. In other words partial execution of one transaction is not visible to other transactions. Only committed data is visible to other transactions.
Durability	Once a transaction is committed, it is permanently saved, the data is preserved even in the case of power failure, hardware failure etc.

All transactions exhibit ACID properties

Based on the below condition we need to make the query
Query writing :

Columns	Tables	Display	Row Filter	Join	Aggregate	Group	Group Filter	Subquery	Misc.

Functional Dependency

Just like relationship between entities, attributes within an entity can be dependent on each other. These dependencies are expressed in terms of functional dependency. An attribute A is said to functionally determine attribute B if each value of A is associated with only one value of B. A is called the Determinant while B is called the Dependent.

Functional Dependency Type	Description
Full Functional Dependency	If A and B are attributes of a relation, B is fully functionally dependent on A if it is functionally dependent on A, but not on any subset of A.
Partial Functional Dependency	If A and B are attributes of a relation, B is partially dependent on A if it is dependent on subset of A.
Transitive Functional Dependency	If A, B, and C are attributes of a relation such that if A -> B and B -> C, then C is transitively dependent on A via B.

Normality in SQL:

STUDENTID	COURSE	FNAME	LNAME	MOBILE NO	HOME NO	MARKS	GRADE
1	OOP	James	Potter	111-111-1111	123-456-7890	80	B+
1	DBMS	James	Potter	111-111-1111	123-456-7890	95	A+
2	OOP	Ethan	McCarty	222-222-3222	Null	75	B
2	SE	Ethan	McCarty	222-222-3222	Null	85	A
3	PF	Emily	Rayner	333-333-3333	Null	75	B

relation R is in 1NF if and only if

Each attribute contains atomic values (that cannot be split further)
Value of each attribute contains single value from domain i.e. no multivalued attributes.

relation is in second normal form if and only if

R is already in 1NF, and
There is no partial dependency between non-key attributes and key attributes.

relation R is said to be in the Third Normal Form (3NF) if and only if :

It is in 2NF and
Transitive dependency does not exists between key attributes and non-key attributes through another non-key attribute.

Comparing Parameters	Online Transaction Processing	Online Analytical Processing
Data	Dynamic (day to day transactional/Operational data)	Static (historical data)
Purpose of data	To control and run fundamental business task	Assist in planning, problem solving and decision making
Data Atomicity	Data is stored at the microscopic level	Data is aggregated or summarized and stored at a higher level
Normalization	Normalized databases to facilitate insertion, update, and deletion	De-normalized databases to facilitate queries and analysis
Operations	Simple operations that use small amounts of data	Complex queries using large amounts of data
Updates	Updates are frequent	Updates are infrequent
Response time	Fast response time is important. Data must be up-to-date, consistent at all times	Transactions are slow. Queries consume a lot of bandwidth
Joins in queries	Joins are more and complex as tables are normalized	Joins are few and simple as tables are de-normalized

Desired features of Database.

Consistency – An end user must be able to see the latest data at all times.

Availability – Every database request must be responded by the server.

Partition Tolerance – When two systems cannot talk to each other in a network, it is called network partition. Our DB system should continue to function even if there is a network partition.

Consistency, Availability and Partition Tolerance is usually abbreviated as CAP.

Create table: with auto incremient
CREATE TABLE books (
id NUMBER(10) NOT NULL,
name VARCHAR2(45) DEFAULT NULL,
year VARCHAR2(45) DEFAULT NULL,
grapes VARCHAR2(45) DEFAULT NULL,
country VARCHAR2(45) DEFAULT NULL,
region VARCHAR2(45) DEFAULT NULL,
description blob,
picture VARCHAR2(256) DEFAULT NULL

);
ALTER TABLE books
ADD (
CONSTRAINT id_pk PRIMARY KEY (id)
);
CREATE SEQUENCE book_sequence;
CREATE OR REPLACE TRIGGER books_on_insert
BEFORE INSERT ON books
FOR EACH ROW
BEGIN
SELECT books_sequence.nextval
INTO :new.id
FROM dual;
END;

Author Details

InShortView

DataBase - RDBMS - Inshorts View

Database

Types of Database:

Relational Databases:

Candidate Key

Primary key

Foreign Key

ER model

Relationships

Cardinality of relationship

1:1 one-to-one relationship

1: N. one-to-many relationship

M: N. many-to-many relationship

Crow foot notation

SQL:

Non integral data types

Miscellanies data type

Operations:

CREATE TABLE and DROP TABLE

Constraints

TYPES:

NOT NULL Constraint

DEFAULT

PRIMARY KEY

CHECK

UNIQUE

Foreign key:

Composite Primary key:

ALTER TABLE

INSERT

SELECT

Aliases

DISTINCT

LIKE

ORDER OF EXECUTION:

UPDATE

DELETE

ERROR CODES

SQL Functions:

Numeric functions

Character functions

Substring functions

Conversion functions

DATE functions

Aggregate functions

CASE STATEMENT

Order By clause

GROUP BY

UNION SYNTAX

JOINS

CROSS Join

INNER Join

SELF Join

LEFT OUTER Join

RIGHT OUTER Join

FULL OUTER Join

Sub query

Independent subquery

Correlated subquery

Functional Dependency

Desired features of Database.

Divnesh BLOG

Featured Post

HTML cheetsheet

Popular Post

Blog Archive

Popular Posts

Labels

Blog Archive