In this question, you are asked to design a database scheme for summer internship system of a department. Rules:

* All firms can have multiple supervisors, * A student can perform more than one internships, * A student can not perform internships in the same firm more than once

Every Student has: Name, Surname, Student number, E-mail, Phone number, SSN, Registration status, Registrationdate, Password Birthdate.

Every Firm has: Firm name, Address, Phone, Website.

Every Firm Supervisor has: Firm, Name, Surname, Phone, E-mail. Finally,

every Assigned Internships has: Student, Firm, Start date, End date, Supervisor.

Write the queries that perform the following operations:

1. Create all tables considering types, primary / foreign keys, indexes and null/not null status.

2. Display number of students whose status are not active.

3. Display a grouped list of names and passwords of all students who share the same password in the system.

4. Display a list of all student who has performed an internship in companies that have \Software" word in its name (e.g. \Innovative Software", \Adept Software Systems", etc).

5. Display a list of all internships performed in 2020. Your results must include student number, student name, firm name, firm e-mail, supervisor name, supervisor phone, start and end dates.

6. Set status of students who has registered at least two years ago to passive.