MSIT 630 Database Systems
1. Explain the concept of physical data independence and its importance in database systems (4
points)
2. Explain what problems are caused by the design of the following table. (4 points)
The Customer table
3. List four significant differences between a file-processing system and a DBMS. (4 points)
4. Describe the differences in meaning between the terms relation and relation schema. (4
points)
5. List two reasons why null values might be introduced into the database. (4 points)
6. Write the following queries in SQL, using the university schema
(https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-dir/DDL.sql) and
execute your SQL statement on the sample database with small relations
(https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-
dir/smallRelations/smallRelationsInsertFile.sql) and show me both the SQL statements and the
query results. (20 points, 4 points each)
a. Find the titles of courses in the Comp. Sci department that have 4 credits.
b. Find the name(s) of the instructor(s) who DON’T earn the lowest salary in the Physics
department.
c. Find the enrollment of each section (number of students enrolled) that was offered in Fall
2017.
d. Find the minimum enrollment, across all sections offered in Fall 2017.
e. Find the course ID and section ID of the sections that had the minimum enrollment in Fall
2017.
7. Write the following queries in SQL, using the university schema
(https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-dir/DDL.sql) and
execute your SQL statement on the sample database with small relations
(https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-
https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-dir/DDL.sql
https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-dir/smallRelations/smallRelationsInsertFile.sql
https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-dir/smallRelations/smallRelationsInsertFile.sql
https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-dir/DDL.sql
https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-dir/smallRelations/smallRelationsInsertFile.sql
dir/smallRelations/smallRelationsInsertFile.sql) and show me both the SQL statements and the
query results. (20 points, 4 points each)
a. Find the names of all students who have taken at least two courses offered by Comp. Sci.
department; make sure there are no duplicate names in the result. Note that students in other
departments can take courses from Comp. Sci. as well.
b. Find the IDs and names of all students who have not taken any course offering in 2017.
c. For each department, find the name and salary of the instructor who earns the minimum salary
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 minimum salary computed by
the preceding query (part 7.c).
e. Find the course titles of all prerequisite courses of “CS-319”.
https://codex.cs.yale.edu/avi/db-book/university-lab-dir/sample_tables-dir/smallRelations/smallRelationsInsertFile.sql
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
