Ask a question from expert

Ask now

Database Design and Development Case | Assignment 1

Creating tables and inserting records in a database for the MIS602 Data Modelling & Database Design subject.

13 Pages2159 Words29 Views
   

Added on  2022-09-01

Database Design and Development Case | Assignment 1

Creating tables and inserting records in a database for the MIS602 Data Modelling & Database Design subject.

   Added on 2022-09-01

BookmarkShareRelated Documents
Running head: ASSIGNMENT 1
ASSIGNMENT 1
Name of the Student:
Name of the University:
Author note
Database Design and Development Case | Assignment 1_1
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
);
Database Design and Development Case | Assignment 1_2
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,
Database Design and Development Case | Assignment 1_3
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:
Database Design and Development Case | Assignment 1_4

End of preview

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

Related Documents
Task 1 CREATE TABLE EMPDB
|7
|1224
|62

Database System : SQL Queries
|14
|2042
|9

Database Modelling Assignment | Task
|11
|1429
|21

Database Design in DBMS Tutorial: Learn Data Modeling
|17
|1795
|26

Assignment on Data Modelling & Database Design
|11
|2081
|24

Database Design and SQL
|9
|1255
|102