I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement and th

Are you pressed for time and haven’t started working on your assignment yet? Would you like to buy an assignment? Use our custom writing services for better grades. Even if your deadline is approaching fast, our writers can handle your task right when you need it.


Order a Similar Paper Order a Different Paper

I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram.  For the SQL assignments, you must provide both the SQL statement and the output of your work. So for example1. SQL statement here —-Output here — 2. SQL statement here — You can use word or notepad to provide the answers just have it be somewhat clean looking. If there are any questions feel free to PM me.

I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement and th
CIS240 – Assignment 1 Student Key Creating Tables For each question below, write and execute the appropriate SQL statement in the Database Management System of your choice using the indicated database. Please provide both the SQL statement and the output from your statement. You may copy and paste or provide screen shots of your work. If you provide your work in a separate document, please be certain to number your responses. Submit your completed assignment to the appropriate Assignment Submission Folder in the course shell by the due date indicated on the Course Schedule. 8 points Use the BITS database for steps 1-4. 1. Create a table named Location. The Location table should have the same structure as the Trip table from the Colonial Adventure Tours database (see the ER Diagram), but with the following changes (2pts): a. Change TripID to LocationID and use the number/numeric data type (use number for Oracle and numeric for all other DBMSs). b. Change TripName to LocationName. c. Change the data type for the Distance column to decimal(4,1). * Table should be created successfully. 2. Insert the following records into the Location table (1pt): 42,Poudre River Trail,Island Grove Park,CO,21.4,10,Biking,Late Spring 43,Pikes Peak,Barr Trail,CO,26,12,Hiking,Early Fall * Insertion should succeed. 3. Display the contents of the Location table to ensure the records were added correctly. (1pt) 4. Remove the Location table. Ensure that it was dropped successfully using a query or describe statement. (1pt) * Query should fail. 5. Run the appropriate script file to create the Colonial Adventure Tours database. Make sure that you run the correct file for the Database Management System you are using. (1pt) * Commands should complete successfully. 6. Double check that you are switched to the Colonial database (in Oracle SQL Developer, this will require creating a connection for the Colonial user). Verify that the database was created correctly by using a DESCRIBE (or equivalent) command for each table and comparing it to the ER diagram. (1pt) 7. Verify that the data was loaded correctly by querying each table and comparing the results to pages 16-20 in the Concepts book. (1pt)LocationID LocationName StartLocation State Distance MaxGrpSize Type Season 42 Poudre River Trail Island Grove Park CO 21.4 10 Biking Late Spring 43 Pikes Peak Barr Trail CO 26 12 Hiking Early Fall
I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement and th
CIS240 – Assignment 3 Student Key Multiple-Table Queries For each question below, write and execute the appropriate SQL statement in the Database Management System of your choice using the Colonial Adventure Tours database. Please provide both the SQL statement and the output from your statement. You may copy and paste or provide screen shots of your work. If you provide your work in a separate document, please be certain to number your responses. Submit your completed assignment to the appropriate Assignment Submission Folder in the course shell by the due date indicated on the Course Schedule. 15 points 1. For each reservation, list the trip ID, trip name, state, trip date, and number of persons. Order the results by trip date. (1pt) 2. For each customer who lives in the state of New York, list the reservation id, trip date, and total cost (trip price + other fees) of any trips they have reserved. (1pt)TripID TripName State TripDate NumPersons 40 Wachusett Mountain MA 2018-03-26 2 2 Mt Ascutney – North Peak VT 2018-05-14 3 21 Long Pond MA 2018-06-08 2 21 Long Pond MA 2018-06-08 1 32 Northern Forest Canoe Trail NH 2018-06-11 3 1 Arethusa Falls NH 2018-06-12 4 32 Northern Forest Canoe Trail NH 2018-06-18 1 39 Welch and Dickey Mountains Hike NH 2018-06-18 3 39 Welch and Dickey Mountains Hike NH 2018-06-25 5 22 Long Pond Tour ME 2018-07-09 8 8 Black Pond NH 2018-07-09 1 11 Chocorua Lake Tour NH 2018-07-23 6 10 Mt. Cardigan – Firescrew NH 2018-07-23 1 15 Crawford Path Presidentials Hike NH 2018-07-25 6 28 Mount Garfield Hike NH 2018-08-27 2 25 Mount Battie Ride ME 2018-08-29 2 38 Sawyer River Ride NH 2018-09-11 2 38 Sawyer River Ride NH 2018-09-11 1 38 Sawyer River Ride NH 2018-09-11 2 28 Mount Garfield Hike NH 2018-09-12 2 28 Mount Garfield Hike NH 2018-09-12 1 3 Mt Ascutney – West Peak VT 2018-09-15 3 38 Sawyer River Ride NH 2018-09-18 4 4 Bradbury Mountain Ride ME 2018-09-19 4 12 Cadillac Mountain Ride ME 2018-10-01 2 12 Cadillac Mountain Ride ME 2018-10-01 2 26 Mount Cardigan Hike NH 2018-10-16 4 ReservationID TripDate TotalCost 1800005 2018-06-25 55 1800025 2018-09-11 115 3. List the trip name, type, and trip price for each reservation that has 2 people. (1pt) 4. List the name of each hiking trip that has a $15 fee on its reservation. (1pt) 5. List the last name and first name of each guide along with the trip name and type for each trip that they are scheduled to lead. Order the results by the guides’ last names. (2pts)TripName Type TripPrice Wachusett Mountain Hiking 55 Long Pond Hiking 95 Mount Garfield Hike Hiking 35 Sawyer River Ride Biking 90 Cadillac Mountain Ride Biking 40 Mount Battie Ride Biking 110 Mount Garfield Hike Hiking 35 Sawyer River Ride Biking 70 Cadillac Mountain Ride Biking 40 TripName Mount Cardigan Hike Crawford Path Presidentials Hike LastName FirstName TripName Type Abrams Miles Long Pond Hiking Abrams Miles Mt Ascutney – North Peak Hiking Abrams Miles McLennan Reservation Hike Hiking Boyers Rita Bradbury Mountain Ride Biking Boyers Rita Black Pond Hiking Boyers Rita Welch and Dickey Mountains Hike Hiking Boyers Rita Mt Adams Hiking Boyers Rita Westfield River Loop Biking Boyers Rita Cadillac Mountain Ride Biking Boyers Rita Mount Garfield Hike Hiking Boyers Rita Big Rock Cave Hiking Boyers Rita Mount Battie Ride Biking Devon Harley Metacomet-Monadnock Trail Hike Hiking Devon Harley Masons Farm Paddling Devon Harley Lower Pond Tour Paddling Devon Harley Chocorua Lake Tour Paddling Devon Harley Wachusett Mountain Hiking Gregory Zach Crawford Path Presidentials Hike Hiking Gregory Zach Pisgah State Park Ride Biking Gregory Zach Mt. Cardigan – Firescrew Hiking Gregory Zach Arethusa Falls Hiking Gregory Zach Mount Cardigan Hike Hiking Gregory Zach Bradbury Mountain Ride Biking Gregory Zach Mt. Chocorua Hiking Kelly Sam Cannon Mtn Hiking Kelly Sam Cherry Pond Hiking Kelly Sam Low Bald Spot Hike Hiking Kelly Sam Pondicherry Trail Ride Biking Kelly Sam Sawyer River Ride Biking Kiley Susan Baldpate Mountain Hiking Kiley Susan Chocorua Lake Tour Paddling Kiley Susan Northern Forest Canoe Trail Paddling Kiley Susan Pontook Reservoir Tour Paddling 6. Using a subquery, list the reservation ID and trip date for each reservation made by customers who live in the city of Londonderry. (1pt) 7. List the trip date and number of persons for each trip where Sam Kelly is a guide (use his name as the criteria). (2pts) 8. List the reservation id, customer number, and type for each trip where the trip price is greater than or equal to $90 and the reservation is for June. (2pts) 9. List the trip name, trip price, and other fees for any reserved trip that is longer than the Sawyer River Ride. Make sure you use the trip name for you criteria rather than hard- coding the distance value. (2pts)Rowan Hal Cadillac Mountain Hiking Rowan Hal Huguenot Head Hike Hiking Rowan Hal Blueberry Mountain Hiking Rowan Hal Seal Beach Harbor Hiking Rowan Hal Arethusa Falls Hiking Stevens Lori Mt Ascutney – North Peak Hiking Stevens Lori Missisquoi River – VT Paddling Stevens Lori Mt Ascutney – West Peak Hiking Stevens Lori Lake Mephremagog Tour Paddling Stevens Lori Bloomfield – Maidstone Paddling Stevens Lori Lower Pond Tour Paddling Unser Glory Park Loop Ride Biking Unser Glory Baldpate Mountain Hiking Unser Glory Long Pond Tour Paddling Unser Glory Chocorua Lake Tour Paddling ReservationID TripDate 1800001 2018-03-26 1800002 2018-06-08 1800014 2018-10-01 1800022 2018-06-08 TripDate NumPersons 2018-09-11 2 2018-09-18 4 2018-09-11 1 2018-09-11 2 ReservationID CustomerNum Type 1800002 101 Hiking 1800021 112 Paddling 1800022 119 Hiking TripName TripPrice OtherFees Northern Forest Canoe Trail 80 20 Long Pond Tour 75 10 Chocorua Lake Tour 75 15 Mount Battie Ride 110 25 Northern Forest Canoe Trail 90 20 Bradbury Mountain Ride 105 25 Crawford Path Presidentials Hike 60 15 10. Without using a subquery, list the customer number, name, and phone number of each customer in the database who has NOT placed a reservation. (2pts) CustomerNum FirstName LastName Phone 110 Martha Bers 585-555-0111 123 Larry Barnett 860-555-9876
I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement and th
CIS240 – Assignment 2 Student Key Single-Table Queries For each question below, write and execute the appropriate SQL statement in the Database Management System of your choice using the Colonial Adventure Tours database. Please provide both the SQL statement and the output from your statement. You may copy and paste or provide screen shots of your work. If you provide your work in a separate document, please be certain to number your responses. Submit your completed assignment to the appropriate Assignment Submission Folder in the course shell by the due date indicated on the Course Schedule. 12 points 1. List the trip ID and trip name of each trip. (1pt) 2. List the complete guide table. (1pt) TripID TripName 1 Arethusa Falls 2 Mt Ascutney – North Peak 3 Mt Ascutney – West Peak 4 Bradbury Mountain Ride 5 Baldpate Mountain 6 Blueberry Mountain 7 Bloomfield – Maidstone 8 Black Pond 9 Big Rock Cave 10 Mt. Cardigan – Firescrew 11 Chocorua Lake Tour 12 Cadillac Mountain Ride 13 Cadillac Mountain 14 Cannon Mtn 15 Crawford Path Presidentials Hike 16 Cherry Pond 17 Huguenot Head Hike 18 Low Bald Spot Hike 19 Masons Farm 20 Lake Mephremagog Tour 21 Long Pond 22 Long Pond Tour 23 Lower Pond Tour 24 Mt Adams 25 Mount Battie Ride 26 Mount Cardigan Hike 27 Mt. Chocorua 28 Mount Garfield Hike 29 Metacomet-Monadnock Trail Hike 30 McLennan Reservation Hike 31 Missisquoi River – VT 32 Northern Forest Canoe Trail 33 Park Loop Ride 34 Pontook Reservoir Tour 35 Pisgah State Park Ride 36 Pondicherry Trail Ride 37 Seal Beach Harbor 38 Sawyer River Ride 39 Welch and Dickey Mountains Hike 40 Wachusett Mountain 41 Westfield River Loop GuideNum LastName FirstName Address City State PostalCode PhoneNum HireDate AM01 Abrams Miles 54 Quest Ave. Williamsburg MA 1096 617-555-6032 2012-06-03 BR01 Boyers Rita 140 Oakton Rd. Jaffrey NH 3452 603-555-2134 2012-03-04 DH01 Devon Harley 25 Old Ranch Rd. Sunderland MA 1375 781-555-7767 2017-01-08 GZ01 Gregory Zach 7 Moose Head Rd. Dummer NH 3588 603-555-8765 2013-11-04 KS01 Kiley Susan 943 Oakton Rd. Jaffrey NH 3452 603-555-1230 2016-04-08 KS02 Kelly Sam 9 Congaree Ave. Fraconia NH 3580 603-555-0003 2016-06-10 MR01 Marston Ray 24 Shenandoah Rd. Springfield MA 1101 781-555-2323 2015-09-14 RH01 Rowan Hal 12 Heather Rd. Mount Desert ME 4660 207-555-9009 2014-06-02 SL01 Stevens Lori 15 Riverton Rd. Coventry VT 5825 802-555-3339 2014-09-05 UG01 Unser Glory 342 Pineview St. Danbury CT 6810 203-555-8534 2017-02-02 3. List the name of each customer from Connecticut. (1pt) 4. List the name of each customer not from Massachusetts. (1pt) 5. List the trip ID and trip name of each trip that has the type “paddling” or has the season “summer”. (1pt)FirstName LastName Sujata Kasuma Larry Barnett Karen Busa Brianne Brown FirstName LastName Liam Northfold Sujata Kasuma Kyle McLean Joseph Morontoia Quinn Marchand Uschi Rulf Jean Luc Caron Martha Bers Adam Vaccari Clement Chau Sadie Gernowski Siam Bretton-Borak Orlagh Hefferson Larry Barnett Karen Busa Becca Peterson Brianne Brown TripID TripName 1 Arethusa Falls 7 Bloomfield – Maidstone 8 Black Pond 9 Big Rock Cave 10 Mt. Cardigan – Firescrew 11 Chocorua Lake Tour 15 Crawford Path Presidentials Hike 19 Masons Farm 20 Lake Mephremagog Tour 21 Long Pond 22 Long Pond Tour 23 Lower Pond Tour 24 Mt Adams 30 McLennan Reservation Hike 31 Missisquoi River – VT 32 Northern Forest Canoe Trail 34 Pontook Reservoir Tour 35 Pisgah State Park Ride 39 Welch and Dickey Mountains Hike 6. List the trip ID and trip name of each trip that has the season “late spring” and is in Maine. (1pt) 7. Colonial tours is considering offering a discount on the base price of their trips (not including other fees). List the reservation ID, trip ID, number of persons, and discounted price of each reservation. The discounted price should be 90% of the regular trip price. Use Discounted_Price as the name of the new column. Order the results by Discounted_Price in descending order. (2pts) 8. Find the name of each trip that has the word “pond” in it. Note: be sure that your query lists instances of the work pond, but not the letters “pond” inside of another word. Thus, your query should NOT return the “Pondicherry Trail Ride” record. (1pt)TripID TripName 5 Baldpate Mountain 13 Cadillac Mountain 23 Lower Pond Tour 33 Park Loop Ride 41 Westfield River Loop ReservationID TripID NumPersons Discounted_Price 1800019 25 2 99 1800029 4 4 94.5 1800002 21 2 85.5 1800022 21 1 85.5 1800009 38 2 81 1800021 32 3 81 1800018 38 4 76.5 1800006 32 1 72 1800007 22 8 67.5 1800016 11 6 67.5 1800025 38 2 63 1800024 38 1 63 1800030 15 6 54 1800005 39 5 49.5 1800001 40 2 49.5 1800004 26 4 40.5 1800014 12 2 36 1800026 12 2 36 1800020 28 2 31.5 1800008 28 2 31.5 1800003 28 1 31.5 1800011 3 3 22.5 1800010 2 3 22.5 1800015 10 1 18 1800013 8 1 18 1800017 39 3 18 1800012 1 4 13.5 TripName Black Pond Cherry Pond Long Pond Long Pond Tour Lower Pond Tour 9. For each type of trip, list the type and the average distance. (2pts) 10. For each type of trip that occurs in summer, list the type and the average distance. Note: this is very similar to question 9, but only looking at those record where the season is “summer”. (1pt)Type avg_dist Biking 17.125 Hiking 6.875 Paddling 11.5556 Type avg_dist Biking 12 Hiking 7.7778 Paddling 12.75
I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement and th
CIS240 – Assignment 5 Student Key Database Administration For each question below, write and execute the appropriate SQL statement in the Database Management System of your choice using the Colonial Adventure Tours database. Please provide both the SQL statement and the output from your statement. You may copy and paste or provide screen shots of your work. If you provide your work in a separate document, please be certain to number your responses. Submit your completed assignment to the appropriate Assignment Submission Folder in the course shell by the due date indicated on the Course Schedule. 15 points 1.Create a view named Vermont that contains the trip ID, trip name, state, and type where the state equals Vermont. a. Write and execute the create view command to create the Vermont view. (1pt) * Provide command. No output needed. b. Write and execute the code to retrieve the trip ID, trip name, and type for every paddling trip in the Vermont view. (.5pt) c. Write and execute the query that the DBMS actually executes. (1pt) Output for b and c: d. Is it possible to insert, update, or delete records through this view? Why or why not? (1pt) 2. Create a view called GuideHireDate that contains each guide’s name and hire date. Combine the first and last name into a single column called Name. For example the Name column for employee AM01 would be “Miles Abrams”. a. Write and execute the create view command to create the GuideHireDate view. (1pt) * Provide command. No output needed. b. Is it possible to insert, update, or delete records through this view? Why or why not? (1pt) 3. Create the following users: Matthews, Garcia, Smith, and McBride. Assign all users the starting password Passw0rd, but ensure that McBride must change their password the next time they log in. (2.5pts) * Provide command. No output needed. 4. Grant the following privileges to the users you created: a. User Matthews must be able to retrieve data from the Reservation table. (1pt) b. Users Garcia and Smith must be able to create and change (but not remove) reservations. (1pt) c. Users Matthews and McBride must be able to change the structure of the Guide table. (1pt) * Provide command. No output needed. 5. Create the following indexes. a. Create an index called trip_index1 on the TripName column of the Trip table. (.5pt) b. Create an index called trip_index2 on the TripID and CustomerNum columns of the Reservation table. (.5pt)TripID TripName Type 20 Lake Mephremagog Tour Paddling 31 Missisquoi River – VT Paddling * Provide command. No output needed. 6. Delete the index named trip_index2. (.5pt) * Provide command. No output needed. 7. Write the commands to obtain the following information from the system catalog. a. List every column in the Guide table and its associated data type. (1pt) * Data type may vary based on DBMS: b. List every table that contains a column called TripID. Your results should include tables only (no views), so you will likely need to include two system catalog tables using a join or subquery. (1.5pt)column_name data_type GuideNum char LastName varchar FirstName varchar Address varchar City varchar State char PostalCode char PhoneNum char HireDate date table_name reservation trip tripguides
I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement and th
CIS240 – Assignment 4 Student Key Updating Data For each question below, write and execute the appropriate SQL statement in the Database Management System of your choice using the Colonial Adventure Tours database. Please provide both the SQL statement and the output from your statement. You may copy and paste or provide screen shots of your work. If you provide your work in a separate document, please be certain to number your responses. Submit your completed assignment to the appropriate Assignment Submission Folder in the course shell by the due date indicated on the Course Schedule. Note: The output from describing tables includes the information that I want you to determine, as a result, output from describing tables is omitted from this key. 10 points – 1 point each 1. Create a Hiking table with the following structure: Column Type Length Decimal Places Comments ReservationID Char 7 Primary Key TripName Varchar 75 TripDate Date Total Decimal 6 2 CustomerNum Char 4 Describe the Hiking table. * Verify using table above. 2. Insert into the Hiking table the reservation ID, the trip name, trip date, total cost (trip price plus other fees), and customer number for each reserved trip that has a type of Hiking. Query the Hiking table to show that all records were inserted successfully. ReservationId TripName TripDate Total CustomerNum 1800001 Wachusett Mountain 2018-03-26 55 101 1800002 Long Pond 2018-06-08 95 101 1800003 Mount Garfield Hike 2018-09-12 35 103 1800004 Mount Cardigan Hike 2018-10-16 60 104 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 1800008 Mount Garfield Hike 2018-09-12 35 108 1800010 Mt Ascutney – North Peak 2018-05-14 25 102 1800011 Mt Ascutney – West Peak 2018-09-15 25 102 1800012 Arethusa Falls 2018-06-12 15 115 1800013 Black Pond 2018-07-09 25 116 1800015 Mt. Cardigan – Firescrew 2018-07-23 20 120 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 1800020 Mount Garfield Hike 2018-08-27 45 124 1800022 Long Pond 2018-06-08 120 119 1800030 Crawford Path Presidentials Hike 2018-07-25 75 104 3. All July hiking trip reservations are being given a 4% discount off of the total trip price. Update the Hiking table to reflect this change. Query the Hiking table to show the changes. 4. Customer 102 has decided to cancel their hiking trips. Delete those records from the Hiking table. Query the Hiking table to show the changes.ReservationId TripName TripDate Total CustomerNum 1800001 Wachusett Mountain 2018-03-26 55 101 1800002 Long Pond 2018-06-08 95 101 1800003 Mount Garfield Hike 2018-09-12 35 103 1800004 Mount Cardigan Hike 2018-10-16 60 104 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 1800008 Mount Garfield Hike 2018-09-12 35 108 1800010 Mt Ascutney – North Peak 2018-05-14 25 102 1800011 Mt Ascutney – West Peak 2018-09-15 25 102 1800012 Arethusa Falls 2018-06-12 15 115 1800013 Black Pond 2018-07-09 24 116 1800015 Mt. Cardigan – Firescrew 2018-07-23 19.2 120 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 1800020 Mount Garfield Hike 2018-08-27 45 124 1800022 Long Pond 2018-06-08 120 119 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 ReservationId TripName TripDate Total CustomerNum 1800001 Wachusett Mountain 2018-03-26 55 101 1800002 Long Pond 2018-06-08 95 101 1800003 Mount Garfield Hike 2018-09-12 35 103 1800004 Mount Cardigan Hike 2018-10-16 60 104 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 1800008 Mount Garfield Hike 2018-09-12 35 108 1800012 Arethusa Falls 2018-06-12 15 115 1800013 Black Pond 2018-07-09 24 116 1800015 Mt. Cardigan – Firescrew 2018-07-23 19.2 120 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 1800020 Mount Garfield Hike 2018-08-27 45 124 1800022 Long Pond 2018-06-08 120 119 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 5. Add a new record to the Hiking table with the following information: Customer 114 has scheduled a trip to Long Pond for June 24th 2018. The reservation ID for this trip is 1800031, and the total price is $105. Query the Hiking table to show the changes. 6. Add a variable length character field named Difficulty to the Hiking table. The maximum length for this column is 12 characters. Ensure that the value for all records (and any future additions) is ‘Beginner’. Query the Hiking table to show the changes. 7. None of the trip names in the Hiking table are long enough to need all 75 characters that are allowed in the trip name column. Leave TripName as a variable length character field, but shorten the maximum length to 35 characters. Describe the Hiking table to show the changes. * Verify using information above. 8. Change the CustomerNum column so that it does not allow NULLs. Describe the Hiking table to show the changes. * Verify using information above.ReservationId TripName TripDate Total CustomerNum 1800001 Wachusett Mountain 2018-03-26 55 101 1800002 Long Pond 2018-06-08 95 101 1800003 Mount Garfield Hike 2018-09-12 35 103 1800004 Mount Cardigan Hike 2018-10-16 60 104 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 1800008 Mount Garfield Hike 2018-09-12 35 108 1800012 Arethusa Falls 2018-06-12 15 115 1800013 Black Pond 2018-07-09 24 116 1800015 Mt. Cardigan – Firescrew 2018-07-23 19.2 120 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 1800020 Mount Garfield Hike 2018-08-27 45 124 1800022 Long Pond 2018-06-08 120 119 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 1800031 Long Pond 2016-06-24 105 114 ReservationId TripName TripDate Total CustomerNum Difficulty 1800001 Wachusett Mountain 2018-03-26 55 101 Beginner 1800002 Long Pond 2018-06-08 95 101 Beginner 1800003 Mount Garfield Hike 2018-09-12 35 103 Beginner 1800004 Mount Cardigan Hike 2018-10-16 60 104 Beginner 1800005 Welch and Dickey Mountains Hike 2018-06-25 55 105 Beginner 1800008 Mount Garfield Hike 2018-09-12 35 108 Beginner 1800012 Arethusa Falls 2018-06-12 15 115 Beginner 1800013 Black Pond 2018-07-09 24 116 Beginner 1800015 Mt. Cardigan – Firescrew 2018-07-23 19.2 120 Beginner 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 Beginner 1800020 Mount Garfield Hike 2018-08-27 45 124 Beginner 1800022 Long Pond 2018-06-08 120 119 Beginner 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 Beginner 1800031 Long Pond 2016-06-24 105 114 Beginner 9. Customer 105 has decided to postpone their trip. Remove the trip date from their reservation in the Hiking table. Query the Hiking table to show the changes. 10. Delete the Hiking table. Attempt to query the Hiking table to verify deletion. * Query should failReservationId TripName TripDate Total CustomerNum Difficulty 1800001 Wachusett Mountain 2018-03-26 55 101 Beginner 1800002 Long Pond 2018-06-08 95 101 Beginner 1800003 Mount Garfield Hike 2018-09-12 35 103 Beginner 1800004 Mount Cardigan Hike 2018-10-16 60 104 Beginner 1800005 Welch and Dickey Mountains Hike 55 105 Beginner 1800008 Mount Garfield Hike 2018-09-12 35 108 Beginner 1800012 Arethusa Falls 2018-06-12 15 115 Beginner 1800013 Black Pond 2018-07-09 24 116 Beginner 1800015 Mt. Cardigan – Firescrew 2018-07-23 19.2 120 Beginner 1800017 Welch and Dickey Mountains Hike 2018-06-18 25 122 Beginner 1800020 Mount Garfield Hike 2018-08-27 45 124 Beginner 1800022 Long Pond 2018-06-08 120 119 Beginner 1800030 Crawford Path Presidentials Hike 2018-07-25 72 104 Beginner 1800031 Long Pond 2016-06-24 105 114 Beginner
I have 35 written questions and 5 SQL assignments.I will provide all three Query languages for the database and the ER Diagram. For the SQL assignments, you must provide both the SQL statement and th
Review Questions 1 Please answer these questions in your own words: 1. What is redundancy? What problems are associated with redundancy? 2. What is an entity? An attribute? 3. What is a relationship? A one-to-many relationship? 4. How do you create a one-to-many relationship in a database system? 5. What is an E-R diagram? 6. What is database design? 7. What is an integrity constraint? When does a database have integrity? 8. What is data independence? Why is it desirable? 9. How can the complexity of a DBMS be a disadvantage? 10. Why can a failure in a database environment be more serious than an error in a non-database environment? Review Questions 2 Please answer these questions in your own words: 1. What is a relational database? 2. What is an unnormalized relation? Is it a relation according to the definition of the word relation? 3. How is the term attribute used in the relational model? What is a more common name for attribute? 4. What is a primary key? What is the primary key for each table in the Colonial Adventure Tours database shown in the Colonial Tours ER Diagram file? 5. Describe the purpose of the UNION command in relational algebra. Review Questions 3 Please answer these questions in your own words: 1. Define functional dependence. 2. Define candidate key. 3. Define first normal form. 4. Define second normal form. What types of problems would you find in tables that are not in second normal form? 5. Define third normal form. What types of problems would you find in tables that are not in third normal form? Review Questions 4 Please answer these questions in your own words: 1. Define the term user view as it applies to database design. 2. The information-level design method presented in this lecture contains steps that must be repeated for each user view. List the steps and briefly describe the kinds of activities that must take place at each step. 3. A database at a college is required to support the following requirements. Complete the information- level design for this set of requirements. Use your own experience to determine any constraints you need that are not stated in the problem. Represent the answer in DBDL. – For a department, store its number and name. – For an advisor, store his or her number and name and the number of the department to which he or she is assigned. – For a course, store its code and description (for example, MTH110 or Algebra). – For a student, store his or her number and name. For each course the student has taken, store the course code, course description, and grade received. In addition, store the number and name of the student’s advisor. Assume that an advisor may advise any number of students but that each student has just one advisor. 4. List the changes you would need to make in your answer to Question 3 if a student could have more than one advisor. 5. Suppose in addition to the requirements specified in Question 4, you must store the number of the department in which the student is majoring. Indicate the changes this would cause in the design if the student’s advisor does not necessarily have to be in the department in which the student is majoring. Review Questions 5 Please answer these questions in your own words: 1. What is metadata? Which component of a DBMS maintains meta data? 2. How does a catalog differ from a data dictionary? 3. What is meant by concurrent update? 4. Describe a situation that could cause a lost update. 5. What is locking, and what does it accomplish? 6. Describe two-phase locking. 7. What is deadlock? How does it occur? 8. What is security? 9. What is encryption? How does encryption relate to security? 10. What is authentication? Describe three types of authentication.

Writerbay.net

Most students find it hard to finish papers at some point in their studies. If it ever happens to you, don’t get desperate—we have a service for every writing emergency! Whether you’re stuck with a problem, equation, or a piece of creative writing, we will definitely come to your rescue. Fill in the order form with the details of your paper. Write your personal instructions so we can meet your expectations.


Order a Similar Paper Order a Different Paper