Unit -2 Database Management System
1. Choose the correct answer.
a. Which of the following is NOT a function of a DBMS?
i. Data storage
ii. Data retrieval
iii. Word processing
iv. Data manipulation
b. Which type of key uniquely identifies each record in a table?
i. Foreign Key
ii. Composite Key
iii. Primary Key
iv. Candidate Key
c. What is the primary purpose of a foreign key?
i. To store only text data.
ii. To uniquely identify each record in its own table.
iii. To establish and enforce relationships between tables.
iv. To combine multiple fields into one.
d. In a one-to-many relationship between teachers and classes, which statement is true?
i. Each teacher teaches only one class, and each class has only one teacher.
ii. Each teacher can teach multiple classes, and each class has only one teacher.
iii. Each teacher teaches only one class, and each class can have multiple teachers.
iv. Each teacher can teach multiple classes, and each class can have multiple teachers.
e. Which SQL command is used to add new rows to a table?
i. SELECT
ii. UPDATE
iii. INSERT
iv. DELETE
f. Which SQL command is used to modify existing data in a table?
i. SELECT
ii. UPDATE
iii. INSERT
iv. DELETE
g. Which SQL clause is used to specify conditions for data retrieval?
i. SELECT
ii. FROM
iii. WHERE
iv. ORDER BY
h. What does the SQL LIKE clause help you do?
i. Perform extract matches.
ii. Search for patterns in data.
iii. Sort the data.
iv. Define data types.
i. Which SQL data type is used for storing only date values (e.g. YYYY-MM-DD)?
i. TIME
iii. TIMESTAMP
ii. DATETIME
iv. DATE
j. Which SQL data type is suitable for storing names and addresses, allowing for variable length?
i. CHAR
iii. INT
ii. TEXT
iv. VARCHAR
k. What is the primary difference between CHAR(n) and VARCHAR(n) data types?
i. CHAR stores only numeric data, while VARCHAR stores text.
ii. CHAR has a fixed length, while VARCHAR has a variable length up to n characters.
iii. VARCHAR is used for dates, while CHAR is for text.
iv. There is no significant difference between them.
3. Write short answers to these questions.
a. Define data and information.
Ans: Data are raw facts and figures (like numbers, names).
Information is processed data that has meaning and is useful.
b. What is primary key?
Ans: A primary key is a field that uniquely identifies each record in a table. It cannot have duplicate or null values.
c. What is the foreign key?
Ans: A foreign key is a field in one table that links to the primary key of another table to create a relationship.
d. Define fields and rows.
Ans: Fields are columns in a table that store a specific type of data.
Rows (records) are horizontal entries that contain data about one item.
e. Differentiate database and DBMS.
Ans: A database is a collection of related data.
A DBMS (Database Management System) is software used to create, manage, and control the database.
f. Describe RDBMS.
Ans: RDBMS (Relational Database Management System) stores data in tables and uses relationships between those tables.
g. Why is a DBMS considered more advantageous for managing large amounts of data compared to a simple spreadsheet?
Ans: A DBMS can handle large data efficiently, reduces duplication, improves security, and allows multiple users to access data at the same time.
h. Describe the purpose of primary and foreign keys in a relational database.
Ans: Primary key uniquely identifies records, while foreign key connects tables and maintains relationships.
i. Explain the concept of a one-to-many relationship and provide a real world example (different from the one in the text).
Ans: A one-to-many relationship means one record in a table is linked to many records in another table.
Example: One customer can place many orders, but each order belongs to one customer.
j. Briefly describe the roles of DDL and DML in SQL.
Ans: DDL (Data Definition Language) is used to create and modify database structure (e.g., CREATE, ALTER).
DML (Data Manipulation Language) is used to insert, update, and delete data (e.g., INSERT, UPDATE, DELETE).
k. Explain the function of the WHERE clause in a SELECT statement.
Ans: The WHERE clause is used to filter records based on specific conditions.
l. What are SQL constraints? Why are they important for maintaining data integrity?
Ans: SQL constraints are rules applied to table columns (like NOT NULL, UNIQUE).
They are important because they ensure accuracy and consistency of data.
m. What is a composite key, and when might it be necessary to use one?
Ans: A composite key is a combination of two or more fields used to uniquely identify a record.
It is used when a single field is not enough to uniquely identify records.
4. Write long answers to these questions.
a. Define DBMS with its advantages.
Ans: A Database Management System (DBMS) is a software used to create, store, organize, and manage data in a database. It helps users to easily insert, update, delete, and retrieve data.
Advantages of DBMS:
- Reduces data redundancy: Avoids repetition of the same data.
- Improves data security: Only authorized users can access data.
- Data consistency: Ensures accurate and reliable data.
- Easy data access: Data can be quickly retrieved using queries.
- Backup and recovery: Data can be saved and restored if lost.
- Multi-user access: Many users can work on the same database at the same time.
b. What is the importance of primary key in db? List out its features. Ans: A primary key is a field (or set of fields) in a table that uniquely identifies each record. Importance of Primary Key: - It ensures that each record is unique. - Helps in identifying and retrieving data quickly. - Prevents duplicate records. - Helps to create relationships between tables. Features of Primary Key: - Must be unique (no repetition). - Cannot be NULL (empty). - Contains one value per record. - Can be single or composite (more than one field). - Remains constant (should not change frequently). c. Differentiate between DDL and DML. Ans:
d. Show difference between Table and Query.
Ans:
e. What is the relationship in DBMS? Explain its types.
Ans: A relationship in DBMS is a connection between two or more tables using common fields (usually keys).
Types of Relationships:
1. One-to-One (1:1):
One record in one table is related to only one record in another table.
Example: One person → One citizenship number
2. One-to-Many (1:M):
One record in one table is related to many records in another table.
Example: One teacher → Many students
3. Many-to-Many (M:N):
Many records in one table are related to many records in another table.
Example: Students ↔ Subjects
f. What is a Query? List out its importance in DBMS. Ans: A query is a request to the database to get specific data. Importance of Query: - Helps to retrieve specific data quickly - Used to filter data (using conditions) - Helps in sorting and organizing data - Can be used to update or delete data - Supports decision making by showing useful information - Saves time compared to manual searching
g. What is a report in the context of MySQL and what is its purpose? Ans: A report in MySQL is a formatted output of data taken from the database, usually created using queries. It presents data in a structured and readable way. Purpose of Report: - To present data clearly - To help in analysis and decision making - To summarize large data - To generate printable output - To show results in a professional format
h. Describe report with its features. Ans: A report is an organized presentation of data from a database, usually displayed in a formatted layout. Features of Report: - Well formatted: Organized in headings and sections - Readable: Easy to understand - Summarized data: Shows totals, averages, etc. - Printable: Can be printed or exported - Customizable: Users can design layout as needed - Data from queries: Uses query results - Professional look: Suitable for official use
i. Write down the SQL query for the following statement. a. Write an SQL query to create a new database named CompanyData. Ans: CREATE DATABASE CompanyData; b. Write an SQL query to create a table named Employees with the following columns:- - - - EmployeeID as an integer (INT) and the primary key. - FirstName as a variable-length string (VARCHAR) with a maximum length of 50 characters. - LastName as a variable-length string (VARCHAR) with a maximum length of 50 characters. - Salary as a decimal number (DECIMAL) with a total of 10 digits and 2 digits after the decimal point. Ans: CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Salary DECIMAL(10,2) ); c. Write an SQL query to add a new column named Email to the Employees table. The data type for this column should be a variable-length string (VARCHAR) with a maximum length of 100 characters. Ans: ALTER TABLE Employees ADD Email VARCHAR(100); d. Write an SQL query to modify the Salary column in the Employees table to have a default value of 25000. Ans: ALTER TABLE Employees MODIFY Salary DECIMAL(10,2) DEFAULT 25000; e. Write an SQL query to rename the Employees table to StaffMembers. Ans: ALTER TABLE Employees RENAME TO StaffMembers; f. Write an SQL query to insert a new record into the StaffMembers table with the following values: [ EmployeeID : 101, FirstName : Sampada, lastName : Bhattrai, Salary : 30000.50 ] Ans: INSERT INTO StaffMembers (EmployeeID, FirstName, LastName, Salary) VALUES (101, 'Sampada', 'Bhattrai', 30000.50); g. Write an SQL query to retrieve all columns and all rows from the table. Ans: SELECT * FROM StaffMembers; h. Write an SQL query to retrieve only the FirstName and Salary columns from the Employees table. Ans: SELECT FirstName, Salary FROM StaffMembers; i. Write an SQL query to increase the salary of all employees by 10%. Ans: UPDATE StaffMembers SET Salary = Salary * 1.10; j. Write an SQL query to delete the employee with EmployeeID 103 from the table. Ans: DELETE FROM StaffMembers WHERE EmployeeID = 103; k. Write an SQL query to delete all records from the Employees table (or StaffMembers), but keep the table structure intact. Ans: TRUNCATE TABLE StaffMembers;
b. What is the importance of primary key in db? List out its features. Ans: A primary key is a field (or set of fields) in a table that uniquely identifies each record. Importance of Primary Key: - It ensures that each record is unique. - Helps in identifying and retrieving data quickly. - Prevents duplicate records. - Helps to create relationships between tables. Features of Primary Key: - Must be unique (no repetition). - Cannot be NULL (empty). - Contains one value per record. - Can be single or composite (more than one field). - Remains constant (should not change frequently). c. Differentiate between DDL and DML. Ans:
| DDL (Data Definition Language) | DML (Data Manipulation Language) |
|---|---|
| i) Used to define database structure | i) Used to manipulate data |
| ii) Works on tables and schema | ii) Works on data inside tables |
| iii) Commands: CREATE, ALTER, DROP | iii) Commands: INSERT, UPDATE, DELETE, SELECT |
| iv) Changes structure of database | iv) Changes content of database |
| v) Auto-commit (cannot be rolled back easily) | v) Can be rolled back |
| Table | Query |
|---|---|
| i) Stores data in rows and columns | i) Used to retrieve data |
| ii) Permanent storage | ii) Temporary result |
| iii) Contains actual data | iii) Shows filtered data |
| iv) Created once and stored | iv) Created to perform specific tasks |
| v) Example: Student table | v) Example: Show students with marks > 50 |
f. What is a Query? List out its importance in DBMS. Ans: A query is a request to the database to get specific data. Importance of Query: - Helps to retrieve specific data quickly - Used to filter data (using conditions) - Helps in sorting and organizing data - Can be used to update or delete data - Supports decision making by showing useful information - Saves time compared to manual searching
g. What is a report in the context of MySQL and what is its purpose? Ans: A report in MySQL is a formatted output of data taken from the database, usually created using queries. It presents data in a structured and readable way. Purpose of Report: - To present data clearly - To help in analysis and decision making - To summarize large data - To generate printable output - To show results in a professional format
h. Describe report with its features. Ans: A report is an organized presentation of data from a database, usually displayed in a formatted layout. Features of Report: - Well formatted: Organized in headings and sections - Readable: Easy to understand - Summarized data: Shows totals, averages, etc. - Printable: Can be printed or exported - Customizable: Users can design layout as needed - Data from queries: Uses query results - Professional look: Suitable for official use
i. Write down the SQL query for the following statement. a. Write an SQL query to create a new database named CompanyData. Ans: CREATE DATABASE CompanyData; b. Write an SQL query to create a table named Employees with the following columns:- - - - EmployeeID as an integer (INT) and the primary key. - FirstName as a variable-length string (VARCHAR) with a maximum length of 50 characters. - LastName as a variable-length string (VARCHAR) with a maximum length of 50 characters. - Salary as a decimal number (DECIMAL) with a total of 10 digits and 2 digits after the decimal point. Ans: CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Salary DECIMAL(10,2) ); c. Write an SQL query to add a new column named Email to the Employees table. The data type for this column should be a variable-length string (VARCHAR) with a maximum length of 100 characters. Ans: ALTER TABLE Employees ADD Email VARCHAR(100); d. Write an SQL query to modify the Salary column in the Employees table to have a default value of 25000. Ans: ALTER TABLE Employees MODIFY Salary DECIMAL(10,2) DEFAULT 25000; e. Write an SQL query to rename the Employees table to StaffMembers. Ans: ALTER TABLE Employees RENAME TO StaffMembers; f. Write an SQL query to insert a new record into the StaffMembers table with the following values: [ EmployeeID : 101, FirstName : Sampada, lastName : Bhattrai, Salary : 30000.50 ] Ans: INSERT INTO StaffMembers (EmployeeID, FirstName, LastName, Salary) VALUES (101, 'Sampada', 'Bhattrai', 30000.50); g. Write an SQL query to retrieve all columns and all rows from the table. Ans: SELECT * FROM StaffMembers; h. Write an SQL query to retrieve only the FirstName and Salary columns from the Employees table. Ans: SELECT FirstName, Salary FROM StaffMembers; i. Write an SQL query to increase the salary of all employees by 10%. Ans: UPDATE StaffMembers SET Salary = Salary * 1.10; j. Write an SQL query to delete the employee with EmployeeID 103 from the table. Ans: DELETE FROM StaffMembers WHERE EmployeeID = 103; k. Write an SQL query to delete all records from the Employees table (or StaffMembers), but keep the table structure intact. Ans: TRUNCATE TABLE StaffMembers;
End of Unit 2 : Database Management System
