DATABASES

Syllabus

Details of the discipline

Educational level	First (bachelor)
Field of knowledge	12 Information technology
Specialty	126 Information systems and technologies
Educational program	Integrated information systems
Discipline status	Normative
Educational form	full-time / part-time / remote
Educational year, semester	2 course, autumn semester
Discipline scope	120 hours (36 hours – Lections, 18 hours – Laboratories, 66 hours – SSW)
Semester control / control measures	Credit / credit work
Schedule	http://rozklad.kpi.ua
Language	Ukrainian
Information about course leader / teachers	Lecturer: Ph.D., Kseniia Ulianytska, k.ulianytska@kpi.ua, mob. +38(050)355-62-15 Laboratories: Ph.D., As. Prof., Oleksandra Boiko, alexbb14112016@gmail.com, mob. +38(097)294-40-83
Course placement	https://campus.kpi.ua

Curriculum of the discipline

Description of the discipline, its purpose, subject of study and learning outcomes

Description of the discipline. During this course, students will get acquainted with the concept of "database", types of databases, and will study in more detail the relational data model. Laboratory classes master the stages of designing relational databases. At the user level, they master the Oracle Database 11g database management system. The course provides quality control of the acquired knowledge in the form of express control and modular tests. The lectures will include practical group classes on mastering the principles of building a relational data model.

Subject of the discipline: basic concepts of relational theory, introduction to relational databases, infologic model, datalogic model, relational SQL database management systems based on Oracle Databases.

Interdisciplinary connections. Discipline Databases is based on disciplines: Operating systems; Programming - 1. Basics of programming; Programming - 2. Data structures and algorithms; Discrete Math.

Purpose of the discipline. Training of highly qualified specialists who have the basic concepts of relational theory, terms of relational databases, modeling of the subject area for building relational databases, normalization, algorithm for building relational databases, physical implementation of designed database schemes on relational database management systems, administration user-level databases with experience, namely Oracle Databases.

Main tasks of the discipline

Knowledge:

the role and place of relational databases in the information technology environment of their use;
relational model for relational databases;
stages of relational database design;
normal forms;
SQL (Structured Query Language) to create a database schema and work with data (requests for sampling, modification, update, deletion).

Skills:

analyze the subject area defined by the framework of a particular project;
build a conceptual data model based on the results of the analysis of the subject area;
transform a conceptual data model into a datological model – physical structure of the database;
on the basis of a datalogic model to carry out physical implementation of a database - to develop scripts and to work out them on the chosen database management system (DBMS);
have the SQL language in its DDL (Data Definition Language) sections and one-table queries of the SELECT construct;
administer at the user level with experience relational database Oracle Server 11g XE.

Prerequisites and postrequisites of the discipline (place in the structural and logical scheme of education according to the relevant educational program)

Prerequisites: be able to use a computer at the administrator level, be able to work with virtual machines (create, configure, modify), basic knowledge of set theory, be able to build Use-Case models for further analysis and predict future application functionality for which, in fact, and any relational database will be designed.

Postrequisites: design and implementation of relational databases on relational DBMS.

After completing the course, students will be able to reproduce the basic stages of creating relational databases, namely to analyze the subject area, build an infological or conceptual model of the subject area, transform the conceptual model into a physical model of the database, apply table normalization and formalize relationships. Write scripts (SQL section DDL) for the physical data model under the relational DBMS Oracle Database 11g/18c. Execute simple SQL queries to the created database.

Content of the discipline

Lectures

Chapter 1. General provisions

Chapter 2. Relational databases. Introduction

Chapter 3. Relational database modeling

Chapter 4. Introduction to SQL.

Chapter 5. Relational DBMS Oracle on the example of the server Oracle Database 11g XE.

Chapter 6. Introduction to the mechanisms of relational databases.

Chapter 7. Many tabular SQL queries.

Laboratory classes

1. Analysis of the subject area.

2. Infological data model.

3. Construction of a datalogical model.

4. Deploy a relational database (Oracle Server 11g XE) on a virtual machine.

5. Physical design of the database on the Oracle database.

6. Physical implementation of the database on the Oracle database.

7. Implementation of complex queries within SQL commands DML and DQL.

Training materials and resources

Basic literature

Date K.J. Introduction to database systems - 8th ed. - M .: Williams, 2005. - 1328 p.
Kuznetsov S.D. Fundamentals of databases. - 2nd ed. - Moscow: Internet University of Information Technology; BINOM. Laboratory of Knowledge, 2007. - 484 p.
Rebecca Rayordan - Fundamentals of Relational Databases, 2001
Garcia-Molina G., Ullman J., Weed J. Database systems. Full course.-M.:Williams, 2003. - 1088 p.

Additional literature

Sanjay Mishra, Alan Buley. Secrets of Oracle SQL. - Symbol Plus. - 2006.
Stern D., Greenwald R., Stakowiak R. Oracle 11g. Fundamentals, 4th edition. - Symbol Plus. - 2009.
Itzik Ben-Gan - Microsoft SQL Server 2008. Basics of T-SQL. - BHV P .: - 2009.
Kite Tom. Oracle for professionals: architecture and programming techniques. 3rd ed .: Per. with English - M .: LLC "ID Williams", 2016. - 960 p.
SQL. Collection of recipes. - Per. with English - Спб: Символ-Плюс, 2009. - 672 с.
Wiescas John - SQL Queries for Mortals, 2003.
Graber Martin - SQL for ordinary mortals, 2014.Educational content

Educational content

Methods of mastering the discipline (educational component)

Lectures

№	The title of the lecture topic and a list of key issues (list of teaching aids, references to literature and tasks on SSW)
1	Topic 1.1. The structure and content of the course. RES. Topic 1.2. General concepts of discipline. Database types. Lecture 1. Introduction. General concepts. Database types. The structure of the course Databases, RES. General concepts of the database area. Database as an information model of the subject area of design. Rule of three is NOT. General information about non-relational databases. Database types. Literature: [1, Chapter 1.1.4], [3, Chapter 1] Tasks on SSW. Non-relational databases, BlockChain - general concepts, types, models.
2	Topic 1.3. Relational and non-relational databases. The CAP theorem. Topic 2.1. ACID principles. Lecture 2. Relational and non-relational databases. The CAP theorem. ACID principles. Definition of relational and non-relational databases, differences, models, principles. The CAP theorem is a combination of the capabilities of relational and nonrelational databases. Principles of relational databases with detailed explanation. Literature: [1, Chapter 1.1.6], [3, Chapter 1] Tasks on SSW. CAP theorem - a detailed consideration, comparative analysis of the sides of the «triangle».
3	Topic 2.2. Basic terms of relational databases. Lecture 3. Terms of relational database theory. Relational data model. Existing terms of relational databases and relational database management systems. Schematic representation of terms. Three-level scheme of metadata presentation. Literature: [1, Chapter 1.1.2; Chapter 3], [3, Chapter 1] Tasks on SSW. Relational data model developed by E. Codd - definitions, principles.
4	Topic 3.1. The list of stages of a full cycle of construction of relational databases (RDB). Lecture 4. Relational database model. Principles of construction. Full RDB design cycle. Key points of RDB construction. A generalized and complete list of stages of relational database design. Literature: [1, Chapter 5], [3, Chapter 6]
5	Topic 3.2. The concept of infological database design. Lecture 5. Example of designing an infological model of the Publishing House database. Subject area analysis and identification of certain elements for infological design. A list of existing relationships between relationships. Construction of ER-diagram according to Chen's notation. Literature: [1, Chapter 14], [3, Chapter 6; Chapter 9], [6, Chapter 2] Tasks for SSW. Research of the full list of elements of model of infological designing. Execution of ER-diagram of another notation (optional, IDEF0, Yordana, etc.).
6	Topic 3.3. The concept of data design of databases Lecture 6. Example of designing a databack model of the database "Publishing House". Transforming the existing infological data model into a datalogical with the formalization of all relationships between entities. The construction of the model is carried out by a generalized set of elements of data models. Literature: [1, Chapter 5; Chapter 6], [3, Chapter 10], [6, Chapter 3; Chapter 4] Tasks for SSW. Research of other ways and means of construction of logical models of the subject area according to the conceptual diagram.
7	Topic 3.4. Normalization and introduction to de-normalization. Lecture 7. Normalization of the data model. Denormalization - application, consequences. Definition of normalization terms. Eight normal forms (3 required). Cases of de-normalization. Consequences of de-normalization. Literature: [1, Chapter 12], [2, L.6], [3, Chapter 9] Tasks for SSW. Study of normal forms starting with 4NF.
8	Topic 4.1. Twelve rules of Codd. Topic 4.2. SQL language construction - the main types of queries of the corresponding languages DDL, DML, DCL, TCL. Lecture 8. Background of SQL language. E. Codd's rules. SQL language construction - basic types of queries. The history of a single structured language, which is currently used by all relational database management systems. Codd's rules - definition, justification. Anatomy of relational databases. Recall the Codd rules, normalization / de-normalization. Classification of relationships between relations. Classification of SQL commands. Literature: [1, Chapter 1; Chapter 4], [2, Chapter 4; Chapter 5] Tasks for SSW. Elaboration of all twelve Codd rules. Examples of application. Preparation for control work on all previous material.
9	Lecture 9. Modular control work All previous material is submitted for control work, which includes the basic concepts of relational database theory and the construction of the SQL language in general. Tasks include theoretical and practical parts, as well as additional tasks, in case of timely completion of the main issues. Tasks for SSW. Repeat the material of 1-8 lectures.
10	Topic 5.1. Generalized Oracle server architecture Lecture 10. Relational DBMS. Oracle Database database server architecture. History of origin and development of relational DBMS Oracle. Oracle technology stacks. The general structure of the Oracle database server. General Oracle database architecture. Literature: [7, Chapter 2] Tasks for SSW. Download the appropriate version of the Oracle server. Install software and an empty database for further study and use.
11	Topic 5.2. Oracle server memory structures Lecture 11. Oracle database structures. Generalized memory structure. Schematic representation of Oracle database structures. Logical and physical structures of memory. Literature: [7, Chapter 4] Tasks for SSW. Independently master the materials for the management of database storage structures - clarification of the physical structure.
12	Topic 5.3. Basic concepts in Oracle for working and creating databases Lecture 12. Oracle instance management. Highlights. The concept of an Oracle instance. Generalized Oracle instance management (memory structures and processes). Literature: [7, Chapter 2; 3] Tasks for SSW. Simplified maintenance of initialization parameters. Start and stop an Oracle database instance.
13	Topic 6.1. The concept of integrity Topic 6.2. Indexes. Basic concepts, introduction. Lecture 13. Integrity constraints in relational databases. Indexing in relational databases. Types of indices List of integrity constraints for relational databases. Application, example. Classification of relationships between tables. The concept and application of indexing in relational databases. Classification of indexes. Literature: [1, Chapter 9], [2, L.9], [3, Chapter 10], [7, Chapter 11] Tasks for SSW. Practice of theoretical material on the Oracle server, for which to master the formalization of connections in the specifics of the Oracle server, as well as the types and applications of Oracle server indexes.
14	Topic 6.3. SQL queries in the DML section Lecture 14. Construction of SQL queries. Defining a metadata dictionary. Three-level scheme of metadata representation in relational database management systems. The concept of data about data. Queries on one table in the SELECT construction. Literature: [1, Chapter 4], [2, L.13] Tasks for SSW. Testing of theoretical material on the Oracle server, definition of the corresponding representations where Oracle metadata is displayed. Tabular space where metadata is stored.
15	Lecture 15. Modular control work All previous material, which includes work in the Oracle 11g relational database at the user level with experience, is submitted for control work. Theoretical questions on integrity, indexes and SQL queries in DDL, DML sections. Tasks include a theoretical part, a test question and a practical part where you will need to write one of the SQL queries from DDL and/or DML sections. Tasks for SSW. Repeat the material of 10-14 lectures.
16	Topic 7.1. Working with tables in Oracle Lecture 16. Constructions of SQL language. DDL premise. List of known DDL subtext commands with examples and practical training on the Oracle Database 11g/18c XE server. Literature: [7, Chapter 10] Tasks for SSW. Locally managed table spaces and dictionary-managed table spaces. Definition, application.
17	Topic 7.2. DDL, DML language commands Lecture 17. SQL language constructs. DML premise. Multi-table queries. The list of known commands of the DML subtext with examples and practical training on the Oracle Database 11g/18c XE server. Algorithm for forming complex multi-table queries. Example of testing on an Oracle server. Literature: [7, Chapter 10], [8] Tasks for SSW. Data schemes and accounts on the Oracle server. Working with security settings.
18	Topic 7.3. The concept of transactions Lecture 18. Definition of transactions in RBD. Examples. The concept of transactions, examples of transactions on the server Oracle Database 11g/18s XE. The concept of COMMIT and ROLLBACK. Literature: [7, Chapter 8] Tasks for VTS. Independently train and master the concept of blocking transactions, using the SAVEPOINT construction.

**
**

Laboratory classes

№	Name of laboratory work	Number of hours
1	Laboratory work 1. Analysis of the subject area. It is necessary to analyze, find the appropriate material for a given topic of the subject area. The result of the work should be a description of the subject area at least on page A4 and the identification of relevant entities in the form of tabular classification by their types. Literature: [3, Chapter 6]	4
2	Laboratory work 2. Infological model of the database. According to the data obtained from laboratory work №1, it is necessary to identify all attributes of entities and relationships between entities. Summarize everything in classified tables according to attributes and relationships. The result of this laboratory is an infological data model in the form of an ER-diagram in furnace notation (Chen, Jordan, IDEF0, (). Literature: [3, Chapter 6]	2
3	Laboratory work 3. Database model of the database. Convert the resulting infological model of a relational database into a datalog model by formalizing all types of connections. Carry out normalization and apply certain integrity constraints. Literature: [3, Chapter 6]	2
4	Laboratory work 4. Deployment of relational database (Oracle Server 11g XE) on a virtual machine. Perform the installation and configuration of the initialization parameters. Master the administrative skills of working with a super user password. Know how the software is installed - where the relevant system files and variables are located. Literature: [7]	2
5	Laboratory work 5. Physical design of the database on Oracle database. Based on the developed data model in laboratory work №3 to develop appropriate scripts in SQL (DDL). Literature: [7], [8]	2
6	Laboratory work 6. Physical implementation of the database on Oracle database. Create appropriate tablespaces (permanent and temporary) and data schema on the Oracle server. Add relevant roles and grants. Accompany everything by saving the appropriate scripts in SQL. Expand the data schema with the appropriate tables, relationships, and integrity constraints from the scripts obtained in the previous work. Literature: [7]	4
7	Laboratory work 7. Implementation of complex queries within SQL commands of DML and DQL subplots. Add test values to all tables of the data scheme obtained in the previous laboratory work. Record all actions with the appropriate SQL scripts. Work out complex multi-table queries using 3 or more relationships. Literature: [8]	2

Student’s self work

№	The name of the topic submitted for self-study	Number of SSW hours
1	Non-relational databases, BlockChain - general concepts, types, models.	2
2	CAP theorem - a detailed consideration, comparative analysis of the sides of the «triangle».	2
3	Relational data model developed by E. Codd - definitions, principles.	4
4	Research of the full list of elements of model of infological designing. Execution of ER-diagram of another notation (optional, IDEF0, Yordana, etc.).	4
5	Research of other ways and means of construction of logical models of the subject area according to the conceptual diagram.	4
6	Study of normal forms starting with 4NF.	2
7	Elaboration of all twelve Codd rules. Examples of application. Preparation for control work on all previous material.	4
8	Download the appropriate version of the Oracle server. Install software and an empty database for further study and use.	4
9	Independently master the materials for the management of database storage structures - clarification of the physical structure.	4
10	Simplified maintenance of initialization parameters. Start and stop an Oracle database instance.	4
11	Practice of theoretical material on the Oracle server, for which to master the formalization of connections in the specifics of the Oracle server, as well as the types and applications of Oracle server indexes.	8
12	Testing of theoretical material on the Oracle server, definition of the corresponding representations where Oracle metadata is displayed. Tabular space where metadata is stored.	4
13	Locally managed table spaces and dictionary-managed table spaces. Definition, application.	4
14	Data schemes and accounts on the Oracle server. Working with security settings.	4
15	Independently train and master the concept of blocking transactions, using the SAVEPOINT construction.	2
16	Preparation for the test on all material of the module.	10

Policy and control

Policy of academic discipline (educational component)

The system of requirements for students:

attending lectures and laboratory classes is a mandatory component of studying the material;
at the lecture the teacher uses his own presentation material; works out the practical part on a virtual machine with the appropriate RDBMS installed; uses Google Drive to teach the material of the current lecture, additional resources, laboratory work, etc.; the teacher opens access to a certain directory of the Google disk for downloading electronic laboratory reports and responses to MCR;
it is forbidden to distract the teacher from teaching the material, all questions, clarifications, etc. at the lecture. students ask at the end of the lecture in the allotted time;
laboratory works are defended in two stages - the first stage: students perform tasks for admission to the defense of laboratory work; the second stage - protection of laboratory work. Points for laboratory work are taken into account only in the presence of an electronic report;
modular tests are written in lectures without the use of aids (mobile phones, tablets, etc.); the result is sent in a file to the appropriate directory of Google Drive;
incentive points are awarded for: active participation in lectures; participation in faculty and institute Olympiads in academic disciplines, participation in competitions of works, preparation of reviews of scientific works; presentations on one of the topics of the SSW discipline, etc. Number of encouraged points no more than 10;
penalty points are set for: late delivery of laboratory work. Number of penalty points for no more than 10.

Types of control and rating evaluation system for assessing learning outcomes (RES)

The student's rating in the discipline consists of points that he receives for:

performance of control works (5 express control tests in laboratory classes);
performance and protection of 7 laboratory works;
performance of 2 modular control works (MCW);
incentive and penalty points.

Rating points system and evaluation criteria

Express control works:

«Excellent» - complete answer (not less than 90% of the required information) – 2.5 points;

«Good» - a fairly complete answer (at least 75% of the required information) or a complete answer with minor flaws – 1,5-2 points;

«Satisfactory» - incomplete answer (not less than 60% of the required information) and minor errors - 1 points;

«Unsatisfactory» - the answer does not meet the requirements for «satisfactory» - 0 points.

Laboratory works:

«Excellent», a complete answer to the question during the defense (not less than 90% of the required information) and a properly executed electronic protocol for laboratory work - 5 points;

«Good», a sufficiently complete answer to the question during the defense (not less than 75% of the required information) and a properly executed electronic protocol for laboratory work - 4/3 points;

«Satisfactory», incomplete answer to the question during the defense (not less than 60% of the required information), minor errors and properly executed electronic protocol for laboratory work - 2/1 points;

«Unsatisfactory», unsatisfactory answer and/or not properly executed electronic protocol for laboratory work - 0 points.

For each lesson late with the submission of laboratory work to defend against the deadline, the score is reduced by 1 point.

Modular tests:

«Excellent», complete answer (not less than 90% of the required information) - 5 points;

«Good», a sufficiently complete answer (not less than 75% of the required information), or a complete answer with minor errors - 3-4 points;

«Satisfactory», incomplete answer (but not less than 60% of the required information) and minor errors - 1-2 points;

«Unsatisfactory», unsatisfactory answer (incorrect solution of the problem), requires mandatory rewriting at the end of the semester - 0 points.

Incentive points

- for performing creative work on the credit module (for example, participation in faculty and institute competitions in academic disciplines, participation in competitions, preparation of reviews of scientific papers, etc.); for active work on lectures (questions, additions, remarks on the topic of the lecture, when the lecturer asks students to ask their questions) 1-2 points, but in the amount of not more than 10;

- SSW presentations - from 1 to 5 points.

Intersessional certification

According to the results of educational work for the first 7 weeks the maximum possible number of points – 22,5 points (2 laboratory, MKR-1, 1 express control). At the first certification (8th week) the student receives "enrolled" if his current rating is not less than 8 points.

According to the results of 13 weeks of training, the maximum possible number of points is 45 points (4 laboratory, MKR-2, 2 express control). At the second attestation (14th week) the student receives "credited" if his current rating is not less than 16 points.

The maximum amount of weight points of control measures during the semester is:

RD = 7*r_lab+2*r_mcr+2*r_ew + (r_i - r_p) = 7*5+2*5+2*2,5+ (r_i - r_p) = 50 + (r_i - r_p),

де r_lab – points for laboratory work (0…5);

r_mcr – points for MCW (0…5);

r_ew – points for express control work (0…2,5);

r_i – incentive points for active participation in lectures, presentations, participation in competitions, competitions, research papers on the subject of the discipline (0…10);

r_p – penalty points.

Examination:

The condition for admission to the exam is enrollment in all laboratory work, writing both modular tests and a starting rating of at least 17 points.

At the exam, students perform a written test. Each ticket contains two theoretical questions (tasks) and two practical tasks and one test. The list of theoretical questions is given in appendices 1 and 2. Each question (task) is evaluated in 10 points.

Question evaluation system:

«Excellent», complete answer (not less than 90% of the required information) - 9-10 points;

«Good», a fairly complete answer (at least 75% of the required information, or minor inaccuracies) - 7-8 points;

«Satisfactory», incomplete answer (not less than 60% of the required information and some errors) - 5-6 points;

«Unsatisfactory», unsatisfactory answer - 0-4 points.

The sum of starting points and points for the examination test is transferred to the examination score according to the table:

Table 1. Translation of rating points to grades on a university scale

Points	Mark
100-95	Excellent
94-85	Very good
84-75	Good
74-65	Satisfactory
64-60	Enough
Less, then 60	Unsatisfactory
There are no credited labs or modular control work is not included	Not allowed

Additional information on the discipline (educational component)

the list of theoretical questions submitted for semester control is given in Appendix 1;
At the beginning of the semester, the teacher analyzes the existing courses on the subject of the discipline and offers students to take the appropriate free courses. After the student receives a certificate of distance or online courses on the subject, the teacher closes the relevant part of the course (laboratory or lectures) by prior arrangement with the group.

Syllabus:

Written by assoc. prof., Ph.D., Kseniia Ulianytska

Approved by department ACTS (protocol № 1 from 27.08.2020 р.)

Agreed with Methodical commission of the faculty [1] (protocol № 1 from 02.09.2020 р.)

Appendix 1

List of theoretical questions for credit part 1

1. Information and data. Definitions, examples.

2. Data, data models. Types of data models considered in the course.

3. Subject area. Definition, example. Infological model.

4. Infological model of the subject area. Options for building a model. One option is described with an example.

5. Datological model. Definitions, construction options. One option is described with an example.

6. Database. Definition, description, content. Example database.

7. Database. Rule of three "NO". Examples.

8. Database. Definition, description, content. Rule of «NO redundancy».

9. Database. Definition, description, content. Rule of «CONSISTENCY».

10. Database. Definition, description, content. Rule «Independence from applications». Means of achieving this independence.

11. Metadata - definition. Three-level scheme of metadata presentation.

12. General scheme of database formation. Infological design - design sequence, design results.

13. General scheme of database formation. Datological design - design sequence, design results.

14. General scheme of database formation. Physical design - design sequence, design results.

15. Physical design of the database. Physical realization. Example.

16. Infological design. Conceptual modeling - definition, model, description, example.

17. Infological design. Models of local representations.

18. Infological design. Integrity constraints in infological design.

19. Data design. Methods of transition from the model of the subject area to the scheme of the database.

20. Data design. Creating schemes of relations. The concept of "relationship" and "connection".

21. Detailed design. Normalization of relations. Three normal forms - definitions, examples.

22. Normalization. Terms of normalization (attribute, attribute domain, normal form). 1NF - definition, example.

23. Normalization. Terms of normalization (tuple, cardinality, functional dependence). 2NF - definition, example.

24. Normalization. Terms of normalization (relationship, scheme of relations). 3NF - definition, example.

25. Normalization. Terms of normalization (anomaly - to list, determine). 4NF - definition, example.

26. Normalization. Terms of normalization (relations, connections - classification of connections). BCNF - definition, example.

27. Database management systems. Definition, schematic representation of a DBMS.

28. Types of databases. Relational database - the principle of construction.

29. Types of databases. Non-relational database - the principle of construction.

30. Types of databases. BlockChain - general information, example.

31. The SAR theorem. Definition, explanation of the expression "only 2". Belonging of different types of databases to the edges of the "triangle" of the theorem.

32. Non-relational data models. Types, definitions, examples.

33. Relational DBMS - the principle of construction, definition. ACID - definitions, examples.

34. Non-relational DBMS - the principle of construction, definition. BASE - definitions, examples.

35. Terms of relational database. Definition, scheme.

36. Relational data model. Relational database. (Definition, connection of terms, terms of relational theory).

37. Complete cycle of relational database development. Definition of stages.

38. Complete cycle of relational database development. List of stages. Analysis of the subject area of design. Example.

39. Complete cycle of relational database development. List of stages. Defining project requirements. Description of requirements.

40. Complete cycle of relational database development. List of stages. Database infological design (list of stages, classification of connections).

41. Complete cycle of relational database development. List of stages. Infological design of the database (list of stages, classification of entities).

42. Complete cycle of relational database development. List of stages. Infological design of the database (list of stages, classification of attributes).

43. Complete cycle of relational database development. List of stages. Infological design of the database (comments on the design results).

44. Chen's notation. Definition, application, description of symbols.

45. Complete cycle of relational database development. List of stages. Database database design (algorithm for converting an infological model into a data model).

46. Database design of the database. Classification of relationship relations. Transform logical connections into physical connections.

47. Database design of the database. Defining integrity constraints. Examples of relevant links.

48. 1NF. Definition, example of reduction to 1NF, methods of reduction to 1NF.

49. 2NF. Definition, example of reduction to 2NF, methods of reduction to 2NF.

50. 3NF. Definition, example of reduction to 3NF, methods of reduction to 3NF.

51. Denormalization. Definition, application, limitations.

52. Codd's rules. Principle, definition, historical information.

53. Modern interpretation of the relational data model.

54. Classifications of relational data model relationships. Recursive communication is an example of logical and physical implementation.

55. SQL. Definitions, terms of relational theory (connections, attributes, keys).

56. SQL. Definition, classification of SQL commands (list, application).

57. The main stages of database design.

58. What tasks are solved at the stage of infological design?

59. What problems are solved at the stage of logical design?

60. What tasks are solved at the stage of physical design?

61. Define the concept of «entity».

62. Distinguish the concept of "type" of essence from the "element" of essence.

63. Define the terms "attribute" and "attribute domain".

64. Are the concepts of "essence" and "attribute" absolute?

65. Define unconditional communication in a relational model. Example.

66. Define a conditional relationship in a relational model. Example.

67. Define a bi-conditional relationship in a relational model. Example.

68. Define a recursive relationship in a relational model. A complete example of the possibilities of this connection.

69. Formalize a 1: 1 relationship.

70. Formalize the connection 1: M.

71. Formalize the connection M: N.

72. Define the core entity.

73. Define the associative entity.

74. Define the characteristic entity.

75. Define the entity of the designation.

76. Define the primary key of the relationship.

77. Define the secondary key of the relationship.

78. Give the definition of the functional dependence (full and partial) of a non-key attribute on the composite primary key.

79. Define the transitive dependence.

Appendix 2

List of theoretical questions for credit part 2

1. Give the definition and general structure of the Oracle database. What does an Oracle database server (two components) consist of?

2. What options for data storage and processing are provided in the Oracle11g database? Which option is chosen for this course?

3. General logical architecture of Oracle11g database. Give a schematic picture and explain the connections.

4. OLTP - definition, purpose, application. What type of block is used > in such databases?

5. OLAP - definition, purpose, application. What type of block is used in such databases?

6. Define a block in the general Oracle11g database architecture. Application of different types of blocks.

7. List the base files of the Oracle11g database.

8. Define and compile an instance of the Oracle11g database.

9. Define the term "session" in terms of RDBMS Oracle. List the components of the session.

10. List and define the client part to work with the Oracle database.

11. List and define the server part to work with the Oracle database.

12. Oracle RDBMS structures. Schematic representation, description.

13. Logical storage structures in the Oracle database. Schematic representation, description.

14. Physical storage structures in the Oracle database. Schematic representation, description.

15. Oracle RDBMS processes. Schematic representation, description.

16. Oracle instance management. Schematic representation, description.

17. Define one of the base files of the Oracle database - Parameter File. What are parameter files and where are they originally located? Which file does the Oracle server work with directly?

18. Define one of the base files of the RDBMS Oracle - Control File. How many control files are there when you first install the Oracle database? What is the maximum number? Where, initially, are they located? File storage rules.

19. Define one of the base files of the Oracle database - Data File. What is the initial composition of the date files? The maximum number? Where, initially, are they located?

20. Define one of the base files of the Oracle database - Password File. Feature of application. Which utility is responsible for working with this file? Where, initially, is the Password File?

21. Define one of the base files of the Oracle database - Redo Log. > Where, initially, are they located? File storage rules.

22. Describe the operating system-level services created after installing the database server and the Oracle database directly. Which services start by default, which do not.

23. Describe the oracle.exe process at the Windows operating system level. What are the features of this process? How, just by watching this process, can you tell if an Oracle database is running?

24. What environment variables must be set to work properly with Oracle in the Linux family of operating systems? List. At what stage are the initial values of these variables set? Is it possible to change these values and how?

25. Stages of launching the Oracle database. Define the NOMOUNT stage - what happens at this stage, which base files are opened, which base files can still be modified?

26. Stages of launching the Oracle database. Define the MOUNT stage - what happens at this stage, which base files are opened, which base files can still be modified?

27. Stages of launching the Oracle database. Define the OPEN stage - what is happening at this stage, which base files are opened, which base files can still be changed?

28. Which parameter is responsible for the amount of RAM provided to an Oracle instance? How many such parameters? Where to view the values of these parameters?

29. MEMORY_TARGET and MEMORY_MAX_TARGET. Give a definition. Where to view the current value of these parameters? How do these files depend on each other?

30. PGA. Define, decipher. What processes are associated with this area of memory and where do these processes occur - on the client or server side?

31. SGA. Give a schematic definition, decipher. What processes are associated with this area of memory and where do these processes occur - on the client or server side?

32. SGA data structures. Schematic representation, description of constituent structures.

33. Background processes of the Oracle server side. Schematic representation. SMON - definition and application of this process.

34. Background processes of the Oracle server side. Schematic representation. PMON - definition and application of this process.

35. Background processes of the Oracle server side. Schematic representation. DBWn - definition and application of this process.

36. Background processes of the Oracle server side. Schematic representation. SKRT - definition and application of this process.

37. Background processes of the Oracle server side. Schematic representation. LGWR - definition and application of this process.

38. Oracle table spaces. Definition. The place of table spaces on the logical and physical structure of the Oracle database.

39. Oracle table spaces. Definition. Required table spaces. How to view in the database all available table spaces with their parameters?

40. What state of the database instance is responsible for opening control files? What happens to control files when they are opened? What view can I use to view the status of a database instance? Give an example of a script.

41. Give the scheme of interaction SEGMENTS, EXTENTS, BLOCKS. Describe the relationships between these elements.

42. Give the scheme of interaction DATAFILES, TABLESPACES, SEGMENTS. Describe the relationships between these elements.

43. Tabular spaces. Definitions, types of table spaces. Application of UNDO TABLESPACE. What parameters are responsible for working with rollback table spaces?

44. Users and data schemas in Oracle. The relationship of these elements. What view can I use to view an existing list of database users? Give an example of a script.

45. Roles and privileges in the Oracle database. Schematically show the relationship of these elements. Give an example script to grant and remove privileges for a role.

46. Oracle Server Administration. Privileges - definitions, types, uses. System Privileges - Hover the script to display all system privileges applied to the server.

47. Oracle Server Administration. Privileges - definitions, types, uses. Describe additional parameters when assigning privileges -

WITH ADMIN OPTION; WITH HIERARCHY OPTION; WITH GRANT OPTION

What permissions do these options open and what privileges do they apply to?

48. Oracle server administration. User profile. Definitions, applications, default profiles. How to check which profile the current user has? Enter the script.

49. Data schemes of the Oracle server. Which ones are installed by default with the database? Which scheme stores all the necessary administrative information for the server? Comment on view prefixes:

DBA_ USER_ ALL_

Access levels for users with these prefixes? How can I change the access (for example, to open access to views with the prefix ALL_ for the average user)?

50. Data tables in the Oracle server. Give the logical structure of memory and explain where the tables are stored? How are large tables stored?

51. LISTENER. Definition, application. What are the features of creating LISTENER for Linux and Windows? Which utility is responsible for LISTENER? How to check LISTENER status?

52. LISTENER. Definition, application. In which operating system (Linux or Windows) is this service created and configured by default when creating a database? Which utility is responsible for LISTENER? How do I restart LISTENER?

[1] Methodical council of the university - for general university disciplines.