I have some Info but need help with this part. Please help edit and update libra
ID: 3691797 • Letter: I
Question
I have some Info but need help with this part. Please help edit and update
library database system would need to run a number of queries and updates to function. For now we'll ignore the fact that the workers at the library are not likely to be typing in SQL- We're going to write some SQL queries and updates to do some of the basic work of the airline. These queries and updates will. If you think they require multiple SQL statements (like test 1), give me the entire sequence of SQL statements Please write the queries (SELECTs) and updates (UPDATEs) and inserts (INSERTs) for your database (these are the queries for the pages you mocked up in [H004]. You must run these on the LAMP.
CREATE TABLE Book Genre Char (15) NOT NULL, BookNumber VarChar(5) NOT NULL, Title Char (25) PRIMARY KEY CREATE TABLE Branch( Address Char (35) State Char(2) Town Char (20) Primary Key, Null Deafult 'NY Null Deafult'West Islip', CREATE TABLE Author( First Name Char (25) Last Name Char (25) Title Primary Key, NOT NULL, NOT NULL, Char (35) CREATE TABLE Patron( EmailAddress VarChar(50) NOT NULL, First Name Last Name Char (25) PRIMARY KEY, Char (25) NOT NULL, CREATE TABLE Copy of Book( Title Char (25) PRIMARY KEY, Author Char (25) NOT NULL Due Date Date NOT NULL, CREATE TABLE Wrote( Get Title From (Book) Get First Name From(Author)Explanation / Answer
Before going to implement your requirement. Let me tell you that your code has a lot of issues. to be corrected.
In the table book Title is given the primary key. Remember primary key has an unique value. Do you think that all books have different titles. Should have assigned booknumber as primary key.
Making adress as primary key will not give you database-accuracy. Normalizing the table with a zip code based data will give more advantage.
In the table Author, First name has given primary key. Do you think all authors have different first names. the same mistake has done in patron.
However, We will write the insertions into each and every table now.
While inserting check the primary key and foreign key relations.
Insertion into Book table.
insert into Book(Genre, BookNumber,title)
values('science',1235,'stars');
insert into Book(Genre, BookNumber,title)
values('Adventure',1234,'jump');
insert into Book(Genre, BookNumber,title)
values('thriller',1214,'shock');
Insertion into Branch table.
insert into Branch(Adress,state,town)
values('street1','fran','newyork');
insert into Branch(Adress,state,town)
values('street2','cal','zinkong');
Insertion into Author table.
Insert into author(Firstname,Lastname,title)
values('sam','halsv','jungle');
Insert into author(Firstname,Lastname,title)
values('smith','degeneres','Ellenbook');
Insertion into patron table.
insert into patron(Email Address, First name, last name)
values('ellen@gmail.com','taylor','swift');
insert into patron(Email Address, First name, last name)
values('jimmy@gmail.com','jimmy','fallon');
Insertion into copy of book table.
insert into copy of book(Title, author, due date)
values('comic','smith',12-1-2016);
insert into copy of book(Title, author, due date)
values('com','justin',12-1-2017);
Now we will write some update to the above queries.
Update Book
set Genre='comic'
where BookNumber=1214;
Update Author
set last name='Ellen'
where Title='"EllenBook';
Now we will write some selection queries.
the following query selects all attributes from the Books table.
select * from Book;
select title from Books where Booknumber=1214;
The following query gives all data from two table books and authors where titles are equal in both the tables.
select * from Book B, Author A where B.Title=A.Title.
For suppose if admin wants to know the list of available books which are written by a specific Author, then we need a query to retrieve the book's names.
select Title from Author
where first name='smith';