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.

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 System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

SQL and Relational Algebra Related question There is a Company Database that manages the data of employee, department and project. The relational schemas are as follows: EMPLOYEE (Gname, Fname, Employee_id, Bdate, Gender, Salary, Dno). The attribute means: given name, family name, employee id, birthdate, gender, salary, the department number in which the employee works. Each employee has a unique employee id, and can join in projects that in different department. DEPARTMENT (Dno, Dname, Manager_id, Dloc). The attribute means: Department number, department name, department manager id, department location. The department manager is a type of the employee, thus the Manager_id is a foreign key that references the Employee_id in EMPLOYEE table. PROJECT (Project_id, Pname, DNO). The attribute means: project id, project name, department number of the department that the project belongs to. Part of the data in the relations are as follows： Table1 EMPLOYEE Gname Fname Employee_id…
branch(branchhame, branch+citysassets} customer (HD, Customer name, customer street, customer city) loan loan number branch_name, amounty borrower(ID; loan_number account (account_number, branch name, balance)e depositor (HDs account number)- Figure le Consider the bank database in Figure 1, where the primary keys are underlined. Each branch might have many loans or accounts, associated with borrowers or depositors, respectively. Construct the following SQL queries for this relational database. 1. Find the name of each branch that has at least one customer who has an account in the bank and who lives in the "Harrison" city. Make sure each branch name only appears once. 2. Find the ID of each customer who lives on the same street and in the same city as customer "12345". (1) Please use "tuple variables". e (2) Please use "derived relations" or “with". 3. Find the ID of each customer of the bank who has an account but not a loan. 4. Find the total sum of all loan amounts for each branch…
Suppliers (sid, sname, city) Parts (pid, pname, color) Catalog (sid, pid, price) Write relational algebra expressions for the following queries: Find the names of supplier who supply part name “fog light”. Find the sids of suppliers who supply every part.
Course: Database *(SQL)* Given the following schemas for a student record: STUDENTS (student_id, student_name, region)COURSES (course_id, course_name, subject_type, lecturer_name)RESULTS (student_id, course_id, score, result_status) STUDENTS relation shows the information of the students. The COURSE relation specifies the course details. The RESULTS relation lists the scores that students obtain for each course taken. *Please execute this SQL codes for creating the table schemas mentioned above, Then execute the tasks in the table accordingly using proper SQL queries and Write your answer into the Answer Template CREATE TABLE STUDENTS (student_id VARCHAR(10) PRIMARY KEY, student_name VARCHAR(150), region VARCHAR(50)); CREATE TABLE COURSES (course_id VARCHAR(10) PRIMARY KEY, course_name VARCHAR(150), subject_type VARCHAR(50), lecturer_name VARCHAR(150)); CREATE TABLE RESULTS (student_id VARCHAR(10), course_id VARCHAR(10), score NUMBER, result_status VARCHAR(20), CONSTRAINT pk_results…
branch(branch name, branch city, assets) customer (customer name, customer street, customer city) loan (loan number, branch name, amount) borrower (customer name, loan number) account (account number, branch name, balance) depositor (customer name, account number) Give an expression in the relational algebra for each of the following queries: Find all loan numbers with a loan value greater than $50,000. b. Find the names of all depositors who have an account with a value greater than $9,000. Find the names of all depositors who have an account with a value greater than $8,000 at the “Uptown” branch.
2) FLIGHT (passenger, phone#, flight#, seat#, departure-place, departure-time) The values of attributes of a relational table FLIGHT satisfy the following properties: a passenger has one phone number, a passenger books seats on many different flights, the attributes flight#, seat#, and departure place uniquely identify each passenger, only one flight takes of from a given departure place at a given departure time. (registration# , type, owner, driver, date-used) 3) TRUCK The values of attributes of a relational table TRUCK satisfy the following properties: a truck has a unique registration number, a truck has only one owner, a truck is used only once per day, an owner owns many trucks and driver uses many trucks. 4) EMPLOYE (employee#, skill, project) The values of attributes of a relational table EMPLOYEE satisfy the following properties: an employee has many skills, an employee participates in many projects, a skill is possessed by many employees, a project is implemented by many…
Database system concept 7th Exercise problem 3.19) List two reasons why null values might be introduced into the database. Exercise problem 3.20) Show that, in SQL, <> ALL is identical to NOT IN.
2. (Short Answer, 10.0points) employee (employee_name, street, city) works (employee_name, company name, salary) company (company name, city) manages (employee_name, manager_name) Figure 3.20 Employee database for Exercises 3.9, 3.10, 3.16, 3.17, and 3.20. Consider the relational database of Figure 3.20. Give an expression in SQL for each of the following queries. a. Modify the database so that "Jones" now livesin "Newtown". b. Give all managers of "First Bank Corporation" a 10 percent raise unless the salary becomes greater than $100,000; in such cases, give only a 3 percent raise.
Formulate the following queries in: Relational Algebra. The following tables form part of a database held in an RDBMS: Employee (empNo, fName, lName, address, DOB, sex, position, salary, deptNo) Department (deptNo, deptName, mgrEmpNo) Project (projNo, projName, deptNo) WorksOn (empNo, projNo, dateWorked, hoursWorked) where Employee contains employee details and empNo is the key. Department contains department details and deptNo is the key. mgrEmpNo identifies the employee who is the manager of the department. There is only one manager foreach department. Project contains details of the projects in each department and the key is projNo (notwo departments can run the same project). WorksOn contains details of the hours worked by employees on each project, and empNo/ projNo/dateWorked form the key. Formulate the following queries in: Relational Algebra. List all the details of employees who are female and born after 1990. List employee numbers and names of all employees who are not…
Movie(ID, title, rating, genre)Primary key(ID)Constraint genre in (‘Classic’, ‘Horror’, ‘Action’, ‘Comedy’, ‘Foreign’, ‘Educational’, ‘Others’) write a table for this in SQL
Formulate the following queries in: Relational Algebra. The following tables form part of a database held in an RDBMS: Employee (empNo, fName, lName, address, DOB, sex, position, salary, deptNo) Department (deptNo, deptName, mgrEmpNo) Project (projNo, projName, deptNo) WorksOn (empNo, projNo, dateWorked, hoursWorked) where Employee contains employee details and empNo is the key. Department contains department details and deptNo is the key. mgrEmpNo identifies the employee who is the manager of the department. There is only one manager foreach department. Project contains details of the projects in each department and the key is projNo (notwo departments can run the same project). WorksOn contains details of the hours worked by employees on each project, and empNo/ projNo/dateWorked form the key. Formulate the following queries in: Relational Algebra. Produce a list of the names of all employees who work on the Biometric project. Produce a report of all projects under the IT…
Doctor (SSN, FirstName, LastName, Specialty, Years0fExperience, PhoneNum) Patient(SSN, FirstName, LastName, Address, DOB, PrimaryDoctor_SSN) Medicine(TradeName, UnitPrice, GenericFlag) Prescription(Id, Date, Doctor_SSN, Patient_SSN) Prescription Medicine(Prescription Id, TradeName, NumofUnits) a. Write a relational algebra expression to list patient's first name, patient's last name, and primary doctor's first name of all patients. b. Write a relational algebra expression to list all the prescriptions given by each doctor. Select TradeName, doctor's first name. c. Write a relational algebra expression to list the first and last name of patients who have no prescriptions written by doctors other than their primary doctors. d. Write SQL statement to retrieve the number of patients that each doctor has written prescriptions for. You must displav the doctor's first name along with the number of prescriptions he/she wrote. e. Write the DDL (SQL) statement to create Prescription_Medicine…