Saltar al contingut Saltar a navegacio
  • Enlarging letters
  • Home
  • Information
  • Contacting
  • Map

Design and Administration of Databases (DABD)

Credits Dept. Type Requirements
7.5 (6.0 ECTS) ESSI
  • Elective for DIE
  • Elective for DCSFW
  • Elective for DCSYS
BD - Prerequisite for DIE , DCSYS , DCSFW
ES1 - Prerequisite for DIE , DCSYS , DCSFW

Instructors

Person in charge:  Alberto Abello Gamazo (aabello@essi.upc.edu)
Others:Xavier Burgués Illa (diafebus@essi.upc.edu)

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 Design traps
2.5 Classes vs relationships
2.6 Multivalued attributes
2.7 Normalization
2.8 CASE tools
  • Laboratory
    Using a CASE tool to design a RDB.

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)

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

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

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

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.

Previous capacities

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


Share this page
  • Forum
  • Twitter
  • Menéame
  • Delicious
  • Yahoo! bookmarks
  • Google bookmarks
  • LinkdedIn
  • Facebook

 
logo FIB © Barcelona school of informatics - webmaster@fib.upc.edu - RSS