Question

Consider the table LIBRARY as given below.

BookId	Title	Genre	Price
101	Python Basics	Technology	278
201	The Silent Patient	Fiction	340
301	Data Science	Technology	291
401	Oceans	Marine Life	NULL

(I) Which attribute(s) can be considered as the Candidate keys(s)? Justify your answer.

(II) Write an SQL query to insert a new record with the following values: BookId: 501, Title: AI for ALL, Genre: Technology, Price: 589

(III) Write an SQL query to add a new column Author that is a character data type of size 20.

Hint:

A candidate key never repeats and never holds NULL. Use INSERT INTO for the new row and ALTER TABLE ... ADD for the new column.

Answer 1

Step 1: Understand a candidate key:
A candidate key is a column (or set of columns) whose value is different in every row and is never NULL.
Such a column can identify each record on its own.

Step 2: Part (I) - test every attribute:
BookId has the values 101, 201, 301 and 401. All are different and none is NULL, so BookId is a candidate key.
Genre has Technology twice, so it cannot identify a row.
Price has a NULL value for the book Oceans, and 278, 291 and 340 could also repeat for new books. So Price cannot be a key.
Title has four different values in this table. It could still repeat when a new edition is added, so BookId is the safe choice.
So BookId is the candidate key.

Step 3: Part (II) - insert a new record:
INSERT INTO adds a row. Text values go inside quotes, numbers do not. The values follow the column order BookId, Title, Genre, Price.

INSERT INTO LIBRARY VALUES (501, 'AI for ALL', 'Technology', 589);

The safer form names the columns: INSERT INTO LIBRARY (BookId, Title, Genre, Price) VALUES (501, 'AI for ALL', 'Technology', 589);

Step 4: Part (III) - add a column:
A change in the table structure needs ALTER TABLE. ADD puts in the new column. A character column of size 20 is CHAR(20) or VARCHAR(20).

ALTER TABLE LIBRARY ADD Author CHAR(20);

After this, every existing row gets NULL in Author until we update it.

Final Answer:
(I) BookId is the candidate key because its values are unique and not NULL.
(II) INSERT INTO LIBRARY VALUES (501, 'AI for ALL', 'Technology', 589);
(III) ALTER TABLE LIBRARY ADD Author CHAR(20);