Description
For this assignment, you are provided a MySQL database backup file. This is a text file
full of SQL commands that will create tables and insert data to populate a database. The
data are taken from a real system that enables users to view real estate information for
areas around Cleveland according to the roles assigned in the system.
Submit a plain text file (.txt or .sql) to Canvas by the due date and time.
Steps for setup:
1. Download eecs341as3.sql from Canvas
2. Create a database in your local MySQL instance, for example:
mysql> create database eecs341as3;
3. Next, switch to the database and load the database backup file, for example:
mysql> use eecs341as3
mysql> source eecs341as3.sql
4. Now, examine the table definitions one-by-one, for example:
mysql> show tables;
mysql> show create table user;
Once you have loaded the tables and you understand the entities and relationships
represented by the tables, answer the following questions by providing SQL queries that
are valid in MySQL:
1. Find the ID and name for all users who are not associated with a role.
2. Find names that are used by more than fifty (50) users. For each name, also
return the count of how many times the name is used, and sort the list of names so
that the most popular name is at the top of the list.
3. For each user, return the user ID, user name, and count of distinct areas that the
user is able to access based on the roles assigned to the user.
4. π user.id, user.name, role.id, role.name (
σuser.id=user_role.user_id ^ role.id=user_role.role_id ^ role.name=’Ward 6’ (
user × role × user_role
)
)
5. π user.id, user.name (
σuser_role.user_id is null (
user ⟕ user.id = user_role.user_id user_role
)
)
