Basics of Database Management Systems
Data: Basic/raw facts about something which is not organized, for example details of some students which is not organized.
Data Item: Each piece of information about an entity, such as name of a person or address, age or name of a product or the price is a Data Item.
Database: A well organised collection of data that ensures safety, security and integrity of data.
DataBase Management System(DBMS) :
o Comprehensive software that provides the essential services to create, manage and maintain the databases. In short a DBMS provides the means to store the data in the database, to edit or delete the data stored, to search and analyze the data in the database. They also provide various safety and security mechanisms that ensures that in any case stored data will be safe and accessible.
Relational DataBase Management System(RDBMS) :
o In a relational data model, the data is organized into tables (i.e. Rows and Columns). These tables are called Relations. A row in a table represents a relationship among a set of values. Since table is a collection of relationships it is generally referred to using the mathematical term Relation.
Database Systems:
Systems comprising of Databases and Database Management Systems are simply referred as database systems.
Advantages of Database Systems:
o They reduce data redundancy (duplication of data).
o They control data inconsistency.
o They facilitate sharing of data.
o They enforce standards.
o They ensure data security & integrity
o They facilitate efficiency in handling the data.
Data Model:
o A way by which data structures and their relationships are analyzed.
Different Data Models :
o Relational data model
o Hierarchical data model
o Network data model
o Object Oriented data model
Relational data model: In this model data is organised into tabular structures called relations. A database may contain many relations providing a better classification of data based on its nature and use. Multiple relations are then linked/associated together on some common key data values (foreign key).
Basics of Relational Model
Relation : A tabular structure containing data. To be a relation is must satisfy following four conditions:
Atomicity : At every row-column intersection§ (Cell) there must be an atomic value i.e. a value that can not be further subdivided.
No duplicity: No two rows of relation will be identical§ i.e. in any two rows value in at least one column must be different.
Tuple : A row in a relation is called a tuple.
Attribute : A column in a relation is called an attribute.
Domain : Domain of an attribute refers to the set of all the possible values for that attribute.
Degree : Number of attributes in a relation is the degree of that relation.
Cardinality : Number of tuples in a relation is the cardinality of that relation.
Candidate Key: A set of one or more minimal attributes used to uniquely identify a tuple in the relation and which can act as Primary Key. A relation can have multiple candidate keys.
Primary Key: A candidate key that is primarily chosen for unique identification of tuples in a Relation. Any subset of Primary key should not be Primary key. For example: Admission Number in the Student Table, Accession Number of a Book in the Book table, Employee Id of an employee in the Employee Table, Item Number of an item in the Stock table, Flight Number of a flight in the Flight Master Table, etc. A table may have more than one candidate keys but definitely has one and only one primary key.
Alternate Key: Candidate keys that not chosen as primary key are the alternate keys.
Foreign Key: When the primary key of one relation appears in another relation, it is called foreign key. Foreign key refers to a tuple in its original table.
Referential Integrity: When a table contain foreign key, referring to a tuple/record in another table, the referenced record must be exist. The record in first table may be termed as parent record while a record in foreign key table is called the child record. Referential integrity ensures that a child record will exist only if its parent record exists. Child record can not be created if parent record does not exist and parent record can not be deleted if a child record exists.
Data: Basic/raw facts about something which is not organized, for example details of some students which is not organized.
Data Item: Each piece of information about an entity, such as name of a person or address, age or name of a product or the price is a Data Item.
Database: A well organised collection of data that ensures safety, security and integrity of data.
DataBase Management System(DBMS) :
o Comprehensive software that provides the essential services to create, manage and maintain the databases. In short a DBMS provides the means to store the data in the database, to edit or delete the data stored, to search and analyze the data in the database. They also provide various safety and security mechanisms that ensures that in any case stored data will be safe and accessible.
Relational DataBase Management System(RDBMS) :
o In a relational data model, the data is organized into tables (i.e. Rows and Columns). These tables are called Relations. A row in a table represents a relationship among a set of values. Since table is a collection of relationships it is generally referred to using the mathematical term Relation.
Database Systems:
Systems comprising of Databases and Database Management Systems are simply referred as database systems.
Advantages of Database Systems:
o They reduce data redundancy (duplication of data).
o They control data inconsistency.
o They facilitate sharing of data.
o They enforce standards.
o They ensure data security & integrity
o They facilitate efficiency in handling the data.
Data Model:
o A way by which data structures and their relationships are analyzed.
Different Data Models :
o Relational data model
o Hierarchical data model
o Network data model
o Object Oriented data model
Relational data model: In this model data is organised into tabular structures called relations. A database may contain many relations providing a better classification of data based on its nature and use. Multiple relations are then linked/associated together on some common key data values (foreign key).
Basics of Relational Model
Relation : A tabular structure containing data. To be a relation is must satisfy following four conditions:
Atomicity : At every row-column intersection§ (Cell) there must be an atomic value i.e. a value that can not be further subdivided.
No duplicity: No two rows of relation will be identical§ i.e. in any two rows value in at least one column must be different.
Tuple : A row in a relation is called a tuple.
Attribute : A column in a relation is called an attribute.
Domain : Domain of an attribute refers to the set of all the possible values for that attribute.
Degree : Number of attributes in a relation is the degree of that relation.
Cardinality : Number of tuples in a relation is the cardinality of that relation.
Candidate Key: A set of one or more minimal attributes used to uniquely identify a tuple in the relation and which can act as Primary Key. A relation can have multiple candidate keys.
Primary Key: A candidate key that is primarily chosen for unique identification of tuples in a Relation. Any subset of Primary key should not be Primary key. For example: Admission Number in the Student Table, Accession Number of a Book in the Book table, Employee Id of an employee in the Employee Table, Item Number of an item in the Stock table, Flight Number of a flight in the Flight Master Table, etc. A table may have more than one candidate keys but definitely has one and only one primary key.
Alternate Key: Candidate keys that not chosen as primary key are the alternate keys.
Foreign Key: When the primary key of one relation appears in another relation, it is called foreign key. Foreign key refers to a tuple in its original table.
Referential Integrity: When a table contain foreign key, referring to a tuple/record in another table, the referenced record must be exist. The record in first table may be termed as parent record while a record in foreign key table is called the child record. Referential integrity ensures that a child record will exist only if its parent record exists. Child record can not be created if parent record does not exist and parent record can not be deleted if a child record exists.
MYSQL:MySQL is a fast,
easy-to-use RDBMS used for small and big business applications. MySQL is
developed, marketed, and supported by a Swedish Company MySQL AB. MySQL is
released under an open-source license so it is customizable. It requires no
payment for its usage.• MySQL is a very powerful software to handle RDBMS.
MySQL uses a standard form of the well-known ANSI-SQL standards.
MySQL is a platform independent application which works on many operating
systems like Windows, UNIX, LINUX etc. And has compatibility with many
languages including JAVA , C++, PHP, PERL, etc. MySQL is a easy to
install RDBMS and capable of handling large data sets.
SQL Commands:
—
SQL commands are
instructions used to communicate with the database to perform specific task
that work with data. SQL commands can be used not only for searching the
database but also to perform various other functions like, for example, you can
create tables, add data to tables, or modify data, drop the table, set
permissions for users. SQL commands are grouped into four major categories
depending on their functionality:
—
Data Definition Language
(DDL) - These SQL commands are used for creating,
modifying, and dropping the structure of database objects. The commands are
CREATE, ALTER, DROP, RENAME, and TRUNCATE.
—
Data Manipulation
Language (DML) - These SQL commands
are used for storing, retrieving, modifying, and deleting data. These commands
are SELECT, INSERT, UPDATE, and DELETE.
—
Transaction Control
Language (TCL) - These SQL commands
are used for managing changes affecting the data. These commands are COMMIT,
ROLLBACK, and SAVEPOINT.
—
Data Control Language
(DCL) - These SQL commands are used for providing
security to database objects. These commands are GRANT and REVOKE.
DATA TYPES:
TEXT:CHAR(size)
A fixed-length string from 1 to 255 characters in length right-padded with
spaces to the specified length when stored.Values must be enclosed in single
quotes or double quotes.
VARCHAR(size)
A variable-length string from 1 to 255 characters in length; for example
VARCHAR(25) Values must beenclosed in single quotes or double quotes.
NUMERIC :
DECIMAL(p,s) It
can represent number with or without the fractional part. The size argument has
two parts :
precision and scale. Precision (p) indicates the number of significant digits and scale (s)maximum number of digits to the right of the decimal point.
precision and scale. Precision (p) indicates the number of significant digits and scale (s)maximum number of digits to the right of the decimal point.
INT It
is used for storing integer values INT (3)
DATE :
It represents the date including day,
month and year between 1000-01-01 and 9999-12-31 YYYY-MM-DD
2009-07-02
2009-07-02
TIME: It represents time.Format:
HH:MM:SSNote: The supported range is from'-838:59:59' to '838:59:59'
No comments:
Post a Comment