Databases
Code: 43848 ECTS Credits: 6| Degree | Type | Year |
|---|---|---|
| 4315985 Geoinformation | OB | 0 |
Contact
- Name:
- Meritxell Gisbert Traveria
- Email:
- meritxell.gisbert@uab.cat
Teaching groups languages
You can view this information at the end of this document.
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
- Carry out the database creation process automatically from models developed with CASE programmes.
- Choose and set up a suitable sequence of GIS operations to solve a particular problem in the construction of geospatial data.
- Conceive and implement procedures of combined consultation and exploitation on alphanumeric and spatial databases, in client-server environments of corporate information systems.
- Consult and manage spatial data bases and spatial SQL language.
- Continue the learning process, to a large extent autonomously.
- Develop and apply procedures for implementation and loading of data onto alphanumeric and spatial databases.
- Develop imaginative, creative and innovative ideas in projects for geospatial information systems, services, products or applications.
- 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.
- Know and apply the concepts, methodologies and languages of conceptual and logical design of alphanumeric and spatial databases.
- Know and consistently apply cartographic analysis and maps algebra operations using spatial SQL language.
- Know and use the language of SQL databases to solve complex problems of database consultation, creation, manipulation and modification.
- Model each one of the different spatial configurations using the structures of the different geospatial data models.
- Perform the conceptual and logical design of alphanumeric and spatial databases using computer-aided software engineering (CASE) programmes.
- Pick the most suitable geospatial data model for each application case.
- Recognise the different spatial configurations or conceptual patterns in a particular universe of geographic entities to be represented.
- 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.
- 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.
Introduction.
Geometry storage in databases.
Spatial Query Language.
Types of spatial databases.
Spatial indexes.
2. Simple Feature Access.
Introduction.
Basic methods in geometric objects.
Methods for testing spatial relationships between geometric objects, dimensionally extended 9-Intersection Model (DE-9IM).
3. Conceptual schemes of geographic information.
Spatial configurations based on elements.
Spatial configurations based on sampling.
Spatial configurations to represent surfaces.
Spatial configurations of the representation by locations.
4. Spatial reference system.
Components of spatial reference systems.
Dynamic transformation of spatial reference systems.
Standard coding of spatial reference systems.
Storing data in geographic coordinates in spatial databases.
Activities and Methodology
| Title | Hours | ECTS | Learning Outcomes |
|---|---|---|---|
| Type: Directed | |||
| Lectures and practical exercises in a computer lab | 36 | 1.44 | 2, 3, 9, 10, 11, 12, 14, 15, 18 |
| Type: Supervised | |||
| Semester project | 15 | 0.6 | 1, 2, 3, 5, 6, 7, 8, 9, 10, 13, 14, 16, 17, 18 |
| Type: Autonomous | |||
| Solving practical exercies using specific software and suggested bibliography. Personal study | 69 | 2.76 | 1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18 |
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.
Annotation: Within the schedule set by the centre or degree programme, 15 minutes of one class will be reserved for students to evaluate their lecturers and their courses or modules through questionnaires.
Assessment
Continous Assessment Activities
| Title | Weighting | Hours | ECTS | Learning Outcomes |
|---|---|---|---|---|
| Oral presentations | 25 | 7.5 | 0.3 | 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18 |
| Practical exercises | 30 | 9 | 0.36 | 1, 2, 3, 4, 6, 8, 9, 10, 11, 12, 13, 14, 15 |
| Report submissions | 45 | 13.5 | 0.54 | 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18 |
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. This subject/module does not incorporate single assessment.
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 31st 2025.
1st semester project oral presentation: Making during all the semester. Oral presentation at the end of semester, on February 7th 2025.
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 1st semester or made the Oral Defense of the project ofthe 1st semester. Students will obtain a Not assessed/Not submitted course grade unless they have submitted more than 1/3 of the assessment items.
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.
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 Systems 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)
Support Webs:
Tutorials SIG català (Open Model Sphere intro 00): https://youtu.be/rPWG0-DuXmc
Tutorials SIG català (Open Model Sphere intro 01): https://youtu.be/blPdP9ij1dQ
Tutorials SIG català (Open Model Sphere intro 02): https://youtu.be/rmftnefDnro
Tutorials SIG català (Install pgADMIN4): https://youtu.be/WO_lJsXZhKg
W3schools (SQL): https://www.w3schools.com/sql/
Software
ArcGis Desktop
Qgis
Postgre SQL
Postgis
PgAdmin
Enterprise Architect
Open ModelSpehere
Language list
| Name | Group | Language | Semester | Turn |
|---|---|---|---|---|
| (PLABm) Practical laboratories (master) | 1 | Spanish | first semester | morning-mixed |
| (TEm) Theory (master) | 1 | Spanish | first semester | afternoon |