You are designing a simple database connected application that contains one form

Question

You are designing a simple database connected application that contains one form and one table. The purpose of the application is to allow the user to enter new books into the library's inventory.

20 Points - Briefly describe your table. You discussion should include attributes, justification, and data type for each field. You should have at least five fields of your choosing.

10 Points - Write the SQL command to create the table that you described above.

10 Points - Write the SQL command to insert a record into the table.

10 Points - Write the SQL command to delete a record from the table.

10 Points - Write at least 2 conditional SELECT queries.

40 Points - Design a simple interface using the drag and drop tools in NetBeans to support inserting a record into your database table. Include a screenshot in your write-up.

Explanation / Answer

For allowing user to enter new books, we need to define attributes of books and users. So for a book, there will be ISBN_Code, title, language,number of copies and book category with publication year and userId from which it is added.
Also, we can define master table for categories like programming, database or mathematics etc.
We will have a master table for users. Here we will insert all users of library with designation. User will have id, name, password for login and if it is admin or not. If user is admin then only we will give access to add books.

CREATE TABLE BookDetails
(
ISBN_Code int PRIMARY KEY,
title varchar(100),
Language varchar(10),
Totalcopies int,
Category_id int,
Publication_year int
AddedUserId int
);

CREATE TABLE BookCategories
(
Category_Id int PRIMARY KEY,
Category_Name varchar(50)
);

CREATE TABLE UserDetails
(
User_id int PRIMARY KEY,
userName varchar(50),
Password varchar(16),
isAdmin tinyint(1),
Designation varchar(20)
);

To insert records into table;

INSERT INTO BookDetails
VALUES('0006','Programming Concept','English',2,2,2006);
INSERT INTO BookDetails
VALUES('0007','Data Structures','English',5,1,2016);

INSERT INTO BookCategories VALUES(1,'Database');
INSERT INTO BookCategories VALUES(2,'Programming Language');

Insert into UserDetails values (1,'John Doe','1234abcd@123',1,'Lib_mgr');
Insert into UserDetails values (2,'Henry Stevens','1234abcd',0,'Lib_clr');

To delete records from table:

DELETE from BookDetails where ISBN_Code = '0007';

Conditional Select queries :
If we want to select all books where category is database then,

1. select * from BookDetails where Category_Id = 1;

If we want to select Users where user is admin then,
1. Select * from UserDetails where isAdmin = 1;

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.