Class 10th unit-3 RELATIONAL DATABASE MANAGEMENT SYSTEMS (BASIC)
RELATIONAL DATABASE MANAGEMENT SYSTEMS (BASIC)
Database :-
1. A database is an organized collection of data. You can visualize it as a
container of information.
2. A database can have one or many tables.
3. Example:-
Suppose if you own a stationary shop, you need to keep detailed
records of the materials available in your shop. You also need to store
information about pricing, stock levels for reordering, old stocks, etc. While in
the manual system, you would maintain several files with different bits of
information; in the computerized system you would use database programs
such as Microsoft Access, OpenOffice.org Base, and MySQL, to organize the
data as per your business need.
Database Management System:-
1. A database management system is a software package with computer
programs that controls the creation, maintenance, and use of a database.
2. A database is an integrated collection of data records, files, and other
objects.
3. A DBMS allows different user application programs to concurrently access
the same database.
4. DBMSs include Oracle, IBM DB2, Microsoft SQL Server, Microsoft Access,
PostgreSQL, MySQL, FoxPro, and SQLite.
Data can be organized into two types:-
• Flat File:
Data is stored in a single table. Usually suitable for less amount
of data.
• Relational:
Data is stored in multiple tables and the tables are linked using a common field. Relational is suitable for medium to large amount of data.
Database Servers:-
1. Database servers are dedicated computers that hold the actual databases and run only the DBMS and related software.
2. Databases available on the database servers are accessed through command line or graphic user interface tools referred to as Frontends.
3. Database servers are referred to as Back-ends. Such type of data access is referred to as a client-server model.
Key components of databases:-
v Field:- Database field is a single piece of information from a record . The term field refer to a column and vertical Categories of data.
v Record:--A record is a set of information (made up of fields) stored in your database about one of the items.
v Primary Key (PK) :-This unique field is called the Primary Key (PK). A primary key is a unique value that identifies a row in a table. In our example, ClientID is the primarykey in the Client table. Primary Keys are also indexed in the database, making it faster for the database to search for a record.
v Composite Primary Key:- when primary key constraint is applied on one or more columns then it is known as Composite Primary Key.
v Foreign key (FK) :- The referred field ClientID which occurs in the Sales table is called the Foreign key (FK). Hence, the foreign key identifies a column or set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The “one” side of a relation is always the parent, and provides the PK attributes to be copied. The “many” side of a relation is always the child, into which the FK attributes are copied. Memorize it: one, parent, PK; many, child, FK.
CREATE AND EDIT TABLES USING WIZARD AND SQL COMMANDS
Tables:
A table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. A table has a defined number of columns, but can have any number of rows.
Columns or Fields or Attributes:
The columns provide the structure according to which the rows are composed. For example, StudentFirstName, or StudentLastName are fields in a row.
Rows or Records or Tuples:
A row also called a Record or Tuple represents a single, data item in a table. Each row in a table represents a set of related data, and every row in the table has the same structure.
Data types:
Data types are used to identify which type of data (value) we are going to store in the database.
Data types in OpenOffice base are broadly classified into five categories listed below.
• Numeric Types
• Alphanumeric Types
• Binary Types
• Date time
• Other Variable types
Numeric Types:
Numeric data types in a database can be used for storing information such as mobile number, roll number, door number, year of school admission, true or false statements, statistical values, etc.
The different types of numeric data types available are listed here.
Alphanumeric Types:
Binary Types:
Binary data types are used for storing data in binary formats. Binary data types in a database can be using for storing photos, music files, etc.
Date time:
Date time data types are used for describing date and time values for the field used in the table of a database.
Create table using SQL DDL Command:-
1. SQL stands foe structure query language
2. SQL commands are also used to manage and manipulate data in a database.
3. The SQL commands are categorized as:
i) DDL (Data Definition Language)
ii) DML (Data Manipulation Language)
DDL (Data Definition Language)
A data definition language or data description language (DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER, and DROP.
Data Manipulation Language (DML)
A data manipulation language (DML) is a language that enables users to access and manipulate data in a database. The goal is to provide efficient human interaction with the system.
Data manipulation involves:
I. Retrieval of information from the database- SELECT statement
II. Insertion of new information into the database - INSERT statement
III. Deletion of information in the database - DELETE statement
IV. Modification of information in the database - UPDATE statement
There are two types of DML:
1. Procedural:
The user specifies what data is needed and how to get it
2. Nonprocedural:
The user only specifies what data is needed. This is easier for the user but may not generate code as efficient as that produced by procedural languages.
Create table Statement:-
Syntax:
Create table student
(
Stud_Id integer Primary Key,
Stud_Name varchar(20),
Stud_Class varchar(3)
);
INSERT statement:-
INSERT statement is used to add one or more records to a database.
Syntax:-
insert into table_name values (value1, value2, value3 ...);
Example: insert into student values(101, “suman”, “10th A”);
Select statement(display table summary):-
SELECT statement is used to display the records of table.
Synatx:
Select * from table_name;
Example: select * from student;
DELETE statement
Delete Statement is used to remove one or more records in a database.
Syntax:
DELETE FROM <table_name> [WHERE] <condition>;
Example: delete from student where Stud_Id=101;
Grouping of Data
To display the records containing the same type of values “WHERE” clause can be used with the Select SQL Command.
To get details about the list of students whose favorite color is blue, you can use:
select * from SDetails where Color=’Blue’;
View records in ascending order
select * from SDetails order by “Rollno” ASC;
UPDATE statement
Update statement is used for modifying records in a database.
Syntax: update table-name set column_name=”value” where Stud_Id=101;
Example: update student set name = “lata” where Stud_Id = 101;
ALTER TABLE statement
The ALTER TABLE statement in SQL is used to add, remove, or modify columns in an existing table.
Syntax Add column:
Syntax: alter table table_name add (Columnname_1 datatype, Columnname_2 datatype);
Example: alter table student add Email varchar(255);
ALTER TABLE DROP Column Statement Syntax:
Syntax: alter table table_name drop column column_name;
Example: alter table student drop column class;
SORTING DATA
Sorting means to arrange the data in either ascending order of descending order.
Referential Integrity
Referential integrity is used to maintain accuracy and consistency of data in a relationship.
Referential integrity helps to avoid:
1. Adding records to a related table if there is no associated record available in the primary key table.
2. Changing values in a primary if any dependent records are present in associated table(s).
3. Deleting records from a primary key table if there are any matching related records available in associated table(s).
Creating and Editing Relationships between Tables
A relationship refers to an association or connection between two or more tables. When you relate two tables, you don't need to enter the same data in separate tables.
Relationships between tables helps to:
1. Save time as there is no need to enter the same data in separate tables.
2. Reduce data-entry errors.
3. Summarize data from related tables
Types of relationships:
1. ONE to ONE
2. ONE to MANY OR MANY to ONE
3. MANY to MANY
One to One Relationship
In this relationship, both the tables must have primary key columns. For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person.
One to Many Relationship
In this relationship, one of the table must have primary key column. It signifies that one column of primary key table is associated with all the columns of associated table. For Example. If the two entity types are 'Customer' and 'Account,' each 'Customer' can have many 'Accounts,' but each 'Account' can only be owned by one 'Customer.
Many to Many Relationship
In this relationship, no table has the primary key column. It signifies that all the columns of primary key table are associated with all the columns of associated table. For example a student can register for many classes, and a class can include many students
Hi
ReplyDeletePlz share more topic
ReplyDelete