SQL DBMS Administration
Use Notepad to create the commands that will run the following queries/problem scenarios.
1. Create a view named LARGE_SLIP. It consists of the marina number, slip number, rental fee, boat name, and owner number for every slip whose length is 40 feet.
A. Write and execute the CREATE VIEW command to create the LARGE_SLIP view.
B. Write and execute the command to retrieve the marina number, slip number, rental fee, and boat name for every slip with a rental fee of $3,800 or more.
C. Write and execute the query that the DBMS actually executes against the base tables.
D. Does updating the database through the view create any problems? If so, what are they? If not, why not?
2. Create a view named RAY_4025. It consists of the marina number, slip number, length, rental fee, boat name, and owner’s last name for every slip in which the boat type is Ray 4025.
A. Write and execute the CREATE VIEW command to create the RAY_4025 view.
B. Write and execute the command to retrieve the marina number, slip number, rental fee, boat name, and owner’s last name for every slip in the RAY_4025 view with a rental fee of less than $4,000.
C. Write and execute the query that the DBMS actually executes against the base tables.
D. Does updating the database through the view create any problems? If so, what are they? If not, why not?
3. Create a view named SLIP_FEES. It consists of two columns: the first is the slip length, and the second is the average fee for all slips in the MARINA_SLIP table that have that slip length. Use AVERAGE_FEE as the name for the average fee field. Group and sort the rows by each slip length.
A. Write and execute the CREATE VIEW command to create the SLIP_FEES view.
B. Write and execute the command to retrieve the slip length and average fee for each length for which the averaged fee is less than $3,500.
C. Write and execute the query that the DBMS actually executes against the base tables.
D. Does updating the database through the view create any problems? If so, what are they? If not, why not?
4. Write, but do not execute (Just include the code in your final SQL file), the commands to grant the following privileges.
A. User Oliver must be able to retrieve data from the MARINA_SLIP table
B. Users Crandall and Perez must be able to add new owners and slips to the database.
C. Users Johnson and Klein must be able to change the rental fee of any slip.
D. All users must be able to retrieve the length, boat name, and owner number for every slip.
E. User Klein must be able to add and delete service categories.
F. User Adams must be able to create an index on the SERVICE_REQUEST table.
G.Users Adams and Klein must be able to change the structure of the MARINA_SLIP tables.
H.User Klein must have all privileges on the MARINA, OWNER, and MARINA_SLIP tables.
5. Write, but do not execute (Just include the code in your final SQL file), the commands to revoke privileges from user Adams. 6. Create the following indexes: (You should be able to execute these commands in MySQL).
A. Create an index named BOAT_INDEX1 on the OWNER_NUM column in the MARINA_SLIP table.
B. Create an index named BOAT_INDEX2 on the BOAT_NAME column in the MARINA_SLIP table.
C. Create an index named BOAT_INDEX3 on the LENGTH and BOAT_NAME columns in the MARINA_SLIP table. List the lengths in descending order.
D. Delete the index names BOAT_INDEX3.
When you have all of your commands ready to run, test them in MySQL and save it as a SQL script.
Order an Essay Now & Get These Homework Help Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
