Question: Consider the relational schema / / Where the
Consider the relational schema
Where the primary-key attributes are underlined. A tuple (p1, p2, 3) in the subpart relation denotes that the part with part id p2 is a direct subpart of the part with part id p1, and p1 has 3 copies of p2. Note that p2 may itself have further subparts. Write a recursive SQL query that outputs the names of all subparts of the part with part-id 'P-100'.
> Give four ways in which information in web logs pertaining to the web pages visited by a user can be used by the web site.
> Consider the bank database of Figure 2.18. Assume that branch names and customer names uniquely identify branches and customers, but loans and accounts can be associated with more than one customer. a. What are the appropriate primary keys? b. Given your
> What is multifactor authentication? How does it help safeguard against stolen passwords?
> a. What is an XSS attack? b. How can the referrer field be used to detect some XSS attacks? XSS attacks:
> Many web sites today provide rich user interfaces using Ajax. List two features each of which reveals if a site uses Ajax, without having to look at the source code. Using the above features, find three sites which use Ajax; you can view the HTML source o
> Explain the terms CRUD and REST.
> Write pseudo code to manage a connection pool. Your pseudo code must include a function to create a pool (providing a database connection string, database user name, and password as parameters), a function to request a connection from the pool, a connect
> Normalize the following schema, with given constraints, to 4NF.
> What is an SQL injection attack? Explain how it works and what precautions must be taken to prevent SQL injection attacks.
> Explain why 4NF is a normal form more desirable than BCNF.
> Given a relational schema r (A, B, C, D), does A →→ BC logically imply A →→ B and A →→ C? If yes prove it, or else give a counter example.
> Give a lossless, dependency-preserving decomposition into 3NF of schema R of Exercise 7.1.
> Given the three goals of relational database design, is there any reason to design a database schema that is in 2NF, but is in no higher-order normal form? (See Exercise 7.19 for the definition of 2NF.)
> Write a servlet that authenticates a user (based on user names and passwords stored in a database relation) and sets a session variable called use rid after au- then taxation.
> In designing a relational database, why might we choose a non-BCNF design?
> List the three design goals for relational databases, and explain why each is desirable.
> Show that every schema consisting of exactly two attributes must be in BCNF regardless of the given set F of functional dependencies.
> Although the BCNF algorithm ensures that the resulting decomposition is loss- less, it is possible to have a schema and a decomposition that was not generated by the algorithm that is in BCNF, and is not lossless. Give an example of such a schema and its
> Consider the schema R = (A, B, C, D, E, G, and H) and the set F of functional dependencies: Use the 3NF decomposition algorithm to generate a 3NF decomposition of R, and show your work. This means: a. A list of all candidate keys b. A canonical cover for
> Consider the schema R = (A, B, C, D, E, and G) and the set F of functional dependencies: Use the 3NF decomposition algorithm to generate a 3NF decomposition of R, and show your work. This means: a. A list of all candidate keys b. A canonical cover for F,
> Explain why No SQL systems emerged in the 2000s, and briefly contrast their features with traditional database systems.
> Consider the schema R = (A, B, C, D, E, and G) and the set F of functional dependencies: a. Find a nontrivial functional dependency containing no extraneous at- tributes that is logically implied by the above three dependencies and ex- plain how you foun
> Consider the schema R = (A, B, C, D, E, G) and the set F of functional dependencies: R is not in BCNF for many reasons, one of which arises from the functional dependency AB → CD. Explain why AB → CD shows that R is no
> Consider the following set F of functional dependencies on the relation schema (A, B, C, D, E, and G): a. Compute B+. b. Prove (using Armstrong’s axioms) that AG is a super key. c. Compute a canonical cover for this set of functional de
> Write a servlet and associated HTML code for the following simple application: A user is allowed to submit a form containing a number, say n, and should get a response saying how many times the value n has been submitted previously. The number of times e
> Give a lossless decomposition into BCNF of schema R of Exercise 7.1.
> Design a database for an automobile company to provide to its dealers to assist them in maintaining customer records and dealer inventory and to assist sales staff in ordering cars. Each vehicle is identified by a vehicle identification number (VIN). Each i
> Consider the E-R diagram in Figure 6.30, which models an online bookstore. a. Suppose the bookstore adds Blu-ray discs and downloadable video to its collection. The same item may be present in one or both formats, with differing prices. Draw the part of
> Construct appropriate relation schemas for each of the E-R diagrams in: a. Exercise 6.1. b. Exercise 6.2. c. Exercise 6.3. d. Exercise 6.15.
> We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets?
> Consider two entity sets A and B that both have the attribute X (among others whose names are not relevant to this question). a. If the two X s are completely unrelated, how should the design be improved? b. If the two X s represent the same property and
> Explain the difference between a weak and a strong entity set.
> List two features developed in the 2000s and that help database systems handle data-analytics workloads.
> Extend the E-R diagram of Exercise 6.3 to track the same information for all teams in a league.
> Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.
> Explain what a challenge– response system for authentication is. Why is it more secure than a traditional password-based system?
> Explain the distinction between total and partial constraints.
> Explain the distinction between disjoint and overlapping constraints.
> Design a generalization– specialization hierarchy for a motor vehicle sales company. The company sells motorcycles, passenger cars, vans, and buses. Justify Your placement of attributes at each level of the hierarchy. Explain why they s
> In Section 6.9.4, we represented a ternary relationship (repeated in Figure 6.29a) using binary relationships, as shown in Figure 6.29b. Consider the alternative shown in Figure 6.29c. Discuss the relative merits of these two alternative representations
> Design a database for an airline. The database must keep track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routing of future flights. Your design should include an E-R diagram,
> Design a database for a worldwide package delivery company (e.g., DHL or FedEx). The database must be able to keep track of customers who ship items And customers who receive items; some customers may do both. Each package must be identifi
> Explain the distinctions among the terms primary key, candidate key, and super key.
> The execution of a trigger can cause another action to be triggered. Most database systems place a limit on how deep the nesting can be. Explain why they might place such a limit.
> Explain the difference between two-tier and three-tier application architectures. Which is better suited for web applications? Why?
> Suppose there are two relations r and s, such that the foreign key B of r references the primary key A of s. Describe how the trigger mechanism can be used to implement the on delete cascade option when a tuple is deleted from s.
> Hackers may be able to fool you into believing that their web site is actually a web site (such as a bank or credit card web site) that you trust. This may be done by misleading email, or even by breaking into the network infrastructure and rerouting net
> Redo Exercise 5.12 using the language of your database system for coding stored procedures and functions. Note that you are likely to have to consult the online Documentation for your system as a reference, since most systems use syntax diï¬
> Consider the relational schema from Exercise 5.16. Write a JDBC function using non recursive SQL to find the total cost of part “P-100”, including the costs of all its subparts. Be sure to take into account the fact that a part may have multiple occurrenc
> Consider an employee database with two relations Where the primary keys are underlined. Write a function avg salary that takes a company name as an argument and finds the average salary of employees at that company. Then, write an SQL stat
> Repeat Exercise 5.13 using ODBC, defining void print Table (char *r) as a function instead of a method.
> Suppose you were asked to define a class Meta Display in Java, containing a method static void print Table(String r); the method takes a relation name r as input, executes the query “select * from r”, and prints the result out in tabular format, with the
> Consider the relation, r, shown in Figure 5.22. Give the result of the following query:
> Consider the nice relation of Exercise 5.9. For each month of each year, show the total monthly dollar volume and the average monthly dollar volume for that month and the two prior months. (Hint: First write a query to find the total dollar volume for eac
> Given relation s (a, b, c), write an SQL statement to generate a histogram show- ing the sum of c values versus a, dividing a tin to 20 equal-sized partitions (i.e., where each partition contains 5 percent of the tuples in s, sorted by a).
> Assume that two students are trying to register for a course in which there is only one open seat. What component of a database system prevents both students from being given that last seat?
> Consider the advisor relation shown in the schema diagram in Figure 2.9, with s id as the primary key of advisor. Suppose a student can have more than one advisor. Then, would s I d still be a primary key of the advisor relation? If not, what should the
> Modify the recursive query in Figure 5.16 to define a relation Prefer depth (course id, prefer id, depth) Where the attribute depth indicates how many levels of intermediate prerequisites there are between the course and the prerequisite. Direct prerequis
> Write a Java program that allows university administrators to print the teaching record of an instructor. a. Start by having the user input the login ID and password; then open the proper connection. b. The user is asked next for a search substring and t
> Show how to express the coalesce function using the case construct.
> For the view of Exercise 4.18, explain why the database system would not allow a tuple to be inserted into the database through this view.
> Show how to define a view to credits (year, numb credits), giving the total number of credits taken in each year.
> Under what circumstances would the query Include tuples with null values for the title attribute?
> For the database of Figure 4.12, write a query to find the ID of each employee with no manager. Note that an employee may simply have no manager listed or may have a null manager. Write your query using an outer join and then write it again using no outer
> Express the following query in SQL using no sub queries and no set operations.
> Write an SQL query using the university schema to find the ID of each student who has never taken a course at the university. Do this using no sub queries and no set operations (use an outer join).
> Rewrite the query Select * From section natural join classroom Without using a natural join but instead using an inner join with a using condition.
> Suppose you wish to create an audit trail of changes to the takes relation. a. Define triggers to create an audit trail, logging the information into a relation called, for example, takes trail. The logged information should include the user-id (assume a
> List at least two reasons why database systems support data manipulation using a declarative query language such as SQL, instead of just providing a library of C or C++ functions to carry out data manipulation.
> Explain the difference between integrity constraints and authorization con- strains.
> Suppose a user creates a new relation r1 with a foreign key referencing another relation r2. What authorization privilege does the user need on r2? Why should this not simply be allowed without any such authorization?
> Suppose user A, who has all authorization privileges on a relation r, grants select on relation r to public with grant option. Suppose user B then grants select on r to A. Does this cause a cycle in the authorization graph? Explain why.
> Explain why, when a manager, say Satoshi, grants an authorization, the grant should be done by the manager role, rather than by the user Satoshi.
> Consider the query Explain why appending natural join section in the from clause would not change the result.
> List two reasons why null values might be introduced into the database.
> Give an SQL schema definition for the employee database of Figure 3.19. Choose an appropriate domain for each attribute and an appropriate primary key for each relation schema. Include any foreign-key constraints that might be appropriate.
> Consider the employee database of Figure 3.19. Give an expression in SQL for each of the following queries. a. Give all employees of “First Bank Corporation” a 10 percent raise. b. Give all managers of “First Bank Corporation” a 10 percent raise. c. Dele
> Consider the employee database of Figure 3.19, where the primary keys are underlined. Give an expression in SQL for each of the following queries. a. Find ID and name of each employee who lives in the same city as the location of the company for which th
> What are two advantages of encrypting data stored in the database?
> Consider the bank database of Figure 3.18, where the primary keys are under- lined. Construct the following SQL queries for this relational database. a. Find each customer who has an account at every branch located in “Brook- Lyn”. b. Find the total sum
> List five responsibilities of a database-management system. For each response ability, explain the problems that would arise if the responsibility were not dis- charged.
> Consider the insurance database of Figure 3.17, where the primary keys are underlined. Construct the following SQL queries for this relational database. a. Find the number of accidents involving a car belonging to a person named “John Smith”. b. Update t
> Write SQL DDL corresponding to the schema in Figure 3.17. Make any reason- able assumptions about data types, and be sure to declare primary and foreign keys.
> Write the SQL statements using the university schema to perform the following operations: a. Create a new course “CS-001”, titled “Weekly Seminar”, with 0 credits. b. Create a section of this course in fall 2017, with sec id of 1, and with the location o
> Using the university schema, write an SQL query to find the name and ID of each History student whose name begins with the letter ‘D’ and who has not taken at least five Music courses.
> Using the university schema, write an SQL query to find the names and IDs of those instructors who teach every course taught in his or her department (i.e., every course that appears in the course relation with the instructor’s department name). Order res
> Using the university schema, write an SQL query to find the IDs of those students who have retaken at least three distinct courses at least once (i.e., the student has taken the course at least two times).
> Using the university schema, use SQL to do the following: For each student who has retaken a course at least twice (i.e., the student has taken the course at least three times), show the course ID and the student’s ID. Please display your results in orde
> Using the university schema, write an SQL query to find the names of those departments whose budget is higher than that of Philosophy. List them in al- phonetic order.
> Consider the Oracle Virtual Private Database (VPD) feature described in Sec- ton 9.8.5 and an application based on our university schema. a. What predicate (using a sub query) should be generated to allow each faculty member to see only takes tuples corr
> Using the university schema, write an SQL query to find the name and ID of those Accounting students advised by an instructor in the Physics department.
> With dept. total (dept. name, value) as (select dept. name, sum (salary) from instructor Group by dept. name), dept. total avgas (value) as (Select avgas (value) from dept. total) Select dept. name From dept. total, dept. total avgas Where dept. total. V
> Explain the concept of physical data independence and its importance in database systems.
> Rewrite the where clause Where unique (select title from course) Without using the unique construct.
> Choose an enterprise of personal interest to you and explain how block chain technology could be employed usefully in that business.
> Explain how off-chain transaction processing can enhance throughput. What are the trade-offs for this benefit?
> Why is Byzantine consensus a poor consensus mechanism in a public block chain?
> How is the difficulty of proof-of-work mining adjusted as more nodes join the network, thus increasing the total computational power of the network? De- scribe the process in detail.
> Consider the library database of Figure 3.20. Write the following queries in SQL. a. Find the member number and name of each member who has borrowed at least one book published by “McGraw-Hill”. b. Find the member number and name of each member who has b
> Suppose a user forgets or loses her or his private key? How is the user affected?
