Intoduction:
A database Managment System is a collection of inter-releted data which helps in efficient retrievel,insertion and deletion of data from database and organization data in the form of tables,views,schemas,reports etc.
SQL:
Sql is a not procedure query Language.which was in intoduced by IBM in 1970.
which is used to communicate with database.
SQL is also called as sequeal or CLT(common language interface).this is only the language which can use to communicate with any RDBMS product. SQL is not case sensitive language that we can write sql predefined queries or syntexes in any case character's(either upper or lower case).every sql statement should be ends with a semicolon but it is optional in sql server.
Sub-Language:
1.Data Defination Language :
Data Defination Language helps you to define the database structure or schema.this language commands are using to define,modify and drop an object or database from the SQL server.
Commands:
1.Create
2.Alter
3.Truncate
4.Drop
2.Data Manupulation Language:
Commands:
1.Insert
2.Update
3.Delete
3.Data Query Language:
Data Query Language is used to review the data from the database.It used only one command.
Commands:
1.Select
4.Data Control Language:
DCL command are like Grand and revoke,which are useful to give "rights and permission" other permission controles paramerters of the database system.
Commands:
1.Grant
2.Revoke
5.Transaction Control Language:
TCL commands deal with the transaction within the database.
Commands:
1.Commit
2.Rollback
3.Savepoint
KEYS:
Keys in dbms is an attribute or set of attribute which help you to identify a row(tuple) in a relation.they allow you can find the relation between tables.keys is also help to finding unique record or row from the table.database key is also helpful for finding unique record or row from the table.
Types of key :
1.Primary Key
2.Super Key
3.Alternate Key
4.Candidate Key
5.Foreign Key
6.Compound Key
7.Composite Key
8.Surrogate Key
1.Primary Key :
Primary key is a column or group of columns in a table that uniquely identify every in that table.the primary key can't be a duplicate meaning the same value can't apprear more than once in a table.A table connot have more than one primary key.
2.Super Key:
A Super key is a group of single or multiple keys which identifier rows in a table.
A super key may have additinal attributes that are not needed for unique identification.
3.Alternate Key:
Alternate key is a column or group of column in a table that uniquely identify every row in that table.A table can have multiple choice for a primarhy key but only one can be set as the primary key.
4.Candidate Key:
Candidate key is a set of attributes that uniquely identify tuples in a table candidate key is a super key with no repeate attributes.Primary key should be selected from the candidate keys.Every table must have at least a single candidate key.A table can have multiple candidate key but only one a single primary key.
5.Foreign key:
Foreign key is a column that create a reletionship between two tables the purpose of foreign key is to maintain data integrate and allow navigation between two different instand of an entity.It acts as a cross reference between two tables as it reference the primary key of another table.
6.compound key:
Compound key has two or more attributes that allows you to uniquely recognice a specific recordd.It is possible that each column may not be itself within the database.
7.Composite Key:
Composite key is a combination of two or more colume that uniquely row in a table.the combination columns guarantes unequences through individually uniquely is not guaranted,how they are combined to uniquely identify record in a table.
8.Surrogate Key:
Surrogate Key is an artificial key which aims to uniquely identify each record is called a surrogate key.The kind of partial key in dbms is unique because it is created when you don't have any primary key.Surrogate key in dbms in usally an integer.a surrogate key is a value generated right before the record is inserted into a table.
Database Architecture :
DBMS 3-tier architecture divides the complete system into three interelated but independ module as below.
Types of Architecture:
1.1-Tier Architecture:
1-Tier Architecture in DBMS is the simpled architecture of DBMS in which the client,server and database all reside on the same machine.
ex-Would be anytime you install DB in your system and access in to practice SQL queries.
1.2-Tier Architecture:
1.3-Tier Architecture:
A 3-Tier Architecture in DBMS is the most popular client server architecture in DBMS in which the devlopment and maintance of function process,logic,data access,data storage and user interface is done independently as seprate modules.3-tier Architecture design is an extension of the 2-tier clien server architecture.
ex-Any large website on the internet.
Schemas:
structure of table or blueprint of table.
Types:
1.Internal Level
2.Conceptual Level
3.External Level
1.Internal Level:
Internal level schema define the physics storage structure of database.the internal schema is very low-level representation of the entire database.
'It is also called physics layer or stored record'
2.Conceptual Level:
The conceptual shema describes the database structure of the while database for the community of users. This schema hides information about the physical storage structure and focus on discribing data types,entities,relationship etc.
This logical level comes between the user loud and physical storage view hownever there is only single conceptual view of single database.
3.External Level:
Advantages:
1.Improved data sharing
2.Improved data security
3.Better data intergration
4.Improved data access
5.Improved decision making
