Question

1 Approved Answer

Posted on Sep 25, 2024

NEEDED IN MS SQL / MS ACCESS URGENTLY. THANKS ANSWER ONLY QUESTION 6. Talking: Q.7. (16 pts] Implement a trigger such that: * It will

NEEDED IN MS SQL / MS ACCESS URGENTLY. THANKS

ANSWER ONLY QUESTION 6. image text in transcribed

Talking: Q.7. (16 pts] Implement a trigger such that: * It will be fired whenever there is an update on table Sitelncome. When the update operation takes place, you will update the fields TotalAmountToBePaid and TotalAmountPaid in table Flat for those flats only. Person PersonID int 1 2 7 8 FirstName LastName Birthdate Email Tel nvarchar(50) nvarchar(50) smalldatetime varchar(50) varchar(50) Ali AYDIN 1970-01-01 ali@aydin.com 0532 787 99 88 Hakan YILMAZ 1965-01-01 hakan@gmail.com 0542 444 77 88 Mustafa AGAOGLU 1974-01-01 agaoglum@gmail.com 0532 222 22 22 Mahmut NEYZEN 1944-01-01 mahmut@outlook.com 0555 666 44 11 Flat FlatlD *** Use Site Management database below to write the following SQL queries. Q.5. (16 pts] Retrieve flat ID, the last name of the owner, the percentage of the amount paid to the amount to be paid, and the cluster (Platinum, Gold, Bronze) for the flats which have at least 5 income records (consider the fields AmountToBePaid and AmountPaid in table Sitelncome). If the percentage is greater than 75, the cluster value will be Platinum; if it is between 75 and 50, the cluster will be Gold; and if it is less than 50, then the cluster will be Bronze. Discard redundant data, if any. Q.6. [20 pts] Implement a stored procedure such that: It takes one input parameter: a period (i.e. month). Given a period, calculate the heating payment for the flats, i.e. update the records in table Sitelncome for the given period with the following assumptions: There are totally 44 flats starting from 1 to 44. You may use table Flat. Calculations will be done only for heating expense/income type for the given period. Income/Expense Type will be "Heating". Fixed Cost (FC): For the given period, 30% of the amount (AmountToBePaid) of the site expense will be distributed to the flats according to their areas (AreaM2). Variable Cost (VC): For the given period, 70% of the amount (AmountToBePaid) of the site expense will be distributed to the flats according to their Index differences (LastIndex-FirstIndex). Total Cost, which is the amount to be paid for the flat, is equal to the sum of FC and VC. You have the related records in table Sitelncome Sitelncome before your calculations. For the given period, the fields Income Type, Periodis, FlatID, char(7) FirstIndex, and LastIndex are not null (there are values stored in those fields), and you are going to calculate the field AmountToBePaid. int 1 2 32 41 Ownerld RenterID int int 1 1 2 NULL 8 8 7 7 AreaM2 decimal(7.2) 90.00 85.00 100.00 225.00 TotalAmountToBePaid decimal(10,2) 0.00 0.00 0.00 0.00 TotalAmount Paid decimal(10,2) 0.00 0.00 0.00 0.00 Amount ToBePaid SiteExpense Expense Type PeriodID varchar(15) char(7) Electricity 2016-11 Heating 2016-11 Heating 2016-12 Water 2016-11 decimal(10,2) 3833.70 4500.85 5479.00 2528.00 Payment Due Date AmountPaid smalldatetime decimal(10,2) 2016-12-15 3833.70 2016-12-25 450.85 2017-01-18 NULL 2016-12-20 2528.00 Date Paid smalldatetime 2016-12-15 2016-12-20 NULL 2016-12-20 PeriodID Flatid LastIndex AmountToBePaid Amount Paid DatePaid Income Type varchar(15) Electricity Heating Water 2016-11 2016-12 2016-11 int 2 41 32 FirstIndex decimal(10,2) 81.95 6254.00 83.40 decimal(10,2) 81.95 7215.00 90.50 decimal(10,2) 0.00 154.41 33.59 Payment Due Date smalldatetime 2016-12-10 2017-01-10 2016-12-20 decimal(10,2) 0.00 NULL 33.59 smalldatetime 2016-12-10 NULL 2016-12-19 Talking: Q.7. (16 pts] Implement a trigger such that: * It will be fired whenever there is an update on table Sitelncome. When the update operation takes place, you will update the fields TotalAmountToBePaid and TotalAmountPaid in table Flat for those flats only. Person PersonID int 1 2 7 8 FirstName LastName Birthdate Email Tel nvarchar(50) nvarchar(50) smalldatetime varchar(50) varchar(50) Ali AYDIN 1970-01-01 ali@aydin.com 0532 787 99 88 Hakan YILMAZ 1965-01-01 hakan@gmail.com 0542 444 77 88 Mustafa AGAOGLU 1974-01-01 agaoglum@gmail.com 0532 222 22 22 Mahmut NEYZEN 1944-01-01 mahmut@outlook.com 0555 666 44 11 Flat FlatlD *** Use Site Management database below to write the following SQL queries. Q.5. (16 pts] Retrieve flat ID, the last name of the owner, the percentage of the amount paid to the amount to be paid, and the cluster (Platinum, Gold, Bronze) for the flats which have at least 5 income records (consider the fields AmountToBePaid and AmountPaid in table Sitelncome). If the percentage is greater than 75, the cluster value will be Platinum; if it is between 75 and 50, the cluster will be Gold; and if it is less than 50, then the cluster will be Bronze. Discard redundant data, if any. Q.6. [20 pts] Implement a stored procedure such that: It takes one input parameter: a period (i.e. month). Given a period, calculate the heating payment for the flats, i.e. update the records in table Sitelncome for the given period with the following assumptions: There are totally 44 flats starting from 1 to 44. You may use table Flat. Calculations will be done only for heating expense/income type for the given period. Income/Expense Type will be "Heating". Fixed Cost (FC): For the given period, 30% of the amount (AmountToBePaid) of the site expense will be distributed to the flats according to their areas (AreaM2). Variable Cost (VC): For the given period, 70% of the amount (AmountToBePaid) of the site expense will be distributed to the flats according to their Index differences (LastIndex-FirstIndex). Total Cost, which is the amount to be paid for the flat, is equal to the sum of FC and VC. You have the related records in table Sitelncome Sitelncome before your calculations. For the given period, the fields Income Type, Periodis, FlatID, char(7) FirstIndex, and LastIndex are not null (there are values stored in those fields), and you are going to calculate the field AmountToBePaid. int 1 2 32 41 Ownerld RenterID int int 1 1 2 NULL 8 8 7 7 AreaM2 decimal(7.2) 90.00 85.00 100.00 225.00 TotalAmountToBePaid decimal(10,2) 0.00 0.00 0.00 0.00 TotalAmount Paid decimal(10,2) 0.00 0.00 0.00 0.00 Amount ToBePaid SiteExpense Expense Type PeriodID varchar(15) char(7) Electricity 2016-11 Heating 2016-11 Heating 2016-12 Water 2016-11 decimal(10,2) 3833.70 4500.85 5479.00 2528.00 Payment Due Date AmountPaid smalldatetime decimal(10,2) 2016-12-15 3833.70 2016-12-25 450.85 2017-01-18 NULL 2016-12-20 2528.00 Date Paid smalldatetime 2016-12-15 2016-12-20 NULL 2016-12-20 PeriodID Flatid LastIndex AmountToBePaid Amount Paid DatePaid Income Type varchar(15) Electricity Heating Water 2016-11 2016-12 2016-11 int 2 41 32 FirstIndex decimal(10,2) 81.95 6254.00 83.40 decimal(10,2) 81.95 7215.00 90.50 decimal(10,2) 0.00 154.41 33.59 Payment Due Date smalldatetime 2016-12-10 2017-01-10 2016-12-20 decimal(10,2) 0.00 NULL 33.59 smalldatetime 2016-12-10 NULL 2016-12-19