Database Design, SQL and JDBC

1
Database Design, SQL and JDBC

• Overview
• Objectives of this Lecture
• Logical Database Models
• ER Model, Hierarchical and Network data Model and
  Relational Model
• JDBC
• SQL
• Preview More on JDBC

2
Objectives of this Lecture

• Understand the use of data modeling
• Understand the different types of logical data
  models
• Learn about Relational Data Model in detail
• Understand the concept of JDBC
• Learn about SQL

3
Why to model data?

• A model highlights the important aspects of a
  subject and obscures unimportant aspects of it.
• A data model emphasizes features of interest to
  the user and makes its interaction with a DBMS
  transparent.
• Physical data models show how the data structures
  are organized so that their resources are
  optimized.
• Logical data models interprete the data in the
  context of the application.
• The modeling phase of database design is crucial
  to assure an accurate representation of the
  user's perspective of the business.

4
Logical Data Models

• A logical data model is a design tool used in the
  design of a database system to emphasize features
  of interest to the user and makes interaction
  with a DBMS transparent.
• A good logical data model is independent of the
  DBMS and can be moved from one management system
  to another.
• Popular approaches to logical data models
  include
• 1. Entity-Relationship model
• 2. Hierarchical and Network models.
• 3. Relational model.
• Next we briefly discuss the Relational model.

5
Relational Model

• The most popular logical data model in use today
  is the relational model which is noted for its
  consistency, simplicity and data independence.
• It is composed of relations, attributes, domains,
  keys, tuples and their representations.
• A relation is a table of rows and columns. Each
  column of the table is an attribute.
• The domain of each attribute is the collection of
  values that can be assigned to a particular
  attribute.
• A principal key is one or more attribute values
  that uniquely identify an entity instance.
• A tuple is an ordered sequence of elements.
  Example the sequence (p,q,r,s,t,u) is a 6-tuple.
  Each row is an entity instance represented by a
  tuple.

6
Components of Relational Model

• Relational modeling has many interchangeable
  terms for its components as shown below
• Relation, table, fileA two-dimensional table
  consisting of columns and rows created from the
  entities of the object model.
• Attribute, column, fieldThe columns of the
  table, usually defined from the attributes of the
  object model.
• Tuple, row, recordThe rows of the table derived
  from the entity occurrences of the object model.
• Relations
• A Relation is a two-dimensional table containing
  a set of related data.
• The true requirements of a relation are that
• Each cell must be atomic (contain only one
  value).
• Each attribute contains the same type of physical
  and semantic information in all its cells.
• Each tuple is unique there can be no duplicate
  rows of data.
• The order of columns and rows is not significant.
  

7
Relation

• Typically, relations have a representation
  consisting the relation name, followed by a list
  of attributes in form of a tuple with the
  principal key highlighted.
• e.g., ADVISOR (AdvId, Adv-Name, Adv-Phone)
• The following shows a typical relational data
  model called an instance table.
• RELATION ADVISOR primary key AdvId

8
Relations (cont.)

• The following table defines another relation for
  the entity Student with attributes StuID,
  StuName, StuPhone and AdvId.
• RELATION STUDENT
• In relational databases, new relations can be
  generated from others. For example, the relation
  is advised by can be defined between the
  relations STUDENT and ADVISOR.

StuId StuName StuPhone AdvId
987654 Al-Amer 452-7634 66104
978956 Al-Quasim 555-3215 66102
993421 Al-Ali 865-3476 66101
981267 Al-Fathah 894-2384 66102
9
Definitions of Relational Terms

• Primary keyPrimary keys are essential in
  relational modeling one should be specified for
  each relation. A primary key uniquely identifies
  a record (or row) in a table in other words, a
  particular primary key value returns a record
  that is identical to no other. A primary key is
  composed of one column (simple primary key) or a
  combination of columns (composite primary keys)
  that provide this unique identification. The best
  possibilities for primary keys are attributes
  that seldom change and are familiar to users. The
  primary key should contain the fewest columns
  needed to uniquely identify a record.
• Simple primary key
• Consider the relation ADVISOR where each value of
  Advisor ID returns a unique record. Simple
  primary key AdvId uniquely identifies records.

10
Definitions of Relational Terms

• Composite primary key
• Consider the relation STUDENT where each student
  can take more than one course. Student ID, Class,
  or Grade alone does not return a unique record
  however, a composite primary key of Student
  ID-Class does. Composite primary key Student
  ID-Course defines unique records.
• Foreign keyA foreign key is an attribute in a
  relation that is also a primary key in another
  relation. This foreign key-primary key match
  allows references between relations in order to
  locate information.

11
Definitions of Relational Terms

• Foreign key Example
• Relation ADVISOR where AdvID is the primary key.
• Relation STUDENT where StuID is the primary key
  and AdvID is a foreign key.
• RELATION ADVISOR RELATION STUDENT

StuId StuName StuPhone AdvId
987654 Al-Amer 452-7634 66104
978956 Al-Quasim 555-3215 66102
993421 Al-Ali 865-3476 66101
981267 Al-Helal 894-2384 66102
12
Communication with DB from Java Application

• To communicate with the DB from Java application,
  we need the following.
• An interface which connects Java application and
  the DBMS (JDBC - Drivers)
• A language to communicate (SQL Structured Query
  Language)
• JDBC
• JDBCTM API provides universal data access from
  the Java programming language. The JDBC API is a
  Java API for accessing virtually any kind of
  tabular data from relational databases.
• To use the JDBC API with a particular database
  management system, you need a JDBC
  technology-based driver to mediate between JDBC
  technology and the database. The latest Java SDK
  includes a JDBC-ODBC Bridge driver that makes
  most Open Database Connectivity (ODBC) drivers
  available to programmers using the JDBC API.

MS Access DBMS Engine
DB
Java Application
JDBC-ODBC Bridge Driver
Sending query
Result of the query
13
Communication with DB from Java Application

• SQL
• Structured Query Language is used to write
  queries to the database in order to get
  information from the database and to update the
  information to the DB
• Here we will see some simple queries their
  format and some simple examples
• SELECT
• UPDATE
• INSERT
• SELECT
• Format
• SELECT ltLIST OF COLUMNSgt FROM ltLIST OF TABLESgt
  WHERE ltCONDITION(S)gt GROUP BY ltGROUPING
  COLUMN(S)gt ORDER BY ltORDERING COLUMN(S)gt
• Explanation
• For ltLIST OF COLUMNSgt
• a is used to select all the columns of the
  specified table(s).
• Individual columns can be selected by specifying
  the column names separated by comas.
• If columns from different table are needed, then
  tablename.columnname is specified.

14
Communication with DB from Java Application

• For ltLIST OF TABLESgt
• A table name or list of table name is used
• For ltCONDITION(S)gt
• The operators are relational and logical and the
  operands are the column names
• For ltGROUPING COLUMN(S)gt and ltORDERING
  COLUMN(S)gt
• List of columns are specified
• Example
• SELECT StuId, StuName FROM STUDENT WHERE AdvId
  66102
• The table Result of the query
• 978956 Al-Quasim
• 981267 Al-Helal

StuId StuName StuPhone AdvId
987654 Al-Amer 452-7634 66104
978956 Al-Quasim 555-3215 66102
993421 Al-Ali 865-3476 66101
981267 Al-Helal 894-2384 66102
15
Communication with DB from Java Application

• UPDATE
• Format
• UPDATE ltTABLE NAMEgt SET ltCOLUMN NAMEgt ltVALUEgt
  WHERE ltCONDITIONgt --if the Where clause is
  left out, all rows will be updated according to
  the Set statement.
• Example
• UPDATE STUDENT SET AdvId 66109 WHERE StuId
  993421
• The table before update The table after update

StuId StuName StuPhone AdvId
987654 Al-Amer 452-7634 66104
978956 Al-Quasim 555-3215 66102
993421 Al-Ali 865-3476 66109
981267 Al-Helal 894-2384 66102
StuId StuName StuPhone AdvId
987654 Al-Amer 452-7634 66104
978956 Al-Quasim 555-3215 66102
993421 Al-Ali 865-3476 66101
981267 Al-Helal 894-2384 66102
16
Communication with DB from Java Application

• INSERT
• Format
• INSERT INTO ltTABLE NAMEgt (ltCOLUMN LISTgt)
  VALUES (ltVALUE LISTgt)
• Example
• INSERT INTO STUDENT VALUES (994433,Al-Ghamdi,
  866-2687,66105)
• The table before insert The table after insert

StuId StuName StuPhone AdvId
987654 Al-Amer 452-7634 66104
978956 Al-Quasim 555-3215 66102
993421 Al-Ali 865-3476 66101
981267 Al-Helal 894-2384 66102
StuId StuName StuPhone AdvId
987654 Al-Amer 452-7634 66104
978956 Al-Quasim 555-3215 66102
993421 Al-Ali 865-3476 66109
981267 Al-Helal 894-2384 66102
994433 Al-Ghamdi 866-2687 66105