Database Design in DBMS Tutorial: Learn Data Modeling

Running head: DATA MODELLING & DATABASE DESIGN
DATA MODELLING & DATABASE DESIGN
Name of the Student
Name of the University
Author Note

DATA MODELLING & DATABASE DESIGN
1 Task 1: Creating three tables with relevant keys as suggested in the above diagram:
Creating Department Table:
CREATE TABLE department (
department_id int(11) NOT NULL,
Deptname varchar(30) NOT NULL,
deptLocation varchar(20) NOT NULL,
deptFloor varchar(20) NOT NULL,
PRIMARY KEY (department_id)
);

DATA MODELLING & DATABASE DESIGN
2
Creating Employee Table:
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,
PRIMARY KEY (employee_id),
CONSTRAINT dept_fk FOREIGN KEY (Department_id) REFERENCES
Department(Department_id)
);

DATA MODELLING & DATABASE DESIGN
3
Creating Salary Table:
CREATE TABLE salary(
salary_level int(11) NOT NULL,
salarymin int(11) NOT NULL,
salarymax int(11) NOT NULL,
PRIMARY KEY (salary_level));
Task 2: Insert record of 10 employees in the employee table: