Database Design and Development Case | Assignment 1

Running head: ASSIGNMENT 1
ASSIGNMENT 1
Name of the Student:
Name of the University:
Author note

1
ASSIGNMENT 1
Task 1
I have developed the database using sql queries. For the first I have been asked to create
three tables with the ERD as provided below:
At first I created the department table as with the help of the following SQL queries:
CREATE TABLE `department` (
`Department_ID` int(11) NOT NULL,
`DeptName` varchar(30) NOT NULL,
`DeptLocation` varchar(20) NOT NULL,
`DeptFloor` varchar(30) NOT NULL
);

2
ASSIGNMENT 1
I created the department table before the employee table as the employee table is linked
directly with the department table and hence, the primary was required to be set in the
department table before it can be used in the employee table. After completion of the department
table the employee table has been created with the flowing command:
CREATE TABLE `employee` (
`Employee_ID` int(11) NOT NULL,
`EmpName` varchar(25) NOT NULL,
`ManagerID` int(11) NOT NULL,
`DateOfHire` date NOT NULL,
`JobName` varchar(15) NOT NULL,
`Salary` decimal(10,2) NOT NULL,
`Department_ID` int(11) NOT NULL,
`DOB` date NOT NULL,
`Address` varchar(30) NOT NULL
) ;
The salary table was developed by me with the following command.
CREATE TABLE `salary` (
`Salary_Level` int(11) NOT NULL,
`SalaryMin` int(11) NOT NULL,

3
ASSIGNMENT 1
`SalaryMax` int(11) NOT NULL
) ;
After the three tables were developed I decided to create the primary keys for each of the
table and the following commands were used for creating the primary keys:
ALTER TABLE `department`
ADD PRIMARY KEY (`Department_ID`);
ALTER TABLE `employee`
ADD PRIMARY KEY (`Employee_ID`),
ADD KEY `Department_ID` (`Department_ID`);
ALTER TABLE `salary`
ADD PRIMARY KEY (`Salary_Level`);
Now, that all the primary keys are set I would like to form a reference in between the
employee table and department table. Therefore, I developed a foreign key for the employee
table by using the following commands:
ALTER TABLE `employee`
ADD CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`Department_ID`) REFERENCES
`department` (`Department_ID`);
The following database has been developed: