Reference no: EM132691205

MIS602 Data Modelling & Database Design - Laureate International Universities

Task 1: Create a schema called "BuildingGo" and then write DDL queries to create the three tables with relevant keys as suggested in the above diagram and the sample data shown in the tables.
Query: create schema BuildingGo;
use BuildingGo;
Create Tables: create table Building(
Building_ID int,
Building_name varchar(20),
Built_year int,
Building_Capacity int,
Building_Address varchar(25),
primary key (Building_ID)
);
create table Owner(
Owner_ID int,
Owner_Fname varchar(10),
Owner_Lname varchar(7),
Owner_email varchar(20),
Owner_Phone varchar(8),
primary key (Owner_ID)
);
create table Apartment(
Apartment_ID int,
Total_rooms int,
Building_ID int,
Apartment_rent decimal(4,2),
primary key (Apartment_ID),
foreign key (Building_ID) references Building(Building_ID)
);
Task2: Write queries to insert 5 records into Building and Owner each.
insert into Building values(1001,'Lilli Pilli',1995,5000,'Wakefield street');
insert into Building values(1002,'Early Settler',2006,2000,'Flinders street');
insert into Building values(1003,'Horizon East',2018,5000,'Maldives street');
insert into Building values(1004,'Ocean Blue',2020,15000,'Wakefield street');
insert into Building values(1005,'Calm Place',2007,7000,'Ward street');
insert into Owner values(2001,'Hazel','Alex','[email protected]',04023466);
insert into Owner values(2002,'Saber','Khan','[email protected]',04013567);
insert into Owner values(2003,'Adam','Smith','[email protected]',04015748);
insert into Owner values(2004,'Lisa','Owen','[email protected]',04069874);
insert into Owner values(2005,'Heinz','Elex','[email protected]',04074569);
select * from Owner;
Task3: Write queries to insert 10 records into the Apartment table.
insert into Apartment values(1001,500.10,2,1001,2001);
insert into Apartment values(1002,600,3,1001,2002);
insert into Apartment values(1003,1000,2,1001,2001);
insert into Apartment values(1004,389,2,1001,2001);
insert into Apartment values(1005,400,2,1001,2001);
insert into Apartment values(1006,590.50,2,1001,2001);
insert into Apartment values(1007,345,2,1001,2001);
insert into Apartment values(1008,789,2,1001,2001);
insert into Apartment values(1009,900,2,1001,2001);
insert into Apartment values(1010,500,2,1001,2001);
Task 4: Write a query to display all the information about the buildings in the Building table.
Query: select * from Building;

Task 5: Write a query to display the building names of all buildings in the Building table.
Query: select building_name from Building;

Task 6: Write a query to display all the building names and their capacity.
Query: select building_name,building_capacity from Building;

Task 7: Write a query to update the Building_Capacity of ‘Lilly Pilly' to 2000 people.
Query: update Building set building_capacity = 2000 where building_name= 'Lilli Pilli';
select * from Building;

Task 8: Write a query to display the Building_ID and Building_Name of all the buildings with a capacity of above 3000 people.
Query: select Building_ID, Building_Name from Building where building_capacity>3000;

Task 9: Write a query to increase the rent of all apartments by 2% for all the apartments of Ocean Blue.

update Apartment set
Apartment_rent = Apartment_rent + (Apartment_rent * 0.02)
where Building_ID = 1004 ;

Task10: Write a query to display all the details of the apartments owned by Owner_ID ‘2003'.
Query: select * from Apartment where Owner_ID = 2003;

Task11: Write a query to display all the unique Building_Locations.
Query: select distinct building_address from Building;

Task12: Write a query to display Building_Name and Built_Year for all buildings built in 2001.
Query: select Building_name, Built_year from Building where Built_year = 2001;

Task13: Write a query to display the list of all the Builidng_Names with Buiding_capacity in the range of 1000 - 2000 people in descending order.
Query: select Building_name, Building_capacity from Building
where Building_capacity between 1000 AND 2000 order by Building_capacity DESC;

Task14: Write a query to display the total number of apartments in the Apartment table.
Query: select count(Apartment_ID) from Apartment;

Task15: Write a query to display the Owner_ID and the total number of apartments owned by each owner in ascending order.
select Owner_ID ,count(Apartment_ID) from Apartment
group by Owner_ID order by Owner_ID ASC;

Task16: Write a query to delete the record of the owners whose Owner_Fname contains the word ‘James'.
Query: delete from Owner where Owner_Fname = 'James';

Taskk17: Write a query to display all the apartments owned by the Owner ‘Hazel' as the Owner_Fname.
select * from Apartment A join Owner O
on A.Owner_ID = O.Owner_ID
where owner_Fname = 'Hazel';

Task18: Write a query to display all the apartment details and their corresponding Building_Names.
select A.Apartment_ID,A.Apartment_rent,A.Total_rooms,A.Owner_ID,A.Building_ID,
B.Building_name from A Apartment join B Building
on A.Building_ID = B.Building_ID;

Task19. Write a query to display all the apartment details in Building ‘Ocean Blue'.
select * from Apartment A join Building B
on A.Building_ID = B.Building_ID
where Building_name = 'Ocean Blue';

Task20 Write a query to display all the building names having more than 5 apartments
along with total count of apartments for each building displayed.
select building_name,count(*) from building
b inner join apartment a
ON A.building_id = B.building_id;

Task21 Write a query to display Owner_ID and Owner_Fname of all the owners who do not own any apartments.
select o.Owner_ID, o.Owner_Fname from Owner o
left join apartment a
on o.owner_id = a.owner_id
where Apartment_ID is null;

Task22: Write a query to display the building name, which has the apartment with the lowest weekly rent.
select building_name,min(apartment_rent) from
building b left join apartment a
on b.building_id = a.building_id;

Task23: Write a query to display all the Apartment_IDs, Apartment_Rent and their owner names of the apartments, which has a rent greater than 600 per week ordered in descending order by owner name.
select a.Apartment_ID,a.Apartment_rent, o.Owner_Fname from
Owner o left join apartment a
on o.owner_id = a.owner_id
where Apartment_rent>600 order by o.owner_Fname desc;

Attachment:- Data Modelling & Database Design.rar