Search results for 'dbm 502 learning team'

Types of Relationships and Sample Databases

Part 2
Come up with an original example of a one to many unary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

Part 3
Come up with an original example of a ternary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

Part 4
Using MySQL Workbench – create a data model for on Adult Baseball league which supports all of the following data requirements:
Teams
    A team has a unique number associated with it
    Team Name
    Team City
    Each team has more than one player associated with it

Players
    Each Player has a unique number
    A player may play or have played for more than one team
    The number of years and batting average is tracked for each team the player played for.

Coaches
    In this league, all coaches are also players.
    A Team can have more than one coach.

Bats
    A team can have multiple bats
    Each bat is identified by a unique serial number.

For each of the three models above, you should export your model as a single page PDF and submit the resulting files.

Develop an Art Museum ERD for DBM/380 Week 3

List the data specifications (must include a minimum of three entities with attributes)

Has to be on An Art Museum that needs to track the artwork, artists, and locations where the art is displayed or stored within the museum If you Don't have Visio then Do a ERD in Access. Need this last week. Make sure the Database flows.

Use a Microsoft® Visio® diagram to create a detailed ERD using the data specifications noted in the Week Two Individual Assignment. Make any necessary changes provided in your faculty's feedback.

Use a Microsoft® Access® database to create the preliminary database tables, columns with data types, primary keys, and relationships.

DBM/380 Week 4 Normalization of the Art Museum ERD

The following assignment is based on the database environment chosen and created in that last request

Your database project must meet the following assessment requirements:

Design and develop a database using professional principles and standards.
Provide a logical and physical design of the database.
Use relational database software application to develop database.
Provide an entity relationship diagram.
Normalize the database.
Generate and provide test data.

Use a Microsoft® Visio® diagram to normalize the ERD to third normal form (3NF).

Use the Microsoft® Access® database that you just created for me, create a minimum of 10 rows of test data in each table. Also, create at least one query that joins two tables and returns values from both tables.

Note. Only the Microsoft® Visio® diagram must be normalized to the 3NF. The 3NF is not required for a Microsoft® Access® database.

Submit the ERD and final database.

This is your final examination which consists of five programming questions.
You are to provide me with a .txt file or a .sql file for supplying the code for each question. Put these in a .zip file and submit them.

1. Use a cursor to retrieve the location number and the city from the locations table. Pass the location number to another cursor to retrieve from the emp_details_view the last_name, job_title and salary for that employees that work at that location.
a. Use the %ISOPEN attribute
b. Use a simple loop
c. Use an EXIT WHEN and a %NOTFOUND attribute

2. Write an exception handler to raise an exception named DUE_FOR_ROTATION
The criteria for raising the exception is if location id is 2700.
If the employee’s location in the em_details_view meets this criteria output 'Due for Rotation'.
Insert a listing of all of the EMPLOYEES that meet this criteria into the employee_analysis table.
You will need to create an analysis table as:
CREATE TABLE location_analysis
(employee_id NUMBER(6) not null,
Last_name VARCHAR(25),
First_name VARCHAR2(20),
Location_id NUMBER(4),
City VARCHAR2(30));
Screenshot #1 Creating the Analysis Table

3. You will be using the EMPLOYEES table in YOUR TABLESPACE. Using a cursor write a block to find the largest salary where the hire date is before ‘ 1-jan-96’ . When you find employees who match this criteria change give them a salary increase of 25%.

4. You will be using the JOBS table in YOUR TABLESPACE. Create a record that incorporates the four columns from the JOBS table. Output the values for a single job on four lines of output.

5. Write a PL/SQL block to print information about a given country.
a. Declare a PL/SQL record based on the structure of the COUNTRIES table.
b. Use the DEFINE command to provide the country ID. Pass the value to the PL/SQL block through a SQL*Plus substitution variable.
c. Use DBMS_OUTPUT.PUT_LINE to print selected information about the country.
d. Execute and test the PL/SQL block for the countries with the IDs CA, DE, UK, US.

DBM 405 Lab 3 Procedures and Functions Advanced Database Oracle

Step 1: Creating the First Procedure
Your first procedure is to be named MOVIE_RENTAL_SP and is going to provide functionality to process movie rentals. Based on data that will represent the movie ID, member ID, and payment method your procedure will need to generate a rental ID and then insert a new row of data into the mm_rental table. The process will also need to update the quantity column in the mm_movie table to reflect that there is one less copy of the rented movie in stock. Along with the processing, you will also need to define some user-defined exception handlers that will be used in validating the input data. Since you may need to recreate your procedure several times during the debugging process, it is suggested that you use the CREATE OR REPLACE syntax at the beginning of the CREATE statement.

The following steps will help you in setting up your code.
1. You will need to define three parameters, one each for movie ID, member ID, and payment method. Make sure that each one matches the data type of the associated column in the database tables.
2. You will have several other variables that will need to be identified and defined. It might be easier to read through the rest of the specs before you start trying to define these (look for hints in the specifications).
3. You will need to define four user-defined exceptions; one for unknown movies, one for unknown member, one for unknown payment method, and one for if a movie is unavailable.
4. You will need to validate each of the three pieces of data passed to the procedure. One easy way to do this might be to use a SELECT statement with the COUNT function to return a value into a variable based on a match in the database table against the piece of data that you are validating. If the query returns a zero then there is no match and the data is invalid; any value greater than zero means a match was found and thus the data is valid. You will need the following validations.
    1. Validate the movie ID to make sure it is valid. If not then raise the unknown movie exception.
    2. Validate the member ID to make sure one exists for that ID. If not then raise the unknown member exception.
    3. Validate the payment method to make sure it exists. If not then raise the unknown payment method exception.
    4. Check the movie quantity to make sure that there is a movie to be rented for the movie ID. If not then raise the unavailable movie exception.
    5. If all the data passes validation then you will need to create a new rental ID. This process should be in a nested block with its own EXCEPTION section to catch a NO_DATA_FOUND exception if one should happen. You can generate a new rental ID by finding the largest rental ID value in the mm_rental table (Hint: MAX function) and then increasing that value by one. The NO_DATA_FOUND exception would only be raised if there were no rental IDs in the table.
    6. Now you are ready to insert a new row of data into the mm_rental table. Use the SYSDATE function for the checkout date and NULL for the check-in date.
    7. Now, update the mm_movie table to reflect one less movie for the associated movie ID.
    8. Finally, you will need to set up an EXCEPTION section for all of your exception handling. For each exception output, you want to state what the problem is, the invalid data value, and a note that the rental cannot proceed. For example, for an invalid movie ID number, you might say, "There is no movie with id: 13 - Cannot proceed with rental". You also want to include a WHEN OTHERS exception handler.

Compile and check your code. If you get a PROCEDURE CREATED WITH COMPILATION ERRORS message then type in SHOW ERRORS and look in your code for the line noted in the error messages (be sure to compile your code with the session command SET ECHO ON). Once you have a clean compile then your are ready to test.


Step 2: Testing the First Procedure
You will need to test for scenarios that will allow both a clean movie rental and test each exception. This means that you will need to run at least five test cases.

One each for the following:
1. No movie for the ID supplied (use 13, 10, and 2 for the parameters).
2. No member for the ID supplied (use 10, 20, and 2 for the parameters).
3. No payment method for the ID supplied (use 10, 10, and 7 for the parameters).
4. A successful rental (use 5, 10, and 2 for the parameters).
5. No movie available for the ID supplied (use 5, 11, and 2 for the parameters). Since there is only one movie available for ID 5, you will get this exception.
Your output from the testing should look similar to (this would be the output for the first test above):
exec movie_rent_sp(13, 10, 2);
Output:
There is no movie with id: 13
Cannot proceed with rental
PL/SQL procedure successfully completed.

Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.


Step 3: Creating the Second Procedure
Your second procedure should be named MOVIE_RETURN_SP and should facilitate the process of checking a movie rental back in. For this procedure, you will only need to pass one piece of data to the procedure; the rental ID. You will need two user-defined exceptions; one for no rental record and one for already returned. You will be able to use several of the same techniques you used in the first procedure for your validation.

The following steps will help in setting up your code.
1. You will need to define only one parameter for the rental ID number. Make sure that it matches the data type of the associated column in the database table.
2. You will have several other variables that will need to be identified and defined. It might be easier to read through the rest of the specs before you start trying to define these (look for hints in the specifications).
3. You will need to define the two user-defined exceptions mentioned above.
4. You will need to validate the rental ID that is passed to the procedure. If it is not a valid one then raise the associated exception.
5. If it is valid then get the movie ID and check-in date from the mm_rental table.
6. Now, check the check-in date to make sure that it is NULL. If it is not then raise the associated exception.
7. If everything checks out then update the mm_rental table for the rental ID you have and use the SYSDATE function for the check-in date.
8. Now, you can update the quantity in the mm_movie table for the associated movie ID to reflect that the movie is back in stock.
9. Last, set up your exception section using appropriate error message text and data.

Compile and check your code. If you get a PROCEDURE CREATED WITH COMPILATION ERRORS message then type in SHOW ERRORS and look in your code for the line noted in the error messages (be sure to compile your code with the session command SET ECHO ON). Once you have a clean compile then your are ready to test.


Step 4: Testing the Second Procedure
You will need to test for scenarios that will allow both a clean rental return and test each exception. This means that you will need to run at least three test cases.

One each for the following:
1. No rental for the ID supplied (use 20 for the parameter).
2. A successful rental return (use 1 for the parameter).
3. Try to return the same rental in Step 2.
You output from the testing should look similar to (this would be the output for the first test above):
exec movie_return_sp(20);
Output:
There is no rental record with id: 20
Cannot proceed with return
PL/SQL procedure successfully completed.

Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.


Step 5: Creating the Function
Your function should be named MOVIE_STOCK_SF and will be used to return a message telling the user whether a movie title is available or not based on the movie ID passed to the function. The exception handling that will be needed is for NO_DATA_FOUND but we are going to set it up as a RAISE_APPLICATION_ERROR.

The following steps will help in setting up your code.
1. You will need to define only one parameter for the movie ID number. Make sure that it matches the data type of the associated column in the database table. Also, since you will be returning a notification message, you will want to make sure your RETURN statement references a data type that can handle that (Hint: variable length data type).
2. You will have several other variables that will need to be identified and defined. It might be easier to read through the rest of the specs before you start trying to define these (look for hints in the specifications).
3. You will not be doing any validation so the first thing you need to do is retrieve the movie title and quantity available from the mm_movie table based on the ID passed to the function.
4. Now, you need to determine if any are available. IF the value in the quantity column is greater than zero then you will be returning a message saying something like "Star Wars is available: 0 on the shelf", ELSE if the value is zero then you should return a message saying something like "Star Wars is currently not available". Hint: A good way to return a test string is to assign it to a variable and then simply use the variable name in the RETURN clause.
5. Finally, set up your exception section to use a RAISE_APPLICATION_ERROR for the NO_DATA_FOUND exception handler. Assign an error number of -20001 to it and an error message that states there is no movie available for the ID (be sure to include the id in the message).

Compile and check your code. If you get a FUNCTION CREATED WITH COMPILATION ERRORS message then type in SHOW ERRORS and look in your code for the line noted in the error messages (be sure to compile your code with the session command SET ECHO ON). Once you have a clean compile then your are ready to test.


Step 6: Testing the Function
You will need to test for all three possible scenarios.

1. Test for a movie in stock using movie ID 11.
2. Test for a movie not in stock using movie ID 5 (from your tests of the second procedure above, the quantity should be 0).
3. Test for an invalid movie ID using movie ID 20.
For test number 2, you may need to manipulate the quantity amount in the database, which will be fine.
Test your function by using a select statement against the DUAL table like in the example below:
select movie_stock_sf(20) from dual;

Be sure that when you have verified that everything works, you run your testing in a spools session and save the file to be turned in.
This concludes the Lab for Week 3.
 
Deliverables
Your deliverable submission should consist of your Lab 3 script file and the spooled output files described at the beginning of the lab. If you would like, you can include both files in a single ZIP file to be submitted to the Week 3 Lab Dropbox.

DBM/380 Week 3 ERD Smith Consulting in Access

Individual Database Creation Project

The following assignment is based on the database environment chosen and discussed in the Week Two Individual Assignment.

Create a Microsoft® Visio® diagram to create a detailed Entity Relationship Diagram(ERD) using the data specifications noted in the Week Two Individual Assignment. Make any necessary changes provided in your faculty’s feedback.

Create a Microsoft® Access® database to create the preliminary database tables, columns with data types, primary keys, and relationships.

DBM/380 Week 4 Normalization of the Smith Consulting ERD

The following assignment is based on the database environment chosen and created in the Week Three Individual Assignment.
Your database project must meet the following assessment requirements:

Design and develop a database using professional principles and standards.
Provide a logical and physical design of the database.
Use relational database software application to develop database.   
Provide an entity relationship diagram.
Normalize the database.
Generate and provide test data.

Use a Microsoft® Visio® diagram to normalize the ERD to third normal form (3NF).

Use the Microsoft® Access® database created in Week Three to create a minimum of 10 rows of test data in each table. Also, create at least one query that joins two tables and returns values from both tables.

Note. Only the Microsoft® Visio® diagram must be normalized to the 3NF. The 3NF is not required for a Microsoft® Access® database.

Submit the ERD and final database to the appropriate Assignment link.

DBM 405 Lab 4 PL/SQL Packages Advanced Database Oracle

Step 1: Creating the Package Specifications
Before you begin, there are several things you will want to do to get ready for the lab.
1.  Refresh your database tables by running the movierental.sql script. This will restore all of the data back to where it was in the beginning for the course.
2.  Drop the two procedures and the function created in Lab 3. Be sure that you have your script file from Lab 3 so you can copy the procedure and function code from it.

Now you are ready to create your package specification. Your package name should be MM_RENTALS_PKG and it will contain the two procedures and the one function that were created in Lab 3.
Remember that for the specification, you only need to list the procedure and function header data (CREATE statement with parameters).

Test your package specification by running the script in SQL*Plus. If you have any errors, debug them and once you have a clean compile, move on to Step 2.

Step 2: Creating the Package Body
Creating the package body should be simple since you already have the code for the two procedures and the function, and you know it works.
Remember that the name for the package body must match the name of the specifications, and that the procedure and function header in the body must match that of the specification exactly.
Once you have created the body then run the script in your SQL*Plus session. Once you have a clean compile then move on to Step 3 to do your testing.

Step 3: Testing the Package
To test your package, you will need to run the same exact tests you did in Lab 3. The following outlines what you will test for:
Testing the first procedure -
1.  No movie for the ID supplied (use 13, 10, and 2 for the parameters).
2.  No member for the ID supplied (use 10, 20, and 2 for the parameters).
3.  No payment method for the ID supplied (use 10, 10, and 7 for the parameters).
4.  A successful rental (use 5, 10, and 2 for the parameters).
5.  No movie available for the ID supplied (use 5, 11, and 2 for the parameters). Since there is only one movie available for ID 5, you will get this exception.
Testing the second procedure -
1.  No rental for the ID supplied (use 20 for the parameter).
2.  A successful rental return (use 1 for the parameter).
3.  Try to return the same rental in Step 2.
Testing the function -
1.  Test for a movie in stock using movie ID 11.
2.  Test for a movie not in stock using movie ID 5 (from your tests of the second procedure above, the quantity should be 0).
3.  Test for an invalid movie ID using movie ID 20.
IMPORTANT: Remember that all of your testing needs to be saved in a spool session so that it can be submitted to the Dropbox for grading.

Step 4: Determining Dependencies
Having created a package that contains program units to support the movie rental process is a major step in customizing the new database.
As application modifications are made in the future, however, we need to be able to identify all object dependencies to test changes.
For this step in the lab, you are to use either data dictionary views or the dependency tree utility found in Doc Sharing (utldtree.sql file) to compile a list of dependencies for all the More Movies database objects.
Remember that an object is anything that was created using the CREATE statement.
Present your finding in a separate Word document in a tabular format as in the following sample.
Each dependency type should be listed as either direct or indirect.

DBM 405 Lab 5 User Defined Database Triggers

Scenario/Summary
This week, we are going to continue to expand the functionality of our database schema by adding a couple of triggers to that will help us automate some of the processing we already have in place. Triggers can be used to automate repetitive tasks within the database, such as adjusting inventory levels based on other actions taken in the database. Once you have created and tested your triggers, you will need to make some adjustments to the code in the package that was created in Lab 4.
For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. You will be running tests to verify that your triggers are working once your package has been updated. Spool your output and name your files with your last name plus lab 5 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab5.txt. Submit both the spooled output files AND the script file for grading of the lab.

LAB STEP
Step 1: Creating the First Trigger
The first trigger you are going to create is to be named RENTING_MOVIE and is going to take care of the process of updating the mm_movie table to reflect a change downward in the quantity column for a movie when it is rented. Keep the following in mind:
1. The trigger needs to be an AFTER INSERT trigger on the mm_rental table. We want it to be an AFTER trigger so that, in case there are any exceptions raised, the trigger will not fire.
2. The trigger needs to be able to fire for each row that is inserted into the table.
3. The trigger process will only involve the update statement to lessen the quantity amount in the mm_movie table by one for the referenced movie ID.
Test your code by running the script in SQL*Plus. If you have any errors, debug them and once you have a clean compile, move on to Step 2.

Step 2: Creating the Second Trigger
The second trigger you are going to create is to be named RETURNING_MOVIE and is going to take care of the process of updating the mm_movie table to reflect a change upward in the quantity column for a movie when it is returned. Keep the following in mind:
1. The trigger needs to be an AFTER UPDATE trigger on the mm_rental table based on the updating of the check in date in the mm_rental table.
2. The trigger needs to be able to fire for each row that is updated.
3. The trigger process will only involve the update statement to increase the quantity amount in the mm_movie table by one for referenced movie ID.
Test your code by running the script in SQL*Plus. If you have any errors, debug them and once you have a clean compile, move on to step 2.

Step 3: Modifying the Package Code
Now, we have two triggers that will handle changes in our movie rental stock each time a movie is rented or returned. Since that same process currently exists in the procedures in our package then we need to make some changes.
To keep from having repetitive processes, and thus have a scenario for generating invalid inventory data, we need to take the processes out of the procedures in the package body by:
1. removing the update statement in the MOVIE_RENT_SP that decreases the quantity by one in the mm_movie table; and
2. removing the update statement in the MOVIE_RETURN_SP that increases the quantity by one in the mm_movie table.

Recompile the package body (you do not have to recompile the package specifications). If you have any errors, debug them and once you have a clean compile, move on to Step 4.

Step 4: Testing
To test your changes, you will only need to test a valid movie rental and a valid movie return. The following steps will help you with the process.
1. Query the mm_movie table to see all data for movie ID 1.
2. Execute the movie_rent_sp procedure in the package and use 1, 13, and 2 for the parameters.
3. Query the mm_movie table to verify the change in quantity for movie ID 1.
4. Query the mm_rental table to get the current rental ID for movie ID 1.
5. Execute the movie_return_sp procedure in the package using the rental ID from Step 4.
6. Query the mm_movie table to verify the change in quantity for movie ID 1.

This concludes the Lab for Week 5.