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".< (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 in the bank.
Q: 10. Sometimes logic for a query can be quite complex. It is possible to produce the desired outcomes…
A: Database: A database is a collection of structured data stored in the computer system. SQL: SQL…
Q: Q: 1: Design a database which shows the relationship between the tables of a customer ordering food…
A: Design a database which shows the relationship Between the tables of a customer ordering food in A…
Q: The InstantRide Management team founded a new team for car maintenance. The new team is responsible…
A: CREATE TABLE MAINTENANCE_TYPES(MAINTENANCE_TYPE_ID char(5) PRIMARY KEY, MAINTENANCE_TYPE_DESCRIPTION…
Q: Perform basic operations (query with/without join, insertion, deletion, etc), explain the execution…
A: SQL(Structured Query Language.):It is a database computer language designed for retrieving and…
Q: Create a BANASTHALI database and an Employee table with following attributes Employee Id, Employee…
A: The above given question is to create a data base and also create a data table in a relational data…
Q: Make a payroll system depending on your designed/created database. The system should be able to…
A: <!DOCTYPE html><html><head><meta name="viewport" content="width=device-width,…
Q: Assume a university database's student table includes StudentID, Major, Age, MaritalStatus, and…
A: Information that has been provided includes a student table that can be found on the university's…
Q: Consider the following schema for a company database and answer the following questions: DEPARTMENT…
A: The question did not say which DBMS platform has to be used.So MS SQL Server has been used to…
Q: A dentist’s office needs to keep information about patients, the number of visits they make to the…
A: ER Diagram: Entity Relation Diagram, as the name said it explains the relationship between…
Q: Database Systems Each branch of the Jaja restaurant has a mainstay menu that is very popular with…
A: In Two phase locking , locking or unlocking is done in Growing phase, in which new locks can be…
Q: Suppose in a movie database, a movie is identified by id, name, year and ratings where a movie might…
A: Answer
Q: The InstantRide Management team founded a new team for car maintenance. The new team is responsible…
A: Hello student I am going to explain you about this question in next step
Q: Consider the following structure of an inventory Oracle database. HUMERC (B) WOWR) VIMOHAR ( S DECL…
A: Neither not we have to select customers not from new york or grade value greater than 100 i.e less…
Q: C# Address Balance Credit Limit Customer Discount EX Description Item# m Order m m Item m QOH Order#…
A: The Objective is to convert the given ER diagram into relational tables and then implement the…
Q: A signed screenshot (or more if needed) with the ERD done in MySQL Workbench You must follow these…
A: The given problem is to be solved in MYSQL WOrkbech where the scenario to school database facts is…
Q: For each item below, specify whether the statement is true (T) or false (F). A Database Schema keeps…
A: A database schema keeps the data in a database- True Each data model is based on a Database…
Q: Data type 1.) Database Design : Take a crack at designing a simple database for 'A Small Dentist…
A: According to the information given:- We have to design database , determine the entities and design…
Q: Consider a database application which maintains the stock (product details) of a Hypermarket. The…
A: As per the requirement, i gave the information that the database application that maintains the…
Q: onsider the following two tables in a banking database application. unt = {AccNumber, Type, Balance}…
A: There are two tables, one is the account and another is the customer. In the account table, there…
Q: We will assume that the Computer Science Department at a university has an agreement with the…
A: ANS 1. The ERD has 3 Entity, relationship has and checkout. The primary key are : num, Id, cid. --…
Q: A relational database has the following schema: Customer (Cno, name, address, age, loan_type,…
A: Sigma operator is used for selecting record. Project operator
Q: All parts of this question use the following database schema: Flights(flno, from, to, distance)…
A: A procedural query language that accepts relation instances as input and returns relation instances…
Q: A car-rental company maintains a database for all vehicles in its current fleet. For all vehicles,…
A: For this problem, table inheritance will be used. Suppose that Color, MyDate, and DriveTrainType are…
Q: The following relational schema form a university database which is implemented in a relational…
A: Here have to determine about sql query for given problem.
Q: Airline Company An airline company is interested in implementing a database system to manipulate the…
A: (1) The database represents each AIRPORT, keeping its unique AirportCode, the AIRPORT Name, and the…
Q: Question & V Saved Problem 4-7 will use the same database Let a database application be Model…
A: The answer is
Q: QUESTION 1 SQL: Consider the following database schema for flight reservation database. A flight…
A: Syntax to create table: CREATE TABLE table_name ( column1 datatype, column2 datatype, .... );…
Q: 1. Display all customers whose email is null or ends with 'fiu.edu’ 2. Display the number of orders…
A: Use LIKE to match the ending of email USE IS NULL to check whether value is null or not SELECT *…
Q: Database Systems: The Complete Book (2nd Edition) :P238 5.4.8 Exercises for Section 5.4 Exercise…
A:
Q: Student Database Management System We are making a project by means of data structure which will…
A: I give the code in C++ as per your requirement along with output and code screenshots
Q: Module 01 Introduction to Database Management Currently, the class price and other fees reside in…
A: Given: A database management system consisting of five relations, such as: 1. Classes 2.…
Q: Write the DDL statements for declaring the Library relational database schema listed below. Specify…
A: Set of statements for the “LIBRARY” relational schema is provided below. CREATE TABLE BOOK ( BookId…
Q: MongoDB ShelI Queries: Suppose that you were asked to create a collection of employees (collection…
A: // first we use the db "company" use company 1. // now create the collection "employee"…
Q: Create crime database ERD using the FOLLOWING ENTITIES ● Criminal • Victim Officer .
A: An Entity Relationship (ER) Diagram is a kind of flowchart that delineates how "elements, for…
Q: lotor Vehicle Dealership Database: Consider Aaron's dealership, a multi-brand vehicle deale atabase…
A: Below is the solution:-
Q: Database Systems: The Complete Book (2nd Edition) :P238 5.4.8 Exercises for Section 5.4 Exercise…
A: As per guidelines, I can answer only first three subparts. Please re-post your pending parts. Below…
Q: Assignment: NBA Database For this assignment you will create a database and C++ program that can…
A: Lets see the solution in the next steps
Q: Consider the following schema for a company database and answer the following questions: DEPARTMENT…
A: Q1. SQL DDL statements CREATE DATABASE EmpDepProjDBGOUSE EmpDepProjDBGO CREATE TABLE…
Q: Assume that you have the following relation; R(a,b,c) a b c ---------- 1 A F 2 B M 3…
A: Degree is the total number of attribute/fields of a relationship/table. cardinality is the total…
Q: 86. Using ODL, you can define which of the following? a. Attribute b. Structure c. Operation d. All…
A: Here is the solution which is mentioned below:
Q: 3. Database management systems branch(branch name, branch.city, assets) customer(customer_name,…
A: Creating Bank database tables using mysql The bank database schema has a combination of…
Q: Respond to one or both of the following statements: It is impossible to produce good information…
A: It is impossible to produce good information from poor data. True. The quality of the data…
Q: The following database is maintained by a bookstore: the database structure is shown below.…
A: CREATE TABLE Author(AuthorName text primary key); INSERT INTO Author VALUES('Author1');INSERT INTO…
Q: The table shown below lists sample dentist/patient appointment data. A patient is given an…
A: To solve this problem, we will be using the rules of all normal forms and remove all those…
Q: ACROSS 4. the logical structure of the entire database (13 characters) 6. simply the way the data…
A: NOTE:- Please provide the grid structure of the puzzle before asking any criss cross puzzles because…
Q: Assume, a sales firm using an automated system in their work. They maintain a database of…
A: The given relation is already in 1NF since the atributes are not multivalued attribute. Ptrimary key…
Trending now
This is a popular solution!
Step by step
Solved in 5 steps
- Alternate keys: Identify at least five keys (not already listed as PK or FK) needed by end users. These indexes would be considered Alternate or Secondary keys and are mostly used for queries and quick reporting. They may contain multiple columns.An insurance company needs to store their salespeople’s information who are selling their insurance policies. They already have a database with multiple tables, one of the tables (Salesperson) stores information about each salesperson along with the bonus percent they receive, based on the city where the insurance is sold. The table has the following fields: Salesperson(spID, spName, spBirthDate,spCitySelling, bonusPercent) spID: Unique identification number of the salesperson. spName: Full name of the salesperson. spBirthDate: Birthdate of the salesperson. spCitySelling: The city in which the salesperson is selling the insurance. bonusPercent: The bonus percent received by the salesperson based on the city in which he/she sells the insurance. Each salesperson can sell the insurance in just one city. However, for a city, there can be more than one salesperson appointed. Also, the bonus percent is fixed for each city. For example, all of the salespeople who sells insurance in…Access Assignment Problem: JMS TechWizards is a local company that provides technical services to several small businesses in the area. The company currently keeps its technicians and clients’ records on papers. The manager requests you to create a database to store the technician and clients’ information. The following table contains the clients’ information. Client Number Client Name Street City State Postal Code Telephone Number Billed Paid Technician Number AM53 Ashton-Mills 216 Rivard Anderson TX 78077 512-555-4070 $315.50 $255.00 22 AR76 The Artshop 722 Fisher Liberty Corner TX 78080 254-555-0200 $535.00 $565.00 23 BE29 Bert's Supply 5752 Maumee Liberty Corner TX 78080 254-555-2024 $229.50 $0.00 23 DE76 D & E Grocery 464 Linnell Anderson TX 78077 512-555-6050 $485.70…
- Database Schema The schema for the Ch07_FACT database is shown below and should be used to answer the next several problems. Click this image to view it in its own tab. FIGURE P7.56 THE CH07_FACT ERD CHECKOUT PATRON PK Check Num PK Pat ID FK1 Book_Num FK2 Pat_ID Check_Out_Date Check_Due_Date Check_In_Date >0-----H- Pat_FName Pat LName Pat_Type BOOK AUTHOR PK Book_Num PK Au ID Book_Title Book_Year Book_Cost Book_Subject FK1 Pat_ID Au_FName Au_LName Au_BirthYear WRITES PK,FK1 Book Num PK,FK2 Au ID The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection of current technology e-books for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book,//Need typed version please You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project. The schema for the database used in this question is as follows: ( primary keys are shown underlined, foreign keys in bold). SUPPLIER (SNo, SupplierName, City) PART (PNo, PartName, Weight) PROJECT (JobNo, JobName, StartYear, Country) SUPPLY (SNo, PNo, JobNo, Quantity) Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols s, P, etc or the words ‘PROJECT’, ‘RESTRICT’ etc . do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the name of any part that was not used on a project that commenced in 2020. List the name of any part that has been supplied to all projects that commenced in 2020.Q2: Design Database for the following scenario and Write SQL queries. Create an ERD for the following scenario. Suppose there is a grocery store near your house. Following can be considered for ERD: A grocery store may have more than one employee. A grocery store has exactly one manager. The manager has one or more sales men working under him. Grocery store has more than one portion for the products. • Each product has a barcode, name, expired date. Many customers can busy many products, but each product is bought by only one customer. Each customer will get an invoice for his /her purchase. The bill invoice has an id.
- Create ERD for Real-estate database: There are Many Cities in each State There are Many Zip Codes in each City Many Houses belong to each ZipCode Each House can be one of HouseTypes ( Town House, Residential, Condo, Single Family) Each House can be Sold multiple times. ( entity SalesCUSTOMER (Cust#, Cname, City)ORDER (Order#, Odate, Cust#, Ord_Amt)ORDER_ITEM (Order#, Item#, Qty)ITEM (Item#, Unit_price)SHIPMENT (Order#, Warehouse#, Ship_date)WAREHOUSE (Warehouse#, City) Given the database model above, propose a solution for the below business requirement:Business requirement: Assume this database belongs to an e-commerce application and the company has several warehouses in different cities. The company wants to optimize its item shipment time. You can propose a series of SQL queries. Explain your answer in details.The relational schema shown below is part of a hospital database. The primary keys are highlighted in bold. Patient (patientNo, patName, patAddr, DOB)Ward (wardNo, wardName, wardType, noOfBeds)Contains (patientNo, wardNo, admissionDate)Drug (drugNo, drugName, costPerUnit)Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) for CONTAINS nad PRESCRIBED table , which one is primary key and how to create table ?
- 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows: PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the identifier of the book copy from the BOOK_COPY table, and the due date.Below is part of a Student database. The primary keys are highlighted in bold. Student (studNo, studName, address, mobileNo) Registration (studNo, courseNo, regDate, semester, session) Course (courseNo, courseName, creditHour, level) Project (projNo, projName, courseNo) Assignment (projectNo, studNo, startDate, dueDate, hoursSpent) INSERT INTO STUDENT VALUES (175,'Ali Ahmad','10 jalan Bukit Bintang','019-123');INSERT INTO STUDENT VALUES (176,'Hanna Syamil','32A Jalan Danau Kota','012-234');INSERT INTO STUDENT VALUES (182,'Ibnu Hassan','19 Jalan Kota Raya','011-345');INSERT INTO STUDENT VALUES (183,'Aliya Hamidi','233 Jalan Matahari ','013-456');INSERT INTO STUDENT VALUES (184,'Kayla Adila','98 Jalan Enau','018-567'); INSERT INTO REGISTRATION VALUES (175,1100,'5/SEP/19',1,'2019/2020');INSERT INTO REGISTRATION VALUES (175,1103,'3/SEP/19',2,'2019/2020');INSERT INTO REGISTRATION VALUES (176,1103,'7/SEP/19',2,'2019/2020');INSERT INTO REGISTRATION VALUES…struct student { char name[20]; char studentID[10]; char phonenum [9]; char advisor [20]; float gpa; } Operations needed: addnewStudent { purpose: to create a new student in the database input: name, studentID.phonenum,advisor.gpa output: none } Name another operation besides add that we could use for our student data structure.