(a) Let R(A,B,C) and S(B,C,D) be the relations: Compute the following for the relations above: (b) The relational schema for an on-line bookstore is given below. Customer (Custno, Name, Address, State) Book (Bookno, Title, Author, Price) Order (Orderno, Custno, Bookno, ShipDate) Inventory (Bookno, Quantity, Warehouseno, ShelfLocation) Warehouse (Warehouseno, Region) Write relational algebra expressions for the following queries. (i) List the details of the books, with price $20 in the warehouse in Singapore region. The list should include quantity of books available. [5marks] (ii) Find books ordered by customer C200 that has not shipped yet. [5marks] (iii) Find the details of other books purchased by the customers who purchased book Bno12345. [6 marks] (iv) Find the total number of books in each price value. [4 marks] (c) Explain the functions of Client/Server DBMS. [8 marks] (d) Discuss the major changes of Client/Server DBMS functions from traditional database. [8 marks] QUESTION 2 (a) Consider a database with objects x and y. There are two transactions T1 and T2.Transaction T1 reads objects R1(x) and R1(y) and then writes W1(x) and W2(y). Transaction T2 reads objects R2(x) and R2(y) and writes W2(x) and W2(y). Commit and Abort on transactions are given as Commit (1) & Commit (2) and Abort (1) & Abort (2). Identify the concurrency problems associated with each of the following Schedules. Schedules A1 = R1(x) W1(x) Commit(1) R2(x) R2(y) W2(y) W2(x) Commit(2) A2 = W1(x) W2(x) W2(y) W1(y) A3 = R1(x) R2(x) W2(x) Commit(2) R1(x) W1(x) Commit(1) A4 = R1(x) W1(x) R2(x) W2(x) R2(y) Commit(2) Abort(1) [8 marks] (b) There is a concurrency problem in the following schedule. Check whether a 2PL schedule will resolve the problem.B1 = R1(x) R2(x) W1(x) W2(x) [8 marks] (c) Identify whether the following schedules are serializable or not (use the conflict operations available in the given schedule to construct the conflict serialization graph). There are three transactions T1, T2 and T3. (i) C1 = W1[y] R3[x] R2[x] R3[y] W2[x] W3[y] W1[x] [12 marks] (ii) C2 = W1[x] W1[y] R2[x] R3[y] W2[x] W3[y] [8 marks] (d) A database has three relations R1, R2 and R3. They have to be distributed over four sites S1, S2, S3 and S4. Each of the three relations has to be allocated to one of the four sites using the non redundant best fit method. There are only three transactions T1, T2 and T3. The originating sources for each transaction (that is the sites at which they are run) and the frequency with which the transactions run are shown in the table given below. The table also includes the user transaction which are described in terms of frequency of occurrence, which relation they access and whether the accesses are reads or writes. Transaction sources and frequencies are: QUESTION 3 The horizontal fragments of the relation Student and Project are as given below (a) Identify the fragmentation criteria based on which the relations Student and Project have been fragmented. [8 marks] (b) The fragments Student-Basic and Project-1 are allocated to site 1 and the fragments Student-Elective and Project-2 are allocated to site 2. The following SQL query is placed in site 3 and the result is needed in site 3. Select StudentId, Sname, Project-Topic, Role From Student, Project Where Student.StudentId = Project.StudentId And Module = IS352 (i) Identify ANY TWO query strategies for the above query. [16 marks] (ii) Show that ONE of the query strategies is an optimum strategy. [3 marks] (c) Explain how the fragments Project-1 can be further fragmented based on the Module attribute. [6 marks] (d) Making database information available to web users requires converting it from the database format to a mark up language such as HTML or XML. In addition, a bridge between the web and databases needs to be built. Explain any THREE technologies and architectures that may be used to build bridges between web and enterprise database. [9 marks] (e) Discuss any TWO of the following database administration issues with regard to 24*7 databases. • High Availability • Scalability • Performance • Backup and Recovery QUESTION 1 (a) Consider the following relational schema: Employee(Employe-No, Employee-Name, Street, Region, Manager-No) Works(Employee-No, Company-Name, AnnualSalary, Date-of-Joining) Company(Company-Name, Street, Region) Give the relevant Relational Algebra expressions for the following queries. (i) Find the Employee-No, Names, Street and regions of residence of all employees who work for Informatics and earn more than $50,000 per year. [6 marks] (ii) Find the list of employees in the database who do not work in Informatics. [6 marks] (iii) Cambridge University has its own branches located in several regions. Find all companies located in every Region in which Cambridge University is located. [6 marks] (iv) Find the number of employees working in companies in the same region in which they are living. [8 marks] (b) What is meant by optimum query processing in using relational algebra expression? [6 marks] (c) Draw an optimum relational algebra expression query tree for the following SQL query. Select Employee-No, Region, Company-Name, Company.Region From Employee, Works, Company Where Employee.Employee-No = Works.Employee-No and Works.Company-Name = Company.CompanyNo AND Salary > 50000 AND Employee.Region ="West" AND Company.Region "West" QUESTION 2 (a) Given the following schedule, Schedule - 1, show whether there is concurrency problem present in the given schedule with initial value A = 100 and B = 60. [8 marks] (b) In the Schedule - 1, given above, identify the conflict operations and hence draw a precedence graph and find if the schedule is conflict serializable or not. [12 marks] (c) Show what happens if 2PL is used to execute the above schedule. [18 marks] (d) Discuss the architecture of a client-server system. QUESTION 3 (a) A video rental company rents three categories of products: Music albums, Films and Video games. It has four stores in all the four regions: North, South, East and West. It has two warehouses, A and B, from which shipments of the products are done. The relational schema is as given below. PRODUCT(ProdId, Category, Description, Price) INVENTORY(ProdId, StoreRegion, StockOnHand) ORDER(OrderId, ProdId, Warehouse) (i) Give examples of three simple predicates that may be used in horizontal Fragmentation of the Video Rental Company relations for horizontal partitioning. Give an example of horizontal fragment for each predicate. [18 marks] (ii) How would a derived horizontal partitioning for PRODUCT, INVENTORY or ORDER may be defined based on any ONE of the fragments identified in Q3.a. i. [8 marks] (iii) Explain how the PRODUCT relation may be vertically fragmented. [6 marks] (b) In a distributed database, the relations in node 1 and node 2 and application query on Node 3 are as given below. [18 marks] Identify any TWO methods of processing the above query and identify the OPTIMUM method among the two methods (to minimize the cost). Assume that the major cost is involved only in shipping the data among the nodes and is proportional to the bytes of data to be transferred (that is, other cost are negligible). [TOTAL FOR QUESTION 3: 50 MARKS] 2013-12: QUESTION 1 (a) Consider the following database at carsales.com, owned by a company in Singapore that sells used cars. CUSTOMER (Cid, CName,CStreet, CRegion) CAR (CarRegdNo, OwnerName, Price, Model, Manufacturer) SALES (SaleNo, Cid, CarRegdNo, SaleDate, PaymentMode) OWNER (OwnerName, OwnerStreet, OwnerRegion) Write the following queries in relational algebra expressions. (i) List the cars sold, manufactured by Ford Company. [5 marks]