Explain the concept of physical data independence- Database Management System
1. Explain the concept of physical data independence and its importance in database systems
2. Explain what problems are caused by the design of the following table.
The Customer table attached in complete requirement doc file
3. List four significant differences between a file-processing system and a DBMS.
4. Describe the differences in meaning between the terms relation and relation schema.
5. List two reasons why null values might be introduced into the database.
6. Write the following queries in SQL, using the university schema. (Appendix A, page 1271)
a. Find the titles of courses in the Biology department that have 4 credits.
b. Find the lowest salary of instructor in Comp. Sci. department.
c. Find the enrollment of each section that was offered in Fall 2011.
d. Find the minimum enrollment, across all sections, in Fall 2011.
e. Find the sections that had the minimum enrollment in Fall 2011.
7. Write the following queries in SQL, using the university schema. (Appendix A, page 1271)
a. Find the names of all students who have taken at least one Comp. Sci. course; make sure there
are no duplicate names in the result.
b. Find the IDs and names of all students who have not taken any course offering before Spring
2009.
c. For each department, find the maximum salary of instructors in that department. You may
assume that every department has at least one instructor.
d. Find the highest, across all departments, of the per-department maximum salary computed by
the preceding query (part 7.c).
e. Find the course titles of all prerequisite courses of “CS-315”.
8. Write the following queries in SQL, using the university schema. (Appendix A, page 1271)
a. Create a new course “CS-001”, titled “Weekly Seminar”, with 1 credit.
b. Create a section of this course in Fall 2011, with sec_idof 1.
c. Delete the course CS-001. What will happen if you run this delete statement without first
deleting offerings (sections) of this course?
9. Show that (proof), in SQL, <>all is identical to not in.
10. Show how to define a view total_credits(year, num_credits), giving the total number of
credits taken by students in each year. a
