一站式論文代寫,英国、美国、澳洲留学生Essay代寫—FreePass代写

SQL代寫 - INFO2120/INFO2820 Database Systems
時間:2020-12-04
Information: 1. This is a collection of possible exam questions . Some of them are taken from previous assignments and tutorials, sometimes with slight changes to make them more suitable for 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 just examples. The exam will somewhat differ... 3. The final exam will be a partially-open book exam: Your are allowed to bring one double?sided A4 sheet of paper of your own notes to the exam, but otherwise no course materials or textbooks are allowed. 4. There will be common questions to be answered by all streams, plus a few dedicated questions per stream (either INFO2120 or INFO2820). Check your exam thoroughly for which parts you are expected to answer. Do not miss any subquestion (the exam will be printed double-sided) nor answer a question for the wrong stream... 5. Try to answer all questions within the space provided on this question paper. Accident Person Vehicle involved owns license name address model location date report_nr damage?amount year driver regno ISA Car Truck engine axes license_ validUntill owner disjoint Paper code: EXAMPLE EXAM 2017, Semester 1 Page 2 of 14 Question 1: Database Design [20 points] The purpose of this question is to test your ability to map an E-R diagram into a relational schema, and to apply integrity constraints. Note: This question has four parts (a – d). (a) [10 points] Given the following E-R diagram: Transform the E-R diagram into relations using a graphical notation. Clearly indicate all primary and foreign keys. Solution: license name address validUntil Person regno model year owner Vehicle regno engine Car regno axes Truck report nr location date Accident driver regno report nr damage amount Involved (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 well as the relationship connecting them. Choose appropriate data types and pay specifically attention 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 14 address 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. Indicate whether there is a way to express this with the CREATE TABLE statement, and if yes, how would 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 an SQL assertion that ensures that this is always true. Solution: CREATE ASSERTION AssertVehicleDisjoint CHECK ( NOT EXISTS ( SELECT regno FROM Car INTERSECT SELECT regno FROM Truck ) ); Paper code: EXAMPLE EXAM 2017, Semester 1 Page 4 of 14 Question 2: Data Normalisation [16 points] Consider the following BIGPATIENT table and its functional dependencies: visitID visitDate patID age city zip provNo provSpeciality diagnosis V10020 13/2/2015 P1 35 Sydney 80217 D1 Internist Ear Infection V10020 13/2/2015 P1 35 Sydney 80217 D2 Practitioner Influenca V93030 20/2/2015 P3 19 Sydney 80113 D2 Gynecologist Pregnancy V82110 18/2/2015 P2 60 Newcastle 85932 D3 Cardiologist Murmur Functional dependencies: PatID → Age, Zip Zip → City ProvNo → ProvSpeciality VisitID → PatID, VisitDate (a) [2 points] Explain whether the following meaning is expressed by any of the given func?tional dependencies: There can be multiple Zip-codes within the same city. Solution: According to the given functional dependencies (Zip → City ), this is pos?sible: 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 could be used for the very same city. (b) [2 points] One of the functional dependencies is violated in the given BIGPATIENT in?stance. 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 on how 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 whether your decomposition is lossless join and dependency preserving Solution: 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 14 Solution: R2(zip, city) is in BCNF as any binary relation is in BCNF. Paper code: EXAMPLE EXAM 2017, Semester 1 Page 6 of 14 Question 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 ’electronics’ category, whose energy class (eclass) is at least 2 or above. Solution: Example solution: SELECT AVG(price) FROM Product NATURAL JOIN ProductType WHERE category='electronics' AND eclass >= 2 Assume the following example database instance for above’s schema: Product model maker eclass price ’T20FV300’ ’Tony’ 1 400 ’T34HS510’ ’Tony’ 1 600 ’B45’ ’Bundig’ 2 2000 ’Ice200’ ’Niele’ 4 400 ’ABC tba’ ’ABC’ 3 null ProductType model type category ’T20FV300’ ’TV’ ’electronics’ ’T34HS510’ ’TV’ ’electronics’ ’Ice200’ ’fridge’ ’white goods’ ’B45’ ’VCR’ ’electronics’ ’ABC tba’ ’TV’ ’electronics’ (b) [2 points] What will be the result of your SQL query from the previous subquestion (a) on this 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 Manufacturer GROUP BY country ORDER BY country (d) [4 points] Write an SQL query that answers the following question: Which is the cheapest TV made by ’Tony’ and what does it cost? Solution: Example solution: SELECT model, price FROM Product NATURAL JOIN ProductType WHERE maker = 'Tony' AND type = 'TV' AND price <= ALL ( SELECT price FROM Product NATURAL JOIN ProductType WHERE 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.maker FROM Product P WHERE P.model IN ( SELECT T.model FROM ProductType T WHERE T.type = 'Plasma TV' ) Solution: Example solution: SELECT maker FROM Product NATURAL JOIN ProductType WHERE type = 'Plasma TV' (f) [4 points] Write in Relational Algebra a corresponding relational algebra query for your non-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 shall be used to populate the following summary table: DVDplayer TV VCR . . . Total ABC . . . Tony . . . . . . . . . . . . . . . . . . Total . . . Solution: Example solution: SELECT maker, type, AVG(price) FROM Product NATURAL JOIN ProductType NATURAL JOIN Manufacturer WHERE country='JPN' GROUP BY CUBE(maker,type); Paper code: EXAMPLE EXAM 2017, Semester 1 Page 8 of 14 Question 4: Transactions [20 points] The purpose of this question is to test the understanding of transactions. Given the following relation Employee(empId:INT, name:VARCHAR(20), department:CHAR(2), salary:INT) with this example instance: empId name department salary 12122 Steve IT 75000 12123 Jack HR 80000 12130 Sarah HR 85000 Consider the following hypothetical interleaved execution of two transactions T1 and T2 in a DBMS where concurrency control (such as locking) is not done; that is, each statement is executed as it is submitted, using the most up-to-date values of the database contents and without proper isolation from concurrent transactions: T1: BEGIN TRANSACTION T1: SELECT * FROM Employee WHERE department = 'HR' T2: BEGIN TRANSACTION T2: SELECT salary INTO :sal FROM Employee WHERE empId = 12130 T1: UPDATE Employee SET salary=salary+1000 WHERE department = 'HR' T2: UPDATE Employee SET salary=:sal+5000 WHERE empId = 12123 T1: COMMIT T2: COMMIT (a) [3 points] Indicate the values returned by the SELECT statements and the final value of the 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): 80000 Final 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 given execution. The possible anomalies are: (i) dirty read, (ii) lost update, (iii) unrepeatable read. Solution: lost-update problem for T1’s 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 T2 or T2 before T1 (d) [3 points] In a single sentence each, briefly define the exact meanings of the COMMIT and the ABORT commands in SQL. Solution: Question continues on next page. Paper code: EXAMPLE EXAM 2017, Semester 1 Page 9 of 14 COMMIT – 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 completely or not at all Consistency A transaction takes the database from one valid state to another by some semantically meaningful transition Isolation 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 last consistent 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 car?sharing application: 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 a statistics 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 last ’available’ attribute is 1, if a given car is available for the time period from ’avail start’ for ’avail 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, as shown on the following page. 1. Add BEGIN TRANSACTION, COMMIT AND ROLLBACK statements at the appropri?ate placeholders of this function to make it a correct transaction. Where would you put those commands. Note that you can leave some placeholders ( ) empty. 2. Discuss whether the execution of this stored procedure with auto-commit on would be correct. 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 not operate as a logical unit - examples could be given here for how the ACID prop?erties would fail to hold. Question continues on next page. Paper code: EXAMPLE EXAM 2017, Semester 1 Page 10 of 14 1. PROCEDURE bookCar(member IN INT, car IN VARCHAR, start IN DATE, duration IN INT) 2. AS $$ 3. DECLARE 4. car_availability INT; 5. BEGIN 6. BEGIN TRANSACTION 7. /* check whether the car is available */ 8. /* assume that there's a overlaps() function */ 9. SELECT available_seats INTO :car_availability FROM Availabilities WHERE car_regno=:car AND 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 ) THEN 13. ____________ 14. /* make the booking */ 15. INSERT INTO Bookings VALUES (:member, (SELECT MAX(bookingNo)+1 FROM Bookings WHERE memberNo = :member), :car, :start, :duration ); 16. ____________ 17. /* change the availability */ 18. UPDATE Availabilities SET available=0 WHERE car_regno=:car AND avail_start=:start; 19. ____________ 21. /* adjust the member statistics */ 22. UPDATE Members SET booking_stats = booking_stats + 1 WHERE memberNo=:member; 23. COMMIT; 24. ELSE 25. :my_ticket := NULL; /* to indicate that booking failed */ 26. ROLLBACK; 27. END IF; 28. ____________ 29. END; 30. $$ LANGUAGE plpgsql Paper code: EXAMPLE EXAM 2017, Semester 1 Page 11 of 14 Question 5: Review Questions [22 points] The purpose of this question is to test your understanding of different database concepts that we 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 in?tegrity constraint over multiple tables.  A trigger can be used to check for a dynamic integrity constraint, while a SQL assertion is a static integrity constraint. A trigger can be used to view maintenance, while a SQL assertion is a dynamic integrity 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 with NULL 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 depen?dent table  Referential integrity ensures that each foreign key value is present in the parent table.  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 faster query execution for a given search key. (g) [2 points] The DBA of your database finds that your application frequently executes the following query: SELECT a, b FROM R WHERE c > 500 Which 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-side database 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 ac?cessed data, 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 server which 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-row without the need to load the complete (potentially very large) query result into the main memory. It also allows programming languages that have no native support for sets to work with database result sets. A cursor allows to define dedicated error handling routines for a whole block of code without the need to write individual condition statements around each database API call. A cursor allows to efficiently find individual rows in a very large data set by their primary key. The cursor concept allows to iterate over a set of rows and also to jump to specific rows in a table using their position. This is needed as programming languages do not directly support SQL, hence the table access must be programmed manually using 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 fact table. No because a fact table can have functional dependencies between non-key attributes, while BCNF requires all non-trivial functional dependencies to have a key 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 only functional 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 actual schema and fact table. mainly historic as data warehouses are a relatively new technology which was only lately introduced into the existing IT infrastructures. (k) [2 points] Select examples of syntactic and semantic transformations that might have to be made while loading data into a data warehouse.  Semantic transformation: The weekly salary of an employee is mapped and converted to an annual salary of the employee in the data warehouse. Semantic transformation: Mapping a varchar(20) attribute to a char(20) data type in the data warehouse.  Syntactic transformation: A source attribute firstname is mapped to an attribute givenName in the data warehouse. Syntactic transformation: The SQL query to retrieve the name of a customer if mapped 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 implement the corresponding integrity constraint. CREATE ASSERTION AssertDisjointEmployeeTypes CHECK ( NOT EXISTS ( SELECT empid FROM BankManager INTERSECT SELECT empid FROM Secretary INTERSECT SELECT 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 two cities. 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 equivalent to a serial schedule, choose the equivalent serial order. (Note: ’Ti ? Tj’ means Ti executes before Tj)

在線客服

售前咨詢
售后咨詢
微信號
Essay_Cheery
微信
专业essay代写|留学生论文,作业,网课,考试|代做功課服務-PROESSAY HKG 专业留学Essay|Assignment代写|毕业论文代写-rushmyessay,绝对靠谱负责 代写essay,代写assignment,「立减5%」网课代修-Australiaway 代写essay,代写assignment,代写PAPER,留学生论文代写网 毕业论文代写,代写paper,北美CS代写-编程代码,代写金融-第一代写网 作业代写:CS代写|代写论文|统计,数学,物理代写-天天论文网 提供高质量的essay代写,Paper代写,留学作业代写-天才代写 全优代写 - 北美Essay代写,Report代写,留学生论文代写作业代写 北美顶级代写|加拿大美国论文作业代写服务-最靠谱价格低-CoursePass 论文代写等留学生作业代做服务,北美网课代修领导者AssignmentBack