Database Add-ons

SQL knowledge.

According to the objectives of the course, its target public, and achievement goals, certain methodologies and strategies must be used in order to allow the right comprehension of the taught content.

As such, it is prioritized the use of several techniques and methodologies together to maximize the apprehension of contents. Thus, we have:

1. Expository method: oral presentation of theoretical content using multimedia presentations, as well as the use of specific programs and resources allocated to the planning and implementing DBMS;

2. Interrogative method: through individual questions or questions directed to the group during the classes, allowing immediate feedback on the content covered;

3. Active method: by using several techniques, such as conducting discussions/debates on the themes developed, case studies regarding important facts in the area of ​​the matter, problem-solving and development of individual works;

4. Demonstrative method: useful to demonstrate different aspects of using DBMS.          

– Identify internal concepts regarding persistence and data organization within the DBMS.

– Identify implications from persistence in the DBMS data.

– Recognize the aspects that influence the performance in relational DBMS.

– Implement approaches to optimize DBMS.

– Project administration procedures in relational DBMS.

– Implement procedures, functions, cursors, triggers and packages in PL-SQL.

1. Architecture and file structure:

                a) Introduction;

                b) ANSI-SPARC;

                c) DBMS file types.

2. Metadata and catalogs:

                a) Metadata types;

                b) Catalog definition and use cases.

3. Indexes:

                a) Index definition and types;

                b) Use cases.

4. Query processing and optimization:

                a) Query rewriting;

                b) Analysis of execution plans.

5. Monitoring and tuning:

                a) Approaches to monitoring and tuning an Oracle DB.

6. Transactions and concurrency:

                a) Concepts of transactions and concurrency;

                b) Integrity constraints.

7. Replication, backup & restore:

                a) Base concepts;

                b) Complete and partial restore.

8. Security:

                a) Physical and logical security;

                b) Data encryption.

10. SQL (Oracle):

                a) Concepts review;

                b) Using SQL in real-world problems.

11. PL/SQL:

                a) Introduction;

                b) Procedures and functions;

                c) Cursors;

                d) Packages;

                e) Triggers.

NAO

Connolly, M., & Begg, E. (2014). Database systems: A practical approach to design, implementation and Management (6th ed.). Pearson.

Elmasri, R., & Navathe, S. (2015). Fundamentals of database systems (7th ed.). Addison Wesley.

Garcia-Molina, H. (2014). Database systems: The complete book. Prentice Hall.

Gouveia, F. (2021). Bases de dados: Fundamentos e aplicações (2nd ed.). FCA.

Ramakrishnan, R., & Gehrke, J. (2003). Database management systems. McGraw-Hill.

Rodrigues, A. (2005). Oracle 10g e 9i : Fundamentos para profissionais. FCA.

Silberschatz, A., Korth, H., & Sudarshan, S. (2019). Database system concepts (7th ed.). McGraw Hill.