Assignment 2: Database Design - SQL
—--------------------------------------------------------------------------------
1. Define a relational schema to capture the above information. The schema should include table definitions, attribute definitions, and attribute data types.
2. Create tables in SQL for the above schema.
3. Insert sample records in the tables. At least 5 records should be inserted in each table.
4. Write SQL and Relational Algebra Queries for the following:
(i) Roll number and name of all the students who are managing the “Megaevent”
(ii) Roll number and name of all the students who are managing “Megevent” as an “Secretary”.
(iv) Name of all the colleges who have at least one participant in “Megaevent” (v) Name of all the events which is managed by a “Secretary”
(vi) Name of all the “CSE” department student volunteers of “Megaevent”
(vii) Name of all the events which has at least one volunteer from “CSE”
(ix) Name of the department with the largest number of volunteers in all the events which has at least one participant from “IITB”
Deliverable:
(a) Write a report listing all the SQL commands, records inserted and output of queries. (b) A single script file with the commands (in sequence) – table create, record insert, sql queries in listed order. The file extension should be .sql
Submission Details:
The SQL queries will have to be executed from pgsql in your terminal in the next lab.
