Question

A small company is expanding into new offices spread across three buildings. To keep
track of office furniture, computers, and printers, the company would like a database to
keep track of everything. Each piece of office furniture, computer, or printer is given an
identification number. Each item (which is either a piece of furniture, a computer, or a
printer) is placed in a room of one of three buildings. The building manager is responsible
for the items in their building. The building manager is identified by an employee ID,
first name, last name, and DOB. Draw an ER diagram that will assist in the creation of
this database. After you have constructed your ER diagram, construct your database using
SQL queries on PHPMyAdmin. Lastly, populate the database with dummy data
(https://mockaroo.com/). Submit your ER diagram and screenshots of your queries to
generate the database, in addition to a screenshot of a SELECT query after the database
has been populated, to prove that you have successfully populated the database.

Answer 1

Here you can see the ER Diagram.
Here employee is a manager.
empID is employee id , bgID is the building ID , roomID is the Room ID , itemID is the Item ID, these are the primary keys .
So these are the set of primary keys with which we can able to retrive and find all other tuples form the table uniquely.
here item can be anything like furniture,computer and printer each item is identified by its itemID.

ER-DIAGRAM:

Creating tables in database:

CREATE TABLE Employee(
empID PRIMARYKEY,
fname VARCHAR(20),
lname VARCHAR(20),
dob DATE;
);

CREATE TABLE ITEM(
itemID PRIMARYKEY,
itemdescription VARCHAR(50),
roomID INT NOT NULL
);

CREATE TABLE Building(
bgID PRIMAYRKEY,
bgname VARCHAR(20),
empid INT
);

CREATE TABLE Room(
roomID PRIMARYKEY,
bgID INT,
type VARCHAR(20)
);

Inserting values into tables :

example to insert into table Employee

INSERT INTO Employee(empID,fname,lname,dob) VALUES(1,"justin","beiber","1996-05-20");
INSERT INTO Employee(empID,fname,lname,dob) VALUES(1,"snoop","dog","1975-10-25");
INSERT INTO Employee(empID,fname,lname,dob) VALUES(1,"cristiano","ronaldo","1985-03-11");
INSERT INTO Employee(empID,fname,lname,dob) VALUES(1,"leionel","messi","1990-05-27");

example to insert into table Item

INSERT INTO Item(itemID,itemdescription,roomID) VALUES(100,"bag",50);
INSERT INTO Item(itemID,itemdescription,roomID) VALUES(101,"box",51);
INSERT INTO Item(itemID,itemdescription,roomID) VALUES(102,"bottle",52);
INSERT INTO Item(itemID,itemdescription,roomID) VALUES(103,"laptop",53);

example to insert into table Building

INSERT INTO Building (bgID,bgname,empid) VALUES(23,"rock",50);
INSERT INTO Building (bgID,bgname,empid) VALUES(24,"song1",53);
INSERT INTO Building (bgID,bgname,empid) VALUES(23,"song2",52);
INSERT INTO Building (bgID,bgname,empid) VALUES(23,"rock2",51);

example to insert into table Room

INSERT INTO Room(roomID,bgID,type) VALUES (52,24,"large");
INSERT INTO Room(roomID,bgID,type) VALUES (54,23,"medium");
INSERT INTO Room(roomID,bgID,type) VALUES (51,27,"large");
INSERT INTO Room(roomID,bgID,type) VALUES (53,22,"small");

I hope you find this helpful and don't forget to giver UPVOTE as it means a lot THANKS:)