DBMS Notes.pdf

DBMS NOTES 1. Define data, the major component of a database. Answer: Data is raw numbers, characters, or facts represented by a value. Most organizations generate, store, and process large amounts of data. The data acts as a bridge between the hardware and the software. Data m...

DBMS NOTES 1. Define data, the major component of a database. Answer: Data is raw numbers, characters, or facts represented by a value. Most organizations generate, store, and process large amounts of data. The data acts as a bridge between the hardware and the software. Data may be of different types such as User data, Metadata, and Application Metadata. 2. Shubham is learning about DBMS. He wants to know about the following: (4) (a) The purpose of DBMS (b) Any two uses of DBMS Answer: (a) DBMS (Database Management System) is used to store logically related information at a centralized location. It facilitates data sharing among all the applications requiring it. (b) Two uses of the database management system are as follows: DBMS is used to store data at a centralized location. It is used to minimize data redundancy and data inconsistency. 3. Lavish wants to know the following points about the database. (a) Definition of a Database Management System. (b) Any two advantages of using a database management system for school. Answer: (a) A Database Management System (DBMS) is a collection of programs that enable users to create, and maintain a database, and control all the access to the database. The primary goal of the DBMS is to provide an environment that is both convenient and efficient for users to retrieve and store information. (b) The advantages of using DBMS for school are as follows: In school, DBMS is used to store data about students, teachers, and any other related things at a centralized location. It provides security to the personal information of the school, stored in it. 4. What is the role of key fields in a database? Name the keys in a DBMS. Answer: The key is defined as the column or the set of columns of the database table which is used to identify each record uniquely in a relation, e.g. If a table has id, name, and address as the column names then say id is the key for that table as it is a unique identifier for each record. Alternate, foreign, primary, and candidate are a few types of keys in a DBMS. 5. Your friend owns a chemist shop, he needs to keep records of the medicines with their id’s, date of purchase, expiry date, price, etc. in a database program. But he does not have any knowledge about the database. Explain to him the following to get a better understanding of the DBMS concepts. 1. What is DBMS? Explain in brief. 2. Name any two database programs which can be used to create a table and store the data as per the requirement. 3. Which field can be set as a Primary Key?  4. Is it possible to make more than one field as a primary key in your table? (Yes/No). Justify your answer. 1. A database management system is a software package with computer programs that controls the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications. 2. Two databases are: i. Microsoft Access ii. MySQL iii. OpenOffice Base 3. Id field will be suitable for Primary Key. 4. Yes, we can make more than one column as a primary key in a table and it is known as Composite Primary Key. 6. List Numeric and Alphanumeric Datatypes in OpenOffice Base. Numeric datatypes are: TinyInt, SmallInt, Integer, BigInt, Decimal, Real, Float, Double and Boolean. Alphanumeric Datatypes are: Char(fix), Varchar and Longvarchar(Memo) 7. Your friend’s father owns a restaurant. He manually enters the customer’s records in a register. You want to explain to him the importance of creating a database on a computer. Tell the advantages of using a computerized database with the help of the following points: (a) Data redundancy (b) Data inconsistency (c) Confidentiality (d) Sharing Answer: (a) Data redundancy means duplication of data computerised database avoids duplication of data and ensures that there is only one instance of certain data. (b) Data inconsistency helps, if a single database is used by multiple users then it ensures that the same data is present for all the users. (c) The DBMS can ensure different views for the different users of the database. This keeps the confidentiality of the data safe. (d) Different users can use the same database to access the data according to their needs. Hence, DBMS provides the sharing of data and resources. 8. Write the steps to insert a record in a Datasheet View. Answer: The steps to insert a record in a Datasheet View are as follows: Step 1: When you create a table, a new blank record automatically appears in the second row of the table. Step 2: If you enter data in the last record, a new blank record will automatically appear at the end of the table. Step 3: Type data into the fields. Step 4: When you have finished adding records in the datasheet, save it and close it. 9. What is the purpose of a primary key in a database management system? Answer: The primary key in a DBMS uniquely identifies each record in a table and ensures data  integrity. It enforces data integrity by preventing duplicate and null values. Also, it facilitates efficient data retrieval and serves as a reference for establishing relationships between tables. 10. What is the role of a foreign key in a database management system (DBMS)? Answer: In a DBMS, a foreign key establishes a relationship between two tables. It references the primary key of another table, enforcing referential integrity. It ensures data consistency by preventing actions that would violate the defined relationships, such as deleting a record referenced by a foreign key constraint. 11. What is Referential Integrity? Explain its purposes. Answer: Referential Integrity is a property of data stating that all its references are valid. In the context of relational databases, it is required that if a value of one attribute of a relation references a value of another attribute, then the referenced value must exist. Referential integrity is a database concept that is used to build and maintain logical relationships between two tables. It helps to maintain the accuracy and consistency of data in RDMS. 12. Define the following types of integrity: (i) Entity integrity (ii) Domain integrity (iii) Referential integrity (iv) User-defined integrity Answer: (i) Entity Integrity: It defines the primary key of a table. Entity integrity rule on a column does not allow duplicate and null values. (ii) Domain Integrity: It defines the type, range, and format of data allowed in a column. Domain integrity states that all values in a column must be of the same type. (iii) Referential Integrity: It defines the foreign key concepts. Referential integrity ensures that data in related tables remains accurate and consistent before and after changes. (iv) User-Defined Integrity: If some business requirements do not fit any above data integrity then the user can create their integrity, which is called user-defined integrity. 13. When is the Memo data type is preferred over the Text data type for a field? (2) Answer: Text data type is not capable of storing the project description because its length cannot be more than 255 characters so the memo data type is preferred over the Text data type. 14. Ajay is learning about the different terms and commands of a database. He came across the following terms. Tell him the definition of the following. (i) Insert command (ii) Select command (iii) Delete command Answer: (i) INSERT Command: It is used to add a single record or multiple records into a table. Syntax: INSERT INTO (col1, col2…) VALUES (val1, val2);  (ii) SELECT Command: It is used to query or retrieve data from a table in the database. Syntax: SELECT column_list FROM table_name WHERE condition; (iii) DELETE Command: To discard unwanted data from a database, the delete command is used. Syntax: DELETE FROM table_name WHERE condition; 15. Consider the following table: Employee (a) How many fields and records are there in the table Employee? (b) Write the SQL queries for the following: (i) Display Ename and salary of those employees whose Salary is less than or equal to 16000. (ii) Display details of employees who are getting a commission of ₹ 300 or greater. (iii) Display all the details of the employee profile ‘CLERK’. Answer: (a) There are 8 fields and 6 records in the table Employee. (b) (i) SELECT Ename, Salary FROM Employee WHERE Salary< = 16000; (ii) SELECT* FROM Employee WHERE Commission >=300; (iii) SELECT *FROM EMPLOYEE WHERE Profile="CLERK’; 16. Consider the following table: SHOPPE TABLE: SHOPPE Id SName Area S001 ABC Computeronics CP S002 All Infotech Media GK II S003 Tech Shoppe CP S004 Geeks Tecno Soft Nehru Place S005 Hitech Tech Store Nehru Place (i) How many fields and records are there in the SHOPPE table? (ii) Write SQL commands for the following: (a) Display Id and SName of all the shops located in Nehru Place. (b) Display the details alphabetically by SName. (c) Display the SName of shops whose Area is CP. Answer: (i) There are 3 fields and 5 records in the table SHOPPE. (ii) (a) SELECT Id, SName FROM SHOPPE WHERE Area= ‘Nehru Place’;  (b) SELECT * FROM SHOPPE ORDER BY SName; (c) SELECT SName FROM SHOPPE WHERE Area=’CP’; 17. A table named School (containing data of students of the entire school) is created, where each record consists of several fields including AdmissionNo (Admission Number), RollNo (Roll Number), and Name. Which field out of these three should be set as the primary key and why? (2) Answer: AdmissionNo should be set as the primary key because admission numbers are unique for every student of the school, which is not possible in the case of RolINo and Name. 18. Consider the following table: SummerCamp. (i) How many fields and records are there in the table SummerCamp? (ii) Write SQL queries for the following: (a) Display all the records of table SummerCamp whose DOB is between 02/02/09 and 06/03/10. (b) Display all the records having Gender F. (c) Display all the records according to the First Name. Answer: (i) There are 6 fields and 7 records in the table SummerCamp. (ii) (a) SELECT * FROM SummerCamp WHERE DOB BETWEEN 02/02/09 AND 06/03/10; (b) SELECT * FROM SummerCamp WHERE Gender=’F’; (c) SELECT * FROM SummerCamp ORDER BY FirstName; 19. Consider the following table: 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: i. Display Sales ID and price of all products whose discount is more than 1000. ii. Display the details alphabetically by product name. iii. Display product name and sales price after deducting the discount from the price. Note: Sales price can be calculated as (price-discount) 1. Four fields and four records are there in table Sales. 2. SQL commands: i. select Sale_Id, Price from Sales where Discount > 1000; ii. select * from Sales order by Prod_Name;  iii. select Prod_Name, Price- Discount from Sales; Define the following: 1. Many-to-Many Relationship (with respect to DBMS) 2. Foreign key Many to Many Relationship 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. Example: In the given tables EMP and DEPT, there is no primary key. Foreign key (FK): identifies a column or set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The “one” side of a relation is always the parent, and provides the PK attributes to be copied. The “many” sides of a relation is always the child, into which the FK attributes are copied.