What is Data?
Data is a collection of facts, such as numbers, words,
measurements, observations or even just descriptions of things.
Data are plain
facts. The word "data" is plural for "datum." When data are
processed, organized, structured or presented in a given context so as to make
them useful, they are called Information.
What
is information?
Information is
data that has been processed in such a way as to be meaningful to the person
who receives it. It is anything that is communicated.
What
is database?
A database is an
organized collection of data. It is the collection of schemas, tables, queries,
reports, views, and other objects.
The term "schema" refers to the organization
of data as a blueprint of how the database is constructed (divided into database
tables in the case of relational databases).
What
is DBMS?
“A database
management system (DBMS) is system software for creating and managing
databases. The DBMS provides users and programmers with a systematic way to
create, retrieve, update and manage data.”
“A database
management system (DBMS) is a computer software application that interacts with
the user, other applications, and the database itself to capture and analyze
data.”
What
is RDBMS?
RDBMS is the
basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2,
Oracle, MySQL, and Microsoft Access. A Relational database management system
(RDBMS) is a database management system (DBMS) that is based on the relational
model as introduced by E. F. Codd.
What
is 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. 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.
Selecting Data
The select statement is used to query the
database and retrieve selected data that match the criteria that you specify.
Here is the format of a simple select statement:
select "column1"
[,"column2",etc]
from "tablename"
[where "condition"];
[] = optional
Creating Tables
The create table statement is used to create a new
table. Here is the format of a simple create
table statement:
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");
Here are the most common Data types:
|
char(size)
|
Fixed-length character
string. Size is specified in parenthesis. Max 255 bytes.
|
|
varchar(size)
|
Variable-length
character string. Max size is specified in parenthesis.
|
|
number(size)
|
Number value with a
max number of column digits specified in parenthesis.
|
|
date
|
Date value
|
|
number(size,d)
|
Number value with a
maximum number of digits of "size" total, with a maximum number of
"d" digits to the right of the decimal.
|
Inserting into a Table
The insert statement is used to insert or add a
row of data into the table.
insert into "tablename"
(first_column,...last_column)
values (first_value,...last_value);
Updating Records
The update statement is used to update or change
records that match a specified criteria. This is accomplished by carefully
constructing a where clause.
update "tablename"
set "columnname" =
"newvalue"
[,"nextcolumn" =
"newvalue2"...]
where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];
Deleting Records
The delete statement is used to delete records or
rows from the table.
delete from "tablename"
where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];
Drop a Table
The drop table command is used to delete a table and
all rows in the table.
drop table "tablename"
Keys in DBMS
Key plays an
important role in relational database; it is used for identifying unique rows
from table. It also establishes relationship among tables.
Types of
keys in DBMS
Primary
Key – A primary is a column or set of columns in a table that
uniquely identifies tuples (rows) in that table.
Super Key – A super key is a set of one of more columns
(attributes) to uniquely identify rows in a table.
Candidate
Key – A super key with no redundant attribute is known as
candidate key
Alternate
Key – Out of all candidate keys, only one gets selected as
primary key, remaining keys are known as alternate or secondary keys.
Composite
Key – A key that consists of more than one attribute to
uniquely identify rows (also known as records & tuples) in a table is
called composite key.
Foreign
Key – Foreign keys are the columns of a table that points to
the primary key of another table. They act as a cross-reference between tables.
Super Key
A Super key is any combination
of fields within a table that uniquely identifies each record within that
table.
Candidate Key
A candidate is a subset of a
super key. A candidate key is a single field or the least combination of fields
that uniquely identifies each record in the table. The least combination of
fields distinguishes a candidate key from a super key. Every table must have at
least one candidate key but at the same time can have several.
1. Candidate
Key: are individual columns in a table that qualifies for
uniqueness of all the rows. Here in Employee table EmployeeID & SSN are Candidate keys.
2. Primary
Key: is the columns you choose to maintain uniqueness in a
table. Here in Employee table you can choose either EmployeeID or SSN columns, EmployeeID is preferable choice, as SSN is a
secure value.
3. Alternate
Key: Candidate column other the Primary column, like if
EmployeeID is PK then SSN would be the Alternate key.
4. Super Key: If you add any other column/attribute to a Primary Key
then it become a super key, like EmployeeID
+ FullName is a Super Key.
5. Composite
Key: If a table do have a single columns that qualifies for a
Candidate key, then you have to select 2 or more columns to make a row unique.
Like if there is no EmployeeID or SSN columns, then you can make FullName + DateOfBirth as Composite primary Key. But still
there can be a narrow chance of duplicate row.
What is ER diagram?
An entity
relationship model, also
called an entity-relationship (ER) diagram, is a graphical
representation of entities and their relationships to each other, typically
used in computing in regard to the organization of data within databases or
information systems.
Let us now
learn how the ER Model is represented by means of an ER diagram. Any object,
for example, entities, attributes of an entity, relationship sets, and
attributes of relationship sets, can be represented with the help of an ER
diagram.
Entity
Entities
are represented by means of rectangles. Rectangles are named with the entity
set they represent.
Attributes
Attributes
are the properties of entities. Attributes are represented by means of
ellipses. Every ellipse represents one attribute and is directly connected to
its entity (rectangle).
If the
attributes are composite,
they are further divided in a tree like structure. Every node is then connected
to its attribute. That is, composite attributes are represented by ellipses
that are connected with an ellipse.
Multivalued attributes are depicted by double ellipse.
Derived attributes are depicted by dashed ellipse.
Relationship
Relationships
are represented by diamond-shaped box. Name of the relationship is written
inside the diamond-box. All the entities (rectangles) participating in a
relationship, are connected to it by a line.
Binary
Relationship and Cardinality
A
relationship where two entities are participating is called a binary relationship.
Cardinality is the number of instance of an entity from a relation that can be
associated with the relation.
·
One-to-one − When only one instance of an entity is associated with
the relationship, it is marked as '1:1'. The following image reflects that only
one instance of each entity should be associated with the relationship. It
depicts one-to-one relationship.
·
One-to-many − When more than one instance of an entity is associated
with a relationship, it is marked as '1:N'. The following image reflects that
only one instance of entity on the left and more than one instance of an entity
on the right can be associated with the relationship. It depicts one-to-many
relationship.
·
Many-to-one − When more than one instance of entity is associated with
the relationship, it is marked as 'N:1'. The following image reflects that more
than one instance of an entity on the left and only one instance of an entity
on the right can be associated with the relationship. It depicts many-to-one
relationship.
·
Many-to-many − The following image reflects that more than one instance
of an entity on the left and more than one instance of an entity on the right
can be associated with the relationship. It depicts many-to-many relationship.
Participation
Constraints
·
Total Participation − Each entity is involved in the relationship. Total
participation is represented by double lines.
·
Partial
participation − Not all
entities are involved in the relationship. Partial participation is represented
by single lines.
