Ask a question from expert

Ask now

SQL Database Design for Employee Group Management

15 Pages519 Words397 Views
   

Added on  2019-09-22

About This Document

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
SQL Database Design for Employee Group Management_1
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);
SQL Database Design for Employee Group Management_2
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));
SQL Database Design for Employee Group Management_3
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);EXECUTION SCREEN SHOTS
SQL Database Design for Employee Group Management_4

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Desklib Online Library for Study Material with Solved Assignments, Essays, Dissertation etc
|7
|505
|106

TRA Race Entry System Author: Name + Number.
|7
|471
|85

DATABASE SYSTEMS INFORMATION 2022
|8
|666
|22