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.

Create ER diagram for the following:

A database has been designed for a Human Resources for a school in the UK. The database includes records of the teachers and their holidays. The Dean of this school has the power to approve those holidays for all teachers in the school.   

For each teacher, Human Resources keeps track of the Teacher's ID, name, Cell phone number(s), total number of holidays for each year, number of unemployed holiday days remaining in the present year, age, date of the employment, Department name.

Some teachers may work as supervisors. For those teachers, as well as the attributes mentioned above, we also keep track of the date when they are supervisors, in addition to the total time they spend to be supervisor and their categorical level (Category 1, Category 2, Category 3, Category 4 or Category 5).

The Human Resources Department in the school keeps track every holiday has been taken by each teacher. For each holiday, The Human Resources keep track, Unique holiday ID, the number of days taken (spent) in this holiday, the start day and end day of a holiday. Sometimes (in a few cases), an alternative phone number may be documented for a holiday.

The Human Resources Department in the school keeps track every reward has been taken by each teacher. For each reward, The Human Resources keep track, Unique Reward ID, the number of rewards, Date of a reward, teacher name and the amount of the reward.

Each holiday is documented for only one teacher. A teacher can take many holidays per year. Each holiday has been approved by many supervisors and each supervisor has the power to approve many holidays for many teachers. Each supervisor has the power to approve the holidays for many teachers and each teacher may have many supervisors and may not. Every supervisor has the power to give many rewards to each teacher, but does not have to reward any. Every teacher can get many rewards every year.

Draw an EER model of given scenario

We wish to create a database for a company that runs training courses. For this, we must store data about the trainees and the instructors. For each course participant (about 5,000),identified by a code, we want to store her social security number, surname, age, sex, place of birth, employer’s name, address and telephone number, previous employers (and periods employed), the courses attended (there are about 200 courses) and the final assessment for each course. We need also to represent the seminars that each participant is attending at present and, for each day, the places and times the classes are held. Each course has a code and a title and any course can be given any number of times. Each time a particular course is given, we will call it an ‘edition’ of the course. For each edition, we represent the start date, the end date, and the number of participants. If a trainee is self-employed, we need to know her area of expertise, and, if appropriate, her title. For somebody who works for a company, we store the level and position held. For each instructor (about 300), we will show the surname, age, place of birth, the edition of the course taught, those taught in the past and the courses that the tutor is qualified to teach. All the instructors’ telephone numbers are also stored. An instructor can be permanently employed by the training company or freelance.        

Note:It will be really helpful if you provide it on a paper by hand.

The following tables form a Library database held in an RDBMS:

Borrower (card_no , last_name , first_name , address , city , state , zip )
Books (ISBN, title, pub_date , pub_id , list_price, category_id, pub_id)
Categories (category_id, category_desc)
Author (author_id , last_name , first_name)
Bookauthor (ISBN, author_id)
Publisher (pub_id, name, contact, phone)
Bookloans (ISBN, branch_id, card_no , date_out, due_date)
Bookcopies (ISBN, branch_id , no_of_copies)
Branch (branch_id, branch_name, city)

Please use the standard join method, no implicit method allowed.

Write SQL statements to perform the following queries:
1. Display the title of each book and the name and phone number of the contact at the publisher’s office for reordering each book.

2. Which books were written by an author with the last name Steven? Perform the search using the author name.

3. Identify the authors of the books Leila Smith borrowed Perform the search using the borrower last name.

4. Display the most recent publication date of all books.

5. Display the list price of the least expensive book in the Computer Science category.

6. What’s the list price of the most expensive book written by Carlos Tim?

7. Display how many times each book title has been borrowed.

8. How many times has the book with ISBN “0401140733” been borrowed?