This article explains the SQL database design for employee group management with ERD, tables, execution screenshots, all tables list, and queries. It includes an ERD diagram, tables with insert commands, execution screenshots, all tables list, and three queries. The queries include all groups and the users in each group, all rooms and the groups assigned to each room, and a list of users, the groups that they belong to, and the rooms to which they are assigned. The article is relevant for students studying database management, SQL, and related courses in universities and colleges.
SQL Database Design for Employee Group Management
Added on 2019-09-22
BookmarkShareRelated Documents
ContentsERD.............................................................................................................................................................2TABLES with insert commands..................................................................................................................2EXECUTION SCREEN SHOTS.........................................................................................................................4All tables list............................................................................................................................................8QUERIES..................................................................................................................................................12Q1).........................................................................................................................................................12Q2).........................................................................................................................................................14Q3).........................................................................................................................................................14
ERDTABLES with insert commandsCREATE 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));