Write a Java program (use JDBC to connect to the database) that implements the following function (written in pseudo code): (20 points) CALL RECURSION ( GIVENP# ) ; RECURSION: PROC ( UPPER_P# ) RECURSIVE ; DCL UPPER_P# ... ; DCL LOWER_P# ... INITIAL ( ' ' ) ; EXEC SQL DECLARE C CURSOR FOR SELECT MINOR_P# FROM PART_STRUCTURE WHERE MAJOR_P# = :UPPER_P# AND MINOR_P# > :LOWER_P# ORDER BY MINOR_P# ; print UPPER_P# ; DO "forever" ; EXEC SQL OPEN C ; EXEC SQL FETCH C INTO :LOWER_P# ; EXEC SQL CLOSE C ; IF no "lower P#" retrieved THEN RETURN ; END IF ; IF "lower P#" retrieved THEN CALL RECURSION ( LOWER_P# ) ; END IF ; END DO ; END PROC ; Given the value of the input parameter ‘P1’, it should print out the following sequence (in the exact same order) for the table in Q1: P1 P2 P3 P5 P6 P4 P5 P6 P3 P5 P6

Case Study 4

A relational database is to be designed for a medium sized Company dealing with industrial applications of computers. The Company delivers various products to its customers ranging from a single application program through to complete installation of hardware with customized software. The Company employs various experts, consultants and supporting staff. All personnel are employed on long‐ term basis, i.e. there is no short‐term or temporary staff. Although the Company is somehow structured for administrative purposes (that is, it is divided into departments headed by department managers) all projects are carried out in an inter‐disciplinary way. For each project a project team is selected, grouping employees from different departments, and a Project Manager (also an employee of the Company) is appointed who is entirely and exclusively responsible for the control of the project, quite independently of the Company's hierarchy.

Description: The following is a brief statement of some facts and policies adopted by the Company.

• Each employee works in some department.

• An employee may possess several skills

• Every manager (including the MD) is an employee

• A department may participate in none/one/many projects.

• At least one department participates in a project.

• An employee may be engaged in none/one/many projects

• Project teams consist of at least one member.

A publisher needs to create a database based on the following requirements:

The publisher publishes different books. Each book has an ISBN, name, author(s) name, edition number, category, and price. Each book has a unique ISBN.

The publisher deal with many authors, each has a name, unique number.

For every book, each author will have different percentage of revenue.

They also hire editors, who edit books before publishing. Each has name, unique number, SSN, salary, book category and list of books they edited.

Draw a complete ER diagram. Write the (min,max), total/partial and the cardinality ratio.

Here is listing of the tables for the database for this assignment.

SELLERS(SellerNum, SellerLastName, SellerFirstName, SellerStreet, SellerCity, SellerState, SellerZip, CRate)

BUYERS(BuyerNum, BuyerLastName, BuyerFirstName, BuyerStreet, BuyerCity, BuyerState, BuyerZip, BonusLevel, SellerNum)

PRODUCTS(ProductNum, Description, NumberInStock)

STATEMENT(StatementNum, StatementDate, BuyerNum)

STATEMENTLINE(StatementNum, ProductNum, Quantity, ItemPrice)

Using the above database’s tables, respond to the following questions:

1. Write SQL CREATE TABLE statements for each of the above tables. Be sure to include the Primary and Foreign keys.

Note: The id numbers for each table should be a numeric (integer) value, the commission rate (Crate) and the ItemPer (individual quoted price per item) should be numeric with 2 decimal places, and the quantity (quantity of the products ordered) and NumberInStock (number of items in stock) should be numeric (integer) values. Additionally, the states should use the common 2 character state abbreviation and the statement date should be a date value. All other values are character values.

2. Write SQL statements to insert onecomplete row of data into each table. You make up the values that go in the rows.
3. Write SQL statements to list all columns of each table -- one statement per table.
4. Write a SQL statement to list all of the sellers (first and last names and commission rates) that have commission rates of 4.25% (as a decimal value) or higher
5. Write a SQL statement to list all of the buyers (first and last names) that have a bonus level of “Green”.
6. Write a SQL statement to list the all of the Arkansas buyers’ first and last names in alphabetical order.
7. One of the sellers has a last name of “Lee”, another has the last name of “Caswell”. Write a SQL statement to list all of the buyers (first and last names) that have been assigned to them.
8. One of the buyers has the last name of “Gadea”. Write a SQL statement to list the descriptions of all of the items that she purchased.

1. Create an example of what a form in a database could look like. The form should have 5 fields to get the following information (Music ID, Album Name, Medium Type, Artist Name, and Release Year.) (You can sketch it using Paint, draw it on paper and scan it, use shapes in Word, use wireframe.cc and take a screenshot of the result and turn that in...sky's the limit)
2. Create an example report (same method used as the example form). This report display the information Music ID, Album Name, Medium Type, Artist Name, and Release Year in an organized manner with titles.
3. Create a table named tblMusic.
4. In the table, add fields called MusicID, AlbumName, MediumType, ArtistName, ReleaseYear.
5. Make MusicID the primary key. The MusicID should be simply a number (you can have it auto-generate, if you wish).
6. Create a form named frmMusic. This form should have 5 required fields.
7. Use the form to input five CDs, records, or other types of musical media. You can make up names or use real albums.
8. Create a report that displays the information from the tblMusic in an attractive and clear manner. Make sure all items are labeled and are visible. Name it rptMusic.

1. For each of the following, write a single SELECT query against the TSQLV4 database that returns the result set described. Each of these queries involves two tables and can be written using a join operation.

a. One row for each order shipped to France or Germany, showing the order ID, the last name of the employee for the order, and the customer ID for the order.

b. One row for each employee who handled orders to Belgium, showing the employee’s initials (for example, for an employee named Yael Peled, this would be YP) and the number of orders that employee handled that were shipped to Belgium.

c. Same as part b., but include a row for every employee in the HR.Employees table, even if they did not handle any orders shipped to Belgium. (The number of orders for such an employee should be 0.)

You are to design a database for the upcoming Greater Wisconsin River Fishing Contest. Consider the following functional dependencies;

• SportsPersonId --> SportsPersonFirstName, SportsPersonLastName
• SportsPersonId --> Address
• SportsPersonId --> BoatId
• BoatId --> MotorMake
• FishId --> SpeciesOfFish, Weight, Length
• SportsPersonId + FishId --> DateTimeCaught

Please note the following;

1. This is a “catch and release” contest meaning that once a fish is caught, the specifics are collected, the fish is safely tagged with a unique id, and then is released back to the river. One fish might be caught by numerous sports persons but a sports person can only register the catch of a fish no more than one time.
2. Each Sports Person must be assigned a boat and a boat can be assigned to multiple sports persons.

Create an ERD in 3NF for these relations. Create appropriate table names for the ERD. Include all applicable fields based on information you have been given and underline the Primary or Composite Primary Key fields. Make sure to include the correct ERD diagram symbols between tables to show the proper relationship type (e.g. 1:1, 1:M, etc.)

Web Programming Task + Database sql

• Write a command to display the ISBN number and the price in the table books.
  • Question #2: What command did you enter?
  • Answer:
• Write a command to increase the price of each book in the table books by 50 cents.
  • Question #3: What command did you enter?
  • Answer:

• Write a command to display the price and title in the table books of ISBN 0-672-31697-8
  • Question #4: What command did you enter?
  • Answer:
• Write a command to change the price in the table books of ISBN 0-672-31697-8 to 25.00.
  • Question #5: What command did you enter?
  • Answer:

• Write a command to insert yourself into the table customers. Remember customerid is autoincrement.
  • Question #6: What command did you enter?
  • Answer:
• Write a command to delete yourself from the table customers.
  • Questin #7: What cmmand did you enter?
  • Answer:

JAVA FILE PROGRAM

Write a contacts database program that presents the user with a menu that allows the user to select between the following options:

• Save a contact.
• Search for a contact.
• Print all contacts out to the screen.
• Quit

If the user selects the first option, the user is prompted to enter a person's name and phone number which will get saved at the end of a file named contacts.txt.
If the user selects the second option, the program prompts the user asking for the name of the contact. It then searches the contacts.txt for a matching name. If found, it displays the phone number on the screen. If not found, it will display an appropriate error message.
If the user selects the third option, the program displays all contacts stored in contacts.txt in a neat table.
The program is menu driven and will repeat presenting the menu and processing choices until the user selects the fourth option to quit.
If the user selects an invalid option, an appropriate error message should be displayed.
If the user selects to print all contacts to the screen with no stored contacts, an appropriate error message should be displayed.

You may use message dialogs or make it a purely console-based application

Question #1

Data Modelling is the primary step in the process of database design. Compare and contrast Conceptual data model versus Physical data model. Illustrates with help of example to list down data (entities), relationship among data and constraints on data.

Question #2

What strategic competitive benefits do you see in a company’s use of extranets?

Question #3

Explain how Internet technologies are involved in developing a process in one of the functions of the business? Give an example and evaluate its business value.

Question #4

What are the basic differences between HRM, Intranet and Internet in terms of Domain and Network Communication Scope?

All information are written. there is no more information.