2. Horizontal Data First, let's take a look at some issues regarding data in general

1. 2. Horizontal DataFirst, let's take a look at some issues regarding data in general CENTRALITY OF DATA Data are central to every computer program. If a program has no data, there is no input, no output, no constants, no variables... It is hard to imagine a program in which there is no data? Therefore, virtually all programs are data management programs and therefore, virtually all computing involves data management. However, not the all data in computer programs is RESIDUALIZED. RESIDUALIZED data is data stored and managed after the termination of the program that generated it (for reuse later). Database Management Systems (DBMSs) store and manage residualized data.

2. SOME OF THE MAIN PROBLEMS WITH DATA HUGE VOLUME(EVERYONE HAS LOTS OF DATA AVAILABLE TO THEM TODAY!) Data are collected much faster than data are process or managed. NASA's Earth Observation System (EOS), alone, has collected over 15 petabytes of data already (15,000,000,000,000,000 bytes). Most of it will never be use! Most of it will never be seen! Why not? There's so much volume, so usefulness of much of it will never be discovered. SOLUTION: Reduce the volume and raise the information density through structuring, querying, filtering, mining, summarizing, aggregating... That's the main task of Data and Database workers today! Claude Shannon's information theory principle comes into play here: More volume means less information.

3. STRUCTURING and RESIDUALIZING DATA Proper Structuring of datamay be the second most important task in data and database system work today! At the highest level, is the decision as to whether a data set should be structured as horizontal or vertical data (or some combination). Another important task to be addressed in data systems work today is RESIDUALIZATION OF DATA MUCH WELL-STRUCTURED DATA IS DISCARDED PREMATURELY Databases are about storing data persistently, for later use. RESIDUALIZING DATA may be the third most important task in data and database system work today!

4. WHAT IS A DATABASE? An integrated shared repository of operational data of interest to an enterprise INTEGRATED: it must be the unification of several distinct files SHARED: same data can be used by more than 1 user (concurrently) REPOSITORY: implies "persistence". OPERATIONAL DATA: data on accounts, parts, patients, students, employees, genes, stock, pixels,... By contrast, nonoperational incl. I/O data, transient data in buffers, queues... ENTERPRISE: bank, warehouse, hospital, school, corp, gov agency, person..

5. WHAT IS A DATABASE MANAGEMENT SYSTEM (DBMS) A program which organizes and manages access to residual data Databases also contains METADATA also (data on the data). Metadata is non-user data which contains the descriptive information about the data and database organization (i.e., Catalog data).

6. WHY USE A DATABASE? COMPACTNESS (saves space - no paper files necessary) EASE OF USE(less drudgery, more of the organizational and search work done by the system; user specifies what, not how) CENTRALIZED CONTROL (by DB Administrator (DBA) and by the CEO) REDUCES REDUNDANCY(1 copy is enough, but concurrent use must be controlled NO INCONSISTENCIES(again, since there is only 1 copy necessary) ENFORCE STANDARDS(corporate, dept, industry, national, international) INTEGRITY CONSTRAINTS(automatically maintained) (e.g., GENDER=male => MAIDEN_NAME=null) BALANCE REQUIREMENTS(even conflicting requirements? DataBase Administrator (DBA) can optimize for the whole company) DATA INDEPENDENCE(occurs because applications are immune to storage structure and access strategy changes. Can change the storage structure without changing the access programs and vice versa)

7. HORIZONTAL DATA Almost all commerical databases today are HORIZONTAL. That is, the contain horizontally structure data. Horizontal data is data is formed into files of horizontal records of a common type. HORIZONTAL DATA TERMINOLOGY stored (physical, on disk) FIELDS, RECORDS, FILES logical (as viewed by user) type (e.g., datatype) FIELDS, RECORDS, FILES occurrences (instances) TYPE: defines structure and expected contents (time-independent - changes only upon DB reorganization) OCCURRENCE: actual data instances at a given time (time-dependent - changes with every insert/delete/update)

8. STORED FIELD = smallest unit of stored data e.g., is a Lname field occurrence Char 25 might be the metadata type of that occurrence. Jones STORED RECORD = named horizontal concatenation of related stored fields. e.g., | Jones | John | 412 Elm St | Fargo | ND | 58102 | an instance field names City St Zip Lname Fname Address Lname(char25), Fname(char15), Address(char20), City(char15), St(char2), Zip(char5) field types Employee | Lname | Fname | Address | City | St | Zip | record and field names | Trath | Phil | 234 12St |Fargo|ND |58105| record instance | Thom | Bob | 12 Main | Mhd |MN|56560| record instance | Smith | James | 415 Oak | Mhd |MN|56560| record instance EoF End of File marker . . . | Jones | John | 412 Elm |Fargo| ND|58102| record instance Stored? STORED FILE = named collection of all occurrences of 1 type of stored record

9. | Jones | John | 412 Elm |Fargo| ND|58102| | Smith | James | | Thom | Bob | 12 Main | Mhd |MN|56560| | Trath | | 415 Oak | Mhd |MN|56560| | Jones | John |Fargo| ND| | Phil | 234 12St |Fargo|ND |58105| EoF | How these entities are stored and how they are viewed or known to users may differ. They may be known to the users in various logical variations. A logical record based on the physical employee record: Stored continued The employee file type IS the employee record type (+ possibly, some other type characteristics, e.g., max-#-records) In todays storage device world, there is only linear storage space, so the 2-D picture of a stored file, strictly speaking, not possible in physical storage media today. Some day there may be truly 2-D storage (e.g., holographic storage) and even 3-D. A more accurately description of a store file (as it would be stored on linear storage) is: So we also have LOGICAL FIELD = smallest unit of logical data LOGICAL RECORD= named collection of related logical fields. LOGICAL FILE = named collection of occurrences of 1 type of logical record which may or may not correspond to the physical entities.

10. Terminology Unfortunately there is a lot of variation in terminology. It will suffice to "equate" terms as follows in this course: COMMON USAGERELATIONAL MODELTABULAR USAGE File Relation Table Record Tuple Row Field Attribute Column When we need to be more careful we will use: relation is a "set" of tuples whereas a table is a "sequence" of rows or records (has order) tuple is a "set" of fields whereas a row or record is a "sequence" of fields (has order)

11. PHYSICAL DATA MODELS For conceptualizing (logically) and storing (physically) data in a database. HORIZONTAL MODELS for files of horizontal records, in which processing is typically done through vertical scans, e.g., Get and process1st record. Get and process next record ... RELATIONAL (simple flat unordered files or relations of records of tuples . of unordered field values) TABULAR (ordered files of ordered fields) INVERTED LIST (Tabular with an access paths (index?) on every field) HIERARCHICAL (files with hierarchical links) NETWORK(files with record chains) OBJECT-RELATIONAL (Relational with "Large OBject" (LOBs) fields) (attributes . which point to or contain complex objects)

12. PHYSICAL DATA MODELS cont. VERTICAL MODELS (for vertical vectors or trees of attribute values processing . is typically through logical horizontal AND/OR programs). BINARY STORAGE MODEL (Copeland ~1986) (This model used vertical value . and bit vectors. It has virtually dissappeared!) BIT TRANSPOSE FILES (Wang ~1988) (This model used vertical bit files. . It has also virtually dissappeared!) VIPER STRUCTURES (~1998) (Used vertical bit vectors for data mining.) PREDICATE-Trees or Ptrees (This model and technology is patented by NDSU . and uses vertical bit trees) (~1997)

13. STUDENT COURSE S# SNAME LCODE C# CNAME SITE |25|CLAY |NJ5101| |8 |DSDE |ND | |32|THAISZ|NJ5102| |7 |CUS |ND | |38|GOOD |FL6321| |6 |3UA |NJ | |17|BAID |NY2091| |5 |3UA |ND | |57|BROWN |NY2092| ENROLL LOCATION S# C# GRADE LCODE STATUS |32|8 | 89 | |NJ5101| 1 | |32|7 | 91 | |NJ5102| 1 | |25|7 | 68 | |FL6321| 4 | |25|6 | 76 | |NY2091| 3 | |32|6 | 62 | |NY2092| 3 | |38|6 | 98 | |17|5 | 96 | REVIEW OF HORIZONTAL DATA MODELS RELATIONAL DATA MODEL The only construct allowed is a simple flat relation for both entity description and relationship definition. The STUDENT and COURSE relations represent entities The LOCATION relation represents a relationship between the LCODE and STATUS attributes (1-to-many). The ENROLL relations represents a relationshipbetween Student and Course entities (a many-many relationship)

14. 25|CLAY|OTBK 32|THAISZ|KNB 38|GOOD|GTR STUDENTS 7|CUS 8|DSDE 6|3UA COURSES 6|3UA 7|CUS 6|3UA ND|68 ND|89 NJ|98 ENROLLMENTS NJ|76 ND|62 ND|91 REVIEW OF HORIZONTAL DATA MODELS HIERARCHICAL DATA MODELentities=records relationships=links of records forming trees EX: root type is STUDENT (with attributes S#, NAME, LOCATION), dependent type is COURSE (with attributes C#, CNAME), 2nd-level dependent type ENROLLMENT (with attributes, GRADE, LOC) If the typical workload involves producing class lists for students, this organization is very good. Why? If the typical workload is producing course enrollment lists for professors, this is very poor. Why? The problem with the Hierarchical Data Model is that it almost always favors a particular workload category (at the expense of the others).

15. 25| CLAY | MJ511 32 | THAISZ | NJ512 STUDENT records 68 76 89 91 62 ENROLLMENT records 8|DSDE|ND 7|CUS |ND 6|3UA |NJ COURSE records REVIEW OF HORIZONTAL DATA MODELS NETWORK DATA MODELentities = records relationships = owner-member chains (sets) many-to-many relationships easily accomodated EX: 3 entities (STUDENT ENROLLMENT COURSE) 2 owner-member chains: STUDENT-ENROLLMENT COURSE-ENROLLMENT Easy to insert (create new record and reset pointers), delete (reset pointers), update (always just 1 copy to worry about, ZERO REDUNDANCY!) network approach: fast processing, complicated structure (usually requires data processing shop) Again, it favors one workload type over others.

16. page1 RRN S# ST STATE-INDEX | 0 | 25 |NJ| RID STATE | 1 | 32 |NJ| |1,2| FL | | 2 | 38 |FL| |1,0| NJ | | 3 | 47 |NY| |1,1| NJ | |1,3| NY | page2 |2,0| NY | | 0 | 57 |NY| | | | | | | | | | | | | REVIEW OF HORIZONTAL DATA MODELS INVERTED LIST MODEL (TABULAR): Flat Ordered Files (like relational except there's intrinsic order visible to user programs on both tuples and attributes). Order is usually "arrival order", meaning each record is given a unique "Relative Record Number" or RRN when it is inserted. - RRNs never change (unless there is a reorganization). Programs can access records by RRN. Physical placement of records on pages is in RRN order ("clustered on RRN" so that application programs can efficiently retrieve in RRN order. Indexes, etc can be provided for other access paths (and orderings).

17. REVIEW OF HORIZONTAL DATA MODELS OBJECT RELATIONAL MODEL Object Relational Model (OR model) is like relational model except repeating groups are allowed (many levels of repeating groups - even nested repeating groups) and Pointers to very complex structures are allowed. (LOBs for Large OBjects, BLOBs for Binary Large OBjects, etc. for storing, e.g., pictures, movies, and other binary large objects.