Computer Science
School uniform is available at M/s Sheetal Private Limited. They have maintained SCHOOL_UNIFORM Database with two relations viz. UNIFORM andCOST. The following figure shows database schema and its state.
School Uniform Database
Attributes and Constraints
Table: UNIFORM
| Attribute | UCode | UName | UColor |
|---|---|---|---|
| Constraints | Primary Key | Not Null | - |
Table: COST
| Attribute | UCode | Size | Price |
|---|---|---|---|
| Constraints | Composite Primary Key | >0 | |
Table: UNIFORM
| UCode | UName | UColor |
|---|---|---|
| 1 | Shirt | White |
| 2 | Pant | Grey |
| 3 | Skirt | Grey |
| 4 | Tie | Blue |
| 5 | Socks | Blue |
| 6 | Belt | Blue |
Table: COST
| UCode | Size | COST Price |
|---|---|---|
| 1 | M | 500 |
| 1 | L | 580 |
| 1 | XL | 620 |
| 2 | M | 810 |
| 2 | L | 890 |
| 2 | XL | 940 |
| 3 | M | 770 |
| 3 | L | 830 |
| 3 | XL | 910 |
| 4 | S | 150 |
| 4 | L | 170 |
| 5 | S | 180 |
| 5 | L | 210 |
| 6 | M | 110 |
| 6 | L | 140 |
| 6 | XL | 160 |
(a) Can they insert the following tuples to the UNIFORM Relation ? Give reasons in support of your answer.
- 7, Handkerchief, NULL
- 4, Ribbon, Red
- 8, NULL, White
(b) Can they insert the following tuples to the COST Relation ? Give reasons in support of your answer.
- 7, S, 0
- 9, XL, 100
Relational Database
2 Likes
Answer
(a)
1. Tuple (7, Handkerchief, NULL): This tuple can be inserted because there is no constraint mentioned in the schema that prohibits NULL values for the UColor attribute.
2. Tuple (4, Ribbon, Red): This tuple can be inserted as all attributes have valid non-null values.
3. Tuple (8, NULL, White): This tuple cannot be inserted because UName attribute cannot be NULL as per the schema constraints.
(b)
1. Tuple (7, S, 0): This tuple cannot be inserted because the COST Price attribute must be greater than 0 as per the schema constraints.
2. Tuple (9, XL, 100): This tuple can be inserted as all attributes have valid values and the COST Price is greater than 0.
Answered By
1 Like
Related Questions
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?
An organisation wants to create a database EMPDEPENDENT to maintain following details about its employees and their dependent.
EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID) DEPENDENT(EmployeeID, DependentName, Relationship)(a) Name the attributes of EMPLOYEE, which can be used as candidate keys.
(b) The company wants to retrieve details of dependent of a particular employee. Name the tables and the key which are required to retrieve this detail.
(c) What is the degree of EMPLOYEE and DEPENDENT relation?
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)(a) Is it correct to assign Movie_ID as the primary key in the MOVIE relation ? If no, then suggest an appropriate primary key.
(b) Is it correct to assign AudiNo as the primary key in the AUDI relation ? If no, then suggest appropriate primary key.
(c) Is there any foreign key in any of these relations ?
For the below given database STUDENT-PROJECT, answer the following:
(a) Name primary key of each table.
(b) Find foreign key(s) in table PROJECT-ASSIGNED.
(c) Is there any alternate key in table STUDENT? Give justification for your answer.
(d) Can a user assign duplicate value to the field RollNo of STUDENT table? Justify.
Student Project Database
Table: STUDENT
Roll No Name Class Section Registration_ID 11 Mohan XI 1 IP-101-15 12 Sohan XI 2 IP-104-15 21 John XII 1 CS-103-14 22 Meena XII 2 CS-101-14 23 Juhi XII 2 CS-101-10 Table: PROJECT
ProjectNo PName SubmissionDate 101 Airline Database 12/01/2018 102 Library Database 12/01/2018 103 Employee Database 15/01/2018 104 Student Database 12/01/2018 105 Inventory Database 15/01/2018 106 Railway Database 15/01/2018 Table: PROJECT ASSIGNED
Registration_ID ProjectNo IP-101-15 101 IP-104-15 103 CS-103-14 102 CS-101-14 105 CS-101-10 104