Introduction to Database Systems: Ready SQLite3 CS312 Create - PowerPoint PPT Presentation

Data Models Types of bases Our FIRST DB in SQLite3 Data to add Introduction to Database Systems: Ready SQLite3 CS312 Create table Schema A Small Database System Consider this... Oliver Bonham-Carter 4 Sept 2020 1 / 16

All types of data! Data Models Types of bases Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... 1 / 16

A database, simply stated Data Models Types of bases Relational Models Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... The entire database fits into one table. Is the column “Dept” necessary in this table? 2 / 16

A database, not-so-simply stated Data Models Types of bases Relational Models Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... The entire database is made up of many tables. A table must be connected to the others in some way . 3 / 16

Relational Models: A single table Data Models Types of bases Relational Models Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... Each field of a row is an “observation” Rows are a series (i.e., tuples) of “observations” Columns contain same “observation” class (are called attributes ) 4 / 16

Specific information for each table Data Models Types of bases Relational Models Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... Two tables containing specific types of data 5 / 16

Specific information for each table Data Models Types of bases Relational Models Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... Two tables containing specific types of data, using the same ID on a row Each table organizes non-redundant information, but needs a way to connect a row to the rest of the base (i.e., the common ID column serves as a primary key). 6 / 16

We’ve got it! Data Models Types of bases Relational Models Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... Let’s build a small-sized database using SQLite3!! 7 / 16

SQL is... Data Models Types of bases Relational Models Our FIRST DB in SQLite3 Data to add Ready SQLite3 Pronounced “ess-que-el” stands for Structured Query Create table Language . Schema Used to communicate with a database. Consider this... According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. The standard computer language for relational database management and data manipulation. Used to query, insert, update and modify data 8 / 16

SQLite3 A practical open source database Data Models Types of bases Relational Models Command Our FIRST DB in $sqlite3 SQLite3 Data to add Ready You should see this, or similar: SQLite3 SQLite version 3.19.3 2017-06-27 16:48:08 Create table Schema Enter ”.help” for usage hints. Consider Connected to a transient in-memory database. this... Use ”.open FILENAME” to reopen on a persistent database. sqlite > 9 / 16

We are going to build this database Data Models Types of bases Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... Our database will contain this same ordering of data 10 / 16

Data and its Schema We need to tell SQLite3 where to contain the data Data Models Types of bases Our FIRST DB in SQLite3 Data Data to add Only three columns in our base: Ready SQLite3 ID: up to four chars in size 1 Dept: up to four chars Create table 2 RoomNum: up to 3 chars Schema 3 Consider this... Plenty of space for as many rows as we want: Limited by memory 1 11 / 16

Running an SQLite client Data Models Ways to run SQLite for this demo Types of bases Download and install a local version; Our FIRST See supplemental slides about Tools DB in SQLite3 Use an online tool (shown below) Data to add See sqliteonline at https://sqliteonline.com/ Ready SQLite3 Use Docker solution: Create table See sandbox/ for Dockerfile and execution bash script Schema Consider this... 12 / 16

Make a General Table Data Models Types of bases Our FIRST Pseudo code DB in SQLite3 CREATE TABLE table_name ( Data to add column1 datatype, Ready SQLite3 column2 datatype, Create table column3 datatype Schema ... Consider ); this... This data structure allocates the memory space for the database to keep data that is assigned to this table. 13 / 16

Schema Create a table for the DB Data Models Create database called dept.sqlite3 Types of bases $sqlite3 dept.sqlite3 Our FIRST DB in SQLite3 CREATE TABLE department( Data to add Ready ID varchar(4), SQLite3 Dept varchar(4), Create table RoomNum varchar(3) ); Schema Consider this... We create a table called department to contain our data In fact, we have created a memory space for this task Note: attribute 1 VARCHAR( n ) Declaration VARCHAR of size ( n ) to contain attribute 1 . VARCHAR guesses the datatype of the attribute: is it a string or an int ? 14 / 16

After table is created Add the data Data Models Check that the table has been created Types of sqlite> .tables bases department Our FIRST DB in SQLite3 Data to add Insert some data as a tuple Ready INSERT INTO department VALUES ( SQLite3 "OBC", Create table "CS", Schema Consider "104" ); this... Query everything in the table, department sqlite> select * from department; OBC|CS|104 Exit and save your database .exit 15 / 16

Consider this... Data Models Types of bases Our FIRST DB in SQLite3 Data to add Ready SQLite3 Create table Schema Consider this... Can you add and populate a new database? Can you populate your base by adding more data? Can you also check that the data was correctly added? 16 / 16