ASSIGNMENT 4 QUESTIONS 1. Modify Modify the trains trains schema/table schema/table which which we saw saw earlier to create constra constraints ints to check check the following:
2.
1.
The value of timein is always less than or equal to timeout
2.
When a train is removed from service, all its halts should be deleted.
Insert inconsistent data and verify the constraints.
3. Write SQL Create table statements to create the following schema. Include all appropriate primary and foreign key declarations. Choose appropriate types for each attribute. 1. remotecentre(c remotecentre(centreId entreId,, college, college, town, town, state) 2. person person(ID, (ID, name name,, email) email) 3. programme(p programme(progId rogId,, title, fromdate, fromdate, todate) todate) 4. coordinator coordinator(ID, (ID, progId, progId, centreId centreId)) 5. participant(I participant(ID, D, progId progId,, centreId) centreId)
ANSWERS
Q1 1.1 Query
create table trainhalts1 as select * from trainhalts; alter table trainhalts1 add constraint timeincheck check (timein <= timeout);
update trainhalts1 values set timein='24.00' where stcode='CST';
OUTPUT
ERROR: new row for relation "trainhalts1" violates violates check constraint "timeincheck"
********** Error **********
ERROR: new row for relation "trainhalts1" violates check constraint "timeincheck" SQL state: 23514
1.2. Query
create table trainhalts2 (id varchar(5) , seqno integer , stcode varchar(10), timein varchar(5) , timeout varchar(5) , primary key (id) ); output: - NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "trainhalts2_pkey" for table "trainhalts2" Query returned successfully with no result in 87 ms.
alter table trainhalts2 drop constraint trainhalts2_pkey; Query returned successfully with no result in 35 ms.
insert into train2 values ('KP11' ,'CST-KYN'); insert into train2 values ('KP11L' ,'CST-KYN_LOCAL'); insert into train2 values ('T129' ,'CST-TNA_LOCAL'); insert into train2 values ('A63' ,'CST-DL_LOCAL'); insert into train2 values ('K101' ,'CST-KYN_LOCAL'); insert into train2 values ('N27' ,'CST-TNA_LOCAL'); insert into train2 values ('S33' ,'CST-KGR_LOCAL'); insert into train2 values ('A65' ,'CST-AMR_LOCAL'); insert into trainhalts2 values ('KP11' , 0 , 'CST' , NULL, '20.23'); insert into trainhalts2 values ('KP11' , 1 , 'BYC' , '20.31', '20.32'); insert into trainhalts2 values ('KP11' , 2 , 'DR' , '20.41', '20.42'); insert into trainhalts2 values ('KP11' , 3 , 'GPR' , '20.52', '20.53'); insert into trainhalts2 values ('KP11' , 4 , 'GPR' , '20.52', '20.53'); insert into trainhalts2 values ('KP11' , 5 , 'DR' , '20.41', '20.42'); insert into trainhalts2 values ('KP11' , 6 , 'GPR' , '20.58', '20.59'); insert into trainhalts2 values ('KP11' , 7 , 'TNA' , '21.21', '21.22'); insert into trainhalts2 values ('KP11' , 8 , 'DL' , '21.45', '21.46'); insert into trainhalts2 values ('KP11' , 9 , 'KYN' , '21.54', NULL); insert into trainhalts2 values ('A65' , 0 , 'CST' , NULL , '20.52'); insert into trainhalts2 values ('A65' , 1 , 'BYC' , '21.00' , '21.01'); insert into trainhalts2 values ('A65' , 2 , 'DR' , '21.10' , '21.11'); insert into trainhalts2 values ('A65' , 3 , 'KRL' , '21.22' , '21.23'); insert into trainhalts2 values ('A65' , 4 , 'GPR' , '21.28' , '21.29');
insert into trainhalts2 values ('A65' , 5 , 'TNA' , '21.49' , '21.50'); insert into trainhalts2 values ('A65' , 6 , 'DL' , '22.13' , '22.14'); insert into trainhalts2 values ('A65' , 7 , 'KYN' , '22.22' , '22.23'); insert into trainhalts2 values ('A65' , 8 , 'AMR' , '22.36' , NULL); Query returned successfully: 1 row affected, 22 ms execution time.
alter table trainhalts2 add constraint trainhalts2_fkey foreign key(id) references train2(id) on delete cascade;
Query returned successfully with no result in 46 ms. delete from train2 where id='KP11'; Query returned successfully: 1 row affected, 25 ms execution time. select * from trainhalts2; "A65";0;"CST";"";"20.52" "A65";1;"BYC";"21.00";"21.01" "A65";2;"DR";"21.10";"21.11" "A65";3;"KRL";"21.22";"21.23" "A65";4;"GPR";"21.28";"21.29" "A65";5;"TNA";"21.49";"21.50" "A65";6;"DL";"22.13";"22.14" "A65";7;"KYN";"22.22";"22.23" "A65";8;"AMR";"22.36";"" in which the service with id KP11 IS DELETED FROM TRAINHALTS2 IF WE DELETE FROM TRAIN2.
Q 2. verifying check constraint which is created in question1 and sub question 1
trying to update with inconsistent data
Query
update trainhalts1 values set timein='24.00' where stcode='CST';
OUTPUT
triERROR: new row for relation "trainhalts1" violates check constraint "timeincheck"
********** Error **********
ERROR: new row for relation "trainhalts1" violates check constraint "timeincheck" SQL state: 23514
verifying check constraint which is created in question 1 and sub question 2 trying to insert data in id of trainhalts2 which is foreign key and not in train2 id attribute which is primary key Query insert into trainhalts2 values ('bq11',1,'x','10.00','12.00'); ERROR: insert or update on table "trainhalts3" violates foreign key constraint "trainhalts3_fkey" DETAIL: Key (id)=(bq11) is not present in table "train3".
********** Error ********** ERROR: insert or update on table "trainhalts3" violates foreign key constraint "trainhalts3_fkey" SQL state: 23503 Detail: Key (id)=(bq11) is not present in table "train3".
Q3.1 Query
create table remotecentre(centreId varchar(20) primary key, college varchar(50), town varchar(30), state varchar(30)); output
:--
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "remotecentre_pkey" for table "remotecentre" Query returned successfully with no result in 73 ms.
Q 3.2
Query
create table person(ID varchar(30) primary key , name varchar(50), email varchar(50)); output NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "person_pkey" for table "person" Query returned successfully with no result in 53 ms.
Q 3.3 Query
create table programme(progId varchar(30) primary key, title varchar(50), fromdate date, todate date ); output NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "programme_pkey" for table "programme" Query returned successfully with no result in 86 ms.
