Question: For each order, display the order ID,

> Which tutor needs to be reminded to turn in reports? Write the SQL query. Show how you constructed this query using a Venn or other type of diagram.

> Which tutors, by name, are available to tutor? Write the SQL query.

> List the total number of lessons taught in 2018 by tutors in each of the three Status categories (Active, Temp Stop, and Dropped).

> For simplicity, subtype discriminators were left off many figures in this chapter. Add subtype discriminator notation in each figure listed below. If necessary, create a new attribute for the discriminator. a. Figure 3-2 b. Figure 3-3 c. Figure 3-4b d. F

> Match the following terms and definitions: - agile software development - database application - constraint - repository - metadata - data warehouse - information - user view - database management system - data independence - entity - enterprise resource

> For each student group, list the number of tutors who have been matched with that group.

> List all active students in June by name. (Make up names and other data if you are actually building a prototype database.) Include the number of hours students received tutoring and how many lessons they completed.

> Write an SQL query to list the Read scores of students who were ever taught by tutors whose status is Dropped.

> Write an SQL query to determine the total number of hours and the total number of lessons Tutor 106 taught in June and July 2018.

> Where do you think student and tutor information such as name, address, phone, and e-mail should be kept? Write the necessary SQL commands to capture this information.

> Write the SQL query to find any tutors who have not submitted a report for July.

> Write a SQL query to identify all students who have been matched in 2018 with a tutor whose status is Temp Stop.

> What do you need to do if a tutor signs up and wants to tutor in both reading and math? Draw the new ERD, create new relations, and write any SQL statements that would be needed to handle this development.

> Write the SQL command to add column SUBJECT to TUTOR. The only values allowed for SUBJECT will be Reading, Math, and ESL.

> Write the SQL command to add column MATH SCORE to the STUDENT table.

> Add a subtype discriminator for each of the supertypes shown in Figure 3-10. Show the discriminator values that assign instances to each subtype. Use the following subtype discriminator names and values: a. PERSON: Person Type (Employee? Alumnus? Student

> Determine the relationships among the four relations in Figure. List primary keys for each relation and any foreign keys necessary to establish the relationships and maintain referential integrity. Pay particular attention to the data contained in TUTOR

> Write an SQL query to answer the following questions: a. Which students were not enrolled in any courses during semester I-2018? b. Which faculty members are not qualified to teach any courses?

> Write SQL queries to answer the following questions: a. What are the names of the course(s) that student Altvater took during the semester I-2018? b. List the names of the students who have taken at least one course that Professor Collins is qualified to

> Write SQL queries to answer the following questions: a. How many students were enrolled in section 2714 during semester I-2018? b. How many students were enrolled in ISM 3113 during semester I-2018?

> Write an SQL query to answer the following question: Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930? If yes, list the faculty ID and name of the instructor.

> Write SQL retrieval commands for each of the following queries: a. Display the course ID and course name for all courses with an ISM prefix. b. Display the numbers and names of all courses for which Professor Berndt has been qualified. c. Display the cla

> Write an SQL query to answer the following question: Which instructors are qualified to teach ISM 3113?

> Tyler Richardson set up a house alarm system when he moved to his new home in Seattle. For security purposes, he has all of his mail, including his alarm system bill, mailed to his local UPS store. Although the alarm system is activated and the company i

> Not all versions of this database include referential integrity constraints for all foreign keys. Use whatever commands are available for the RDBMS you are using, investigate if any referential integrity constraints are missing. Write any missing constra

> List the average number of customers per state (including only the states that are included in the Customer_T table). Hint: A query can be used as a table specification in the FROM clause.

> Examine the hierarchy for the university EER diagram. As a student, you are an instance of one of the subtypes: either UNDERGRAD STUDENT or GRADUATE STUDENT. List the names of all the attributes that apply to you. For each attribute, record the data valu

> For products 1, 2, and 7, list in one row and three respective columns that product’s total unit sales; label the three columns Prod1, Prod2, and Prod7.

> For each customer, list the customer ID, the number of orders from that customer, and the ratio of the number of orders from that customer to the total number of orders from all customers combined. (This ratio, of course, is the percentage of all orders

> For each order, list the order ID, customer ID, order date, and most recent date among all orders. Show how you constructed this query using a Venn diagram.

> List ProductID, ProductDescription, ProductFinish, and ProductStandardPrice for oak products with a ProductStandardPrice greater than $400 or cherry products with a StandardPrice less than $300. Show how you constructed this query using a Venn diagram.

> List MaterialName, Material, and Width for raw materials that are not cherry or oak and whose width is greater than 10 inches. Show how you constructed this query using a Venn diagram.

> For each salesperson, list the total number of orders by month for the year 2018. (Hint: If you are using Access, use the Month function. If you are using Oracle, convert the date to a string, using the TO_CHAR function, with the format string ‘Mon’ [i.e

> Display the SalesPersonID and a count of the number of orders for that salesperson for all salespersons except salespersons 3, 5, and 9. Write this query with as few clauses or components as possible, using the capabilities of SQL as much as possible.

> Display the territory ID and the number of salespersons in the territory for all territories that have more than one salesperson. Label the number of salespersons NumSalesPersons.

> Measured by average standard price, what is the least expensive product finish?

> For employees who live in TN or FL, list the age at which they were hired.

> Select a suitable programming language and outline how a transaction rollback can be coded. Annotate your code to provide clear instructions of what is happening.

> Which product is ordered most frequently?

> List all sales territories (TerritoryID) that have more than one salesperson.

> Assume that for those materials the ID of which starts with a numeric character, the last three letters of the ID represent a wood type. Further, assume that the numeric part of MaterialID (everything except the last three characters) is called material

> For each customer who had more than two orders, list the CustomerID and the total number of orders placed.

> For each salesperson, list the total number of orders.

> For each customer, list the customer ID and the total number of orders placed in 2018.

> For each payment made on or after March 10, 2018, list PaymentID, OrderID, PaymentAmount, and the first 10 characters of PaymentComment.

> Display the product ID and the number of orders placed for each product. Show the results in decreasing order by the number of times the product has been ordered and label this result column NumOrders.

> For each salesperson, display a list of CustomerIDs.

> For each customer, list the CustomerID and total number of orders placed.

> Rewrite the example shown in Figures 7-10 through 7-14 using Java. Data from Figure 7-10: Data from Figure 7-11: Data from Figure 7-12: Data from Figure 7-13: Data from Figure 7-14:

> For every product that has been ordered, display the product ID and the total quantity ordered (label this result TotalOrdered). List the most popular product first and the least popular last.

> Modify query in P&E 5-67 by considering only those products the standard price of which is greater than $200. Include in the answer set only those product lines that have an average standard price of at least $500. Data from P&E 5-67: Display the produc

> Display the product line ID and the average standard price for all products in each product line.

> List the MaterialID, MaterialName, Material, MaterialStandardPrice, and Thickness for all raw materials made of cherry, pine, or walnut. Order the listing by Material, StandardPrice, and Thickness.

> List all raw materials that are made of cherry and that have dimensions (thickness and width) of 12 by 12.

> List the number of customers living at each state that is included in the Customer_T table.

> List the customers who live in California or Washington. Order them by zip code, from high to low.

> Which employees were hired during 2005?

> List the employees whose last names begin with an L.

> Rewrite the example shown in Figure 7-6 using Python or PHP. Data from Figure 7-6:

> Use the Pine Valley database to answer the following questions: a. How many work centers does Pine Valley have? b. Where are they located?

> Add an order to the Order_T table and include a sample value for every attribute. a. First, look at the data in the Customer_T table and enter an order from any one of those customers. b. Enter an order from a new customer. Unless you have also inserted

> Enter sample data of your own choosing into QtyOn Hand in the Product_T table. Test the modification you made in Problem and Exercise 5-57 by attempting to update a product by changing the inventory to 10,000 units. Test it again by changing the inventor

> Modify the Product_T table by adding an attribute Qty OnHand that can be used to track the finished goods inventory. The field should be an integer field of five characters and should accept only positive numbers.

> What is the average length of time a student stayed (or has stayed) in the program?

> How many tutors have an Active status in the database?

> Which tutors have a Dropped status and have achieved their certification after 4/01/2018?

> How long had each student studied in the adult literacy program?

> Show the average, maximum, and minimum Read score per student group.

> Which student has the highest Read score?

> Based on Figure 1-5, what are the four perspectives from which you will explore transactional systems in this book? What are the main competencies associated with each of these perspectives?

> How many students were matched with someone in the first five months of the year?

> What are the TutorIDs for tutors who have not yet tutored anyone?

> List the IDs of the tutors who are currently tutoring more than one student.

> What is the average Read score for all students? What are the minimum and maximum Read scores?

> How many tutors have a status of Temp Stop? Which tutors are active?

> Write SQL queries to answer the following questions: a. For each course included in the QUALIFIED table, list CourseID and the number of faculty members qualified to teach it. b. For each section included in the REGISTRATION table, list SectionNo and the

> Write SQL queries to answer the following questions: a. List the numbers of all sections of course ISM 3113 that are offered during the semester “I-2018.” b. List the course IDs and names of all courses that start with

> Write SQL queries to answer the following questions: a. What are the courses included in the Section table? List each course only once. b. List all students in alphabetical order by StudentName. c. List the students who are enrolled in each course in Sem

> Write SQL queries to answer the following questions: a. Which students are enrolled in Database and Networking? (Hint: Use SectionNo for each class so you can determine the answer from the REGISTRATION table by itself.) b. Which instructors cannot teach

> Write SQL queries to answer the following questions: a. How many students are enrolled in Section 2714 in the first semester of 2018? b. What are the numbers of the faculty members who are currently qualified to teach the course ISM 3113? c. Which facult

> Discuss some of the languages that are associated with Internet application development. Classify these languages according to the functionality they provide for each application. It is not necessary that you use the same classification scheme used in th

> Write SQL queries to answer the following questions: a. Which students have an ID number that is less than 50000? b. What is the name of the faculty member whose ID is 4756? c. What is the smallest section number used in the first semester of 2018?

> Write SQL commands for the following: a. Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the STUDENT table. b. Now write a command that will remove this student from the STUDENT table. c

> Write SQL data definition commands for each of the following queries: a. How would you add an attribute, Class, to the STUDENT table? b. How would you remove the REGISTRATION table? c. What would you need to take into account if you wanted to remove the

> To enforce referential integrity, before any row can be entered into the SECTION table, the CourseID to be entered must already exist in the COURSE table. Write an SQL assertion that will enforce this constraint.

> Use SQL to define the following view:

> Analyze the database to determine whether or not it is fully normalized.

> Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate, or change any data names, as needed for your SQL version). Assume the following attribute data types: StudentID (integer, primary key) StudentName (25 chara

> A university library system is responsible for tracking information about its books and users. At present, it offers lending facilities to over 5,000 students and has a catalog exceeding 1,000 books and periodicals. It is essential for the library to eff

> A price aggregator system provides users with a onestop portal where they can compare the price of products across multiple Web sites. The portal allows users to not only compare prices but also view customer feedback and reviews of the different Web sit

> Jack Patel is a huge comic book fan who is looking to turn his hobby into a business. Spotting a gap in the market, he intends to create a comic rental business to provide locals with access to comics and tap into the growing interest in superheroes. Jac

> Identify some interactive applications around you that require access to a database to fetch content or information. Look for the middleware used in these applications. You may need to interview a systems analyst or database administrator for this.

> The following attributes form a relation that includes information about the issue and return of books by students from a university library. Students of each department in the university are authorized to issue and return the books after a specific time

> Explore the data included in Table 4-9. Assume that the primary key of this relation consists of two components: Author’s ID (AID) and Book number (BNbr). The relation includes data regarding authors, books, and publishers. In addition,

> Figure 4-40 shows an EER diagram for a university dining service organization that provides dining services to a major university. a. Transform the EER diagram to a set of relations and develop a relational schema. b. Diagram the functional dependencies

> How would Problems and Exercises 4-50 and 4-51 change if a type of fish could be supplied by multiple suppliers? Data from Problem and Exercise 4-51: For Problem and Exercise 4-50, draw the ER diagram based on the normalized relations. Data from Proble

> For Problem and Exercise 4-50, draw the ER diagram based on the normalized relations. Data from Problem and Exercise 4-50: A pet store currently uses a legacy flat file system to store all of its information. The owner of the store, Peter Corona, wants

> A pet store currently uses a legacy flat file system to store all of its information. The owner of the store, Peter Corona, wants to implement a Web-enabled database application. This would enable branch stores to enter data regarding inventory levels, o

> Examine the set of relations in Figure 4-39. What normal form are these in? How do you know this? If they are in 3NF, convert the relations into an EER diagram. What assumptions did you have to make to answer these questions?

> Figure includes an EER diagram for a medium-size software vendor. Transform the diagram into a relational schema that shows referential integrity constraints. In addition, verify that the resulting relations are in 3NF.