Explain the distinction between total and partial constraints.
> The organization of parts, chapters, sections, and subsections in a book is related to clustering. Explain why, and to what form of clustering.
> Suppose half of all the transactions in a clothes shop purchase jeans, and one- third of all transactions in the shop purchase T-shirts. Suppose also that half of the transactions that purchase jeans also purchase T-shirts. Write down all the (nontrivial
> Construct a schema diagram for the bank database of Figure 2.18.
> Consider the star schema from Figure 11.2. Suppose an analyst finds that monthly total sales (sum of the price values of all sales tuples) have decreased, instead of growing, from April 2018 to May 2018. The analyst wishes to check if there are specific it
> Consider each of the takes and teaches relations as a fact table; they do not have an explicit measure attribute, but assume each table has a measure attribute rig count whose value is always 1. What would the dimension attributes and dimension tables be
> Why is column-oriented storage potentially advantageous in a database system that supports a data warehouse?
> Explain how multiple operations can be executed on a stream using a publish subscribe system such as Apache Kafka.
> Suppose a stream can deliver tuples out of order with respect to tuple times- tamps. What extra information should the stream provide, so a stream query processing system can decide when all tuples in a window have been seen?
> Fill in the blanks below to complete the following Apache Spark program which computes the number of occurrences of each word in a file. For simplicity we assume that words only occur in lowercase, and there are no punctuation marks. Java RDD text File =
> Although SQL does not support functional dependency constraints, if the database system supports constraints on materialized views, and materialized views are maintained immediately, it is possible to enforce functional dependency constraints in SQL. Giv
> The map-reduce framework is quite useful for creating inverted indices on a set of documents. An inverted index stores for each word a list of all document IDs that it appears in (offsets in the documents are also normally stored, but we shall ignore them
> Suppose your company has built a database application that runs on a centralized database, but even with a high-end computer and appropriate indices created on the data, the system is not able to handle the transaction load, leading to slow processing of
> One of the characteristics of Big Data is the variety of data. Explain why this characteristic has resulted in the need for languages other than SQL for processing Big Data.
> 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 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 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
> 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).
