C.1
C.2
C.3
C.4
C.5
C.6
C.7
C.8
C.9
C.10
Exercises 1-3 will be performed in the publicly available and free LibreOffice package [13]. It must be installed first, paying particular attention to the Base and Writer modules.
Exercises 4-10 will be performed in applications that allow access to the SQL database PostgreSQL - PSQL [10] or PGAdmin [11]. You should first download the PSQL program, unpack it and you can use it. For the more demanding - the PGadmin program. PSQL for win32 is available here: PSQL.7z [10].
Note! Access to the Postgresql database requires prior connection to the AGH network via VPN.
_
_
L 1
Introductory lecture. Database system components.
Types of databases. Selected functionalities of
database systems. Access to data. Data types.
Selected functionalities of databases.
L 2
Relational databases. Relational database
postulates. Formal notation of the relational model.
The concept of a relational database. Entity
relationships. Entity relationship diagrams.
L 3
Relational databases. Keys. Weak entity sets.
Transforming entity relationship diagrams into a
relational data model. Anomalies in relations.
Decomposition of relations. Functional dependencies.
Multivalued functional dependencies. Normalization of
the relational database model. First normal form
(1NF), second normal form (2NF), third normal form
(3NF), Boyce-Codd normal form (BCNF).
Decomposition to BCNF. Other normal forms.
L 4
Relational database operations. Simple set-theoretic
operations: union, difference, product. Compound
operations: projection, selection, Cartesian product,
quotient, theta join, natural join. Other joins: equijoin,
inner join, left outer join, right outer join, full outer
join, self-join. Compositions.
L 5
SQL language. SQL syntax. Basic data types in SQL.
Projection. Operators. Creating tables in SQL.
Inserting, modifying, and deleting data in tables.
Creating and deleting indexes.
L 6
SQL language. SELECT query. Aggregation
functions. Grouping data. Joins. Operations on sets.
Wykład 7
Query nesting (subqueries). Nested queries and joins.
Views (perspectives). Transactions. Stored procedures.
Triggers.
C 1
Data types. Constructing a simple table.
C 2
Construction of simple relational database schemas. Data definition. Constructing tables. Relationship constraints.
Getting to know the LibreOffice Base package. Preparation based on the materials from the lecture.
C 3
Designing relational databases (continued) - preparation based on lecture materials.
Construction of advanced data schemas.
Ćw. 4
Defining relational schemas in SQL. Please familiarize yourself with the possibilities (options) and syntax of the commands presented in the last lecture (keys!). The SQL language standard applies, S.Z.B.D. PostgreSQL [8], [9]. PSQL for win32 is available here: PSQL.7z [10].
Syntax: psql -h serveraddress databasename user (database name in exercises: database) (username: s[index_number])
Ćw. 5
Constructing simple queries (single table) to the database using the SELECT statement. Before the exercise, familiarize yourself with the syntax, capabilities and properties of this statement.
Ćw. 6
Constructing simple queries (continued). Aggregating and grouping data
Ćw. 7
Operations on sets. Nestings.
Ćw. 8
Internal and external joints.
Ćw. 9
Advanced syntactic constructs in SQL.
Ćw. 10
SQL Transactions. You should be familiar with the construction of SQL transactions, commits, and rollbacks. Which SQL statements can participate in a transaction? Keywords: {BEGIN, COMMIT, ROLLBACK}
References:
[1] Ullman, J.D., Widom J.: Podstawowy wykład z systemów baz danych. WNT, Warszawa 1999
[2] Delobel C., Adiba M.: Relacyjne bazy danych. WNT, Warszawa 1989
[3] Ullman J.D.: Systemy Baz Danych WNT, Warszawa 1988
[4] Fehily Ch.: SQL. Szybki start. Helion, Gliwice 2003. pdf
[5] Gruca A.: Bioinformatyczne bazy danych. PJWSTK, Warszawa 2010. pdf
[6] Widel, S., Widel, A., Spinczyk, D.: Overview of available open source PACS frameworks. Studia Informatica, 2016, 37(3A), 21-30. pdf
[7] Ladniak M., Piorkowski A., Banys R. P.: Przeglad otwartych rozwiazań systemów archiwizacji systemów archiwizacji i komunikacji obrazów medycznych. Komputerowe wspomaganie badań naukowych, WTN, Wrocław, 2014, pp. 79-88. pdf
[8] http://www.postgresql.org
[9] PostgreSQL: Documentation: 11: Part II. The SQL Language
[10] https://www.postgresql.org/docs/11/app-psql.html
[11] https://www.pgadmin.org/
[12] http://www.mysql.com/
[13] https://www.libreoffice.org
[101] D. Ciupek: Animal Transcription Factor Database (youtube)
[102] J. Staniszewski: NCI-Nature Pathway Interaction Database (youtube)
[103] N. Nowosinska: ConsensusPathDB (youtube)
[104] K. Kolodziej: COSMIC Cancer Database (youtube)
[105] Z. Schneider: Online Mendelian Inheritance in Man (youtube)
[106] J. Sorysz: Baza Reactome (youtube)
