[Solved] CSE3330-5330 Project 1

In this project, you will get started on how to use a relational DBMS. You can use the MySQL system and the MySQL workbench for creating tables, populating them with data, and querying the tables. You should do the following:

1. Create the following tables specified in the textbook(Figure 3.2): EMPLOYEE,
2. DEPARTMENT, PROJECT, WORKS_ON, DEPT_LOCATIONS. You can write CREATE

TABLE statements in a text file, and execute the file using SQLPLUS; OR you can use MySQL workbench to create the tables. Choose appropriate data types for each attribute. (Important Note: Because there are circular references in the referential integrity constraints, you may have to add some referential integrity constraints later, after the tables are first created without these constraints).

2. Load the records that will be provided to you into each of the tables that you created. (Note: Again, here you may have to either: disable some foreign key constraints when you insert records in the first table you load because of circular references and then enable the constraints; OR insert some records with NULL for some of their foreign keys and then update the foreign key values after the records they reference are inserted (because of the circular references)). You can use any programming or scripting language you are familiar with (JAVA with JDBC, Pro*C, PERL, PHP, etc.) OR you can use MySQL workbench OR other tool to load the data.
3. Write SQL queries OR use a simple Web interfaces to get the results of the following queries:
4. Enter a department name, and retrieve all the names and salaries of all employees who work in that department.
5. Enter an employee last name and first name and retrieve a list of projects names/hours per week that the employee works on.
6. Enter a department name and retrieve the total of all employee salaries who work in the department.
7. For each department, retrieve the department name and the number (count) of employees who work in that department. Order the result by number of employees in descending order.
8. For each employee who is a supervisor, retrieve the employee first and last name and the number (count) of employees that are supervised. Order the result in descending order.