*/

GO

PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’

PRINT ‘Read the questions below and insert your queries where prompted.  When  you are finished,

you should be able to run the file as a script to execute all answers sequentially (without errors!)’ + CHAR(10)

PRINT ‘Queries should be well-formatted.  SQL is not case-sensitive, but it is good form to

capitalize keywords and to capitalize table names as they appear in the database; you should also put 

each projected column on its own line and use indentation for neatness.  Example:

   SELECT Name,

          CustomerID

   FROM   CUSTOMER

   WHERE  CustomerID < 106;

All SQL statements should end in a semicolon.  Whatever format you choose for your queries, make

sure that it is readable and consistent.’ + CHAR(10)

PRINT ‘Be sure to remove the double-dash comment indicator when you insert your code!’;

PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’ + CHAR(10) + CHAR(10)

GO

GO

PRINT ‘CIS 275, Lab Week 4, Question 1  [3pts possible]:

Popular Genres

————–

We will start with the IMDB database.

For each genre, show the total number of shows that are listed in that genre. Format genre as

15 characters wide. Order in descending order of popularity.

Correct results will have 28 rows and will look like this:

Genre           Count

————— ———–

Drama           1183422

Comedy          1049517

Short           670864

Documentary     499078

Talk-Show       452645

Romance         398779

Family          339665

News            338804

Animation       255212

Reality-TV      232633

…

Western         25128

War             20984

Film-Noir       852

‘ + CHAR(10)

GO

USE IMDB

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 2  [3pts possible]:

M*A*S*H

——-

For each season of M*A*S*H, show the total number of episodes and total number of votes.

Display results ordered by season. Write your query to match the primaryTitle in title_basics

instead of hard-coding a tconst value.

Hint: title_episode.parentTconst is the series, title_episode.tconst is the episode.

Episodes have ratings in title_ratings, where the number of votes for the episode is also contained.

Correct results will look like this:

Season Number Number of Episodes Total Votes by Season

————- —————— ———————

1             24                 8470

2             24                 6724

3             24                 6383

4             24                 5859

5             24                 5546

6             24                 5092

7             25                 5362

8             25                 5340

9             20                 4177

10            21                 4049

11            16                 5849

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 3  [3pts possible]:

Again Popular Genres

——————–

Repeat the query from Question 1, but this time express the popularity as a percentage of the total number 

of shows. Format the percentage to two decimal places and add a % sign to the end.

Hint: Start by adding the Total column to the SELECT clause. You”ll need to use a windowed function.

If you use … OVER () that will window over the entire contents of the table. Once you have that working,

the percent is 100 * the expression that gives you Count / the expression that gives you Total. Use STR

to convert that to 6 characters with two digits after the decimal point, then add a % to the end.

Hint 2: Percent is a reserved keyword in SQL, so you”ll need to quote it if you want to use it as a column name.

Correct results will have 28 rows and look like this:

Genre           Count       Total       Percent

————— ———– ———– ——-

Drama           1183422     7297619      16.22%

Comedy          1049517     7297619      14.38%

Short           670864      7297619       9.19%

Documentary     499078      7297619       6.84%

Talk-Show       452645      7297619       6.20%

Romance         398779      7297619       5.46%

Family          339665      7297619       4.65%

News            338804      7297619       4.64%

Animation       255212      7297619       3.50%

Reality-TV      232633      7297619       3.19%

…

Western         25128       7297619       0.34%

War             20984       7297619       0.29%

Film-Noir       852         7297619       0.01%

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 4 [3pts possible]:

Metaphone with the most Variants

——————————–

We”re going to switch over to the NAMES database for the next few queries.

Produce a report that shows the most popular metaphones for baby names. Include two rows

for each metaphone, one for F babies and one for M babies. For each metaphone, show the

total number of names that match that metaphone, and the total number of babies that were

given those names. Order in descending order by total number of babies, and display the

top 10 results.

Format Metaphone as 10 characters wide.

For practice, do not use the all_data view. Write the correct JOIN instead.

Correct results will look like this:

Gender Metaphone  Total Names Total Babies

—— ———- ———– —————————————

M      JN         3565        5910583

M      JMS        403         4859289

F      MR         3499        4835115

M      RBRT       410         4755683

M      MXL        1520        4641296

M      WLM        604         3746134

M      TFT        606         3547986

F      TN         10004       3276887

F      JN         9945        3216270

M      RXRT       435         2516670

‘ + CHAR(10)

GO

USE NAMES

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 5  [3pts possible]:

What about John?

—————-

For the most popular combination of Metaphone with Gender (JN and M), show a list of the most popular names

that match the metaphone.

Name       Total Babies

———- —————————————

John       4712974

Juan       331865

Johnny     305889

Jon        164821

Gene       124121

Johnnie    93336

Jonah      54315

Jean       22359

Jan        21534

Johnie     17265

Gino       11250

Gianni     8071

Joan       5594

…

Giani      227

Jony       216

Jauan      190

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 6  [3pts possible]:

Year-by-Year Popularity for JN

——————————

This time, show a breakdown of the popularity of JN names for M babies.

Your results should contain 100 rows that look like this:

Metaphone  Year                                    Total Babies

———- ————————————— —————————————

JN         1915                                    50983

JN         1916                                    53675

JN         1917                                    55877

JN         1918                                    61148

JN         1919                                    58457

JN         1920                                    62319

JN         1921                                    63521

JN         1922                                    62980

JN         1923                                    63395

JN         1924                                    65348

…

JN         2012                                    21925

JN         2013                                    21402

JN         2014                                    21125

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 7  [3pts possible]:

Corey in the House

——————

For all years where the total number of both M and F babies named “Corey” was 100 or more,

show the name (formatted as 10 characters wide) along with the year and the total number

of babies with that name. Display results in chronological order.

Correct results will have 62 rows formatted as:

Name       Year                                    Total Babies

———- ————————————— —————————————

Corey      1952                                    117

Corey      1954                                    161

Corey      1955                                    271

Corey      1956                                    313

Corey      1957                                    285

Corey      1958                                    336

Corey      1959                                    351

Corey      1960                                    347

Corey      1961                                    450

Corey      1962                                    485

Corey      1963                                    484

Corey      1964                                    501

Corey      1965                                    544

Corey      1966                                    513

Corey      1967                                    512

Corey      1968                                    1668

Corey      1969                                    5069

…

Corey      2012                                    895

Corey      2013                                    889

Corey      2014                                    818

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 8  [3pts possible]:

Popular Genres on TV

——————–

JOIN the SHOW table to the SCHEDULE table.

For each genre, calculate the total minutes spent airing shows in that genre.

Let”s switch to the TV database for the last three problems. Then, calculate the total

percent of time dedicated to that genre. Show the genre formatted to 20 characters wide,

the total number of minutes, and the percentage of minutes. Only include rows where

the total number of minutes was 1000 or more. Display in descending order by total minutes.

In the case of ties, order alphabetically by genre.

Hint: Review the hints and your answer to Question 3. Your calculation of the percentage

will be similar here.

Hint 2: Use DATEDIFF(mi, StartTime, EndTime) to get the total number of minutes that a show

was on. Total minutes will be the sum of those values for all the shows in a particular genre.

Correct results will have 88 rows that look like this:

Genre                Total Minutes Percent

——————– ————- ——-

Special              1257851        14.33%

Sports non-event     959388         10.93%

Reality              680033          7.75%

Children             412604          4.70%

Sitcom               393738          4.49%

Drama                377146          4.30%

Shopping             375642          4.28%

Sports event         362470          4.13%

Comedy               282608          3.22%

Crime drama          247615          2.82%

…

Baseball             1200            0.01%

Collectibles         1200            0.01%

Rodeo                1200            0.01%

Medical              1100            0.01%

‘ + CHAR(10)

GO

USE TV

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 9  [3pts possible]:

0.4% of TV is SpongeBob SquarePants

———————————–

Do the same thing as in the previous query, except group by SeriesNum instead of Genre. This time, only show

series where the total minutes is 10,000 or more. Display the Title formatted to be 20 characters wide instead

of the genre.

Add another column for the total number of episodes aired.

Correct results will have 98 rows and look like this:

Title                Total Minutes Total Episodes Percent

——————– ————- ————– ——-

MLB Extra Innings    258240        658             10.06%

Paid Programming     218507        7292             8.51%

NBA League Pass      103200        215              4.02%

MLS Direct Kick      100620        221              3.92%

College Football     73575         421              2.86%

MLB Baseball         69510         390              2.71%

Programa Pagado      54940         1792             2.14%

SIGN OFF             43025         119              1.68%

SportsCenter         36580         596              1.42%

Public Affairs Event 24362         93               0.95%

To Be Announced      23770         161              0.93%

2017 U.S. Open Tenni 23520         98               0.92%

Law & Order          22727         379              0.88%

Forensic Files       22140         738              0.86%

…

SpongeBob SquarePant 10295         353              0.40%

Politics and Public  10186         60               0.40%

SEC Now              10080         174              0.39%

Keeping Up With the  10080         172              0.39%

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT ‘CIS 275, Lab Week 4, Question 10  [3pts possible]:

Everything is Repeats

———————

For the entire schedule, calculate the total number of distinct shows aired and the

total number of shows aired. The difference in these two numbers is the total number of

repeats. Show all three values as a single row, along with the percentage of shows that

are repeats.

Note: If you look in the messages pane, you might see a warning like the following:

Warning: Null value is eliminated by an aggregate or other SET operation.

Eliminate this warning by only including rows where FK_ShowID isn”t NULL.

(The results say that 75% of TV is repeats, but it”s even worse than that, because we”re 

counting the first airing of a show in this 2 week period as an original broadcast, even 

though many of them are repeats from previous weeks).

Correct results should look like this:

Distinct Shows Total Shows Repeats     Percent of Repeats

————– ———– ———– ——————

35183          138286      103103      74.56%

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

————————————————————————————-

— This is an anonymous program block. DO NOT CHANGE OR DELETE.

————————————————————————————-

BEGIN

    PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’;

    PRINT ‘ End of CIS275 Lab Week 4’ + REPLICATE(‘ ‘,50) + CONVERT(CHAR(12),GETDATE(),101);

    PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’;

END;

Faster Computing was impressed with your presentation. The company is interested in moving forward with the project, but the senior management team has responded to the presentation with the following questions and concerns:

(12.3.2: Describe the implementation of controls.)

• How will security be implemented in the Linux systems—both workstations and servers?

(10.1.2: Gather project requirements to meet stakeholder needs.)

• End users have expressed some concern about completing their day-to-day tasks on Linux. How would activities such as web browsing work? How would they work with their previous Microsoft Office files?

(12.4.1: Document how IT controls are monitored.)

• The current Windows administrators are unsure about administering Linux systems. How are common tasks, such as process monitoring and management, handled in Linux? How does logging work? Do we have event logs like we do in Windows?

(2.3.2: Incorporate relevant evidence to support the position.)

• Some folks in IT raised questions about the Linux flavor that was recommended. They would like to see comparisons between your recommendation and a couple of other popular options. What makes your recommendation the best option?

(10.1.3: Define the specifications of the required technologies.)

• How does software installation work on Linux? Can we use existing Windows software?
• How can Linux work together with the systems that will continue to run Windows? How will we share files between the different system types?

The deliverable for this phase of the project is a memo. There is no minimum or maximum page requirement, but all of the questions must be fully answered with sufficient detail. The recommended format is to respond to the questions in a bulleted format. Provide sufficient detail to fully address the questions. You must cite at least two quality sources.

(1.2.3: Explain specialized terms or concepts to facilitate audience comprehension.)

Create a memorandum template with a header of your own design or choosing, brief introduction, addresses Faster Computing, Inc’s questions, and summarizes your position on adopting the specific version of Linux.

(1.4.3: Write concise and logical sentences in standard academic English that clarify relationships among concepts and ideas.)

Your memorandum should not include spelling or grammatical errors. Any Linux commands must be displayed in lower case. Information technology acronyms (e.g., SSH or FTP) should be explained for the reader.

 

How Will My Work Be Evaluated?

In writing a clear and concise memo in response to senior management queries, you are demonstrating your communication skills, technical expertise, and responsiveness to customer/client needs and concerns.  

The following evaluation criteria aligned to the competencies will be used to grade your assignment:

• 1.2.3: Explain specialized terms or concepts to facilitate audience comprehension. 
• 1.4.3: Write concise and logical sentences in standard academic English that clarify relationships among concepts and ideas. 
• 2.3.2: Incorporate relevant evidence to support the position. 
• 10.1.2: Gather project requirements to meet stakeholder needs. 
• 10.1.3: Define the specifications of the required technologies. 
• 12.3.2: Describe the implementation of controls. 
• 12.4.1: Document how IT controls are monitored. 

  

Deliverables

The project is divided into several parts. Details for each deliverable can be found in this document. Refer to the course Syllabus for submission dates.

§ Project Part 1: Network Design

§ Project Part 2: Firewall Selection and Placement

§ Project Part 3: Remote Access and VPNs

§ Project Part 4: Final Network Design Report

    

1. Consider the following downtimes (in minutes) in the painting department of a manufacturing plant. 

 

37.2 10.9 69.8 2.7 73.7 14.6 14.8 29.1 10.1 45.4 99.5 16.0 30.2 41.3 61.0 2.6 26.1 20.1 71.2 7.7 

3.9 1.6 17.3 30.8 24.3 61.0 24.7 15.4 26.1 

6.9 7.4 71.2 54.7 9.7 99.5 4.5 20.8 30.2 21.2 31.3 2.6 70.4 7.5 20.1 6.9 32.6 7.4 39.0 43.3 9.7 

 

a. Use the chi-square test to see if the exponential distribution is a good fit to this sample data at the significance level a 1⁄4 0:05. 

b. Using Arena’s Input Analyzer, find the best fit to the data. For what range of significance levels a can the fit be accepted? (Hint: revisit the concept of p-value in Section 3.11.) 

2. Consider the following data for the monthly number of stoppages (due to failures or any other reason) in the assembly line of an automotive assembly plant. 

12 14 26 13 19 15 18 17 14 10 11 11 14 18 13 

9 20 19 9 12 17 20 25 18 12 16 20 20 14 11 

Apply the chi-square test to the sample data of stoppages to test the hypothesis that the underlying distribution is Poisson at significance level a 1⁄4 0:05. 

3. Let {xi} be a data set of observations (sample). 

1. Show that for the distribution Unif(a, b), the maximal likelihood estimates of
   the parameters a and b are ^a 1⁄4 minfxig and ^b 1⁄4 maxfxig.
    
2. What would be the estimates of a and b obtained using the method of
   moments?
    
3. Demonstrate the difference between the two sets of estimates for a and b using
   a data set of 500 observations from a uniform distribution generated by the Arena Input Analyzer.
    

  

Input Analysis 139 

4. The Revised New Brunswick Savings bank has three tellers serving customers in their Highland Park branch. Customers queue up FIFO in a single combined queue for all three tellers. The following data represent observed iid interarrival times (in minutes). 

 

2.3 4.7 1.8 4.9 2.7 1.0 0.1 0.7 5.5 6.6 3.0 2.0 1.8 4.1 0.1 1.7 4.7 4.7 2.5 0.7 

0.2 2.1 1.6 1.0 1.6 1.0 1.9 0.5 0.5 3.0 3.6 0.6 1.1 0.3 1.4 2.8 1.4 2.1 0.2 4.7 0.5 1.3 0.5 2.2 0.5 2.6 2.9 0.3 0.1 1.2 

 

The service times are iid exponentially distributed with a mean of 5 minutes. 

1. Fit an exponential distribution to the interarrival time data in the previous table. Then, construct an Arena model for the teller queue at the bank. Run the model for an 8-hour day and estimate the expected customer delay in the
   teller’s queue.
    
2. Fit a uniform distribution to the interarrival time data above. Repeat the
   simulation run with the new interarrival time distribution.
    
3. Discuss the differences between the results of the scenarios of 4.a and 4.b. What do you think is the reason for the difference between the corresponding
   mean delay estimates?
    
4. Rerun the scenario of 4.a with uniformly distributed service times, and com-
   pare to the results of 4.a and 4.b. Use MLE to obtain the parameters of the
   service time distribution.
    
5. Try to deduce general rules from the results of 4.a, 4.b, and 4.d on how
   variability in the arrival process or the service process affects queue performance measures.
    

 

1. Open the EmergencyProcedures-02.docx start file. If the document opens in Protected View, click the Enable Editing button so you can modify it.
2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
3. Change the theme to Integral and the theme color to Red.
4. Change the top, bottom, left, and right margins to 0.75″.
5. Select the entire document and change the font size to 12 pt.
6. Format the title of the document.
   1. Select the title of the document and apply Heading 1 style.
   2. Open the Font dialog box, apply All caps effect, and change the font size to 16 pt.
   3. Change the Before paragraph spacing to 0 pt.
   4. Add a bottom border to the title using the Borders drop-down list.
7. Apply and modify the Heading 2 style and delete blank lines.
   1. Apply the Heading 2 style to each of the bold section headings.
   2. Select the first section heading (“Emergency Telephones [Blue Phones]”).
   3. Change Before paragraph spacing to 12 pt. and After paragraph spacing to 3 pt.
   4. Apply small caps effect.
   5. Update Heading 2 style to match selection. All the section headings are updated.
   6. Turn on Show/Hide and delete all the blank lines in the document.
8. Select the bulleted list in the first section and change it to a numbered list.
9. Apply numbering format and formatting changes, and use the Format Painter.
   1. Apply numbering to the text below the section headings in the following sections: “Assaults, Fights, or Emotional Disturbances”; “Power Failure”; “Fire”; “Earthquake”; and “Bomb Threat.”
   2. Select the numbered list in the “Bomb Threat” section.
   3. Open the Paragraph dialog box, set Before and After paragraph spacing to 2 pt., deselect the Don’t add space between paragraphs of the same style check box, and click OK to close the dialog box.
   4. Use the Format Painter to copy this numbering format to each of the other numbered lists.
   5. Reset each numbered list so it begins with 1 (right-click the first item in each numbered list and select Restart at 1 from the context menu).
10. Customize a bulleted list and use the Format Painter.
    1. Select the text in the “Accident or Medical Emergency” section.
    2. Create a custom bulleted list and use a double right-pointing triangle symbol (Webdings, Character code 56).
    3. Open the Paragraph dialog box and confirm the left indent is 0.25″ and hanging indent is 0.25″. If not, change the settings.
    4. Set Before and After paragraph spacing to 2 pt. and deselect the Don’t add space between paragraphs of the same style check box.
    5. Use the Format Painter to apply this bulleted list format to the following text in the following sections: “Tips to Professors and Staff” and “Response to Students.”
11. Change indent and paragraph spacing and apply a style.
    1. Select the text below the “Emergency Telephone Locations” heading.
    2. Set a 0.25″ left indent.
    3. Set Before and After paragraph spacing to 2 pt.
    4. Confirm the Don’t add space between paragraphs of the same style box is unchecked (Paragraph dialog box).
    5. Apply Book Title style to each of the telephone locations in the “Emergency Telephone Locations” section. Select only the location, not the text in parentheses or following text.
12. Change left indent and paragraph spacing and set a tab stop with a dot leader.
    1. Select the text below the “Emergency Phone Numbers” heading.
    2. Open the Paragraph dialog box and set a 0.25″ left indent for this text.
    3. Set Before and After paragraph spacing to 2 pt. and confirm the Don’t add space between paragraphs of the same style box is unchecked.
    4. Open the Tabs dialog box, set a right tab stop at 7″, and use a dot leader (2).
    5. Press Tab before the phone number (after the space) on each of these lines. The phone numbers align at the right margin with a dot leader between the text and phone number.
13. Apply the Intense Reference style to the paragraph headings in the “Accident or Medical Emergency” section (“Life-Threating Emergencies” and “Minor Emergencies”). Include the colon when selecting the paragraph headings.
14. Use the Replace feature to replace all instances of “Phone 911” with “CALL 911” with bold font style. Note: If previous Find or Replace criteria displays in the Replace dialog box, remove this content before performing this instruction.
15. Insert a footer with document property fields and the current date that appears on every page.
    1. Edit the footer on the first page and use the ruler to move the center tab stop to 3.5″ and the right tab stop to 7″.
    2. Insert the Title document property field on the left. Use the right arrow key to deselect the document property field.
    3. Tab to the center tab stop and insert the Company document property field at center. Use the right arrow key to deselect the document property field.
    4. Tab to the right tab stop, insert (not type) the date (use January 1, 2020 format), and set it to update automatically.
    5. Change the font size of all the text in the footer to 10 pt.
    6. Add a top border to the text in the footer using the Borders drop-down list and close the footer.
16. Use the Borders and Shading dialog box to insert a page border on the entire document.
    1. Use Shadow setting and solid line style.
    2. Select the fifth color in the first row of the Theme Colors (Dark Red, Accent 1) and 1 pt. line width.
17. Center the entire document vertically (Hint: use the Page Setup dialog box).
18. View the document in Side to Side page movement view [View tab, Page Movement group] and then return to Vertical page movement view.
19. Save and close the document (Figure 2-119).
20. Upload and save your project file.
21. Submit project for grading.