Question: Write SQL commands for the following: a.
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. How would your command look like if your task was to remove any student with the last name Lopez from the STUDENT table? d. Create an SQL command that will modify the name of course ISM 4212 from Database to Introduction to Relational Databases. 
> 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 each order, display the order ID, the number of separate products included in the order, and the total number of product units (for all products) ordered.
> 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 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.
> Figure includes an EER diagram describing a publisher specializing in large edited works. Transform the diagram into a relational schema that shows referential integrity constraints. In addition, verify that the resulting relations are in 3NF.
> Figure includes an EER diagram describing a car racing league. Transform the diagram into a relational schema that shows referential integrity constraints. In addition, verify that the resulting relations are in 3NF.
> Search the Internet for some examples of dynamic Web sites other than e-commerce sites. What are the possible limitations of a dynamic Web site compared to a static Web site?
> Transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the relations to third normal form.
> Figure 4-35 shows an EER diagram for Vacation Property Rentals. This organization rents preferred properties in several states. As shown in the figure, there are two basic types of properties: beach properties and mountain properties. a. Transform the EE
> Table 4-8 shows extracts from a customer’s flight booking confirmation. The booking is identified by the booking reference, which in turn identifies the customer id, their flight origin, and final destination. This booking reference als
> The materials manager at Pine Valley Furniture Company maintains a list of suppliers for each of the material items purchased by the company from outside vendors. Table 4-7 shows the essential data required for this application. a. Draw a dependency diag
> The Public Safety office at Millennium College maintains a list of parking tickets issued to vehicles parked illegally on the campus. Table 4-6 shows a portion of this list for the fall semester. (Attribute names are abbreviated to conserve space. a. Con
> Transform Figure 2-15a, attribute version, to 3NF relations. Transform Figure 2-15b, relationship version, to 3NF relations. Compare these two sets of 3NF relations with those in Figure 4-10. What observations and conclusions do you reach by comparing th
> For your answers to the following Problems and Exercises from prior chapters, transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the relations to third normal form. a. Chapter 3, Problem and
> Transform the relational schema developed in Problem and Exercise 4-37 into an EER diagram. State any assumptions that you have made. Data from Problem and Exercise 4-37: Table 4-5 shows a shipping manifest. Your assignment is as follows: a. Draw a rela
> Table 4-5 shows a shipping manifest. Your assignment is as follows: a. Draw a relational schema and diagram the functional dependencies in the relation. b. In what normal form is this relation? c. Decompose MANIFEST into a set of 3NF relations. d. Draw a
> Table 4-4 shows a relation called GRADE REPORT for a university. Your assignment is as follows: a. Draw a relational schema and diagram the functional dependencies in the relation. b. In what normal form is this relation? c. Decompose GRADE REPORT into a
> Suppose your university is offering some courses in business analytics: a six-month certificate course, a two-year regular program, and a three-year part-time program. You are required to design a Web form in HTML that takes students’ names, email addres
> Define each of the following terms: a. data b. information c. metadata d. enterprise resource planning e. data warehouse f. constraint g. database h. entity i. database management system j. data lake k. systems development life cycle l. prototyping m. en
> Suppose that in addition to the operations read and write, we allow an operation prod read(r, P), which reads all tuples in relation r that satisfy predicate P. a. Give an example of a schedule using the prod read operation that ex- habits the phantom ph
> List the ACID properties. Explain the usefulness of each.
> Give an example of an expression defining a materialized view and two situations (sets of statistics for the input relations and the differentials) such that incremental view maintenance is better than recompilation in one situation, and computation is bet
> Describe how to incrementally maintain the results of the following operations on both insertions and deletions: a. Union and set difference. b. Left outer join.
> Consider the query Show how to decor relate this query using the multistep version of the semi join operation.
> Suppose two relations r and shave histograms on attributes r. A and s. A, respectively, but with different ranges. Suggest how to use the histograms to estimate the size of r ⋈ s. Hint: Split the ranges of each histogram further.
> 16.20 Explain how to use a histogram to estimate the size of a selection of the form σ a ≤v(r).
> A set of equivalence rules is said to be complete if, whenever two expressions are equivalent, one can be derived from the other by a sequence of uses of the 16.2.1 complete? Hint: Consider the equivalence σ3=5(r) ≡ { }.
> Consider the two expressions σ θ (E1 ⟕ E2) and σ θ (E1 ⋈ E2). a. Show using an example that the two expressions are not equivalent in general. Give a simple condition on the predicate θ, which if satisfied will ensure that the two expressions are equivale
> Discuss the relative merits of imperative, functional, and declarative languages.
> Consider the following proposed rule for functional dependencies: If α → β and γ → β, then α → γ. Prove that this rule is not sound by showing a relation r that satisfies α → β and γ → β, but does not satisfy α → γ.
> Show how to derive the following equivalences by a sequence of transformations using the equivalence rules in Section 16.2.1. a. σθ1 ∧θ2 ∧θ3 (E) ≡ σθ1 (σθ2 (σθ3 (E))) b. σ θ ∧θ (E1 ⋈θ E2) ≡ σ θ (E1 ⋈θ (σ θ (E2))), where θ2 involves only 1 2 3 1 3 2
> Suppose you have an update query U. Give a simple sufficient condition on U that will ensure that the Halloween problem cannot occur, regardless of the execution plan chosen or the indices that exist.
> Consider a relation r (A, B, C), with an index on attribute A. Give an example of a query that can be answered by using the index only, without looking at the tuples in the relation. (Query plans that use only the index, without accessing the actual rela
> Suppose you want to get answers to r ⋈ s sorted on an attribute of r, and want only the top K answers for some relatively small K. Give a good way of evaluating the query: a. When the join is on a foreign key of r referencing s, where the foreign key att
> Suppose that a B+-tree index on (dept. name, building) is available on relation Depart mint. What would be the best way to handle the following selection? Σ (building
> Suppose you need to sort relation r using sort—merge and merge—join the result with an already sorted relation s. a. Describe how the sort operator is broken into sub operators to model the pipelining in this case. // b. The same idea is applicable even
> Explain how to split the hybrid hash-join operator into sub-operators to model pipelining. Also explain how this split is different from the split for a hash-join operator.
> Write pseudo code for an iterator that implements a version of the sort – merge algorithm where the result of the final merge is pipelined to its consumers. Your pseudo code must define the standard iterator functions open (), next (), and close (). Show w
