For the below 'bank' schema: customer(customerid, username,fname, Ina me,street1,street2,city, state,zip) account(accountid,customerid,description,) transaction(transactionid,trantimestamp,acc ountid,amount) A customer may have several accounts, and each account may participate in many transactions. Each transaction will have at least two records, one deducting amount from an account, and one adding amount to an account (for a single transactionid, the sum of amounts will equal zero). Using SQL, answer this question (write a SQL query that answers this question): 8. List the top 1% of customers (ordered by total balance).

For the below 'bank' schema: customer(customerid, username,fname, Ina me,street1,street2,city, state,zip) account(accountid,customerid,description,) transaction(transactionid,trantimestamp,acc ountid,amount) A customer may have several accounts, and each account may participate in many transactions. Each transaction will have at least two records, one deducting amount from an account, and one adding amount to an account (for a single transactionid, the sum of amounts will equal zero). Using SQL, answer this question (write a SQL query that answers this question): 8. List the top 1% of customers (ordered by total balance).

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

FACULTY (Faculty Code, Faculty Name) DEPARTMENT (Dept_ID, Dept_Name, Faculty_Code*) LECTURER (Lect_ID, Lect_Name, Lect_Qualification, Dept_ID*) FULL TIME LECTURER (Lect ID*, Expertise, HireDate) PART TIME LECTURER (Lect ID*, HoursWork, RatePerHour) a) Based on the above relational schema you are required to draw the Entity Relationship Diagram (ERD). b) Do the relational schema above exhibits referential integrity? Answer yes or no and then explain your answer. TABLE REFERENTIAL INTEGRITY EXPLANATION FACULTY DEPARTMENT LECTURER FULL_TIME_LECTURER PART_TIME_LECTURER
Create a diagram for a database with at least 5 tables. This should show your table names and column names. Show the relationships between the tables. Write a statement explaining your database design (why did you use the tables that you did). Create a database in Access based on your diagram. Include all tables in your diagram. Tables should each have a minimum of 5 rows. Create 5 SQL statements using your database. At least 2 of these should be manipulation statements (insert, update, etc). The others should be select statements. Explain what your sql statements do and how they would be useful to your database.
Topic: Database Design Provide an example of a 3 table OUTER join. Explain how it works and what the order of of the joins is.
The following relational schema forms part of an Employees database. The primary keys are underlined and foreign keys are in italics. Job (jobID, jobTitle, minSalary, maxSalary) Dependent (dependentID, firstName, lastName, relationship, employeeID) Department (departmentID, departmentName, locationID) Location (locationID, street, postcode, city, state) Employee (employeeID, firstName, lastName, phoneNumber, salary, jobID, departmentID) Employee employeeID works as jobID at a department departmentID located at locationID. The employee has dependent(s) dependentID. Question 1 Begins Here 1. List employee names, department names, and their dependent names. Also, include employees without any dependents.
entities: Residential students which fields like student id, Student name, exam papersTransfer students with fields like student id, transferred school, exam papers etc.Exam papers with fields like subjects, total marks, invigilator, checkers.Exam center with fields like Center Id, location, seating capacity etc. -Create an ER Diagram using the enumerated entities.
The Plaza is an apartment rental company that would like to create an online management system to keep track of apartment complex units, tenants, maintenance requests, and rental payment information. Since you are taking a database class, you are asked to assist the company in designing a database based on the following specifications. Each apartment unit has a unique number.Each unit's street number,street name, city, state zip code, number of bedrooms, monthly rent, and occupancy status are recorded. Each tenant must lease at least one apartment unit for a period of time. It is important to document the lease start date and end date. The Plaza tracks each tenant's name, SSN, home mailing address, and phone number. Each tenant is assigned a unique identifier number. Each tenant should pay the monthly rental fee by the first day of each month.Any rent due not paid by the 4th day of the monthly rental period is subject to a $50.00 late fee charge with an additional $10.00 for…
You receive the following requirements for a caterer's database: For a customer, store a customer ID, last name, first name, billing address, and phone number For an order, store an order number, order date, event date, customer ID, description of services, and total price for services When designing this database, which step do you take first? a. Identify the customer ID and order number as unique identifiers. b. Identify and name the entities CUSTOMER and ORDER c. Identify customer ID, last name, first name, billing address, and phone number as attributes d. Create a CUSTOMER table that includes all the customers' attributes as columns
Question 7 On ERD, you can create attributes with the same name in different Entities. Question 7 options: True False
Q: 2. Create the database name Company which consists of one Entity name Employee which consists of 10 attributes (ID/First Name/Last Name/CNIC/Gender/Department/Sal ary/Address/Age) ID should be primary key Insert at least 10 records in the Entity Employee with respect to their type. a) Implementation the example of stored procedure now creates the stored procedure which: a. Have an ability to insert the record in the table. b. Have an ability to delete the record in table. c. Have an ability to update the record in the table. d. Have an ability to display the record in the table. |
Schema: Part(PartNo, PartName, VehicleType, UnitPrice, SalesPrice) Service(ServiceNo, PartNo, ServiceDate, CustomerNo, Qty) Constraints: i. Vehicle Type should be V1', V2', 'V3', 'V4', or 'V5' ii. UnitPrice and SalesPrice should be greater than zero iii. PartNo, FK in Service refers to PartNo, PK in Part Data: Part Service P1 V1 10 12 1 01-Jan-17 C1 P2 V2 100 110 3 01-Jan-17 C1 4 P3 V1 150 175 2 3 05-Feb-18 C2 10 P4 V3 200 250 3 1 15-May-18 Сз 9. P5 V2 75 90 1 03-Jun-19 C1 A. Write the following queries in SQL: i. List the Part Names which are not used to service the vehicle of any customer. ii. List the customer number who has got his vehicle serviced maximum number of times. iii. List the customer number whose vehicle service used all the parts of vehicle type V1. B. Write a PL/SQL program to find the total profit done in the sales of a given part number in the service of different customer vehicles. Raise an exception for invalid part number. 12345n
Question No: 06 This is a subjective question, hence you have to Write your answer in the Text-Fieid given below. The database has 4 transactions as shown below. TID Items Bought (A, D. I, B} (D. A, C, E, B) 3 (С.А, В. В) 4. (B, A. D} Assume min_sup = 60% and min_conf = 80% A Find all frequent iternsets using the Aprioni algorithm. Show your workjust showing the final answer is not acceptable. For each iteration show the candidate and acceptable frequent itemsets. You should show your work similar to the way the example was done in class B. List all the strong association rules, along with their support and confidence values. (Do not remove redundancies by applying lit.) 71 Chat with Proctor - G RUPASREE Ootions
Create a Crow’s Foot notation ERD to support the following business operations: A friend of yours has opened Professional Electronics and Repairs (PEAR) to repair smartphones, laptops, tablets, and MP3 players. She wants you to create a database to help her run her business.   When a customer brings a device to PEAR for repair, data must be recorded about the customer, the device, and the repair. The customer’s name, address, and a contact phone number must be recorded (if the customer has used the shop before, the information already in the system for the customer is verified as being current). For the device to be repaired, the type of device, model, and serial number are recorded (or verified if the device is already in the system). Only customers who have brought devices into PEAR for repair will be included in this system.   Since a customer might sell an older device to someone else who then brings the device to PEAR for repair, it is possible for a device to be brought in for…