Week 4: Database technologies and applications

• Database
  
• Entity relationship model
  
• Database normalization process

Database: "organized collection of data"

• ex. libraries, flight reservation systems
• languages: ex. SQL, Xquery
• data definition: data types and interrelationships
• data manipulation
• query

Db mgmt systems (DBMs)

• software for creating, updating, administering, etc. interaction b/w dbs, and UX? 
• ex.: Access, FileMaker Pro, MySQL
• 3 views of data
• external (can be ++): users
• conceptual (usu. 1): synthesis of external
• internal/physical (usu. 1): op issues

Standards allow for interoperability

Categorization of systems

• contents
• models supported, ex. XML
• type of comp, ex. mobile or server cluster
• query language
• internal engineering

History

• 1960s: navigational DBMs 
• direct-access storage --> shared, interactive
• 1970s: relational DBMs
• split data into "relations" with optimal elements
• more relevant for users?
• integrated approach hw<>sw
• late 1970s: SQL
• entity-relationship model (improve on rel.)
• "key" id for unique recs 
• minimal set of unique factors
• limitation: representation in rel. db not so easy
• 1980s:
•  desktop, dBASE
• object-oriented, data<>individual person (not field)
• 2000s: NoSQL, NewSQL
• XML, document-oriented

Design

• conceptual
• what is the structure of info to be held in db?
• entity-relationship model
• schema/logical database design
• implementation of relevant parts
• takes into acct particular DBMS used
• most popular for gen. use: relational model, esp. using SQL
• physical design
• db independent
• optimal UX

Model

• how data can be stored
• relational, SQL

Additional issues: security, migration, transactions, maintenance, restoration

Normalization

• no repeating elements or groups thereof
• no partial dependencies: must create new if failed
• no dependencies on non-key (non-important) characteristics