Ask a question to Desklib · AI bot
Employee Database: ER Diagram, Table Commands, Sample Data, SQL Queries
Added on -2019-09-21
This article provides an Employee Database with ER Diagram, Table Commands, Sample Data, and SQL Queries. It includes Department, Employee, Project, and WorksOn tables. The ER Diagram, CREATE TABLE commands, and sample data are provided. SQL queries include listing employees in alphabetical order of surname, listing female employees, listing managers, listing employees who work for the IT department, listing retiring managers, finding employees managed by James Adams, listing total hours worked by each employee, listing projects with more than two employees, and listing departments with more than 10 employees.
| 15 pages
| 704 words
| 88 views
Trusted by 2+ million users,
1000+ happy students everyday
EMPLOYEE DATABASE Student Name: Student ID: Course Name: Course ID:Faculty Name: University Name:
ContentsA)ER DIAGRAM.....................................................................................................................2B) CREATE TABLE COMMANDS..............................................................................................3Deparment Table.........................................................................................................................3Employee Table...........................................................................................................................3Project Table................................................................................................................................4WorksOn Table............................................................................................................................4C) SCREEN SHOTS OF SAMPLE DATA....................................................................................4SQL QUERIES................................................................................................................................7
A)ER DIAGRAM
B) CREATE TABLE COMMANDSCREATE DATABASE EMP_DB_SYSTEM;USE EMP_DB_SYSTEM;Deparment TableCREATE TABLE Department( deptNo INT PRIMARY KEY, deptName VARCHAR(40) NOT NULL, mgrEmpID INT UNIQUE DEFAULT NULL);Employee TableCREATE TABLE Employee( empID INT PRIMARY KEY, fName VARCHAR(10) NOT NULL, lName VARCHAR(10) NOT NULL, address VARCHAR(100) NOT NULL, dob DATE NOT NULL, sex ENUM('M','F') NOT NULL, position VARCHAR(40) NOT NULL, deptNo INT NOT NULL,FOREIGN KEY (deptNo) REFERENCES Department(deptNo));ALTER TABLE Department ADD FOREIGN KEY (mgrEmpID) REFERENCES Employee(empID);
Found this document preview useful?
You are reading a preview
Upload your documents to download
or
Become a Desklib member to get accesss
Paid Plans
Free Plan
Single Unlock
Monthly Plan
Yearly Plan