210 likes | 369 Views
NEU 221: Neuroinformatics Seminar Introduction to Databases Bertram Ludäscher ludaesch@sdsc.edu San Diego Supercomputer Center U.C. San Diego. Outline. What is a DB and why should I care? DB Basics & Architecture Relational Model (SQL, ER) Extended/Other Models Deductive Databases
E N D
NEU 221: Neuroinformatics SeminarIntroduction to DatabasesBertram Ludäscherludaesch@sdsc.eduSan Diego Supercomputer CenterU.C. San Diego
Outline • What is a DB and why should I care? • DB Basics & Architecture • Relational Model (SQL, ER) • Extended/Other Models • Deductive Databases • Object-Oriented Databases • Semistructured/Graph-Databases
What is a Database? • The term database can stand for ... • a concrete collection of data (books@amazon, CCDB@NCMIR) • a system (software & hardware) for storing and managing databases (=> Database Management System: DBMS + DB) • Underlying data model => Type of DBMS (short: DB) • relational model: based on relations (“tables”) and entities • object-oriented model: complex objects, classes • object-relational model: relations + objects • XML: “semistructured” model, trees • Specialized/extended models • deductive DBs • multimedia DBs • GIS (Geographic Information Systems)
Functions of a DBMS (aka what does it buy me?) • Persistent Data Storage • but don’t forget to backup! • Efficient & High-Level Querying of Very Large Datasets • file systems + your homegrown “scans” won’t do for VLDBs!! • Same for Updates: insert, delete, and modify • Data Integrity, Security • Checking/enforcement of integrity constraints • Access control • Concurrent (multi-user) Access, Transactions, Recovery • Robust, Scalable Data Management Solutions
3-Level ANSI/SPARC Database Architecture • external (user) level • conceptual (logical) level • internal (physical) level => Data independence • logical data independence • physical data independence View -1 View -2 View -n conceptual/logical schema physical schema
Concurrency Control • Concurrent execution of simultaneous requests • long before web servers where around... • transaction management guarantees consistency despite concurrent/interleaved execution • Transactions = Sequence of DB operations (read/write) • Atomicity: a transaction is executed completely or not at all • Consistency: a transaction creates a new consistent DB state, i.e., in which all integrity constraints are maintained • Isolation: to the user, a transaction seems to run in isolation • Durability: the effect of a successful (“committed”) transaction remains even after system failure
The Relational Model • Relation/Table Name: • employee, dept • Attributes = Column Names: • Emp,Salary,Deptno, Name, Mgr • Relational Schema: • employee(Emp:string, Salary:float, DeptNo:integer), ... • Tuple = Row of the table: • (“anne”, “62000”, “2”) • Relation = Set of tuples: • {(...), (...), ...} employee EmpSalaryDeptno john 60k 1 anne 62k 2 bob 57k 1 jane 45k 3 dept DeptNo Name Mgr 1 Toys anne 2 Sales anne 3 Shoes tim
Name Name since Manager works-for Salary Database Design: Entity-Relationship (ER) Model • Entities: • Relationships: • Attributes: • ER Model: • initial, high-level DB design (conceptual model) • easy to map to a relational schema (database tables) • comes with more constraints (cardinalities, aggregation) and extensions: EER (is-a => class hierarchies) • related: UML (Unified Modeling Language) class diagrams Employee Department
Example: Creating a Relational Database in SQL CREATE TABLEemployee ( ssn CHAR(11), name VARCHAR(30), deptNo INTEGER, PRIMARY KEY (ssn), FOREIGN KEY (deptNo) REFERENCES department ) CREATE TABLEdepartment ( deptNo INTEGER, name VARCHAR(20), manager CHAR(11), PRIMARY KEY (deptNo), FOREIGN KEY (manager) REFERENCES employee(ssn) )
Important Relational Operations • Select(Relation, Condition) • filter rows of a table wrt. a condition • Project(Relation, Attributes) • keep the columns of interest • Join(Rel1, Att1, Rel2, Att2, Condition) • find “matches” in a “related” table • e.g. match Rel1.foreign key = Rel2.primary key • Union (“OR”), Intersection (“AND”) • Set-Difference (“NOT IN”)
Why (Declarative) Query Languages? ,,Die Grenzen meiner Sprache bedeuten die Grenzen meiner Welt.” “The limits of my language mean the limits of my world.” Ludwig Wittgenstein, Tractatus Logico-Philosophicus “If you have a hammer, everything looks like a nail.” • Things we talk and think about in PLs and QLs • Assembly languages: registers, memory locations, jumps, ... • C: if-then-else, for, while, memory (de-)allocation, pointers, ... • Object-oriented languages: • C++: C plus objects, methods, classes, ... • Java: objects, methods, classes, references, ... • Smalltalk: objects, objects, objects, ... • OQL: object-query language • Functional languages(Haskell, ML): • (higher-order) mappings, recursion/induction, patterns, ... => Relational languages (SQL, Prolog) • relations, relational operations: , , , , ..., ,,,,,..., , , => Semistructured/XML (Tree) & Graph Query Languages
employee dept result EmpSalaryDeptno anne 62k 2 john 60k 1 DeptNoMgr 1 anne 2 anne EmpMgr john anne anne anne Example: Querying a Relational Database input tables join SELECTEmp, Mgr FROMemployee, dept WHEREemployee.DeptNo = dept.DeptNo SQL query (or view def.) answer (or view)
Non-Relational Datamodels • Relational model is “flat”: atomic data values • extension: nested relational model (“tables within tables”, cf. nested HTML tables) • values can be nested lists {...}, tuples (...), sets [...] • ISO standard(s): SQL • identity is value based • Object-oriented data model: • complex (structured) objects with object-identity (oid) • class and type hierarchies (sub-/superclass, sub-/supertype) • OODB schema may be very close to “world model” (no translation into tables) (+) queries fit your OO schema (-) (new) queries that don’t fit nicely • ODMG standard, OQL (Object Query Language)
Example: Object Query Language (OQL) • Q: what does this OQL query compute? • Note the use of path expressions like e.manager.children => Semistructured/Graph Databases SELECT DISTINCT STRUCT( E: e.name, C: e.manager.name, M: ( SELECT c.name FROM c IN e.children WHERE FOR ALL d IN e.manager.children: c.age > d.age ) ) FROM e IN Employees;
Querying Graphs with OO-Path Expressions ?- dblp."Inf. Systems".L."Michael E. Senko". Answer: L="Volume 1, 1975”; L="Volume 5, 1980". ?- dblp."Inf. Systems".L.P, substr("Volume",L), P : person.spouse[lives_in = P.lives_in].
Constructs for Querying Graphs Example: ?- dblp . any* . (if(vldb)| if(sigmod))