CSE 360 Introduction to Software Engineering Lecture 11: Databases Dr. Javier Gonzalez-Sanchez [email protected] javiergs.engineering.asu.edu | javiergs.com PERALTA 230U Office Hours: By appointment 

Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 2 Announcements (This Week) § Final Project is due November 30 (Tuesday section) § December 2 (Thursday section). 

Databases Fundamentals 

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

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()){ // ... 

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) 

Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 7 Relational Model 

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

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. 

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. 

Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 11 Relational Model 

Databases Structured Query Language (SQL) 

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. 

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

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

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

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] 

Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 18 Data Manipulation Language 

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

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]; 

Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 21 Data Manipulation Language 

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

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

Javier Gonzalez-Sanchez | CSE360 | Fall 2020 | 24 SQL Exercise http://sqlfiddle.com 

CSE360 – Introduction to Software Engineering Javier Gonzalez-Sanchez [email protected] 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.