Database Design and Queries

This practical assignment focuses on database design and querying using SQL. The solution includes an Entity-Relationship Diagram (ERD), the creation of tables with insert commands for a database named 'EMP_GROUP_DB', and the execution of three SQL queries. Query 1 retrieves all groups and their associated users, even if a group has no users. Query 2 lists all rooms and their assigned groups, including rooms without assigned groups. Query 3 provides a list of users, their groups, and assigned rooms, sorted alphabetically. The assignment demonstrates fundamental database concepts and SQL skills, providing a practical approach to database management.

Contents
ERD.............................................................................................................................................................2
TABLES with insert commands..................................................................................................................2
QUERIES....................................................................................................................................................4
Q1)...........................................................................................................................................................4
Q2)...........................................................................................................................................................6
Q3)...........................................................................................................................................................6

ERD
TABLES with insert commands
CREATE DATABASE EMP_GROUP_DB;
USE EMP_GROUP_DB;
CREATE TABLE USERS(
userID INT PRIMARY KEY,
user_name VARCHAR(20) NOT NULL
);

INSERT INTO USERS VALUES
(1,'Modesto'),
(2,'Ayine'),
(3,'Christopher'),
(4,'Cheong woo'),
(5,'Saulat'),
(6,'Heidy');
CREATE TABLE GROUPS(
groupID INT PRIMARY KEY,
group_name VARCHAR(30)NOT NULL
);
INSERT INTO GROUPS VALUES
(10,'I.T.'),
(11,'Sales'),
(12,'Administration'),
(13,'Operations');
CREATE TABLE ROOMS(
roomID INT PRIMARY KEY,
roomName VARCHAR(30) NOT NULL
);
INSERT INTO ROOMS VALUES
(1,'101'),
(2,'102'),
(3,'Auditorium A'),
(4,'Auditorium B');
CREATE TABLE USERS_GROUP(
userID INT,
groupID INT,
PRIMARY KEY( userID, groupID),
FOREIGN KEY (userID) REFERENCES USERS(userID),
FOREIGN KEY (groupID) REFERENCES GROUPS(groupID)
);

INSERT INTO USERS_GROUP VALUES
(1,10),
(2,10),
(3,11),
(4,11),
(5,12);
CREATE TABLE GROUP_ACCESS_ROOMS(
groupID INT,
roomID INT,
PRIMARY KEY( groupID,roomID),
FOREIGN KEY (roomID) REFERENCES ROOMS(roomID),
FOREIGN KEY (groupID) REFERENCES GROUPS(groupID)
);
INSERT INTO GROUP_ACCESS_ROOMS VALUES
(10,1),
(10,2),
(11,2),
(11,3);
QUERIES
Q1)
All groups, and the users in each group. A group should appear even if there are no users
assigned to the group
SELECT g.group_name,u.user_name
FROM groups g LEFT JOIN users_group ug ON g.groupID = ug.groupID LEFT JOIN
users u ON u.userID = ug.userID;

Q2)
All rooms, and the groups assigned to each room. The rooms should appear even if no
groups have been assigned to them
SELECT r.roomName,g.group_name FROM groups g LEFT JOIN group_access_rooms gar ON
g.groupID = gar.groupID
RIGHT JOIN rooms r ON r.roomID = gar.roomID;
Q3)
A list of users, the groups that they belong to, and the rooms to which they are assigned.
This should be sorted alphabetically by user, then by group, then by room.
SELECT DISTINCT user_name, group_name, roomName FROM users u, groups g, rooms r, users_group
ug, group_access_rooms gar
WHERE u.userID = ug.userID AND ug.groupID = gar.groupID AND r.roomID = gar.roomID AND g.groupID
= ug.groupID;