Q.1. Consider the library database. member(memb_no, name, age) book(isbn, title,
ID: 3765450 • Letter: Q
Question
Q.1. Consider the library database.
member(memb_no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb_no, isbn, date)
Write the following queries in SQL.
a) Print the names of members who have borrowed any book published by McGraw-Hill.
b) Print the number of books borrowed every date.
Q.2. Consider the university database
course(course_id, title, dept_name, credits)
instructor(ID, name, dept name, salary)
section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
student(ID, name, dept_name, tot_cred)
a) Find all instructors earning the highest salary (there may be more than one with the same salary).
b) Delete all courses that have never been offered (that is, do not occur in the section relation).
c) Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.
d) Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outerjoin, and should not use scalar subqueries.
e) Write the same query as above, but using a scalar subquery, without outer join.
Q.3. Perhaps the most important data items in any database system are the passwords that control access to the database. Suggest a scheme for the secure storage of passwords. Be sure that your scheme allows the system to test passwords supplied by users who are attempting to log into the system.
Q.4. List three ways in which caching can be used to speed up Web server performance.
Explanation / Answer
Q.1
member(memb_no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb_no, isbn, date)
a) select m.name from name m,borrowed b,book k where k.publisher = ' McGraw-Hill' and m.memb_no = b.memb_no and b.isbn = k.isbn;
b) select count(k.isbn),b.date from book k,borrowed b where k.isbn = b.isbn;
Q.2 )
course(course_id, title, dept_name, credits)
instructor(ID, name, dept name, salary)
section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
student(ID, name, dept_name, tot_cred)
a) select ID , name from instructor where salary = ( select max ( salary ) from instructor )
b) delete from course where course_id not in ( select course_id from section );
C) insert into instructor select ID , name , dept _name , 10000 from student where tot cred > 100;
d) select ID , name , count ( course_id , sec_id , year , semester ) as ’Number of sections’ from instructor natural left outer join section group by ID , name ;
E) select ID , name , ( select count (*) as ’Number of sections’ from section T where T.id = I.id ) from instructor I ;
Q.3 A schema for storing password would be to encrpt each password and use a hash-index as a user_id .The user_id can be access easily the encrpted password .When the entire database is encrypted, it is easy for the database to perform decryption as data is fetched from disk into memory, so in- memory storage is unencrypted.With this option,every thing in the database, including indices, is encrypted when on disk, but un-encrypted in memory. As a result, only the data access layer of the database system code needs to be modified to perform encryption, leaving other layers untouched. Thus, indices can be used unchanged, and primary-key and foreign-key con- straints enforced without any change to the corresponding layers of the database system code.
Q.4)
What is Caching
Caching refers to any mechanism that stores previously retrieved content for future use.The goal of caching is to increase the speed of content delivery by reducing the amount of redundant work a server needs to perform. Putting a file in memory to re-use it can save millions of drive accesses; thus, the speed of getting the browser what the user needs is increased by magnitudes. Caching and performance go hand-in-hand.
Three ways in which caching can be used to speed up Web server performance.
Proxy caching
Proxy cache hierarchies
Web server acceleration
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.