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

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

Final = 50% P + 50% E

E = final 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

• Sistac, J. Disseny de Bases de Dades, Editorial UOC, 2002.
• Shasha, D. E. Database Tuning. A principled approach, Prentice Hall, 1992.
• Ramakrishnan, R and Gehrke, J. Database Management Systems, McGrau-Hill, 2003.
• Teorey, T.; Lightstone, S.; and Nadeau, T. Database Modeling and Design: Logical Design, Morgan Kaufmann publishers, 2006.
• Lightstone, S.; Teorey, T.; and Nadeau, T. Physical Database Design, Morgan Kaufmann Publishers, 2007.

• P. Gulutzan and T. Pelzer SQL-99 Complete, Really, R&D Books, 1999.
• J. Melton and A. Simon SQL'99, Morgan Kaufmann, 2002.
• W. Inmon, C. Imhof and R. Sousa Corporate Information Factory, John Willey & Sons, 1998.
• C. T. Yu and W. Meng Principles of database query processing for advanced applications, Morgan Kaufmann, 1998.
• Kimball, R.; Reeves, L.; Ross, M.; and Thornthwaite, W The Data Warehouse Lifecycle Toolkit, John Wiley & Sons, 1998.

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.