Share
Embed
CSE360 Flipped Lecture 11
Introduction to Software Engineering
Databases
(202011)
Javier Gonzalez-Sanchez PRO
June 11, 2020
Transcript
25 slides
-
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). -
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 | 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 | 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.