Management Information Systems

1. CLARK UNIVERSITY College of Professional and Continuing Education (COPACE) Management Information Systems Lection 03 Database managementsystem

2. Plan • Term “Database” • Architecture of database • Data models • Normal forms • Operations of relation algebra • Operations of SQL • DBMS

3. Term “Database” • A huge amount of data is entered into computer systems every day. • Where does this data go and how is it used? • How can it help you on a job?

4. Term “Database” Widely database is a collection of facts about real world’s objects if some field. Field is a part of the real world which we learn for managing: company, university, etc.

5. Non-structured data Folder No. 16493, SmithJohn, 01/01/1976; folder No. 16593, LeVering Barbara, 03/15/1975; folder No. 16693, McCow Robert, 04/14/1976.

6. Structured data Structuringis the introduction of agreements on the ways of presenting data.

7. Database definition Database (DB)is a named collection of structured data related to a particular subject area. Database management system (DBMS) - a set of software and language tools necessary to create databases, keeping them up to date and organize the search in them the necessary information.

8. Classification of databases Data processing Centralized stored in a computer system, which may be the mainframe (access via terminals) or file server network. Distributed consists of several parts, which are stored in different computer network connection.

9. Classification of databases Access to data With network access With local access

10. Classification of databases Centralized database with network access can have the following architecture: File-server Client-server Two-level model Three-level model

11. Architecture “File-level”

12. АрхитектArchitecture “File-level”ура файл-сервер 1. Input and Display data 1. Keeping the database file 2. Data access and search Criteria 3. Implementation of computing functions on the data

13. АрхитектArchitecture “File-level”ура файл-сервер Advantages: 1. The absence of very high performance of the server (most importantly - the required amount of disk space) 2. The database is not running and will not being installed on a server Disadvantages: 1. High network traffic 2. Lack of special security arrangements file from the DB

14. Architecture “Client-server”

15. Architecture “Client-server” 1. Input and display data 2. Implementation of computing functions on data sets Keeping the database file Access to the data and search for certain criteria

16. Architecture “Client-server” Advantages: 1. Lower network traffic than the file-server model 2. SQL-Server provides functions to ensure the integrity and security of data Disadvantages: 1. In certain cases, some data sets may take quite a substantial amount of place

17. Two-level architecture Keeping the database file Data access and search by criteria Implementation of computing procedures on data sets 1. Input and Display data

18. Two-level architecture Advantages: 1. Significant reduction in network traffic compared to client server 2. High reliability of data storage and processing Disadvantages: 1. High demands on the computer server (disk space and speed)

19. Three-level architecture Three-level architecturesuggest the following application components: a client application ("thin client" or a terminal) connected to the applicationserver, which in turn is connected to the database server.

20. Stored data have a logical structure described by a model of data (data model), supported by the DBMS. Data modeldetermines the organization of data, constraints and the set of operations that are allowed on the object. Data models

21. Data models • Hierarchical model • Network model • Relational model

22. Hierarchical model The hierarchical model has been developed historically in the first turn. Based on this model it was created the first professional DBMS IMS in the late 60's - early 70‘s (IBM).

23. Links between different entities of data are described by a structured graph or a tree Hierarchical model

24. Hierarchical model Advantage: 1. Sufficiently high run-time operations on data Disadvantages: 1. Complexity of understanding for the average user 2. The presence of redundancy

25. Link between the data are described by an arbitrary graph Network model

26. Network model Advantages: Minimum redundancy Compared to the hierarchical model the network model provides a great deal in terms of the admissibility of the formation of new links Effective implementation in terms of memory consumption. Disadvantages: 1. Complexity of understanding for the average user 2. Weakened control of the accuracy of links

27. Relational model The relational model was proposed by Edgar Codin 1970. Based on the concept of relation. Graphically represented as a ratio of a table. In a relational database assumes that the user perceives the database as tables.

28. Relational model

29. Relational model Advantage: 1. Simplicity and clarity for a wide user, which is the reason of its wide distribution. Disadvantage: 1. Necessary redundancy because of the relationships between tables.

30. Relational model There are alternatives to the terms:

31. Primary key Primary key is a relation attribute (set of attributes) that uniquely identifies each of its records. Student (FolderNo., Surname, First_name, Birth_date, Group)

32. Foreign key We can link tables by foreign keys. Foreign keyis an attribute (set of attributes) of relation, which is the key of another relation. Student(FolderNo., Surname, First_name, Birth_date, Group) Group(Number, Specialisation, Head_of_group)

33. Indexes Indexis a means to accelerate the search operation records in the table, as well as other operations that use search (retrieval, modification, sorting, etc.)

34. Types of indexes Indexes • Secondary • The are used for enforcing searching and executing queries • There might be several secondary indexes • They might include several fields • The same field can enter different indexes Primary The key field is always indexed, so it doesn’t require an additional index.

35. Data redundancy

36. The problem of update

37. The problem of inserting new data

38. Нормализация The normalization of relationsrepresent rules of such formation of relations (tables) that allow to eliminate duplication, inconsistency stored in the database.

39. Нормализация E. Codd developed three normal forms of relations and the mechanism, which allows to convert any relation to the third normal form.

40. Первая нормальная форма (1НФ) Table is in 1NF if each its cell has always the only atomic value, and there can never be the set of such values.

41. Первая нормальная форма (1НФ) The table is not in 1NF

42. Первая нормальная форма (1НФ) Table is in 1NF

43. Диаграмма функциональных зависимостей для примера БД «Студент»

44. Table is in 2NF if it does not contain any non-key attributes which are functionally dependent on part of the key

45. Table is in 3NF if it does not contain any non-key attributes, transitively dependent on the key part 45

46. Результат проектирования БД «Студент»

47. SQL A query language SQL (Structured Query Language) provides an access to information contained in relational databases for users, software and computing systems

48. Relational algebra SQL is based on relational algebra operations. Relational Algebrais a set of operations on relations. Relational algebra was developed within the relational model by Codd. Using the relational algebra we can get other relations

49. Relational algebra Students of group 392 Union Students of group 591 Result

50. Relational algebra Intersection Students of groups 392 and 591 Students of group 392 Result