You have been named the network administrator for a large insurance firm. Your responsibility is to come up with a plan for the network infrastructure, which includes number of servers, network security devices, desktops, laptops and handheld devices. The plan should be carefully crafted to show all the necessary details in deploying the infrastructure (LANs, firewalls, routers, switches, IDS and sensors, servers, database servers, and addressing scheme) including specifics about the configurations. An understanding of the security issues the company is facing in order to conduct its business has to be reflected in the security aspects of the infrastructure (you should justify why a security measure is taken and how it will affect the enterprise business). The insurance firm has ten offices (satellite sites) located in ten different cities around United States. There is the headquarters which is located close to two of the other offices. The enterprise network consists of a main site, a backup site and ten satellite sites. All the satellite sites are connected to the main site as individual networks. The satellite sites are dealing with different specialized type of insurances such as house insurance (3 sites), auto insurance (4 sites) and commercial insurance (3 sites). The sites need to have access to the central database of the firm besides their own databases. Each of the sites has roughly one hundred employees except for the headquarters which has 350. Ten percent of the employees are agents who are actively going out in the field for claims or client recruitment. Given the nature of the business, all the employees require internet access. Email services are required as well. The company uses customized software to interface to the databases and has a web interface for the customer to interact with the company. Each side of the business has its own database, however, the management, which is located at the headquarters, must have access to all the information. The Human Resources department has its own database and so does the payroll department. Each of these departments has a staff of 15 people. Your plans should contain diagrams that describe the networks at the satelite sites, at the headquarters and how they are interconnected, including the backup site. The CTO of the firm has also decided to use virtualization and asked you to look into it. You are supposed to give a detailed plan regarding which portions of the network have to be targeted for virtualization and why. You are also supposed to lay off a plan on how to proceed with this technology and show how it would affect the infrastructure that you have previously designed without using virtualization.

Grayson is in the 24 percent tax rate bracket and has sold the following stocks in 2018: (Loss amounts should be indicated by a minus sign.) Description Date Purchased Basis Date Sold Amount Realized Stock A 1/23/1994 $ 7,650 7/22/2018 $ 4,820 Stock B 4/10/2018 14,800 9/13/2018 18,490 Stock C 8/23/2016 11,750 10/12/2018 16,660 Stock D 5/19/2008 5,550 10/12/2018 13,000 Stock E 8/20/2018 7,580 11/14/2018 3,700 a. What is Grayson’s net short-term capital gain or loss from these transactions?

This is the database

CREATE TABLE AIRCRAFT (
AC_NUMBER varchar(5) primary key,
MOD_CODE varchar(10),
AC_TTAF double,
AC_TTEL double,
AC_TTER double
);

INSERT INTO AIRCRAFT VALUES('1484P','PA23-250',1833.1,1833.1,101.8);
INSERT INTO AIRCRAFT VALUES('2289L','DC-90A',4243.8,768.9,1123.4);
INSERT INTO AIRCRAFT VALUES('2778V','MA23-350',7992.9,1513.1,789.5);
INSERT INTO AIRCRAFT VALUES('4278Y','PA31-950',2147.3,622.1,243.2);

/* -- */

CREATE TABLE CHARTER (
CHAR_TRIP int primary key,
CHAR_DATE date,
AC_NUMBER varchar(5),
CHAR_DESTINATION varchar(3),
CHAR_DISTANCE double,
CHAR_HOURS_FLOWN double,
CHAR_HOURS_WAIT double,
CHAR_TOT_CHG double,
CHAR_OIL_QTS int,
CUS_CODE int,
foreign key (AC_NUMBER) references AIRCRAFT(AC_NUMBER)
);

INSERT INTO CHARTER VALUES(10001,'2008-02-05','2289L','ATL',936,5.1,2.2,354.1,1,10011);
INSERT INTO CHARTER VALUES(10002,'2008-02-05','2778V','BNA',320,1.6,0,72.6,0,10016);
INSERT INTO CHARTER VALUES(10003,'2008-02-05','4278Y','GNV',1574,7.8,0,339.8,2,10014);
INSERT INTO CHARTER VALUES(10004,'2008-02-06','1484P','STL',472,2.9,4.9,97.2,1,10019);
INSERT INTO CHARTER VALUES(10005,'2008-02-06','2289L','ATL',1023,5.7,3.5,397.7,2,10011);
INSERT INTO CHARTER VALUES(10006,'2008-02-06','4278Y','STL',472,2.6,5.2,117.1,0,10017);
INSERT INTO CHARTER VALUES(10007,'2008-02-06','2778V','GNV',1574,7.9,0,348.4,2,10012);
INSERT INTO CHARTER VALUES(10008,'2008-02-07','1484P','TYS',644,4.1,0,140.6,1,10014);
INSERT INTO CHARTER VALUES(10009,'2008-02-07','2289L','GNV',1574,6.6,23.4,459.9,0,10017);
INSERT INTO CHARTER VALUES(10010,'2008-02-07','4278Y','ATL',998,6.2,3.2,279.7,0,10016);
INSERT INTO CHARTER VALUES(10011,'2008-02-07','1484P','BNA',352,1.9,5.3,66.4,1,10012);
INSERT INTO CHARTER VALUES(10012,'2008-02-08','2778V','MOB',884,4.8,4.2,215.1,0,10010);
INSERT INTO CHARTER VALUES(10013,'2008-02-08','4278Y','TYS',644,3.9,4.5,174.3,1,10011);
INSERT INTO CHARTER VALUES(10014,'2008-02-09','4278Y','ATL',936,6.1,2.1,302.6,0,10017);
INSERT INTO CHARTER VALUES(10015,'2008-02-09','2289L','GNV',1645,6.7,0,459.5,2,10016);
INSERT INTO CHARTER VALUES(10016,'2008-02-09','2778V','MQY',312,1.5,0,67.2,0,10011);
INSERT INTO CHARTER VALUES(10017,'2008-02-10','1484P','STL',508,3.1,0,105.5,0,10014);
INSERT INTO CHARTER VALUES(10018,'2008-02-10','4278Y','TYS',644,3.8,4.5,167.4,0,10017);

/* -- */

CREATE TABLE CREW (
CHAR_TRIP int,
EMP_NUM int,
CREW_JOB varchar(20),
primary key (CHAR_TRIP, EMP_NUM)
);

INSERT INTO CREW VALUES(10001,104,'Pilot');
INSERT INTO CREW VALUES(10002,101,'Pilot');
INSERT INTO CREW VALUES(10003,105,'Pilot');
INSERT INTO CREW VALUES(10003,109,'Copilot');
INSERT INTO CREW VALUES(10004,106,'Pilot');
INSERT INTO CREW VALUES(10005,101,'Pilot');
INSERT INTO CREW VALUES(10006,109,'Pilot');
INSERT INTO CREW VALUES(10007,104,'Pilot');
INSERT INTO CREW VALUES(10007,105,'Copilot');
INSERT INTO CREW VALUES(10008,106,'Pilot');
INSERT INTO CREW VALUES(10009,105,'Pilot');
INSERT INTO CREW VALUES(10010,108,'Pilot');
INSERT INTO CREW VALUES(10011,101,'Pilot');
INSERT INTO CREW VALUES(10011,104,'Copilot');
INSERT INTO CREW VALUES(10012,101,'Pilot');
INSERT INTO CREW VALUES(10013,105,'Pilot');
INSERT INTO CREW VALUES(10014,106,'Pilot');
INSERT INTO CREW VALUES(10015,101,'Copilot');
INSERT INTO CREW VALUES(10015,104,'Pilot');
INSERT INTO CREW VALUES(10016,105,'Copilot');
INSERT INTO CREW VALUES(10016,109,'Pilot');
INSERT INTO CREW VALUES(10017,101,'Pilot');
INSERT INTO CREW VALUES(10018,104,'Copilot');
INSERT INTO CREW VALUES(10018,105,'Pilot');

/* -- */

CREATE TABLE AC_CUSTOMER (
CUS_CODE int primary key,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE double
);

INSERT INTO AC_CUSTOMER VALUES(10010,'Ramas','Alfred','A','615','844-2573',0);
INSERT INTO AC_CUSTOMER VALUES(10011,'Dunne','Leona','K','713','894-1238',0);
INSERT INTO AC_CUSTOMER VALUES(10012,'Smith','Kathy','W','615','894-2285',896.53);
INSERT INTO AC_CUSTOMER VALUES(10013,'Olowski','Paul','F','615','894-2180',1285.18);
INSERT INTO AC_CUSTOMER VALUES(10014,'Orlando','Myron',NULL,'615','222-1672',673.21);
INSERT INTO AC_CUSTOMER VALUES(10015,'O''Brian','Amy','B','713','442-3381',1014.55);
INSERT INTO AC_CUSTOMER VALUES(10016,'Brown','James','G','615','297-1228',0);
INSERT INTO AC_CUSTOMER VALUES(10017,'Williams','George','','615','290-2556',0);
INSERT INTO AC_CUSTOMER VALUES(10018,'Farriss','Anne','G','713','382-7185',0);
INSERT INTO AC_CUSTOMER VALUES(10019,'Smith','Olette','K','615','297-3809',453.9);

/* -- */

CREATE TABLE EARNEDRATING (
EMP_NUM int,
RTG_CODE varchar(5),
EARNRTG_DATE date,
primary key (emp_num, rtg_code)
);
INSERT INTO EARNEDRATING VALUES(101,'CFI','1998-02-18');
INSERT INTO EARNEDRATING VALUES(101,'CFII','2005-12-15');
INSERT INTO EARNEDRATING VALUES(101,'INSTR','1993-11-08');
INSERT INTO EARNEDRATING VALUES(101,'MEL','1994-06-23');
INSERT INTO EARNEDRATING VALUES(101,'SEL','1993-04-21');
INSERT INTO EARNEDRATING VALUES(104,'INSTR','1996-07-15');
INSERT INTO EARNEDRATING VALUES(104,'MEL','1997-01-09');
INSERT INTO EARNEDRATING VALUES(104,'SEL','1995-03-12');
INSERT INTO EARNEDRATING VALUES(105,'CFI','1997-11-18');
INSERT INTO EARNEDRATING VALUES(105,'INSTR','1995-04-17');
INSERT INTO EARNEDRATING VALUES(105,'MEL','1995-08-12');
INSERT INTO EARNEDRATING VALUES(105,'SEL','1994-09-23');
INSERT INTO EARNEDRATING VALUES(106,'INSTR','1995-12-20');
INSERT INTO EARNEDRATING VALUES(106,'MEL','1996-04-02');
INSERT INTO EARNEDRATING VALUES(106,'SEL','1994-03-10');
INSERT INTO EARNEDRATING VALUES(109,'CFI','1998-11-05');
INSERT INTO EARNEDRATING VALUES(109,'CFII','2003-06-21');
INSERT INTO EARNEDRATING VALUES(109,'INSTR','1996-07-23');
INSERT INTO EARNEDRATING VALUES(109,'MEL','1997-03-15');
INSERT INTO EARNEDRATING VALUES(109,'SEL','1996-02-05');
INSERT INTO EARNEDRATING VALUES(109,'SES','1996-05-12');

/* -- */

CREATE TABLE EMPLOYEE (
EMP_NUM int primary key,
EMP_TITLE varchar(4),
EMP_LNAME varchar(15),
EMP_FNAME varchar(15),
EMP_INITIAL varchar(1),
EMP_DOB date,
EMP_HIRE_DATE date
);
INSERT INTO EMPLOYEE VALUES(100,'Mr.','Kolmycz','George','D','1942-06-15','1987-03-15');
INSERT INTO EMPLOYEE VALUES(101,'Ms.','Lewis','Rhonda','G','1965-03-19','1988-04-25');
INSERT INTO EMPLOYEE VALUES(102,'Mr.','VanDam','Rhett','','1958-11-14','1992-12-20');
INSERT INTO EMPLOYEE VALUES(103,'Ms.','Jones','Anne','M','1974-10-16','2005-08-28');
INSERT INTO EMPLOYEE VALUES(104,'Mr.','Lange','John','P','1971-11-08','1996-10-20');
INSERT INTO EMPLOYEE VALUES(105,'Mr.','Williams','Robert','D','1975-03-14','2006-01-08');
INSERT INTO EMPLOYEE VALUES(106,'Mrs.','Duzak','Jeanine','K','1968-02-12','1991-01-05');
INSERT INTO EMPLOYEE VALUES(107,'Mr.','Diante','Jorge','D','1974-08-21','1996-07-02');
INSERT INTO EMPLOYEE VALUES(108,'Mr.','Wiesenbach','Paul','R','1966-02-14','1994-11-18');
INSERT INTO EMPLOYEE VALUES(109,'Ms.','Travis','Elizabeth','K','1961-06-18','1991-04-14');
INSERT INTO EMPLOYEE VALUES(110,'Mrs.','Genkazi','Leighla','W','1970-05-19','1992-12-01');

/* -- */

CREATE TABLE MODEL (
MOD_CODE varchar(10) primary key,
MOD_MANUFACTURER varchar(15),
MOD_NAME varchar(20),
MOD_SEATS double,
MOD_CHG_MILE double
);
INSERT INTO MODEL VALUES('DC-90A','Beechcraft','KingAir',8,2.67);
INSERT INTO MODEL VALUES('MA23-250','Piper','Aztec',6,1.92);
INSERT INTO MODEL VALUES('PA31-950','Piper','Navajo Chieftain',10,2.34);

/* -- */

CREATE TABLE PILOT (
EMP_NUM int primary key,
PIL_LICENSE varchar(25),
PIL_RATINGS varchar(25),
PIL_MED_TYPE varchar(1),
PIL_MED_DATE date,
PIL_PT135_DATE date
);
INSERT INTO PILOT VALUES(101,'ATP','SEL/MEL/Instr/CFII','1','2008-04-12','2007-05-16');
INSERT INTO PILOT VALUES(104,'ATP','SEL/MEL/Instr','1','2007-06-10','2008-03-23');
INSERT INTO PILOT VALUES(105,'COM','SEL/MEL/Instr/CFI','2','2008-02-25','2008-02-12');
INSERT INTO PILOT VALUES(106,'COM','SEL/MEL/Instr','2','2008-04-02','2007-12-24');
INSERT INTO PILOT VALUES(109,'COM','SEL/MEL/SES/Instr/CFII','1','2008-04-14','2008-04-21');

/* -- */

CREATE TABLE RATING (
RTG_CODE varchar(5) primary key,
RTG_NAME varchar(50)
);
INSERT INTO RATING VALUES('CFI','Certified Flight Instructor');
INSERT INTO RATING VALUES('CFII','Certified Flight Instructor, Instrument');
INSERT INTO RATING VALUES('INSTR','Instrument');
INSERT INTO RATING VALUES('MEL','Multiengine Land');
INSERT INTO RATING VALUES('SEL','Single Engine, Land');
INSERT INTO RATING VALUES('SES','Single Engine, Sea');

Answer the following questions based on the above database:

1. (4 Points) Create a view named “ATL_CHARTERS_V” that includes the following “For each charter of aircraft to Atlanta (STL), print the charter date, charter hours flown, and the corresponding customer last name, first name, area code, and phone number”. Your answer should include both the SQL statement for view creating along with the contents of the view (you get the contents of the view by select * from ATL-CHARTERS_V).

2. (4 Points) Modify the MODEL table to add the following attribute: (Note: use ALTER TABLE and UPDATE commands for this question.)

Once the attribute has been added, update the values of all rows as per following values:

3. (4 Points) Create a trigger named trg_charter_hours that will automatically update the AIRCRAFT table after a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values.

The meaning for the AIRCRAFT table columns are as follows:

AC_TTAF: Total time on the air frame

AC_TTEL: Total time on the left engine (Also used to record single engine hours) AC_TTER: Total time on the right engine.

So in the trigger, you need to increase all of them with value of CHAR_HOURS_FLOWN

4. (4 Points) Create a trigger named trg_cust_balance that will automatically update the AC_CUSTOMER table’s CUS_BALANCE before a new CHARTER row is added. Use the CHARTER table’s CHAR_TOT_CHG as the update source (Assume that all charter charges are charged to the customer balance.) In addition to the CHAR_TOT_CHG, add $25 for every quart of oil

used on the charter.

5. (4 Points) Create a stored procedure to update model charge per mile attribute. Procedure takes the model number as a parameter. The procedure increases the charge for this model by 25%.

6. (4 Points) Create a stored procedure that will take an Employee number and percentage, then update the corresponding employee’s hourly salary by the input percentage (increase the hourly salary, so you are giving the employee a raise).

Hint: Alter Employee table to add the hourly_salary field, update it with a value of 30 for all rows in the table, before creating the procedure.

When preparing its financial statements at the end of 2018, Bestway Retail Inc. discovered an error in accounting for inventory.

When Bestway started to purchase merchandise from a new supplier, it expensed all transportation costs rather than capitalizing them as a cost of the inventory. It estimated that a portion of the transportation costs were erroneously expensed in 2017 and 2018. Transportation costs were $327,000 and $334,000 in fiscal 2017 and 2018, respectively. The company expensed all of the transportation costs in the year incurred, when it should have capitalized a portion of the costs as ending inventory. Bestway determined that 90% of the inventory purchases in 2017 was sold in 2017 and 92% of the inventory purchased in 2018 was sold in 2018. Assume all inventory on hand at the beginning of the year is sold during the year. Assume no tax implications.

Requirement A.

Would net income have been higher or lower in 2017, and by how much? What will Besway report as net income in 2018?

In 2017, net income would have been higher or lower by $__________.

In 2018, Bestway will report net income as $_________.

Requirement B.

What would Bestway report as its inventory and retained earning balances at the beginning of 2018 and at December 31, 2018? (Round intermediary calculations and final answers to the nearest whole dollar.)

At the beginning of 2018, Bestway would report inventory as $_________.

At the end of 2018, Bestway would report inventory at $________.

At the beginning of 2018, Bestway would report retained earnings as $_______.

At the end of 2018, Bestway would report retained earnings as $_________.

Requirement C.

Prepare the retained earnings portions of the statement of stockholders equity for 2018. (use a minus sign or parentheses for any amounts to be subtracted.)

Stockholders' Equity- Retained Earnings

Requirement D.

What is the necessary journal entry to record the prior-period adjustment in 2018?

The total population of the United States was 151, 325, 798 in 1950; it increased to 281,421,906 in 2000 and to 308,745,538 in 2010.

Find the percent change in population of the US from 1950 to 2010.

Between March 2000 and July 2002 the stock market lost almost 3.5 trillion of vaule and yet consumers spending was still high. Why was there no negative wealth effect?

You are a manager of a monopoly, and your demand and cost functions are given by P =220 -2Q and C(Q) = 2000 + 10 Q2 What Price maximizes your profit