Database Management System

 DBMS and RDBMS

Data: It is defined as a collection of facts. It can also be defined as a fact about a person or an object which is to be stored. It can be numeric, text, audio, video or image. It is directly not useful and meaningful.

Database: It is organized collection of data stored on computers. It is digital filing system to store, maintain and retrieved data as and when required.

Information: It is a collection of processed, ordered and related. It is useful and meaningful. It can be used to take decisions.

Database Management System(DBMS): A database is defined as an integrated collection of data, record, files and other objects. A database is also defined as a collection of tables. Each table stores data about one entity.  A DBMS is a software used to store, maintain and retrieve data from the database. Examples- FoxBASE, dBase.

Relational Database Management System(RDBMS): A relational  database is defined as a collection of related tables. Related tables are linked by common field or an attribute. A Relational DBMS is a software used to store, maintain and query(Selected retrieval) data from the relational database. Example- MS Access for personal databases. My SQL, Microsoft SQL Server, Sybase, Oracle are commercial RDBMS used in large organization.

The concept of relational database was developed by E.F. Codd at IBM in 1970.

Structured Query Language(SQL): It is called as the language of RDBMS. It is used to interact or communicate with the relational database. It is not only used to create the relational database but also used to maintain, retrieve and secure the database.

Primary Key(PK): A field or an attribute in a table or a relation having a unique value and it can be used to identify a given record from a given set of records. Example- Rollno of a student is a unique value, no two students can have the same Rollno. It is also used to avoid duplication of record.

Composite Primary Key: A combination or a group of fields or an attributes that uniquely identify a record from a set of records. Each class Rollno starts with 1 in class, To identify a student in school, a combination is ClassNo and Rollno will be required to identify a student.

Foreign Key(FK): A field or an attribute which is a primary key in one table or relation, It appears in another table It is termed as a Foreign key. The primary and Foreign key combination is used to establish relationship between two tables and it is used to get the data from the related tables. Example:

Student Rollno (PK) Name Mobile No 1 XYZ 1234567890

Installment Rollno (FK) Amount 1 1000 1 2000

Advantages of RDBMS:

1 Reduction in Data Redundancy: Data redundancy refers to the duplication or repetition of data within the database. Duplication results in wastage of memory.

2 Reductions in Data Inconsistency: The aim of RDBMS is to ensure to maintain database in a consistent or correct state. In DBMS, the data is duplicated and this results in inconsistency when data is updated or modified.

3 Data Sharing: RDBMS stores data on database Server. It is a centralized Computer that stores and maintains data of the organization. The users on their client computer share and process data stored on the server. It helps to avoid duplication.

4. Enforcement of Data Standards: Standards can be enforced using the centralized approach. It also helps in migrating or exchanging data between different systems.

5. Ensures Data Security: It ensures that only authorized users are allowed to access and process data stored in the database.

6. Data Integrity: The data when entered is validated and RDBMS ensures that only valid data is stored in the database.

7 Interactive Interface: SQL is a language of RDBMS. Database users used to interact with the relational database.

Database Server

Database servers are dedicated computers that are designed to store data of the organization and provide database services to other computers. Organizations use a database to store, organize, manage and retrieve data efficiently and effectively. The database servers run only database and database related software.

Organization has a Client/Server environment. The organization is on Database Servers and Server is connected to the clients (computers used by employees).

RDBMS:

A database management system that is based on the relational model is called an RDBMS (Relation Database Management System). Tables are used to organize data in relational databases. A relational database management system (RDBMS) is used to store, manage, query, and retrieve data. Structured Query Language (SQL) is a language of RDBMS. It is used to interact or communicate with relational database. In RDBMS terminology, tables are termed as relations, records are termed as tuples.

Database Objects

a. Tables: Data is arranged into rows and columns in a table, which is a type of data structure. It can be applied to both the storage and presentation of structured data.

b. Columns or Fields or Attributes: Data is arranged vertically from top to bottom in columns. Each row of the table has one column, which is a collection of data values of a specific basic type. The structure by which the rows are put together is provided by the columns.

c. Rows or Records or Tuples: A row, also known as a Record or Tuple, in a table represents a single data item. A database table can be represented graphically as being made up of rows and columns, or fields. Every row in a table has the same structure and represents a group of connected data.

Difference between DBMS & RDBMS:

Aspect	DBMS	RDBMS
Data structure	Data is stored in various formats such as flat files or hierarchical structures.	Data is stored in tables that are related to each other using a relational model.
Data integrity	DBMS systems may or may not enforce strict data integrity rules.	RDBMS systems enforce strict data integrity rules that ensure data consistency and accuracy.
Data normalization	DBMS systems may or may not follow normalization rules.	RDBMS systems follow normalization rules to minimize data redundancy and improve data integrity.
Query language	Typically, DBMS systems use proprietary query languages.	RDBMS systems use standard query language like SQL.
Scalability	DBMS systems may not be scalable to handle large amounts of data.	RDBMS systems are highly scalable to handle large amounts of data.
Cost	DBMS systems are generally less expensive than RDBMS systems.	RDBMS systems are generally more expensive than DBMS systems.

Referential Integrity:

The relationship between tables is referred to as referential integrity. Referential integrity is used to maintain accuracy and consistency of data in a relationship. In Base, data can be linked between two or more tables with the help of primary key and foreign key constraints.

Why it is necessary to enforced in RDBMS?

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 primary if any dependent records are present in associated table(s). One can enforce cascade update to achieve it. The primary key or its any component (Composite Primary Key) is changed than the foreign key in the related table will also be changed. It is necessary to ensure database in a consistent and a correct state.

3. Deleting records from a primary key table if there are any matching related records available in associated table(s). One can enforce cascade delete to achieve it. If a record from the master table is deleted, than all its corresponding records in the details table will also deleted by RDBMS thereby ensuring database consistency.

The diagram below shows the concept of Master-Details Table in RDBMS. 

Note: - Memorize it: One, parent, PK; many, child, FK.

What is SQL? How SQL is classified?

SQL stands for Structured Query Language. It is called as the language of RDBMS. It is used to describe the data structure and to modify the data in the database, queries are used as instructions. A query enables the joining and filtering of data from the related tables.

Database Languages having two types: -

DDL (Data Definition Language):

Create: Using this statement, a database or set of tables can be created.

Alternate: This statement is used to change the table’s structure.

Drop: This statement is used to remove database objects from the system

DML (Data Manipulation Language)

SELECT: The statement “SELECT” is used to get data from the database.

INSERT: The statement “INSERT” is used to add a new record to the database.

DELETE: The database can be cleaned out by using the statement DELETE.

UPDATE: This statement is used to modify the database’s information.

What is a Select statement? What is its Syntax?

A select query is a language in a database that displays data in Datasheet view. The SQL SELECT statement is used to retrieve records from one or more tables in a relational database. The records retrieved are known as a result set.

Syntax of Select Statement is –

Select expressions

FROM <TABLENAME>

[WHERE conditions]

[ORDER BY expression [ASC | DESC]];

The expressions specifies columns or calculations that are to be retrieved. Use * to retrieve or select all columns from table.

Select expressions

FROM tables

The tables from which to retrieve records. There must be at least one table listed in the FROM clause.

[WHERE conditions]

Optional. The conditions that must be met for the records to be selected or selected.

If no conditions are provided, then all records will be retrieved.

[ORDER BY expression [ASC | DESC]];

Optional. The expression (column or field) used to sort the records in the result set. If more than one expression is provided, the values should be comma separated. ASC is default i.e. Ascending order and DESC is Descending.

Write the SQL Queries based on the following Product table?

Product No	Product_Name	Price	Quantity
25	Soap	40	80
31	Powder	80	30
45	Shampoo	250	25
52	Soap Box	120	100

Write a Query to display all record from the table;

Select * from product;

Write a Query to display product name from the table;

Select Product_Name from product;

Product_Name

Soap

Powder

Shampoo

Soap Box

Write a Query to display Product_Name and Price from the table;

Select Product_Name, Price from product;

Product_Name	Price
Soap	40
Powder	80
Shampoo	300
Soap Box	120

Write a Query to find the total no of quantity available in table;

Select sum (quantity) from product;

Output – 235

Write a query to display the total amount of each item. The amount must be calculated as the price multiplied by quantity for each item.

Select Product_No, Product_Name, Price * Quantity from product;

Product_No	Product_Name	Price*Quantity
25	Soap	3200
31	Powder	2400
45	Shampoo	6250
52	Soap Box	12000

Write a Query to display the product whose price is less than 90.

Select * from product where price < 90;

Product_No	Product_Name	Price	Quantity
25	Soap	40	80
31	Powder	80	30

What is Create Table statement? Write its syntax.

The create statement is used to create a table in a database. It creates a blank table. Its syntax is –

CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype);

Question – Write a Query to create the following table in the database; Table Name: product

Field	DataType
Product_No	Integer
Product_Name	Varchar (20)
Price	Integer
Quantity	Integer




Create table product (Product_No Int, Product_Name Varchar (20), Price Int, Quantity Int);

The type of data (value) that will be stored in the database is defined by its datatype. Important to know the different types of data helps to ensure that each property’s value is as expected and that data are collected in the correct format.




What is Insert table statement in SQL?  Write its syntax?

The insert statement is used to insert a record in a table. Its syntax is as under: -

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Or

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

Write a Query to add a new row with the following details

(72, “Hair Conditioner‟, 350, 60)

Insert into product (Product_No, Product_Name, Price, Quantity) values(72, “Hair Conditioner‟, 350, 60);

Or

Insert into product values (72, “Hair Conditioner‟, 350, 60);

What is update statement? Write its syntax.

Data stored in the database table often needs to be updated or modified. To edit or update already-existing records in a table, use the UPDATE statement. Using the WHERE clause, one can either define a specific subset of entries to edit or use it to update everything at once.

Syntax of Update Statement –

UPDATE <table name> SET = value [, column_name = value ...] [WHERE];

Q Write SQL Queries for the following using the update statement?

1.      Write a Query to update the price of Shampoo in the product table.

Update product Set Price = 300 where Price = 250;

2.      Write a Query to update the Quantity of Powder in the product table.

Update product Set Quantity = 50 where Product_Name = ‘Powder’;

What is Relationship between tables? What is the need of creating Relationship?

An association or link between two or more tables is referred to as a relationship. The relationship is established using the primary and foreign key in the two related tables. The need to create relationship is to retrieve the data distributed in the related tables. The aim is to ensure that only one entity data is to maintained in a single table. It has the following benefits: -

ü  Repetition of data (Results in wastage of memory and makes database inconsistent)

ü  Loss of Information (Record on deletion result in information loss)

ü  Inability to represent certain information (Data can entered only if complete data is available)

A relationship between tables helps to -

ü  Save time as there is no need to enter the same data in separate tables.

ü  Reduce data-entry errors.

ü  Summarize data from related tables.

Type of Relationship?

The types of Relationships in Database are as under -

1. ONE to ONE

In this relationship, both the tables must have primary key columns. Example a Student and a Class table, i.e. one student belongs to one class only.

2. ONE to MANY OR MANY to ONE

In this relationship, one of the tables must have primary key column. It signifies that one column of primary key table is associated with all the columns of associated table. Example a Student and Faculty, i.e. one student is associated to many faculties

3. MANY to MANY

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. Many Students associated with many Faculties.

What are Forms? How forms are created?

Forms are data-entry forms used to enter validated data in the table. It is an interface that accepts data from the data-entry operator and insert accepted data after validation in the table.

Steps To Create Form Using Wizard

Step 1: Click Use Wizard to Create Form

Step 2: Select the selective Fields using Arrow button

Step 3: Click Next

Step 4: Add Subform if you need to insert

Step 5: Click Next

Step 6: Arrange selected field in a form

Step 7: Click Next

Step 8: Click Finish

What is a Report? How Report is created?

Database contains data and not information. Data, by default is not meaningful but once process becomes information. Information is meaningful. It is used by the top level management to take decisions to achieve the targets of the organization. Information is presented in the form of Report.

A report helps in the presentation of data in a summarized format. It is utilized to create a clear format for the entire work product. In the database, you can build reports.

Steps To Create Report Using Wizard

Step 1: Click on Use Wizard to Create Report

Step 2: Select the selective Fields using Arrow button

Step 3: Click Next

Step 4: Redefine the label of the fields in the reports or else you can set the default name

Step 5: You can define grouping for the fields of the table

Step 6: Click on Next

Step 7: Click Finish

Try Yourself:

Q Consider the following table: Employee:

EMPID	NAME	SALARY	DOJ	COMM	DEPT	GENDER
1001	Rohan	7000	10/04/2006	300	SALES	M
1002	RISHU	12000	08/12/2005	100	FINANCE	F
1003	DEVANCH	9000	25/052004	500	SALES	M
1004	SUMAN	10000	23/08/2005	300	MARKETING	F
1005	ARYAN	11000	16/07/2005	500	SALES	M
1006	TAMANNA	15000	22/09/2005	200	FINANCE	F
1007	ROHIT	8000	26/08/2004	900	SALES	M

Write SQL Commands

a) To display the details of all employees of Sales Department.

b) To increase the SALARY by 1000 whose EMPID is 1007.

c) To display the details of all employees whose salary is more than 10000.

d) To display the list of all employees in descending order of salary.

Q  Consider the following tables: Sales:

Sale_Id	Prod_Name	Price	Discount
1101	Laptop	65000	2500
1103	Pen tab	29500	1000
1105	Desktop	50000	1550
1106	Printer	12000	2000

1. How many fields and records are there in Sales table?

2. Write SQL commands for the following:

a) Display Sales_ID and price of all the products whose discount is more than 1000.

b) Display the details alphabetically by product name.

c) Display product name and sale price after deducting the discount form the price.

    Note: Sales price can be calculated as (price-discount)

To see solution of these questions, please click the link below:

Solutions

Please  do Like, Subscribe and comment on my blog and YouTube Channel.

Check Your Progress:

1. Which of the following can be considered as an example of a database?  

(a) Dictionary  

(b) Telephone directory  

(c) Marks Register  

(d) Newspaper

Answer: b

2. Which of the following is NOT a DBMS?  

(a) MS Access  

(b) Open Office Base  

(c) MS Excel  

(d) MySQL

Answer: c

3. DBMS stands for ______________________.  

(a) Data and Books Management System  

(b) Database Management System  

(c) Duplicate Books Management System  

(d) Data Management Multi System

Answer: b

4. Which of the following data models sets a relation between the two or more tables?  

(a) Relational Data Model  

(b) Network Data Model  

(c) Hierarchical Data Model  

(d) Connection Data Model

Answer: a

5. The details associated with an entity are called ____________.  

(a) Table  

(b) Attributes  

(c) Records  

(d) Primary key

Answer: c

6. A __________ is represented as rows in a table.  

(a) field  

(b) attribute  

(c) record  

(d) candidate key

Answer: c

7. In which of the following forms can a data value be represented?  

(a) Numeric  

(b) Character  

(c) Alphanumeric  

(d) All of the above

Answer: d

8. Which of the following uniquely identifies a row in a table?  

(a) Primary key  

(b) Alternate key  

(c) Foreign key  

(d) Candidate key

Answer: a

9. A ___________is a feature of a database using which we can enter data in a table in an easy and user-friendly manner.  

(a) query  

(b) report  

(c) form  

(d) field

Answer: c

10. A _____________ is a question asked from a database.  

(a) query  

(b) report  

(c) form  

(d) field

Answer: a

11. Which SQL clause is used to sort the result set in ascending or descending order?  

a) group by  

b) order by  

c) sort by  

d) arrange by

Answer: b

12. What is SQL used for?  

a) Managing emails  

b) Creating and managing databases  

c) Playing video games  

d) Designing website

Answer: b

13. Which statement about a foreign key is true?  

a) It must always be unique within a table  

b) It establishes a link between two tables  

c) It is used to sort records in a table  

d) It can be empty in a table

Answer: b

14. Which SQL statement is used to delete table from a database.  

a) remove  

b) delete  

c) erase  

d) drop

Answer: b

15. Which of the following is a disadvantage of a flat-file database compared to a relational database?  

a) It is easier to manage  

b) It can store more complex data  

c) It is more prone to data redundancy  

d) drop

Answer: c

16. Which SQL clause is used to filter rows based on specified conditions?  

a) WHERE  

b) WHICH  

c) FILTER  

d) SORT

Answer: a

17) Which SQL function is used to find the total number of rows in a table?  

a) COUNT(*)  

b) SUM()  

c) TOTAL()  

d) ROWS()

Answer: a