Penny Investment:

On July 1, 2018, you were awarded a penny. If this penny is doubled every day for 31 days;

1. what will be the value of your investment on July 8, 2018?

2. what will be the value of your investment on July 15, 2018?

3. what will be the value of your investment on July 18, 2018?

4. what will be the value of your investment on July 28, 2018?

5. what will be the value of your investment on July 31, 2018?

Lisa’s Darwin Home

Lisa sold her home in Darwin (contract date September 2019, settlement December 2019), receiving $1,220,000 at settlement. This is after legal fees ($12,000), advertising ($2,000) and real estate commissions ($25,000) were deducted. Records indicate that Lisa purchased the property in 2002 (contract date January, settlement March) for $653,000. Legal fees, commissions and advertising of $8,000 were also incurred. Lisa moved in within 6 months, selling her former residence during that time. Over the ownership period, Lisa rented the property for three years beginning December 2010, with $65,000 of $120,000 in non-capital costs claimed against rental income. The property was valued at $890,000 at the time it began being rented. Sculpture Lisa gave a sculpture, valued at $18,900, to her friend in June 2020.

The sculpture was purchased for $480 in December 2000 and repaired in March 2016 for $1,250.

Vase

When Lisa was playing with her cat in September 2019, the cat accidentally knocked over and broke a vase given to her by her grandmother in September 2018 (worth $6,100 at that time). The vase dated back to the Australian gold rush (circa 1850's) and, after undertaking some research, she discovered it was currently worth approximately $27,000. Lisa did not have insurance for the item. Cryptocurrency Lisa converted cryptocurrency into $27,200 Australian dollars in October 2019. To complete the transaction, she incurred $950 in transaction fees. Therefore, Lisa received $26,250 in cash. Lisa had acquired the cryptocurrency in September 2018 for $9,200 Australian dollars.

Shares Lisa sold

shares she held in a construction company in March 2020 for $182,000. She had purchased the shares for $37,200 in December 1986. Lisa has indicated that she has carried forward losses from prior years of $180,000 relating to a prior disposal of shares and land. We will have a meeting first thing Monday morning, so please complete your analysis by the end of Friday so I can review her circumstances over the weekend.

Required: You are required to calculate Lisa’s Net Capital Gain (loss) for the year ending 30 June 2020 based on the above information provided. In doing so, you must present an accurate and complete analysis.

Qa) Determine the taxable capital gain (loss) on the sale of the home. Briefly justify your answer/show all workings.

Qb) Determine the taxable capital gain (loss) on the sale of the Sculpture. Briefly justify your answer/show all workings

Qc) Determine the taxable capital gain (loss) on the sale of the Vase. Briefly justify your answer/show all workings

Qd) Determine the taxable capital gain (loss) on the sale of the Cryptocurrency. Briefly justify your answer/show all workings.

Qe) Determine the capital gain on the sale of the Shares. Briefly justify your answer/show all workings A.6 Determine the Net Capital Gain and/or Loss for Lisa. Briefly justify your answer/show all workings.

Following is an extract from the database of a construction company. The table shows the height of walls in feet and the cost of raising them. The estimated simple linear regression equation is given as Ŷ = b0 + b1X. (Hint: Use Excel functions).

1) What is the value of the coefficient b1?

A) 254.8371

B) 0.010697

C) -2.14625

D) 86.81704

2) What is the estimated cost of raising a 10 foot wall?

A) 1123.008

B) 1505.786

C) 1103.578

D) 968.6109

According to UNCTAD database, France ranks as the 6th largest in the world imports with a 3.40% of the world total. Thus, it can be considered that France has a strong bargaining power in the world markets. As a result, a tariff will _________ increase its national welfare.

A) Never

B) Always

C) Sometimes

D) Only when tariff equalizes the world prices to France's domestic prices

E) None of the above is correct

tableA is a table in a relational database with a composite prime key consisting of fields F1 and F2. You have determined that tableA is 1NF. There are four other fields in tableA. Two of them, F10 and F12, are functionally determined by F1. The other two, F50 and F55, are functionally determined by F2. Because all fields are functionally determined by at least a portion of the key, is tableA 2NF?   If you believe the table is not yet 2NF, what would be required to make it 2NF? Explain your answer

Go to the WCU Library and search the database for the following article. Read the article, and then answer the questions that follow:

Soriano S., Alonso-Magdalena P., Garcıa-Arevalo M., Novials A., & Muhammed S. J. (2012). Rapid insulinotropic action of low doses of Bisphenol-A on mouse and human islets of langerhans: Role of estrogen receptor b. PLoS ONE 7(2): e31109. doi:10.1371/journal.pone.0031109

1. What are the causes of type 2 diabetes?
2. What is the relationship between insulin resistance and hyperinsulinemia?
3. What are endocrine disrupters and what are their functions in our body? Provide some examples.
4. How does concentration of glucose effect insulin secretion?
5. Explain how data from article support or refute the hypothesis that endocrine disrupters can change insulin secretion.

QUESTION 1

1. Which of the following statement is CORRECT about normalisation?

   		A database that is not in at least third normal form cannot be implemented with MySQL.
   		A database that is only in first normal form would breach the CAP theorem.
   		Normalisation ensures consistent formatting, for example, that all the phone numbers would have the same number of digits.
   		All of the above.
   		None of the above.

0.5 points   

QUESTION 2

1. Complete the four blanks in the following SQL query to retrieve all information from the table Customers where birthYear is greater than (or equal to) 1970 but less than (or equal to) 2005. Your answer will be marked incorrect if there is a spelling mistake, missing spaces, or unnecessary spaces.

   SELECT
   FROM
   WHERE  BETWEEN ;

1 points   

QUESTION 3

1. When normalising from first to third normal form, we would typically have:

   		more relations
   		less relations
   		more data stored
   		less data stored
   		none of the above

0.5 points   

QUESTION 4

1. Complete the table below by matching each definition with the appropriate SQL constraint.

   -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A mandatory clause that is at the start of all SQL retrieval queries       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A clause used to return only the values in a result table that are unique       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A clause used to filter rows according to some condition or predicate       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A clause that is used to list the tables and any joins required in a query       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    An operator that allows multiple values to be specified in a comparison statement       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    An operator used to assign an alias to a table or column       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    If used, it must be the last clause in a query       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    Allows rows which have the same value for one or more columns to be grouped together and treated as a unit when using aggregate functions       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A clause which is used to filter aggregate values in the final result table       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    An operator used to create a table with all rows from two tables

   A. IN B. ON C. UNION D. AS E. FROM F. SELECT G. WHERE H. SELECT DISTINCT I. NATURAL JOIN J. ORDER BY K. GROUP BY L. LIKE M. INNER JOIN N. HAVING

This refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script.

Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. Please test your SQL statements in Workbench

1.       Using an INNER JOIN, select the order_id, order_date, shipped_date, fname, and customer_phone from the orders and customers tables. The fname is a column alias and should be formatted as “Smith, John” (last name, first name). Result table should be in order of order_id in ascending order.

2.       Write a SELECT statement that returns these columns using a JOIN of three tables:

a.       order_date from the orders table

b.       shipped_date from the orders table

c.       order_qty from the order_details table

d.       title from the items table

e.       artist from the items table

f.        unit_price from the items table

g.       Result table should be in order of order_date in ascending order

3.       Expand the SELECT statement from the previous question to include the customer last name and first initial from the customers table. The name should be formatted as “Lastname, F.” and given the column alias cname.

Order table is making by yourself. Please do as soon as possible. Please use the tidy tools for SQL proper format.

Data Manipulation

In this lab, you will be manipulating the database to add, delete and modify the values in the database. Please use a "select * from..." after each query to show the effects of your data manipulation query.

1. The title 'Time Flies' now has a new track, the 11th track 'Spring', which is 150 seconds long and has only a MP3 file. Insert the new track into Tracks table (Don’t hand-code any data for insert that can be looked up from the Titles table).

2. Create a new table called Members2 with the same fields as the Members table. (Use DESCRIBE to check if Members2 table is created).

3. Populate Members2 with the content of the Members table.

4. The area code for Columbus, Ohio has been changed from 277 to 899. Update the homephone and workphone numbers of all members in Members2 table accordingly.

5. Delete all members who work for the artist 'Sonata' from Members2 table.

The following are harder questions. They have to do with either multiple joins on tables, multi insert queries, or using the CASE keywords.

6. Members Doug Finney and Terry Irving are forming a new artist to be called "Doug and Terry." Add this record to the Artists table, using ArtistID 13, the address information of Doug Finney, no web address, today's entry date, and no lead source. Don’t hand-code any data for insert that can be looked up from the Members table.

7. Add the appropriate new records to the XrefArtistsMembers table for the artist "Doug and Terry". Doug is the responsible party. Don’t hand-code any data for insert that can be looked up from the Members table. (This will use the CASE keyword to get it to work. Go back to the Functions ppt to see an example of how CASE works)

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
        ArtistID int, 
        ArtistName varchar (50) NOT NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        EntryDate date NULL ,
        LeadSource varchar (10) NULL 
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
        Genre varchar (15)  
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
        MemberID int ,
        FirstName varchar (25) NULL ,
        LastName varchar (25) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        HomePhone varchar (16) NULL ,
        WorkPhone varchar (16) NULL ,
        EMail varchar (40) NULL ,
        Gender char (1) NULL ,
        Birthday date NULL ,
        SalesID smallint NULL 
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);


CREATE TABLE SalesPeople (
        SalesID smallint ,
        FirstName varchar (20) NOT NULL ,
        LastName varchar (20) NOT NULL ,
        Initials varchar (3) NULL ,
        Base decimal(5,2) NULL,
        Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);      


CREATE TABLE Studios (
        StudioID int,
        StudioName varchar (40) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        Contact varchar (50) NULL ,
        EMail varchar (40) NULL ,
        Phone varchar (16) NULL ,
        SalesID smallint NULL 
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);


CREATE TABLE Titles (
        TitleID int ,
        ArtistID int NULL ,
        Title varchar (50) NULL ,
        StudioID int NULL ,
        UPC varchar (13) NULL ,
        Genre varchar (15) NULL 
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');


CREATE TABLE Tracks (
        TitleID int NOT NULL ,
        TrackNum smallint NOT NULL ,
        TrackTitle varchar (50) NULL ,
        LengthSeconds smallint NULL ,
        MP3 smallint NULL ,
        RealAud smallint NULL 
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);


CREATE TABLE XrefArtistsMembers (
        MemberID int NOT NULL ,
        ArtistID int NOT NULL ,
        RespParty smallint NOT NULL 
       );

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;