Computer Science
For the below given database STUDENT-PROJECT, can we perform the following operations?
(a) Insert a student record with missing roll number value.
(b) Insert a student record with missing registration number value.
(c) Insert a project detail without submission-date.
(d) Insert a record with registration ID IP-101-19 and ProjectNo 206 in table PROJECT-ASSIGNED.
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 |
Answer
(a) No. the Roll No attribute in the STUDENT table is marked as primary key and NOT NULL. Therefore, inserting a student record with a missing Roll No value would violate the NOT NULL constraint and is not allowed.
(b) Yes, the registration_ID attribute in the STUDENT table does not have a NOT NULL constraint specified in the schema. Therefore, it is possible to insert a student record without registration number value.
(c) Yes, the SubmissionDate attribute in the PROJECT table does not have a NOT NULL constraint specified in the schema. Therefore, it is possible to insert a project detail without a SubmissionDate value.
(d) No, we cannot perform this operation. ProjectNo in PROJECT ASSIGNED table serves as a foreign key that references the primary key in the PROJECT table. Since ProjectNo "206" is not present in the PROJECT table, it cannot be inserted into the PROJECT ASSIGNED table.
Related Questions
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?
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
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