Databases

Code:

CC301

Acronym:

CC301

Keywords
Classification	Keyword
OFICIAL	Computer Science

Instance: 2013/2014 - 1S

Active?	Yes
Web Page:	http://www.dcc.fc.up.pt/~ricroc/aulas/1314/bd
Responsible unit:	Department of Computer Science
Course/CS Responsible:	Bachelor in Geology

Cycles of Study/Courses

Acronym	No. of Students	Study Plan	Curricular Years	Credits UCN	Credits ECTS	Contact hours	Total Time
L:AST	11	Plano de Estudos a partir de 2008	3	-	7,5	-
L:B	2	Plano de estudos a partir de 2008	3	-	7,5	-
L:CC	26	Plano de estudos de 2008 até 2013/14	3	-	7,5	-
L:F	1	Plano de estudos a partir de 2008	3	-	7,5	-
L:G	0	P.E - estudantes com 1ª matricula anterior a 09/10	3	-	7,5	-
		P.E - estudantes com 1ª matricula em 09/10	3	-	7,5	-
L:M	2	Plano de estudos a partir de 2009	3	-	7,5	-
L:Q	0	Plano de estudos Oficial	3	-	7,5	-
MI:ERS	30	Plano de Estudos a partir de 2007	3	-	7,5	-
M:MAO	0	PE Mestrado em MAOPI	1	-	7,5	-	-
M:SIG	0	PE do Mestrado em Sistemas de Informação Geográfic	1	-	7,5	-

Teaching language

Portuguese

Objectives

Introduce students to the fundamental theoretic and practical principals of database systems.

Learning outcomes and competences

The students should acquire the following competences:

(a) understand what a database management system is, know the extent of its application in the real world and the technological problems associated with its design, implementation and existing solutions;

(b) design database models using the ER/EER models and the relational data model and implement such models in a database management system;

(c) use the SQL language to manipulate a database management system.

Working method

Presencial

Program

Basic Concepts
Main characteristics and advantages of using the database approach. Typical DBMS Functionality. The database design process.

The ER and EER Models
The ER model: entity types, weak entity types, attributes, relationships and key attributes. The EER Model: subclasses, superclasses, specialization, generalization, categories. ER and EER diagrams.

The Relational Data Model
Relations, attributes, domains and tuples. Superkeys, primary keys and foreign keys. Integrity Constraints. Relational schema. Mapping the ER and EER models to the relational model.

The Relational Algebra and Calculus
Relational algebra operations. The union compatibility condition. Tuple relational calculus and domain relational calculus. Quantifiers and safe expressions.

The SQL Language
The DDL (Data Definition Language) of SQL: data definition, constraints and domains. The DML (Data Manipulation language) of SQL: simple, nested and correlated queries. Join, aggregate, insert, remove and update operations. Views, assertions and triggers.

Database Programming
Integration problems. Embedded SQL in C. Cursors. The MySQL C API. Persistent stored modules (SQL/PSM). Concurrency and fault tolerance: transactions, logs and checkpoints.

Interfacing MySQL with PHP
Brief introduction to PHP and to the MySQL PHP API.

Normalization
Normal forms and normalization of relations: 1NF, 2NF, 3NF, BCNF, 4NF e 5NF. Functional, multivalued and join dependencies. Non-additive join decomposition.

Physical Data Organization
Basic file structures, hashing and indexing.

Mandatory literature

Elmasri Ramez; Fundamentals of database systems. ISBN: 0-321-20448-4

Complementary Bibliography

Ullman Jeffrey D., 1942-; A first course in database systems. ISBN: 0-13-887647-9
A. Silberschatz, H. Korth and S. Sudarshan; Database Systems Concepts 5/E, McGraw-Hill
M. Davis and J. Phillips; Learning PHP and MySQL, O'Reilly
H. Williams and D. Lane; Web Database Applications with PHP and MySQL, O'Reilly

Teaching methods and learning activities

Theory classes and practical classes with practical assignments.

Evaluation Type

Distributed evaluation with final exam

Assessment Components

designation	Weight (%)
Exame	75,00
Trabalho laboratorial	25,00
Total:	100,00

Amount of time allocated to each course unit

designation	Time (hours)
Estudo autónomo	98,00
Frequência das aulas	70,00
Trabalho laboratorial	34,50
Total:	202,50

Calculation formula of final grade

Students are assessed by their performance in the following components:

- An SQL submission to be realized by the end of the semester. The grade of this component is 5/20 (25% of the final grade).

- A written examination in the final of the semester. The grade of this component is 15/20 (75% of the final grade).

Examinations or Special Assignments

Students are assessed by their performance in the following components:

- An SQL submission to be realized by the end of the semester. This submission consists on solving a set of SQL queries using the tools studied on the practical classes.

- A written examination in the final of the semester consisting of two parts: a theoretical part with questions about the fundamental concepts learned during the course and a more practical component with questions similar to the ones solved during the practical classes.