Upgrade to Pro — share decks privately, control downloads, hide ads and more …

CSE360 Flipped Lecture 11

CSE360 Flipped Lecture 11

Introduction to Software Engineering
Databases
(202011)

B546a9b97d993392e4b22b74b99b91fe?s=128

Javier Gonzalez
PRO

June 11, 2020
Tweet

Transcript

  1. CSE 360 Introduction to Software Engineering Lecture 11: Databases Dr.

    Javier Gonzalez-Sanchez javiergs@asu.edu javiergs.engineering.asu.edu | javiergs.com PERALTA 230U Office Hours: By appointment
  2. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 2 Announcements

    (This Week) § Final Project is due November 30 (Tuesday section) § December 2 (Thursday section).
  3. Databases Fundamentals

  4. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 4 Databases

    Model View Controller Database Persistence Business Presentation Client Server
  5. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 5 Database

    Management System • Sometimes called Information Management System • Software system designed to facilitate the storage, organization and retrieval of information. // Java Class.forName("com.mysql.jdbc.Driver"); static final String DB_URL = "jdbc:mysql://localhost/ASU"; DriverManager.getConnection(DB_URL,USER,PASS); Statement stmt = conn.createStatement(); String sqlInstruction = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sqlInstruction); while(rs.next()){ // ...
  6. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 6 Categories

    Data Model define the logical structure of a database (entities, attributes, and relationship among data). Examples: • Relational Model (SQL Databases), • Object-Based, Document-Oriented, Key-value, Graph-Based Model, etc. (NoSQL databases)
  7. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 7 Relational

    Model
  8. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 8 Relational

    Model Relational databases provide safe and scalable storage for millions of websites and mobile applications.
  9. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 9 Relational

    Model § two-dimensional) tables – like an Excel spreadsheet, § with a fixed number of named columns (attributes) and § any number of rows of data (records) § Usually, tables have an identifier (a column) called primary key. § The relationships between data values is stored in the form of a table as well.
  10. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 10 Relational

    Mapping Mapping Entity: § Create table for each entity. § Entity’s attributes should become fields of tables with data types. § Declare primary key. Mapping Relationships § Create table for a relationship § Add the primary keys of all participating Entities as fields of table with their respective data types. § If relationship has any attribute, add each attribute as field of table. § Declare a primary key composing all the primary keys of participating entities. § Declare all foreign key constraints.
  11. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 11 Relational

    Model
  12. Databases Structured Query Language (SQL)

  13. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 13 Structured

    Query Language § SQL is a language for relational database. § SQL consists of many types of statements, which are sometimes called (sub)languages. E.g., data definition (DDL) and data manipulation (DML) sublanguages.
  14. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 14 SQL

    Data Definition Language
  15. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 15 SQL

    Data Definition Language
  16. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 16 SQL

    Data Definition Language
  17. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 17 Data

    Manipulation Language Modifies the database instance by inserting, updating and deleting its data. Instructions: § INSERT INTO table (column1[, column2…]) VALUES (value1[,value2…]) § UPDATE table_name SET column_name=value [WHERE condition] § DELETE FROM table_name [WHERE condition] § SELECT column1[, column2…] FROM table [WHERE condition]
  18. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 18 Data

    Manipulation Language
  19. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 19 Data

    Manipulation Language
  20. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 20 Data

    Manipulation Language SELECT column1, column2, … FROM table [WHERE condition1 AND/OR condition2 AND/OR … ] [ORDER BY column ASC/DESC];
  21. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 21 Data

    Manipulation Language
  22. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 22 Data

    Manipulation Language
  23. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 23 Data

    Manipulation Language
  24. Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 24 SQL

    Exercise http://sqlfiddle.com
  25. CSE360 – Introduction to Software Engineering Javier Gonzalez-Sanchez javiergs@asu.edu Summer

    2020 Disclaimer. These slides can only be used as study material for the class CSE360 at ASU. They cannot be distributed or used for another purpose.