Design and Administration of Databases (DABD)

In the preceding course "Databases" some DB models have been studied, particularly the Relational Model, and some languages related to SQL and Relational Algebra. In this course it is intended to learn how to make the logical design of a RDB (Relational DB) for Information Systems, discussing the problems and the multiplicity that this implies. The resulting logical schema will be implemented using some relational system, analyzing its difficulties and limitations. Finally we will study the characteristics for a good physical design of the RDB, and particularly these optimization and tuning parameters that are the most common in the market's relational systems.

Knowledges

1. Obtaining the logic scheme of a database in an IS.
2. Normal forms of a logic scheme of an operational IS.
3. Differences between decisional and operational systems.
4. Physical scheme of a database.
5. Query optimisation.

Abilities

1. 
   
   Turning the UML (Unified Modelling Language) scheme into SQL (Structured Query Language) for creating tables, key expressions, and integrity constraints.
2. Distinguish between decisional and operational environments.
3. Understand the access plan of a query.
4. Optimisation of the critical queries in an IS.
5. DBMS tuning.

Competences

1. Ability to solve problems through the application of scientific and engineering methods.
2. Ability to argue in a critical and logical-mathematical manner.
3. Ability to present a well-written report setting out one"s results and document submission of practical work).
   
   Oral and Written Communication.
4. Ability to work effectively in small groups to solve problems of middling difficulty.
5. Check for a good use of the available resources.

The course comprises theory and lab classes.

Theory: The theory classes comprise the teacher's explanations and constitute the main part of the course.

Laboratory: Mainly, the lab classes will be dedicated to the practice (with and without computer) of the concepts introduced in the theory classes, by means of markable exercices that will be done during the class time. Some tools will be used for the design and administration of RDB, and to practice on a specific DBMS (Oracle).

Final mark = 70% min(10,P) + 20% E + 10%C

E = final exam grade
P = weighted average grade of questionnaires and 11 lab sessions
C = peer evaluation

Calculation of P:
1) Multiply the grade from activity by a weight equal to 1, 2, 4 or 8 (depending on the content of the corresponding activity)
2) Divide the sum of these values by the sum of weights assigned minus 8

Calculation of C: students will have multiple partners during the semester and they will evaluate them at the end. Based on these evaluations, the teacher will assign a grade.

Exam: students may or may not choose some of the questions depending on their participation int the proposal of questions during the semester.

• Jaume Sistac i Planas, coordinador; Albert Abelló Gamazo ... [et al.] Disseny de bases de dades, Universitat Oberta de Catalunya, 2002.
• Dennis E. Shasha Database tuning : a principled approach, Prentice Hall, 1992.
• Raghu Ramakrishnan, Johannes Gehrke Database management systems, McGraw-Hill, 2003.
• Toby J. Teorey Database modeling and design, Morgan Kaufmann, 1999.
• Sam Lightstone, Toby Teorey, Tom Nadeau Physical database design : the database professional's guide to exploiting indexes, views, storage, and more, Morgan Kaufmann Publishers, 2007.
• Golfarelli, Matteo and Rizzi, Stefano Data Warehouse design, McGraw-Hill, 2009.

• Peter Gulutzan, Trudy Pelzer SQL-99 complete, really, R & D books, 1999.
• Jim Melton, Alan R. Simon SQL : understanding relational, language components, Morgan Kaufmann, 2002.
• W.H. Inmon, Claudia Imhoff, Ryan Sousa Corporate information factory, John Wiley, 2001.
• C. T. Yu and W. Meng Principles of database query processing for advanced applications, Morgan Kaufmann, 1998.
• Ralph Kimball ...[et al.] The Data warehouse lifecycle toolkit : expert methods for designing, developing, and deploying data warehouses, John Wiley & Sons, 1998.
• Ling Liu, M. Tamer Özsu (Eds.) Encyclopedia of Database Systems, Springer, 2009.
• Malinowski, Elbieta and Zimányi, Esteban Advanced Data Warehouse Design, Springer , 2008.

1. http://www.oracle.com
   



2. http://www.olapreport.com
   

A prerequisite is that students must have taken the "Databases (BD)" and "Software Engineering 1 (ES1)" courses.