- Data definition
- Structure or data model
- Navigational model
- SQL/ relational model
DBMS-specific Configuration and management of storage engine
Computations to modification of query results by computations, like summing, counting, averaging, grouping, sorting and cross-referencing Constraint enforcement Application Programming Interface
- Data definition language
- Data manipulation language
- Query language
Structured Query Language (SQL) being ANSI standard language updates database and commands for accessing.
- One-to-one: Single table having drawn relationship with another table having similar kind of columns.
- One-to-many: Two tables having primary and foreign key relation.
- Many-to-many: Junction table having many tables related to many tables.
- No duplicate entries
- Saves storage space
- Boasts the query performances.
CREATE TABLE [column name] ( [column definitions] ) [ table parameters]
ALTER objecttype objectname parameters.
DROP objecttype objectname.
- Dynamic: it reflects changes while scrolling.
- Static: doesn’t reflect changes while scrolling and works on recording of snapshot.
- Keyset: data modification without reflection of new data is seen.
- Implicit cursor: Declared automatically as soon as the execution of SQL takes place without the awareness of the user.
- Explicit cursor: Defined by PL/ SQL which handles query in more than one row.
Functions which operate against a collection of values and returning single value is called aggregate functions
- Only the current database can have views.
- You are not liable to change any computed value in any particular view.
- Integrity constants decide the functionality of INSERT and DELETE.
- Full-text index definitions cannot be applied.
- Temporary views cannot be created.
- Temporary tables cannot contain views.
- No association with DEFAULT definitions.
- Triggers such as INSTEAD OF is associated with views.
- INNER JOINs: Blank rows are left in the middle while more than equal to two tables are joined.
- OUTER JOINs: Divided into Left Outer Join and Right Outer Join. Blank rows are left at the specified side by joining tables in other side.
- The query optimizer is used to coordinate the study of queries with the workload and the best use of queries suggested based on this.
- Index, query distribution along with their performance is observed to check the effect.
- Tuning databases to a small collection of problem queries is also recommended.
- No indexes
- Stored procedures are excessively compiled.
- Triggers and procedures are without SET NOCOUNT ON.
- Complicated joins making up inadequately written query.
- Cursors and temporary tables showcase a bad presentation.
- User input should not be allowed while transactions.
- While browsing, transactions must not be opened of data.
- Transactions must be kept as small as possible.
- Lower transaction segregation levels.
- Least information of data must be accessed while transacting.
- SQL Server caches collected procedure or the plan of query execution and used thereafter by subsequent calls.
- An important feature in relation to performance enhancement.
- Data execution plan can be viewed textually or graphically.
- Select col1.*, col2.* from coll, col2 where coll.col1=col2.col2;
For j in (select * from col2 where col2=i.col1) loop
Results are displayed;
End of the loop;
End of the loop;
- Identify outer (driving) table
- Assign inner (driven) table to outer table.
- For every row of outer table, access the rows of inner table.
- Hash join
- Build: It is a hash table having in-memory which is present on the smaller table.
- Probe: this hash value of the hash table is applicable for each second row element.
- Sort merge join
if R1 joins with R2
next row is got R2 from the input 2
return (R1, R2)
else if R1 < style=””> next row is got from R1 from input 1
next row is got from R2 from input 2
end of the loop
- To improve query performance in situations dramatically when mostly rows which are heavily accessed are in one partition.
- Accessing large parts of a single partition
- Slower and cheaper storage media can be used for data which is seldom used.
DDL statements are interpreted and recorded in tables called metadata.
- Analysis Phase.
- Redo Phase
- Undo Phase