580 likes | 758 Views
CLARK UNIVERSITY College of Professional and Continuing Education (COPACE). Management Information Systems. Lection 0 3 Database management system. Plan. Term “Database” Architecture of database Data models Normal forms Operations of relation algebra Operations of SQL DBMS.
E N D
CLARK UNIVERSITY College of Professional and Continuing Education (COPACE) Management Information Systems Lection 03 Database managementsystem
Plan • Term “Database” • Architecture of database • Data models • Normal forms • Operations of relation algebra • Operations of SQL • DBMS
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?
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.
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.
Structured data Structuringis the introduction of agreements on the ways of presenting data.
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.
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.
Classification of databases Access to data With network access With local access
Classification of databases Centralized database with network access can have the following architecture: File-server Client-server Two-level model Three-level model
Архитект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
Архитект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
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
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
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
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)
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.
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
Data models • Hierarchical model • Network model • Relational model
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).
Links between different entities of data are described by a structured graph or a tree Hierarchical model
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
Link between the data are described by an arbitrary graph Network model
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
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.
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.
Relational model There are alternatives to the terms:
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)
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)
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.)
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.
Нормализация The normalization of relationsrepresent rules of such formation of relations (tables) that allow to eliminate duplication, inconsistency stored in the database.
Нормализация E. Codd developed three normal forms of relations and the mechanism, which allows to convert any relation to the third normal form.
Первая нормальная форма (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.
Первая нормальная форма (1НФ) The table is not in 1NF
Первая нормальная форма (1НФ) Table is in 1NF
Диаграмма функциональных зависимостей для примера БД «Студент»
Table is in 2NF if it does not contain any non-key attributes which are functionally dependent on part of the key
Table is in 3NF if it does not contain any non-key attributes, transitively dependent on the key part 45
Результат проектирования БД «Студент»
SQL A query language SQL (Structured Query Language) provides an access to information contained in relational databases for users, software and computing systems
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
Relational algebra Students of group 392 Union Students of group 591 Result
Relational algebra Intersection Students of groups 392 and 591 Students of group 392 Result