DATABASE MANAGEMENT SYSTEM-(MYSQL)

 Write short answer to these questions:

a.       Define the term database with example.

A database is a systematic and organized collection of data that can be easily accessed, managed, and updated. Examples include school records (names, grades, and attendance) or a mobile phone contact list.

b.      What is schema in database?

A schema is the structure or organization of database objects; for example, a defined schema ensures each column has a specific data type like text, number, or date.

c.       Write any four functions of DBMS.

Four functions include data storage in tables, data retrieval using commands, data manipulation (adding/updating/deleting), and security control over who can access data.

d.      Write any two RDBMS software.

Two RDBMS are MySQL and Oracle Database.

e.       What is mean by MYSQL? Write the function of primary key.

MySQL is a popular open-source Relational Database Management System (RDBMS) that uses SQL to manage data. The function of a primary key is to uniquely identify each record in a table, ensuring no duplicate or NULL values exist in that column.

f.        What is the difference between int and float data type?

The INT data type is used for whole numbers, while the FLOAT data type is used for approximate numbers with decimals.

g.       Which data type are used to store name and salary of employee?

Data types used to store name and salary of employee are: VARCHAR (variable-length text) is used for names, and DECIMAL or FLOAT is used for salaries.

h.       Write the purpose of select query.

The purpose is to retrieve specific data from one or more tables so users can view information matching certain conditions.

i.         Why is report in SQL necessary?

Reports are necessary in SQL because they are used to retrieved data in an organized, readable, and meaningful format, helping users analyze information and make decisions.

j.        What is the main difference between data and information?

Main difference between data and information are: Data consists of raw, unprocessed facts that may not have meaning alone, while information is the useful result obtained after processing and organizing that data.

k.       What is RDBMS?

A Relational Database Management System (RDBMS) is a type of DBMS that stores data in tables made of rows and columns, linking them together through common fields.

l.         Define DBMS with one clear example.

A Database Management System (DBMS) is application software designed to store, manage, and organize data efficiently. An example is Microsoft SQL Server.

m.    What is primary key? Why is it important in database table?

A primary key is a field that uniquely identifies each record and cannot be empty. it is important because it maintains data accuracy, prevents duplicates, and allows tables to be linked.

n.      Write the use of foreign key in relational database.

Use of a foreign key in a relational database: A foreign key is used to create a relationship between two tables by referring to the primary key of another table, ensuring linked data remains valid.

o.      What is the purpose of the create command in MYSQL?

Purpose of the CREATE command is that it is used to create new database objects, such as new databases, tables, views, and indexes.

p.      Name any 2 DDL command.

Two DDL commands are CREATE and ALTER.

q.      Why do we use the SELECT command in MYSQL?

We use SELECT command to filter, sort, and search for specific records within a database to obtain meaningful information.

r.        List any two clauses used with SQL statements.

Two common clauses used with SQL statements are WHERE and LIKE.

s.       What is the function of ORDER BY clause in MYSQL?

Function of the ORDER BY clause is to sorth data using certain criteria.

t.        Which command is used to remove specific record from a table? Write its syntax.

Command to remove a specific record and its syntax are:

The DELETE command is used to remove a specific record and its Syntax is: DELETE FROM table_name WHERE condition;

u.      Write any two differences between DDL and DML commands.

Two differences between DDL and DML: DDL defines the physical structure of the database while DML works with the actual content (records); additionally, DDL changes are permanent (cannot be rolled back), whereas DML operations can be rolled back.

v.       Write any two reasons why we use database management system in computing?

Two reasons to use a DBMS in computing are:

i) It reduces data redundancy (duplication)

ii) It allows multiple users to access data simultaneously while maintaining consistency.

w.     What do you mean by redundancy in a database system?

Redundancy means data duplication where information is repeated unnecessarily within the system.

x.       Why should the WHERE clause be used carefully with UPDATE and DELETE command?

WHERE clause should be used carefully with UPDATE carefully because if the WHERE clause is omitted, the command will modify or remove every record in the entire table.

y.       What is relationship in MYSQL?

A relationship is a logical connection between two or more tables based on common fields (keys), allowing separate data to be accessed together in an organized way.

 

z.         

  Write long answers to the following questions.

a.       What is data? Differentiate between data and information.

Difference Between Data and Information

Data	Information
Raw facts, figures, or details without clear meaning.	Processed, organized, and meaningful output derived from data.
Unprocessed, unorganized, and scattered.	Structured, analyzed, and useful.
Numbers, words, images, symbols (e.g., “2082, 95, Binay”).	Statements or insights (e.g., “Binay scored 95 marks in 2082 BS and is a top performer”).
Has no meaning on its own.	Carries meaning and helps in understanding situations.
Limited until processed.	Highly useful for decision‑making and problem‑solving.
“2082, 95, Binay”	“Binay scored 95 marks in 2082 BS and is a top performer.”

b.       What is DBMS? Write the advantages and disadvantages of DBMS.

A Database Management System (DBMS) is application software designed to store, manage, and organize data efficiently while acting as an intermediary between users and the database.

Advantages of DBMS:

·       It minimizes data duplication by centralizing storage.

·       It ensures that data follows specific rules and remains accurate.

·       It allows many users to access and work with the data simultaneously.

·       It controls who can access specific data and what actions they can perform.

·       It provides tools to recover data in case of accidental loss.

Disadvantages (Challenges) of DBMS:

·       It requires specialized skills to manage and operate.

·       Setting up a professional DBMS can be expensive.

·       Large systems require regular and sometimes complex maintenance.

·       If not properly managed, centralized data can be vulnerable to unauthorized access.

·       The system may become slow when handling extremely large volumes of data.

c.       Why are keys necessary in database? How does primary key differ from foreign key?

Keys are necessary because they allow a database to uniquely identify specific records and create logical connections between different tables. They help keep data organized, accurate, and connected across the system.

A Primary Key is a field that uniquely identifies each record within its own table; it cannot contain duplicate values and cannot be empty (NULL). Only one primary key is allowed per table. However, a Foreign Key is a field in one table that refers to the primary key of another table. Its purpose is to create a relationship between tables and ensure that the linked data remains valid.

d.        What is relationship in database? Explain the type of relationship with real life examples.

A relationship is a logical connection between two or more tables, usually based on common fields like keys. They reduce redundancy and allow data to be retrieved accurately.

Types of Relationships:

1. One-to-One (1:1): Each record in the first table connects to exactly one record in the second table. Example: One person can have only one passport, and one passport belongs to only one person.
2. One-to-Many (1:M): A single record in one table links to multiple records in another table. Example: One teacher may teach many different classes, but each class is assigned to only one teacher.
3. Many-to-Many (M:M): Multiple records in one table relate to multiple records in another. Example: A student can enroll in many courses, and each course can have many students.

e.       What is ERD? Draw an ERD about parent child relationship with men and women.

A diagram used to show how entities (objects) in a database are related to each other is called Entity-relationship diagram. It helps in designing the logical structure of a database.

f.     

 

g.       What is database language? Difference it between DDL and DML statements.

A database language consists of instructions used to communicate with a DBMS to perform tasks like defining structures or managing records.

Differences between DDL and DML are as follows:

 

DDL (Data Definition Language)	DML (Data Manipulation Language)
Defines and manages the structure of database objects (tables, schemas, indexes).	Manipulates and works with the actual data stored in database objects.
CREATE, ALTER, DROP,	INSERT, UPDATE, DELETE, SELECT
Changes the database schema (structure).	Changes the database content (records).
Changes are permanent and cannot be rolled back.	Changes can be rolled back if COMMIT has not been executed.
Executed automatically and immediately affect the database structure.	Executed manually; requires COMMIT to make changes permanent.
Used by database administrators to design and maintain database structure.	Used by users/programmers to manage and query data.

h.       What is MYSQL? Explain the features of MYSQL.

MySQL is a popular, open-source Relational Database Management System (RDBMS) that uses SQL to store and organize data. It is widely used in web development and modern applications due to its speed and reliability.

Features of MySQL:

·       Easy to Learn: SQL is simple and straightforward, so beginners can pick it up quickly.

·       Strong Data Management: It provides powerful tools to define structures, manage data, and retrieve information easily.

·       Supports Many Users: Multiple people can work with the same database at the same time without problems.

·       Built‑in Security: Databases include features that protect information from unauthorized access.

·       Widely Used: SQL databases are common in modern web and mobile applications, making them versatile and practical.

i.         What is table in database? Explain the common string data type used in MYSQL.

A table is the primary building block of a database, representing a structured way to store data in horizontal rows (records) and vertical columns (fields).

Common String (Character) Data Types in MySQL:

• CHAR(n): Stores fixed-length text.
• VARCHAR(n): Stores variable-length text up to n characters, making it more flexible for names or addresses.
• TEXT: Used for storing long blocks of text, such as large paragraphs or descriptions.

j.         Why do most relational database system are more popular than other models? Describe.

Relational models are popular because they organize data into a logical structure of rows and columns that mimics how we think about information. The reasons for their popularity are listed below:

• Efficiency: They avoid data duplication by using relationships.
• Meaningful Connections: Tables represent specific entities (like students or courses) and are linked using keys, making the data more connected and meaningful.
• Data Integrity: Through constraints and keys, they ensure that the data remains accurate and valid across the entire system.

k.       What is a primary key? Illustrate your answer with a suitable example.

A primary key is a unique identifier for a record in a table that ensures no two rows are identical and no row is left empty.

Example: In a Students table, the field StudentID would be the primary key. While two students might have the same name or age, every student is assigned a unique ID number that distinguishes them from everyone else in the system.

l.         Explain the differences between a database and DBMS with example.

Database	DBMS (Database Management System)
An organized collection of related data stored systematically.	Software used to create, manage, and interact with the database.
Stores raw data such as names, grades, and attendance records.	Helps users add, update, delete, and retrieve data efficiently.
Holds the actual data in tables, rows, and columns.	Provides tools and commands (like SQL) to manage and query the data.
Users cannot directly manipulate data without a management system.	Users interact through interfaces or queries provided by the DBMS.
Data storage and organization.	Data management, security, and accessibility.

 

m.     What is primary key? List any four features or functions of primary key in a table.

A primary key is a unique identifier for a record in a table that ensures no two rows are identical and no row is left empty.

The four main features of a primary key in a table are:

1. It uniquely identifies each record so no two rows are the same.
2. It prevents the entry of duplicate values in that specific column.
3. The primary key column cannot be left empty; every record must have a value.
4. Only one primary key is allowed per database table.

n.       Describe the function of following SQL commands: CREATE, DROP, INSERT and SELECT.

CREATE: Used to establish new database objects, such as creating a new database or a new table with defined columns.

DROP: Used to permanently delete a database object (like a table or an entire database); once dropped, the data cannot be recovered.

INSERT: A DML command used to add new records (rows) into an existing table.

SELECT: The most common DML command, used to retrieve and view specific data from one or more tables based on the user's needs.

o.       What are clauses in SQL? Explain the uses of WHERE, ORDER BY and GROUP BY clauses.

Clauses are components of SQL statements that allow users to filter, sort, or extract data based on specific criteria.

• WHERE: Used to filter records so that only those meeting specific conditions are retrieved. For example, WHERE Class = 10 will only show students in that specific grade.
• ORDER BY: While not defined with syntax in the text, it is listed as a constraint/feature used for sorting data results in an organized way.
• GROUP BY: The sources mention Grouped Reports which categorize data by groups like department or city. This clause is used to organize data into categories for analysis, such as summarizing total sales per region.

 

p.       Write suitable SQL commands to perform the following tasks:

a.      To create a database as ‘Mydatabase’.

CREATE DATABASE Mydatabase

b.     To create a table as college with the required columns with Stdid, name, address, class, phone number.

CREATE TABLE college (

    Stdid INT,

    name VARCHAR(50),

    address VARCHAR(100),

    class INT,

    phone_number VARCHAR(15)

);

c.      To add Column email in the table ‘college’.

ALTER TABLE college ADD email VARCHAR(100);

d.     To delete record from college table whose age is 16.

DELETE FROM college WHERE age = 16;

e.      To delete email and gender column from employee table.

ALTER TABLE employee DROP COLUMN email, DROP COLUMN gender;

f.      To remove table as ‘Customer’ permanently with its objects.

DROP TABLE Customer;

g.     To change ‘Ramesh’ into ‘Bhuvan’ in the ‘first_name’ column of employee table.

UPDATE employee SET first_name = 'Bhuvan' WHERE first_name = 'Ramesh';

h.     To insert ID, Name and age of three students in School table.

INSERT INTO School (ID, Name, Age)

VALUES (1, 'Arjun', 15), (2, 'Sita', 16), (3, 'Kiran', 15);

i.      To display all data of the cities whose humidity is in the range of 60 to 75 from the ‘weather’ table.

SELECT * FROM weather WHERE humidity >= 60 AND humidity <= 75;

j.       To display all the records from a table ‘employee’ whose name starts with letter ‘S’.

SELECT * FROM employee WHERE name LIKE 'S%';

k.     To select and retrive the last five entries for all columns in the ‘teacher’ table.

SELECT * FROM teacher ORDER BY Id DESC LIMIT 5;

l.      Sort and display the column ‘name’ in ascending order from the table ‘company’.

SELECT name FROM company ORDER BY name ASC;

m.    To display records where letter ‘A’ appears anywhere within the attribute name of the table ‘customer’.

SELECT * FROM customer WHERE name LIKE '%A%';

n.     To return citizen whose country starts with ‘N’ followed by a single character.

SELECT * FROM citizen WHERE country LIKE 'N_';

o.     To display Id, Name, Post and Salary whose salary is more than 50000 from teacher table.

SELECT Id, Name, Post, Salary FROM teacher WHERE salary > 50000;

p.