12 Data Storage and Management DKUMUST Mobile ICT

12. Data Storage and Management DKU-MUST Mobile ICT Education Center

Goal • Learn the basic concepts of database. • Learn how to use the SQLite. • App development using SQLite. • Learn how to use the SQLite GUI Tool. Page 2

1. SQLite Basic ▶ Basic concepts of database v Overview the SQLite ü SQLite is a relational database management system contained in a small C programming library. ü In contrast to other database management systems, SQLite is not a separate process that is accessed from the client application, but an integral part of it. ü SQLite is ACID-compliant and implements most of the SQL standard, using a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity. ü SQLite is a popular choice as embedded database for local/client storage in application software such as web browsers. ü It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems, among others. ü SQLite has many bindings to programming languages. Page 3

1. SQLite Basic ▶ Basic concepts of database v Database definition ü A database is an organized collection of data. The data is typically organized to model relevant aspects of reality(for example, the availability of rooms in hotels), in a way that supports processes requiring this information(for example, finding a hotel with vacancies). ü Database management systems (DBMSs) are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data. ü A general-purpose database management system(DBMS) is a software system designed to allow the definition, creation, querying, update, and administration of databases. Well-known DBMSs include My. SQL, Postgre. SQL, SQLite, Microsoft SQL Server, Microsoft Access, Oracle, SAP, d. BASE, Fox. Pro, and IBM DB 2. A database is not generally portable across different DBMS, but different DBMSs can inter-operate by using standards such as SQL and ODBC or JDBC to allow a single application to work with more than one database. Page 4

1. SQLite Basic ▶ Basic concepts of database v Relational Database ü A relational database is a database that has a collection of tables of data items, all of which is formally described and organized according to the relational model. The term is in contrast to only one table as the database, and in contrast to other models which also have many tables in one database. ü In the relational model, each table schema must identify a primary column used for identifying a row called the primary key. Tables can relate by using a foreign key that points to the primary key. The relational model offers various levels of refinement of the table relations called database normalization. (See Normalization below. ) The database management system (DBMS) of a relational database is called an RDBMS, and is the software of a relational database. ü The relational database was first defined in June 1970 by Edgar Codd, of IBM's San Jose Research Laboratory. Codd's view of what qualifies as an RDBMS is summarized in Codd's 12 rules. A relational database has become the predominant choice in storing data. Other models besides the relational model include the hierarchical database model and the network model. Page 5

1. SQLite Basic ▶ Basic concepts of database v Database-related terms A Database B Database Naver Database Column Name Table Member Table B Table Row C Table Column Page 6

1. SQLite Basic ▶ Build a database in SQLite v Steps to build the database in the SQLite Step. 1 Step. 2 Step. 3 Install the DBMS (Already Installed) Building the Database Using the data that built in application ① Create the Database ↓ ② Create the Table ↓ ③ Input the Data ↓ ④ Select/Using the Data Page 7

1. SQLite Basic ▶ Build a database in SQLite [Practice 12 -1] To build a database (1/7) v To build a Database ü Build a database in SQLite ü Access the command prompt v Project Info. ü Project Name : Project 12_1 ü Package Name : com. cookandroid. project 12_1 ü build SDK : Goolge API 15 or 16 ü Minimum Required SDK : API 15 or 16 ü Activity Name : Project 12_1 Activity ü Layout Name : main ü Title : Project 12_1 Page 8 First, Run the project Because AVD is Running 이번 실습은 프로젝트를 실행해서 우선 AVD를 가동해야 한다.

1. SQLite Basic ▶ Build a database in SQLite [Practice 12 -1] To build a database (2/7) v Run the adb. exe ü Run the command prompt → Run the adb. exe ü Adb. exe path is “androidsdkplatform-tools” Page 9

1. SQLite Basic ▶ Build a database in SQLite [Practice 12 -1] To build a database (3/7) v Ready access to SQLite Page 10

1. SQLite Basic ▶ Build a database in SQLite [Practice 12 -1] To build a database (4/7) v ① Create a Database Page 11

1. SQLite Basic ▶ Build a database in SQLite [Practice 12 -1] To build a database (5/7) v ② Create a table Page 12

1. SQLite Basic ▶ Build a database in SQLite [Practice 12 -1] To build a database (6/7) v ③ Input the Data Page 13

1. SQLite Basic ▶ Build a database in SQLite [Practice 12 -1] To build a database (7/7) v ④ Using and select… the data Page 14

1. SQLite Basic ▶ Build a database in SQLite [Self Test 12 -1] v Create the my. DB(Database), and Create the table Page 15 Product Name Price Date of Manufacture Manufacturing Company Remaining quantity TV 100 2012. 07. 22. Samsung 55 Computer 150 2013. 05. LG 7 Monitor 50 2014. 09. Daewoo 33 Printer 80 2013. 05. 20. HP 24

2. Using the SQLite ▶ SQLite Programming v SQLite behavior for the development of Android App ü Using the SQLite. Open. Helper Class, SQLite. Database Class, Cursor Interface Create the Database Create the Table SQLite Database Inherit override Execution of the SQL Query Move the Cursor Page 16 SQLite. Open. Helper Class SQLite. Database Class Cursor Interface

2. Using the SQLite ▶ SQLite Programming v Mainly used the method in each class Class or Interface SQLite. Open. Helper Class SQLite. Database Class Cursor Interface Page 17 Method Main purpose Constructor Create the Database on. Create() Create the Table on. Upgrade() Delete the Table and recreate get. Readable. Database() Open read-only DB, return the SQLite. Database get. Writable. Database() Open for reading and writing DB, Return the SQLite. Database exec. SQL() Execute the SQL Query(Insert, Update, Delete) close() Close the DB Query(), raw. Query() After running, the select cursor returns move. To. First() Move to the first row of the cursor move. To. Last() Move to the last row of the cursor move. To. Next() Move to the next row of the cursor

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (1/9) v Create the singer name DB management App ü Insert and Select the name of Singer group in the Database v Project Info. ü Project Name : Project 12_2 ü Package Name : com. cookandroid. project 12_2 ü build SDK : Goolge API 15 or 16 ü Minimum Required SDK : API 15 or 16 ü Activity Name : Project 12_2 Activity ü Layout Name : main ü Title : Project 12_2 Page 18

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (2/9) v Design the Screen ü Linear. Layou 1 : 1 Text. View, 1 Edit. Text(edt. Name) ü Linear. Layou 2 : 1 Text. View, 1 Edit. Text(edt. Number) ü Linear. Layou 3 : 3 Button(btn. Init, btn. Insert, btn. Select) ü Linear. Layou 4 : 2 Edit. Text(edt. Name. Result, edt. Number. Result) Page 19

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (3/9) v Java Coding 1 ü Define a class that inherits from SQLite. Open. Helper class ü Modify the constructor Page 20

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (4/9) v Java Coding 2 : Coding the on. Create( ) and on. Upgrade( ) method of the my. DBHelper class ü on. Create( ) method : Coding the create a table code ü on. Upgrade( ) method : Coding the delete table and recreate table code Page 21

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (5/9) v Java coding 3 : Coding the main Activity class ü The newly created my. DBHelper class variables / Four variables respond to Edit. Text ü The three variables respond to the Button / SQLite. Database Class variabless Page 22

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (6/9) v Java coding 4 ü Coding the Listener to run, when you click the <Default> Page 23

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (7/9) v Java coding 5 ü When you click the <input>, Coding the Listener for input the value of the Edit. Text Page 24

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (8/9) v Java Coding 6 ü When you click the <select>, coding the Listener for show the all data in the table. (printed on the bottom of the Edit. Text) Page 25

2. Using the SQLite ▶ SQLite Programming [Practice 12 -2] Create the DB management singer name App (9/9) v Run and the results confirm ü After running the project, enter the required information and confirm the results ü At the command prompt, confirm the contents of the group. DB Page 26

2. Using the SQLite ▶ Using SQLite GUI Tool v SQLite Database Browser (1/2) : Create a database and table Page 28

2. Using the SQLite ▶ Using SQLite GUI Tool v SQLite Database Browser (2/2): Input the data ü 생성한 데이터베이스 파일은 DDMS를 통해 AVD에 넣어서(Push) 사 용할 수 있다. Page 29

2. Using the SQLite ▶ Using SQLite GUI Tool v SQLite Developer Page 31

Summary 1. Database-related terms Data, Table, Database, DBMS, column(column or field), column name, Type of data(data type), row, SQL ü 2. The database type of SQL statements ü Create a table : CREATE TABLE ü Input the data : INSERT INTO ü Update the data : UPDATE ü Delete the data : DELETE FROM ü Select the data : SELECT 3. SQLite Progamming ü Page 32 Using the SQLite. Open. Helper Class, SQLite. Database Class and Cursor Interface

DKU-MUST Mobile ICT Education Center