Data Modeling and Persistence

scacchicgardenΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 10 μήνες)

84 εμφανίσεις

CS 340

Fall 2010

Section 1


Data Model

and

Persistence


Information Model


Conceptual Model


Design Model


Persistence Model


Relational Model

Conceptual Model


True Classes


Sets of Objects


Objects can be members of more than one class


Object migration


Properties determine the class (not the opposite)


Attributes/Relations


Abstraction


Aggregation


Generalization/Specialization

Data Model


Types (not classes)


Instance of class until death


Object is instance of a single type


Type determines properties


Attributes


Inheritance


How does this differ from specialization?


Relations usually turn into double pointers


N
-
ary relations?



Database Models


Relational


Most Common


Mapping to OO
-
Model


For each table


Name (unique within database)


Schema


For each column

»
Attribute name (must be unique)

»
Type

»
Integer, String, Single, Double, Character, Blob

»
Constraints: Primary Key, General Constraint


Object
-
oriented Databases


Implementation Model


Represented in programming language


Precise


Exact


Unless well trained, biases

Persistence


Beginning/Ending of program


Serialization


Database


Object
-
oriented database


Relational database

Relational Database

Review


Database


Contains 1 or more tables (relations)


Tables


Attributes


Name


Type


Tuples


Keys


SQL


I’ve simplified it for us


Syntax Diagram


SQL

Table Creation

Syntax:

<create
-
table
-
statement> ::
-

“create” “table” [“if not exists”] <table
-
name>



“(“ <column
-
def> {“,” <column
-
def>}* “);”

<column
-
def> ::
-


<column
-
name> <type
-
name> <column
-
constraint>*

<type
-
name> ::
-

“text” | “integer” | “long” | “single” | “double”

<column
-
constraint> ::
-


“primary key” | “check <“ <boolean
-
exp> “>” |


“references” <table
-
name>



“(“ <column
-
name>

{“,” <column
-
name”}* “)”

SQL

Drop Table

<drop
-
table
-
stmt>::
-


“drop table” [“if exists”] <table
-
name> “;”

SQL

Insert Statement

<insert
-
stmt>::
-



“insert into “ <table
-
name>



“values (“ <literal
-
value>




{“,” <literal
-
value>}* “);”

<literal
-
value>::
-


<integer
-
literal> | <floating
-
point
-
literal> |


<string
-
literal> | “null”


SQL

Update Statement

<update
-
statement>::
-


“update” <table
-
name>



“set” <set
-
column
-
value>




[“,” <set
-
column
-
value>]*




“where” <column
-
name> “=“





<literal
-
value> “;”

<set
-
column
-
value>::
-


<column
-
name> “=“ <literal
-
value>

SQL

Delete Statement

<delete
-
stmt>::
-


“delete from” <table
-
name> “where” <selectors>

<selectors>::
-


<selector> “and” <selector>}*

<selector>::
-


<column
-
name> “=“ <literal>






SQL

Select Statement

<select
-
stmt>::
-


“select {“*” | <result
-
columns>} “from”



<join
-
source> “where” <select
-
clause> “;”

<result
-
columns>::
-


<column
-
name” {“,” <column
-
name>}*

<join
-
source>::
-


<table
-
name> {“,” <table
-
name}*

<select
-
clause>::
-


<column
-
name> “=“ <literal
-
value>

SQL

Counting Tuples

<Counting
-
Syntax>::
-


“select count(*) from <table
-
name>

Command Line sqlite3


Starting sqlite3


sqlite3 <db
-
name>


Dot
-
commands


.help


.exit


.schema [<table
-
name>]


.tables


When using select queries


headers on


mode columns

JDBC by Example


Tools link


statement.execute(“…”)


ResultSet resultSet =

resultstatement.executeQuery(“…”);


int I = resultSet.getInt(1);


ResultSet reference



import java.sql.*;


public class Test {


public static void main(String[] args) throws Exception {


Class.forName("org.sqlite.JDBC");


Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");


Statement stat = conn.createStatement();


stat.executeUpdate("drop table if exists people;");


stat.executeUpdate("create table people (name, occupation);");


PreparedStatement prep =


conn.prepareStatement( "insert into people values (?, ?);");


prep.setString(1, "Gandhi");


prep.setString(2, "politics");


prep.addBatch();


prep.setString(1, "Turing");


prep.setString(2, "computers");


prep.addBatch();


prep.setString(1, "Wittgenstein");


prep.setString(2, "smartypants");


prep.addBatch();



conn.setAutoCommit(false);


prep.executeBatch();


conn.setAutoCommit(true);



ResultSet rs = stat.executeQuery("select * from people;");


while (rs.next()) {


System.out.println("name = " + rs.getString("name"));


System.out.println("job = " + rs.getString("occupation"));


}


rs.close();


conn.close();


}

}