Part 3 Data ModelsTypes of Data Models Single Flat File B-tree or Indexed-Sequential Hierarchical Network or Codasyl Relational Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 2Database Models Single Flat File- no notion of relationshipsIndexed Sequential- sequenced data withindex to speed file searchindexHierarchicaldata- relationships followa tree structure- each record typehas exactly one parent(except the root) Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 3Network and Relational Database Models Network- relationships betweenrecord types in any 1-1or 1-many pattern- each record typecan participate in avariety of relationshipsRelational- dynamic constructionof relationships betweenrecord types based on content- each record type canparticipate in a varietyof relationships Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 4Flat File Model • Developed in late 1800's • Used on Unit Record equipment and Tape • PC Products: Microsoft File, pfs:File, DB Master, and PC-File Traditional Terminology file: a collection of data describing a set of entities that possess some common characteristics (attributes) sequential file: a collection of records organized only as a simple sequence from first to last flat file: a sequential file in which all records have the same format field names (attributes)Enamesmithjoneskingturnerdomain (set of possiblevalues for an attributerecord (set of valuesfor all attributes foran entity)valueJob Mgr Hired Rate Bonus DeptNoprogrammer barger 12/17/90402programmer radl 2/20/91 3535401clerk barger 2/22/91 18 402supervisor 3/20/91 75 1000 400 Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 5Keys The word “key” can be used in three distinct senses to refer to fields • an identifier (unique) • the Ename field is an example of an identifier • the Ename field would be called a primary key • a selection criteria for a report • the Hired field could be used as a key field in a report listing all employees hired in 1981 • the Hired field would be called a secondary key • a field or group of fields used in sorting • the Job and Mgr fields could be used to determine the order of output if we wished to list all employees by job title, and, within job title, by manager name • the Job field would be the major key and the Mgr field would be the minor key in the sort A composite key is a key containing more than one field (a group of field). Composite keys can be used as primary keys, secondary keys, and sort keys. Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 6Project Database as a Flat File Ename Job Mgr Hired Rate Bonus DeptNo Dname Locsmith programmer barger 12/17/90 35 402 academic 100 jones programmer radl 2/20/91 35 401 financial 200 king clerk barger 2/22/91 18 402 academic 100 turner supervisor 3/02/91 75 1000 400 programming 200 ... ..................................employee task #1.................. Dbudget Project_id Description Tname Hours Pbudget Due_date 390000 billing Student billing implement 40 11000 1/30/98 275000 billing Student billing implement 20 11000 1/30/98 390000 admit Admissions clerical 25 15000 4/7/98 150000 billing Student billing manage 12 11000 1/30/98 ... ............................employee task #2............................. Project_id Description Tname Hours Pbudget Due_date alumni Alumni development implement 90 7500 1/30/99 ... payroll Payroll debug 40 9000 5/15/98 ... records Student records clerical 15 6000 2/11/88 ... budget Budgeting design 45 12500 3/12/88 ... ... Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 7Operations on Flat File Databases Create: Must reserve space for all possible combinations for all records (must reserve space for 4 tasks) Destroy: When last member of a department is destroyed, department data is lost (no data for support department) Update: Update anomalies - when department data changes, must change all occurrences Select: Must search entire file (except if file is kept sorted by employee name, in which case those selections involving employee name are faster) Associate Concatenate Must be done procedurally Predicate (describe how) Function Record-at-a-time Navigation: First, next, prior Possible Improvements: Could reduce wasted space by creating multiple flat files, each with a different focus? However, this replaces one problem with potentially others, including: - How do you decide how to break things up? - How do you avoid further duplication in the break up? - How do you put information back together again? And you need to write code to do all of this Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 8Indexed Sequential (B-tree) Developed in the early 1960's Products such as LISA, MIDAS, ISAM, VSAM, etc. Terminology: allen programmer ...pearsonvogeljonespearsonsmithvogelbarger supervisor ...jones programmer ...king clerk ...martin programmer ...olson analyst ...pearson programmer ...radl supervisor ...rogers programmer ...smith programmer ...sturm clerk ...thomas analyst ...turner supervisor ...vogel consultant ...over-indexindexsequentialsubfile Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 9Indexed Sequential Features Operations basically the same as on the flat file. Selection on the primary key is much faster than flat file access since the index is used Navigation is slightly more complicated between sequential sub-files, but basically allow for: First, next, prior, any primary key value Indices can be created for secondary keys with slightly more effort, and significantly higher update costs Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 10Hierarchical Structure Hierarchical or tree structure developed in late 1960's Products such as System 2000, IMS, and Focus Record types interconnected using a parent-child relationship Still embodies record-at-a-time processing Need to replicate duplicates if they are in different parts of the tree Sample Database Structure: departmentDeptNo Dname Loc Dbudget employeeEname Job Mgr Hired Rate Bonus taskTnameprojectProject_id Hours Description Pbudget Due_date Copyright © 1971-2004 Thomas P. Sturm Data Models Part 3, Page 11Operations on Hierarchical Databases Create: Can create class without all instances Name: Can name to show