1) Create a table “college” that has as attributes name, city name, state/province/region name, country name, and year in which the college was established as well as an ID as primary key. Insert records for 5 colleges of your choice, including one or more you have attended.

2) Create a table “student” that has as attributes first name, last names, and college ID for students, and insert the names of yourself and friends who attended one or more colleges together with you (if you have only attended one college, the name can be the same for all). Note that the student names can be fictitious but not the college name.

3) Add a foreign key to the appropriate table above, using “on delete cascade” as referentially triggered action, and demonstrate that insertion for a student record with a non-existing college ID fails.

4) Do a query that shows all students together with their respective college information. For colleges, that no students have attended, list all student information as null (i.e. OUTER JOIN).

5) Do a query that lists college names together with the number of students in the database that have attended that college, using the GROUP BY statement.

6) Do a query of catalog information.

7) Do a deletion of a college that was referenced and redo the query from question 4.

A young couple buying their first home borrow $65,000 for 30 years at 7.4%, compounded monthly, and make payments of $450.05. After 5 years, they are able to make a one-time payment of $2000 along with their 60th payment.

(a) Find the unpaid balance immediately after they pay the extra $2000 and their 60th payment. (Round your answer to the nearest cent.) $

(b) How many regular payments of $450.05 will amortize the unpaid balance from part (a)? (Round your answer to the nearest whole number.) -this answer is in payments not $

(c) How much will the couple save over the life of the loan by paying the extra $2000? (Use your answer from part (b). Round your answer to the nearest cent.) the answer is not  2395.

A company produces plastic powder in lots of 2000 pounds at the beginning of each week. The company uses the powder in an injection moulding process at the steady rate of 50 pounds per hour for an eight-hour day, five days a week. The manager has indicated that the cost of placing an order is $100, but “we really have not determined what the holding cost is”. (1) What weekly holding cost rate does the lot size imply, assuming the lot size 2000 is optimal? (2) Suppose the figure you compute for weekly holding cost rate has been shown to the manager, and the manager says that it is not that high. Would that mean the lot size 2000 is too large or too small? Explain in at most two sentences

1.

The average price of a gallon of gasoline (regular unleaded) in 1976 was $0.64. In 1998 it was $1.06. The CPI factor in 1976 is 56.9 and the CPI factor in 1998 is 163.0.   Convert the 1976 price to constant 1998 dollars. Was gas more expensive in 1976 or in 1998? Choose two correct answers.

Question 3 options:

2.

In 1989, red delicious apples cost on average $0.57 per pound. In 1998, they cost $0.94 per pound. Were apples more expensive in 1998 or in 1989? The CPI value in 1989 is 124.0 and the CPI value in 1998 is 163.0. Convert the 1989 price to constant 1998 dollars. Were apples more expensive in 1989 or in 1998? Choose two correct answers.

Question 4 options:

3.

In 1948, the President of the United States earned a salary of $75,000. In 2000, the President earned a salary of $400,000. Knowing that the CPI for 1948 is 24.1 and the CPI for 2000 is 172.2, convert the 1948 salary to constant 2000 dollars. When comparing constant dollar amounts, whose salary was worth more--Harry Truman, President in 1948, or Bill Clinton, President in 2000?   Choose two correct answers.

Question 5 options:

Every relation in an ER diagram must translate to an individual relation in the resulting relational database schema.

True/ Flase

For any SQL query, there exist a unique translation into relational algebra.

True or False

IF a relation schema is in 3NF, it is in BCNF.

True or false

Explain what is meant by the time value of money, and discuss its relevance to the capital budgeting process.

Please type out and use a different answer in your own words that differ from what's already on the Chegg database, please and thank you!

IT-344: Database Management Systems
please No handwriting
thank you

Topic of Discussion
According to the conflicts and failure in transactions processing, the need of concurrency control and recovery appeared.
Discuss the differences between concurrency control and recovery in terms of:
The purposes.
The algorithms.
The problems.

Project 28: Daily Invoice Foothills Animal Hospital is a full-service small animal veterinary hospital located in Morrison, Colorado, specializing in routine medical care, vaccinations, laboratory testing, and surgery. The hospital has experienced tremendous growth over the past six months due to customer referrals. While Foothills Animal Hospital has typically kept its daily service records in a workbook format, it feels the need to expand its reporting capabilities to develop a relational database as a more functional structure. Foothills Animal Hospital needs help developing a database, specifically: ? Create a customer table—name, address, phone, and date of entrance. ? Create a pet table—pet name, type of animal, breed, gender, color, neutered/spayed, weight, and comments. ? Create a medications table—medication code, name of medication, and cost of medication. ? Create a visit table—details of treatments performed, medications dispensed, and date of the visit. ? Produce a daily invoice report. Figure AYK.2 displays a sample daily invoice report that the Foothills Animal Hospital accountants have requested. Foothills Animal Hospital organizes its treatments using the codes displayed in Figure AYK.3 . The entities and primary keys for the database have been identified in Figure AYK.4 . The following business rules have been identified: Project Focus Your job is to complete the following tasks: 1. Develop and describe the entity-relationship diagram.2.create an Access database, populate it, and create any queries, forms, and/or reports as per the instructions in AYK 28

(All tables are created and populated,All Relationships are set between tables,query has been created to pull the specific data from the tables to create the report (Customer Name, Pet Name, Type of Animal, Treatment, and Price),This criterion is linked to a Learning OutcomeCreate a report that is something similar to Figure AYK.2 on page AYK.16 of the textbook)

Company XYZ owns a social network web site dedicated for wind surfers. They plan to launch a new online ad delivery and management service web site that will be coupled with their existing social network platform. The online ad management service will enable them to accept online orders for online ads from advertisers. The system will also deliver the ads to the main social network site. The ads will be displayed on the social network site but the content of the ads (the images and/or text objects containing the ad message) will be served by the new web site server. The company forecasts that the web site will receive an average of 10,000 visits from potential or current advertisers and 600 online ad purchase orders per week. They expect to have 30,000 registered online advertisers during the first month of the launch of the web site. Company XYZ dedicate a separate server and database for the new online service. The users of the ad management site and the users of the social network will be managed separately. The data of the two groups will be stored on separate databases. Similarly, web ad management site content and the social network site content will be hosted on separate servers. Company XYZ prefers to manage those two sites separately. The social network site will pull online ads from the database of the online ad management system. The users will experience a seamless presentation of ads within the social network pages without noticing the ads are fed from a different server.

Company XYZ awarded a contract to your company (Company ABC) to develop the web site, web service, and the database that will provide the capability of the online ad delivery and management service. You are the project manager of this project. Your task is to manage a team of three (one graphic designer, one database developer, and one software developer). As the project manager you are required to answer the following questions that provide insight about the project plan.

Explain how the online ads will be stored and served.

Create your own data. Ensure the data will test all criteria and prove all sorts worked as required. Be sure that all tables and queries have descriptive names. “Query 1” and “Step 1” are examples of poor names.

Step 1

Build a Database named DBMS Course Project. The database should include the following tables:

• First Table – Students
  • ID
  • First Name
  • Last Name
  • Address
  • City
  • State
  • Zip Code
  • Phone Number with area code
• Second Table – GPA
  • ID Current GPA (two decimal positions)
  • Class (numeric field, 1 = Freshman, 2 = Sophomore, etc.)
    • Freshman
    • Sophomore
    • Junior
    • Senior
    • Graduate Student
• Third Table – Scholarships
  • ID
  • Major course of study
  • Academic Scholarship (Use Yes/No data type)
  • Athletic Scholarship (Use Yes/No data type)

Step 2

Extract the First and Last Name, Address, City, State, Zip Code and Phone Number of each Senior on the database. Sort by Last Name, then First Name (1 sort).

Step 3

Extract the First and Last Name, and GPA of each student who qualifies for the Dean’s List. Sort by GPA, then Last Name, then First Name (1 sort). A GPA of 3.25 is required to make the Dean’s List.

PART 2

Build a query of all students on athletic scholarship. Additionally, produce a report with descriptive report and column headings. Be sure there is enough data to prove the selection and sort worked as required. Submit the database in unzipped format as well as submitting screenshots of the query and the report.

Step 1

Extract the First and Last Name, Student ID, Course Major and GPA of all students on athletic scholarship. Sort by Course Major, Last Name, then First Name in a single sort.

Step 2

Produce a report with the data from Step 1 and use good headings.