Examine the following case from Turner, Weickgenannt, and Copeland (2017):

Arminello, Inc. does not use a database system; rather, it maintains separate data files in each of its departments. When a sale occurs, the transaction is recorded in the sales department. Next, documentation is forwarded from the sales department to the accounting department so that the transaction can be recorded there. Finally, the customer service group is notified so that its records can be updated. (p. 499)

Describe the data redundancy and concurrency issues that are likely to arise under this scenario at Arminello, Inc. Then determine the requirements for the database system and recommend a solution to achieve the desired results. The recommendation must reflect best practices for timeliness and completeness of the information, the controls that need to be in place, and the best platform for the system.

Create short flowcharts for each of the following examples:

1. Data gathered from magnetic disk is displayed on a computer screen and printed out on a report

2. Manually updating the sales journal using the sales invoices

3. Using the employee pay detail report to manually prepare employee payroll checks. The completed payroll checks are then sent to the employees

4. Data from a source document is manually copied into a ledger then stamped to indicate is has been processed. Afterwards, the stamped document is filed in alphabetical order.

5. Customer sales orders are received into the system and processed electronically, then the sales order data is saved to a database. The sales order data is then pulled from the database and used to prepare three copies of the monthly sales report.

Consider the automobile insurance company database we use for examples in class. For each problem, write an SQL statement to retrieve the specified data.

1. For each accident that occurred in 2005, the names of drivers involved and the make and year of the auto they were driving.
2. The name and ssn of automobile owners who have never been the driver in any accident.
3. The name and ssn of people involved in accidents while driving a car they do not own. (Remember that cars may have multiple owners.)

Database Schema:
• People (ssn, fname, lname, city, state)

• Agents (ssn, salary, photo)

• Autos (vin, make, year, agent_ssn)

• Owns (ssn, vin)

• Accidents (aid, accident_date, city, state)

• Involvements(aid, vin, damages, driver_ssn)

Janet, after years of working as a hairdresser and as a makeup artist, decided to go into business for herself. Over time the business has grown into a spa and beauty retreat and she now employs a few staff. With her growing operation she has identified the need to implement a computerized accounting information system. In order to have a system that meets her needs exactly and also to save cost, she has asked you to create a simple, customized solution for her business. Her immediate need is for a customer sales information system, so that is what you will now create. With customer service being so important to Janet, her customers tend to become regulars and she wants to maintain a record of each customer’s contact information and preferences so that the spa will know what is important to the customer without having to be told each visit. The customer information that Janet considers important to maintain is the full name, cellphone number, favourite treatment, and special considerations (e.g. some customers have allergies or require fragrance-free products). For employees, Janet just needs to record the names and cell phone numbers in the database. The spa also has a standard list of services which each have a standard name, description and price. Janet also wants a database record of each customer visit both for customer billing at the time of service, as well as for future analysis. Janet wants to refer to these sales transactions as “Visits” in the database in order to reflect the spa’s focus on customers’ experiences. For each of these customer visits it is important to record a unique visit identifier, the date, the customer, the main staff member who served the customer, and the service or services experienced (sold)

Based on Janet’s requirements, list out the entities and relationships that should be present in the database, indicating the cardinality of each relationship.

QUESTION FOUR

Margaret Kale Ltd has provided you with the latest two Statements of Cash Flow for the business and some additional information from previous and the current Balance Sheets and Income Statements.

QUESTION FOUR CONTINUED

Additional Information:

Required:

1. Calculate the following ratios for each of the two years, round off to two decimal points.                                                                                    (7 x 1 = 7 Marks)

1. Cash flow adequacy ratio (Industry average: 1: 1.90)                     

2. Repayment of long-term borrowings ratio (Industry average: 1: 0.80)  

3. Reinvestment ratio (Industry average: 1: 0.08)                                            

4. Dividend Payment ratio (Industry average: 1: 0.04)                                 

5. Cash flow Return on Assets (Industry average 40%)                      

6. Debt Coverage (Industry average 150%)                              

7. Operations Index (Industry average 90%)                

Comment on the significance of each ratio and its impact on the business in terms of cash flow adequacy, return on assets, debt coverage and operations index over the two years, in comparison with the industry average.                                                                                                                                                                      

Apply specific models developed from economics to demonstrate how domestic and foreign events (e.g., wars, changes in trade barriers, development abroad) have impacted the level of and changes in imports and exports in the United States from 2000 - 2010.

Please make sure to relate the answer to the time period of 2000 - 2010 in the US.

Write the SQL DDL to create the following 5 tables for an App store: Publisher, Category, App, AppVersion, AppVersionReview:

1. A Publisher table where each publisher is identified by an integer id and has a name (up to 40 characters). (1 mark)

2. A Category table where each category has an id (integer), a name (up to 50 characters), and a parentId to identify its parent category. The parentId should be a foreign key to the Category table. (1.5 marks)

3. An App table storing each app that is identified by a field called id that is an integer. Other attributes include name (string up to 40 characters), publisherId (integer), categoryId (integer), and description (string up to 255 characters). Make all foreign keys set to null on delete and no action (generate error) on update.

4. A AppVersion table that stores each version of the app. The primary key is the appId and version (exactly 10 characters). Each release has a releaseDate (DATETIME), an integer rating, a price (up to 10 digits with 2 decimals), and a description (up to 500 characters). Make all foreign keys set to perform cascade on delete and cascade on update.

5. A AppVersionReview table that stores ratings for each application version. The primary key is the appId, version, and reviewer (exactly 20 characters). There is also a reviewDate (DATETIME), rating (int), and review (up to 1000 characters). Make all foreign keys set to cascade on both update and delete. A value for the reviewDate field is always required.

Reversing Digits - Complete the template program in C++ (Be Original)

//  reverseNumber.cpp
// Reverse the digits of a number.
#include <iostream>
using std::cin;
using std::cout;
using std::endl;

#include <iomanip>
using std::setw;

/* Write prototype for reverseDigits */
//   STUDENT WRITES CODE HERE



int main()
{
   int number; // input number

   cout << "Enter a number between 1 and 9999: ";
   cin >> number;

   cout << "The number with its digits reversed is: ";

   // find number with digits reversed
   //   STUDENT WRITES CODE HERE ***************


   cout << /* Write call to reverseDigits */ << endl;

   return 0; // indicate successful termination
} // end main



// reverseDigits returns number obtained by reversing digits of n
/* Write function header for reverseDigits */
//   STUDENT WRITES CODE BELOW ***************


{
   int reverse    = 0;     // reversed number
   int divisor    = 1000;  // current divisor
   int multiplier = 1;     // current multiplier

   // loop until single-digit number
   while ( n > 9 )
   {
      // if n >= current divisor, determine digit
      if ( n >= divisor )
      {
         // update reversed number with current digit
         reverse += n / divisor * multiplier;
         n %= divisor; // update n
         /* Write a line of code that reduces divisor by a factor of 10 */
//   STUDENT WRITES CODE HERE ***************


         /* Write a line of code that increases multiplier by a factor of 10 */
//   STUDENT WRITES CODE HERE ***************


      } // end if
      else // else, no digit
         divisor /= 10; // update divisor
   } // end while

   reverse += n * multiplier;
   return reverse; // return reversed number
} // end function reverseDigits

Note I did not post the city jail database because it was too long and it couldnt let me post, however, there was another question answered with the same database right here, so please use that. It is exactly the same. If not let me know how i can post the database subqueries)

Your files must be in sql script format so that they can be run in SQL Developer.

Using the City Jail database to answer the questions.

Use an sql sub-query statement to answer the following:

1. List the name of each officer who has reported less than the maximum number of crimes officers have reported.
2. List the names of all criminals who have committed more than average number of crimes and aren’t listed as violent offenders.
3. List appeal information for each appeal that has a less than the average number of days between the filing and hearing dates.
4. List the names of probation officers who have had a greater than average number of criminals assigned.
5. List each crime that has had the least number of appeals recorded.

   HINT:

   The following memo was used to create an initial database design (E-R model) for the City Jail that indicates entities, attributes (columns), primary keys, and relationships.

   MEMO

   To: Database Consultant

   From: City Jail Information Director

   Subject: Establishing a Crime-Tracking Database System

   It was a pleasure meeting with you last week. I look forward to working with your company to create a much-needed crime-tracking system. As you requested, our project group has outlined the crime-tracking data needs we anticipate. Our goal is to simplify the process of tracking criminal activity and provide a more efficient mechanism for data analysis and reporting. Please review the data needs outlined below and contact me with any questions.

   Criminals: name, address, phone number, violent offender status (yes/no), probation status (yes/no), and aliases

   Crimes: classification (felony, misdemeanor, other), date charged, appeal status (closed, can appeal, in appeal), hearing date, appeal cutoff date (always 60 days after the hearing date), arresting officers (can be more than one officer), crime codes (such as burglary, forgery, assault; hundreds of codes exist), amount of fine, court fee, amount paid, payment due date, and charge status (pending, guilty, not guilty).

   Sentencing: start date, end date, number of violations (such as not reporting to probation officer), and type of sentence ( jail period, house arrest, probation).

   Appeals: appeal filing date, appeal hearing date, status (pending, approved, and disapproved).

   Note: Each crime case can be appealed up to three times.

   Police officers: name, precinct, badge number, phone contact, status (active/inactive)

   Additional notes: A single crime can involve multiple crime charges, such as burglary and assault. Criminals can be assigned multiple sentences. For example, a criminal might be required to serve a jail sentence followed by a period of probation. Answer each or the questions with an sql statement that contains at least one subquery.

   Hint: Use subqueries, and work “inside out” toward the result; that is, retrieve the

   employee number of N. Smith, search for the codes of all courses he ever taught, and so on.

Explain the significance of gatekeepers or ‘watchdogs.’ Cite several examples. Explain the Federal Sentencing Guidelines for boards. Briefly discuss the conflicts of interests/implications involved in excessive executive compensation. Cite several (2+) credible examples of excessive wage compensation for CEO’s and/or board members. *Use proper APA in-text citations for ALL examples and list your references at the end of your post.