CLASS XII: Unit 3 Database Concepts & MySQL

INFORMATICS PRACTICES

CLASS XII

UNIT 3 -DATABASE CONCEPTS AND MySQL

Q1. Define the following terms :-

a) Database:- A database is a collection of interrelated data and database system is basically a computer based record keeping system.

The advantages provided by a database system are:-

i) Reduced data redundancy

ii) Controlled data inconsistency

iii) Shared and secured data

iv) Standardized data & integrated data

b) Relation:- A relation is a table having atomic values, unique rows & unordered rows & columns.

c) Tuple:- A row in a relation is known as tuple.

d) Attribute:- A column of a table is known as attribute.

e) Domain:- A domain is a pool of values from which the actual values appearing in a given column are drawn.

f) Primary Key:- It is a column or a combination of columns that uniquely identifies a row in a relational table.

g) Candidate key :- All possible combination of columns that can possibly serve as the primary key are called candidate key.

h) Alternate key:- A candidate key that is not serving as a primary key is called alternate key.

i) Foreign Key:- A column or a combination of columns whose values are derived from primary key of some other table is called the foreign key of the table in which it is contained.

j) Schema:- A Schema is a logical collection of related database objects.

k) Constraint:- A constraint is a restriction applicable on the data contained in a column or a group of columns from one or more tables. Eg:- NOT NULL, CHECK, DEFAULT etc.

l) Degree:- The number of attributes (columns) in a relation determine the degree of a relation.

m) Cardinality:- The number of tuples(rows) in a relation is called the cardinality of the relation.

Q2. Compare char and varchar datatypes.

Ans:- The CHAR datatype stores fixed length strings such that strings having length

smaller than the field size are padded on the right with spaces before being

stored.

The VARCHAR on the other hand support variable length strings therefore stores

strings smaller than the field size without modification.

Q3. Explain the following constraints:-

Ans (a) NOT NULL:- ensures that column cannot have null values.

b) CHECK:- make sure that all values satisfy certain criteria.

c) UNIQUE:- ensures that all values in a column are different.

Q4. What is the role of UNIQUE constraint? How is Primary Key constraint different from UNIQUE constraint?

Ans:- The UNIQUE constraint ensures that all values in a column are distinct. In other words, no two rows can hold the same value for a column with unique constraint .

PRIMARY KEY constraint is different from UNIQUE constraint, since attributes with UNIQUE constraint are permitted to be NULL unless they have explicitly been declared NOT NULL, whereas primary Key attributes are required to be NON NULL and UNIQUE.

Q5. What is DEFAULT constraint?

Ans:- The DEFAULT constraint specifies the default value for the field when no value is specified at time of insertion of record.

Q6. When a column’s value is skipped in an INSERT command, which value is inserted in the database?

Ans:- The column that are not listed in INSERT Command will have their default value, if it is defined for them, otherwise, NULL value.

Q7.Define a Transaction.

Ans:- A Transaction is a logical unit of work (LUW) that must succeed or fail to its entirety. A transaction is an atomic operation which cannot be dived into smaller operations.

Q8. What do you mean by committing a transaction?

Ans:- Committing a transaction means all the steps of a transaction are carried out successfully & all data changes are made permanent in the database.

Q9. What does Transaction RollBack indicate?

Ans:-Transaction RollBack means transaction has not been finished completely and hence all data changes made by the transaction in the database, if any are undone & the database returns to the same state as it was before this transaction execution started.

Q10. What are the different properties of transaction maintained by database systems?

Ans:- To ensure the data integrity, the database system maintains the following properties of transactions:-

1) ATOMICITY:- The property ensures that either all operations of the transaction are reflected properly in the database, or none are. Atomicity ensures either all or none operations of a transaction are carried out.

2) CONSISTENCY:-This property ensures that the database remains in a consistent state before the start of transaction & after the transaction is over(Whether successfully or unsuccessfully)

3) ISOLATION:- Isolation ensures that each executing transaction execution in isolation i.e is unaware of other transactions executing concurrently in the system.

4) DURABILITY:- This property ensures that after the successful completion of a transaction i.e when a transaction commits the changes made by it to the database persist i.e remain in the database irrespective of other system failures.

Q11. What are the two ways in which multiple transactions can be executed?

Ans:- Multiple transactions can be executed in one of the following two ways:-

(i) Serially i.e Serial execution of Transactions. It ensures consistency of the database.

(ii) Concurrently i.e. Concurrent(simultaneous) execution of transactions.

Q12. What is SAVEPOINT?

Ans:- Savepoints are special operations that allow you to divide the work of a transaction into different segments. Incase of a failure, you can execute rollbacks to the savepoint only, leaving prior changes intact. Savepoints are great for situations where part of the transaction needs to be recovered in an uncommitted transaction,

Q13. What are single row and multiple row functions?

Ans:- Single Row Functions:- work with single row at a time. A single row function returns a result for every row of a queried table. Eg- Sqrt(), Concat(), Upper() etc.

Multiple Row Functions:- work with data of multiple rows at a time and return aggregated value. Eg:- Sum (), Count(), Max(), Min() etc.

Q14. What is Cartesian Product? How is it related to join?

Ans:- Cartesian Product is a very basic type of join that simply matches each row from one table to every row from another table.(Where the result table contains Addition of columns of two tables and rows would be multiplication of the number of rows in the two tables)

Q15. What is a join? How many types of joins can you create in MySQL ?

Ans:- Join is a query that combines rows from one or more tables. Different types of join in MySQL are :-

(i) Equi Join (ii) Non Equi Join (iii) Natural Join

(iv) Cross Join (v) Left, Right Join

Q16. Define :-

Ans:-

a) Equi Join:- In an Equi Join the values in the columns being joined are compared for equality. All the columns in the tables being joined are included in the results.

b) Non Equi Join:- A join in which tables are compared for some relationship other than equality.

c) Natural Join:- In Natural join one of the two identical columns can be eliminated by restating the query. i.e. in natural join the result consists of only the matched rows.

d)Cross Join:- all possible concatenations are formed of all rows in both the tables.

e) Left join:- Left Join is a form of join where all the rows (even non matching ones) from the first table are returned into the result.

Q17. Can you have multiple primary keys and multiple foreign keys in your Table?

Ans:- No we can have only one primary key in out table.

Yes, a table may have multiple foreign keys, and each foreign key can have different referenced table.

Q18. In MySQL, what table type(storage engine) is required for foreign keys to work?

Ans:- InnoDB

Q19. how would you find the storage engine of your table?

Ans:- SHOW CREATE TABLE < table-name>;

Q20. With which command, can you change storage engine of your table?

Ans:- ALTER TABLE <table- name> ENGINE=InnoDB;

Q21. What do you mean by Relational database?

Ans:- The relational database is one that follows the relational data model i.e., it stores information in the form of related tables consisting of rows and columns

Q22. Drop Table Employee2

Ans: DELETE FROM EMPLOYEE2;

DROP TABLE EMPLOYEE2;

Q23. Change the data type of column pincode to varchar(10) in the table customer.

Ans:- ALTER TABLE CUSTOMER
MODIFY (Pincode VARCHAR(10));

Q24. ADD a PRIMARY KEY constraint to the table Employee2 using the EmpID column.

Ans:- ALTER TABLE Employee2

ADD CONSTRAINT P_key

PRIMARY KEY(EmpID);

Q25. Can a foreign key refer to a column that has not been declared to be the primary key of a table? Explain.

Ans:- Yes a foreign Key can also refer to the columns that have been declared to be unique(i.e. the alternate keys of the table).

Q26. What is MySQL. Give its key features.

Ans:- MySQl is a freely available open source Relational Database Management System(RDBMS) that uses Structured Query Language(SQL). In a MySQL database, information is stored in Tables. A single MySQL database can contain many tables at once and store thousands of individual records.

Its features include a secure environment for storing, maintaining and accessing data. MySQl is a fast, reliable, scalable alternative to many of the commercial RDBMSs available today.

Q27. How are SQL Commands classified?

Ans:- SQL commands are classified into following categories:-

(i) Data Definition Language(DDL) Commands:- Commands that allow us to perform tasks released to data definition e.g.

Creating, altering and dropping.(CREATE TABLE, ALTER TABLE etc.)
Granting and revoking privileges and roles.
Maintenance commands

(ii) Data Manipulation Languages(DML) commands:- Commands that allow us to perform data manipulation e.g. retrieval, insertion, deletion and modification of data stored in a database. e.g insert, select, update , delete etc.

(iii) Transaction Control Commands(TCL):- Commands that allow us to manage and control the transaction( a transaction is one complete unit of work involving many steps) e.g.

Making changes to database permanent
Undoing changes to database, permanent
Creating Savepoints
Setting properties for current transactions

Q28. How do you define a foreign key in your table?

Ans:- a foreign Key is a field (or fields) that point to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words only values that are supposed to appear in the database are permitted.

In create Table command, one can add foreign key’s definition through the following syntax:

Foreign Key(<column-to-be designated as foreign key>) references Master Table(<primary Key of Master table>);

CREATE TABLE ORDERS

(

Order_ID integer,

Order_Date date,

Customer_Sid integer,

Amount double,

Primary Key (order_ID),

Foreign key (customer_SID) references CUSTOMER (SID)

);

Q29. List employee last name, work departments and monthly salary for those in Services, Planning and Information centre Departments.

Ans:- SELECT LastName, WorkDept, Salary

From Empl, Dept

Where WorkDept=Deptno

AND DeptName IN (‘Services’, ‘Planning’, Information Centre’);

Q30. Add one column Email of data type VARCHAR and size 30 to the table Customer.

Ans:- ALTER TABLE CUSTOMER

ADD (Email VARCHAR(30));

Q31. Give the commands to perform the following:-

(a) create the table structure Tour (TourNo, busNo, TicketPrice).

CREATE TABLE Tour

(

TourNo VARCHAR(5),

BusNo VARCHAR (15),

TicketPrice INT(5)

);

(b) Define the combination of TourNo and BusNo the Primary key

ALTER TABLE Tour ADD CONSTRAINT PK_TOUR PRIMARY KEY (TourNo,BusNo);

(c ) Create the Table structure Tourist (TourNo, BusNo, SeatNo, Name) where the combination of TourNo, BusNo and SeatNo is the Primary key

CREATE TABLE Tourist

(

TourNo VARCHAR(5),

BusNo VARCHAR(15),

SeatNo int(2),

Name VARCHAR(20),

CONSTRAINT PK_Tourist PRIMARY KEY(TourNo, BusNo, SeatNo)

);

(d) Define the combination of TourNo and BusNo a foreign key that refers to the

corresponding combination of the Tour Table.

ALTER TABLE Tourist

ADD CONSTRAINT FK_TOURIST _TOUR FOREIGN KEY(TourNo, BusNo)

REFERENCES Tour (TourNo, BusNo);

Q32. Give the command to create the table

Product(ProdCode, Name, Price, FreeProdCode)

Where FreeProdCode refers to ProdCode.

Ans:-

CREATE Table Product

( ProdCode CHAR(5) Priamry Key,

Name VARCHAR(20),

Price INT(5),

FreeProdCode CHAR(5) FOREIGN KEY (FreeProdCode)

RERENCES product (ProductCode)

);

Q33. Name and explain the different data models?

Ans:- A data model is a collection of conceptual tools for describing data, data relationships, data semantics etc. There are generally three data models available:-

Relational Model:- The relational model represents data and relationships among data by a collection of tables known as relations, each of which has a number of columns with unique names.
Network Models:- The network Model represents data by collections of records and relationships among data are represented by links which can be viewed as pointers. The records in the database are organized as collection of arbitrary graphs.
Hierarchical Model:- The hierarchical model is similar to the network model in the sense that data and relationships among data are represented by records and links respectively. It differs from the network model in that the records are organized as collections of trees rather than arbitrary graphs. The relational model differs from the network and hierarchical models in that it does not use pointers or links. Instead , the relational model relates records by the values they contain.

Q34. Write SQL Commands for the following on the basis of given table CLUB:-

COACH_ID	COACHNAME	AGE	SPORTS	DATEOFAPP	PAY	SEX
1	KUKREJA	35	KARATE	27/03/1996	1000	M
2	RAVINA	34	KARATE	20/01/1998	1200	F
3	KARAN	34	SQUASH	19/02/1998	2000	M
4	TARUN	33	BASKETBALL	01/01/1998	1500	M
5	ZUBIN	36	SWIMMING	12/01/1998	750	M
6	KETAKI	36	SWIMMING	24/02/1998	800	F
7	ANKITA	39	SQUASH	20/02/1998	2200	F
8	ZAREEN	37	KARATE	22/02/1998	1100	F
9	KUSH	41	SWIMMING	13/01/1998	900	M
10	SHAILYA	37	BASKETBALL	19/02/1998	1700	M

a) to show all information about the swimming coaches in the club

SELECT * FROM CLUB WHERE SPORTS=’SWIMMING’;

b) to list names of all coaches with their date of appointment (DATEOFAPP) in descending order.

SELECT COACHNAME, DATEOFAPP FROM CLUBORDER BY DATEOFAPP DESC;

c) To display a report, showing coachname , pay age and bonus(15% of pay) for all the coaches.

SELECT COACHNAME, PAY,AGE,PAY*0.15 AS BONUS FROM CLUB;

d) Give the output of following SQL Commands

(i) SELECT LCASE(SPORTS) FROM CLUB;

LCASE(SPORTS)

karate

squash

basketball

swimming

squash

karate

swimming

basketball

(ii) SELECT MOD(AGE,5) FROM CLUB WHERE SEX=’F’;

MOD(AGE,5)

(iii) SELECT POWER(3,2) FROM CLUB WHERE SPORTS =’KARATE’;

POWER(3,2)

(iv) SELECT SUBSTR(COACHNAME, 1,2) FROM CLUB WHERE DATEOFAPP>’1998-01-31’;

Substr(coachname,1,2)

(v) SELECT COUNT(DISTINCT SPORTS ) FROM CLUB;

(vi) SELECT MIN(AGE) FROM CLUB WHERE SEX=’F’;

(vii) SELECT AVG(PAY) FROM CLUB WHERE SPORTS=’KARATE’;

1100.0000

(viii) SELECT SUM(PAY) FROM CLUB WHERE SPORTS=’SWIMMING’;

NULL

Q35. Consider the following tables Item and Customer. Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii)

Table: Item

I_ID	ItemName	Manufacturer	Price
PC01	Personal Computer	ABC	35000
LC05	Laptop	ABC	55000
PC03	Personal Computer	XYZ	32000
PC06	Personal Computer	COMP	37000
LC03	Laptop	PQR	57000

Table : Customer

C_ID	CustomerName	City	I_ID
01	N Roy	Delhi	LC03
06	H Singh	Mumbai	PC03
12	R Pandey	Delhi	PC06
15	C Sharma	Delhi	LC03
16	K Agarwal	Banglore	PC01

(i) To display the details of those customers whose City is Delhi

SELECT * FROM CUSTOMER

WHERE City = ‘Delhi’;

(ii) To display the details of Item whose Price is in the range of 35000 to

55000(Both values included)

SELECT * FROM ITEM

WHERE PRICE BETWEEN 35000 TO 55000;

(iii) To display the CustomerName, City from table Customer, and ItemName and Price from table Item, with their corresponding matching I_ID

SELECT CustomerName, City , ItemName, Price

FROM CUSTOMER, ITEM

WHERE CUSTOMER.I_ID =ITEM.I_ID;

(iv) To increase the Price of all Items by 1000 in the table Item.

UPDATE ITEM

SET Price = Price + 1000;

(v) SELECT DISTINCT CITY FROM CUSTOMER;

Delhi

Mumbai

Bangalore

(vi) SELECT ItemName, Max (Price), Count(*) FROM Item Group By ItemName;

Personal Computer 37000 3

Laptop 57000 2

(vii) SELECT CustomerName, Manufacturer FROM Item, Customer WHERE Item.Item_ID = customer.Item_Id;

N Roy PQR

H Singh XYZ

R Pandey COMP

C Sharma PQR

K Agarwal ABC

(viii) SELECT ItemName, Price * 100

FROM Item WHERE Manufacturer = ‘ABC’;

Personal Computer 3500000

Laptop 5500000

Q36.(a) CREATE TABLE Customer as per the following table Instance Chart

Column Name	Cust_IDPrimary key	Cust_Name	Cust_Address1	Cust_Address2	PinCode	Cust_Phone
Datatype	Number	Varchar	Varchar	Varchar	Varcahr	Number
Length	7	30	20	20	6	10

(b) Add one column Emailof datatype Varchar and size 30 to the table customer

a) Create Table Customer

( Cust_ID( int (7) primary Key,

Cust_Name varchar(30),

Cust_Address1 varchar(20),

Cust_Address2 varchar(30),

PinCode int(6),

Cust_Phone Varchar(10));

(b) Alter Table Customer

Add (Email varchar (30));

(c ) Alter Table Customer

Add (customerIncomeGroup varchar(10));

(d) Alter Table Customer

Drop CustomerIncomeGroup;

Q37. (a)Write SQL Queries for the following

Column Name	DeptID	DeptName
Key Type	Primary
Null/Unique		Not Null
Datatype	Number	Varcahr
Length	2	20

(b) Create table employee as per the following Table Instance Chart

Column Name	EmpID	EmpName	EmpAddress	EmpPhone	EmpSal	DeptID
Key Type	Primary					Foreign
Null/Unique		Not Null
FK Table						Department
FK Column						DeptID
Datatype	Number	Varchar	Varchar	Varchar	Number	varchar
Length	6	20	30	10	9,2	2

Ans:- (a) Create table Department

( DeptID int (2) Primary Key,

Deptname varchar(20) NOTNULL);

(b) Create Table employee

(

EmpID int(6) Primary Key,

EmpName varchar(20) Not Null,

EmpAddress varcahr(30),

EmpPhone varchar (10),

EmpSal double(9,2),

DeptID int(2),

Foreign Key(DeptID) References Department (DeptID)

) Engine=InnoDB;

Q38. Write the output of the following SQL queries

(i) Select ROUND(6.5675,2);

6.57

(ii) Select Truncate(5.3456,1);

5.3

(iii) Select DayofMonth(‘2009-08-25);

(iv) Select MID(‘Class 12’, 23);

las

CLASS XII

Thursday, 7 May 2015

Unit 3 Database Concepts & MySQL

No comments:

Post a Comment