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).

Along the course there will be closed sessions for lab work. All of them will be considered for the final grade, as follows:

Final = 80% P + 20% E

E = final theory exam mark
P = weighted average of the lab marks

Obtaining P:
1) Multiply the mark of every session by a weight that can be 1, 2, or 4 (depending on the contents of the corresponding practice)
2) Divide the sum of those values by the sum of the assigned weights minus 4

• 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.
• Malinowski, Elbieta and Zimányi, Esteban Advanced Data Warehouse Design, Springer, 2008.

• 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.

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.