Search results for 'im 300'

CIS515 Assignment 2: Database Systems and Database Models

The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server.

Imagine that you have been hired as a consultant to assist in streamlining the data processing of an international based organization that sells high-end electronics. The organization has various departments such as payroll, human resources, finance, marketing, sales, and operations. The sales department is the only department where employees are paid a commission in addition to their yearly salary and benefits. All other departments compensate their employees with a yearly salary and benefits only. Commission is paid by multiplying the employee’s commission rate by the total amount of product units sold. You have access to the following data sets:

Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID)
Invoice (InvNumber, InvDate, EmpNumber, InvAmount)
InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity)
Product (ProductNumber, ProductDescription, ProductCost)
Department (DepartmentID, DepartmentDescription)
Job (JobID, JobDescription)

Write a two to three (2-3) page paper in which you:

Design a query that will allow the finance department to determine the commissions paid to specific employees of the sales department for the month of December. Note: You will need to generate the tables described above (Employee, Invoice, InvoiceLine, Product, Department, and Job) in order to compare and validate your code. Validated query code must be part of your paper.
Compare the code of the query you designed in Question one (1) to one that would show how much total compensation is paid to each employee for the same month.
Determine and explain the factors necessary to ensure referential integrity.
Create an object-oriented model to show how the tables are interrelated through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Make sure that you are able to show the relationship types such as 1:M, 1:1, or M:1. Additionally, remember to include the determined factors from the previous assignment requirement. Note: The graphically depicted solution is not included in the required page length.
Identify which data components are the entities and attributes, and the relationship between each using an object representation diagram through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length.
Describe how Big Data could be used to assist in the productivity and forecasting of the organization’s products and resources.

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.

GU CS371 Database Design Week 4 Assignment Chapter 5 Relational Database Model

The assignment comes from Chapter 5 Minicase 1 but includes an additional step:
Using MySQL Workbench, create the relational data model for the database for happy cruise lines, which includes 6 tables. (Note: Be sure primary and foreign keys are correct). Export the PDF version of your model for submission.
Then complete steps a – g. Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
You may submit a single word doc with SQL queries and your model pasted in – OR – submit 2 separate files including a PDF of your model and a word doc with your queries.

Fundamentals of Database Management Systems 2nd Edition Chapter 5 Minicase 1
1. Consider the following relational database for Happy Cruise Lines. It keeps track of ships, cruises, ports, and passengers. A “cruise” is a particular sailing of a ship on a particular date. For example, the seven-day journey of the ship Pride of Tampa that leaves on June 13, 2009, is a cruise. Note the following facts about this environment.
Both ship number and ship name are unique in the SHIP Relation.
A ship goes on many cruises over time. A cruise is associated with a single ship.
A port is identified by the combination of port name and country.
As indicated by the VISIT Relation, a cruise includes visits to several ports, and a port is typically included in several cruises.
Both Passenger Number and Social Security Number are unique in the PASSENGER Relation. A particular person has a single Passenger Number that is used for all of the cruises that she takes.
The VOYAGE Relation indicates that a person can take many cruises and a cruise, of course, has many passengers.

SHIP Relation
Ship Number
Ship Name
Ship Builder
Launch Date
GrossWeight

CRUISE Relation
Cruise Number
Start Date
End Date
Cruise Director
Ship Number

PORT Relation
Port Name
Country
Number of Docks
Port Manager

VISIT Relation
Cruise Number
Port Name
Country
Arrival Date
Departure Date

PASSENGER Relation
Passenger Number
Passenger Name
Social Security Number
Home Address
Telephone Number

VOYAGE Relation
Passenger Number
Cruise Number
Stateroom Number
Fare

Additional step:
Using MySQL Workbench, create the relational data model for the database for happy cruise lines, which includes 6 tables. (Note: Be sure primary and foreign keys are correct). Export the PDF version of your model for submission.

Then complete steps a – g.
Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
a. Identify the candidate keys of each relation.
b. Identify the primary key and any alternate keys of each relation.
c. How many foreign keys does each relation have?
d. Identify the foreign keys of each relation.
e. Indicate any instances in which a foreign key serves as part of the primary key of the relation in which it is a foreign key. Why does each of those relations require a multi-attribute primary key?
f. Identify the relations that support many-to-many relationships, the primary keys of those relations, and any intersection data.
g. Using the informal relational command language described in this chapter, write commands to:
Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
i. Retrieve the record for passenger number 473942.
ii. Retrieve the record for the port of Nassau in the Bahamas.
iii. List all of the Ships built by General Shipbuilding, Inc.
iv. List the port name and number of docks of every port in Mexico.
v. List the name and number of every ship.
vi. Who was the cruise director on cruise number 38232.
vii. What was the gross weight of the ship used for cruise number 39482?
viii. List the home address of every passenger on cruise number 17543.

GU CS371 Database Design Week 7 Assignment Chapter 8 Final Project

Fundamentals of Database Management Systems 2nd Edition
Questions and Task:
Question 1
In your own words discuss the benefits of normalization.

Question 2
Do you think we should normalize our designs to higher levels than 3NF? Why or why not?

Question 3
There are 14 physical database design techniques discussed in chapter 8. The goal of a quality physical design is to improve performance while disrupting the logical design as little as possible. Pick a technique, which you believe to be the most beneficial in accomplishing the above goal, and explain your reasoning.

Question 4
Discuss the concept of an index and explain how they improve performance. Assignment "Final Project Overview" You just took a job with a University and have been asked to track some information about the courses they offer. The previous employee was using a spreadsheet to track this information. The University is expanding from three classes to three hundred classes. Enrollment is expected to increase from around 20 students to 5,000. Your supervisors are not very tech savvy and they simply ask you to continue maintaining the spreadsheet. Review the spreadsheet labeled final_project.xlsx.

Explain the issues that exist with maintaining the data in its current form.
Explain the process of migrating the data in its current form to a well formed data model by highlighting the following in detail:
Identify required attributes that need to be tracked
Identify functional dependencies Show the redesign in 1NF, 2NF, and 3NF (similar to figures 7.29, 7.31, 7.33)
Explain the benefits of the data in its new form.
Create the E-R diagram of your relational tables using MySQL Workbench.
Provide the SQL statement required to create at least one of your tables.
Provide the SQL statement required to delete at least one of your tables.
Provide the SQL statement required to create a view which includes Course #, Course Name, Time, and Days only.
Identify at least one index, which you believe would improve performance.
Explain your choice.
Propose some additional data items, which you believe may be beneficial to the University if tracked in your database (i.e. Descriptions, more info about the instructors and students, departments, etc).
Describe the steps required to implement these additions.
If you add a course description attribute to your course table, there may be some performance impacts imposed by the addition of this text field.
Discuss a physical design technique to improve the performance of the new course description field.

ITCO333 Data Modeling and Design Unit 2 Individual Project
Deliverable Length: 1 Word document and 1 .accdb file

Details: Create a SQL Server database called: ITCO333Database. Using Data Definition Language (DDL) and Data Manipulation Language (DML) you will create the below DEPARTMENTS and EMPLOYEES tables. Before you begin creating the new tables, review your tables created in Unit 1 IP to ensure that your database is in third normal form (3NF).

Part 1:
Your ITCO333 database should contain data related to the organizational departments in your Unit 1 chosen topic. Therefore, create a DEPARTMENTS table with the following field specifications:
Field Name Data Type Other Comments and Requirements
Department_ID   int Primary Key
Department_Name nvarcha(50)
Insert at least four records of sample data into the DEPARTMENTS table.

Your ITCO333 database should contain data related to employees in your Unit 1 chosen topic. Therefore, create an EMPLOYEES table with the following field specifications:
Field Name Data Type Other Comments and Requirements
Employee_ID Int Primary Key Last_Name nvarchar(50) Cannot be null.
First_Name nvarchar(50) Cannot be null.
Birth_Date datetime
Employment_Start_Date datetime
Hourly_Pay decia(p[,s]) Must be greater that 0
Department_ID Int Related to the DEPARTMENTS table.
Create a foreign key constraint.
Manager_ID Int Related to the Employee_ID in this table.
Create a foreign key constraint.
Insert at least eight records of sample data into the EMPLOYEES table.

Part 2: Using your Unit 1 ERD, create tables, fields, primary keys and relationship constraints in your ITCO333Database.
Insert your Unit 1 sample data into the newly created tables.

Part 3: Generate a SQL Server Database Diagram.

Additional Requirements: All tables must be in Third Normal Form (3NF). This may require you to normalize your Unit 1 data. Be sure to incorporate Instructor feedback from your Unit 1 IP. Use the following data types:
Integers: int
Decimals: decimal(p[,s])
Strings: nvarchar(50)
Date/Time: datetime

Deliverable: One Word document with:
SQL DDL to create database
SQL DDL to create tables (including EMPLOYEES and DEPARTMENTS tables)
SQL DML to insert data (including EMPLOYEES and DEPARTMENTS sample data)
SQL Server Database Diagram

The aforementioned SQL DML must be in text format (no screen shots).

“Martial Arts R Us” (MARU) needs a database. MARU is a martial arts school with hundreds of students. It is necessary to keep track of all the different classes that are being offered, who is assigned to teach each class, and which students attend each class. Also, it is important to track the progress of each student as they advance.
Create a complete Crow’s Foot ERD for these requirements:
• Students are given a student number when they join the school. This is stored along with their name, date of birth, and the date they joined the school.
• All instructors are also students, but clearly, not all students are instructors. In addition to the normal student information, for each instructor, the date that they start working as an instructor must be recorded, along with their instructor status (compensated or volunteer).
• An instructor may be assigned to teach any number of classes, but each class has one and only one assigned instructor. Some instructors, especially volunteer instructors, may not be assigned to any class.
• A class is offered for a specific level at a specific time, day of the week, and location. For example, one class taught on Mondays at 5:00 pm in Room #1 is an intermediate-level class. Another class taught on Mondays at 6:00 pm in Room #1 is a beginner-level class. A third class taught on Tuesdays at 5:00 pm in Room #2 is an advanced-level class.
• Students may attend any class of the appropriate level during each week so there is no expectation that any particular student will attend any particular class session. Therefore, the actual attendance of students at each individual class meeting must be tracked.
• A student will attend many different class meetings; and each class meeting is normally attended by many students. Some class meetings may have no students show up for that meeting. New students may not have attended any class meetings yet.
• At any given meeting of a class, instructors other than the assigned instructor may show up to help. Therefore, a given class meeting may have several instructors (a head instructor and many assistant instructors), but it will always have at least the one instructor that is assigned to that class. For each class meeting, the date that the class was taught and the instructors’ roles (head instructor or assistant instructor) need to be recorded. For example, Mr. Jones is assigned to teach the Monday, 5:00 pm, intermediate class in Room #1. During one particular meeting of that class, Mr. Jones was present as the head instructor and Ms. Chen came to help as an assistant instructor.
• Each student holds a rank in the martial arts. The rank name, belt color, and rank requirements are stored. Each rank will have numerous rank requirements. Each requirement is considered a requirement just for the rank at which the requirement is introduced. Every requirement is associated with a particular rank. All ranks except white belt have at least one requirement.
• A given rank may be held by many students. While it is customary to think of a student as having a single rank, it is necessary to track each student’s progress through the ranks. Therefore, every rank that a student attains is kept in the system. New students joining the school are automatically given a white belt rank. The date that a student is awarded each rank should be kept in the system. All ranks have at least one student that has achieved that rank at some time.

Create ER Diagram with the following information:

Gamer -user name, password, email, first name and last name; User name is unique; Can create multiple characters
Character -name is unique. Needs to show current level, exp points, health points, magic points, strength, intelligence, dexterity, belong ONLY ONE class. 3 classes: warrior, mage, ranger
Items (for character)- name, type, value, status (0 to 100, 100 is new and 0 is broken), 3 Items (weapon, armor, accessory) Many Character can have the same item of same name, but not same individual item
Weapon - type, speed, damage
Armor - defense value, type
Accessory - type, special effect
Character Skill - name, Magic Point cost of skill, level requirement, description (multiple characters can have same skill and can have multiple skills) (Some skills have prereqs)
Player vs Player - each match has: score of each character, list of match history (only 2 players in match)
Guild - name of guild, time created, current status, list of characters in guild

Murach’s SQL for SQL Server Chapter 10 How to create and maintain databases and tables with SQL statements
Exercises
1. Create a new database named Membership.
2. Write the CREATE TABLE statements needed to implement the following design in the Membership database. Include reference constraints. Define IndividualID and GroupID with the IDENTITY keyword. Decide which columns should allow null values, if any, and explain your decisions. Define the Dues column with a default of zero and a check constraint to allow only positive values.
3. Write the CREATE INDEX statements to create a clustered index on the GroupID column and a nonclustered index on the IndividualID column of the GroupMembership table.
4. Write an ALTER TABLE statement that adds a new column, DuesPaid, to the Individuals table. Use the bit data type, disallow null values, and assign a default Boolean value of False.
5. Write an ALTER TABLE statement that adds two new check constraints to the Invoices table of the AP database. The first should allow (1) PaymentDate to be null only if PaymentTotal is zero and (2) PaymentDate to be not null only if PaymentTotal is greater than zero. The second constraint should prevent the sum of PaymentTotal and CreditTotal from being greater than InvoiceTotal.
6. Delete the GroupMembership table from the Membership database. Then write a CREATE TABLE statement that recreates the table, this time with a unique constraint that prevents an individual from being a member in the same group twice.