Reference no: EM131947295

Using the SQL script below, how can I do the following?

a. Modify the database to reflect that two new properties (property # 7 and 8) were added by the renting company and two new renters (renter # 5 and 6) rented these new properties from Nov 1, 2010 [You can make up property and renter details]

b. Show property number, property rent, and renters' name for all the properties that were rented in any part of year 2010.

c. Show rental history (renter number, renter name, and rent start date, end date) of the properties that are currently in rent.

Drop table Property Cascade Constraints;
Drop table Renter Cascade Constraints;
Drop table Renting Cascade Constraints;

Create Table Property (
Pno        Number(4) Not Null,
Street           Char(14),
City       Char(10),
Zip        Char(8),
Rooms      Number(3),
Rent       Number(5,2),
Primary Key(Pno));

Create Table Renter (
Rno        Number(4) Not Null,
FName      Char(10),
LName      Char(10),
Address          Char(40),
Max_Rent   Number(5,2),
Primary key (Rno));

Create Table Renting (
Pno        Number(4) Not Null,
Rno        Number(4) Not Null,
Rent_Start Date,
Rent_End   Date,
Primary key (Pno, Rno, Rent_Start),
Foreign Key(Pno) references Property,
Foreign Key(Rno) references Renter);

Insert into Property values (1,'16 Holhead','Dee','Aberdeen',6,650);
Insert into Property values (2,'6 Argyll St','Kilburn','London',4,400);
Insert into Property values (3,'6 Lawrence St','Partick','Glasgow',3,350);
Insert into Property values (4,'2 Manor Rd',NULL,'Glasgow',3,375);
Insert into Property values (5,'18 Dale Rd','Hyndland','Glasgow',5,600);
Insert into Property values (6,'5 Novar Dr','Hyndland','Glasgow',4,450);

Insert into Renter values(1,'John','Kay','56 High St, Putney, 
London',425);
Insert into Renter values(2,'Aline','Stewart','64 Fern Dr, Pollock, 
Glasgow',450);
Insert into Renter values(3,'Mike','Ritchie','18 Tain St, Gourock',750);
Insert into Renter values(4,'Mary','Treager','5 Tarbot Rd, Kildary, 
Agerdeen',600);

Insert into Renting values(1,4,'01-JAN-2005','30-DEC-2006');
Insert into Renting values(2,1,'01-FEB-2005','30-JUL-2006');
Insert into Renting values(3,2,'15-JAN-2006','30-DEC-2007');
Insert into Renting values(4,2,'01-JAN-2004','30-DEC-2006');
Insert into Renting values(1,3,'06-JUN-2007',NULL);
Insert into Renting values(3,4,'01-JAN-2007','30-DEC-2007');
Insert into Renting values(4,1,'01-JAN-2008',NULL);
Insert into Renting values(5,2,'01-MAR-2007',NULL);
Insert into Renting values(6,4,'01-JUL-2008','15-OCT-2010');

Insert into Renting values(6,3,'01-NOV-2010',NULL);
COMMIT;