Chapter 7: Database Concepts

NCERT solutions Database Concepts

1. Give the terms for each of the following:
a) Collection of logically related records

Database

b) DBMS creates a file that contains description about the data stored in the database

Metadata

c) Attribute that can uniquely identify the tuples in a relation

Primary Key

d) Special value that is stored when actual data value is unknown for an attribute. Null

e) An attribute which can uniquely identify tuples of the table but is not defined as primary key of the table

Candidate key

f) Software that is used to create, manipulate and maintain a relational database. RDBMS

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

Foreign keys are referenced values from the table having similar attribute as a primary key from referenced relation. So sometimes in the relation, some values are unknown or not matching with referenced relation in the database. Thus, this foreign key can have a null value. For example, I am working on a database of the student. I have prepared two tables StudentMaster and ResultMaster. Now I have inserted records into StduentMaster with primary key GRNo which is unique for each student. In ResultMaster table I have the GRNO as foreign key but for some records, I don’t have assigned a GRNO to the students. So these records can have a null value.

3. Differentiate between: a) Database State and Database Schema

Database State Database state provides the status of a database. It is like an extension of a database schema. When a database is created, the state will be empty Insert, update or delete affects the state of the database. Database Schema Database schema provides the description and metadata of the database. It is like a blueprint of a database. When the database is created the schema enables the related attributes for relationship and list of field values. Structure of data, relationship attributes, and organization of the database affect the database schema.

b) Primary key and foreign key

Primary Key It identifies rows uniquely from the table. It cannot have a null value. It cannot have a duplicate value. A primary key can be assigned on any table. Database Schema It is referenced value of a primary key from the referenced table. It can have a null value. The value in the foreign key can be repeated. 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 The degree means the number of attributes of columns of a relation. For example, If a table is having 4 rows and 5 columns, then the degree of the table is 5. Cardinality 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 cardinality of the table is 4.

NCERT solutions Database Concepts 4. Compared to a file system, how does a database management system avoid redundancy in data through a database?

The file system stores the data in terms of bits. There are no features identifying data uniquely in the file system. So it is very difficult to find duplicate values as the user has to do a manual search for this. Whereas Database provides a feature called the primary key, that allows entering only unique records into the table. Hence the redundancy in data is avoided.

5. What are the limitations of file system that can be overcome by a relational DBMS? There certain limitations of the file system than can be overcome by a relational DBMS are as following: 1. Difficulty in access: When you are using a file system you need an application to access those files. These applications have their own limitations or they made in such manner that they allow few kinds of features. These files have a different pattern, structure, and format. The database can read all kinds of data in a specific format. 2. Data Redundancy: In a file system, you have don’t have an option to avoid duplicity of data. If you need to use data in different files, you have to insert them in those files also. It adds extra load on the system and memory. In the database, you have a relational model that allows relationship that maps data between different tables. 3. Data Inconsistency: It is a process when users are working on a shared platform. When a similar file is loaded into a different computer system and performed any DML operation (Insert, Update, Delete) then data inconsistency will occur. Means in one file it gets changed but not in all shared files simultaneously. 4. Data Isolation: The file system does not support any relationship or mapping. Hence it very difficult to retrieve isolated data from different files. 5. Data Dependence: If any modification is done in the format or structure of the file, all the related applications must be changed. Hence it requires the upgraded version to access the data files. 6. Control data sharing: Whether it is a large or small organization, data must be shared between different users. Some of the files accessed by specific people and some other files with some other people. So this type of sharing control is very difficult on file system. 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.

NCERT solutions Database Concepts 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. The states of both tables are different as the columns order is changed. If the constraints are applied to both tables then both have the different states, otherwise both have same states if primary key is given to the column roll_no.

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?

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              Size Constraints order_id         int(3)                   Primary Key Qty                 int(3) item_id           int(3)                   Foreign Key (Item table)

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 caloriesstudents 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 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. NCERT solutions Database Concepts

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. a) Can they insert the following tuples to the UNIFORM Relation? Give reasons in support of your answer. i) 7, Handkerchief, NULL ii) 4, Ribbon, Red iii) 8, NULL, White

i) Yes ii)No, Because Ucode 4 is already assigned to Tie in the UNIFORM iii)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 ii) 9, XL, 100

i)  No, as it is violating check constraints ii) 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)  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 above 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.

Cloud computing stores data into a server and it is available at almost free of cost or nominal cost. When it comes to time-saving by saving data on the internet there is no need to wait to start up any computer or device. It is easily available with a browser program or app of that. So it can be accessed any time from anywhere. 7. What is the on-demand service? How it is provided in the cloud computing? On-demand service means that data is available as and when required. In cloud computing, users can access any file by using a login with a username or email account and password. This is called on-demand service. 8. Write examples of the following: a) Government provided cloud computing platform  The government of India has provided a government cloud service named Megh Raj. b) Large scale private cloud service providers and the services they provide  As we have discussed earlier google is large-scale private cloud service provider which provide storage, web services, and many more things. 9. A company interested in cloud computing is looking for a provider who offers a set of basic services such as virtual server provisioning and on-demand storage that can be combined into a platform for deploying and running customized applications. What type of cloud computing model fit these requirements? a) Platform as a Service b) Software as a Service c) Infrastructure as a Service The correct answer is option c) Infrastructure as a Service NCERT solutions Emerging Trends 10. Which is not one of the features of IoT devices? a) Remotely controllable b) Programmable c) Can turn themselves off if necessary d) All of the above The correct answer is option a) Remotely controllable 11. If Government plans to make a smart school by applying IoT concepts, how can each of the following be implemented in order to transform a school into IoT enabled smart school? a) e-textbooks b) Smart boards c) Online tests d) Wifi sensors on classrooms doors e) Sensors in buses to monitor their location f) Wearables (watches or smart belts) for attendance monitoring a) e-textbooks : e-textbooks can be made available e-library by using web services. b) Smart Boards: Smart boards can be operated by digital stylus or pen and should be connected with network. c) Online Tests: A web portal or online examination should be prepared to conduct exams smoothly. d) Wifi sensors and classrooms doors: Wifi sensors and classroom doors can be used to lock and unlock them. These sensors also provides security to students and other stakeholders. e) Sensors in buses to monitor their location: These sensors enables quick reporting and monitoring to the coordinates and parents to know the status of child and bus after leaving the school. f) Wearables (watches or smart belts) for attendance monitoring: These devices can be used or implemented for students in such a way that identifies a student and marks the attendance accordingly. 12. Five friends plan to try a startup. However, they have a limited budget and limited computer infrastructure. How can they avail the benefits of cloud services to launch their startup? They can go for registration with any suitable service provider either government or private. They can register for Infrastructure as a service to avail the facility of cloud storage and deploy applications initially. 13. Governments provide various scholarships to students of different classes. Prepare a report on how blockchain technology can be used to promote accountability, transparency, and efficiency in the distribution of scholarships? As you are aware in blockchain technology, a separate ledger is created for individuals and stored so the transparency and efficiency automatically improve. The students can avail the scholarship in their ledger only. The transactions are also more authentic. 14. How IoT and WoT are related? IoT and WoT both can be users on a network to communicate with multiple devices. They can provide a communication medium between various devices, this medium can be an app, a web service or any network-enabled device. 15. Match the following: Column A                                                                              1. You got a reminder to take medication  2. You got a SMS alert that you forgot to lock the door  3. You got the SMS alert that parking space is available near your block 4. You turned off your LED TV from your wristwatch  Column B i. Smart Parking ii. Smart Wearable  iii. Home Automation iv. Smart Health Answer 1 – iv 2 – iii 3 – i 4 – ii NCERT Class 11 Solutions can be of great value if you are trying to excel in your school examinations. The journey starts directly from when you step into class 1 as every class holds great to use as you progress. CBSE Class 11 is no doubt a very valuable class and the last class of your school life. Not to mention, it also leads to a lot of career-making decisions as you seem for some important competitive exams to get your desire college. NCERT solution can help you immensely throughout this adventure of yours. NCERT Class 11 Solutions Although higher classes students like Class 10-12 should have a better knowledge of all the topics that will help them to examine deeper into the basics as well as an advanced level of questions that are asked in many competitive exams after 12 class. One of the best ways to achieve it is by answering questions of NCERT Books with the help of NCERT solutions. We at SelfStudys strive hard to devise better ideas in order to help students and give you detailed solutions for all questions from the NCERT textbook as per the latest CBSE CCE marking scheme pattern is to back you up in useful learning in all the exams conducted by CBSE curriculum. The plethora of advantages of referring to NCERT solutions requires an in-depth understanding of detailed solutions of all NCERT book’s questions. We have given all the detailed NCERT questions and solutions in PDF format which you can read for FREE. All you have to do is download the SelfStudys NCERT Solutions for Class 11 once for FREE and you are able to go. You can get all the Subject NCERT solutions by the links provided here. NCERT Solutions for all the subjects of Class 11 is available on this page including NCERT Class 11 Solution for Maths, Chemistry, Physics, English, Business Studies, Biology, economics are provided below to download in a free PDF file. NCERT Solution for Class 11 NCERT Class 11 Solutions to the questions with Chapter-wise, detailed are given with the objective of helping students compare their answers with the example. NCERT books provide a strong foundation for every chapter. They ensure a smooth and easy knowledge of advanced concepts. As suggested by the HRD ministry, they will perform a major role in JEE. NCERT Class 11 Solutions | NCERT Solution for Class 11 NCERT Solution for Class 11 includes all the questions given in NCERT Books for all Subjects. Here all questions are solved with detailed information and available for free to check. NCERT Solutions for Class 11 are given here for all chapter-wise. Select the subject and choose a chapter to view NCERT Solution chapter-wise. We hope that our NCERT Class 11 Solutions helped with your studies! If you liked our NCERT Solutions for Class 11, please share this post. Why NCERT Solutions for Class 11 is Important? NCERT Class 11 Solutions increases the base for conceptual knowledge. It provides a complete view of the prescribed syllabus, as the textbook doesn't have a detailed description of the syllabus nor solutions. All the important theorems and formulas of Class 11 are completely solved, which works to make the concepts and find new links to them. Importantly, the NCERT Class 11 Solutions is the point of reference for those students planning for competitive examinations such as NRRT, JEE Main/Advanced, BITSAT & VITEEE, etc. When the NCERT book is accompanied by the solutions, the knowledge of concepts becomes simple and in-depth. Moreover, students can trail into the solutions without break in topics, as it is designed to give a step-by-step explanation. Try out the Class 11 Solutions and learn from the resources. Why try NCERT Class 11 Solutions? Well, here are the solid reasons why you should and must try out the NCERT Class 11 Solutions. Has ERRORLESS answers with 100% Reasoning The quality of solutions is perfect Gives in-depth knowledge with notes shortly after the solutions with shortcuts, tips, alternative methods & points to remember Gives quick revision of the concepts included along with important definitions and Formulas on the chapters, which acts as a prepared refresher. Designed step-by-step to give 100% Concept Clearness