Database Management System 

Database

A database is a collection of interrelated data and a database system is basically a computer based record keeping system.

Its need

Database system is used to overcome the limitations of file processing system such as data redundancy, data inconsistency, unshar able data, unstandardized data, insecure and incorrect data.

Different data models

There are three data models that are used for database management are

1.     Relational data model

2.    Network data model

3.    Hierarchical data model

Relational data model

In relational data model, the data is organized into tables (i.e. row and column).These tables are called relations.

A row in a table represents a relationship among a set of values.

Equivalent Database concept

·        Table                                                               relation

·        Row or record                                                tuple

·        Column or field                                             attribute

·        Number of columns                                     degree

·        Unique identifiers                                         primary key

·        Pool of legal values                                      domain

·        Number of rows                                           cardinality

Domain

A domain is a named set of scalar(possible) values, all of the same type of an attribute.

Ex. A field for gender may have the domain {male, female}

Domain specifies the “type of data” a column have.

Tuple

A row of tables (relations) is generally referred to as tuple.

Degree

The number of attribute in a relation determines the degree of relations.

Ex. A relation having three attribute is called a relation of degree three.

Primary key

A primary key is a key of one or more attribute used to uniquely identify tuples within the relation.

Candidate key

A candidate key is a column, or set of columns, in a table that can uniquely identify record in a table. In a table there may be many candidate key and have one primary key.

Alternate key

A candidate key that is not primary key is called alternate key .

SQL

SQL stands for Structured Query Language. It is a database language used for managing data in relational database management system(RDBMS).It is used for creation, deletion, fetching rows and modifying rows in database.

Advantage of using SQL

1.      High speed

SQL Queries can be used to retrieve large amount of records from a database quickly and efficiently.

2.    Well Defined Standard Exist

SQL database use long established standard, which is being adopted by ANSI & ISO.

3.    No coding required

Using standard SQL is easier to manage database system without having to write substantial amount of code.

Data Definition language and Data manipulation language

DDL provides statements for creation and deletion of tables, indexes, views etc.

Command	Description
CREATE ALTER DROP	Create a new table, a view of table or other object. Modify an existing database object such as table. Delete an entire table.

DML

The DML provides statement to enter, update, delete data and perform complex queries on these tables.

Command	Description
Insert UPDATE DELETE	Create a record Modifies record Delete record

                                                      Student Table

Id          first-name           last-name           age          Subject             games

100        Rahul                  Sharma               10           Science             Cricket

101        Anjali                  Bhagwat               12           Maths               Footbal

102        Stephen              Aeming                09           Science              Cricket

103        Shekar                Gowda                 18            Maths             Badminton 

104        Priya                  Chandra               15            Econo                Chess

SELECT

1.Select first-name from Student  

                        

2.SELECT first-name, last-name FROM Student WHERE Subject=’Math’ 

 

DISTINCT

SELECT DISTINCT Subject FROM Student

IN, BETWEEN

1.     SELECT  * FROM Student WHERE game IN(‘Cricket’, ‘Football’, ’Chess’)

2.    SELECT * FROM Student WHERE age BETWEEN 10 AND 15

                                                        EMPLOYEE

Id               name              dept                  age         salary                 location

100            Ramesh          Electrical          24          25000               Bangalore      

101            Hritik              Electronics       28          35000               Bangalore  

102            Harsh             Aeronautics      28          35000               Mysore  
103            Sonu               Electronics        22          20000              Bangalore

104            Priya               Infotech            25           30000            Mangalore        

1.      Select name, salary FROM employee ORDER BY salary

2.    SELECT dept, SUM(salary) FROM employee GROUP BY dept

3.    SELECT dept, SUM (salary) FROM employee GROUP BY dept HAVING sum(salary)>25000

Thank you for giving us your valuable time.....