What is a Database:
Unique constraint:
The UNIQUE constraint uniquely identifies each record in a database table. A
PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. A
table can have many UNIQUE constraints, but only one PRIMARY KEY constraint.
What is Index:
What is Clustering:
Database is a collection of related Data. The data is nothing but physical or logical
facts that can be recorded and can have implicit meaning. For example name and
address of all the friends. This is a collection of related data with an
implicit meaning and hence is a database.
Why use Database:
Using database duplication of data can be reduced.
Inconsistency can be avoided. Data can be shared. Security restriction can be
enforced. Integrity can be maintained. Data independence can be provided.
Conflicting requirements can be balanced.
Explain Database
Architecture:
It has 3 a layered architecture. First one is External level
which is nothing but front end applications. Second one is logical level which
is nothing but tables. Third one is internal level which is nothing but files
and physical storage.
Figure 1. Database
System
What is DBMS: Database
management system is a collection of programs that facilitate the process of
defining, constructing and manipulating database. For example Excel, spread sheets.
What is RDBMS: Relational
database management system is a DBMS in which data is stored in tables and the
relationships among the data are also stored in tables. RDBMS is based on the relational
model as introduced by E. F. Codd. Any DBMS which satisfies 6 or more Codd’s
rule is considered as RDBMS. Relational database do not allow repeating.
Important Keywords:
What Data Models: A data
model can be thought of as a diagram or flowchart
that illustrates the relationships between data.
What is Schema:
Schema is collection of objects i.e. table, view, procedure,
function.
What is Catalog:
Consists of metadata in which definitions of database objects
such as base tables, views (virtual tables), synonyms, value
ranges, indexes, users, and user groups are stored.
What is Relation:
Relation is a table.
What is Tuple:
Tuple is a row or record in a table.
What do you mean by Degree:
Degree is number of attributes in a table.
What is Cardinality:
Cardinality is number of rows in a table.
What is a Primary key:
Primary key is a unique identifier for the table.
What is a Candidate Key:
Two or more attributes or combinations of attributes that
uniquely identify an instance of entity set are called candidate keys.
What is a Secondary Key: An
attribute or combination of attributes that may not be a candidate key but
classifies the entity set on a particular characteristic.
What is Entity: An entity is an object that exists and is
distinguishable from other
Object e.g. employee, students, event, company etc.
What is Entity Set:
A group of similar entities makes an entity set e.g. all
employees, all automobile companies etc.
What is Attribute:
Attributes are the properties that characterize an entity
set e.g. some of these properties for employee entity set are name, address,
skills etc. Attribute is a column or
field in a table.
What is Relationship:
Relationship is an association between entities.
1:1 Relationship:
Every row in one table corresponds to one row in another
table e.g. Student : IDCard.
1:M Relationship :
Every row in one table corresponds to too many rows in
another table e.g. Student: Course.
M:M Relationship:
Many rows in one table correspond to too many rows in
another table e.g. Student: Tests.
What is E-R Diagram:
ER diagram is the graphical representation of entity
relationship.
What is Normalization:
The process of organizing data to minimize
redundancy is called normalization.
Normalization is refinement of ER Model which improves database design, reduces
redundancy of Data. EF Codd, the
inventor of the relational model, introduced the concept of normalization.
There are 6 normal forms defined. A relational database table is often
described as "normalized" if it is in the Third Normal Form.
1NF:
A database is in 1NF if and only if all the
tables contain scalar values only, that means it does not have non repeating
columns in any table.
2NF:
A database is in 2NF if and only if it is in
1 NF and every non key column is fully dependent on the primary key of the
table.
3NF:
A database is in 3NF if and only if it is in
2 NF and every non key column is non
transitively dependent on the primary key of the table , that means all the non key columns are independent
of all other non key columns of the same table in a database.
4NF:
A database is in 4NF if and only if it is in 3 NF and no table contain 2 or
more 1:M or M:M relationships that are not directly related .i.e. if a column is
not directly related to other columns in
the table then we need to move the
column to new table.
5NF:
A database is in 5NF if and only if it is in
4 NF and the related multiple relationships within a database is isolated i.e. if several complex relationship exists in the
database , we need to separate each of the relationship into its own table.
What is De-Normalization:
De-normalization is process of moving
from higher to lower normal forms of database modeling. This is generally
done to improve the performance of the database.
What is SQL:
SQL is structured query language is the standard relation database
language supported by almost all the RDBMSs.
Originally spelled as SEQUEL, developed at IBM research lab
almost all the RDBMSs support SQL to interact with the data stored in the
database.
SQL Parts:
DDL: Data
definition language includes commands for defining, deleting and modifying
database objects like tables, indexes and views e.g. create, alter, and drop.
DML: Data
manipulation language includes commands for inserting, deleting, modifying and
retrieving rows in the tables e.g. Select, update, delete, Insert.
DCL: Data control
language includes commands for specifying access rights to database objects and
controlling transactions e.g. Grant , Revoke, Commit, Rollback.
What are DataTypes:
The major data types in a Database are Char, Varchar,
Integer, Long, Date, Datetime.
What is Constraints:
Constraints limit the values entered into the table. There
are generally 5 types of constraints namely Unique, Not null, primary key,
foreign key and check constraints.
Not Null:
This constraint specifies that a column can not contain null
values. To satisfy these constraints, every row in table must contain a value
from the column.
Foreign key:
A foreign key is a column or set of columns those points to the primary key
of another table. The aim of the foreign key is to ensure referential integrity
of the data.
Check constraint:
The CHECK constraint is used to limit the value range that can be placed in
a column.Unique constraint:
What is Index:
Index help in faster retrieval of data. There are few
index types commonly used are Bitmap index, B+ tree Index, Dense index , Sparse
index and Reverse index.
What is Clustering:
Cluster is group of rows from separate tables stored in the
same disk block.
SQL Commands:
Insert:
Insert:
Insert command is used to add rows to a table or view.
Update:
Update command is used to change existing values in a table
or view.
Delete:
The delete command is used to remove rows from a table or
view.
Query:
It is a command to manipulate data in the database.
Select:
The Select command extracts data from the database based on
the conditions specified.
What are Joins:
Join is a multiple table query. It combines two or more
relations or tables to form a single new relation on the basis of common values
in a common column for extraction of data.
There 4 types of join generally used are Inner join (a . Equi Join b. natural Join),
Cross Join, Outer join (a. left outer join b .Right outer join 3. Full outer
join) and Self join
What is Subquery : The
result of one query is substituted dynamically in the result of the another query
. There are 2 types of sub queries namely nested subquery and Correlated sub
query.
What is Nested Subquery: The
Inner query does not depend upon the outer
query but outer query depends on the inner query. The inner query is executed once and the outer
query is executed as many times as the number of records are presents.
What is Correlated subquery: Outer
query passes the values ( number of rows) and that is substituted in the Inner
query where clause condition. The inner query result is substituted in the
outer queries where condition clause. The Inner query depends upon the outer
query and vice versa.
What is View:
A view is copy of a table or a set of tables, which acts as
virtual table and doesn’t occupy storage space.
What is Transactions:
A database transaction is a logical unit of database
operations which are executed as a whole to process user requests for
retrieving data or updating the database.
What is Commit:
A commit statement in SQL ends a transaction
within a relational database management system (RDBMS) and makes all changes part
of database and visible to other users.
What is Rollback:
The rollback statement in SQL cancels the proposed changes
in a pending database transaction
What are Null Values: Any unknown
value of 1 byte memory.
What is Trigger:
Trigger is a unit of database operation automatically
executed/ triggered in response to certain events like delete, update and
insert on a table or view. There manyly 3 trpes of triggers namely insert
trigger, update trigger and delete trigger.
What is Stored procedure:
A stored procedure is a unit of database operation with an
assigned name that's stored in the database in compiled form so that it can be
shared by a number of programs.
What is Cursors:
Cursors are database objects used to traverse the results of
an SQL query. They point to a certain location within a record set and allow
the operator to move forward through the results one record at a time.
What are Codd’s rule: A
set of 13 rules:
RDBMS is based on the relational
model as introduced by E. F. Codd. Any DBMS which satisfies 6 or more Codd’s
rule is considered as RDBMS.
0. The system must qualify as relational, as a
database, and as a management system.
- Information representation i.e representation of records in a table.
- Guaranteed access . We can access any record using primary key.
- Systematic treatment of NULL values. We can assign null values to any table.
- Integrity: we can assign constraints on table.
- Data descriptive rule.
- View updating.
- Physical data independence.
- Logical data independence.
- Distributive independence.
- Comprehensive data sub language.
- High level update, insert ,delete.
- Nonsubversion rule.
Reference:
No comments:
Post a Comment