Database

1. Database Chapter 1 – Pascal’s Practical Issues... The issue of Data Types

2. Fabian Pascal – Database Activist • General argument – that the Relational Model of Data is by far the best and most robust method for organising data. • Relational model is the application of logic and math to database management.

3. Intro • Computer industry is driven by fads (just like the fashion industry) • Vendors profit from accelerated obsolescence • Users pay, vendors (helped by the trade media) profit • Profit comes largely from ignorance and luring users to the next fad

4. Larry Ellison (?) • CEO of Oracle • “Client Servers were a tremendous mistake and we are sorry that we sold it to you” (1999 ) • Only thing that should run on the desk top is a browser and word processing.

5. Trouble at mill • Database mgmt failures are largely due to failure of DBMS users to be properly educated – • The approach is often ad-hoc, cookbook approach... This is based on business culture • DBAs are often self-taught • Not exposed to database concepts, principles and methods

6. DB fundamentals • Are not product or vendor specific • Fundamentals are often deemed “theoretical” or “academic” • Industry focused on training (in a particular product)

7. The main issues • Unstructured data and complex data types • Business rules and integrity enforcement • Keys • Duplicate data • Normalisation and “denormalisation” • Entity subtypes and supertypes • Redundancy

8. What is a DBMS • Basically a deductive logic system • Derives new facts from a set of asserted facts • The derived facts are true if and only if : • The initial assertions are correct • The derivation rules are logically sound and consistent. • Everyone wants the right answers – but many are unaware that their practices and their use of DBMS software fails to adhere to these points

9. Careful what you wish for: Data Types and Complexity • Issue of Type (data type) is often misunderstood • A cause and a consequence (historically) by RDBMS to implement domains • So, the blame is placed on the “relational model” for lack of support for so-called complex data types

10. Fundamentals • A row in a database can be expressed in natural language – for eg (proposition) • Employee number is 100, the name is Jones, works in department HR, was hired 22/08/2009 and earns a salary of $45,000 • The general form... (predicate) • Employee number is EMP#, the name is ENAME, works in DEPT#, was hired on HIREDATE and earns salary SAL

11. So... • When specific values are substituted for the place holders the predicate reduces to an individual proposition. • Data Types are in essence the rules that define among other things, sets of valid values for a database. • Data types serve as conceptual pools of the permissible values.

12. Every database value is of a type... Or “typed” • And it consists of • A name • One or more possible representations of which ; • One is physically stored • At least one is declared to the users • Possible additional type constraints • A set of operators permissible on the type’s values.

13. Type v representations • How a types values are represented internally in physical storage is distinct to how we view it. • Also, a type can have multiple representations for eg. • Temperature can be Celsius or Fahrenheit (or simply warm/hot/cool/brass monkey • Actual representation should be hidden from users – Data Independence

14. Operators • At least one operator to select (obviously in SQL that is SELECT) • Many things are not meaningfully comparable even though those operators would allow for comparison • It wouldn’t make sense to compare TEMPERATURE with SALARY even if they were both represented as decimals

15. “Simple” Types • Atomic types • System-defined – but all DBMS’s come with their own set • INTEGER (INT) Declared representation INTEGER Type constraint >=-231 and <=231 Operators – set applicable to integers

16. Cont.. • Physical representations of system defined types are vendor specific (OS platform)

17. User-defined • Type EMP# • Declared representation CHAR(3) • Type constraint [A-Z][0-9][0-9] • A set of operators applicable to employee numbers (eg. select, =, but not < or >) • SALARY could be constrained by some business rules, not more than 150,000 for eg. Operators may include the general integer operators but probably not sqroot

18. User defined • Includes the ability to define and limit the set of operators that are associated with a given data type • Otherwise erroneous comparisons can occur. - Examples?

19. Erroneous comparisons? • The truth of facts, or, the correctness of data representations depends on whether or not the values are (a) consistent with the rules and (b) correspondent with the external world, that is not erroneous. • (a) is your responsibility as a DB designer. • (b) is almost invariably accidental.

20. Does it make sense • To compare shoe size with age (or IQ) ? • Sometimes perhaps... DBMS should support user-defined types but also user-defined functions Yes Oracle does!

21. Complex types • A type is a named set of values without any restrictions as to how those values are represented. • ANY representation is ok • Operators must be able to be defined for ANY such representation • What about a type FINGERPRINT?

22. How would be store the FINGERPRINT data type • How would we compare it? • How do we agree upon what kinds of operators are the right ones • We can all agree on INTEGER operators. • The implementation of operators for such types is not only complex but also not universally agreed upon.

23. Cont. • Is this a shortcoming of the relational model? • It’s not that relational can’t handle such data types... (it can) • Implementation of “complex” types is best left to those with expertise in the application domain where such types might be used (user defined) • And they can be defined for any desired representation / manipulation

24. “Unstructured Data” • Is there such a thing? • Isn’t all (meaningful) data structured ? • Info in text/images can be represented relationally – but at a cost (knowledge, time, effort) • Complex coding of types and associated operators • Perceptions are subjective ...