Class 12 It Chapter 1 RDBMS Concepts in MYSQL

Class 12 It Chapter 1 RDBMS Concepts in MYSQL Solutions English Medium As Per AHSEC New Syllabus to each chapter is provided in the list so that you can easily browse through different chapters Class 12 It Chapter 1 RDBMS Concepts in MYSQL Question Answer and select need one. Class 12 It Chapter 1 RDBMS Concepts in MYSQL Notes Download PDF. AHSEC Class 12 Elective It-ITes Question Answer English Medium.

Class 12 It Chapter 1 RDBMS Concepts in MYSQL

Also, you can read the NCERT book online in these sections Solutions by Expert Teachers as per Central Board of Secondary Education (CBSE) Book guidelines. AHSEC Class 12 Elective It-ITes Textual Solutions are part of All Subject Solutions. Here we have given AHSEC Class 12 It Chapter 1 RDBMS Concepts in MYSQL Textbook Solutions English Medium for All Chapters, You can practice these here.

RDBMS Concepts in MYSQL

Chapter: 1

SESSION 1: RDBMS CONCEPTS

Check Your Progress

A. Multiple choice questions:

1. A database is a_____________.

(a) Organized collection of information that cannot be accessed, updated, and managed.  

(b) Collection of data or information without organizing.  

(c) Organized collection of data or information that can be accessed, updated, and managed.  

(d) Organized collection of data that cannot be updated.  

Ans: (c) Organized collection of data or information that can be accessed, updated, and managed. 

2. Which of the following is not a valid SQL type?  

(a) Float.  

(b) Numeric.  

(c) Decimal.  

(d) Character.  

Ans: (c) Decimal.  

3. In DBMS, table is known as ___________ and row is known as __________.  

(a) Relation, tuple.  

(b) Tuple, tuple.  

(c) Tuple, relation.  

(d) Relation, relation.  

Ans: (a) Relation, tuple.  

4. In any table, the data types describe the kind of ___________ that it can contain.  

(a) Table.  

(b) Data.  

(c) Number.  

(d) Column.  

Ans: (b) Data. 

5. The SQL statement used to select data items from the database is-

(a) SELECT.  

(b) USE.  

(c) ALTER.  

(d) CREATE.  

Ans: (b) USE.

6. The database can be renamed using __________ SQL statement. 

(a) CREATE DATABASE.  

(b) RENAME DATABASE.  

(c) DROP DATABASE.  

(d) SELECT DATABASE.  

Ans: (b) RENAME DATABASE. 

7. The syntax used to show all databases is-  

(a) USE DATABASES.  

(b) SELECT DATABASES.  

(c) SHOW DATABASES.  

(d) DISPLAY DATABASE.  

Ans: (c) SHOW DATABASES. 

8. In a database table the field which uniquely identifies each row in the table is known as ________________.  

(a) Primary key.  

(b) Unique key.  

(c) Composite key.  

(d) Foreign key.  

Ans: (a) Primary key. 

9. Foreign key is a ____________ key in another table.  

(a) Primary.  

(b) Unique.  

(c) Composite.  

(d) Candidate key.  

Ans: (a) Primary.  

10. The multiple columns that are used as primary key is known as-  

(a) Unique key.  

(b) Composite key.  

(c) Foreign key.  

(d) Candidate key.  

Ans: (b) Composite key.

11. Which of the following key is used to link between two tables.  

(a) Primary.  

(b) Foreign.  

(c) Composite.  

(d) Unique.  

Ans: (b) Foreign. 

12. A primary key cannot be-  

(a) Zero.  

(b) Foreign key.  

(c) Duplicate.  

(d) NULL.  

Ans: (d) NULL. 

B. Fill in the blanks:

1. In DBMS, table is known as _________ and row is known as _________. 

Ans: Relation, Tuple.

2. Organized collection of data or information for accessing, updating and management is known as __________ . 

Ans: DBMS.

3. A relational database consists of a collection of _____________. 

Ans: Tables.

4. To see all available databases in MySQL; __________ command is used. 

Ans: Show database.

5. Data Definition language is the language which is used to defining the __________ of relation. 

Ans: Structure.

6. In order to build a link between two tables, ___________ is used. 

Ans: Foreign key.

7. In order to make multiple columns as a Primary Key, ___________ can be used. 

Ans: Composite Key.

8. Foreign key is a field in a table that is __________ in another table. 

Ans: Primary Key.

9. A Key which uniquely identifies each row in the table is known as __________. 

Ans: Primary Key.

10. A foreign key can be ___________ or _______________. (null, duplicate).

Ans: Null, duplicate.

C. State whether True or False:

1. DBMS is an interface between Database application and database. 

Ans: True. 

2. Using the SQL statement RENAME DATABASE; a database can be renamed. 

Ans: True. 

3. To see all existing databases; SHOW DATABASES; syntax is used. 

Ans: True. 

4. A Primary Key is basically a Column or Columns. 

Ans: True. 

5. To make a link between two tables, we can use foreign key constraints. 

Ans: True. 

6. A Primary Key can be NULL.

Ans: False. 

7. A Foreign Key cannot be Duplicate. 

Ans: False. 

8. If multiple columns are used as Primary Key, it is known as Composite Key. 

Ans: True. 

9. There could be two Primary keys constraints in a single table. 

Ans: False. 

10. A Foreign Key cannot have NULL value.  

Ans: False. 

D. Short answer questions.

1. What is file system? Write down limitations of file system. 

Ans: In computers, any contents are stored in the form of a file, which is opened and viewed in the respective software. In a computer, a file is a container to store data or information. These files are stored on the storage device of the computer, such as hard disk drive or pen drive. The student data can be stored in the document file or spreadsheet file. These files stored on the computer can be accessed quickly. To process or manipulate this data, it is required to write the program in computer programming languages. The various operations can be performed through computer programming. It includes searching, sorting, computing the percentage of marks, number of days attendance, retrieving the data.

Limitations of a File System: There are certain limitations to maintain and manipulate such type of data when there are several hundreds or thousands of students. It also becomes difficult to maintain the number of files as it increases the volume when data grows. There are certain limitations of file system to maintain such type of data. 

The limitations of file system are:

(i) Difficulty in Access: Files themselves do not provide any mechanism to retrieve data. Data maintained in a file system are accessed through application programs. While writing such programs, the developer may not anticipate all the possible ways in which data may be accessed. So, sometimes it is difficult to access data in the required format and one has to write application program to access data. 

(ii) Data Redundancy: Redundancy means same data are duplicated in different files. For example, if we are maintaining students data for the various purpose then data such as student names are maintained in different files. The common data in all such files are required to be maintained number of times. This may cause the data redundancy which is difficult to avoid in a file system. Redundancy leads to excess storage use and may cause data inconsistency also. 

(iii) Data Inconsistency: Data inconsistency occurs when same data maintained in different places do not match. If a student wants to get changed the spelling in name, it needs to be changed in the number of files where it appears. Likewise, if a student leaves school, the details need to be deleted from these files. As the files are being maintained by different people, the changes may not happen in one of the files. In that case, the student name will be different (inconsistent) in both the files. 

(iv) Data Isolation: Although these files are maintained for the students of the same class, but there is no link or mapping between these files. The school will have to write separate programs to access these files. This is because data mapping is not supported in file system. In a more complex system where data files are generated by different person at different times, files being created in isolation may be of different formats. In such case, it is difficult to write new application programs to retrieve data from different files.

(v) Data Dependence: Data are stored in a specific format or structure in a file. If the structure or format itself is changed, all the existing application programs accessing that file also need to be changed. Otherwise, the programs may not work correctly. This is data dependency. Hence, updating the structure of a data file requires modification in all the application programs accessing that file. 

(vi) Controlled Data Sharing: There can be different category of users like teacher, office staff and parents. Ideally, not every user should be able to access all the data. It means different types of users should be given different types of access, such as read only. It is very difficult to enforce this kind of access control in a file system while accessing files through application programs. 

2. Why foreign keys are allowed to have NULL values? Explain with an example.

Ans: A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation. This means that any attribute of a relation (referencing), which is used to refer contents from another (referenced) relation, becomes foreign key if it refers to the primary key of referenced relation.

In some cases, foreign key can take NULL value if it is not the part of primary key of the foreign table. The relation in which the referenced primary key is defined is called primary relation or master relation.

3. What are the limitations of file system and how that are overcome by DBMS? 

Ans: There are certain limitations of file system to maintain such type of data. The limitations of file system are:

(i) Difficulty in Access.

(ii) Data Redundancy.

(iii) Data Inconsistency.

(iv) Data Isolation.

(v) Data Dependence.

(vi) Controlled Data Sharing.

Limitations faced in file system can be overcome by storing the data in a database where data are logically related. A database management systems (DBMSs) is used as an interface to manage databases. DBMS allows to create a database, store, manage, update/modify and retrieve data from that database by users or application programs. DBMS is used to provide an effective method of performing database operations, troubleshooting database issues, and restricting data access.

4. What is database schema?

Ans: A database schema is a set of schema for a database’s relations. It consists of table with all attributes with their data types and constraints if any. It also represents the relationships among the tables. It is also used to visualize the logical architecture of database and how the data are organized in a database.

5. What is data redundancy and its associated problems? 

Ans: Data Redundancy: Redundancy means same data are duplicated in different files. For example, if we are maintaining students data for the various purpose then data such as student names are maintained in different files. The common data in all such files are required to be maintained number of times. This may cause the data redundancy which is difficult to avoid in a file system. Redundancy leads to excess storage use and may cause data inconsistency also.

6. How data redundancy problem is solved in DBMS? 

Ans: Relational DBMS (RDBMS) is used to store data in related tables. Rows and columns of a table are called tuples and attributes respectively. A table is referred to as a relation. Restrictions on data stored in a RDBMS is applied by use of keys such as Candidate Key, Primary Key, Composite Primary Key, Foreign Key. Primary key in a relation is used for unique identification of tuples. Foreign key is used to relate two tables or relations.

A tuple is a unique entity whereas attribute values can be duplicate in the table.

7. What is MYSQL and its features? 

Ans: MySQL is an open source RDBMS software which can be easily downloaded from its official website https://dev.mysql.com/downloads. After installing MySQL, start MySQL service. It is shown in Figure 2.2. The appearance of mysql> prompt as shown below. MySQL is ready to accept SQL statements on this prompt. 

Following are some important features to be kept in mind while using SQL:

(i) SQL is not case insensitive. For example, the column names ‘salary’ and ‘SALARY’ are the same for SQL.

(ii) SQL statements terminates with a semicolon (;). In multi-line SQL statements, the “;” is not required after the first line. Just press the Enter key to continue on the next line. The prompt mysql> then changes to “->”, indicating that statement is continued to the next line. Only at the end of SQL statement, put “;” and press Enter.

(iii) Only at the end of SQL statement, put “;” and press Enter.

(iv) It is possible to manage multiple databases on a single computer. USE command is used to select the specific database. After selecting the database, it is possible to create tables or querying data from this database.

(v) In LINUX OS environment, names for database and tables are case-sensitive whereas in WINDOWS OS, there is no such differentiation. However, as a good practice, it is suggested to write database or table name in the same letter cases that were used at the time of their creation.

8. What are various data types available in MYSQL? 

Ans: Commonly used data types in MySQL are numeric types, date and time types, and string types:

(i) HAR (n): Specifies character type data of length n where n could be any value from 0 to 255. CHAR is of fixed length, means, declaring CHAR (10) implies to reserve spaces for 10 characters. If data does not have 10 characters (for example, ‘city’ has four characters), MySQL fills the remaining 6 characters with spaces padded on the right.

(ii) VARCHAR (n): Specifies character type data of length ‘n’ where n could be any value from 0 to 65535. But unlike CHAR, VARCHAR is a variable-length data type. That is, declaring VARCHAR (30) means a maximum of 30 characters can be stored but the actual allocated bytes will depend on the length of entered string. So ‘city’ in VARCHAR (30) will occupy the space needed to store 4 characters only.

(iii) INT: INT specifies an integer value. Each INT value occupies 4 bytes of storage. The range of values allowed in integer type are -2147483648 to 2147483647. For values larger than that, we have to use BIGINT, which occupies 8 bytes.

(iv) FLOAT: Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.

(v) DATE: The DATE type is used for dates in ‘YYYY-MM-DD’ format. YYYY is the 4 digits year, MM is the 2 digits month and DD is the 2 digits date. The supported range is ‘1000-01-01’ to ‘9999-12-31’.

9. Differentiate between: 

(a) Database state and database schema.

Ans: The database schema along with various constraints on the data is stored by DBMS in a database catalog or dictionary, called meta-data. A meta-data is data about the data.

When we define database structure or schema, state of database is empty. After loading data, the state or snapshot of the database at any given time is the database instance. We may then retrieve data through queries or manipulate data through updation, modification or deletion. Thus, the state of database can change, and thus a database schema can have many instances at different times.

(b) Primary key and foreign key.

Ans: Primary Key: Out of one or more candidate keys, the attribute chosen by the database designer to uniquely identify the tuples in a relation is called the primary key of that relation.

Foreign Key: A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation.

(c) Degree and cardinality of a relation.

Ans: Degree: The number of attributes in a relation is called the Degree of the relation.

Cardinality: The number of tuples in a relation is called the Cardinality of the relation.

10. Explain the terms:

(a) Relation.

Ans: In relational model, tables are called relations that store data for different entities. Each relation in a relational model represents a specific type of entity.

(b) Domain.

Ans: Domain: It is a set of values from which an attribute can take a value in each row. Usually, a data type is used to specify domain for an attribute.

(c) Tuple.

Ans: Each row of data in a relation (table) is called a tuple. In a table with n columns, a tuple is a relationship between the n related values.

(d) Attribute.

Ans: Attribute: Characteristic or parameters for which data are to be stored in a relation. Simply stated, the columns of a relation are the attributes which are also referred to as fields.

(e) Degree.

Ans: The number of attributes in a relation is called the Degree of the relation.

(f) Cardinality.

Ans: The number of tuples in a relation is called the Cardinality of the relation.

(g) Primary Key.

Ans: Out of one or more candidate keys, the attribute chosen by the database designer to uniquely identify the tuples in a relation is called the primary key of that relation.

(h) Foreign Key.

Ans: A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation.

11. Describe the various integrity constraints? 

Ans: Constraints are the certain types of restrictions on the data values that an attribute can have. They are used to ensure correctness of data. However, it is not mandatory to define constraints for each attribute of a table. 

Commonly used SQL Constraints are:

(i) NOT NULL: Ensures that a column cannot have NULL values where NULL means missing/unknown/not applicable value.

(ii) UNIQUE: Ensures that all the values in a column are distinct/unique.

(iii) DEFAULT: A default value specified for the column if no value is provided.

(iv) PRIMARY KEY: The column which can uniquely identify each row or record in a table.

(v) FOREIGN KEY: The column which refers to value of an attribute defined as primary key in another table.

SESSION 2: STRUCTURED QUERY LANGUAGE (SQL)

Check Your Progress

SESSION 3: FUNCTIONS IN SQL

Check Your Progress