Transaction control***

Create a stored procedure with the following requirements: 

    Accept input that identifies the customer, the ISBN for one book, and the quantity being ordered.  CHALLENGE — try this for two books instead of one 
    Create an ORDER record that reflects today’s date as the OrderDate.   
    Create an OrderItem record for the book(s) being ordered,  reduce the (quantity on hand) appropriately for the ordered book, and either:

        Return an error message if there is not sufficient inventory for the book ordered. 

OR

        Commit the transaction   

    Leave the shipping information null (there will be a separate process for shipping information). 

Remember that the Order#  is a unique, sequential key.

Create a stored procedure NEWGUESTBOOKING that adds a booking for a new guest.

The stored procedure NEWGUESTBOOKING receives the following parameters

Input Parameters
Guest name
Guest address
HotelNo
RoomNo
dateFrom
dateTo

Output Parameters
BookingNo int
BookingResult char(40)

The stored procedure
1.  Set autocommit = OFF;
2.  Issue the START TRANSACTION;
3.  Get the max guestNo in the database.
4.  Insert a new guest into the guest table using the max_guestno + 1, and the input parameters of the guest name.
5.  Check if the HotelNo is valid, if not rollback and exit. Code CONTINUE HANDLE Exception for NOT FOUND and set a HOTELINVALID FLAG ON.
6.  Check if the RoomNo  is valid, if not rollback and exit. Code CONTINUE HANDLE Exception for NOT FOUND and set a ROOMINVALID FLAG ON.
7.  Optionally, check if a booking already exist for the hotelno, roomno for the requested stay, and if room is not available rollback and exit.  Code EXIT HANDLE Exception for this.(This is complex and will be optional).
8.  If hotel and roomno is valid, (and optionally if the room is available) insert a booking record for the specified stay, and COMMIT.  An ELSE Condition should be coded and a ROLLBACK issued setting the output parameters as BookingNo = 0, BookingResult = ‘ERROR Hotel or Room INVALID’.
9.  Test booking a hotel stay in your name for 2020-05-20 to 2020-05-25 at Hotel of your choice.

Submit the CREATE PROCEDURE statement to blackboard and image of a successful test results.

7.1 Output Design Prototypes
7.1.1 Design and implement (with data that is important to the management) 3 reports for management, 1 detailed, 1 exception, and 1 summary (one by each student).
Each report design description & layout:
Title- (Pick a Pet)
Purpose What need does it fulfill
Data Fields list from the database
Calculated fields
Grouping/Sorted (for detailed report) or Exception subset (for Exception report)
Screen-Paper Layout design
Show the sql query that was needed to fetch the data for each report. (not needed for summary report)
Detailed Report
Be sure the detailed one has grouping, and subtotals per each group.
Exception Report
Be sure the exception is not just a smaller version of the detailed report, nor the same as your exception list query.
7.1.2 Design 2 queries for any employee, 1 single query and 1 exception list query
7.1.3 Design 1 output transaction document (e.g. receipt).
7.2 Input Design Prototypes
7.3 Producing User Interface Prototypes- How do the functions of the system fit together for the user
7.3.1  Design the menu function tree for the major different user/actor types:
Show all functions that each user type will be able to request. For each user interface, show the order in which the main window and the sub-windows appear in a typical interaction.
7.3.2  Design the search equipment criteria window (by EquipID, or type, or ??)
7.3.3  Design the main User menu interface screen as a sample for all non-search screens

That is the assignment , we are suppose to be using sql and php but I have no clue how to code in that language and this assignment is due at midnight.
The title of Our Project is Pick A Pet. I will attach files below of more info.

PART I Design a database in MS-Access(Time line Plan) Ex) schedule, HR Time line
1. Describe your business environment and why you need a database.
2. Draw your business E-R Model.
3. Create a MS-Access database that reflects to your business needs with:
a. Entities Relationship [Database Tools > Relationships]
b. At least 4 tables
c. At least 3 queries
d. At least 3 forms
e. At least 3 reports
f. Main Screen Menu that loaded on the start-up
Open Form button
Open Report button
Open Searchable Form/Report button Quit button
4. Compact your MS-Access database
a. [Database Tools > Compact and Repair Database]

PART II- Time line Plan Description (PPT or Word)
a. Brief introduction about your business environment and why you need a database for your business
b. Show the content of your database as well as the interaction forms/reports
c. What is the most difficult for this project? 2. Timeline plan
a. Start / End of the project.
b. Explain each phase described on your timeline plan.

In this series, we will build and enhance the a hotel booking database.

Series 1 Objectives – Create Views

Create the bookingdb environment by downloading the bookingdb.zip file attached in this Assignment.

Review the base tables that is created.  Code SQL to create a VIEW for each base table.  The viewname should begin with ‘v’+basetablename.  The CREATE VIEW statement should code all the existing columns in the SQL portion of the statement.

Review the lesson that created the guestmessage table and create a guestmessage base table in this bookingdb.  Create a VIEW for the guestmessage table.

In this series, we will learn to create views of the base tables, create stored procedures using the views, and create triggers for insert, update and delete events on the booking table.

Upon completion of the above, export the whole database and zip it.  Rename it to bookingdbSeries1.zip and upload

Student Learning Objectives
After successful completion of this assignment the student will be able to:

Use BigQuery to query public datasets.
Write queries using aggregate functions, grouping, ordering, and subqueries.
Important Notes about Grading
A reminder, any syntax error, including missing semi-colon at the end of even one SQL statement, will result in a grade of 0 for the entire assignment.

Further each question will be graded in a binary fashion.  That is, if it is correct you will get full marks for that question.  If there is any problem with your code (except a syntax error) you will receive a grade of 0 for that question.

Requirements
First there must be no syntax errors in any of the code submitted, that includes missing a semi-colon at the end of any SQL statement.  Any syntax error (even one) will result in a grade of 0 for the entire assignment.
In a file named baby_names.sql, you will put all your SQL code for this assignment.
Your SQL statement answers to each of the questions must be numbered in order as comments, as below:
— Q1
your code for Q1 here;

— Q2
your code for Q2 here;

etc.
Pay attention to the definite article a, such as in “write a SQL query,” which means write only 1 SQL statement.
If you provide more than one answer/statement for any question, that question will receive a grade of 0..
In the case there are multiple results columns they must appear in the order specified.
Questions
Here are the questions for the code that you must write to query the Google Cloud USA  Names Public Dataset (Links to an external site.).  Please refer to the schema (below) which should assist you in determining the correct columns.usa-names-schema.png

Write a SQL query to find the total number of records with the result labelled record_count
Write a SQL query to find the total number of boys and the total number of girls born/named, containing the columns (and labels) and rows as shown below.  NOTE you may not hard code in the birth numbers, you must get them through proper grouping and aggregate function(s). Your output should be sorted on the gender column as shown below.
q2-result.png
Write a SQL query to find:
the fewest number of occurrences of any name, labelled fewest_num,
the average number of occurrences of any name, rounded to the nearest whole number, labelled as avg_num,
and the most number of occurrences of any name, labelled most_num,
grouped by state
and sorted by most number of occurrences of any name, from highest to least.
IMPORTANT NOTE there should be no where clause, finding the correct entry must come from using aggregate functions, grouping, and ordering.
q3-partial-result.png
Using the highest most_num and its state value found in question 3, write a SQL query that just finds the given name of that person at birth.  NOTE you may hardcode in the state and most_num values.  HINT you may use a simple where or having (Links to an external site.).  You may not use >, >=, <. or <=. You must find an exact match. Your result will have a single column, named name, with a single result.
Write a SQL query with a subquery to find
most popular baby name in Florida, labelled name
and the total number of occurrences of that name , labelled total
HINT You may want to work on your subquery first, to verify that the subquery is syntactically correct and that it selects the most popular baby name in Florida.
IMPORTANT NOTE You must use a subquery to find the most popular baby name in Florida. If you do not use the subquery to find this and instead, for example, hardcode values, you will receive 0 for this question.
q5-partial-result.png
What to Submit
Submit your baby_names.sql as an attachment to this assignment.

Rubric
Baby Names Rubric
Baby Names Rubric
Criteria    Ratings    Pts
This criterion is linked to a Learning OutcomeExtra Credit — early submission
Refer to Course Syllabus for eligibility timeframe
0.0 pts
+5 Extra Credit — early submission
Refer to Course Syllabus for eligibility timeframe
0.0 pts
No Marks
0.0 pts
This criterion is linked to a Learning OutcomeQuestion 1
22.0 pts
Full Marks
0.0 pts
No Marks
22.0 pts
This criterion is linked to a Learning OutcomeQuestion 2
22.0 pts
Full Marks
0.0 pts
No Marks
22.0 pts
This criterion is linked to a Learning OutcomeQuestion 3
22.0 pts
Full Marks
0.0 pts
No Marks
22.0 pts
This criterion is linked to a Learning OutcomeQuestion 4
22.0 pts
Full Marks
0.0 pts
No Marks
22.0 pts
This criterion is linked to a Learning OutcomeQuestion 5
12.0 pts
Full Marks
0.0 pts
No Marks
12.0 pts
Total Points: 100.0

In this assignment, we will create a view in finhistdb to pick S&P 100 tickers.

S&P100 is an index of ticker symbols that comprise the index.  Create a View named currentSP100Price and include all the Tickers and current prices of the stocks.  The base table to use is pricehist table.

Since there are 100 tickers, not all the tickers might be available in the pricehist table. 

In this assignment, please upload two items:

1.  SQL to create the view currentSP100Price.  Upload your SQL to Blackboard.

2.  Code SQL to select accounts with stock positions that are in the currentSP100Price view results.  In the SQL, show the account name, ticker, no. of shares, price per share and market value.  Upload your SQL to Blackboard.

– Final product must be sent back as a Word document.

– Queries should be written in MySQL code.

– The diagram can be designed in any software desired, it has to be a ER diagram like this one: https://www.researchgate.net/publication/307110857/figure/fig3/AS:403359166156803@1473179727851/Entity-relationship-ER-diagram-of-Busan-traffic-data.png