You have to execute some SQL queries on the sample university database used in the textbook by Silberschatz et al and report your results. You have two options.
Set up the database on your own laptop.
From https://www.db-book.com/university-lab-dir/sample_tables-dir/index.html, download the SQL files to set up the university database. For the tables, there are two options: the small tables used in the textbook examples, and large tables with random data. You should download the small tables.
Create and populate the database from the downloaded files.
Record all queries and responses in a single text file YourRollNumber-assignment2.txt, and submit this file. You should be able to record your SQL session in a text file. For instance, in MySQL, you can save the entire session to a file MDS202499-assignment2.txt by using the following command.
mysql --tee=MDS202499-assignment2.txt
Optionally, copy and paste each query and its output to the text file.
Use the online SQL interpreter.
An online SQL interpreter is provided at https://www.db-book.com/university-lab-dir/sqljs.html
This already has the sample university database with the small tables preloaded.
Create a text file containing all your queries with the name YourRollNumber-assignment2-SQL-queries.txt.
For each query, execute it using the online SQL interpreter and take a screen dump as a pdf file using "Print to file" from the browser. Use the following naming convention for the pdf files. For the solution to question number N, the pdf dump should be named YourRollNumber-assignment2-SQL-answer-N.pdf.
Submit the SQL query text file and pdf screen dumps as separate files on Moodle.
Consider the following relation schema from the university database discussed in the lectures.
instructor(ID,name,dept_name,salary)
department(dept_name,building,budget)
course(course_id,title,dept_name,credits)
prerequisites(course_id,prereq_id)
section(course_id,sec_id,semester,year,building,
room_number,time_slot_id)
teaches(ID,course_id,sec_id,semester,year)
student(ID,name,dept_name,tot_cred)
takes(ID,course_id,sec_id,semester,year,grade)
Here student contains information about students in the university, and takes contains information about course registration by students.
Write SQL queries for the following.
Find the department(s) with the minimum budget. The output should contain the department name and the budget.
Find all departments that do not share a building with any other department. The output should contain the department name and the building.
Find all faculty members who teach exactly one course. The output should contain the instructor ID, instructor name, the course ID and title of the course taught by instructor.
Find all courses that have more than one pre-requisite. The output should contain the course ID, course title and the number of pre-requisites for that course.
Find all courses that are pre-requisites for more than one course. The output should contain the course ID, course title and the number of course for which this is a pre-requisites.
Find all students who are not registered for any course either in 2017 or in 2018. The output should contain the student ID, student name and the year in which they did not register for any course. If a student did not register in both 2017 and 2018, this should be reported in two separate rows.
Find all students who have registered for the same course more than once. For each instance where a student has registered for the same course more than once, the output should contain a row with the student ID, the student name, the course ID, the course title, the most recent semester and year when the course was registered for and the number of times the student has registered for that course.
Submit your solutions on Moodle.
Follow the instructions above about naming the submitted file(s).