Page 303 - Information_Practice_Fliipbook_Class11
P. 303

Table: UNIFORM
                  UCode          UName           UColor
                  1              Shirt           White
                  2              Pant            Grey
                  3              Skirt           Grey
                  4              Tie             Blue
                  5              Socks           Blue
                  6              Belt            Blue
                  a. Can they insert the following tuples into the UNIFORM relation? Give reasons in support of your answer.
                    i.  7, Handkerchief, NULL
                    ii.  4, Ribbon, Red
                    iii.  8, NULL, White
                  b. Can they insert the following tuples into the COST relation? Give reasons in support of your answer.
                   i.  7, S, 0
                   ii.  9, XL, 100
             Ans.  a.
                   i.  Yes, because Ucode 7
                   ii.  No, because Ucode 4 is already assigned to Tie and Ucode can’t have duplicate values being a primary key.
                   iii.  No, because Uname is following the Not Null constraint.
                  b.
                    i.  No, as it is violating check constraints
                   ii.  Yes
              11.  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?
             Ans  a. Yes, we can use Movie_ID as the primary key because each movie can have unique values.
                  b.  No, AudiNo along with Movie_ID should be made as the primary key. That is, the relation should have a composite primary key as
                    (AudiNo, Modie_ID)
                  c. Yes, Movie_ID is a foreign key in the AUDI table, which refers  to the Movie_ID column of MOVIE table.

              12.  For the above given database STUDENT-PROJECT, answer the following:
                  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


                                                                Database Concepts and the Structured Query Language  289
   298   299   300   301   302   303   304   305   306   307   308