DATABASES DATABASE MANAGEMENT SYSTEMS DBMS MS ACCESS What

DATABASES & DATABASE MANAGEMENT SYSTEMS (DBMS). MS ACCESS • What is a database • Database terms • DB constructing stages • DB models • Relational model • Normal forms • DBMS objects • SQL 1

A database is a collection of associated or related data. For example, the collection of all information in a doctor’s office can be referred to as a “medical office database”. Information stored in computer storage device is organized in files. For example, a medical office database may have files on patient data, pharmaceutical data, employee data, and so on. Individual data is stored in records within files. For example, individual items on patient’s record would include his (her) name, address, and so on. Information such as name, address, etc. is referred to as a field or attribute. So, databases contain files, files contain records, and records contain fields or attributes. 2

Database constructing stages: conceptual, logical, physical (program-technical) To create a database for a certain organization one must first collect those data, then conceptualize how data might be organized (what are the relations between data) with a logical model then realize a database by the mean of software physically. Depending on the database logical model we classify databases as: Hierarchical Network Relational Mixed (relational-object oriented, hierarchicalrelational, etc. ) 3

In early 1970 Dr. Edgar F. Codd introduced the relational model. The relational model is based on the idea that if you ignore the way data files are connected and arrange your data into simple twodimensional, unordered tables, then you can develop an algebra for queries and focus on the data as data, not as a physical relation of a logical model. Note: consequently, in the most resources you may find data base terms entity set, entity, and attribute instead of file, record, and field, respectively, so that to disconnect the idea of physical file, record and field from the logical notion of these things. A collection of two-dimensional tables with records is referred to as a relational database. 4

No Patient’s name Address 1 Petrov Ivan 125 Abai ave. 2 Rustemova Aina 68 Dostyk ave To arrange the tables so that Codd’s relational algebra would work, Codd proposed normal forms: first, second, third normal forms. There also normal forms of BCNF (Boyce-Codd Normal Form), 4 -th, 5 -th normal forms. 5

Data Base Management Systems (DBMS) store, process and retrieve huge volumes of data DBMS help people to organize data in convenient for storing, processing, accessing, transmitting data way Widely spread DBMS systems: MS Access, Oracle DBMS main objects: • Table • Query • Form • Report • Page 6

SQL - structured Query Language built in most DBMS (PL SQL, MS SQL Server-2003, etc. ) SQL consists of: • Data Definition Language (DDL) • Data Manipulation Language (DML) containing operators & functions to store, process, access, transfer the needed data 7

• DDL helps to define data (data field name and properties) in a new Data Base Data types available: text, numeric, currency, date/time, logical, etc. • DML makes possible to: create new Data Base; delete tables, table fields, records; insert new data; edit data; Calculate (process) data; Arrange data in order (ascending, descending) Select needed data, etc. 8