Computer Science
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 |
- Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student’s preference field ?
- Roll no 17 has given two preferences in sports. Which property of relational DBMS is violated here ? Can we use any constraint or key in the relational DBMS to check against such violation, if any?
- Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation ?
Relational Database
1 Like
Answer
- In a relational database model where each student is required to have exactly one preference for a sports activity, assigning a NULL value to Roll no 24's preference field is not permitted.
- The primary key constraint ensures uniqueness in a relational database table. If Roll no 17 has two sports preferences, it violates this rule because a primary key constraint on "Rollno" would not allow different values for the same roll number. By using a primary key constraint on "Rollno," the relational database management system (DBMS) can prevent such violations by rejecting attempts to insert rows with duplicate roll numbers.
- No, since no student has selected Kabaddi as their preferred sport, there should not be an entry for Kabaddi in the Sports Preferences table.
Answered By
2 Likes
Related Questions
Compared to a file system, how does a database management system avoid redundancy in data through a database?
What are the limitations of file system that can be overcome by a relational DBMS?
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 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 ?
(b) In order to generate 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 appropriate name and data type for attributes. Also, identify appropriate primary key and foreign key so that the following two restrictions are satisfied:
- The same bill cannot be generated for different orders.
- Bill can be generated only for available items in the canteen.
(c) The school wants to find out how many calories students intake when they order an item. In which relation should the attribute 'calories' be stored?