Database Concepts Class 11 with an easy explanation – in this article I am going to provide you the NCERT Solutions for chapter 7 of NCERT Informatics Practices Textbook Class 11. So here we go!
Database Concepts Class 11 with an easy explanation
So let’s start the article Database Concepts Class 11 with an easy explanation.
[1] Give the terms for each of the following:
a) Collection of logically related records
b) DBMS creates a file that contains description about the data stored in the database
c) Attribute that can uniquely identify the tuples in a relation
d) Special value that is stored when actual data value is unknown for an attribute.
e) An attribute which can uniquely identify tuples of the table but is not defined as primary key of the table
f) Software that is used to create, manipulate and maintain a relational database.
The question 2 for Database Concepts Class 11 with an easy explanation is based on foreign key. Have a look on it!
[2] Why foreign keys are allowed to have NULL values? Explain with an example.
The next questions of Database Concepts Class 11 are based on the differentiations:
[3] Differentiate between:
a) Database State and Database Schema
| Database State | Database Schema |
| Database state provides the status of a database. | Database schema provides the description and metadata of the database. |
| It is like an extension of a database schema. | It is like a blueprint of a database. |
| When a database is created, the state will be empty. | When the database is created the schema enables the related attributes for relationship and list of field values. |
| Insert, update or delete affects the state of the database. | Structure of data, relationship attributes, and organization of the database affect the database schema. |
b) Primary key and foreign key
| Primary Key | Foreign Key |
| It identifies rows uniquely from the table. | It is referenced value of a primary key from the referenced table. |
| It cannot have a null value. | It can have a null value. |
| It cannot have a duplicate value. | The value in the foreign key can be repeated. |
| A primary key can be assigned on any table. | Foreign key only assigned to that table which can be referenced to a table having a primary key. |
c) Degree and cardinality of a relation
| Degree | Cardinality |
| The degree means the number of attributes of columns of a relation. | Cardinality means the number of tuples or rows of a relation. |
| For example, If a table is having 4 rows and 5 columns, then the degree of the table is 5. | For example, If a table is having 4 rows and 5 columns, then the cardinality of the table is 4. |
The next questions for Database Concepts Class 11 are about advantaged and disadvantages of DBMS.
4) Compared to a file system, how does a database management system avoid redundancy in data through a database?
5) What are the limitations of file system that can be overcome by a relational DBMS?
The next question of Database Concepts Class 11 is about the relations and its structure.
6) A school has a rule that each student must participate in a sports activity. So each one should give only one preference for sports activity. Suppose there are five students in a class, each having a unique roll number. The class representative has prepared a list of sports preferences as shown below. Answer the following:
Table: Sports Preferences
| Roll_no | Preference |
| 9 | Cricket |
| 13 | Football |
| 17 | Badminton |
| 17 | Football |
| 21 | Hockey |
| 24 | NULL |
| NULL | Kabaddi |
a) Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student’s preference field? – Yes, as preference is not unique so the NULL value can be assigned to it.
b) Roll no 17 has given two preferences sports. Which property of relational DBMC is violated here? Can we use any constraint or key in the relational DBMS to check against such violation, if any? – According to primary key constraints the roll no cannot be repeated.
c) Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation? – If roll no is assigned as primary key, then this row is no longer needed, otherwise, you have to insert roll no value to it.
7) In another class having 2 sections, the two respective class representatives have prepared 2 separate Sports Preferences
tables, as shown below: Sports preference of section 1 (arranged on roll number column)
Table: Sports Preferences
| Roll_no | Sports |
| 9 | Cricket |
| 13 | Football |
| 17 | Badminton |
| 21 | Hockey |
| 24 | Cricket |
Sports preference of section 2 (arranged on Sports name column, and column order is also different)
Table: Sports Preferences
| Sports | Roll_no |
| Badminton | 17 |
| Cricket | 9 |
| Cricket | 24 |
| Football | 13 |
| Hockey | 21 |
Are the states of both the relations equivalent? Justify.
8) The school canteen wants to maintain records of items available in the school canteen and generate bills when students purchase any item from the canteen. The school wants to create a canteen database to keep track of items in the canteen and the items purchased by students. Design a database by answering the following questions:
a) To store each item name along with its price, what relation should be used? Decide appropriate attribute names along with their data type. Each item and its price should be stored only once. What restriction should be used while defining the relation?
Ans.: To store the items along with its name and price, item relation can be used. User can assign a primary key for an item while defining the relation. The item table should be as per below given structure:
Relation: Items
| Attribute | Datatype | Constraint |
| item_id | int(3) | Primary key |
| item_name | varchar(20) | Not Null |
| price | decimal(6,2) |
b) In order to generate a bill, we should know the quantity of an item purchased. Should this information be in a new relation or a part of the previous relation? If a new relation is required, decide the appropriate name and data type for attributes. Also, identify appropriate primary key and foreign key so that the following two restrictions are satisfied:
Yes, a new relation can be created to store orders records. This table stores details about orders only. The structure can be something like this:
| Attribute | Datatype and Size | Constraints |
| order_id | int(3) | Primary Key |
| Qty | int(3) | |
| item_id | int(3) | Foreign Key (Item table) |
For the item purchased you need a new relation named orders.
i) The same bill cannot be generated for different orders.
The bill can be generated using orders table and the order_id is a primary key.
ii) Bill can be generated only for available items in the canteen.
Here the items quantity should be checked, if found null or 0 should raise an error.
c) The school wants to find out how many calories students intake when they order an item. In which relation should the attribute ‘calories’ be stored?
The calories can be added into the orders table.
9) An organisation wants to create a database EMPDEPENDENT to maintain the following details about its employees and their dependent.
EMPLOYEE(AadharNumber, Name, Address, Department,EmployeeID)
DEPENDENT(EmployeeID, DependentName,Relationship)a) Name the attributes of EMPLOYEE, which can be used as candidate keys.
In the EMPLOYEE table Adhaar Number, Department can be used as candidate keys.
b) The company wants to retrieve details of dependent of a particular employee. Name the tables and the key which are required to retrieve this detail.
From dependent table Employeeid, dependentname and from employee table adhaarnumber, name can be required for particular of employee.
c) What is the degree of EMPLOYEE and DEPENDENT relation?
The degree of EMPLOYEE table is 5 and DEPENDENT table is 3.
10) School uniform is available at M/s Sheetal Private Limited. They have maintained SCHOOL_UNIFORM Database with two relations viz. UNIFORM and COST. The following figure shows database schema and its state.
School Uniform Database
Table Data > Uniform
| Ucode | Uname | Ucolor |
| 1 | Shirt | White |
| 2 | Pant | Grey |
| 3 | Skirt | Grey |
| 4 | Tie | Blue |
| 5 | Socks | Blue |
| 6 | Belt | Blue |
Table Data > Cost
| Ucode | Size | Price |
| 1 | M | 500 |
| 1 | L | 580 |
| 1 | XL | 620 |
| 2 | M | 810 |
| 2 | L | 890 |
| 2 | XL | 940 |
| 3 | L | 770 |
| 3 | M | 830 |
| 3 | XL | 910 |
| 4 | S | 150 |
| 4 | L | 170 |
| 5 | S | 180 |
| 5 | L | 210 |
| 6 | M | 110 |
| 6 | L | 140 |
| 6 | XL | 160 |
a) Can they insert the following tuples to the UNIFORM Relation? Give reasons in support of your answer.
i) 7, Handkerchief, NULL > Yes
ii) 4, Ribbon, Red > No, Because Ucode 4 is already assigned to Tie in the UNIFORM
iii) 8, NULL, White > No, Because Uname is following the Not Null constraint.
b) Can they insert the following tuples to the COST Relation? Give reasons in support of your answer.
i) 7, S, 0 > No, as it is violating check constraints
ii) 9, XL, 100 > Yes
11) In a multiplex, movies are screened in different auditoriums. One movie can be shown in more than one auditorium. In order to maintain the record of movies, the multiplex maintains a relational database consisting of two relations viz. MOVIE and AUDI respectively as shown below:
Movie(Movie_ID, MovieName, ReleaseDate)
Audi(AudiNo, Movie_ID, Seats, ScreenType,TicketPrice)a) Is it correct to assign Movie_ID as the primary key in the MOVIE relation? If no, then suggest an appropriate primary key.
Yes we can assign Movie_ID as primary key as every movie can be inserted with unique values.
b) Is it correct to assign AudiNo as the primary key in the AUDI relation? If no, then suggest an appropriate primary key.
Yes, we can assign AudiNo as the primary key as every audio can be inserted with unique values.
c) Is there any foreign key in any of these relations?
Yes, Movie_ID can be a foreign key in AUDI table which can be referenced to the Movie_ID column of MOVIE table,
12) For the given database STUDENT-PROJECT, answer the following:
a) Name primary key of each table.
| Table | Primary Key |
| Student | RollNo |
| Project | ProjectNo |
| ProjectAssigned | RegsitrationID |
b) Find foreign key(s) in table PROJECT-ASSIGNED.
The ProjectNo is the foreign key for PROJECT-ASSIGNED table.
c) Is there any alternate key in table STUDENT? Give justification for your answer.
Yes, there are 3 alternate keys available in STUDENT table. They are – Name, Class and Section. As they are not a primary key or candidate key or foreign key.
d) Can a user assign duplicate value to the field RollNo of STUDENT table? Jusify.
No, as we have already defined as a primary key. So user cannot assign duplicate value to the field rollno.
13) For the above given database STUDENT-PROJECT, can we perform the following operations?
a) Insert a student record with missing roll number value.
No, as rollno is primary so it doesn’t allow to insert a null value.
b) Insert a student record with missing registration number value.
Yes, as it is a foreign key in student table, so you can insert it.
c) Insert a project detail without submission-date.
Yes, it can be inserted if not null constraint is not given.
d) Insert a record with registration ID IP-101-19 and ProjectNo 206 in table PROJECT-ASSIGNED.
Yes, it can be inserted.
That’s all from this chapter Database Concepts Class 11. I hope you enjoyed this article. Feel free to ask you questions in the comment section.
Thank you for visiting our blog. Before leaving this article, follow the below given links to read other articles for class 11.
Class 11 Study Material