Databases: Why? DATABASE DESIGN I - 1DL300 Extremely prevalent - PowerPoint PPT Presentation

Databases: Why? DATABASE DESIGN I - 1DL300 • Extremely prevalent – Web sites, banking, telecom, sensors, retail, science and engineering, … Spring 2012 • You are using a database every day hour An introductury course on database systems http://www.it.uu.se/edu/course/homepage/dbastekn/vt12 • Database Management Systems (DBMS) provide … Erik Zeitler … efficient, reliable, convenient, and safe multi-user storage of and access to Uppsala Database Laboratory Department of Information Technology, Uppsala University, massive amounts of persistent data. Uppsala, Sweden Erik Zeitler - UDBL - IT - UU 2012-01-16 1 Erik Zeitler - UDBL - IT - UU 2012-01-16 2 Databases: What? Databases: How? • Massive – Terabytes per day • Data model • Persistent – Set of records, graph, XML • Safe – Hardware software power users • Schema vs data • Multi-user types – Concurrency control values variables • Convenient – Physical data independence • Data definition language (DDL) – High-level declarative query language – Set up schema • Efficient – 1000’s of queries and/or updates per second • Data manipulation language (DML)/Query Language (QL) • Reliable – Query and modify – 99.99999% Erik Zeitler - UDBL - IT - UU 2012-01-16 3 Erik Zeitler - UDBL - IT - UU 2012-01-16 4

Key DB people 1DL300 in a nutshell 13 LECTURES • DBMS implementer 3 ASSIGNMENTS To be done in pairs: – Builds system (works at Oracle, IBM, Microsoft, Uppsala University ) • Course intro 1. Normalization – Overview of DB technology 2. DB Design and ER modeling • DB designer – DB terminology 3. SQL in RDBMS – Establish schema • ER-modeling Queries and views 1DL400 • Relational model and relational JDBC API access to RDBMS algebra • DB application developer 1DL300 • ER-to-relational mapping and – Programs that operate on DB 1 FINAL EXAM Normalization Skrivsalen Mon March 5 • • SQL • No books allowed • DB Administrator (DBA) • Transactions, Concurrency control – Operations & Maintenance • Recovery techniques – Tuning & Optimization • Intro to storage and index structures Erik Zeitler - UDBL - IT - UU 2012-01-16 5 Erik Zeitler - UDBL - IT - UU 2012-01-16 6 Friendly reminders from the Student Office Personnel 1. Not admitted? 1. Swedish students: Go to www.antagning.se 1DL300 sen anmälan Lecturers Assistants 2. Master students: Go to your study counsellor 3. Exchange students: Ulrika Jaresund Mikael Lax • Erik Zeitler (main teacher) • 2. Make sure you are registered (registration deadline January 29) phone 471 7345 – room 1320 • room 1306 • 1. Studentportalen (all) • Silvia Stefanova Lars Melander • 2. Registered before, want to register again? Student Office it-kansli@it.uu.se (re-register) – room 1319 phone 471 1051 • 3. Master students: Study counsellor • Anne Peters room 1316 • 4. Exchange students: Ulrika Jaresund – room 2005 Minpeng Zhu • 3. Want to quit the course? phone 471 3155 • room 1310 – Inform the Student Office! (it-kansli@it.uu.se) • • If less than 3 weeks have passed since the course started, the course registration will be removed. After 3 weeks a "course intermission" will be reported to UPPDOK instead. 4. Don’t forget to sign up for the final exam firstname.lastname@it.uu.se Erik Zeitler - UDBL - IT - UU 2012-01-16 7 Erik Zeitler - UDBL - IT - UU 2012-01-16 8

The database market ( Computer Sweden May 24, 2002) Introduction to Database Terminology Elmasri/Navathe chs 1-2 Padron-McCarthy/Risch ch 1 Erik Zeitler Department of Information Technology Uppsala University, Uppsala, Sweden Erik Zeitler - UDBL - IT - UU 2012-01-16 9 Erik Zeitler - UDBL - IT - UU 2012-01-16 10 Outline of a database system DBMS deployment plans (Gartner 2008) Erik Zeitler - UDBL - IT - UU 2012-01-16 11 Erik Zeitler - UDBL - IT - UU 2012-01-16 12

Database? Database management system? • A database (DB) is a more or less well-organized collection of related data . • A database management system (DBMS) is one (or several) program that provides functionality for users to develop , use , and maintain a database. • The information in a database . . . • Thus, a DBMS is a general software system for defining , populating (constructing), manipulating and sharing databases for different types of applications. – represents information within some sub-area of “ reality ” (i.e. objects, characteristics and relationships between objects) • Also supports protection (system and security) and maintenance to evolve the – is logically connected system. – has been organized for a specific group of users and applications Erik Zeitler - UDBL - IT - UU 2012-01-16 13 Erik Zeitler - UDBL - IT - UU 2012-01-16 14 Database system? Database vs files • A database system consists of . . . – the physical database (instance) • DB in comparison to conventional file management: – a database management system – data model – data abstraction – one or several database languages – meta-data – in catalog (means for communicating with the database) – one or several application program(s) – program-data and program-operation independence – multiple views of data • A database system makes a simple and efficient manipulation of large data sets – sharing data – multiuser transactions possible. – high-level language for managing data in the database • The term DB can refer to both the content and to the system (the answer to this ambiguity is governed by the context). Erik Zeitler - UDBL - IT - UU 2012-01-16 15 Erik Zeitler - UDBL - IT - UU 2012-01-16 16

Advantages of using a database approach Data model? • Efficient search and access of large data sets • Every DB has a data model • Controlling redundancy and inconsistency – “ hides ” the physical representation of data • Access control • Persistent storage • A data model is a formalism that defines • Indexes and query processing • Backup and recovery – a notation for describing data on an abstract level • Multiple user interfaces – a set of operations to manipulate data represented using the data model • Complex relationships • Integrity constraints • Data models are used for data abstraction • Active behaviour – Enabling definition and manipulation of data on an abstract level. • Enforcing standards, reducing application development time, flexibility to evolve system, up-to-date info Erik Zeitler - UDBL - IT - UU 2012-01-16 17 Erik Zeitler - UDBL - IT - UU 2012-01-16 18 Data models - examples Meta-data, i.e. “data about data” • Examples of representational (implementation) data models within the database • Information about which information that exists and about how/where data is field are: stored cf. variables – Hierarchical (IMS) – names and data types of data items in a program – Network (IDMS) – names and sizes of files – Relational (ORACLE, DB2, SQL Server, InterBase, Mimer) – storage details of each file – Object-oriented (ObjectStore, Objectivity, Versant, Poet) – mapping information among schemas – Object-relational (Informix, Odapter, DB2) – constraints • Conceptual data model • Meta-data is stored in a system catalog (alt. term data dictionary ). – ER-model - Entity-Relationship model – (not an implementation model since there are no operations defined for the notation) Erik Zeitler - UDBL - IT - UU 2012-01-16 19 Erik Zeitler - UDBL - IT - UU 2012-01-16 20

Schema and instance Data independence • To be able to separate data in the database and its description the terms • Reduces the connection between: – database instance and – the actual organization of data and – database schema are used. – how the users/application programs process data (or “ sees ” data.) • The schema is created when a database is defined. A database schema is not • Why? changed frequently. – Data should be able to change without requiring a corresponding alteration of the application programs. • The data in the database constitute an instance. Every change of data creates a new – Different applications/users need different “ views ” of the same data. instance of the database. Erik Zeitler - UDBL - IT - UU 2012-01-16 21 Erik Zeitler - UDBL - IT - UU 2012-01-16 22 Three-schema architecture Data independence - how? By introducing a multi-level architecture where each level represents one abstraction level End users End users • The three-schema architecture view 2 – Introduced in 1971 … External level … … view 1 view n – a.k.a. ANSI/SPARC architecture for databases – CODASYL Data Base Task Group. logical Conceptual level Conceptual schema • It consists of 3 levels: – Internal level physical Internal schema Internal level – Conceptual level – External level • Each level introduces one abstraction layer and has a schema that describes how Database instance Database instance representations should be mapped to the next lower abstraction level. Erik Zeitler - UDBL - IT - UU 2012-01-16 23 Erik Zeitler - UDBL - IT - UU 2012-01-16 24