Topic 1 Introduction Database systems Basic concepts and

Topic 1. Introduction. Database systems. Basic concepts and architecture. SQL and relational databases HNEU, Department of Information Systems, Course Database, V. V. Fedko

Contents 1. The goals and tasks of the discipline, its place in the educational process 2. Database and files 3. The Database Management System (DBMS) 4. Roles in the Database Environment 5. History of Database Management Systems 6. SQL and relational databases HNEU, Department of Information Systems, Course Database, V. V. Fedko 2

Test questions En: Ru: 1. Compare file and databased approaches. 2. What are the components of a database system? 3. What type of database activity would you like to do? Why? 1. Сравните подходы на основе файлов и баз данных. 2. Каковы компоненты системы базы данных? 3. Каким видом деятельности с базами данных вы хотели бы заняться? Почему? Send your answers at email fvv. stud@gmail. com HNEU, Department of Information Systems, Course Database, V. V. Fedko 3

1. The goals and tasks of the discipline, its place in the educational process Goals: • • use of the SQL language to work with databases; building effective data models based on a domain study; theoretical and engineering training in the field of database design and database use; familiarity with No. SQL databases. HNEU, Department of Information Systems, Course Database, V. V. Fedko 4

Tasks: 1. 2. 3. 4. Query languages. Data modeling. Database designing. Performing operations with a No. SQL database. HNEU, Department of Information Systems, Course Database, V. V. Fedko 5

Place in the educational process: Database technology Introduction to Computer Science Mathematical analysis Discrete Math Programming Algorithms and data structures The Basics of Object Oriented Programming Database System analysis and design of information systems Web technologies and web design Artificial intelligence systems Basics of Information Systems Protection of information BI systems Predictive data analysis Enterprise Management Information Systems … HNEU, Department of Information Systems, Course Database, V. V. Fedko 6

Order of study: Specialty 121 Tariffs Hours/ points Total Lectures Labs Self-study Exam hours 210 24 48 138 points 60 Tariff, Quantity, Sum, points pcs points lecture 1. 5 12 18 defence of labs lab 5. 0 7 35 40 test Total Work Class lab HNEU, Department of Information Systems, Course Database, V. V. Fedko 3. 5 2 7 60 7

Additional Main Recommended Textbooks: 1. Database Systems: Design, Implementation, and Management. 13 th Edition / Carlos Coronel, Steven Morris. – Cengage Learning, Inc. , 2017. – 837 p. 2. Database Systems: A Practical Approach to Design, Implementation, and Management. Sixth Edition / Thomas Connolly, Carolyn Begg. – Pearson, 2015. – 1442 p. 3. Fundamentals of Database Systems. Seventh edition / Ramez Elmasri, Shamkant B. Navathe. – Pearson, 2016. – 1273 p. 4. T-SQL fundamentals. Third edition / Itzik Ben-Gan. – Microsoft Press, 2016. – 1361 p. 5. Hands-on database: An introduction to database design and development / Steve Conger. – Pearson Education, Inc. , 2012. – 214 p. 6. The New Relational Database Dictionary / C. J. Date. – O’Reilly Media, Inc. , 2016. – 452 p. Web 7. SQLServer. Central. [Electronic resource]. – Acces mode : http: //www. sqlservercentral. com/. 8. MSSQLTips. [Electronic resource]. – Acces mode : https: //www. mssqltips. com/. 9. Oracle Database. [Electronic resource]. – Acces mode : https: //www. oracle. com/database/index. html. 10. Mongo. DB. [Electronic resource]. – Acces mode : https: //www. mongodb. com/. HNEU, Department of Information Systems, Course Database, V. V. Fedko 8

2. Basic concepts of databases The basis of information technology is data that must be organized into databases in order to adequately reflect the changing real world. Product data; Data on the status of accounts; Patient data; Student data … Definitions: Database - A shared collection of logically related data and its description, designed to meet the information needs of an organization (T. Connolly). Database - A collection of persistent data that is used by the application system of some given enterprise (C. J. Date). Database - A large amount of information stored in a computer system in such a way that it can be easily looked at or changed (Cambridge Dictionary). HNEU, Department of Information Systems, Course Database, V. V. Fedko 9

Features: 1. The database is stored and processed in the computing system. 2. The data in the database is logically structured in order to ensure the possibility of their effective search and processing in the computer system. Structuredness implies an explicit choice of constituent parts (elements), the relationship between them, as well as the typing of elements. 3. A database includes a schema, or metadata, describing the logical structure of the database in a formal form. The data management system uses data definitions in the schema to provide access and control access to the data in the database. HNEU, Department of Information Systems, Course Database, V. V. Fedko 10

Subject area: The subject area is a part of the real world, data about which are reflected in the database. The data model is our knowledge about subject area. Database Design - Data Modeling SA DB When designing a database, 3 levels of information are created: 1. conceptual 2. logical 3. physical HNEU, Department of Information Systems, Course Database, V. V. Fedko 11

Limitations of the File-Based Approach: 1. 2. 3. 4. 5. Separation and isolation of data Duplication of data Program-data dependence Incompatible file formats Fixed queries/proliferation of application programs Due to factors: 1. The definition of the data is embedded in the application programs, rather than being stored separately and independently. 2. There is no control over the access and manipulation of data beyond that imposed by the application programs. HNEU, Department of Information Systems, Course Database, V. V. Fedko 12

The main characteristics of the database approach: 1. 2. 3. 4. Self-describing nature of a database (metadata) Insulation between programs and data, and data abstraction Support of multiple views of the data Sharing of data and multiuser transaction processing HNEU, Department of Information Systems, Course Database, V. V. Fedko 13

3. The Database Management System (DBMS) Definition: DBMS - A software system that enables users to define, create, maintain, and control access to the database. DBMS assignment: The DBMS is the software that interacts with the users’ application programs and the database. A DBMS provides the following facilities: • allows users to define the database (DDL) • allows users to insert, update, delete, and retrieve data from the database (DML) • provides controlled access to the database (password, CCL, integrity, backup/restore) HNEU, Department of Information Systems, Course Database, V. V. Fedko 14

Database system: HNEU, Department of Information Systems, Course Database, V. V. Fedko 15

Advantages and Disadvantages of DBMSs Advantages: 1. 2. 3. 4. Control of data redundancy Economy of scale Data consistency Improved data accessibility and responsiveness 5. Sharing of data 6. Improved maintenance through data independence 7. Improved security 8. Increased concurrency 9. Enforcement of standards 10. Improved backup and recovery services HNEU, Department of Information Systems, Course Database, V. V. Fedko 16

Disadvantages: When Not to Use a DBMS: 1. 2. 3. 4. 5. 6. 7. 1. Simple, well-defined database applications that are not expected to change at all 2. Stringent, real-time requirements for some application programs that may not be met because of DBMS overhead 3. Embedded systems with limited storage capacity, where a general-purpose DBMS would not fit 4. No multiple-user access to data Complexity Size Cost of DBMSs Additional hardware costs Cost of conversion Performance Greater impact of a failure HNEU, Department of Information Systems, Course Database, V. V. Fedko 17

4. Roles in the Database System Data Administrator Database Designer Application Developer End-User Data Scientist Data Engineer Data. Ops Engineer HNEU, Department of Information Systems, Course Database, V. V. Fedko 18

5. History of Database Management Systems Timeline: • 1955 - appeared programmable equipment for processing records. The software supports a file-based record processing model. Punch cards used to store data. • 1960 s - the term database. Navigation databases (network and hierarchical) • 1970 s - relational databases. SQL language • 1980 s - desktop • 1990 s - object-oriented databases, ORM • 2000 s - No. SQL and New. SQL • 2010 s – clouds. Big Data HNEU, Department of Information Systems, Course Database, V. V. Fedko 19

6. SQL and relational databases Definition: • A relational database is a set of interrelated tables from which data can be accessed or reassembled in various ways without the need to reorganize database tables. HNEU, Department of Information Systems, Course Database, V. V. Fedko 20

Table, Relation Primary key PRODUCTS PRODUCT_ID NAME PRICE PURCHASE_PRICE 1 Bread "Ukrainian" 13. 50 12. 00 2 Baton "Milk" 12. 80 12. 50 3 Bun with poppy seeds 10 9 Rows, Records Columns, Fields HNEU, Department of Information Systems, Course Database, V. V. Fedko 21

PRODUCTS Parent PRODUCT_ID NAME PRICE PURCHASE_PRICE 1 Bread "Ukrainian" 13. 50 12. 00 2 Baton "Milk" 12. 80 12. 50 3 Bun with poppy seeds 10 9 Child 1 SALES N SALE_ID SALE_DATE PRODUCT_ID MANUFACTURER_ID QUANTITY 1 01. 09. 2019 1 1 200 Relationship 2 01. 09. 2019 2 1 250 3 01. 09. 2019 3 1 180 4 01. 09. 2019 1 2 220 5 02. 09. 2019 1 1 200 6 02. 09. 2019 2 2 180 Foreign key HNEU, Department of Information Systems, Course Database, V. V. Fedko 22

SQL & relational databases: • The main types of databases are relational databases. • Relational databases are built on the relational model (algebra) • The SQL language is also built on relational algebra. Therefore, it allows you to build queries of any complexity. • SQL - non-procedural programming language HNEU, Department of Information Systems, Course Database, V. V. Fedko 23

Test questions En: Ru: 1. Compare file and databased approaches. 2. What are the components of a database system? 3. What type of database activity would you like to do? Why? 1. Сравните подходы на основе файлов и баз данных. 2. Каковы компоненты системы базы данных? 3. Каким видом деятельности с базами данных вы хотели бы заняться? Почему? Send your answers at email fvv. stud@gmail. com HNEU, Department of Information Systems, Course Database, V. V. Fedko 24