This page contains the CBSE Computer Science with Python class 12 Unit-3 chapter 1 Database Concepts and SQL. You can find the solutions for chapter 1 of CBSE class 12 Computer Science with Python Exercise. So is the case if you are looking for CBSE class 12 Computer Science with Python related topic Database Concepts and SQL questions and answers for the Exercise.
Exercise
Question 1
1. Expand the following:
(i)
SQL
(ii)
DBMS
Answer 1
(i)
SQL: Structured Query Language
(ii)
DBMS: Database Management System
Question 2
2. What is relational database model?
Answer 2
Relational database model is a model in which data is represented as a collection of related tables (relations) made of rows and columns.
Each row represents a record (tuple) and each column represents an attribute (field), and relations can be linked through keys.
Question 3
3. What is relation?
Answer 3
In a database, a relation means a table.
A relation stores data in tabular form with rows and columns, and each relation has a unique name in the database.
Question 4
4. Define the following:
a)
Cardinality
b)
Degree
c)
Tuple
d)
Field
Answer 4
a)
Cardinality: number of rows (tuples) present in a relation/table.
b)
Degree: number of attributes (columns) in a relation/table.
c)
Tuple: one complete row/record containing values for all columns.
d)
Field: one attribute/column that stores one type of data in the table.
Question 5
5. Define the following keys:
a)
Primary key
b)
Candidate key
c)
Alternate key
Answer 5
a)
Primary key: a column or set of columns selected to uniquely identify each record in a table.
b)
Candidate key: all possible keys that can uniquely identify records and are eligible to be primary key.
c)
Alternate key: the candidate key(s) that are not chosen as primary key.
Question 6
6. What is DDL?
Answer 6
DDL (Data Definition Language) is used to define and modify database structure.
It works on schema-level objects such as tables and columns, for example:
CREATE, ALTER, DROP.Question 7
7. What all character types are possible in SQL?
Answer 7
Character types in SQL are:
CHAR, VARCHAR, VARCHAR2.CHAR is fixed-length, while VARCHAR/VARCHAR2 are variable-length character types.Question 8
8. What all numeric data types are possible in SQL?
Answer 8
Numeric types are:
NUMBER, NUMERIC, DECIMAL, INT/INTEGER, FLOAT.These are used for integers as well as real/fractional values depending on type and precision/scale.
Question 9
9. Write all character data types in SQL.
Answer 9
Character data types in SQL are:
CHAR, VARCHAR, VARCHAR2.They are used to store text/string values.
Question 10
10. Write all number data types in SQL.
Answer 10
Number data types in SQL are:
NUMBER, NUMERIC, DECIMAL, INT/INTEGER, FLOAT.These support exact and approximate numeric storage based on requirement.
Question 11
11. Define the following:
a)
Projection
b)
Selection
c)
Union
d)
Cartesian product
Answer 11
a)
Projection: operation that selects specific columns (attributes) from a relation. It returns a table with fewer columns.
b)
Selection: operation that selects rows (tuples) satisfying a condition. It returns a table with fewer rows.
c)
Union: operation that combines rows of two compatible relations (same number/type/order of columns). Duplicate rows are removed in the result.
d)
Cartesian product: operation that pairs each row of first relation with each row of second relation. If tables have m and n rows, result has m × n rows.
Question 12
12. Differentiate between
char and varchar.Answer 12
Basis
CHAR
VARCHAR
Length
Fixed length
Variable length
Storage
Padded with spaces up to fixed size
Stores only actual characters
Use
When data size is constant
When data size varies
Question 13
13. Write the similarity between decimal and numeric data types.
Answer 13
Basis
DECIMAL
NUMERIC
Precision/Scale
Fixed precision and scale
Fixed precision and scale
Format
DECIMAL(p, s)NUMERIC(p, s)Use
Exact numeric values
Exact numeric values
Question 14
14. What is the importance of primary key in a table? Explain with suitable example.
Answer 14
Primary key is important because it gives each record a unique identity in the table.
•
It prevents duplicate values in the key column.
•
It does not allow
NULL values.•
It helps in accurate searching, updating, and establishing relationships with other tables.
Example: In
Student(AdNo, Name, Class), AdNo can be primary key because every student has a unique admission number, so no two rows can have same AdNo.Question 15
15. Differentiate between primary key and candidate key.
Answer 15
Basis
Primary Key
Candidate Key
Definition
Chosen key that uniquely identifies rows
Any key that can uniquely identify rows
Count in table
Only one primary key
Can be more than one candidate key
Role
Main key used for identification/references
Possible options for primary key
NULL values
Not allowed
Should uniquely identify records; selected one becomes strict primary key
Example: If both
AdNo and Email are unique in a student table, both are candidate keys. If AdNo is selected, it becomes the primary key.Question 16
16. Differentiate between candidate key and alternate key.
Answer 16
Basis
Candidate Key
Alternate Key
Meaning
Any key eligible to become primary key
Candidate key not selected as primary key
Selection stage
Before choosing primary key
After choosing primary key
Count
One or more
Zero or more
Role in table design
Used to choose best primary key
Works as backup unique key
Example: If a table has unique
RollNo and unique AadhaarNo, both are candidate keys. If RollNo is selected as primary key, AadhaarNo becomes an alternate key.Question 17
17. What all are domain name possible in gender?
Answer 17
Possible domain values for gender are typically: Male and Female.
Question 18
18. Write any four advantages of SQL.
Answer 18
Any four advantages of SQL are:
1.
SQL is portable: it works on servers, PCs, laptops and other platforms.
2.
High speed: SQL queries retrieve large amounts of records quickly and efficiently.
3.
Easy to learn and understand: SQL uses English-like statements and needs less coding.
4.
Works with any DBMS vendor: SQL is supported by almost all major relational database systems.
Question 19
19. In which situation one can apply union operation of two tables.
Answer 19
Union can be applied when both tables are compatible:
•
same number of columns,
•
corresponding columns have compatible data types,
•
columns are in the same order (or selected in same order).
In this situation, rows from both tables can be combined and duplicate rows are removed.
Question 20
20. Differentiate between union and Cartesian product.
Answer 20
Basis
Union
Cartesian Product
Condition
Requires compatible tables (same number/order of columns and compatible types)
No such compatibility requirement
Operation type
Appends rows of one table with another table
Pairs every row of first table with every row of second table
Result rows
Combined rows; duplicate rows removed
Rows = r1 x r2
Result columns
Same as one input table
c1 + c2
Question 21
21. A table
customer has 10 columns but no row. Later, 10 new rows are inserted and 3 rows are deleted in the table. What is the degree and cardinality of the table customer?Answer 21
Degree means number of columns, so it remains 10.
Cardinality means number of rows, so calculate row changes step-by-step:
•
Initially rows = 0
•
After inserting 10 rows = 10
•
After deleting 3 rows = 10 – 3 = 7
Final Answer: Degree = 10, Cardinality = 7.
Question 22
22. A table
game1 has 3 columns and 20 rows and another table game2 has the same columns as game1 (i.e., 3) and 15 rows. 5 rows are common in both tables. If we take union, what is the degree and cardinality of the resultant table?Answer 22
For
UNION:•
Degree stays same as input table structure, so degree = 3.
•
Cardinality = total rows from both tables minus common rows.
•
So, cardinality = 20 + 15 – 5 = 30.
Final Answer: Degree = 3, Cardinality = 30.
Question 23
23. A table
student1 has 4 columns and 10 rows and student2 has 5 columns and 7 rows. If we take Cartesian product of these two tables, what is the degree and cardinality of the resultant table?Answer 23
For Cartesian product:
•
Degree = sum of columns of both tables = 4 + 5 = 9.
•
Cardinality = product of rows of both tables = 10 x 7 = 70.
Final Answer: Degree = 9, Cardinality = 70.
Question 24
24. From the following two tables, write the output of the Cartesian product (
customer and product table). Also write primary key of both tables.Customer
Cust. No.
Name
Address
Phone No.
111
Rohan Aggarwal
Delhi
28756389
222
Kanika Jain
Delhi
29807654
333
Keshav Gupta
Mumbai
25678945
444
Dharna
Bambay
24675678
Product
P. No.
P. Name
Price
Qty
101
Computer
35000
3
103
Scanner
20000
2
105
Printer
15000
1
Answer 24
Primary keys:
•
Customer table: Cust. No.•
Product table: P. No.Cartesian Product Output (4 x 3 = 12 rows):
Cust. No.
Name
Address
Phone No.
P. No.
P. Name
Price
Qty
111
Rohan Aggarwal
Delhi
28756389
101
Computer
35000
3
111
Rohan Aggarwal
Delhi
28756389
103
Scanner
20000
2
111
Rohan Aggarwal
Delhi
28756389
105
Printer
15000
1
222
Kanika Jain
Delhi
29807654
101
Computer
35000
3
222
Kanika Jain
Delhi
29807654
103
Scanner
20000
2
222
Kanika Jain
Delhi
29807654
105
Printer
15000
1
333
Keshav Gupta
Mumbai
25678945
101
Computer
35000
3
333
Keshav Gupta
Mumbai
25678945
103
Scanner
20000
2
333
Keshav Gupta
Mumbai
25678945
105
Printer
15000
1
444
Dharna
Bambay
24675678
101
Computer
35000
3
444
Dharna
Bambay
24675678
103
Scanner
20000
2
444
Dharna
Bambay
24675678
105
Printer
15000
1
Question 25
25. In the following two tables, find the union value of
employee and emp.EMPLOYEE
Emp. No.
Name
Salary
1000
Abishek Garg
25000
222
Prachi Goal
30000
1002
Simran Dua
25000
1003
Rishika Pal
40000
1004
Mohit Batra
23000
EMP
Emp. No.
Name
Salary
1002
Simran Dua
25000
1004
Mohit Batra
23000
1007
Sonal Gupta
26000
1009
Rohit Batia
50000
Answer 25
Union keeps all distinct rows from both tables and removes duplicate rows automatically.
Here, records for Emp. No.
1002 and 1004 are common in both tables, so they appear only once in the final result.Result (7 unique rows):
Emp. No.
Name
Salary
1000
Abishek Garg
25000
222
Prachi Goal
30000
1002
Simran Dua
25000
1003
Rishika Pal
40000
1004
Mohit Batra
23000
1007
Sonal Gupta
26000
1009
Rohit Batia
50000