Modern database systems & their applications Spring 2012 - PowerPoint PPT Presentation

Modern database systems & their applications Spring 2012 Lecturer: Serafim Dahl serafim@csc.kth.se DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 1 / 48

General information • Course credits 7,5 hp • In this course we elaborate on databases • You must have basic knowledge about relational database concepts, relational algebra, relational calculus, functional dependencies and database modelling. DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 2 / 48

What will we do on the course? We’ll look at: • problems with relational databases, • attempts to solve the problems and • how the problems actually were solved (if at all . . . ). • Modelling, • new data types (e.g. multimedia data types) and • indexes for new data types • Object / Object relational databases. • Database driven applications / web applications with db back-end (architecture, how to build them) DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 3 / 48

What we will actually do . . . We will: • model, sometimes with inadequate tools, to get a grip on what tools that are adequate in different situations, • practice some on XML and indexes and • build layered applications with database back-end DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 4 / 48

Literature Any textbook from the basic courses on database technology will do, e.g. Elmasri, Navathe: ”Fundamentals of Database Systems” Connolly, Begg: ”Database Systems”, Addison-Wesley Silberschatz, et al: ”Database System Concepts”, McGraw-Hill They focus on slightly different things but cover most of the issues on this course. The slides and the web will do for what the books don’t cover DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 5 / 48

Database systems user/appl user/appl user/appl communication DBMS OS database DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 6 / 48

Database systems . . . ... external external external schema 1 Data schema 2 schema n definition conceptual Data schema manipulation Data internal administration schema DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 7 / 48

Database applications back−end web browser esql DBMS HTTP RMI/IIOP front−end applet jdbc DBMS logical HTTP RMI/IIOP layer RMI/IIOP swingGUI orm/map HTTP/XML wrapper RMI/IIOP RMI/IIOP DBMS DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 8 / 48

Web database applications Client Application layer Data layer Presenta− tion layer Application server JVM HTTP JDBC Bean JSP HTML DB Web server Bean XHTML JSP engine XML Java Mail Mail server Connection pool Catalogue JNDI Security service Applet manager Servlets RMI Java Error log Servlet engine application IIOP CORBA Bean Bean Client Sessions application Bean Bean appl Streams Session beans Entity beans Sockets Jini Java− EJB server Serialization Spaces PHP engine File system DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 9 / 48

what is a relational database (on this course)? A relational database is a set of schemas , {R 1 , R 2 , . . . , R n } . Each schema R i has more than one interpretation. It defines a type for the tuples that the corresponding relation may contain and the type for the relation as well and the set of attributes {A 1 , A 2 , . . . , A k } that identify the values of the tuples The type for the tuples is defined by the fact that each attribute name A j is associated with a domain Thus, a tuple define a mapping that to each attribute sets a value from its domain or as a function that to each tuple in a database identifies a value from the power domain of the database universe. DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 10 / 48

A relational database . . . So, a relational database is a set of sets (of sets . . . ). It is important to note that an “SQL database” without restrictions is not really a relational database as it is possible to store duplicates. Thus, the SQL language defines relational databases as if the type was “bag”. To enforce the set type we need primary key restrictions. But that is not all . . . DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 11 / 48

A relational database . . . A relational database is a variable, logically consistent set of interrelated data, organized to reduce redundancy to a minimum and to facilitate maintenance of data integrity. The set of data is collected for a specific purpose and the database shall 1. correspond well with the “reality” it is supposed to represent 2. not allow storing data that is impossible in the corresponding “reality” 3. not allow storing undesirable data 4. contain a minimum of redundant data 5. not contain contradictory data (redundant data must match) DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 12 / 48

A relational database . . . Thus we must normalise the database 1NF All domains are atomic 2NF Every non-prime attribute is fully functionally dependent of every candidate key 3NF Every non-prime attribute is non-transitively dependent of every candidate key. , Alt For each FD X → A at least one of the following holds 3NF • X → A is trivial • X is a super-key • A is a prime attribute BCNF Every determinant is a super-key (which implies the first two rules for 3NF) DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 13 / 48

Important relational database properties • All FDs are preserved in a 3NF normalized DB • All search is performed by matching stored values • Keys, candidate keys & foreign keys Restrictions • Domains, predefined and user defined • Unique for candidate keys • Triggers and assertions • NOT NULL Design • Conceptual • Logical • Physical DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 14 / 48

Functional dependencies • Used to describe restrictions • Transitive closures for verification of keys • Starting-point for normalization DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 15 / 48

Normalization • Analysis • Find all candidate keys • Split the (universal) relation until the result satisfies the demands for 1NF, 2NF, 3NF/BCNF • Synthesis • Find a minimal set of FDs • Rewrite FD set on canonical form • The obtained set of FDs implicates the database structure • Analysis vs Synthesis • Analysis requires that we find all candidate keys (NP-complete), for synthesis one is enough • Synthesis require good mathematical knowledge about FDs, while analysis can be performed mechanically DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 16 / 48

Query languages, mathematics Relational algebra: σ , π , × , ⊲ ⊳ , ∪ , \ Tuple calculus: { t | R ( t ) ∧ φ ( t ) } Domain calculus: { a , b , c | R ( a , b , c ) ∧ φ ( a , b , c ) } DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 17 / 48

SQL, DML select [ distinct ] list-of-attributes // Projection from list-of-base-relations // Cartesian product [ where list-of-predicates ] // Selection [ group by second-list-of-attributes // Partitioning [ having another-list-of-predicates ] ] // Demands on each partition [ order by third-list-of-attributes ] // Sorting DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 18 / 48

SQL, DML . . . We also have union, except, intersect, join, natural join, outer join But must distinguish between SQL(-86,-87,-89), SQL-92 (SQL2) – major revision of SQL (mostly sharpening semantics defs) and SQL3, which is still evolving: SQL:1999 introduced regular expressions, recursive queries, triggers, support for programming features (procedures, flow-control), non-scalar types, and some object-oriented features. SQL:2003 introduced XML-stuff, window functions, standardized sequences SQL:2006 expanded XML features SQL:2008 expanded use of ORDER BY, added INSTEAD OF triggers, added TRUNCATE statement (delete all data in a relation).. . . ) DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 19 / 48

Communication with databases May be achieved through a variety of techniques embedded SQL (ESQLC, ESQLP , . . . ) SQL statements embedded into a standard programming language – works with nearly all programming languages JSQL SQL embedded into a Java program ODBC Object DataBase Connectivity – works with most any programming language JDBC Java DataBase Connectivity JSP , PHP , Perl, Python and other scripting languages for database access from web applications Perl, tk/tcl, Python for access from traditional applications. DD2471 (Lecture 01) Modern database systems & their applications Spring 2012 20 / 48