DATABASE CONCEPT WITH MYSQL
MySQL stands for My Structured Query Language. MySQL is a Relational Database
Management System (RDBMS) type software in which data can be stored in tabular
format & all tables are linked with other tables. Any DBMS and RDBMS software has
basic features called rows, columns and tables. Row is horizontal line of table whereas
column is vertical line of table. The row of table is also called as tuple or record. The
column of table is also called as field or attribute.
A collection of rows and columns is called as table. Table is also called as entity set or
relation. An entity is real world value that specifies single value in cell. Group of entities
is called as a Table.
Degree: A Degree is total number of columns available in table.
Cardinality: A Cardinality is total number of rows available in table.
Domain: A spool (Group) of values under any column is known as Domain
RDBMS Concepts:
DBMS refers to software for storing, maintaining and utilizing databases.
Database may be defined as a collection of interrelated data stored together to serve
multiple application. A database may contain one or more tables. Tables in database
will have relationship among them, which will help to store interrelated data. This kind
of relationship in database forms RDBMS concept.
DBMS works on major limitations and disadvantages of file processing system which
are data redundancy (Duplication of data), without inconsistency, unsharable data,
unstandardized data, unsecured and incorrect data. A DBMS is an answer to all above
problems of file processing system as it provides centralized control of data.
1
,Constraints of MySQL:
1) Primary key: A primary key is a set of attributes (columns) that can uniquely
identify tuples (rows) within the relation.
A primary key is also called as super key of a relation (table). This super key will
be useful to make relationship with other table. A relation can have primary key
to more than one columns (which is called as combined primary key) but more
than one primary key to a single relation is not possible.
Primary key is a column or combination of columns which uniquely identify a
record. Only one candidate key can be primary key.
2) Unique key: A unique key ensures that no two rows have the same value in
specified column.
Difference between unique and primary key: Though both ensure unique value
for each row in a column but unique allows null values whereas primary key does
not. That is primary key is combination of two constraints unique and not null.
There can exist multiple columns with multiple unique constraints in a table but
there can exist only one column or one combination with primary key constraint.
Unique can never be super key whereas primary key can.
3) Not null key: The not null constraint will not allow null values in specific column.
4) Candidate key: All attributes combinations inside a relation that can serve as a
primary ley is called as a candidate key as they are candidates for primary key.
Candidate key is one which is capable of becoming primary key i.e. a field or
attribute that has unique value for each row in a relation whereas primary key
is designation attribute or group of attributes whose value can uniquely identify
the tuples in relation.
A candidate key can be any column or combination of columns that can qualify
as unique key in table. There can be multiple candidate keys in table.
2
, 5) Alternate key: A candidate key which is not primary key is called as alternate
key.
6) Foreign key: A non – key attribute whose values are derived from the primary
key attribute of other table is known as foreign key of current table.
Foreign key is used to represent a relationship between two tables. Foreign key
is a non – key attribute or group of non – key attributes whose values are derived
from primary key attribute (key attribute) of other table.
A table which has key attribute is called as primary key table or master table or
parent table. A table which has non – key attribute is called as secondary table
or detailed table or child table. A table which does not have key attribute is
called as weak table.
7) Default constraint: it is used to keep default value in domain to avoid null value
to be inserted.
8) Check constraint: It allows to enter only limited values within specific range that
can be inserted in table.
View: A view is temporary shortcut of any table data. View doesn’t contain any
structure of table. Table structure contains information like table columns along with
datatypes and constraints. If view will be deleted then original table will not be
affected but if table will be deleted then views do not work.
Operations on tables:
1) Select operation: It selects the specific rows from entire table. (like where
condition)
3
MySQL stands for My Structured Query Language. MySQL is a Relational Database
Management System (RDBMS) type software in which data can be stored in tabular
format & all tables are linked with other tables. Any DBMS and RDBMS software has
basic features called rows, columns and tables. Row is horizontal line of table whereas
column is vertical line of table. The row of table is also called as tuple or record. The
column of table is also called as field or attribute.
A collection of rows and columns is called as table. Table is also called as entity set or
relation. An entity is real world value that specifies single value in cell. Group of entities
is called as a Table.
Degree: A Degree is total number of columns available in table.
Cardinality: A Cardinality is total number of rows available in table.
Domain: A spool (Group) of values under any column is known as Domain
RDBMS Concepts:
DBMS refers to software for storing, maintaining and utilizing databases.
Database may be defined as a collection of interrelated data stored together to serve
multiple application. A database may contain one or more tables. Tables in database
will have relationship among them, which will help to store interrelated data. This kind
of relationship in database forms RDBMS concept.
DBMS works on major limitations and disadvantages of file processing system which
are data redundancy (Duplication of data), without inconsistency, unsharable data,
unstandardized data, unsecured and incorrect data. A DBMS is an answer to all above
problems of file processing system as it provides centralized control of data.
1
,Constraints of MySQL:
1) Primary key: A primary key is a set of attributes (columns) that can uniquely
identify tuples (rows) within the relation.
A primary key is also called as super key of a relation (table). This super key will
be useful to make relationship with other table. A relation can have primary key
to more than one columns (which is called as combined primary key) but more
than one primary key to a single relation is not possible.
Primary key is a column or combination of columns which uniquely identify a
record. Only one candidate key can be primary key.
2) Unique key: A unique key ensures that no two rows have the same value in
specified column.
Difference between unique and primary key: Though both ensure unique value
for each row in a column but unique allows null values whereas primary key does
not. That is primary key is combination of two constraints unique and not null.
There can exist multiple columns with multiple unique constraints in a table but
there can exist only one column or one combination with primary key constraint.
Unique can never be super key whereas primary key can.
3) Not null key: The not null constraint will not allow null values in specific column.
4) Candidate key: All attributes combinations inside a relation that can serve as a
primary ley is called as a candidate key as they are candidates for primary key.
Candidate key is one which is capable of becoming primary key i.e. a field or
attribute that has unique value for each row in a relation whereas primary key
is designation attribute or group of attributes whose value can uniquely identify
the tuples in relation.
A candidate key can be any column or combination of columns that can qualify
as unique key in table. There can be multiple candidate keys in table.
2
, 5) Alternate key: A candidate key which is not primary key is called as alternate
key.
6) Foreign key: A non – key attribute whose values are derived from the primary
key attribute of other table is known as foreign key of current table.
Foreign key is used to represent a relationship between two tables. Foreign key
is a non – key attribute or group of non – key attributes whose values are derived
from primary key attribute (key attribute) of other table.
A table which has key attribute is called as primary key table or master table or
parent table. A table which has non – key attribute is called as secondary table
or detailed table or child table. A table which does not have key attribute is
called as weak table.
7) Default constraint: it is used to keep default value in domain to avoid null value
to be inserted.
8) Check constraint: It allows to enter only limited values within specific range that
can be inserted in table.
View: A view is temporary shortcut of any table data. View doesn’t contain any
structure of table. Table structure contains information like table columns along with
datatypes and constraints. If view will be deleted then original table will not be
affected but if table will be deleted then views do not work.
Operations on tables:
1) Select operation: It selects the specific rows from entire table. (like where
condition)
3
