Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I Primary Keys / Foreign Keys / Default Bring up your previous 2 assignments with the sql code: 1. Write the statements to remove
I Primary Keys / Foreign Keys / Default Bring up your previous 2 assignments with the sql code: 1. Write the statements to remove (drop) the previous tables: Booking and Guest. 2. Modify your CREATE TABLE statements (from the last two assignments SQL Basics and SQL Continued) to use: a. A primary key for each of the tables. Hint: One of them is a composite key! b. A default price in table Booking should be set to 500. c. Foreign keys where appropriate: guestNo in the Booking table must refer to guestNo in the Guest table. d. Guest table - Instead of assigning a unique id to each guest manually try out auto_increment by updating your CREATE TABLE statement's guestNo field to use auto_increment example: guestNo INT auto increment Paste the 2 new create statements. Note: When running multiple sql statements you need semicolons (;) separating them. Otherwise run them one at a time. 3. Inserts: (a) Guest table - Modify your insert statements (from the SQL basic assignment) to insert the rows you did before in the Guest table, however, remove the guestNo's. Example: INSERT into Guest (lastName, firstName, phoneNo, email) VALUES ('Washington', 'George', '999-999-9999', 'g-washington@neiu.edu'); The above should generate the number beginning with 1. Try it with your own inserts! Include the revised insert statements in your Word doc and the results of a Select * from Guest so I can verify it worked! (b) Booking table Modify your insert statements (from the SQL Basic & Continued assignments) by double-checking the guestNo matches the new guestNo generated in the Guest table. Run the insert statements. Did they work (yes or no)? If not, explain what happened and fix your errors. Paste only the updated inserts and a screenshot of the results from running Select * from Booking into your Word doc. 6. Write the SQL to insert a row into Guest where the guestNo is 1. 7. Was there an error? (There should be) If so, paste it. Describe what happened. Note: When I ask for errors, I am not referring to errors like "Column count doesn't match" - those errors mean something is wrong with your code! I am looking for primary key or later foreign key errors. 8. Write a statement to insert a new row into Booking for an existing guest and do not specify the price. Note: In the insert specify the columns that you do want to add. (guestNo, dateArrive, dateDepart) 9. Check out what value got entered into the price. What happened? Paste the results of a select * from Booking. 10. When you run the same command from #8 again what happens? What error message do you receive? 11. Write a statement to insert a new entry into the Booking table, but use a guestNo that is not currently in the Guest table. 12. What happened? Paste the error you II Create another Table received. Since booking guests was so successful you decide to purchase additional properties to sublet. 13. Please write the correct statement to create the following table, choose a primary key which is the field you think should be unique: a. Property with the following fields: i. propertyNo ii. address iii. city iv. state v. zipcode vi. phone 14. Insert into the above table 3 rows. The first is propertyNo 1 which is the address info for your current place that you have been subletting in the previous assignments (you can use any address of your choosing). Insert 2 more addresses with property numbers 2 and 3, for the two new rooms you purchased. 15. Paste the results of select * from Property. III Alter 16. Write the statement to drop the attribute phone from the Property table. 17. Paste the result of select * from Property. 18. Write one statement to alter the table Booking to add a column, propertyNo, which: a. is an integer representing the propertyNo for each booking b. is a foreign key to the propertyNo in the Property table c. has a default value of 1 (i.e., your previous rental that all guests were subletting). 19. Paste the result of Select * from Booking. Check out the propertyNo attribute. What happened? 20. Guest 4 would like to sublet one of your new properties, property number 2, from June 1, 2023 until June 30, 2023 for a price of 650. Write the insert statement. 21. Paste the results of a select * from Booking. 22. Write another insert statement to insert a DIFFERENT date for guest 4 to rent a new property. But this time for a property number which does not exist in the Property table. 23. What error did you receive? Paste the error. Submission: Submit the Word document in the D2L assignment tab folder titled SQL Primary Keys and More. Label each question beginning at 1 to match the question numbers above.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started