Blog

We use the following database schema, which has five tables,…

We use the following database schema, which has five tables, shown below. Primary key attributes are underlined and foreign keys are noted in the superscript.• Customer = {customerID, firstName, lastName, income, birthDate}• Account = {accNumber, type, balance, branchNumberFK-Branch}• Owns = {customerIDFK-Customer, accNumberFK-Account}• Transaction = {transNumber, accNumberFK-Account, amount}• Employee = {ssn, firstName, lastName, salary, branchNumberFK-Branch}• Branch = {branchNumber, branchName, managerSSNFK-Employee, budget}   Notes:• The customerID attribute (Customer) is a unique number that represents a customer, it is not a customer’s SSN• The accNumber attribute (Account) represents the account number• The balance (Account) attribute represents the total amount in an account• The type (Account) attribute represents the type an account: checking, saving, or business• The transNumber attribute (Transactions) represents a transaction number, combined with account number it uniquely identify a transaction• The branchNumber attribute (Branch) uniquely identifies a branch• The managerSSN attribute (Branch) represents the SSN of the branch managerWrite SQL queries to return data specified in the following questions, which have to satisfy the following requirements: Each question should be answered with a single SQL query. Name every column in the result. So if the query asks you to return something like income times 10, make sure you include an AS statement to name the column. Your question will not be assessed on efficiency, but marks may be deducted if you include unnecessary tables (e.g., including both Owns and Customer when you only need the customerID of customers who own accounts). 1. With regard to the account table mentioned above, assume account 100011 has a balance of 4500. State the final balance of the account after the following updates. [4] set autocommit = 0; commit; update account set balance=100 where accNumber =’100011′; update account set balance=500 where accNumber =’100011′; rollback; update account set balance = balance + 1500 where accNumber =’100011′; commit;  

Here are the schema and insert statements for  create databa…

Here are the schema and insert statements for  create database store; use store;create table suppliers(    sid integer primary key,    sname varchar(50),    address varchar(50)    );create table parts(    pid integer primary key,    pname varchar(40),    color varchar(15)    );create table catalog(    sid integer,    pid integer,    cost integer,    primary key(sid,pid),    foreign key(sid) references suppliers(sid),    foreign key(pid) references parts (pid)    );     Some sample data for the tables are given below insert into suppliers values (sid, sname, address) (1, ‘Acme Widget Suppliers’, ‘1 Grub St., Potemkin Village, IL 61801’),(2, ‘Big Red Tool and Die’, ‘4 My Way, Bermuda Shorts, OR 90305’),(3, ‘Perfunctory Parts’, ‘99999 Short Pier, Terra Del Fuego, TX 41299’),(4, ‘Alien Aircaft Inc.’, ‘2 Groom Lake, Rachel, NV 51902’); insert into parts values (pid, pname, color)(1, ‘Left Handed Bacon Stretcher Cover’, ‘Red’),(2, ‘Smoke Shifter End’, ‘Black’),(3, ‘Acme Widget Washer’, ‘Red’),(4, ‘Acme Widget Washer’, ‘Silver’),(5, ‘I Brake for Crop Circles Sticker’, ‘Translucent’),(6, ‘Anti-Gravity Turbine Generator’, ‘Cyan’),(7, ‘Anti-Gravity Turbine Generator’, ‘Magenta’),(8, ‘Fire Hydrant Cap’, ‘Red’),(9, ‘7 Segment Display’, ‘Green’); insert into catalog values (sid, pid, cost) (1, 3, 0.50),(1, 4, 0.50),(1, 8, 11.70),(2, 3, 0.55),(2, 8, 7.95),(2, 1, 16.50),(4, 8, 12.50),(2, 9, 1.00),(4, 5, 2.20),(4, 6, 1247548.23),(4, 7, 1247548.23); (Please use functionality supported by MySQL only) (a) Find the pnames of parts for which there is some supplier. [4] [Hint: a part will only be added to the catalog table if it has a supplier]  (b) Find the suppliers who supply some part. You must use Exists to write the query. One can use an equivalent non-nested query without the ‘exists’ clause to answer the same question, but that would not receive any grade. [5] [Hints: must have an entry in the catalog table]