04 (10 Marks) Consider the Banking database: (Answer 2 only) branch (branch-name, branch-city, assets) customer (customer-name, customer-street, customer-only) account (account-number, branch-name, balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-number) borrower (customer-name, loan-number) A) Use the Relational Algebra to Explain the following queries: 1. Gamount>1200 (loan) 2. Ioan-number (amount > 1200 (loan)) 3. Icustomer-name (borrower) Icustomer-name (depositor) B) Use the UML to represent the Banking database. C) Use SQL to write 4 different queries from Banking database.

Database Design   [Image below]   Before essential oil products are marketed, testing is carried out based on the production batch of each essential oil. This is done to maintain the quality and content of the constituents in the essential oil. Where the Test Percentage Content must reach the Minimum Percentage Content standard for each constituent. If there is one type of constituent that does not meet the minimum standards, the batch will not be marketed because it does not meet the quality standard. 1. If recording the results of each testing process is still done manually, in your opinion, please explain which approach is suitable between Centralized and View Integration for collecting requirements. 2. What Fact Finding methods will you choose to obtain information related to the development of a database system to record the testing process, and explain the reasons for choosing it.

1) Find names of those departments, each of which has at least two same-aged employees. 2)List names of those departments each of which has an employee named ‘Joe’. 3)List the names of employees, each of whom has salary more than the budget of the department he/she works in. Answer the above questions in relational algebra with reference to the following relational schema. Key fields are bold. Emp (eid:integer, ename:string, age:integer, salary:real)Works (eid:integer, did:integer, pct_time:integer)Dept (did:integer, dname:string, budget:real)

For the following Relational Algebra / SQL statements, specify the output of each query. (do not explain how each query will be executed, but specify the output to be obtained when the query is executed). (c) R1: ACTORS WHERE YearOfBirth < 1940 OR Gender = 'female' R2(SSNo, FName, LName): R1 [ SSNo, FName, LName ] R3: R2 NATURAL-JOIN ACTEDIN OVER SSNo    C: R3 [ LName, Character ]   (d)  SELECT A1.FName, A1.LName, A1.YearOfBirth FROM ACTORS A1, ACTORS A2 WHERE A1.YearOfBirth = A2.YearOfBirth AND                        A1.SSNo != A2.SSNo   (e) R1: ACTEDIN [ FilmNo, SSNo ] R2: FILMS WHERE FilmTitle = 'Unforgiven' [ FilmNo ] R3: ACTEDIN NATURAL-JOIN R2 OVER FilmNo [ SSNo ] R4: ACTORS NATURAL-JOIN R3 OVER SSNo [ SSNo, FName, LName ] R5: ( R1.SSNo ÷ R4.SSNo ) [ FilmNo ]   C: ACTEDIN NATURAL-JOIN R5 OVER FilmNo [ FilmTitle ]   (f)  SELECT A.FName, A.LName, COUNT(*) FROM ACTORS A, ACTEDIN AI WHERE A.SSNo = AI.SSNo GROUP BY A.SSNo, A.FName, A.LName HAVING count(*) > 2 OR count(*) = 0