Data is basically a raw/un-organized information which is stored in a database table.

Example:

New Delhi

Rahul Kumar

E1256

Information

The meaningful arrangement /organization of data is known as information.

Example:

Location	Age	Employee_Name	Employee_ID
New Delhi	25	Rahul Kumar	E1256

Differences between Data and Information

Data	Information
Data is un-organized raw facts.	Information is meaningful organization of facts.
Data do not carry any specific meaning.	Information is a group of data that collectively carry a logical meaning.
Data do not depend on information.	Information depends on data.
Raw data is insufficient for decision making.	Information is sufficient for decision making.

Database

· A database is an organized collection of data, so that it can be easily accessed and managed.

· A database is an integrated collection of data records, files, and other objects.

· You can organized data into tables, row, columns and index it to make easier to find relevant information.

· Well known 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.

Advantages of Database

Reduces Data Redundancy

The database management systems contain multiple files that are to be stored in many different locations in a system or even across multiple systems.

This is prevented in a database as there is a single database and any change in it is reflected immediately.

Sharing of Data

In a database, the users of the database can share the data among themselves. There are various levels of authorisation to access the data, and consequently the data can only be shared based with the authorized users.

Data Integrity

Data integrity means that the data is accurate and consistent in the database.

All of these databases contain data that is visible to multiple users. So it is necessary to ensure that the data is correct and consistent in all the databases and for all the users.

Data Security

Only authorised users should be allowed to access the database and their identity should be authenticated using a username and password.

Privacy

The privacy rule in a database states that only the authorized users can access a database according to its privacy constraints. To secure data levels are set in the database and a user can only view the data which is allowed to be seen.

Backup and Recovery

Database Management System automatically takes care of backup and recovery.

It also restores the database after a crash or system failure to its previous condition.

Data Consistency

Data consistency is ensured in a database because there is no data redundancy.

Features of Database

· A database can have one or many tables.

· Each table in a database contains information about one type of item.

· When creating a database an important feature is record uniqueness in every table. it is important to be able to distinguish between different items having duplicate values.

· Every database table should have one or more fields designated as key.

Database Servers

Database servers are dedicated computers that hold the actual databases and run only the DBMS and related software.

Databases available on the database servers are accessed through command line or graphic user interface tools referred to as Frontends; database servers are referred to as Back-ends.

Relational Database Management System (RDBMS)

RDBMS stands for Relational Database Management System.

RDBMS is a program used to maintain a relational database.

RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access.

Types of keys in database

Primary Key

- It is a unique key.

- It can identify only one tuple (a record) at a time.

- It has no duplicate value, it has unique value.

- It cannot be NULL (empty).

Example: Table : Std_Details

Regno	Name	Class	Sec
102	Ram	X	A
103	Mohan	X	A
104	Rajeev	X	A
110	Sumit	X	A

- Here Regno field name acts as a primary key.

Foreign Key

- It is a key that acts as a primary key in one of the table and it acts as a secondary key in another table.

- It combines two or more relations (table) at a time.

- They acts as a cross reference between the tables.

Example: Table: Std_Details

Regno *#PK*	Name	Class	Sec
102	Ram	X	A
103	Mohan	X	A
104	Rajeev	X	A
110	Sumit	X	A

Example: Table: Std_Marks

Eng	Hin	Math	Sci	SSC	Comp	Regno#FK
45	30	37	35	34	32	110
32	36	35	34	30	35	103

Query: What is the name of the student who scored 35 marks in comp?

Answer: Mohan

Composite Key

- Sometimes, a table might not have a single column/attribute that uniquely identifies all the record. In this situation two or more column/attribute can be used.

Example: Table: Std_Details

Name	Fname	Class	Sec
Ram	Shyam Kumar	X	A
Mohan	Mahant Singh	X	A
Rajeev	Rajveer Verma	X	A
Sumit	Sujeet Thakur	X	A

- Composite Key : Name + Fname

- It acts as a primary key if there is no any primary key in the table.

- Two or more attributes are used together to make a PK.

Alternate Key

- All the keys which are not PK are called alternate key.

- It cantains two or more fields to identify two or more records.

- The values are repeated.

Database Objects

Table

- Table is used to organize data in rows and columns.

- A table has fixed number of columns (65536) but any number of rows.

- Each row is identified by value appearing in a particular column.

Table : Std_Details

Regno	Name	Class	Sec
102	Ram	X	A
103	Mohan	X	A
104	Rajeev	X	A
110	Sumit	X	A

Rows/Tuples/Records

- Not have fixed limit.

- Tuple represent a single data item in a table.

- Each row in a table represents a set of data.

Regno	Name	Class	Sec
102	Ram	X	A

Column/Field/Attribute

- A column is a set of data value of a particular type.

- MS Access 65536 columns.

Regno

Name

Class

Sec

Data Types in Base

Numeric Types:

TINYINT	Tiny Integer	Store range of 0-255
SMALLINT	Small Integer	Store range -2^15 - +2^15-1(-32768 to 32767)
INT	Integer	Store range -2^31 - +2^31-1
FLOAT	Hold Decimal value	Store range 2^-1074 to 2(^-52)*2^1023

Alphanumeric Types

VARCHAR

Text

Stores upto a specified length.

CHAR

Text(fix)

Stores exactly the length specified by the user.

Accepts any UTF-8 characters,

UCS Transformation format 8. Encoding (ASCII)

Binary Types

LONGVARBINARY	Stores audio, images, etc.
BINARY	Store any array of bytes.

Date Time

DATE

Stores Date in the format MM/DD/YYYY

11/8/2022

or 11/8/22

TIME

Stores Time in the format HH:MM:SS

18:15:36

TIMESTAMP

Stores Date and Time information.

11/8/2022 19:14:20

Query

Query Command

DDL (Data Definition Language)

- DDL statements are used to create database, users, tables, etc.

- Commands : CREATE, DROP, ALTER, RENAME

DML (Data Manipulation Language)

- DML statements are used to insert, update or delete the records.

- Commands : INSERT, UPDATE and DELETE

SELECT statement retrieves zeros or more rows from one or more database tables.

SELECT statement has many optional clauses:

- WHERE : specifies which records to retrieve.

- ORDER BY : specifies an order (asc/desc) in which to return the rows.

SQL Commands

Creating a table

Command

CREATE TABLE table_name (

Field1 datatype(range),

Field2 datatype (range),

Field3 datatype (range),

............

);

Example:

CREATE TABLE student_details(

regno varchar(7),

name varchar(50),

class varchar(10),

sec varchar(1)

);

Creating a table with primary key

CREATE TABLE student_details(

regno varchar(7) primary key, #Here regno now acts a primary key

name varchar(50),

class varchar(10),

sec varchar(1)

);

Output

REGNO

NAME

CLASS

SEC

Inserting data in a table

Command

INSERT INTO table_name values(value1, value2, value3, ........);

Example

INSERT INTO student_details VALUES('J00101','Rahul Kumar','VIII','C');

Output

REGNO	NAME	CLASS	SEC
J00101	RAHUL KUMAR	VIII	C

Inserting bulk data in a table

INSERT INTO student_details VALUES

('J00102','Mohit Kumar','VIII','C'),

('J00103','Saloni Kumari','VIII','C'),

('J00104','Manoj Kumar','VIII','C'),

('J00105','Suraj Kumar','VIII','C');

Output

REGNO	NAME	CLASS	SEC
J00101	Rahul Kumar	VIII	C
J00102	Mohit Kumar	VIII	C
J00203	Saloni Kumari	VIII	C
J00104	Manoj Kumar	VIII	C
J00105	Suraj Kumar	VIII	C

Updating a table

Command

UPDATE table_name SET fieldname='value' where fieldname=value;

Example

UPDATE Student_details SET NAME='Rohit Kumar' WHERE regno='J00101';

Output

REGNO	NAME	CLASS	SEC
J00101	Rohit Kumar	VIII	C
J00102	Mohit Kumar	VIII	C
J00203	Saloni Kumari	VIII	C
J00104	Manoj Kumar	VIII	C
J00105	Suraj Kumar	VIII	C

Deleting all records from a table

Command

DELETE FROM table_name;

Example

DELETE FROM STUDENT_DETAILS;

Output

REGNO

NAME

CLASS

SEC

Alter

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Adding a new column to a table

Command

ALTER TABLE table_name ADD column_name datatype;

Example

ALTER TABLE STUDENT_DETAILS ADD ROLL INT;

Output

REGNO

NAME

CLASS

SEC

ROLL

Drop a column

Command

ALTER TABLE table_name DROP column_name;

Example

ALTER TABLE STUDENT_DETAILS DROP ROLL;

Output

REGNO

NAME

CLASS

SEC

Order By

- The ORDER BY keyword is used to sort the result-set in ascending or descending order.

- The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Command

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Example

SELECT NAME,CLASS,SEC FROM STUDENT_DETAILS ORDER BY NAME;

Output

REGNO	NAME	CLASS	SEC
J00101	Manoj Kumar	VIII	C
J00102	Mohit Kumar	VIII	C
J00203	Rohit Kumar	VIII	C
J00104	Saloni Kumari	VIII	C
J00105	Suraj Kumar	VIII	C

Example

SELECT NAME,CLASS,SEC FROM STUDENT_DETAILS ORDER BY NAME DESC;

Output

REGNO	NAME	CLASS	SEC
J00101	Suraj Kumar	VIII	C
J00102	Saloni Kumai	VIII	C
J00203	Rohit Kumar	VIII	C
J00104	Mohit Kumar	VIII	C
J00105	Manoj Kumar	VIII	C

Query Examples

Get all the columns from the Student_Details table.

Command

SELECT * FROM Student_Details;

Output

REGNO	NAME	CLASS	SEC
J00101	Rohit Kumar	VIII	C
J00102	Mohit Kumar	VIII	C
J00203	Saloni Kumari	VIII	C
J00104	Manoj Kumar	VIII	C
J00105	Suraj Kumar	VIII	C

Write a statement that will select the Name column from the Student_Details table.

Command

SELECT Name FROM Student_Details;

Output

NAME

Rohit Kumar

Mohit Kumar

Saloni Kumari

Manoj Kumar

Suraj Kumar

Select all records where the Class column has the value "VIII".

Command

SELECT * FROM Student_Details WHERE Class=VIII;

Output

REGNO	NAME	CLASS	SEC
J00101	Rohit Kumar	VIII	C
J00102	Mohit Kumar	VIII	C
J00203	Saloni Kumari	VIII	C
J00104	Manoj Kumar	VIII	C
J00105	Suraj Kumar	VIII	C

Use the NOT keyword to select all records where Class is NOT "VIII".

Command

SELECT * FROM Student_Details WHERE NOT Class='VIII';

Output

REGNO

NAME

CLASS

SEC

Select all records where the Regno column has the value ‘J00104’.

Command

SELECT * FROM Student_Details WHERE Regno='J00104';

Output

REGNO	NAME	CLASS	SEC
J00104	Manoj Kumar	VIII	C

Select all records where the Class column has the value 'VIII' and the Regno column has the value 'J00104' .

Command

SELECT * FROM Student_Details WHERE Class='VIII' AND Regno='J00104';

Output

REGNO	NAME	CLASS	SEC
J00104	Manoj Kumar	VIII	C

Select all records where the Class column has the value 'VIII' or 'IX'.

Command

SELECT * FROM Customers WHERE Class='VIII' OR City='IX';

Output

REGNO	NAME	CLASS	SEC
J00101	Rohit Kumar	VIII	C
J00102	Mohit Kumar	VIII	C
J00203	Saloni Kumari	VIII	C
J00104	Manoj Kumar	VIII	C
J00105	Suraj Kumar	VIII	C

Performing Calculation

Form

It is an interface in a user specified layout that lets users to view, enter, and change data directly in database objects such as tables.

Report

- A report helps to display the data in a summarized manner.

- It is used to generate the overall work outcome in a clear format.

C10 U3 RELATIONAL DATABASE MANAGEMENT SYSTEMS (BASIC)

Comments

Post a Comment

Popular posts from this blog