Scribd
Upload
3 views1 page

Improved Database Assignment-2

The document outlines a database assignment that includes an ER diagram representing the relationships between Location, Department, and Employee entities. It provides SQL table creation commands along with sample data insertion for each table. Additionally, it includes SELECT queries to retrieve data from the Employee, Department, and Location tables.

Uploaded by

maulidizaharani
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views1 page

Improved Database Assignment-2

The document outlines a database assignment that includes an ER diagram representing the relationships between Location, Department, and Employee entities. It provides SQL table creation commands along with sample data insertion for each table. Additionally, it includes SELECT queries to retrieve data from the Employee, Department, and Location tables.

Uploaded by

maulidizaharani
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DATABASE ASSIGNMENT (IMPROVED WITH ER

DIAGRAM)

ER Diagram:

1 M 1 M
Location Department Employee

SQL Table Creation:


CREATE TABLE Location ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE
Department ( id INT PRIMARY KEY, name VARCHAR(50), location_id INT, FOREIGN KEY
(location_id) REFERENCES Location(id) ); CREATE TABLE Employee ( id INT PRIMARY KEY,
name VARCHAR(50), salary DECIMAL(10,2), dept_id INT, FOREIGN KEY (dept_id)
REFERENCES Department(id) );

Sample Data (INSERT):


-- Locations INSERT INTO Location VALUES (1,'Dar es Salaam'),(2,'Arusha'),(3,'Mwanza'); --
Departments INSERT INTO Department VALUES
(1,'HR',1),(2,'IT',1),(3,'Finance',2),(4,'Sales',3),(5,'Marketing',2); -- Employees INSERT INTO
Employee VALUES (1,'Ali',500000,1),(2,'Asha',600000,2),(3,'John',550000,3),
(4,'Fatma',520000,1),(5,'Peter',700000,2), (6,'Zainab',480000,4),(7,'Hassan',510000,5),
(8,'Neema',530000,3),(9,'David',620000,4),(10,'Halima',590000,5);

SELECT Queries:
SELECT * FROM Employee; SELECT [Link], [Link] FROM Employee e JOIN Department d ON
e.dept_id = [Link]; SELECT [Link], [Link] FROM Department d JOIN Location l ON d.location_id =
[Link];

You might also like