CS 422 Principles of Database Systems Course Overview

CS 422 Principles of Database Systems Course Overview Chengyu Sun California State University, Los Angeles

Database Management Systems (DBMS) Queries DBMS Results • Efficient • ACID • Standard Data

Database Management Systems (DBMS) Queries Results Query Compiler and Optimizer Execution Engine Index and File Manager Transaction and Concurrency Control Buffer Manager Disk Manager index files data files system catalog Logging and Recovery

Databases DB DBMS DB DB Schema elements (objects) • Relations (tables) • Views, indexes • Procedures and triggers • … Schema Database

Database on CS 3 Server Oracle CS 422 STU 01 SCOTT CS 422 STU 02 HR SH CS 422 STU 31 ORC

Database Courses Course Audience Coverage CS 122 Database Users SQL CS 422 Database Developers w. Design w. Implementation w. Query w. Application development w. Performance Tuning CS 522 DBMS Developers DBMS implementation

Database Development Problem Schema Design Tables Attributes Constraints Database Implementation Query Application Development Client Applications • • • Indexes Views Procedures Triggers Privileges … Performance Tuning

Schema Design Data modeling n n n Entity-Relationship (ER) model Relational model Object-oriented model Conversion between models n n ER diagram relational model Object-Relational Mapping (ORM) Normalization

Data Modeling Example Some Restaurant Terminal ID: NC 2 HHRY Merchant ID: 4492414532566624 VISA ******1234 srv: 1 SALE inv: 000032 Batch: 000244 Date: JUN 17, 06 Time: 18: 44 AUTH: 00559 B Base: $36. 70 Tip: Total: Chengyu Sun

Database Implementation Data Definition Language (DDL) n CREATE, DROP, ALTER Authorization n GRANT, REVOKE PL/SQL and Java n n n Functions Procedures Triggers

Query Languages Relational Algebra Datalog Data Manipulation Language (DML) n n n SELECT … FROM … WHERE Conditional queries Subqueries Transactions …

Recursion Example A B Edges P 1 C E D P 2 A B B C C D B E Is there a path from C to E?

Application Development Embedded SQL JDBC Hibernate

Query Tuning Background knowledge Execution plan n View Interpret Control Common tips and techniques