CBSE Class 12 Informatics Practices Database Concepts Assignment

DATABASE CONCEPTS

Q.1. What is RDBMS?
Ans: RDBMS stands for relational data base management system. It is a software package that managed a database.

Q.2 What is the function of a database management system?
Ans. It provides the users that much information that is required by them.

Q.3. What is data redundancy?
Ans. It is duplication of data in a database. It leads to the problems like wastage of space and data inconsistency.

Q.4. What are different levels of data abstraction?
Ans. External level, conceptual level, physical level.

Q.5. What do you mean by relational database?
Ans. In relational database, the data is organized in form of tables(rows and columns).

Q.6. What is relational algebra?

Ans. Relational algebra is used to perform specific operation on existing relation to provide desired result. Relationl algebra is procedural language.

Q.7. What does union operator do?

Ans. It produces third relation that contains tuples from both the operand relation.

Q.8. What is normalization?

Ans. It is concerned with the transformation of the conceptual schema (logical data structures) into a computer represent able form.

Q.9. What is meant by functional dependency?

Ans. It is a relationalship that exist between any two fields of tables.

Q.10. What do you understand by domain?

Ans: A domain is a pool of values from which the actual vales appearing in a given column are drawn.

(Two Marks Questions)

Q.11. What is data model?
Ans: A data model is a collection of conceptual tools for describing data, relationship, data semantic etc. There are generally three data models available: relational, network and hierarchical model.
(a) Relational Model: The relational model represent data and relationships among data by a collection of tables known as relations, each of which has a number of columns with a unique names.
(b) Network Model: The Network model represent data by collections of records and relational ship among the data are represented by links which can be viewed as pointers. The record in the database are organized as collection of arbitrary graph.
(c) Hierarchical Model : This model is similar to network model in the sense that data are relational ships among the data are represented by records and links respectively. In this model records are represented by tree.

Q.12 What is relation?
Ans: A relation is a table i.e. data is arranged in rows and columns. A relational has the following properties.
(a) In any column of table, all items are of same kind where as items in different columns may not be of same kind.
(b) For a row, each columns must have an atomic values. .
(c) All rows of a relation are distinct.

Q.13. Define the terms : Primary Key, Alternate Key, Candidate Key
Ans: (a) Primary Key:- A Primary Key is a set of one or more attribute that can uniquely identify tuples with in the relation.
(b) Alternate Key:- A Candidate key which is not the primary key is known as alternate key.
(c) Candidate Key:- All attribute combinations inside a relation that can serve as primary key are candidate keys as they are candidate

Q.14. What is data independence? How logical data independence is different from physical data independence?
Ans. The ability to modify a scheme definition in one level without affecting a scheme definition in the next higher level is called data independence.
Logical data independence is different from physical as in logical the conceptual scheme can be modified without causing any changes in the scheme followed at view levels where as in physical level could be modified without affecting the scheme followed at conceptual level.

Q15 What are views? How they are useful?
Ans. A view is a virtual table that does not really exist in its own right but is instead derived form one or more underlying base tables. The view is a kind of table whose contents are taken upon other tables depending upon given query condition.
The usefulness of a views lies in the fact that they provide an excellent way to give people access to some but not all of the information in a table.

Q.16. Define First, second and third normal forms?
Ans: A relation R is in first normal form( 1NF) if and only if all underlying domains of the relation contains atomic vales only.
A relation is said to be in second normal form (2NF) if and only if it is in 1NF and every non key attribute is fully dependent of the primary key.
A relation R is said to be in third normal form (3NF) if and only if it is in 2NF and every non key attribute non transitively dependent upon the primary key.

Q.17. How many type of user works in database ?
Ans: Three type of user can work with data base.
1. End User -> This user is not a computer trained person but it uses the data base to retrieve information.
2. Application System Analyst.:-This user is concerned about all of the data base at logical level i.e. what all data constitute the database.
3. Physical storage system analyst-> This user is concerned with the physical implementation details of the database such as which storage device? Which storage technique should be used.

Q.18. What is foreign Key? What is its purpose?
Ans: A non key attribute, whose value are derived from the primary key of some other table, is known as foreign key in the current table. The table in which this non-key attribute i.e. foreign key attribute exists, is called a foreign table.

Q.19. Define the terms Tuple and Attribute
Ans: Tuples: The rows of tables (relations) are generally referred to as tuples.
Attribute: The columns of tables are generally referred to as attribute.

Q.20. What do you understand by the terms Cardinality and Degree of the table?
Ans: Degree: The number of attributes in a relation determines the degree of a relation. A relation having 3 attributes is said to be a relation of degree 3.
Cardinality: The number of rows in a relation is known as Cardinality.

STRUCTURED QUERY LANGUAGE

(Two Marks Questions)

Q.1. What are DDL and DML?
Ans:- The DDL provides statements for the creation and deletion of tables and indexes.
The DML provides statements to enter, update , delete data and perform complex queries on these tables.

Q.2. What is the difference between Where and Having Clause ?
Ans: The having clause places the condition on group but where clause places the condition on individual rows

Q.3. What are the different Data types available in SQL.
Ans. Data Type              Description
VARCHAR2(w)               Variable length character having Max width is 2000 character
CHAR(w)                        Fixed length character Min Length 1 Ma Length 255
NUMBER(w.p)                Number with precision w and scale p.
DATE                              Date value from Jan. 1, 4712 BC to DEC. 31,4712 AD.

Q.4. What do you understand by constraints?
Ans: Constraints are used to enforce rules at table level when ever row is inserted. , updated or deleted from table.
Constraints can be defined to one of the Two level.
Column Level. Reference to a single column. can be defined any type of integrity.
Table Level. References one or more columns and is defined separately from definition of the columns in the table.

Q.5. Write some features of SQL?
Ans: Recovery ad Concurrency:- Concurrency is concerned with the manner in which multiple user operate upon the Database.
Security: The Security can be maintained by view mechanism.
Integrity Constraints-> Integrity constraints are enforced by the system.

Q.6. Write the rules to name an objects?
Ans : • The maximum length must be 30 characters long.
• The Object name should not contain quotation mark.
• The name must start with letter.
• The use of $ and # is discouraged in the object name
• A name must not be a reserved name.

Q.7. What are group Functions
Ans: The aggregate functions are group functions. They return result based on groups of rows. The group functions are AVG(), COUNT(), MAX(), MI N(), SUM()

Q.8 What are column alias?
Ans: In many cases heading table may not be descriptive and hence it difficult to understand. In such case we use columns alias It will change column heading with column alias.

(Six Marks Questions)

Q.9. Table : SchoolBus

(a) To show all information of students where capacity is more than the no of student in order of rtno.
(b) To show area_covered for buses covering more than 20 km., but charges less then 80000.
(c) To show transporter wise total no. of students traveling.
(d) To show rtno, area_covered and average cost per student for all routes where average cost per student
is - charges/noofstudents.
(e) Add a new record with following data:
(11, “ Moti bagh”,35,32,10,” kisan tours “, 35000)
(f) Give the output considering the original relation as given:
(i) select sum(distance) from schoolbus where transporter= “ Yadav Co.”;
(ii) select min(noofstudents) from schoolbus;
(iii) select avg(charges) from schoolbus where transporter= “ Anand travels”;
(iv) select distinct transporter from schoolbus;
Ans: 9
(a) select * from schoolbus where capacity>noofstudents order by rtno;
(b) select area_covered from schoolbus where distance>20 and charges < 80000;
(c) select transporter, sum(noofstudents) from schoolbus group by transporter;
(d) select rtno, area_covered, charges/noofstudents avgcost from schoolbus;
(e) insert into schoolbus values(11,'Moti Bagh', 35, 32, 10, 'Kisan Tours', 35000);
(f) Output of given following commands will be :
(i) select sum(distance) from schoolbus where transporter='Yadav Co.';
Ans : SUM(DISTANCE)
                 50
(ii) select min(noofstudents) from schoolbus;
MIN(NOOFSTUDENTS)
                 32
(iii) select avg(charges) from schoolbus where transporter='Anand travels';
AVG(CHARGES)
81666.6667
(iv) select distinct transporter from schoolbus;
TRANSPORTER
Anand travels
Bhalla Co.
Kisan Tours
Speed travels
Yadav Co.
shivamtravels
6 rows selected.

Q.10. Write SQL command for (i) to (vii) on the basis of the table SPORTS
Table: SPORTS

(a) Display the names of the students who have grade ‘C’ in either Game1 or Game2 or both.
(b) Display the number of students getting grade ‘A’ in Cricket.
(c.) Display the names of the students who have same game for both Game1 and Game2.
(d) Display the games taken up by the students, whose name starts with ‘A’.
(e) Assign a value 200 for Marks for all those who are getting grade ‘B’ or grade ‘A’ in both Game1 and Game2.
(f) Add a new column named ‘Marks’.
Ans: 10
(a) select name from sports where grade='C' or grade2='C';
(b) select count(name) from sports where (grade='A' and Game1='Cricket') or (grade2='A' and Game2 = 'Cricket');
(c) select name from sports where game1=game2;
(d) select game1, game2 from sports where name like 'A%';
(e) update sports set marks = 200 where grade in ('A','B') and grade2 in ('A','B');
(f) Alter table sports add marks int;