Part I The input to the program will be a text file containing the information for a tolerance table. An example follows using the values from the first lecture on tolerance analysis. These values will be stored in a text file. The data is comma delimited, which means that each data field is separated by a comma. If the first word is ‘PART’ the following values are the nominal size, +/- impact, tolerance, and fixed/variable. If the first word is ‘GAP’ the following values are the minimum and maximum gap sizes. (Note: assume all units are inches.) PART,2.000,-1,0.050,V PART,0.975,-1,0.025,V PART,3.000,+1,0.010,F GAP,0.000,0.080 These input values will be processed using the methods taught in class. A sample output for the first stage is given. Actual Gap Mean: 0.025” Actual Gap Tolerance: 0.085” The Maximum Gap (0.110”) is (Greater) than specified (0.080”) The Minimum Gap (-0.060”) is (Less) than the specified (0.000”)  

Part II The second stage extends the Stage 1 analysis. In this stage the program will suggest various combinations of part dimensions and tolerances to meet the gap specifications. The fixed dimension parts are not able to have their dimensions or tolerances adjusted. Take the required adjustment to all the variable parts and apply this adjustment to each part equally as a percentage of the total part dimension. Round dimensions to the nearest thousandth. Do the same application to adjust the gaps of all parts to have the gap fully use the available tolerance. Example Input: PART,2.000,-1,0.050,V PART,0.975,-1,0.025,V PART,3.000,+1,0.010,F GAP,0.000,0.080 Example Output: Recommended Adjustments to meeting GAP,0.000,0.80: PART,1.990,-1,0.020,V PART,0.970,-1,0.010,V PART,3.000,+1,0.010,F Math used to get to the result Variable parts should add to (3 – 0.04) = 2.96 Variable parts actually add to (2 + 0.975) = 2.975 All variable parts must be adjusted to 2.96/2.975 = 99.496% of the original value Variable tolerance should add to (0.04 – 0.01) = 0.03 Variable tolerance actually add to (0.05 + 0.025) = 0.075 All variable parts’ tolerance must be adjusted to 0.3/0.073 = 40% of the original value

Part III The third stage involves a statistical analysis called Monte Carlo simulation. Basically each of the dimensions is varied randomly and the gap is calculated. This random calculation is repeated hundreds or thousands of times. For each iteration, the individual gap value is calculated and stored in an array and in a file. The array of values will be used to compute the mean and standard deviation of the gap. The file will be opened using a spreadsheet program to graph a histogram, calculate an average, and calculate the standard deviation, which should match the result from your program. These values will then be used to estimate the number of rejected assemblies during production. It is reasonable to assume that the tolerance for a part is 3 standard deviations (99.73% of parts will fall within the tolerance). So for any part, we can generate random realistic values by taking the nominal value and adding/subtracting a random number, which represents variations due to the tolerance. To do this, we will need to generate a specific type of random numbers. The standard random number function in most programming language (rand() in C included) has a uniform distribution. This means that if we are finding random numbers from 0.0 to 1.0, the probability of getting 0.5 is the same as getting 0.1. This is not realistic for our application, since it will be more likely that we have our dimensions change by 0.1 than it is 0.5. For this reason, we need to modify the numbers from the rand() function so that they have a Gaussian (Normal) Distribution, which is sometimes referred to as the “bell curve”. This can be accomplished by using the Box-Muller Transformation. Although it sounds complicated, this transformation can be easily done and applied to generate a random dimension with the code given. Summary of Part 3: Apply the random_dimension() function below to get an output of a random_value of each part that will follow the explanation above. This will create a statistically likely value for each dimension given that you can then use to rerun the “actual mean gap” calculation from Part 1 1000 times. For each calculation, have the program store the value as a line in a text file (.csv extension.) You’ll be able to open this file up using Excel as a .csv file. In Excel, graph a histogram, calculate the average gap of all assemblies (of parts), and the standard deviation of the gap of all assemblies. void random_dimension(double nominal, double tolerance, double *random_value){ double r1, r2, r12; double sigma = tolerance / 3; do{ r1 = (double)( rand() % 10001 ) / 10000; }while(r1==0); r2 = (double)( rand() % 10001 ) / 10000; r12 = sqrt(-2*log(r1))*cos(2*M_PI*r2); *random_value = nominal + sigma * r12; }

Coastline Community College

Acct C103, Individual Taxation

Summer 2018

Tax Year 2017 Form 1040 Tax Return Project

Based upon Bill and Susan Minor’s information below, complete a Form 1040 for Tax Year 2017.

You will receive 10 points for each correct figure you list on Lines 7, 8a, 12, 21, 37, 40, 42, 43, 63, and 64.Email your completed Form 1040 to me by August 15, 2018.

-In 2017, Bill and Susan Minor had W-2 Wages totaling $99,000, Federal Tax Withheld of $9,000, and State Tax Withheld of $4,000.

-In 2017, Bill and Susan both had health insurance for the entire year through their employers which met the Minimum Essential Coverage requirements under the Affordable Care Act.

-Bill is an auditor at a hotel and Susan is a high school business teacher.

-Bill and Susan will file Married Filing Joint in 2017.

-In 2017, Bill received interest income of $250 from his 1^st Bank of the Third Best Account.He also cashed in a Certificate of Deposit early and the bank assessed a $25 penalty for early withdrawal of savings.

-In 2017, Bill and Susan spent $3,700 making repairs on their home.The repairs included work on their garage door, replacement of the kitchen flooring, and refurbishing their electrical breaker box.

-The Minor’s took the Standard Deduction on their Form 1040 Tax Return in 2016.They don’t know if they can take the Standard Deduction or not in 2017.They want to take the largest deduction they can in 2017, whether that be the Standard Deduction OR the Itemized Deduction.

-In 2017, Susan received a $6,000 inheritance from the estate of her great-great-grandmother.

-Susan and Bill are both 50 years old and neither one is blind or disabled.

-Their grown children are Porsche and Carmen.The kids are out of the house and have their own jobs and apartments.Porsche, who made $96,000 in 2017, is a poor money manager and Bill gave her $3,800 in 2017.

-In 2017, Susan sent her Mom $1,500 to help her Mom buy medication and medical devices.Susan does not and cannot claim her Mom as a Dependency Exemption.

-The Minor’s took fencing lessons in 2017 that cost $2,400.They said these lessons help them “unwind” after a busy workday. Susan liked fencing so much she bought a foil, epee, and saber costing $2,200.A day after buying the fencing equipment, a thief broke into Susan’s fencing locker at the fencing center and stole the foil and saber worth $1,800.Susan filed a police report, had no insurance on the foil and saber, and the property was not recovered.

-In 2017, Susan paid $850 in union dues to the El Camino Real Teachers, Educators, and Mentors Union.

-Bill owed the IRS $4,600 from his 2015 Federal Income Tax Return.He is paying $212 a month on this debt.In 2017, Bill calculated he paid $2,344 in tax and $200 in interest to the IRS.

-Susan helped her friend start a business.The friend plans on paying Susan $4,950 for all the work

she did.This payment will be made in December 2018.

-Susan decided in 2017 to become a self-employed tutor/teacher.She taught 2 kids business planning and

was paid $400.Her ONLY business expenses as a self-employed tutor/teacher were $50 for business cards and $75 for paper, pencils, and supplies.Susan also tells you she paid “Mystic Maria the World’s   7^th Best and Inexpensive Fortune Teller” $540 to predict Susan’s success.Maria reported that Susan would be wildly successful in her tutoring/teaching business and would gross approximately $500 in 2018.

-In 2017, Bill and Susan owned a rental property and calculated Total Rental Real Estate Income on Line 26, Schedule E, of $6,820.

-In 2017, Bill won a $470 cash prize for a contest he entered.Bill said he did NOT get a W-2 or a Form 1099 for the $470 he won.

-The Minor’s received a State Tax Refund of $630 in April 2017 from the State of California.This refund was for Tax Year 2016.

-Bill stated he contributed $2,000 to his Roth IRA in 2017.

-Bill and Susan are members of their local Elks Lodge, which is a non-profit organization, involved in charitable work, and a Qualifying Charitable Organization.The Minor’s pay $140 a year for dues and their only activity with the Elks is going to eat Sunday breakfast, attending dances, and buying drinks at the bar.

-The Minor’s paid $9,850 in Home Mortgage Interest in 2017.They paid a property tax bill of $3,700 in 2017.

-In 2017, Bill drove his personal auto 3,900 miles as a volunteer meal deliverer for Let’s Feed All of the Hungry, a Qualifying Charitable Organization.   

-In 2017, Bill donated 150 hours of time to Goodwill, a Qualifying Charitable Organization. He figures his time is worth $40 an hour.

-Susan has AB- blood type. When the Red Cross Bloodmobile stops by her work, Susan donated blood. In 2017, she donated 6 pints of blood. The local hospital charges $300 a pint for patients who receive AB- Blood.

-In 2017, Bill incurred $2,900 in gambling losses at Lost Wages Casino. In 2017, he won $4,100 in the California Super Big Lotto.

-Bill and Susan calculated they had 6,000 commuting miles to their W-2 jobs in 2017.

-The Minor’s donated $1,600 to Let’s Feed the Whales and Save the Children Fund in 2017. Bill’s friend Dante said the fund was is a scam. Bill went on the IRS Website and found that the fund was a Qualifying Charitable Organization.

- In 2017, Susan sold stock she had in The Coffee Grind Company. Susan’s friend, Alma Cabrera, an Enrolled Agent, and calculated that Susan had a Short-Term Capital Gain of $5,300 from the sale of the stock.

-In 2017, Susan decided to run for the city council. She paid a $255 filing fee and borrowed $15,000 from the local bank to finance her campaign. Susan lost the election.

-In 2017, Bill and Susan paid homeowner’s association dues of $1,400 on their personal residence.

-In 2017, Bill paid a dental bill of $7,500 for removal of 16 of his bad teeth and the filling of 16 of his other teeth.

In 2017, Bill paid $580 for DMV registration fees for his 2002 Chevy S-10 Pickup Truck and Susan’s 2007 Yaris. Bill has determined these DMV fees are deductible on a Schedule A.

In 2017, Susan’s sister, who is a well-known, successful, and highly paid entertainer, gave Susan a Tesla Model S Automobile with a Fair Market Value of $80,000. Susan uses the car to commute to her job and paid $900 in DMV registration fees that she has determined are deductible on a Schedule A.

In 2017, Bill was ticketed for driving his car without a valid registration and no proof of insurance. He paid a fine of $350, which included court costs.

-In 2017, Susan found a gold and diamond ring near hear house. She turned it into the police department and after 30 days, the police gave the property back to Susan since no one claimed it. In November 2017, Susan had the ring appraised and found out the fair market value of this found property was $6,200.

-In 2017, Susan paid a researcher $300 to trace her family history. The researcher reported to Susan she may be the rightful owner of a small farm in Germany worth $1,000,000. Susan has made plans to see if she owns the land, she has set aside $2,500 in a savings account for a title and legal search, and she will hire an attorney in December 2018 to determine her ownership rights.

For each of the salts on the left, match the salts on the right that can be compared directly, using K_sp values, to estimate solubilities.

(If more than one salt on the right can be directly compared, include all the relevant salts by writing your answer as a string of characters without punctuation, e.g, ABC.)

Write the expression for K in terms of the solubility, s, for each salt, when dissolved in water.

Note: Multiply out any number and put it first in the K_sp expression. Combine all exponents for s.

In this problem, we will compare the merits of leasing vs. buying a car. When you lease a car, you will typically have a smaller payment for a shorter period of time. This sounds attractive, but at the end of the lease you either have to buy the car or get a new car with no equity. If you buy a car, you have equity. You could use your car as a trade-in or sell it outright when you want to buy your next car. We want to see if it makes sense to lease then buy the car after the lease.

To make the comparison, we're going to assume you can afford the payment on a new car. If you choose to lease, you will set aside the money you're saving into an annuity. If the car payment is $500 but the lease payment is $300, then you are going to set aside $200 per month into the annuity. At the end of the lease, you will use the money from the annuity to make a down payment on buying out the car. Then you'll take out a new loan to pay off the car.

Car

We will be looking at a Ford Fusion that has a selling price of $31,000.

Buying the Car

You are going to buy the car with monthly payments at 3.85% interest for 5.5 years.

Your monthly payment will be: $522

How much money will you pay over the life of the loan? $

36-month Lease

You are given the option to lease the same car for 36 months. Your payment will be $470 per month. You can then buy the car at the end of the lease for $18,600.

Since you can afford $522 per month, how much money would you be putting into an annuity each month? $

If the annuity offers 1.0% annual interest, how much money will be in your account at the end of 36 months? $ (Round to the nearest dollar.)

At the end of the lease, you owe $18,600. If you use the money from your annuity as a down payment, how much is left that you need to borrow? $

If you are going to finance the remaining balance at 3.85% interest for 2 years, what will your payment be? $ (Round to the nearest dollar.)

Fill in the following table to help determine the money you have spent out of pocket over five years:

24-month Lease

You are given the option to lease the same car for 24 months. Your payment will be $462 per month. You can then buy the car at the end of the lease for $23,870.

Since you can afford $522 per month, how much money would you be putting into an annuity each month? $

If the annuity offers 1.0% annual interest, how much money will be in your account at the end of 24 months? $ (Round to the nearest dollar.)

At the end of the lease, you owe $23,870. If you use the money from your annuity as a down payment, how much is left that you need to borrow? $

If you are going to finance the remaining balance at 3.85% interest for 3 years, what will your payment be? $ (Round to the nearest dollar.)

Fill in the following table to help determine the money you have spent out of pocket over five years:

Conclusion

Complete the following table:

Which option is the best?

• Buying the Car
• 36-Month Lease
• 24-Month Lease

1. Which of the following z-scores is located furthest AND below the mean of a distribution?

a.z = -3.0

b.z = -1.0

c.z = +1.0

d.z = +3.0

2. All of the following apply to the concept of variability EXCEPT:

a.it is a form of inferential statistics

b.it is a form of descriptive statistics

c.it measures the extent to which scores deviate from the mean

d.it measures distance/spread of scores in a distribution

3. Which statement best describes the concept of a z-score?

a.they standardize a distribution, but do not provide any information about location

b.they standardize a distribution and allow for comparisons between different distributions

c.they standardize a distribution and are always equal to raw scores

d.they do not standardize a distribution, but do provide information about location

4.What does the Unit Normal Table tell you about a distribution?

a.the standardized proportions of a normal distribution corresponding to raw scores

b.the standardized proportions of a skewed distribution corresponding to raw scores

c.the standardized proportions of a normal distribution corresponding to z-scores

d.the standardized proportions of a skewed distribution corresponding to z-scores

5.With respect to probability and sampling:

a.each individual in a population should have an equal chance of being selected for a sample

b.sampling should occur with replacement

c.both answers a and b

d.none of the above

27. Chapter 7: Question 27

Which statement best defines the concept of the distribution of sample means?

a.it is a distribution of all the possible means of a specified (n) taken from a population

b.it is a distribution of sample statistics (a sampling distribution)

c.it is a distribution of all the possible raw scores taken from a population

d.both answers a and b

29. Chapter 7: Question 29

All of the following are characteristics of a distribution of sample means EXCEPT:

a.sample means should occur mostly near this distribution's mean

b.with larger n's, this distribution becomes more variable

c.with smaller n's, this distribution becomes more variable

d.all of the above

31.) Hypothesis testing is a form of _________, which uses _________ data to evaluate a hypothesis about a _________.

            a. descriptive statistics; population; sample

            b. inferential statistics; population; sample

            c. inferential statistics; sample; population

            d. descriptive statistics; sample; population

33.Which of the following is true about the concept of statistical power?

a.it is a useless piece of information with respect to hypothesis testing

b.it is the probability that a hypothesis test will erroneously reject a true null hypothesis

c.it is the probability that a hypothesis test will correctly reject a false null hypothesis

d.it is also known as Type I Error (?)

35.) Which of the following does NOT apply to Type II Errors?

            a. Type II Error influences the power of a hypothesis test

            b. they occur when a researcher fails to reject a false null hypothesis

            c. the probability of a Type II Error is known as beta (β)

            d. all of the above apply

36. Chapter 9: Question 36

Under which conditions will it be very difficult to detect a treatment effect using the t-statistic?

a.with a small sample size and low sample variance

b.with a small sample size and high sample variance

c.with a large sample size and high sample variance

d.with a large sample size and low sample variance

37. Chapter 9: Question 37

All of the following are assumptions of the one-sample t-test EXCEPT:

a.scores come from independent observations

b.the population that is sampled is skewed

c.the population that is sampled is normal

d.none of the above (all of the above are assumptions)

39. Chapter 9: Question 39

What is the term for the estimated standard distance between a sample mean (M) and the population mean (u)?

a.estimated standard variance

b.estimated standard deviation

c.estimated standard error

d.estimated standard treatment effect

In this assignment, you will use regular JavaScript and the Fetch API to read an external JSON data file from the server and then add the data from each student object into new rows in an existing HTML table.

This assignment is very similar to the Adding Rows to a Table assignment. The main difference is that you will:

• Use the Fetch API instead of using the AJAX API to read the external JSON data file.

Set Up This Assignment

Add this HTML to your "week11/index.html" file. Put your name in the meta author tag (highlighted below).

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Using Fetch to read a JSON file</title>
    <meta name="description" content="This is a simple example using Fetch to read a 
       JSON string then add student data as rows in an HTML table.">
    <meta name="author" content="Your name here">

    <!-- link to external CSS file -->
    <link rel="stylesheet" href="css/styles.css?v=1.0">
</head>
<body>
    <!-- Content of the page goes here. -->
    <main>
        <h1>Use Fetch to read JSON data</h1>
        <section class="intro">
            <h2>Introduction</h2>
            <p>In this assignment you will use Fetch to read in an external JSON data file. 
                You will then display the contents in a web page as new rows in an existing table. 
            </p>
        </section>

        <section class="student-info">
            <h2>Student Information from JSON file</h2>
            <p class="loading">Loading student data...</p>
            <table id="student-table">
              <thead>
                <tr><th scope="col">Name</th><th scope="col">Favorite Hobby</th><th scope="col">Favorite Color</th></tr>
              </thead>
              <tbody>
              </tbody>
            </table>
        </section>
    </main>
    
    <!-- link to external JS file -->
    <script src="js/scripts.js"></script>
</body>
</html>

JSON Data
You will also use the same students.json file that you used in the week9 assignment. Put the JSON file in a "data" subdirectory: "week11/data/students.json"

CSS Stylesheet
You will also use the same CSS file that you used in the week9 assignment. Put the CSS file in a "css" subdirectory: "week11/css/styles.css"

JavaScript
Your "week11/js/scripts.js" file will use the fetch API to read an external JSON data file, then add student data as rows to an existing table. This page contains sample code that uses the fetch API: Fetch API: An Example

This displayData function needs to do these things:

• Create a DOM fragment.
• Loop through the "students" object.
• Read the first name, last name, favorite hobby, and favorite color from each object.
• Create a table row "tr" element.
• Create a table data "td" element for each column in the row.
• Add the student's name to the first column of the row using createTextNode, template literals, and appendChild. The first column should contain both the student's first and last name in this format - "lastname, firstname" - then append the td to the tr element.
• Add the student's hobby in the second column, then append it to the tr element.
• Add the student's favorite color in the third column, then append it to the tr element.
• Add the tr to a DOM fragment
• Finally, after you've built your DOM fragment append it the "#student-table tbody" element.
• Use querySelector to read the "p.loading" paragraph.
• Change the class attribute of that paragraph from 'loading' to 'loadingHidden' using 'classList.replace': Setting CSS Styles Using JavaScript

Task 1.

For each table on the list, identify the functional dependencies. List the functional dependencies. Normalize the relations to BCNF. Then decide whether the resulting tables should be implemented in that form. If not, explain why. For each table, write the table name and write out the names, data types, and sizes of all the data items, Identify any constraints, using the conventions of the DBMS you will use for implementation. Write and execute SQL statements to create all the tables needed to implement the design.

Create indexes for foreign keys and any other columns that will be used most often for queries. Insert about five records in each table, preserving all constraints. Put in enough data to demonstrate how the database will function. Write SQL statements that will process five non-routine requests for information from the database just created. For each, write the request in English, followed by the corresponding SQL command. Create at least one trigger and write the code for it.

Tables / DDL and Insert Data have been provided below:

-- DDL to create the MS SQL tables for initial relational model for Theater Group
CREATE DATABASE Theater;

CREATE TABLE Member(
    memId       INT,
    dateJoined   DATETIME,
    firstname    VARCHAR(15),
    lastName    VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber CHAR(7),
    currentOfficeHeld VARCHAR(20),
    CONSTRAINT Member_memId_pk PRIMARY KEY(memid));

CREATE TABLE Sponsor(
    sponID       INT,
    name       VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber CHAR(7),
    CONSTRAINT Sponsor_sponId_pk PRIMARY KEY(sponID));

CREATE TABLE Subscriber(
    subID       INT,
    firstname    VARCHAR(15),
    lastName    VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber   CHAR(7),
    CONSTRAINT Subscriber_subId_pk PRIMARY KEY(subID));

CREATE TABLE Play(
    title            VARCHAR(100),
    author            VARCHAR(35),
    numberOfActs   SMALLINT,
    setChanges        SMALLINT,
    CONSTRAINT Play_title_pk PRIMARY KEY(title));

CREATE TABLE Production(
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    seasonEndDate   VARCHAR(7),
    title            VARCHAR(100),
    CONSTRAINT Prod_year_seasStDate_pk primary key(year, seasonStartDate),
    CONSTRAINT Prod_title_fk FOREIGN KEY(title) REFERENCES Play(title));

CREATE TABLE Performance(
    datePerf        VARCHAR(7),
    timePerf        VARCHAR(10),
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    CONSTRAINT Performance_date_pk PRIMARY KEY(datePerf,year),
    CONSTRAINT Performance_yr_seasStart_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year, seasonStartDate));

CREATE TABLE TicketSale(
    saleID       INT,
    saleDate   DATETIME,
    totalAmount   DECIMAL(6,2),
    perfDate   VARCHAR(7),
    perfYear   SMALLINT,
    subId       INT,
    CONSTRAINT TicketSale_ID_PK PRIMARY KEY(saleId),
    CONSTRAINT TicketSale_perfDate_fk FOREIGN KEY(perfDate,perfYear) REFERENCES Performance(datePerf,year),
    CONSTRAINT TicketSale_subId_fk FOREIGN KEY(subId) REFERENCES Subscriber(subId));

CREATE TABLE DuesPayment(
    memId        INT,
    duesYear   SMALLINT,
    amount       DECIMAL(5,2),
    datePaid   DATETIME,
    CONSTRAINT DuesPayment_memId_year_pk PRIMARY KEY(memid, duesyear),
    CONSTRAINT DuesPayment_memId_fk FOREIGN KEY(memid) REFERENCES Member(memid));

CREATE TABLE Donation(
    sponId            INT,
    donationDate   DATETIME,
    donationType   VARCHAR(20),
    donationValue   DECIMAL(8,2),
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    CONSTRAINT Donation_sponId_date_pk PRIMARY KEY(sponId, donationDate),
    CONSTRAINT Donation_sponId_fk FOREIGN KEY(sponId) REFERENCES Sponsor(sponId),
    CONSTRAINT Donation_year_seasStartDate_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year, seasonStartDate));

CREATE TABLE Ticket(
    saleId            INT,
    seatLocation   VARCHAR(3),
    price            DECIMAL(5,2),
    seattype        VARCHAR(15),
    CONSTRAINT Ticket_saleid_pk PRIMARY KEY(saleId, seatLocation),
    CONSTRAINT Ticket_saleid_fk FOREIGN KEY(saleid) REFERENCES TicketSale(saleId));

CREATE TABLE Member_Production(
    memId            INT,
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    role            VARCHAR(25),
    task            VARCHAR(25),
    CONSTRAINT Mem_Prod_Id_year_seas_pk PRIMARY KEY(memId, year, seasonStartDate),
    CONSTRAINT Mem_Prod_memId_FK FOREIGN KEY (memid) REFERENCES Member(memId),
    CONSTRAINT Mem_Prod_yr_seasStartDate_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year,seasonStartDate));

INSERT DATA:

-- insert some records

INSERT INTO Member values(11111,'01-Feb-2015', 'Frances','Hughes','10 Hudson Avenue','New Rochelle','NY','10801','914','3216789','President');
INSERT INTO Member values(22222,'01-Mar-2015', 'Irene','Jacobs','1 Windswept Place','New York','NY','10101','212','3216789','Vice-President');
INSERT INTO Member values(33333,'01-May-2015', 'Winston', 'Lee','22 Amazon Street','New York','NY', '10101','212','3336789',null);
INSERT INTO Member values(44444,'01-Feb-2015', 'Ryan','Hughes','10 Hudson Avenue','New Rochelle','NY','10801','914','5556789','Secretary');
INSERT INTO Member values(55555,'01-Feb-2015', 'Samantha', 'Babson','22 Hudson Avenue','New Rochelle','NY','10801','914','6666789','Treasurer');
INSERT INTO Member values(66666,'01-Feb-2015', 'Robert', 'Babson','22 Hudson Avenue','New Rochelle','NY','10801','914','6666789',null);

INSERT INTO Sponsor values(1234, 'Zap Electrics', '125 Main Street','New York','NY', '10101', '212','3334444');
INSERT INTO Sponsor values(1235, 'Elegant Interiors', '333 Main Street','New York','NY', '10101', '212','3334446');
INSERT INTO Sponsor values(1236, 'Deli Delights', '111 South Street', 'New Rochelle','NY','10801', '914','2224446');

INSERT INTO Subscriber values(123456, 'John','Smith','10 Sapphire Row', 'New Rochelle','NY','10801', '914','1234567');
INSERT INTO Subscriber values(987654, 'Terrence','DeSimone','10 Emerald Lane','New York','NY', '10101','914','7676767');

INSERT INTO Play values('Macbeth','Wm. Shakespeare', 3,6);
INSERT INTO Play values('Our Town','T. Wilder', 3,4);
INSERT INTO Play values('Death of a Salesman','A. Miller', 3,5);

INSERT INTO Production values(2015,'05-May', '14-May', 'Our Town');
INSERT INTO Production values(2014,'14-Oct','23-Oct','Macbeth');

INSERT INTO Performance values('05-May','8pm',2015,'05-May');
INSERT INTO Performance values('06-May','8pm',2015,'05-May');
INSERT INTO Performance values('07-May','3pm',2015,'05-May');
INSERT INTO Performance values('12-May','8pm',2015,'05-May');
INSERT INTO Performance values('13-May','8pm',2015,'05-May');
INSERT INTO Performance values('14-May','3pm',2015,'05-May');
INSERT INTO Performance values('14-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('15-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('16-Oct','3pm',2014,'14-Oct');
INSERT INTO Performance values('21-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('22-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('23-Oct','3pm',2014,'14-Oct');

INSERT INTO TicketSale values(123456,'01-May-2015',40.00,'05-May',2015,123456);
INSERT INTO Ticket values(123456, 'A1',20.00,'orch front');
INSERT INTO Ticket values(123456, 'A2',20.00,'orch front');

INSERT INTO TicketSale values(123457,'02-May-2015',80.00,'05-May',2015,987654);
INSERT INTO Ticket values(123457, 'A3',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A4',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A5',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A6',20.00,'orch front');

INSERT INTO TicketSale values(000001,'01-Oct-2014',40.00,'14-Oct',2014, 987654);
INSERT INTO Ticket values(000001, 'A1',20.00,'orch front');
INSERT INTO Ticket values(000001, 'A2',20.00,'orch front');

INSERT INTO TicketSale values(000002,'9-Oct-2014',60.00,'14-Oct',2014,123456);
INSERT INTO Ticket values(000002, 'A1',20.00,'orch front');
INSERT INTO Ticket values(000002, 'A2',20.00,'orch front');
INSERT INTO Ticket values(000002, 'A3',20.00,'orch front');

INSERT INTO DuesPayment values(11111, 2015, 50.00, '01-Jan-2015');
INSERT INTO DuesPayment values(22222, 2015, 50.00, '15-Jan-2015');
INSERT INTO DuesPayment values(33333, 2015, 50.00, '01-Feb-2015');
INSERT INTO DuesPayment values(44444, 2015, 50.00, '30-Jan-2015');
INSERT INTO DuesPayment values(55555, 2015, 50.00, '28-Jan-2015');

INSERT INTO Donation values(1234, '01-Mar-2015','sound board',1250.00,2015,'05-May');
INSERT INTO Donation values(1235, '15-Apr-2015','cash', 500.00,2015,'05-May');
INSERT INTO Donation values(1236, '05-May-2015','food',500.00,2015,'05-May');
INSERT INTO Donation values(1236, '06-May-2015','beverges',200.00,2015,'05-May');
INSERT INTO Donation values(1236, '07-May-2015','snacks',100.00,2015,'05-May');

INSERT INTO Member_Production values(11111,2015,'05-May','Emily','sets');
INSERT INTO Member_Production values(22222,2015,'05-May','Mrs. Webb','costumes');

-- DDL to delete all of the tables, use only if you need to rebuild the DB
DROP TABLE Member_Production;
DROP TABLE Ticket;
DROP TABLE Donation;
DROP TABLE DuesPayment;
DROP TABLE TicketSale;
DROP TABLE Performance;
DROP TABLE Production;
DROP TABLE Play;
DROP TABLE Subscriber;
DROP TABLE Sponsor;
DROP TABLE Member;
DROP DATABASE Theater;