Saltar al contingut Menu
Map
  • Home
  • Information
  • Contact
  • Map

Design and Administration of Databases (DABD)

Credits Dept.
7.5 (6.0 ECTS) ESSI

Instructors

Person in charge:  (-)
Others:(-)

General goals

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.

Specific goals

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.

Contents

Estimated time (hours):

T P L Alt Ext. L Stu A. time
Theory Problems Laboratory Other activities External Laboratory Study Additional time

1. Introduction to DB design
T      P      L      Alt    Ext. L Stu    A. time Total 
3,0 0 1,0 0 0 1,0 0 5,0
1.1 DB design in a corporate IS.
1.2 Stages in the design of a database.
1.3 Design alternatives.

2. Logical design of a RDB
T      P      L      Alt    Ext. L Stu    A. time Total 
11,0 0 6,0 0 5,0 15,0 0 37,0
2.1 Null values
2.2 Relationships translation
2.3 Generalization/specialization translation
2.4 Classes vs relationships
2.5 Multivalued attributes
2.6 Normalization
  • Laboratory
    Relational translation

3. Support systems for decision taking
T      P      L      Alt    Ext. L Stu    A. time Total 
5,0 0 1,0 0 1,0 5,0 0 12,0
3.1 Data storage
3.2 Multidimensional databases (OLAP tools)
  • Laboratory
    Statement of multidimensional queries with SQL

4. Physical design of a RDB
T      P      L      Alt    Ext. L Stu    A. time Total 
4,0 0 8,0 0 9,0 4,0 0 25,0
4.1 Script creation
4.2 Implementation of integrity restrictions
4.3 Surrogates
4.4 Definition and load deadlock
4.5 Horizontal and vertical partitioning
4.6 Aggregation
4.7 Views materialization
  • Laboratory
    Implementation of the physical design in a DBMS (Oracle)
  • Additional laboratory activities:
    Installation of a DBMS (Oracle).

5. Query optimisation
T      P      L      Alt    Ext. L Stu    A. time Total 
12,0 0 4,0 0 4,0 16,0 0 36,0
5.1 Semantic optimisation
5.2 Syntatic optimisation
5.3 Access paths
5.4 Sorting and projection algorithms
5.5 Join algorithms
5.6 Middle results estimation
5.7 Advanced indexing techniques
  • Laboratory
    Access structures definition.

6. Administration
T      P      L      Alt    Ext. L Stu    A. time Total 
9,0 0 6,0 0 8,0 9,0 0 32,0
6.1 Introduction
6.2 Monitors and query plan
6.3 Work load
6.4 Transactions
6.5 Security
6.6 Files and system parameters
  • Laboratory
    Obtaining and interpreting the query plan


Total per kind T      P      L      Alt    Ext. L Stu    A. time Total 
44,0 0 26,0 0 27,0 50,0 0 147,0
Avaluation additional hours 3,0
Total work hours for student 150,0

Docent Methodolgy

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

Evaluation Methodgy

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.

Basic Bibliography

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

Complementary Bibliography

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

Web links

  1. http://www.oracle.com


  2. http://www.olapreport.com


Previous capacities

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


Compartir

 
logo FIB © Barcelona school of informatics - Contact - RSS
This website uses cookies to offer you the best experience and service. If you continue browsing, it is understood that you accept our cookies policy.
Classic version Mobile version