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

Databases (BD)

Credits Dept. Type Requirements
9.0 (7.2 ECTS) ESSI
  • Compulsory for DIE
  • Compulsory for DCSFW
  • Compulsory for DCSYS
PRED - Prerequisite for DIE , DCSYS
PS - Prerequisite for DCSFW

Instructors

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

General goals

This subject aims to teach students to structure and use the data saved in external memory devices by using databases, and to give them a grounding in the physical fundamentals to designing databases.

Specific goals

Knowledges

  1. Understanding what constitutes a database and the objectives required of a DBMS.
  2. Learn the relational database model, its languages (SQL and relational algebra), and the components usually found in relational databases.
  3. Learn the various physical structures for storing data, and their efficiency implications.
  4. Learn the various methods for accessing data, and their efficiency implications.
  5. Learn the concept of transactions and its implications.

Abilities

  1. Ability to define, create, and handle all database components using SQL.
  2. Being able to create database management programmes.
  3. Ability to transform simple conceptual models expressed in UML into the relational model.

Competences

  1. Ability to design systems, components and processes meeting certain needs, using the most appropriate methods, techniques and tools in each case.
  2. Ability to logically argue the decisions taken, work done, or a given viewpoint. Ability to give well-reasoned opinions and reasons with a view to convincing others.
  3. Ability to work effectively in small groups to solve problems of middling difficulty.
  4. Ability to design systems, components and processes meeting certain needs, using the most appropriate methods, techniques and tools in each case.
  5. Ability to logically argue the decisions taken, work done, or a given viewpoint. Ability to give well-reasoned opinions with a view to convincing others.
  6. Ability to work effectively in small groups to solve problems of middling difficulty.

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
T      P      L      Alt    Ext. L Stu    A. time Total 
4,0 0 0 2,0 0 6,0 3,0 15,0
The database concept. Database models and design. Users. Languages. DBMS: Concept, objectives that it provides to databases and architecture.
  • Other activities:
    The student reads the material provided by the teacher, and related with the subject of the classes.

2. The relational model
T      P      L      Alt    Ext. L Stu    A. time Total 
2,0 0 0 2,0 0 4,0 2,0 10,0
Objectives and Origin. Data Structure. Operations. Integrity rules.
  • Other activities:
    The student reads the material provided by the teacher, and related with the subject of the classes.

3. Languages: Relational algebra and SQL
T      P      L      Alt    Ext. L Stu    A. time Total 
0 0 10,0 10,0 4,0 0 4,0 28,0
Introduction. Relational algebra. SQL.



  • Other activities:
    The student prepare the lab classes reading the material provided by the teacher. He/she also do exercices for knowing if he/she has achieved an understanding of the subject. See teaching methodology.
  • Laboratory
    See teaching methodology.

4. Logic components of a database
T      P      L      Alt    Ext. L Stu    A. time Total 
4,0 2,0 0 0 0 6,0 6,0 18,0
Database logic component concept. Data logic components: Schemes, domains, tables, views and assertions. Control logic components: Stored procedures, triggers, privileges and roles.

5. Introduction to the design of relational databases
T      P      L      Alt    Ext. L Stu    A. time Total 
2,0 2,0 0 0 0 6,0 6,0 16,0
Stages in the database design. Introduction to the understanding of simple UML conceptual models. Translation of simple UML conceptual models to relational database models.



Design logic: transformation in the relational model.

6. Databases transactions
T      P      L      Alt    Ext. L Stu    A. time Total 
4,0 4,0 0 0 0 8,0 8,0 24,0
Definition and properties of transactions. ACID. Transaction interferences. Serialisability. Recoverability. Concurrence control techniques. Isolation levels. Locking and isolation levels.



SQL isolation levels.



Recovery.

7. Physical components of a database and access methods
T      P      L      Alt    Ext. L Stu    A. time Total 
4,0 4,0 0 0 0 6,0 6,0 20,0
Introduction. Access methods. Costs.

8. SQL programming
T      P      L      Alt    Ext. L Stu    A. time Total 
0 0 6,0 6,0 4,0 0 1,0 17,0
Programming in Java and JDBC. Considerations about the design and implementation of programs with access to a dababase.
  • Other activities:
    The student prepare the lab classes reading the material provided by the teacher. He/she also do exercices for knowing if he/she has achieved an understanding of the subject. See teaching methodology.
  • Laboratory
    See teaching methodology.

9. Stored procedures and triggers
T      P      L      Alt    Ext. L Stu    A. time Total 
0 0 8,0 8,0 4,0 0 4,0 24,0
Stored procedures implementation using PL/PgSQL. Trigger implementations in PostgreSQL. Considerations about the design and implementation of procedures and triggers.
  • Other activities:
    The student prepare the lab classes reading the material provided by the teacher. He/she also do exercices for knowing if he/she has achieved an understanding of the subject. See teaching methodology.
  • Laboratory
    See teaching methodology.


Total per kind T      P      L      Alt    Ext. L Stu    A. time Total 
20,0 12,0 24,0 28,0 12,0 36,0 40,0 172,0
Avaluation additional hours 4,0
Total work hours for student 176,0

Docent Methodolgy

Theory/Problem classes (2.3 hous a week).
Theory classes. In the theory classes the teacher present a part of the contents of the course. Usually the teachers use slides, which the students should bring to the classes.
Problem classes. In the problem classes the students solve exercises on the content presented during the theory classes. These exercises are done in teams of three students following a cooperative learning technique.
Problem exams. There are four problem classes that consists on one exam and that constitute an evaluative instrument of the course.

Lab classes (1.7 hours a week)
The work at classes is in teams of 2 students. In the first five minutes the students answer a qüestion individually. After that the students have the opportunity to share concerns with his/her teammate on the home work of the previous weed, and if it is necessary they ask the teacher questions unresolved. Then the students do the activities stated by the teacher and finally they solve a moodle/LearnSQL questionnaire.
Laboratory exams: There are four laboratory classes that consists on one exam and that constitute an evaluative instrument of the course.

Guided work (Other activities, 2 hours a week). Every week the students have a work guided by the teacher that will have to do during the week and that will end with the resolution of a moodle/LearnSQL questionaire.

Resources related to laboratory classes:
All documents, materials and questionnaires related with the course are available to students through the platform moodle/LearnSQL.
Apart from the feedback that teachers give to students during classes, the platform moodle/LearnSQL includes a corrector of database exercises that provides feedback to students about exercises solutions. In the laboratory exams the students use this corrector to deliver the solution of the exercises and to obtain feedback and if they wish to perform several retries. Teachers review and evaluate the solutions taking into account the grade of the corrector and the quality criteria established.
Classes and laboratory exams are held in a computer classroom. The servers used during the classes, are also available for access from home, so they can be used in independent learning activities.

Students will be evaluated just in case they assist to the GROUP WHERE ARE ENROLLED, both in classes of theory/problems and in classes of laboratory.

Evaluation Methodgy

Evaluative Instruments:

- First problem evaluative class: Views and / or assertions
- Second problem evaluative class: relational translation
- Third problem evaluative class: transactions and concurrency
- Fourth problem evaluative class: Storage

- First lab evaluative class: Algebra and SQL
- Second lab evaluative class: procedures / Triggers
- Third lab evaluative class: Programming with SQL

- Final exam

The grade for the course is calculated taking into account:
- NPR: problem grade. Average of the three problems submitted in problem evaluative classes.
- NLB: Laboratory grade. It is calculated as the 40% of the grade of the part algebra / SQL, 30% of the grade of the part of procedures / triggers and 30% of the grade of the part of programming with SQL - JDBC. The grade of each part depends on: the grade of the exam of the part (NLL), the average of the grades of the questions that the students answer at the beginning of the laboratory classes (NPCL). The grade is 85% NLL + 15% NLL (if NPCL> = 5).
- NEF: Grade of the final exam.
Grade of the course = Max(NPR*0:15 + NLB*0.25 + NEF*0.60, NLB*0,25+NEX*0,75)

Students will be evaluated just in case they assist to the GROUP WHERE ARE ENROLLED, both in classes of theory/problems and in classes of laboratory.

Any attempt of fraud during the course will imply the application of the general academic regulations of the UPC

Basic Bibliography

  • Jaume Sistac Planas Bases de dades;, EDIUOC, 2005.
  • Jaume Sistac Planas Tècniques avançades de bases de dades, EDIUOC, 2000.
  • Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom Database systems : the complete book, Prentice Hall, 2009.
  • im Melton, Alan R. Simon SQL : understanding relational, language components, Morgan Kaufmann, 2002.
  • Ramakrishnan, R.; Gehrke, J.; Database Management Systems, McGraw-Hill Higher Education, 2003.

Complementary Bibliography

  • Peter Gulutzan, Trudy Pelzer SQL-99 complete, really, R & D books, 1999.
  • Jim Melton, Andrew Eisenberg Understanding SQL and Java together : a guide to SQLJ, JDBC, and related technologies, Morgan Kaufmann Publishers, 2000.
  • Ling Liu, M. Tamer Özsu (Eds) Encyclopedia of Database Systems , Springer, 2009.
  • Elmasri, Ramez; Navathe, Shamkant B.; Fundamentals of database systems, Addison Wesley, 2007.

Web links

  1. https://www.upc.edu/learn-sql


  2. http://www.postgresql.org/


Previous capacities

Learn the data structure employed in internal memory.
Ability to assess these data structures.
Ability to produce programmes of medium complexity.


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