A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.
DBMS:-It is a collection of programs that enables user to create and maintain a database. In other words it is general- purpose software that provides the users with the processes of defining, constructing and manipulating the database for various Applications.
Advantages of DBMS:-1. Redundancy is controlled.
2. Unauthorized access is restricted.
3. Providing multiple user interfaces.
4. Enforcing integrity constraints.
5. Providing backup and recovery.
III-tier Architecture :-
Three levels of data abstraction (III-tier Architecture):-
They are three levels of abstraction are given below, 1. Physical level:- The lowest level of abstraction describes how data are stored. (How to store the data?)
2. Logical level:-
The next higher level of abstraction, describes what data are stored in database and what relationship among those data. (What type of data to be stored?)
3. View level:-
The highest level of abstraction describes only part of entire database. (How to show the data?)
The people who use the database can be categorized -
a) Database users
b) Database administrator (DBA)
Database users are of 4 different types that are given below: 1) Naive users:-
These are the unsophisticated users who interact with the system by invoking one of the application programs that have been written previously. E.g. consider a user who checks for account balance information over the World Wide Web. Such a user access a form, enters the account number and password etc. And the application program on the internet then retrieves the account balance using given account information which is passed to the user.
2) Application programmers:-These are computer professionals who write application programs, used to develop user interfaces. The application programmer uses Rapid Application Development (RAD) toolkit or special type of programming languages which include special features to facilitate generation of forms and display of date on screen.
3) Sophisticated users:-These users interact with the database using database query language. They submit their query to the query processor. Then Data Manipulation Language (DML) functions are performed on the database to retrieve the data. Tools used by these users are OLAP (Online Analytical Processing) and data mining tools.
4) Specialized users:-These users write specialized database applications to retrieve data. These applications can be used to retrieve data with complex data types e.g. graphics data and audio data.
b) Database Administrator (DBA):- A person having who has central control over data and programs that access the data is called DBA.
DBA functions are given below: 1. Schema definition: DBA creates database schema by executing Data Definition Language (DDL) statements.
2. Storage structure and access method definition
3. Schema and physical organization modification: If any changes are to
be made in the original schema, to fit the need of your organization,
then these changes are carried out by the DBA.
4. Granting of authorization for data access: DBA can decide which parts
of data can be accessed by which users. Before any user access the
data, DBMS checks which rights are granted to the user by the DBA.
5. Routine maintenance: DBA has to take periodic backups of the database, ensure that enough disk space is available
to store new data, ensure that performance of DBMS ix not degraded by any operation carried out by the users.
SQL:-
SQL stands for Structured Query Language. SQL is used to communicate with a database.According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.
SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
Some common relational database management systems that use SQL are:
Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.
Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system.
However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.
This tutorial will provide you with the instruction on the basics of each of these commands as well as allow you to put them to practice using the SQL Interpreter.
Relational Model:-
The most popular data model in DBMS is the Relational Model. It is more scientific a model than others. This model is based on first-order predicate logic and defines a table as an n-array relation.
The main highlights of this model are −1. Data is stored in tables called relations.
2. Relations can be normalized.
3. In normalized relations, values saved are atomic values.
4. Each row in a relation contains a unique value.
5. Each column in a relation contains values from a same domain.
Key:-
A key is an attribute (also known as column or field) or a combination of attribute that is used to identify records. Sometimes we might have to retrieve data from more than one table, in those cases we require to join tables with the help of keys. The purpose of the key is to bind data together across tables without repeating all of the data in every table. Such an attribute is called a key attribute, and its values can be used to identify each entity uniquely. For example, the Name attribute is a key of the COMPANY entity type because no two companies are allowed to have the same name. For the PERSON entity type, a typical key attribute is SocialSecurityNumber. Sometimes, several attributes together form a key, meaning that the combination of the attribute values must be distinct for each entity. If a set of attributes possesses this property, we can define a composite attribute that becomes a key attribute of the entity type. The various types of key with e.g. in SQL are mentioned below, (For examples let suppose we have an Employee Table with attributes ̳ID‘ , ̳Name‘ ,‘Address‘ , ̳Department_ID‘ ,‘Salary‘)
(I) Super Key :-An attribute or a combination of attribute that is used to identify the records uniquely is known as Super Key. A table can have many Super Keys.
E.g. of Super Key1 ID
2 ID, Name
3 ID, Address
4 ID, Department_ID
5 ID, Salary
6 Names, Address
7 Names, Address, Department_ID ............ So on as any combination which can identify the records uniquely will be a Super Key.
(II) Candidate Key :-
It can be defined as minimal Super
Key or irreducible Super Key. In other words an attribute or a combination of attribute that identifies the record uniquely but none of its proper subsets can identify the records uniquely.
E.g. of Candidate Key1 Code
2 Names, Address
For above table we have only two Candidate Keys (i.e. Irreducible Super Key) used to identify the records from the table uniquely. Code Key can identify the record uniquely and similarly combination of Name and Address can identify the record uniquely, but neither Name nor Address can be used to identify the records uniquely as it might be possible that we have two employees with similar name or two employees from the same house.
(III) Primary Key:-
A Candidate Key that is used by the database designer for unique identification of each row in a table is known as Primary Key. A Primary Key can consist of one or more attributes of a table. E.g. of Primary Key - Database designer can use one of the Candidate Key as a Primary Key. In this case we have ―Code‖ and ―Name, Address‖ as Candidate Key, we will consider ―Code‖ Key as a Primary Key as the other key is the combination of more than one attribute.
(IV) Foreign Key :-A foreign key is an attribute or combination of attribute in one base table that points to the candidate key (generally it is the primary key) of another table. The purpose of the foreign key is to ensure referential integrity of the data i.e. only values that are supposed to appear in the database are permitted. E.g. of Foreign Key – Let consider we have another table i.e. Department Table with Attributes ―Department_ID‖, ―Department_Name‖, ―Manager_ID‖, ‖Location_ID‖ with Department_ID as an Primary Key. Now the Department_ID attribute of Employee Table (dependent or child table) can be defined as the Foreign Key as it can reference to the Department_ID attribute of the Departments table (the referenced or parent table), a Foreign Key value must match an existing value in the parent table or be NULL.
(V) Composite Key:-If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key (also called a Compound Key or Concatenated Key). E.g. of Composite Key, if we have used ―Name, Address‖ as a Primary Key then it will be our Composite Key.
(VI) Alternate Key:-Alternate Key can be any of the Candidate Keys except for the Primary Key. E.g. of Alternate Key is ―Name, Address‖ as it is the only other Candidate Key which is not a Primary Key.
(VII) Secondary Key:-The attributes that are not even the Super Key but can be still used for identification of records (not unique) are known as Secondary Key. E.g. of Secondary Key can be Name, Address, Salary, Department_ID etc. as they can identify the records but they might not be unique.
SQL Commands-:-SQL have the different type of commands that are given below -
1. DDL (Data Definition Language):-
A data base schema is specifies by a set of definitions expressed by a special language called DDL.
Example of DDL commands are given below:-
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.
2. DML (Data Manipulation Language):-
This language that enable user to access or manipulate data as organized by appropriate data model.
Procedural DML or Low level:-
DML requires a user to specify what data are needed and how to get those data.
Non-Procedural DML or High level:-
DML requires a user to specify what data are needed without specifying how to get those data.
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
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
3. 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
4. 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
5. VDL (View Definition Language) :-
It specifies user views and their mappings to the conceptual schema.
6. SDL (Storage Definition Language):-
This language is to specify the internal schema. This language may specify the mapping between two schemas.