Commerce (English Medium) इयत्ता १२ - CBSE Question Bank Solutions for Computer Science (Python)

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 question:

In order to generate the 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 relationship is required, decide the appropriate name and data type for attributes. Also, identify the appropriate primary key and foreign key so that the following two restrictions are satisfied:

1. The same bill cannot be generated for different orders.
2. Bill can be generated only for available items in the canteen.

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)

1. Name the attributes of EMPLOYEE, which can be used as candidate keys.
2. The company wants to retrieve details of the dependent of a particular employee. Name the tables and the key which are required to retrieve this detail.
3. What is the degree of EMPLOYEE and DEPENDENT relation?

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)

The company wants to retrieve details of the dependent of a particular employee. Name the tables and the key which are required to retrieve this detail.

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)

Is it correct to assign Movie_ID as the primary key in the MOVIE relation? If no, then suggest an appropriate primary key.

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)

Is it correct to assign AudiNo as the primary key in the AUDI relation? If no, then suggest an appropriate primary key.

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)

1. Is it correct to assign Movie_ID as the primary key in the MOVIE relation? If no, then suggest an appropriate primary key.
2. Is it correct to assign AudiNo as the primary key in the AUDI relation? If no, then suggest an appropriate primary key.
3. Is there any foreign key in any of these relations?

1. Name the primary key of each table.
2. Find foreign key(s) in table PROJECT-ASSIGNED.
3. Is there any alternate key in table STUDENT? Give justification for your answer.
4. Can a user assign duplicate value to the field RollNo of STUDENT table? Justify.

For the above-given database STUDENT-PROJECT, can we perform the following operation?

Insert a student record with a missing registration number value.

Write the output produced by the following SQL statement:

SELECT POW(2, 3);

Consider the following MOVIE table and write the SQL query based on it.

1. Display all the information from the Movie table.
2. List business is done by the movies showing only MovieID, MovieName, and Total_Earning. Total_ Earning to be calculated as the sum of ProductionCost and BusinessCost.
3. List the different categories of movies.
4. Find the net profit of each movie showing its MovieID, MovieName, and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.
5. List MovieID, MovieName, and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.
6. List details of all movies which fall in the category of comedy or action.
7. List details of all movies which have not been released yet.

Consider the following MOVIE table and write the SQL query based on it.

List business is done by the movies showing only MovieID, MovieName, and Total_Earning. Total_ Earning to be calculated as the sum of ProductionCost and BusinessCost.

Consider the following MOVIE table and write the SQL query based on it.

List the different categories of movies.

Consider the following MOVIE table and write the SQL query based on it.

Find the net profit of each movie showing its MovieID, MovieName, and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.

Consider the following MOVIE table and write the SQL query based on it.

List MovieID, MovieName, and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.

Consider the following MOVIE table and write the SQL query based on it.

List details of all movies which fall in the category of comedy or action.

Consider the following MOVIE table and write the SQL query based on it.

List details of all movies which have not been released yet.

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

1. Display the MatchID of all those matches where both teams have scored more than 70.
2. Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
3. Display the MatchID and date of matches played by Team 1 and won by it.
4. Display the MatchID of matches played by Team 2 and not won by it.
5. Change the name of the relation TEAM to T_DATA. Also, change the attributes TeamID and TeamName to T_ID and T_NAME respectively.

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

Display the MatchID and date of matches played by Team 1 and won by it.