1) Layna creates a table STOCK to maintain computer stock
in vidyalaya. After creation of the table, she has entered
data of 8 items in the table.
Based on the data given above answer the following questions:
(i) Identify the most appropriate column, which can be
considered as Primary key.
(ii) If three columns are added and 5 rows are deleted from the
table stock, what will be the new degree and cardinality of the
above table?
(iii) Write the statements to:
(a) Insert the following record into the table
Stockid - 201, dateofpurchase – 18-OCT-2022, name –
neckphone
Make – BoAT, price - 500
(b) Decrease the price of stock by 5% whose were purchased in
year 2020
OR (Option for part iii only)
(iii) Write the statements to:
(a) Delete the record of stock which were purchased before
year 2015.
(b) Add a column STATUS in the table with datatype as char
with 1 characters
Answers:
(i) stockid
(ii) degree = 8, cardinality = 3
(iii) (a) insert into stock values(201,’2022-10-
18’,’neckphone’,’boat’,500);
(b) update stock set price=price*0.95 where
year(dopurchase)=2020;
OR
(a) delete from stock where year(dopurchase) < 2015;
(b) alter table stock add column STATUS char(1);
2) Tarun created the following table in MySQL to maintain stock
for the items he has.
a) The Primary Key should be Productid since it uniquely identifies each
row. (1)
b) Degree – 6 Cardinality – 6 (½ + ½)
c) UPDATE inventory SET stock=stock+10 WHERE company = 'Parley';
OR
DELETE FROM inventory WHERE RATING IS NULL; (2)
3)
Answers:
i) Bcode
(ii) degree =9, cardinality =3
(iii) 1 mark for each
(a)INSERT INTO MOTOR VALUES(207, ‘TVS’, 500, 450, 480,
350);
(b) SELECT BNAME FROM MOTOR WHERE JANUARY>200;
OR
(iii) 1 mark for each
(a)ALTER TABLE MOTOR ADD MAY INT;
(b)SELECT SUM(MARCH) FROM MOTOR;
1. FID because FID has unique values.
2. Cardinality : 9 , Degree : 7
3. (i) INSERT INTO FURNITURE
VALUES(‘D001’,’Computer Table’,’01-Nov-2018’,10000,10);
(ii) UPDATE FURNITURE
SET price=price+1000 WHERE discount > 10;
OR (Option for part 3 only)
3. (a) DELETE FROM FURNITURE
WHERE price <20000;
(b) ALTER TABLE FURNITURE
ADD WOOD VARCHAR(20);
(i) Ans: ROLL_NO
(ii) Ans: New Degree: 8 New Cardinality: 5
(iii) a. INSERT INTO RESULT VALUES (108, ‘Aadit’, 470, 444, 475, ‘I’);
b. UPDATE RESULT SET SEM2=SEM2+ (SEM2*0.03) WHERE SNAME LIKE “N%”;
OR (Option for part iii only)
a. DELETE FROM RESULT WHERE DIV=’IV’;
b. ALTER TABLE RESULT ADD (REMARKS VARCHAR(50));
a)Altertable TeacheraddprimarykeyT_ID;
b)Alter tableTeacher add column P_ID int(1);
c)Alter tableTeacher add constraint(c1_01)foreign key(P_ID)
referencesTeacher(P_ID);
or
Altertable Posting modifycolumn Place varchar(10);
i) He creates Projects table first because PID is primary key and project is foreign key referenced
with PID for employee table.
ii) We need no. of rows and columns to compute degree and cardinality of any table which is not
specified over here. Suppose we consider project table has 3 rows and employee table has five
rows then cartesian products will be as follows:
no. of rows = 3 x 5 = 15
no. of columns = 4 + 6 = 10
Hence, Degree of cartesian product is 10.
iii) create table employee
(EID char(4) primary key,
name varchar(20),
DOB date not null,
DOJ date not null,
Salary integer,
Project char(5) references projects(PID));
iv) alter table employee add column gender char(1);
