CLASS -11 COMPUTER SCIENCE
CHAPTER- 16 RELATIONAL DATABASES
πTo download textbook pdf click on the ''DOWNLOAD'' button below -
NOTES FOR COMPLETE UNDERSTANDING -
- A Database System is generally a record storing system.
- Collected form of data is referred as database.
- “Database" is actually a collection of interrelated data so that it
can be used by various applications.
- Some of the Popular database softwares are- • MySQL (open Source) • ORACLE Database • MS SQL Server • SQLite (open Source) • MariaDB • PostgreSQL (open Source)
- Aim of DBMS:
- Database Management System (DBMS) is a software , it's purpose is to store , maintain and
use databases.
- Its prime purpose is to perform operations on databases and to
provide data when required.
- DBMS reduces Data Redundancy.
- It improves data security.
- It stores data n organized and Integrated form.
- Data remains error free.
- Data is available as and when required.
- Database follows a standard.
- Relational Database Model :–
- In Relational Data model, data remains in the form of tables.
- A table is a combination of rows and columns which is also
known as Relation .
- In a table, a row shows relationship between values. A table is
a collection of this relationship.
- Byte: A group of 8 bits used to store a character is known as byte.
- Data Item: Smallest unit of data.
- Record: It is a complete information made of data item.
- Table: A table is a collection of logical records.
- Relational Model Terminology
:
- Relational Model was given by E. F. Codd of IBM.
- Terminology of Relation Model is:-
- Relation: Basically a relation is a table. Its a collection of data
in rows and columns. A relation has following features- • In a table, data in one column should be of same data type. Different
columns can have data of different data types. • A column in
a row can not have multiple values. • In a relation, each row is distinct. Any two rows cannot have exactly
same data. • There is no specific order of rows in a relation. • In a Relation i.e. table, there is no specific order of columns.
- Domain: It is the collection/pool of values for a column is known as Domain.
- Tuple : rows of a table are known as tuples.
- Attribute : columns of a table are known as attributes.
- Degree : In a relation, number of attributes or columns is its Degree.
- Cardinality : In a relation, number of tuples or rows is its Cardinality.
- Views
– A View is a Virtual table which is based on some or specific
data of a table
- Keys
– Some attributes has some properties because of that those
attributes are known as keys.
- Primary Key : It is a attribute which is used to
uniquely identify records of a relation & can be used to establish
relationship with other relation. It is a mixture of unique & not
null constraint. Generally all master tables have primary keys.
- Candidate Key : Its a group of attributes which have the properties to be
selected as a primary key i.e. These attributes shows their capability to
be a Primary Key.
- Alternate Key : A candidate key which is not a primary key is known as
alternate key.
- Foreign Key : In a table, an attribute which is derived from primary
key of some other table is known as foreign key in present table.
- Referential Integrity: Referential integrity is a system of rules
which is used by a DBMS to ensure that there is a valid
relationship between related tables or not.
- Referential integrity is
possible only when following conditions gets completed-
- Primary key should have unique index.
- Related fields should have same datatype.
- All tables should be of same database.
- Primary key of Master table should have a reference with foreign
key of transaction table.
- Entry of that record in transaction table is not possible whose
foreign key value does not exist in primary key of master table.
- Deletion of that record from primary key table is not possible
which has a related record in foreign key table.
- Change in related records of primary key is not possible.
- MySQL:
- MySQL is an open source Relational Database Management
System (RDBMS) which makes use of SQL (Structured Query
Language).
- It can be downloaded from www.mysql.org.
- In MySQL, information is stored in the form of tables.
- A MySQL database can have multiple tables and thousands of
records simultaneously.
- It is a better option to store fast, reliable and big amount of data.
- MySQL was developed by MySQL AB company which is now a
part of Sun Microsystems.
- SERVER : which responds to the requests of clients.
- CLIENTS : these are the programs which are attached to
database server and send requests to server.
- MySQL Features : – Fast Speed – Easy to use. – Free of cost. – Support of SQL. – Portability. – Various Data types. – Secure. – Can handle large data (Scalability and limits). – Connectivity : uses various protocols to get connected with
clients. – Localization : server can send error messages to clients in
different languages. – Clients and Tools. It provides various client and utility programs.
- SQL:
- Structured Query Language (SQL) is used to access any
database.
- SQL stores the commands that are to be used in databases
which are generally accepted by all RDBMS.
- SQL is a language which provides interface to create relational
database and to operate upon them.
- Various versions of SQL are available. First version was
developed in 1970 by San Jose Research Laboratory of IBM.
- In 1992, 2003, 2008 some updates were added.
- SQL is being used by beginners and skilled users.
- Processing Capabilities of SQL :
- Data Definition Language (DDL)
- Interactive Data Manipulation Language(DML)
- Embedded Data Manipulation Language: these are developed to be
used in some programming languages
- View Definition
- Authorization
- Integrity
- Transaction Control
- Data Definition Language (DDL)
:
- Command under this category are used to create or modify
scheme of database. It is used to create data dictionary.
- Data Dictionary is a kind of metadata means Data about Data. A
standard DDL should have following functions-
- It should identify the types of data division.
- It should give a unique name of each data item.
- It should specify the proper data type.
- It may define the length of data items.
- It may define the range of values of Data items.
- It may specify means of checking for errors.
- It may specify privacy locks for preventing unauthorized reading
or modification of the data.
- DDL Commands
Following commands are under this category-
- Create, alter and drop schema Objects:
- Create table
- create view
- create database
- Alter Table
- Drop Table
- Drop View
- Create Index
- Alter Index
- Grant and Revoke privileges and rolls
- Grant
- Revoke
- Analyze Table
- Check Table
- Restore Table etc.
- DML Commands:
- DML (Data Manipulation Language) is a kind of language used to
access data, insert data and delete data from a data model.
- Data manipulation means-
- Accessing the stored data from a Database.
- Insertion of new information into the Database.
- Deletion of information from the Database.
- modification of information in the Database.
- DMLs are basically of two types-
- Procedural : specifies what data is needed and how to get
it.
- Non- Procedural: specifies what data is needed without
specifying how to get it.
- TCL Commands
:
- A transaction is one complete unit of work for ex- withdrawl of
2000 Rs/- from a bank Account.
- Following commands are used to successfully complete a
transaction.
- COMMIT : it makes all the changes permanent.
- ROLLBACK : undoes all the changes.
- SAVEPOINT : it marks a point upto which all earlier statements have
been successfully completed.
- SET TRANSACTION : it establishes properties for the current
transactions.
πMORE MATERIALS TO BE UPLOADED STAY TUNEDπ