CITS1402编程写作、辅导SQL程序设计

” CITS1402编程写作、辅导SQL程序设计Department of Computer Science and Software EngineeringSAMPLE EXAMINATION, SEMESTER 2 2018CITS1402RELATIONAL DATABASE MANAGEMENT SYSTEMSFAMILY NAME: GIVEN NAMES:STUDENT ID: SIGNATURE:This Paper contains: 14 pages (including title page)Time allowed: 2 hoursINSTRUCTIONS: This exam has 7 Single-choice questions, worth 14 marks, and 7 short-answerquestions, worth 46 marks. So, the full mark of this exam is 60 in total. Answer the single-choice questions by ticking the correct choice in this examinationbooklet, and answer the short-answer questions in the spaces providedin this examination booklet. This is a closed book examination. UWA Approved Calculator with Sticker is allowed.***Note***: This is just sample questions only for your exam preparation.PLEASE NOTEExamination Candidates may only bring authorised materials into the examinationroom. If a supervisor finds, during the examination, that you have unauthorisedmaterial, in whatever form, in the vicinity of your desk or on your person, whetherin the examination room or the toilets or en route to/from the toilets, the matterwill be reported to the head of school and disciplinary action will normally betaken against you. This action may result in your being deprived of any creditfor this examination or even, in some cases, for the whole unit. This will applyregardless of whether the material has been used at the time it is found.Therefore, any candidate who has brought any unauthorised material whatsoeverinto the examination room should declare it to the supervisor immediately.Candidates who are uncertain whether any material is authorised should ask thesupervisor for Clarification.Sample Examination, Semester 220182.CITS14021. Consider a relation Store1(id,name,type,amount) and Store2(id,name,price) containingthe following tuplesid name type amount2 iPad 1 10003 iPad 2 10004 iPod 6 1000id name price2 iPad 10003 iPad 9004 iPod 400How many tuples are returned by the following relational algebra expression?name,amount(Store1) ./ name,price(Store2)[2 marks]2. Consider a relation Store1(id,name,type,amount) and Store2(id,name,price) containingthe following tuplesid name type amount2 iPad 1 10003 iPad 2 10004 iPod 6 1000id name price2 iPad 10003 iPad 9004 iPod 400How many Rows would the following query produce?SELECT * FROM Store1 NATURAL JOIN Store2;(a) 0QUESTION 2 CONTINUES OVER THE PAGESample Examination, Semester 220182 (Continued)3.CITS1402(b) 3 ***(c) 4(d) 5(e) 9[2 marks]3. Consider a relation Store1(id,name,type,amount) and Store2(id,name,price) containingthe following tuplesid name type amount2 iPad 1 10003 iPad 2 10004 iPod 6 1000id name price2 iPad 10003 iPad 9004 iPod 400How many rows would the following query produce?SELECT *FROM Store1 LEFT JOIN Store2 on Store1.name=Store2.name;(a) 2(b) 3(c) 4(d) 5 ***(e) 6[2 marks]SEE OVERSample Examination, Semester 220184.CITS14024. Consider two relations R(A,B,C) and T(A,C,D) containing the following tuplesA B C1 2 42 1 42 2 1A C D3 2 52 2 42 2 12 2 4How many rows would the following relational algebra produce?A,CR ./ A,DT(a) 3(b) 4 ***(c) 5(d) 8(e) 9[2 marks]5. Consider two relations R(A,B,C) and T(A,C,D) containing the following tuplesA B C1 2 42 1 42 2 1A C D3 2 52 2 42 2 12 2 4How many rows would the following query produce?SELECT * FROM(Select A,B From R) AS T1 right join (Select A,C From R) AS T2ON T1.A=T2.A;QUESTION 5 CONTINUES OVER THE PAGESample Examination, Semester 220185 (Continued)5.CITS1402(a) 0(b) 3(c) 4(d) 5 ***(e) 9[2 marks]6. Consider a relation R(A, B, C, D, E) with functional dependenciesD C, CE A, D A, AE DGiven the three attribute sets, namely BE, ABE and BDE, which of them canbe the key of the relation R?(a) BE only(b) BDE only(c) BDE and BE only(d) ABE and BDE only ***(e) All sets[2 marks]7. Consider a database used by a university that contains three relations:Create Table Student (id int Primary Key,name Varchar(32));Create Table Unit(code Varchar(10) Primary Key,name Varchar(32));Create Table Enrolled( sid int, ucode Varchar(10), mark int,QUESTION 7 CONTINUES OVER THE PAGESample Examination, Semester 220187 (Continued)6.CITS1402Foreign Key (sid) References Student (id),Foreign Key (ucode) References Unit (code));and a view is defined asCREATE VIEW AcademicRecord ASSELECT S.name as S Name, U.name as U Name, markFROM Student S JOIN Unit U JOIN Enrolled EOn S.id=E.sid and U.code=E.ucode;If the tables are initially empty, then which of the following choices can make theview to have at least one row added?I.INSERT INTO Student VALUES(101, Rob);INSERT INTO Unit VALUES(202, Java);INSERT INTO Enrolled VALUES(101,201,80);II.INSERT INTO Student VALUES(101, NULL);INSERT INTO Unit VALUES(201, NULL);INSERT INTO Enrolled VALUES(101,201,80);III.INSERT INTO Student VALUES(101, Rob);INSERT INTO Unit VALUES(201, Java);INSERT INTO Enrolled VALUES(101,201,NULL);(a) None of them(b) Just III(c) Just II and III(d) Just II ***(e) All of themQUESTION 7 CONTINUES OVER THE PAGESample Examination, Semester 220187 (Continued)7.CITS1402[2 marks]8. Consider a relation R(A, B, C) and S(A, B, D) containing the following tuplesWhat to be produced from the expression A,D (R ./ B=8 (S))?Solution:A D6 6[2 marks]What to be produced from the expression A (R ./ B=8 (S))D (R ./ A=6 (S))?Solution:A D6 66 7[2 marks]SEE OVERSample Examination, Semester 220188.CITS14029. A pizza shop manages its customer orders in a database with three tables calledCustomers, CustomerOrder and Suburbs which have the following schemas:Customers (id INT, name VARCHAR (32));Suburbs (code CHAR(4), name VARCHAR(32));CustomerOrder (cid INT, scode CHAR(4), cost double,order_date DATETIME NOT NULL DEFAULT (GETDATE(),Foreign Key (cid) References Customers(id),Foreign Key (scode) References Suburbs (code));The field Customers.id represents a customer number, the field Suburbs.coderepresents a suburbs area code (such as 6009 for Nedlands), and these two fieldsare keys for their tables respectively. The fields cid and scode in CustomerOrderare foreign keys to Customers.id and Suburbs.code. Assume every suburb onlyhas one such pizza store and the table Suburbs has the full data, i.e., all recordslike (6009, Nedlands) existed in the tables.(1) Consider a new customer named John Smith who ordered a few pizza with30 and wants to pick up from a suburb 6009. Write two SQL statements withthe correct Sequence that will add the customer and his order into the database,assuming his customer ID is 1000.Solution:INSERT INTO Customers VALUES(1000, John Smith);Insert CustomerOrder Values(1000, 6009, 30);(2) Write a SQL statement that will list each suburbs name and the total amountof sales the pizza shop did on the suburb. The list should be sorted in descendingorder using DESC.Solution:QUESTION 9 CONTINUES OVER THE PAGESample Examination, Semester 220189 (Continued)9.CITS1402Select S.name, SUM(CO.cost) AS Total SaleFrom CustomerOrder CO JOIN Suburbs S ON CO.scode=S.codeGroup By CO.scodeOrder By Total Sale DESC;(3) Write a SQL statement that will list all the suburbs information on whichthe pizza shop has sold the total amount of sales no less than $10,000.Solution:Select S.code, S.nameFrom CustomerOrder CO JOIN Suburbs S ON CO.scode=S.codeGroup By CO.scodeHaving SUM(CO.cost)=10000;10. Suppose a bank company has a table BankAccount to maintain their customersbalance information as below.Create Table BankAccount(ID INT NOT NULL,Name Varchar(24),Balance Real,Primary Key(ID));Create a Trigger named CheckBalance on Table BankAccount. The triggerCheckBalance can automatically check the Balance of customers when they makewithdraw from their bank accounts. If the balance after withdraw is less than0, then we require the trigger to send a message Balance is not enough. [Hint:SIGNAL SQLSTATE 45000 SET Message Text=Balance is not enough.]QUESTION 10 CONTINUES OVER THE PAGESample Examination, Semester 2201810 (Continued)10.CITS1402### Write your code from hereDELIMITER ++Create Trigger CheckBalanceSolution:BEFORE UPDATEON BankAccountFOR EACH ROWBEGINIF NEW.balance 0 THENSIGNAL SQLSTATE 45000SET MESSAGE TEXT = Balance too low;END IF;END++DELIMITER ;[6 marks]11. Transaction isolation is An important part of any transactional system. MYSQLpermits the users to choose how isolated they wish each transaction to be bychoosing between READ UNCOMMITTED, READ COMMITTED, REPEATABLEREAD, SERIALIZABLE. Explain what each means in a transactionaldatabase system.Solution:Refer to the lecture notes.[6 marks]SEE OVERSample Examination, Semester 2201811.CITS140212. Strict Two-Phase Locking is the widely used locking protocol to manage thetransactions. Assume two users Kevin and Rod to read and write one itemItem001 at the same time from the same table as below:User Kevin User RodStep 1. Read Item001 – value is 10 Step 1. Read Item001 – value is 10Step 2. Change Item001 – reduce value by 5 Step 2. Change Item001 – reduce value by 3Step 3. Write Item001 Step 3. Write Item001List the order of processing at the database server and show the value of Item001after the two users transactions complete.Solution:Refer to the lecture notes.[4 marks]13. A company manages the salespersons, their sales area, their customers, theshipped warehouse and the sales amount of salesperson to a customer. Thetable below provides the sales-report information.SalesPersonID SalesPersonName SalesArea CustomerID CustomerName WarehouseID WarehouseLocation SalesAmount31001 Peter North 15001 Delta Datta 4 Perth 500015002 Kevin Smith 3 Nedlands 450015003 Ben Rode 3 Nedlands 50031012 John South 18442 S. Press 2 East Victoria Park 300018432 Stodoch Inc 2 East Victoria Park 800018542 Flood Repair 1 Canning Vale 3000(a) Normalise the relation to 2NF and identify primary keys in all the relationsusing the underlines.QUESTION 13(a) CONTINUES OVER THE PAGESample Examination, Semester 2201813(a) (Continued)12.CITS1402Solution:SalesPerson(SalesPersonID,SalesPersonName, SalesArea)Customer(CustomerID,CustomerName, WarehouseID,WarehouseLocation)Sales(SalesPersonID,CustomerID,SalesAmount)[4 marks](b) Normalise the tables to 3NF. Please identify primary keys in all the relationsusing the underlines.Solution:SalesPerson(SalesPersonID,SalesPersonName, SalesArea)Sales(SalesPersonID,CustomerID,SalesAmount)Customer(CustomerID,CustomerName)Shipment(CustomerID, WarehouseID)Warehouse (WarehouseID, WarehouseLocation)OrSolution:SalesPerson(SalesPersonID,SalesPersonName, SalesArea)Sales(SalesPersonID,CustomerID,SalesAmount)Customer(CustomerID,CustomerName, WarehouseID)Warehouse (WarehouseID, WarehouseLocation)[4 marks]SEE OVERSample Examination, Semester 2201814 (Continued)13.CITS140214. NAB is a bank to provide home loads for its customers. To do this, NAB needs todevelop a data management system to record all the information about customers,home loans and their borrows. A loan should have a load number, amount and therate. A borrow should include the access date (e.g., home loan commencementday) and the overall borrow length (e.g., 30 years). One borrowed loan needsto be linked to a bank account that can be owned by more than one customerstogether with different shares, e.g., a couple can share a joint bank account. Thebank account should include the basic information, such as bank ID and balance.Customers should have their unique ID, personal information such as name andaddress.Please draw an Entity Relationship diagram that captures this information aboutthe above description of NAB home loan system, including all Entities, Attributes,Relationships and Minimum Cardinality Constraints, and PrimaryKeys of each entity. You must use the formal notations taught in lecturenotes.Solution:[6 marks]SEE OVERSample Examination, Semester 2201814.CITS1402Blank Page For Working如有需要，请加QQ：99515681 或邮箱：99515681@qq.com

“