5. Suppose we are given both an undirected graph G with weighted edges and
a minimum spanning tree T of G .
(a) Describe an algorithm to update the minimum spanning tree when the
weight of a single edge e is decreased.
(b) Describe an algorithm to update the minimum spanning tree when the
weight of a single edge e is increased.
In both cases, the input to your algorithm is the edge e and its new weight;
your algorithms should modify T so that it is still a minimum spanning tree.
[Hint: Consider the cases e ∈ T and e 6∈ T separately.]

Import the northwind database and write sql statements for the following:

1. List all supplier ids (no duplicates) for all products.
2. List the product name and supplier id for all products that cost more than $20.
3. List all products whose names start with letter "c" and are not discontinued.
4. List each country name and the number of suppliers located in the country.
5. List all supplier names, the number of products that each supplier provides (this column should be named as NumOfProducts), and the average price for the products provided by the same supplier (this column should be named as AvgPrice).
6. Find all suppliers' name, city, and country for those who provide non-discontinued products.
7. List all supplier names who provide at least two different products.
8. Add a new product record to the products table. You must provide values for the following fields in the new product record: ProductID, productName, SupplierID, and CategoryID.
9. Increase the reorder level by 20% and reduce the prices by 10% for all products
10. Delete discontinued products.

QUESTION 26

1. For more efficient SQL, replace a series of OR clauses with

   		IN
   		AND
   		NULL
   		DISTINCT

2.5 points   

QUESTION 27

1. SELECT ADD_MONTHS('01-FEB_2016', 3) from dual; results in

   		04-FEB-2016
   		01-MAY-2016
   		31-MAY-2016
   		04-MAY-2016

2.5 points   

QUESTION 28

1. joining a table to itself is refered to as

   		an equijoin
   		a self join
   		an outer join
   		a non equi-join

2.5 points   

QUESTION 29

1. SELECT MIN(NAME) from Customer; will return which of the following rows

   		ALICE
   		WILLIAM
   		BLAKE
   		JAMAL

2.5 points   

QUESTION 30

1. To obtain all the rows from two tables regardless of matches which join should be used

   		RIGHT OUTER
   		LEFT OUTER
   		EQUIJOIN
   		FULL OUTER

2.5 points   

QUESTION 31

1. To sort data returned from a SQL statement, which clause should be used

   		ORDER BY
   		WHERE
   		GROUP BY
   		HAVING

Its a multiple question answer.I donot have any more database

The following selected transactions relate to investment activities of Ornamental Insulation Corporation during 2018. The company buys debt securities, not intending to profit from short-term differences in price and not necessarily to hold debt securities to maturity, but to have them available for sale when circumstances warrant. Ornamental’s fiscal year ends on December 31. No investments were held by Ornamental on December 31, 2017.

(Hint: Interest must be accrued.)

Required:
1. Prepare the appropriate journal entry for each transaction or event during 2018, as well as any adjusting entries necessary at year end. For any sales, prepare entries to update the fair-value adjustment, record any reclassification adjustment, and record the sale.
2. Indicate any amounts that Ornamental Insulation would report in its 2018 income statement, 2018 statement of comprehensive income, and 12/31/2018 balance sheet as a result of these investments.

1Record the acquisition of 7% Distribution Transformers Corporation bonds costing $520,000 at face value.

2

Record the acquisition of 1,080,000 of American Instruments’ 9% bonds at face value.

3

Record the entry for the semiannual interest received on the Distribution Transformers bonds.

4

Record the entry to adjust to fair value on the date of sale of the Distribution Transformers bonds.

5

Record the entry for the reclassification adjustment on the date of sale

6

Record the entry for sale of Distribution Transformers bonds for $557,000.

7

Record the acquisition of $1,560,000 of M&D Corporation 5% bonds at face value.

8

Record the interest accrual for American Instruments bonds.

9

Record the interest accrual for M&D bonds.

10

Record the entry for Fair-value adjustment.

ndicate any amounts that Ornamental Insulation would report in its 2018 income statement, 2018 statement of comprehensive income, and 12/31/2018 balance sheet as a result of these investments. (Amounts to be deducted should be indicated with a minus sign.)

SQL DATABASE

Task 2 [10.5 marks] using the AdditionCollege database

For task 2, we have provided you with the creation script for the AdditionCollege database. Run this script in MySQL Workbench to create the database. You should execute your query solutions to extract the necessary information.

The script is based on the following schematic:

Unit (Unit_code, Staff_id, [UnitName])

Staff (Staff_id, StaffName, Position, Gender)

Taught_by (Unit_code, Staff_id, weekday)

Student (Student_id, Student_name, Address, Gender)

TuteGroup (TuteGroup_code, Unit_code, DayHrCode, Room_Nr)

TuteGroup_List (TuteGroup_code, Student_id)

Notes

• Units are taught between Monday to Friday.
• Staff position should be “Tutor”, “Lecturer” or “Senior Lecturer”.
• Staff and student gender should be “M”, “F”, or “O”.
• Unit codes are 3 letters and then 2 numbers.
• Student and staff names include both first name and surname.
• Day hour code is the Day and time code.
• Tute group codes are the combination of numbers and letters.

Query 1 (1 mark)

Write a query to list the student ID, name, gender of students whose gender is “M”.

Query 2 (1.5 marks)

Write a query to list the staff ID of all staffs that have taught more than two units.

Query 3 (1.5 marks)

Write a query to list the names of all students of the unit with the unit code “TNS01”.

Query 4

Write a query to determine the total number of different students that the staff with staff_id “S1” is teaching.

Query 5

Write a query to list all students’ information who attended Monday classes. Sort the result set with the ascending order of address.

Query 6 (2.5 marks)

Write a query to list all staffs’ information who have taught more than 6 students. Please note that the same student in a different unit will be counted as different students.

Task 3 [3 marks]

Insert (1 mark)

Write an INSERT command to insert the data of a new student into student table. You need to analyze existing student id information to decide this new student’s ID. The name of the new student is ‘Sybil Stark’. The address of the new student ‘302/28 Dengate Lane, St Lucia, QLD 4067’. The new student is a female.

Delete (1 mark)

Write a DELETE command to remove the data of the staff from the staff table, who do not teach any units.

Update (1 mark)

Write an UPDATE comment to change the position of the staff with the last name ‘Rostov’ to ‘Senior Lecturer’.

Task 4 [1.5 marks]

Create Index (0.5 marks)

Currently, the database only contains a small number of records. However, the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimized.

Write a command to create an index on student_name column of the student table.

Create view – 1 mark

Write a command to create a view to list the student ID and student name of students who have classes at room ‘RM123’.

Task 5 [2 marks]

Working as a Database Administrator for MySQL AdditionCollege database, write the following commands for two users, Vanessa and Jessica to achieve the following database security requirements:

1. User Vanessa is no longer allowed to add data to the Staff table (0.5 marks)
2. User Vanessa is no longer allowed to delete records from the Staff table (0.5 marks)
3. User Jessica must be able to add records to the TuteGroup table (0.5 marks)
4. User Jessica must be able to remove records from the TuteGroup table (0.5 marks)

Assume usernames of users namely Vanessa and Jessica are vanessa and jessica respectively.

Task 6 [4 marks]

Hint: Going through the examples of functional dependencies and normalization covered in the Week 6 Lecture will help you to work on this question.

Using the following table structure, identify all functional dependencies and then decompose this table into a set of 3NF relations. Your answer should:

• List the functional dependences
• Convert the relational schema for the relations in 3NF.

additional information

create database AdditionCollege;
use AdditionCollege;

/*table staff */
create table Staff(
staff_id varchar(30) not null,
staffname varchar(60) not null,
position varchar(60) not null,
gender char(1) ,
primary key(staff_id)
);

/* Table Unit*/
create table Unit (
unit_code varchar(30) not null,
staff_id varchar(30) not null,
unitname varchar(60),
primary key(unit_code),
foreign key(staff_id) references staff(staff_id)
);

/*table taught_by*/
Create table Taught_By(
unit_code varchar(30) not null,
staff_id varchar(30) not null,
weekday enum ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') not null,
primary key(unit_code, staff_id),
foreign key(unit_code) references unit(unit_code),
foreign key(staff_id) references staff(staff_id)
);

/*table student*/
create table Student(
student_id varchar(30) not null,
student_name varchar(60) not null,
address varchar(60) not null,
gender char(1) not null,
primary key(student_id)
);

/*table TuteGroup*/
create table TuteGroup(
tuteGroup_code varchar(30) not null,
unit_code varchar(30) not null,
dayHrCode varchar(30) not null,
room_nr varchar(30) not null,
primary key(tuteGroup_code),
foreign key(unit_code) references unit(unit_code),
unique key (dayHrCode, room_nr)
);

/*tutegroup_list table*/

Create table TuteGroup_List(
tuteGroup_code varchar(60) not null,
student_id varchar(30) not null,
primary key (tuteGroup_code, student_id),
foreign key(tuteGroup_code) references tuteGroup(tuteGroup_code)
);

/* Insert into Staff*/
Insert into Staff values ('S1', 'Vladimir Rostov', 'Lecturer', 'M');
Insert into Staff values ('S2', 'Rory Reid', "Tutor", "O");
Insert into Staff values ('S3', 'Andy Murray', "Tutor", "O");
Insert into Staff values ('S4', 'Novak Djokovic', "Tutor", "M");
Insert into Staff values ('S5', 'Chloe Lai', "Senior Lecturer", "F");

/*Insert into Unit */
insert into Unit values ('TNS01', 'S1', 'Database Essentials');
insert into Unit values ('TNS02', 'S2', 'Database Design and Development');
insert into Unit values ('TNS03', 'S3', 'Essential SQL');
insert into Unit values ('TNS04', 'S4', 'Datawarehousing');
insert into Unit values ('TNS05', 'S4', 'All about MySQL');
insert into Unit values ('TNS06', 'S1', 'SQL Query Performance Tuning');
insert into Unit values ('TNS07', 'S2', 'Database Administration and Maintenance');

/* Insert into Taught_By */
insert into Taught_By values ('TNS01', 'S1', 'Monday');
insert into Taught_By values ('TNS02', 'S1', 'Tuesday');
insert into Taught_By values ('TNS02', 'S2', 'Tuesday');
insert into Taught_By values ('TNS03', 'S3', 'Monday');
insert into Taught_By values ('TNS04', 'S4', 'Thursday');
insert into Taught_By values ('TNS05', 'S4', 'Wednesday');
insert into Taught_By values ('TNS06', 'S1', 'Friday');
insert into Taught_By values ('TNS07', 'S2', 'Thursday');

/* Insert into Student */
insert into Student values ('STD01', 'Siobhan Curran', "Lvl19/ 239 George St, Brisbane, QLD 4000" ,"F");
insert into Student values ('STD02', 'Eddie Murphy', "18 Dorchester St, South Brisbane, QLD 4101", "M");
insert into Student values ('STD03', 'Tony Murphy', "252 Wellington Rd, East Brisbane, QLD 4169", "O");
insert into Student values ('STD04', 'Joe Bloggs', "63 Geelong St, East Brisbane, QLD 4169", "M");
insert into Student values ('STD05', 'Janet Volker', "12 Cairns St, East Brisbane, QLD 4169 ", "F");
insert into Student values ('STD06', 'Jason Crawford', "303 Adelaide St, Brisbane, QLD 4000", "M");
insert into Student values ('STD07', 'Zoha Zidane', "48 Montague Rd, South Brisbane, QLD 4101", "F");
insert into Student values ('STD08', 'Victor Lazslo', "23 Wellington St, Brisbane, QLD 4000", "M");
insert into Student values ('STD09', 'Antony Lawrence', "9 Latrobe St, East Brisbane, QLD 4169", "M");
insert into Student values ('STD10', 'Erwin Schroedinger', "5 Blackall Tce, East Brisbane, QLD 4169", "O");

/*Insert into TuteGroup */
insert into TuteGroup values('TG1', 'TNS01', 'Mon 9 AM', 'RM123');
insert into TuteGroup values('TG2', 'TNS02', 'Tue 2 PM', 'RM123');
insert into TuteGroup values('TG3', 'TNS04', 'Thu 4 PM', 'RM889');
insert into TuteGroup values('TG4', 'TNS05', 'Wed 11 AM', 'RM600');
insert into TuteGroup values('TG5', 'TNS03', 'Mon 9 AM', 'RM889');
insert into TuteGroup values('TG6', 'TNS06', 'Fri 2 AM', 'RM600');

/*Insert into TuteGroup_List*/
insert into TuteGroup_List values ('TG1', 'STD01');
insert into TuteGroup_List values ('TG1', 'STD02');
insert into TuteGroup_List values ('TG1', 'STD03');
insert into TuteGroup_List values ('TG1', 'STD09');
insert into TuteGroup_List values ('TG2', 'STD10');
insert into TuteGroup_List values ('TG2', 'STD04');
insert into TuteGroup_List values ('TG2', 'STD07');
insert into TuteGroup_List values ('TG3', 'STD05');
insert into TuteGroup_List values ('TG3', 'STD06');
insert into TuteGroup_List values ('TG3', 'STD08');
insert into TuteGroup_List values ('TG3', 'STD04');
insert into TuteGroup_List values ('TG4', 'STD01');
insert into TuteGroup_List values ('TG4', 'STD09');
insert into TuteGroup_List values ('TG4', 'STD10');
insert into TuteGroup_List values ('TG4', 'STD03');
insert into TuteGroup_List values ('TG5', 'STD02');
insert into TuteGroup_List values ('TG5', 'STD07');
insert into TuteGroup_List values ('TG5', 'STD06');
insert into TuteGroup_List values ('TG5', 'STD10');
insert into TuteGroup_List values ('TG5', 'STD08');
insert into TuteGroup_List values ('TG6', 'STD09');
insert into TuteGroup_List values ('TG6', 'STD05');
insert into TuteGroup_List values ('TG6', 'STD04');
insert into TuteGroup_List values ('TG6', 'STD10');

Baldrick bought an investment property on 1 July 2018 for $2,000,000. He rented out his property from 1 July 2018. He borrowed $1,800,000 on the same day from the bank to buy the property. The term of the loan was 8 years. The building was originally constructed in 2001 at a cost of $900,000. He received rent in cash from his tenants during the year ended 30 June 2019 in the amount of $93,000. Included in this amount was a payment of $9,000 on 30 June 2019 as rent for the month of July 2019. Baldrick incurred the following expenses during the year ended 30 June 2019 in relation to the property:  State Government Land Tax $22,500  Loan Repayments ($52,000 principal and $130,000 interest) $182,000  Real Estate Agent’s fee to evict a tenant for non-payment of rent $2,300  The front window was badly damaged at the time of acquisition and replaced on 4 July 2018 $2,800  A new refrigerator was purchased on 1 March 2019 $3,800  Loan application fee (paid on 1 July 2018) $2,000  Legal costs for the loan to buy the property (paid on 1 July 2018) $2,600  Legal costs for buying the property (paid on 1 July 2018) $4,600  Stamp duty on the purchase of the property (paid on 1 July 2018) $95,000 Baldrick wants to minimise his taxable income for this year. Assume all depreciating assets, if any, have an effective life of 6 years. He does not wish to use the SBE election. Required: Advise Baldrick as to what his taxable income or loss is for the year ended 30 June 2019. You must give reasons for your answer. Your discussion must include an analysis of the pertinent sections of the relevant legislation, rulings and the relevant case law. If relevant, you must show your calculations.

Identify and discuss four important pieces of legislation about local government which were passed at the inception of democratic dispensation.

Please focus on these ones:

1. Local Government: Municipal Systems Act, 2000 (ACT 32 of 2000)

2.Local Government: Municipal Structures Act, 1998 (ACT 117 of 1998)

3. Local Government: Municipal Demarcation Act, 1998 (Act 27 of 1998)

4. Local Government: Municipal Systems Act, 2000 (ACT 32 of 2000)

Daniel Hardware Co. is considering alternative financing arrangements for equipment used in its warehouses. Besides purchasing the equipment outright, Daniel is also considering a lease. Accounting for the outright purchase is fairly straightforward, but because Daniel has not used equipment leases in the past, the accounting staff is less informed about the specific accounting rules for leases. The staff is aware of some general lease rules related to "right-of-use," but they are unsure how the accounting rules apply to their situation. Daniel has asked you to conduct some research on these items related to lease capitalization criteria.

Instructions:

Access the IFRS authoritative literature at the IASB website (http://eifrs.iasb.org/). (Click on the IFRS tab and then register for free eIFRS access if necessary.) When you have accessed the documents, you can use the search tool in your Internet browser to respond to the following questions. (Provide paragraph citations.)

(a) What is included in the measurement of (1) the lease liability and (2) the right-of-use asset?

(b) Besides the non-cancelable term of the lease, what are other considerations in determining the "lease term"?

(c) When should a lessee account for a lease modification? What procedures are followed?

Daniel Hardware Co. is considering alternative financing arrangements for equipment used in its warehouses. Besides purchasing the equipment outright, Daniel is also considering a lease. Accounting for the outright purchase is fairly straightforward, but because Daniel has not used equipment leases in the past, the accounting staff is less informed about the specific accounting rules for leases. The staff is aware of some general lease rules related to "right-of-use," but they are unsure how the accounting rules apply to their situation. Daniel has asked you to conduct some research on these items related to lease capitalization criteria

Instructions:

Access the IFRS authoritative literature at the IASB website (http://eifrs.iasb.org/). (Click on the IFRS tab and then register for free eIFRS access if necessary.) When you have accessed the documents, you can use the search tool in your Internet browser to respond to the following questions. (Provide paragraph citations.)

(a) What is included in the measurement of (1) the lease liability and (2) the right-of-use asset?

(b) Besides the non-cancelable term of the lease, what are other considerations in determining the "lease term"?

(c) When should a lessee account for a lease modification? What procedures are followed?