University of Linköping Department of Computer Science Tore Risch/Thomas Padron-McCarthy Exam in TDDB38 Database Technology -------------------------- Wednesday January 13, 1999, 9-13 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Utilities: None Points: Maximally 30 points. 15 points required to pass. Results: Will be sent through e-mail. Walk thru: The time will be sent along with the result. Examinator: Tore Risch. Teacher on duty: Thomas Padron-McCarthy, phone 070-734 70 13 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Instructions ------------ Read through the entire exam and make notes of eventual obscurities before you start solving the tasks. In addition to the instructions on the cover of the test the following holds: Write readably and clearly. Solutions that cannot be read will of course not get any points, and unclear sentences will be misunderstood. Additional assumptions to those in each task must be stated. Stated assumptions may of course not modify the given task. Please write on one side of the paper only, and don't use a red pen. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ GOOD LUCK! Scenario for problem 1-4: Because of their love for mankind, and to extend the tax base, the Swedish government has decided to change Sweden into an imperialist super-power. All other countries will be Swedish colonies. A colonial department is created, and this colonial department needs a database to keep track of the colonies. What you need to store is this: 1. The colonies. Each colony has a unique number, a unique name (for example "France" or "USA"), a population, a capital city, and a name. 2. Swedes. Each Swede has a unique personal number, and a name. 3. Which Swedes have been placed in which colonies. 4. Tax payments. Taxes are payed from the colonies. Each tax payment consists of a certain amount, it originates from one of the colonies, and there is a Swede who is responsible for it. It also has a date. 1. (4p) Draw an E/R diagram for the database described above. Use the given information, but also remember that it must be possible to answer the queries in question number 4 below. If you need to make any extra assumptions when making the E/R diagram, then state these assumptions! E/R diagrams can be drawn in many different ways. Therefore you must explain the notation that you use! 2. (4p) Implement this database in the relational model, i. e. translate the E/R diagram to tables. State the relations, the attributes in each relation, and the primary key. The implementation should be a good implementation. 3. (3p) Make (and state) reasonable assumptions about the size of the database, and then choose suitable storage structures for the tables. 4. (4p) Formulate the following queries in SQL: a) Which Swedes have been placed in the colony "Norway"? b) What is the total sum of all the tax payments? c) Which tax payments have responsible Swedes who are not present in the colony from which the tax payment comes from? You can, if you like, simplify by defining views. 5. (3p) Describe similarities and differences between E-R and "Extended E-R". Describe similarities and differences between an OO data model and "Extended E-R". 6. (3p) What is QBE? Formulate in QBR the join query to find those who earn more than their manager's secretary. Sort salaries in descending order. (Assume and state suitable tables for this example.) 7. (5p) What are the important differences between an Object-Oriented and an Object-Relational database? Compare when it is more favourable to use relational, Object-Oriented, or Object-Relational databases, respectively. What is swizzling and what is it used for? 8. (4p) What is a spatial database and when is it useful? Describe the principles of R-trees and Quad trees, as well as what they are good for.