辅导INFO2820编程、写作data课程编程

” 辅导INFO2820编程、写作data课程编程Paper code: EXAMPLE EXAM 2017, Semester 1 Page 1 of 14INFO2120/INFO2820Database Systems 1Example Exam Script2017, Semester 1MARKING SCHEMEThis example examination paper consists of 14 pages.Information:1. This is a collection of possible exam questions . Some of them are taken from previousassignments and tutorials, sometimes with slight changes to make them more suitablefor an exam. Question 1 is actually from one of the previous exams.2. Please note that the questions, the number of sub-questions and the weighting are justexamples. The exam will somewhat differ…3. The final exam will be a partially-open book exam: Your are allowed to bring one doublesidedA4 sheet of paper of your own notes to the exam, but otherwise no course materialsor textbooks are allowed.4. There will be common questions to be answered by all streams, plus a few dedicatedquestions per stream (either INFO2120 or INFO2820). Check your exam thoroughly forwhich parts you are expected to answer. Do not miss any subquestion (the exam will beprinted double-sided) nor answer a question for the wrong stream…5. Try to answer all questions within the space provided on this question paper.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 2 of 14Question 1: Database Design [20 points]The purpose of this question is to test your ability to map an E-R diagram into a relationalschema, and to apply integrity constraints. Note: This question has four parts (a d).(a) [10 points] Given the following E-R diagram:AccidentPerson Vehicleinvolvedownslicense nameaddressmodellocation datereport_nrdamageamountyeardriverregnoISACar Truckengine axeslicense_validUntillownerdisjointTransform the E-R diagram into relations using a graphical notation. Clearly indicate allprimary and foreign keys.Solution:license name address validUntilPersonregno model year ownerVehicleregno engineCarregno axesTruckreport nr location dateAccidentdriver regno report nr damage amountInvolved(b) [3 points] The entity types PERSON and VEHICLE are connected via a binary relationship.Give the SQL CREATE TABLE statements needed to capture both entity types, as wellas the relationship connecting them. Choose appropriate data types and pay specificallyattention to correctly represent the key and the participation constraints of that relationship.Solution:CREATE TABLE Person (license VARCHAR(10) PRIMARY KEY,name VARCHAR(30),Question continues on next page.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 3 of 14address VARCHAR(50),validUntil DATE);CREATE TABLE Vehicle (regno CHAR(6)) PRIMARY KEY,model VARCHAR(10),year INTEGER,owner VARCHAR(10) NOT NULL REFERENCES Person(license));(c) [3 points] According to our E/R diagram, a CAR is a special type of VEHICLE. Indicatewhether there is a way to express this with the CREATE TABLE statement, and if yes, howwould you do it.Solution:CREATE TABLE Car (regno CHAR(6) REFERENCES Vehicle ON DELETE CASCADE ON UPDATE CASCADE,engine VARCHAR(50));(d) [4 points] According to our E/R diagram, a VEHICLE is either a CAR or a TRUCK. Give anSQL assertion that ensures that this is always true.Solution:CREATE ASSERTION AssertVehicleDisjoint CHECK (NOT EXISTS (SELECT regno FROM CarINTERSECTSELECT regno FROM Truck) );Paper code: EXAMPLE EXAM 2017, Semester 1 Page 4 of 14Question 2: Data Normalisation [16 points]Consider the following BIGPATIENT table and its functional dependencies:visitID visitDate patID age city zip provNo provSpeciality diagnosisV10020 13/2/2015 P1 35 Sydney 80217 D1 Internist Ear InfectionV10020 13/2/2015 P1 35 Sydney 80217 D2 Practitioner InfluencaV93030 20/2/2015 P3 19 Sydney 80113 D2 Gynecologist PregnancyV82110 18/2/2015 P2 60 Newcastle 85932 D3 Cardiologist MurmurFunctional dependencies:PatID Age, ZipZip CityProvNo ProvSpecialityVisitID PatID, VisitDate(a) [2 points] Explain whether the following meaning is expressed by any of the given functionaldependencies:There can be multiple Zip-codes within the same city.Solution: According to the given functional dependencies (Zip City ), this is possible:It states that a zip code functionally determines the city, so if ou know the zip code,you also know the city. But note that this does not restrict that different zip codes couldbe used for the very same city.(b) [2 points] One of the functional dependencies is violated in the given BIGPATIENT instance.Explain which functional dependencies is violated and why.Solution: No – rows two and three contradict the third FD (ProvNo – ProvSpeciality)as the same ProvNo is associated with two different specialities;(c) [4 points] Identify a candidate key for the given table. Show the intermediate steps onhow you got your solution.Solution: (VisitID, ProvNo, Diagnosis)(d) [2 points] In which normal form is the table BIGPATIENT?Solution: 1NF as all attributes are atomic, but there are partial dependencies.(e) [3 points] Decompose the BIGPATIENT table into two smaller tables. Explain whetheryour decomposition is lossless join and dependency preservingSolution: Multiple possibilities, e.g: R1(vistiID, visitDate, patID, age, zip, provNo,provSpeciality, diagnosis) and R2(zip, city)(f) [3 points] Show whether any of your two tables is in BCNF already.Question continues on next page.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 5 of 14Solution: R2(zip, city) is in BCNF as any binary relation is in BCNF.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 6 of 14Question 3: SQL and Relational Algebra [22 points]The purpose of this question is to test your knowledge of SQL and relational algebra.Answer the given questions based on the following relational schema:Product(model, maker, eclass, price)ProductType(model, type, category)Manufacturer(maker, city, region, country)EnergyClass(eclass, min consumption, max consumption)(a) [3 points] Write an SQL query that finds the average price of products in the electronicscategory, whose energy class (eclass) is at least 2 or above.Solution: Example solution:SELECT AVG(price)FROM Product NATURAL JOIN ProductTypeWHERE category=electronicsAND eclass = 2Assume the following example database instance for aboves schema:Productmodel maker eclass priceT20FV300 Tony 1 400T34HS510 Tony 1 600B45 Bundig 2 2000Ice200 Niele 4 400ABC tba ABC 3 nullProductTypemodel type categoryT20FV300 TV electronicsT34HS510 TV electronicsIce200 fridge white goodsB45 VCR electronicsABC tba TV electronics(b) [2 points] What will be the result of your SQL query from the previous subquestion (a) onthis example database instance?Solution: Expected result: (2000)(c) [2 points] Write an SQL query that lists all countries in the database in alphabetical order,and per country the number of manufacturers from that country.Solution: Example solution:SELECT country, COUNT(maker)FROM ManufacturerGROUP BY countryORDER BY country(d) [4 points] Write an SQL query that answers the following question: Which is the cheapestTV made by Tony and what does it cost?Solution: Example solution:SELECT model, priceFROM Product NATURAL JOIN ProductTypeWHERE maker = Tony AND type = TV ANDprice = ALL ( SELECT priceFROM Product NATURAL JOIN ProductTypeWHERE maker = Tony AND type = TV )Question continues on next page.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 7 of 14(e) [3 points] Convert the following query to a non-nested SQL query. [3 marks]SELECT P.makerFROM Product PWHERE P.model IN ( SELECT T.modelFROM ProductType TWHERE T.type = Plasma TV )Solution: Example solution:SELECT makerFROM Product NATURAL JOIN ProductTypeWHERE type = Plasma TV(f) [4 points] Write in Relational Algebra a corresponding relational algebra query for yournon-nested SQL query from the previous sub-question.Solution: Example solution:MAKER(PRODUCT (Type=0P lasmaT V 0(ProductType)))(g) [4 points] Write a SQL OLAP query that computes for each manufacturer from Japan(country code JPN) the average prices for each of their product types. The query shallbe used to populate the following summary table:DVDplayer TV VCR . . . TotalABC . . .Tony . . .. . . . . . . . . . . . . . .Total . . .Solution: Example solution:SELECT maker, type, AVG(price)FROM Product NATURAL JOIN ProductType NATURAL JOIN ManufacturerWHERE country=JPNGROUP BY CUBE(maker,type);Paper code: EXAMPLE EXAM 2017, Semester 1 Page 8 of 14Question 4: Transactions [20 points]The purpose of this question is to test the understanding of transactions.Given the following relationEmployee(empId:INT, name:VARCHAR(20), department:CHAR(2), salary:INT)with this example instance:empId name department salary12122 Steve IT 7500012123 Jack HR 8000012130 Sarah HR 85000Consider the following hypothetical interleaved execution of two transactions T1 and T2 in aDBMS where concurrency control (such as locking) is not done; that is, each statement isexecuted as it is submitted, using the most up-to-date values of the database contents andwithout proper isolation from concurrent transactions:T1: BEGIN TRANSACTIONT1: SELECT * FROM Employee WHERE department = HRT2: BEGIN TRANSACTIONT2: SELECT salary INTO :sal FROM Employee WHERE empId = 12130T1: UPDATE Employee SET salary=salary+1000 WHERE department = HRT2: UPDATE Employee SET salary=:sal+5000 WHERE empId = 12123T1: COMMITT2: COMMIT(a) [3 points] Indicate the values returned by the SELECT statements and the final value ofthe database.Solution: T1: sees the two original tuples in HR of table content: (12123, Jack, HR,80000) and (12130, Sarah, HR, 85000))T2: sees the original salary of Jack (empId=12130): 80000Final DB state: first tuple unmodified, (12123, Jack, HR, 90000), (12130, Sarah, HR,86000)(b) [3 points] Indicate which, if any, of the following anomalies are produced by the givenexecution. The possible anomalies are: (i) dirty read, (ii) lost update, (iii) unrepeatableread.Solution: lost-update problem for T1s change of the salary of Jack (empId=12123)(overwritten by T2)(c) [2 points] Explain whether the execution is serializable or not.Solution: non-serializable: the end-effect of this is neither the same as T1 before T2or T2 before T1(d) [3 points] In a single sentence each, briefly define the exact meanings of the COMMITand the ABORT commands in SQL.Solution:Question continues on next page.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 9 of 14COMMIT requests(!) to successfully finish a transaction; might still end in an abort.This is indicated by the return value of COMMIT(e) [4 points] What does the ACID acronym stand for? Explain each property briefly.Solution:Atomicity Transactions cannot be partially committed, they either happen completelyor not at allConsistency A transaction takes the database from one valid state to another bysome semantically meaningful transitionIsolation The changes made by one transaction should not be visible to another.Durability After a system failure the database is guaranteed to be restored to the lastconsistent state (i.e., as it was immediately after the last successful COMMIT).(f) [5 points] Consider the following code fragment from the booking functionality of a carsharingapplication:Members ( memberNo, name, email, booking_stats );Availabilities car_regno, avail_start, avail_duration, available );Bookings ( memberNo, bookingNo, car_regno, starttime, duration );The MEMBERS relation stores information about members of the car sharing organisation.Besides core attributes of each member, such as name and email, it also contains astatistics attribute about the number of bookings done per Member.Members can book cars, as long as those are available for the intended time period.The AVAILABILITIES table lists all availabilities of cars from the organisation. The lastavailable attribute is 1, if a given car is available for the time period from avail start foravail duration. Bookings are stored in the BOOKINGS table.Members of the car-sharing organisation can book a car with a stored procedure bookCar.Your colleague has written the following first implementation of that stored procedure, asshown on the following page.1. Add BEGIN TRANSACTION, COMMIT AND ROLLBACK statements at the appropriateplaceholders of this function to make it a correct transaction. Where would youput those commands. Note that you can leave some placeholders ( ) empty.2. Discuss whether the execution of this stored procedure with auto-commit on would becorrect. Auto-commit would commit each SQL statement individually.Solution:1. Statements added in code below.2. Splitting the code into separately committed statements mean that they do notoperate as a logical unit – examples could be given here for how the ACID propertieswould fail to hold.Question continues on next page.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 10 of 141. PROCEDURE bookCar(member IN INT, car IN VARCHAR, start IN DATE, duration IN INT)2. AS $$3. DECLARE4. car_availability INT;5. BEGIN6. BEGIN TRANSACTION7. /* check whether the car is available */8. /* assume that theres a overlaps() function */9. SELECT available_seats INTO :car_availabilityFROM AvailabilitiesWHERE car_regno=:carAND overlaps(interval(avail_start,avail_duration), interval(start,duration));10. ____________11. /* continue the booking only if there is at least one free seat */12. IF ( :car_availability NOT NULL AND :car_availability 0 ) THEN13. ____________14. /* make the booking */15. INSERT INTO Bookings VALUES (:member,(SELECT MAX(bookingNo)+1FROM BookingsWHERE memberNo = :member),:car, :start, :duration );16. ____________17. /* change the availability */18. UPDATE AvailabilitiesSET available=0WHERE car_regno=:car AND avail_start=:start;19. ____________21. /* adjust the member statistics */22. UPDATE MembersSET booking_stats = booking_stats + 1WHERE memberNo=:member;23. COMMIT;24. ELSE25. :my_ticket := NULL; /* to indicate that booking failed */26. ROLLBACK;27. END IF;28. ____________29. END;30. $$ LANGUAGE plpgsqlPaper code: EXAMPLE EXAM 2017, Semester 1 Page 11 of 14Question 5: Review Questions [22 points]The purpose of this question is to test your understanding of different database concepts thatwe have covered throughout the course.Tick the circle (or circles) corresponding to each correct answer:(a) [2 points] What is the difference between a database trigger and an SQL assertion?An SQL assertion is not part of the SQL standard, while triggers are. A trigger is bound to just a single table, while an assertion can express an integrityconstraint over multiple tables. A trigger can be used to check for a dynamic integrity constraint, while a SQLassertion is a static integrity constraint.A trigger can be used to view maintenance, while a SQL assertion is a dynamicintegrity constraint.(b) [2 points] Why do database systems need three-valued logic? Because of NULL values, SQL works with three-value logic as a comparison withNULL results in a special UNKNOWN value.Databases do not use three-valued logic.Three-value logic is needed to test tables to be in Boyce-Codd normal form.Because of the accent of the lecturer, it is never clear whether something is true,false – or remains unknown.(c) [2 points] Select two differences between a SQL WINDOW clause and grouping.Ranking can be expressed only with GROUP BY. Grouping works on sets, while windowing allows order per window. Groups are disjoint, windows can overlap.Aggregate functions require GROUP BY, while counting requires a SQL window.(d) [2 points] What is the meaning of the following relational algebra query?location(Accident)List all accidents in order of their location.List accidents which have a known (NOT NULL) location.List accidents by driver location. List the locations of all accidents.(e) [2 points] Which two of the following statements are correct about referential integrity?Referential integrity is the guarantee that no foreign key value is NULL.Referential integrity ensures the uniqueness of foreign key values in the dependenttable Referential integrity ensures that each foreign key value is present in the parenttable. Referential integrity is a static integrity constraint.Referential integrity is a dynamic integrity constraint.Question continues on next page.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 12 of 14(f) [2 points] Briefly explain: What is an index and what is it used for?Solution: An index is an access path or a physical data structure that allows fasterquery execution for a given search key.(g) [2 points] The DBA of your database finds that your application frequently executes thefollowing query:SELECT a, bFROM RWHERE c 500Which index would make this query faster?A primary index on a could make this query faster. A secondary index on c could make it faster.A multi-attribute index on a, b, c could make it faster.A clustered index on b could make it faster.(h) [2 points] What are two advantages of stored procedures as compared to client-sidedatabase application development?A stored procedure is written in SQL, while client applications are written in a(complex) programming languages. Stored procedures are executed within the database system close to the accesseddata, avoiding complex network communication between client and server.Stored procedures are executed faster than compiled client code. Stored procedures can be used as a central code base in the database serverwhich can be shared by multiple applications.The languages, in which stored procedures are written, are very vendor specific.(i) [2 points] What is the cursor concept and why is it needed in database APIs? A cursor allows to process a set of return values from a SQL query row-by-rowwithout the need to load the complete (potentially very large) query result intothe main memory. It also allows programming languages that have no nativesupport for sets to work with database result sets.A cursor allows to define dedicated error handling routines for a whole blockof code without the need to write individual condition statements around eachdatabase API call.A cursor allows to efficiently find individual rows in a very large data set by theirprimary key.The cursor concept allows to iterate over a set of rows and also to jump to specificrows in a table using their position. This is needed as programming languages donot directly support SQL, hence the table access must be programmed manuallyusing such cursors.(j) [2 points] Is a typical fact table of a star schema in BCNF?No because there are no non-trivial functional dependencies that hold on a facttable.No because a fact table can have functional dependencies between non-keyattributes, while BCNF requires all non-trivial functional dependencies to have akey on the left side.Question continues on next page.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 13 of 14 Yes, because in a fact table, the key is the set of all dimension keys, and the onlyfunctional dependency is that the fact attributes depend on all the dimensions(the key) – which is what BCNF requires.There is no definite statement possible about this without an analysis of the actualschema and fact table. mainly historic as data warehouses are a relativelynew technology which was only lately introduced into the existing IT infrastructures.(k) [2 points] Select examples of syntactic and semantic transformations that might have tobe made while loading data into a data warehouse. Semantic transformation: The weekly salary of an employee is mapped andconverted to an annual salary of the employee in the data warehouse.Semantic transformation: Mapping a varchar(20) attribute to a char(20) data typein the data warehouse. Syntactic transformation: A source attribute firstname is mapped to an attributegivenName in the data warehouse.Syntactic transformation: The SQL query to retrieve the name of a customer ifmapped to an OLAP MDX query in the data warehouse to retrieve this attribute.Question 6: Advanced Question (INFO2820 Students Only) [16 points]Some advanced-only questions:(a) [2 points] Why does embedded SQL/C require a precompiler?Solution:(b) [4 points] Given the following assertion, write corresponding row-level triggers that implementthe corresponding integrity constraint.CREATE ASSERTION AssertDisjointEmployeeTypes CHECK ( NOT EXISTS (SELECT empid FROM BankManagerINTERSECTSELECT empid FROM SecretaryINTERSECTSELECT empid FROM Teller ) )Solution:(c) [3 points] Consider the following Datalog schema:directFlight(From,To,Distance)Write a recursive Datalog rule that computes all direct or indirect connections between twocities. Using this rule, find all cities reachable from London.Solution:(d) [3 points] Given the following schedule S:w1(x)r1(x)w3(z)r2(x)r2(y)r4(z)r3(x)w3(y)Check whether this schedule is conflict-serializable or not. If the schedule is conflict equivalentto a serial schedule, choose the equivalent serial order. (Note: Ti Tj means Tiexecutes before Tj)Question continues on next page.Paper code: EXAMPLE EXAM 2017, Semester 1 Page 14 of 14Solution:(e) [4 points] Briefly explain the difference between locking and snapshot isolation concurrencycontrol.Solution:如有需要，请加QQ：99515681 或邮箱：99515681@qq.com

“