1 / 15

Database application development

Database application development. INF08104: Database Systems Brian Davison , 2013/14. Agenda. Objects and relations Embedded SQL operations Interoperability. The role of databases. A specialist component in a ranger system Support for multiple concurrent users

suki
Download Presentation

Database application development

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database application development INF08104: Database Systems Brian Davison, 2013/14

  2. Agenda • Objects and relations • Embedded SQL operations • Interoperability

  3. The role of databases • A specialist component in a ranger system • Support for multiple concurrent users • Fine-grained access rights model • Secure and reliable backup facilities • Portability across multiple platforms (DBMS and operating systems) http://netbeans.org

  4. Objects and relations

  5. UML class diagrams

  6. Mapping options • Single table Multiple tables  • Issues • Many null values • Loss of generality

  7. Restoring generality using views CREATE VIEW all_programmes AS SELECT title, null, null, nullFROM   TV_programmeUNIONSELECT title, number_of_episodes,       null, nullFROM   seriesUNIONSELECT title, null, subject, nullFROM   factualUNIONSELECT title, null, subject, periodFROM   history_documentary

  8. Embedded SQL • Typical process • Connect • Prepare statement • Execute statement • Loop over results • Close connection • Examples using PHP and MySQL

  9. Database connection $db_host = "server_1";$username = "hr";$password = "hr";// Connect to MySQLmysql_connect($db_host, $username, $password);

  10. Constructing and sending a query $query  = "SELECT employee_id, first_name, last_name ";$query .= "FROM employees ";$query .= "WHERE department_id = " . $deptId . " ";$query .= "ORDER BY last_name";$result = mysql_query($query);

  11. Processing query results echo "<table>";while ($loc = mysql_fetch_assoc($result)) {   echo "<tr>";   echo "<td>" . $loc{employee_id} . "</td>";   echo "<td>" . $loc{first_name} . "</td>";   echo "<td>" . $loc{last_name} . "</td>";   echo "</tr>";}echo "</table>";

  12. Issues • Null values • Error handling $result = mysql_query($query) or    die("Unable to retrieve employee information: " . mysql_error());

  13. DML $query  = "UPDATE employees "; $query .= "SET commission_pct = " . $newCommission . " "; $query .= "WHERE employee_id = " . $empId; $result = mysql_query($query) or           die("Unable to update commission: " . mysql_error()); echo mysql_affected_rows() . " row(s) updated";

  14. Closing the database connection mysql_close();

  15. Interoperability • ODBC • Connectivity for desktop tools • Practical exercise this week • XML

More Related