https://www.coursehero.com/sitemap/schools/126461-VIT-University-Vellore/

https://www.coursehero.com/sitemap/schools/126461-VIT-University-Vellore/courses/7533862-CSE2004/

Slot : l23+l24 Question 1) Consider the following relational schemas Passenger(pname: varchar2(10); pnr_number: number(8); amount: number(5,2); doj: date; status: varchar2(3)) Reserved_for(pnr_number: number(8); train_no: number(5)) Train(train_no: number(5); train_name: varchar2(10); source: varchar2(15); destination: varchar2(15)) Note: ‘doj’ stands for date of journey. Status may be ‘cf’(confirmed), ‘ncf’ (not confirmed), and ‘c’(cancelled). Do the following 1. Create the above tables with composite primary on Passenger table and primary key on Train table. Enforce the foreign keys on fields of ‘Reserved_for’ table. (b-5)2. See that ‘status’ field value should be any one of these ‘cf’ , ’ncf’, and ‘c’. Also see that ‘source’ and ‘destination’ fields should never be empty. Ensure that the date of journey should be not more than 30 days from date of reservation. (c-5) 3. Add at least 6 records to each table as per the requirements of queries given as below. (e-5) Mandatory: Display the structure and content of each table for each change and store it in your file to be uploaded. It carries 5 marks- (b,c,e-5) Answer the following queries in SQL and obtain required output (Output for each query be there in your file and it carries half of the marks allotted to the query) 4. List tickets numbers along with train names that travels from ‘Bangalore’ to ‘Chennai’ on journey dates between 13-November-2015 and 15-November-2015. (b-2) 5. Display date of journey field values in the format ‘ddth of month yyyy’ by adding one day to it. (c-2) 6. Get the total number of tickets reserved on each date of journey. (e-2) 7. List the ticket numbers whose amount is more than the amount of every ticket on 13-November2015. (Sub Query) (i-4) 8. Create a virtual table to display ticket numbers along with train numbers and train names on a particular date of journey those travel from ‘Bangalore’ to ‘Tirupat