Logo UAB
2020/2021

Databases

Code: 43848 ECTS Credits: 6
Degree Type Year Semester
4315985 Geoinformation OB 0 1
The proposed teaching and assessment methodology that appear in the guide may be subject to changes as a result of the restrictions to face-to-face class attendance imposed by the health authorities.

Contact

Name:
Joan Cristian Padró García
Email:
JoanCristian.Padro@uab.cat

Use of Languages

Principal working language:
spanish (spa)

Prerequisites

This course has no specific requirements, but it is highly recommended to have basic knowledge of information technologies. Specially of spread worksheets and personal databases.

Objectives and Contextualisation

The main goal of the course is to provide an advanced knowledge on the principles and applications of database management systems and their spatial extensions in order to be able of:

  • developing corporate alphanumeric databases in contexts of medium to high complexity.
  • implementig spatial databases as the core of geographic information systems..

The course emcompasses the methodologies and tools for database design, either relational (entity-relationship model) or object-oriented (UML), using computer aided software engineering (CASE) programs. It is developed by means of actual study cases that cover the full process of design, implementation, operation and administration of information systems, both generic and spatial, in corporative, complex, open, distributed and interoperable contexts.

Secondly, the course includes the knowledge of all the processes and tools for database implementation and administration.

Last but not least, the course covers also the proficient use of the universal database language SQL (Structured Query Language), both general and spatially extended, for administration tasks and, most of all, for querying alphanumeric and spatial databases.

Competences

  • Continue the learning process, to a large extent autonomously.
  • Design and manage geospatial information systems, integrating spatial and alphanumerical, relational and object-oriented data bases, in client-server distributed architectures, or those oriented to services.
  • Develop and apply geospatial and alphanumeric information analysis methodologies to resolve urban or land management problems, generating useful information for the implementation of intelligent processes and for decision making.
  • Develop imaginative, creative and innovative ideas in projects for geospatial information systems, services, products or applications.
  • Differentiate between and use different data models and standard of geospatial information (digital cartography, spatial databases and metadata), and be able to recognise their respective components and capacities.
  • Solve problems in new or little-known situations within broader (or multidisciplinary) contexts related to the field of study.
  • Use acquired knowledge as a basis for originality in the application of ideas, often in a research context.

Learning Outcomes

  1. Carry out the database creation process automatically from models developed with CASE programmes.
  2. Choose and set up a suitable sequence of GIS operations to solve a particular problem in the construction of geospatial data.
  3. Conceive and implement procedures of combined consultation and exploitation on alphanumeric and spatial databases, in client-server environments of corporate information systems.
  4. Consult and manage spatial data bases and spatial SQL language.
  5. Continue the learning process, to a large extent autonomously.
  6. Develop and apply procedures for implementation and loading of data onto alphanumeric and spatial databases.
  7. Develop imaginative, creative and innovative ideas in projects for geospatial information systems, services, products or applications.
  8. Evaluate the quality of the different phases in the process of database design and implementation and their alignment with the proposed aims of the system.
  9. Know and apply the concepts, methodologies and languages of conceptual and logical design of alphanumeric and spatial databases.
  10. Know and consistently apply cartographic analysis and maps algebra operations using spatial SQL language.
  11. Know and use the language of SQL databases to solve complex problems of database consultation, creation, manipulation and modification.
  12. Model each one of the different spatial configurations using the structures of the different geospatial data models.
  13. Perform the conceptual and logical design of alphanumeric and spatial databases using computer-aided software engineering (CASE) programmes.
  14. Pick the most suitable geospatial data model for each application case.
  15. Recognise the different spatial configurations or conceptual patterns in a particular universe of geographic entities to be represented.
  16. Solve problems in new or little-known situations within broader (or multidisciplinary) contexts related to the field of study.
  17. Use acquired knowledge as a basis for originality in the application of ideas, often in a research context.
  18. Use the foremost free and proprietary software for database and spatial database management.

Content

Database management systems

1.  Databases. Features and evolution.

Concept of database.

Database management system.

Properties of databases.

User types.

History of databases.

2.  Database management systems (DBMS).

Properties of DBMS.

Functions of DBMS.

Abstraction levels.

Examples of DBMS.

DBMS architecture.

Applications and types of DBMS.

3.  Dara models for databases.

Historical data models.

Relational data model.

Other data models.

4.  Conceptual models.

Entity-Relationship model, ER.

Enhanced Entity-Relationship model, EER.

Unified Modeling Language, UML.

CASE software.

5.  Batabase creation process.

Database design.

Database implementation: database schema creation.

Data load.

6.  Database design.

Conceptual database design.

Logical database design.

7.  SQL, Structured Query Language.

Introduction.

Data definition language (DDL) and data manipulation language (DML).

SQL statements for data definition.

SQL statements for data manipulation.

SQL statements for queries.

SQL statements to combine tables.

Queries for view and table creation.

Nested queries.

8.  Database administration.

Creació de bases de dades, esquemes i espais d'emmagatzematge.

Creació i gestió d'usuaris.

Seguretat i recuperació.

 

Spatial databases

1.  Spatial databases.

Geometry storage in databases.

Spatial query language.

Types of spatial databases (standard, object-oriented).

2.  Modeling spatial relationships.

Topology basic concepts.

Planar topology (arc-node).

Dimensionally Extended nine-Intersection Model (DE-9IM).

3.  Conceptual schemas for geographic information.

Element-based spatial configurations.

Sample-based spatial configurations.

Spatial configurations for surface representation.

Spatial configurations used to represent locations.

4.  Geographic information data models.

Feature model without topology.

Feature model with planar topology (arc-node).

Standard model: Simple Feature Access.

Geodatabase model.

GML model.

Cmparison of spatial data models.

5.  Spatial query language: extended SQL.

Spatial query functions.

Measurement and geometric derivative functions.

Geometry construction functions.

Spatial analysis and manipulation functions.

6.  Topology in the spatial databases.

Rule-based topology.

Integrated topology.

7.  Raster data in the spatial databases.

Raster data in the spatial tables model.

Raster data in the geodatabase model.

8.  Spatial reference systems.

Components of spatial reference systems.

Dynamic transformation between spatial reference systems.

Standard encoding of spatial reference systems.

Geographic coordinates storage in the spatial databases.

9.  Spatial database design.

Specifying entities with spatial representation.

Specifying geometry and spatial relationships.

Implementation according to a specific data model.

Modeling spatial data with UML.

10. Spatial database implementation.

Procedures for spatial database schema creation.

Procedures for spatial data load.

11. Spatial database administration.

Data check-in and check-out procedures.

Versions management.

Spatial indexing.

Methodology

Learning is achieved by means of three types of activities.

Directed activities: Directed activities are theoretical and practical lectures in a computer lab. They include solving case studies and practical exercises. Lectures are the common thread of the course. Lectures serve to systematize all the content, to present the state of the art of the different subjects, to provide methods and techniques for specific tasks, and to sum up the knowledge to learn. Lectures organize also the autonomous and complementary work done by the students

Supervised activities: Supervised activities are focused on the execution of a semester project, consisting of a real case study, carried out through workshop hours, autonomous work and tutorials. This semester project allows to apply together all the knowledge and technical skills learnt in all the courses of the semester. The semester project is a milestone for the students and the actual demonstration that they had achieved the learning goals of all the courses of the semester. It is also the main evidence for evaluation as students should have to submit at the end of the semester a report that summarizes the whole project and do an oral presentation.

Autonomous activities: Autonomous work of the students includes personal readings, data and documentation search, complementary exercises and the personal development of the semester project.

The activities that cannot be done in person will be adapted to the possibilities offered by the UAB virtual tools. The exercises, projects and theoretical classes will be carried out through virtual tools, such as tutorials, videos, TEAMS sessions, etc. The teacher will ensure that the student can access or offer alternative means, when available.

Activities

Title Hours ECTS Learning Outcomes
Type: Directed      
Lectures and practical exercises in a computer lab 36 1.44 3, 10, 9, 11, 14, 12, 15, 2, 18
Type: Supervised      
Semester project 15 0.6 8, 3, 10, 9, 6, 7, 14, 13, 1, 16, 5, 2, 17, 18
Type: Autonomous      
Solving practical exercies using specific software and suggested bibliography. Personal study 69 2.76 8, 3, 10, 9, 11, 6, 7, 14, 13, 12, 1, 16, 5, 15, 2, 17, 18

Assessment

In the event that tests or exams cannot be taken onsite, they will be adapted to an online format made available through the UAB’s virtual tools (original weighting will be maintained). Homework, activities and class participation will be carried out through forums, wikis and/or discussion on Teams, etc. Lecturers will ensure that students are able to access these virtual tools, or will offer them feasible alternatives.

CONTINUOUS EVALUATION

a) Evaluation procedure and activities:

Evaluation of the course is based mostly on the semester project, that comprises two evaluation activities. The elaboration and submission of a synthesis report and the oral presentation of the project done. Given the technical content of the course, the weight assigned to the project report is 45% of the total course grading, assuming that it is the most appropriate means to explain all the technical details of the project, and a weight of 25% at the oral presentation. The course assessment is completed with the evaluation of the practical exercises done along the course, that account for another 30% of the total course grading.

Except when expressly noticed, all the evaluation activities (report and oral presentation of the semester project, as well as practical exercises) have to carried out individually.

Time assigned to each evaluation activity includes the time spent in making all the material evidences for evaluating each activity (e.g., writing of the report, preparing the presentation slides, etc.).

b) Evaluation schedule:

1st semester project report: Making during all the semester. Submission at the end of semester, on January 22nd 2020.

1st semester project oral presentation: Making during all the semester. Oral presentation at the end of semester, on January 28th and 29th 2020.

Course practical exercises: Making and submission weekly or biweekly along the semester.

c) Grade revision:

Once the grades obtained are published, students will have one week to apply for a grade revision by arranging an appointment with the corresponding teachers.

d) Procedure forreassessment:

1st semester project report: It could be reassessed in the following two weeks after the submission date scheduled. Reassessment will require the submission of a new whole report in case of negative evaluation of the former report submitted.

1st semester project oral presentation: It could be reassessed in the following week after the date scheduled for the oral presentation. Reassessment will require doing again the oral presentation in case of negative evaluation of the former presentation done.

Course practical exercises: Can not be reassessed.

To have right to a reassessment the student will have to have been previously evaluated in a set of activities that account for at least two thirds of the total course grading. Therefore he or she will have to have been evaluated of the 1st semester project report (45%) and of the 1st semester project oral presentation (25%) in the dates scheduled.

The right to a reassessment will only be granted to students that, having not passed the course (e.g., having a total course grade below 5 over 10), had obtained at least a total course grade above 3,5 over 10.

e) Conditions for the rating 'Not evaluable':

The student will receive the grade of 'Not evaluable' instead of 'Suspended' if he/she has not submitted the Report of the project of the 1stsemester or made the Oral Defense of the project of the 1st semester. That is, if the student has only delivered part or all the practical exercises in the module.

f) UAB regulations regarding plagiarism and other irregularities in the evaluation process:

In the event of a student committing any irregularity that may lead to a significant variation in the grade awarded to an assessment activity, the student will be given a zero for this activity, regardless of any disciplinary process that may take place. In the event of several irregularities in assessment activities of the same subject, the student will be given a zero as the final grade for this subject.

Assessment Activities

Title Weighting Hours ECTS Learning Outcomes
Oral presentations 25 7.5 0.3 8, 3, 4, 10, 9, 11, 6, 7, 14, 13, 12, 1, 16, 5, 15, 2, 17, 18
Practical exercises 30 9 0.36 8, 3, 4, 10, 9, 11, 6, 14, 13, 12, 1, 15, 2
Report submissions 45 13.5 0.54 8, 3, 4, 10, 9, 11, 6, 7, 14, 13, 12, 1, 16, 5, 15, 2, 17, 18

Bibliography

Date, C.J. (2003) An Introduction to Database Systems, 8th edition. Harlow, Essex, UK: Pearson Education Lted.. 1024 pp. (ISBN 978-0321197849)

Date, C.J. (2015) SQL and Relational Theory. How to Write Accurate SQL Code, 3rd. edition. Sebastopol, California: O'Reilly Media.  582 pp. (ISBN 978-1491941171)

Elmasri, R.B. and Navathe, S. (2015) Fundamentals of Database Systems, 7th edition. Harlow, Essex, UK: Pearson Education Lted.. 1272 pp. (ISBN 978-0133970777)

Faroult, S. (2006) The Art of SQL. Sebastopol, California: O'Reilly Media.  372 pp. (ISBN 978-0596008949)

Fu, Pinde and Sun, Jiulin (2010). Web GIS: Principles and Applications. Redlands, California: ESRI Press. 450 pp. (ISBN-10: 978-1589482456)

ISO/IEC (2006) ISO/IEC 13249-3:2006(E) – Text for FDIS Ballot Information technology - Database languages - SQL Multimedia and Application Packages - Part 3: Spatial, Geneva, Switzerland: International Organization for Standardization (ISO)

Longley, P.A.; Goodchild, M.F.; Maguire, D.J. and Rhind, D.W. (2015). Geographical Information Sys­tems and Science, 4th edition. Hoboken, New Jersey: John Wiley & Sons. 560 pp. (ISBN: 978-0470721445) 

Moestl Vasilik, S. (2017) SQL Practice Problems: 57 beginning, intermediate, and advanced challenges for you to solve using a “learn-by-doing” approach. 144 pp. (ISBN 978-1520807638)

Molinaro, A. (2005) SQL Cookbook: Query Solutions and Techniques for Database Developers. Sebastopol, California: O'Reilly Media.  636 pp. (ISBN 978-0596009762)

Nunes, Joan (2012) Diccionari terminològic de sistemes d'informació geogràfica. Barcelona: Enciclopèdia Catalana i Institut Cartogràfic i Geològic de Catalunya. 551 pp. (ISBN 978-84-393-8863-0). Consultable en línia a http://www.termcat.cat/ca/Diccionaris_En_Linia/197

Nunes, Joan i Badia, Anna (2020) Sistemes d'Informació Geogràfica. Barcelona: Institut Cartogràfic i Geològic de Catalunya. (en preparació)

OGC (2011a) OpenGIS Implementation Standard for Geographic information - Simple feature access - Part 1: Common architecture (Version 1.2.1). Wayland, MA: Open Geospatial Consortium.

OGC (2011b) OpenGIS Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option (Version 1.2.1). Wayland, MA: Open Geospatial Consortium.

Shekhar, S. and Chawla, S. (2003) Spatial Databases: A Tour, Upper Saddle River, NJ: Prentice Hall.

Shekar, S. and Xiong, H. (eds.) (2008). Encyclopedia of GIS. New York: Springer. 1370 pp. (ISBN: 978-0387359755)

Zeiler, Michael (2010). Modeling Our World: The ESRI Guide to Geodatabase Concepts. 2nd edition. Redlands, California: ESRI Press. 308 pp. (ISBN: 978-1589482784)