DATA TYPES, DATABASES

1. DATA TYPES, DATABASES Gergely ZAJZON Dr. Med.

2. DATABASE • Simply: structured collection of data

7. DATABASE • Simply: structured collection of data

8. DATABASE • Simply: structured collection of data • Storage: a collection of tables connecting to each other

9. DATABASE • In computer science, a database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. • The records retrieved in answer to queries are information that can be used to make decisions.

10. DATA • Data is visualised form of some kind of knowledge. Representative property of somebody or something. • From IT perspective: data is coded information, stored in a computer as a signal sequence. • Has to be stored in a structured and processable form

11. DATA - examples • PATIENT DATA:- name- birth date - address - „TAJ” – patient code Phone book:- name- address- phone number

12. INFORMATION • The Sesame Street character Cookie Monster describes information as "news or facts about something." • Measure unit of information: bit: value can be 1 or 0 (true / false). • Information is the result of processing, gathering, manipulating and organizing data in a way that adds to the knowledge of the receiver..

13. INFORMATION Why do we create databases? • Storing and searching of data • Retrieval of new informations from stored data collections

14. DATA TYPES • Basic property of data fields • Defines: • a set of values • structure of data • allowable operations on those values • Makes it possible, that in one column of a table only same type of data can be stored.

15. BASIC DATA TYPES • Can not be divided to further separete parts

16. COUNTABLE DATA TYPE CLASSESS

17. REAL DATA TYPE CLASSESSNumeric real data types

18. DATA TYPE • Choosing the correct data type: • Helps making your database more structured • Eg.: a field containing birth dates should be DATE type. • Helps validating your data: • Eg.: DATE field can not contain a date like 31. february 2008. • You can not enter a text into an INTEGER field

20. Data Types • Binary • Database specific binary objects • Pictures, digital signatures, etc. • Boolean • True/False values • Character • Fixed width or variable size • Numeric • Integer, Real (floating decimal point), Money • Temporal • Time, Date, Timestamp

21. SPREADSHEET <-> DATABASE • Spreadsheet (EXCEL table): structured.- search- order- filter • Software: Eg.: MS-Excel

22. SPREADSHEET <-> DATABASE • „Excel database”: table with special layout: • Rows of the table are connected with eachoter, containing the properties of one selected object. Columns: containing the same kind of property for each object. The table has a header in the first row.

23. SPREADSHEET <-> DATABASE

24. SPREADSHEET <-> DATABASE • Disadvantages of Spreadsheet • The same data set can only be modified by one user at the same time • Storage of data is not safe enough • Structure of data and connections are not defined

25. SPREADSHEET <-> DATABASE • Disadvantages of Spreadsheet • Logical limit: can not use multiple tables easiliy, as connections between tables can not be defined

26. SPREADSHEET <-> DATABASE INDIRECT PROPERTY => REDUNDANCY, MISTAKES IN DATA INPUT

32. DATABASE • Data collection organized on the basis of a data model. • Contains: • data • Structure of data • Connection rule descriptions

33. DATA MODEL • A logical structure, defining the layou of the stored data. • Contains no data values. • Only properties of data, structure and possible connections

34. DATA MODEL CONCEPTUAL DATA MODEL: • Analysis of the informations and connections between them. • Independent from software tools PHYSICAL DATA MODEL: • Structured used in the database:definition of tables, data types, connection • Software dependent

35. CONCEPTUAL DATA MODEL MOZIK FILMEK PK PK MOZI_ID FILM_ID M N MOZINEV FILMCIM MOZICIM HOSSZ MOZITEL NYELV MOZIWEB RENDEZO

36. PHYSICAL DATA MODEL MOZI__FILM FILMEK MOZIK PK MOZIFILM_ID PK N FILM_ID MOZI_FK PK MOZI_ID N 1 FILMCIM 1 FILM_FK MOZINEV HOSSZ MOZICIM VETITES NYELV MOZITEL RENDEZO MOZIWEB

37. Parts of a database • Attributes (fields) • An attribute or field is a component of a record that describes something about an item. • Records • A record is the representation of an individual item. • Table • A collection of records • Database • A collection of tables and rules for accessing the tables

38. Parts of a database Record Tables Attribute/Field • Records become “rows” • Attributes/fields become “columns” • Rules determine the relationship between the tables and tie the data together to form a database

39. Referential Integrity • Rules to preserve relationships • Prevents orphan records • Cannot add records on many sides • Cannot delete from one side • Cascade update • Cascade delete

40. NORMAL FORMS • The normal forms (abbrev. NF) of relational database theory provide criteria for determining a table's degree of vulnerability to logical inconsistencies and anomalies. The higher the normal form applicable to a table, the less vulnerable it is to such inconsistencies and anomalies. Each table has a "highest normal form" (HNF): by definition, a table always meets the requirements of its HNF and of all normal forms lower than its HNF; also by definition, a table fails to meet the requirements of any normal form higher than its HNF. • The normal forms are applicable to individual tables; to say that an entire database is in normal form n is to say that all of its tables are in normal form n.

41. Normalizing • Dependencies between data are identified • Redundant data is minimized • Reduces chances of data errors • Reduces disk space • The data model is flexible and easier to maintain

42. First Normal Form • Eliminate repeating columns in each table • Create a separate table for each set of related data • Identify each set of related data with a primary key Benefits: Now we can have infinite phone numbers or company addresses for each contact. Drawback: Now we have to type in everything over and over again. This leads to inconsistency, redundancy and wasting space. Thus, the second normal form…

43. Second Normal Form • Create separate tables for sets of values that apply to multiple records • Relate these tables with a “foreign key”.

44. Third Normal Form • Eliminate fields that do not depend on the primary key. Is this enough? Codd thought so… What about “many to many”?

45. Kinds of Relationships • “One to One” • One row of a table matches exactly to another • One person, one id number, one address • “One to Many” • One row of a table matches many of another • One person, many phone numbers • “Many to Many” • One row may match many of another or many rows match one row of another

47. BETEGEK PK TAJ NEV IRSZ VAROS CIM HAZIORVOS REND IRSZ REND VAROS REND_CIM RENDMH_TEL BETEGEK ORVOSOK PK PK TAJ PECSÉT M N NEV ORVOS IRSZ IRSZ VAROS VAROS CIM CIM TEL

48. DATA MANAGEMENT LANGUAGE • SQL = Structured Query Language • DDL – Data Definition (CREATE) • DML – Data Manipulation (INSERT, UPDATE, DELETE) • QL – Query Language (SELECT)

49. TABLE TYPES • Main (core) tables • Subtables • Linking tables (association table) • Dictionary tables (look-up table, value list)

50. DATABASE SERVER • A database server is a computer program that provides database services to other computer programs or computers, as defined by the client-servermodel. The term may also refer to a computer dedicated to running such a program. Database management systems frequently provide database server functionality, and some DBMS's (e.g., MySQL) rely exclusively on the client-server model for database access. • Database Master servers are central and main locations of data while Database Slave servers are synchronized backups of the master acting as proxies.