You are hired to design a database for a fitness center. As the fitness center is expanding with more than one branch, they want to create a database to keep track of its customers, facilities and employees. Each branch has a unique id and address (building number, street, district, and city). A branch may have more than one facility (e.g. swimming pool, spa, etc.). Each facility must belong to only one branch, and the information for a facility is name and fees. In addition, each fitness branch offers different classes (such as Yoga, Pilates, Zumba, etc.). All classes should be led by at most one trainer, who is an employee.

Employees must work at one and only one branch. Each employee has an id, name, address (building number, street, district, and city) and can have multiple phone numbers. An employee can only be an admin, managing the facility or working as a clerk, or a trainer. An employee cannot be a trainer and an admin because the trainer id compensated an hourly wage while an admin staff paid a monthly salary. Trainers can train individual customers at different time sessions.

A customer must register at one branch. Each customer has a membership number, name (stored as first name and last name), email, and only one phone number. A customer can join more than one class or uses any number of facilities. A customer may also decide to be trained by at most one personal trainer.


Given the above description: 1. Draw an ERD for the database, identifying the following: a. All the entities, attributes and relationships b. Primary key and (discriminator in weak entity, if any). Don’t forget each entity has to have PK. c. Participation and cardinality constraints. (Explain your choices for two constraints- i.e. identify the words the guided your decision) d. Specialization and completeness constraints (if there is an ISA relationship).

2. Write a schema for two entities and two relationships of your choice. Remember, sometimes a relationship is better not be represented in a separate schema; if this is the case with your chosen relationship explain what you will do.

You are hired to design a database for a fitness center. As the fitness center is expanding with more than one branch, they want to create a database to keep track of its customers, facilities and employees. Each branch has a unique id and address (building number, street, district, and city). A branch may have more than one facility (e.g. swimming pool, spa, etc.). Each facility must belong to only one branch, and the information for a facility is name and fees. In addition, each fitness branch offers different classes (such as Yoga, Pilates, Zumba, etc.). All classes should be led by at most one trainer, who is an employee.

Employees must work at one and only one branch. Each employee has an id, name, address (building number, street, district, and city) and can have multiple phone numbers. An employee can only be an admin, managing the facility or working as a clerk, or a trainer. An employee cannot be a trainer and an admin because the trainer id compensated an hourly wage while an admin staff paid a monthly salary. Trainers can train individual customers at different time sessions.

A customer must register at one branch. Each customer has a membership number, name (stored as first name and last name), email, and only one phone number. A customer can join more than one class or uses any number of facilities. A customer may also decide to be trained by at most one personal trainer.

Given the above description:

1. Draw a Chen's Notation ER Diagram for the database, identifying the following:

a.All the entities, attributes and relationships

b.Primary key and (discriminator in weak entity, if any). Don’t forget each entity has to have PK.

c.Participation and cardinality constraints. (Explain your choices for two constraints- i.e. identify the words the guided your decision)

d.Specialization and completeness constraints (if there is an ISA relationship).

2. Write a schema for two entities and two relationships of your choice. Remember, sometimes a relationship is better not be represented in a separate schema; if this is the case with your chosen relationship explain what you will do.

Create the Database:

The data is the same as was described in the ER Design Project assignment. In that assignment you were asked to map the ER diagram to relations in the database. Here is a formal description of the relations that you will use in this assignment:

streamTV Database Relations

shows(showID, title, premiere_year, network, creator, category)

episode(showID, episodeID, airdate, title)

• showID is a foreign key to shows

actor(actID, fname, lname)

main_cast(showID, actorID, role)

• showID is a foreign key to shows

• actID is a foreign key to actor

recurring_cast(showID, episodeID, actorID, role)

• showID is a foreign key to shows episodeID is a foreign key to episode

• actID is a foreign key to actor

customer(custID, fname, lname, email, creditcard,membersince,renewaldate, password, username)

cust_queue(custID, showID, datequeued)

• custID is a foreign key to customer

• showID is a foreign key to shows

watched(custID, showID, episodeID, datewatched)

• custID is a foreign key to customer
• showID is a foreign key to shows
• (showID, episodeID) is a foreign key to episode
• (custID, showID) is a foreign key to cust_queue

Primary keys are in bold.

Question:

SQL Queries:

The management at streamTV needs to retrieve certain information about their the data in the database. Specify the SQL queries for the questions listed here:

1. Find the titles and premiere years of all shows that were created after 2005.

2. Find the number of episodes watched by each customer, sorted by customer last name.

3. Find the names and roles of all actors in the main cast of Friday Night Lights.

4. Find all actors who are in the main cast of at least one show and in the recurring cast of at least one show. Display the actor's first name, last name, the title of the show in which the actor is in the main cast, the title of the show in which the actors is in the recurring cast, and the role the actor plays in each show.

5. How many shows have episodes with the word "good" in the title.

6. List the show title, episode number, date and episode title for all of the shows with the word "good" in the title. Sort the list by airdate.

7. Which episodes that have been watched originally aired in 2005. Display the show title, the episode title and the original air date.

8. Display the names of all actors who have had recurring roles in shows on NBC. Include the name of the actor, the title of the show and the role.

9. A customer wants to add to her queue every show that Amy Poehler has appeared in. List all of these shows by title.

10. For each customer (display first and last name), display which show and episode was the first one watched by that customer. Sort the result by the customer's last name.

11. Find all shows that have more than 5 seasons. Display the title of the show, and the number of seasons. Sort the result by the number of seasons. Note that the first digit of each episode number represents the season number.

12. Find the titles of all shows that were not watched by any customers in August of 2013.

13. List the title of the show that has been watched the most by customers. Also display the number of times it has been watched.

14. For each show, list the number of customers who have that show in their queue. Display the show title and the number of customers. Sort by show title.

Consider the design of a database for a web site of a web site of videos that teach people how to do things by yourself, such as house renovation. We will call videos as DIY videos. First, all registered users can post DIY questions, such as “how to paint a wall”. Each DIY question is identified by a question ID, the question itself, and a list of tags (each tag is a lower case word) to annotate the question, and the date the question is posted,. For example, for the question “how to paint a wall”, we can add the tags “wall, paint, roller, brush”. A registered user can also post a DIY video (from the existing video website) to an existing DIY question. Each video is identified by a unique URL (use the URL when you click the share button at the video website page, a title and a description of the video, the date on which the video is posted to your database system. A registered user can search for videos by typing a question (ideally, support fuzzy search since a user might not be able to type the exact phrase in a question), all relevant video links will be returned as a list so that a user can click any video link in the result list to play that video. Each registered user is identified by a unique username, which must be an email, a password, a first name, a last name, the gender, and his/her birthday. A registered user can give at most one review for each video, but on a particular day, the user can post at most three videos. The review given by a user has a score of {“Poor”, “Fair”, “Good”, “Excellent”} and then a short remark. A user can also modify an existing review as well as deleting an existing review. Each user has a private favorite list of DIY videos(called my favorite DIYs). Each user can insert or delete a DIY video from that favorite list. When each video is shown, the DIY question will also be shown beside the title of the video, so that a user can click it to see other videos for this DIY question. While a video is on display, there is a button under the video called “Add to my favorite DIYs” so that a user can easily add this video to his/her favorite DIYs. The system has a distinguished user called “root” whose username is root (this is the only username that is not an email).

- Draw an E-R diagram for the system, in particular, use arrows or thick lines to represent constraints appropriately. Write down your assumptions and justifications briefly and clearly.

- Translate the above E-R diagram into a relational model, i.e., write a set of CREATE TABLE statements. In particular, specify primary key, foreign key and other constraints whenever possible.

You are hired to design a database for a fitness center. As the fitness center is expanding with more than one branch, they want to create a database to keep track of its customers, facilities and employees. Each branch has a unique id and address (building number, street, district, and city). A branch may have more than one facility (e.g. swimming pool, spa, etc.). Each facility must belong to only one branch, and the information for a facility is name and fees. In addition, each fitness branch offers different classes (such as Yoga, Pilates, Zumba, etc.). All classes should be led by at most one trainer, who is an employee.

Employees must work at one and only one branch. Each employee has an id, name, address (building number, street, district, and city) and can have multiple phone numbers. An employee can only be an admin, managing the facility or working as a clerk, or a trainer. An employee cannot be a trainer and an admin because the trainer id compensated an hourly wage while an admin staff paid a monthly salary. Trainers can train individual customers at different time sessions.

A customer must register at one branch. Each customer has a membership number, name (stored as first name and last name), email, and only one phone number. A customer can join more than one class or uses any number of facilities. A customer may also decide to be trained by at most one personal trainer.

Given the above description:

1. Draw an ERD for the database, identifying the following:
   1. All the entities, attributes and relationships
   2. Primary key and (discriminator in weak entity, if any). Don’t forget each entity has to have PK.
   3. Participation and cardinality constraints. (Explain your choices for two constraints- i.e. identify the words the guided your decision)
   4. Specialization and completeness constraints (if there is an ISA relationship).

2. Write a schema for two entities and two relationships of your choice. Remember, sometimes a relationship is better not be represented in a separate schema; if this is the case with your chosen relationship explain what you will do.
3. Please use a tool to draw the ERD exactly draw it use the word microsoft to make it clear answer .

1. Data on students in liberal arts colleges in the U.S. was collected, and the average cost per student was recorded for each school. The following five-number summary was calculated for this data set: minimum= $17,554; first quartile = $23,115; median = $26,668; third quartile = $45,879; maximum=$102,262.

WHEN TYPING CORRECT NUMERICAL ANSWER TYPE: 12,345 NOT 12345 or $12345 or $12,345

a. Calculate the Upper Fence

__________________

b. Using the definition of outlier discussed in your textbook, is the maximum an outlier (Yes/No)?

__________________

c. Calculate the Lower Fence

___________________

d. Using the definition of outlier discussed in your textbook, is the maximum an outlier (Yes/No)?

___________________

2.

Suppose an algebra professor found that the correlation (r) between study time (in hours) and exam score (out of 100) is +.80, and the regression line was found to be ExamScore = 20 + 4*HoursStudied. He arrived at this equation through years of collecting data on his students.

What is the explanatory variable?

What is the response variable?

Is there a positive relationship, a negative relationship or no relationship?

Is the relationship strong or weak?

What is the slope (type number):

What is the intercept (type number):

Predict the exam score for a student who studies 10 hours. (type number)

3.

The quantitative reasoning GRE scores are known to approximately have a Normal distribution with a mean of μ = 151 points and a standard deviation of σ = 8 points.  

FORMAT FOR PARTS 1-3: lowerbound, upperbound. using numbers as example:  5,10

1.  Use the Empirical Rule to specify the ranges into which 68% of test takers fall.

2. Use the Empirical Rule to specify the ranges into which 95% of test takers fall.

3. Use the Empirical Rule to specify the ranges into which 99.7% of test takers fall .

FOR ANSWERS 4-5 type number ONLY: for example if 10% just type 10

4.  What percentage of test takers score lower than 159?

5. What percentage of test takers scored between 135 and 151?

This project should be completed using Excel (with formulas and linked data). Below are the deliverables: 1. Prepare a Multi-Step Income Statement for the year ended 2018. This statement should be flexibly designed (formulas in cells). To the right of your dollars in this statement, show common-sized percentages based on sales (vertical analysis). 2. Show journal entries, adjusting entries and closing entries for the below additional information…none of the journal entries for 2018 have been posted to the ledger. 3. Prepare a Statement of Retained Earnings for the year ended 2018. This statement should be flexibly designed. 4. Prepare a Classified Balance Sheet dated Dec. 31, 2018. Again, a flexible design is required so any changes will automatically update the balance sheet. 5. Prepare a Statement of Cash Flows using the indirect method for the year ended 2018. The Statement of Cash Flows (operating section) should automatically change when assumptions are changed. Your Name, Inc. Balance Sheet 12/31/2017 Current Assets Cash $18,000 Marketable Securities (Short-term) 2,000 Accounts Receivable 14,000 Allowance for Bad Debt (2,000) Inventory 15,000 Prepaid Insurance 5,000 Total Current Assets $52,000 Property, Plant, and Equipment Land $30,000 Building 150,000 Accumulated Dep. – Building (45,000) Equipment 100,000 Accumulated Dep. - Equipment (20,000) Total PPE $215,000 Total Assets $267,000 Current Liabilities Accounts Payable $9,000 Unearned Revenue 3,000 Income Taxes Payable 3,000 Total Current Liabilities $15,000 Long-term Liabilities Bonds, 10%, due in 2021 $100,000 Equity Common Stock $ 50,000 (100,000 authorized, 50,000 issued) cont. Additional Pd.-in Capital 80,000 Retained Earnings not given (must be calc.) Total Equity $152,000 Total Liabilities & Equity $267,000 Additional Information (for all entries): 1. Sales for 2018 are $250,000. All sales are on credit. 2. Gross Margin/Profit ratio is 40 percent 3. Accounts Receivable: i. $180,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet). ii. $3,000 of A/R is written off during the year. iii. 5% of Accounts Receivable (after write-off and collections) is considered to be uncollectible. 4. Inventory: i. Inventory purchases is $175,000, all on credit (the periodic method is used). ii. All accounts payable is from inventory purchases; all but $12,000 of inventory purchased is paid by the end of the year. 5. Additional equipment is purchased on 4/1/18 for $20,000 cash. All equipment when new, including the new purchase, has/had a five year life, no salvage value, and is depreciated using the straight-line method. 6. The building depreciates at $5,000 per year. 7. Half of the marketable securities were sold for $1,300. The FMV of the other half of the securities is also $1,300 and an adjustment to FMV is required. 8. Salaries are $2,100 per month (12 months of salaries expense must be booked). It is expected that one-half month will be owed on 12/31/18 because of when payday falls (therefore, 11.5 months of salaries have been paid and ½ month is still owed to the employees at year end). 9. $60,000 in cash is borrowed on 10/31/18 by issuing a Note Payable. Interest is 8% per year. 10. The bonds were sold at face value last December and pay interest on Dec. 31, 2018. 11. 10,000 additional shares of stock were sold for $4 a share (for EPS purposes, assume these shares were outstanding all year). 12. Insurance costing $20,000 was purchased on 7/1/18 (the same time in which the policy purchased in 2017 expired. The new policy was for 12 months). 13. On Dec. 31, 2018, 1000 shares of stock are repurchased from the market at $2.80/share (treasury stock). 14. The tax rate is 30 percent. Income taxes for the current year are due and therefore paid during the first two months of the next year (you will have to complete an entry to pay the 2017 taxes, however the 2018 taxes will not be paid until the end of January 2019). 15. Dividends of $4,000 were paid during 2018. 16. The unearned revenue has been earned during the year (classified as other revenue on the multi-step income stmt.). Required Labeled Sheets in Excel (all statements should be for 2018): 1. Data Sheet (the ending balances from 2017 – provided on the 2017 balance sheet) 2. Entries: Basic and Adjusting (you do not have to separate these entries) 3. Adjusted Trial Balance for 2018 (includes the posted amounts of all entries and adjusting entries) 4. Multi-step Income Statement 5. Retained Earnings Statement 6. Classified Balance Sheet 7. Cash Flow Statement (using the indirect method) 8. Post-Close Trial Balance for 2018 (include your closing entries above your post close trial balance)

Basic entries and adjusting entries and Multi-step Income Statement has been answered. Will require solutions for remaining 6 questions that will be Q 3, 5, 6, 7 and 8.

Thanks!

Project 3 instructions

Based on Larson & Farber: sections 5.2–5.3

Using the provided data. Assume that the closing prices of the stock form a normally distributed data set. This means that you need to use Excel to find the mean and standard deviation. Then, use those numbers and the methods you learned in sections 5.2–5.3 of the course textbook for normal distributions to answer the questions. Do NOT count the number of data points.

Complete this portion of the assignment within a single Excel file. Show your work or explain how you obtained each of your answers. Answers with no work and no explanation will receive no credit.

Show all work

1) If a person bought 1 share of Google stock within the last year, what is the probability that the stock on that day closed at less than the mean for that year? Hint: You do not want to calculate the mean to answer this one. The probability would be the same for any normal distribution. (5 points)

2) If a person bought 1 share of Google stock within the last year, what is the probability that the stock on that day closed at more than $825? (5 points)

3a) If a person bought 1 share of Google stock within the last year, what is the probability that the stock on that day closed within $50 of the mean for that year? (5 points)

3b) If a person bought 1 share of Google stock within the last year, what is the probability that the stock on that day closed at less than $700 per share. (5 points)

3c) At what prices would Google have to close in order for it to be considered statistically unusual? You will have a low and high value. Use the definition of unusual from the course textbook that is measured as a number of standard deviations. (5 points)

4) What are Quartile 1, Quartile 2, and Quartile 3 in this data set? Use Excel to find these values. This is the only question that you must answer without using anything about the normal distribution. (5 points)

5) Is the normality assumption that was made at the beginning valid? Why or why not? Hint: Does this distribution have the properties of a normal distribution as described in the course textbook? Real data sets are never perfect, however, it should be close. One option would be to construct a histogram like you did in Project 1 to see if it has the right shape. Something in the range of 10 to 12 classes is a good number. (5 points)

There are also 5 points for miscellaneous items like correct date range, correct mean, correct SD, etc.

Confidence intervals and margin of error.

Use Excel to calculate the total average income for all counties for both years, the standard deviation for both years, sample size for both years, confidence coefficient for both years, margin of error for both years, and correlation coefficient for both years. Then, compare the two years and explain your findings in at least two paragraphs. Does anything stand out? If there were changes, what could you attribute them to? Do you notice anything questionable about the data? If so, what?

Analyzing Unearned Revenue Disclosures
The following disclosures (excerpted) are from the September 2, 2018, annual report of Costco Wholesale Corporation.

The Company generally recognizes sales, net of returns, at the time the member takes possession of merchandise or receives services. When the Company collects payments from members prior to the transfer of ownership of merchandise or the performance of services, the amounts received are generally recorded as deferred sales, included in other current liabilities in the consolidated balance sheets, until the sale or service is completed. The Company reserves for estimated sales returns based on historical trends in merchandise returns and reduces sales and merchandise costs accordingly. The Company accounts for membership fee revenue, net of refunds, on a deferred basis, ratably over the one-year membership.

The Company’s Executive members qualify for a 2% reward on qualified purchases (up to a maximum reward of approximately $1,000 per year), which can be redeemed only at Costco warehouses. The Company accounts for this reward as a reduction in sales. The sales reduction and corresponding liability (classified as accrued member rewards in the consolidated balance sheets) are computed after giving effect to the estimated impact of non-redemptions, based on historical data. The net reduction in sales was $1,394, $1,281, and $1,172 in 2018, 2017, and 2016, respectively.

(a) Which of the following statements best explains in layman terms how Costco accounts for the cash received for its membership fees?

Because Costco does not know how many of its members will continue to the end of the year, cash received from members is recorded as a liability and recognized as revenue only at year-end.

When it receives cash, the company records it as a current liability. Then, it recognizes revenue evenly over the year.

The company records revenue when the cash is received.

Because Costco has a refund policy, the company records revenue when the cash is received, less an allowance for expected membership terminations.

(b) Use the balance sheet information on Costco's Deferred Membership Fees liability account and its income statement revenues related to Membership Fees earned during fiscal 2018 to compute the cash that Costco received during fiscal 2018 for membership fees.
Total cash received (in $ millions) = $Answer

(c) Use the financial statement effects template to show the effect of the cash Costco received during fiscal 2018 for membership fees and the recognition of membership fees revenue for fiscal 2018.

Use negative signs with answers, when appropriate.

(e) Complete the following sentences:
Costco recorded sales of at least $_____ from the Company’s Executive members, during fiscal 2018.