Data Access Programming

Data Access Programming Relational Database Management System (RDBMS) • software system used to manage a relational database; typical modern-day RDBMSs provide features for, among others: o database creation o querying and manipulation of data o user accounts management o data backup, restore, import, export facilities o report generation o database application development o storage management o performance tuning • e.g., MS Access, MySQL, Oracle, MS SQL Server, Java DB, DB2, etc. Database • collection of data used by an application • data is logically organized into tables (relations), which consists of data rows and columns o data row (a.k.a. record or tuple) o data columns (a.k.a., fields or attributes) • access to (and uniqueness of) data rows are facilitated through primary keys • associations among related tables are facilitated through foreign keys Database Schema • notation used to describe the structure of a database • e.g.: COURSES(course, description, years) STUDENTS(idno, stname, course, yr) FK course REF COURSES SUBJECTS(codeno, description, units, schedule, fee) ENROLL(idno, codeno) FK idno REF STUDENTS codeno REF SUBJECTS Structured Query Language (SQL) • language used to manipulate the data in a relational database • used to create SQL queries, typically for data retrieval or manipulation • SELECT Queries o SELECT * FROM students; o SELECT idno, stname FROM students; o SELECT idno, stname FROM students WHERE course = ”BSIT”; o SELECT idno, stname FROM students WHERE course = ”BSMATH” AND (yr = 2 OR yr = 3); o SELECT stname FROM students ORDER BY stname; o SELECT stname FROM students ORDER BY stname DESC; o SELECT UCase(stname) FROM students; o SELECT description, fee * 0.25 AS discount FROM subjects; o SELECT stname, courses.course, description FROM courses, students WHERE courses.course = students.course ORDER BY course ASC, stname DESC; o SELECT students.idno, stname, Sum(fee) AS totalfee FROM students, enroll, subjects WHERE students.idno = enroll.idno AND enroll.codeno = subjects.codeno GROUP BY students.idno, stname ORDER BY stname; • Data Manipulation Language (DML) Statements o INSERT INTO students VALUES(1234, ”Erap”, ”BSIT”, 2); o INSERT INTO secondyears SELECT * FROM students WHERE yr = 2; o UPDATE students SET yr = yr + 1 WHERE yr < 4; o UPDATE students SET course = ”BSIM”, yr = 1 WHERE idno = 1234; o DELETE * FROM students; o DELETE * FROM students WHERE idno = 1234; • Data Definition Language (DDL) Statements o CREATE, DROP, RENAME, etc. • Transaction Control Statements o COMMIT, ROLLBACK, etc.