1 / 16

Introduction to Databases II - Database Design, SQL and JDBC

Introduction to Databases II - 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: Data Communications and Networks. Objectives of this Lecture.

cruz-harmon
Download Presentation

Introduction to Databases II - Database Design, SQL and JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Databases II - 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: Data Communications and Networks Lecture 28

  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 Lecture 28

  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. Lecture 28

  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. Lecture 28

  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. Lecture 28

  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. Lecture 28

  7. Relation AdvId Adv Name Adv Phone 66101 Sahalu 333-2111 66102 Al-Ghamdi 405-8888 66103 Sadiq 501-8241 66104 Badhusha 222-2357 • 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 Lecture 28

  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. Lecture 28

  9. Definitions of Relational Terms AdvId AdvName AdvPhone 66101 Sahalu 333-2111 66102 Al-Ghamdi 405-8888 66103 Sadiq 501-8241 66104 Badhusha 222-2357 • 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. Lecture 28

  10. Definitions of Relational Terms StudentID Course Grade 1001 COE312 95 1001 ENG101 90 1005 COE312 85 1006 COE430 87 1006 ICS202 95 1010 COE102 92 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. Lecture 28

  11. Definitions of Relational Terms AdvId Adv Name Adv Phone 66101 Sahalu 333-2111 66102 Al-Ghamdi 405-8888 66103 Sadiq 501-8241 66104 Badhusha 222-2357 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 Lecture 28

  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 Lecture 28

  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 <LIST OF COLUMNS> FROM <LIST OF TABLES> [WHERE <CONDITION(S)>] [GROUP BY <GROUPING COLUMN(S)>] [ORDER BY <ORDERING COLUMN(S)>] • Explanation: • For <LIST OF COLUMNS> • 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. Lecture 28

  14. Communication with DB from Java Application • For <LIST OF TABLES> • A table name or list of table name is used • For <CONDITION(S)> • The operators are relational and logical and the operands are the column names • For <GROUPING COLUMN(S)> and <ORDERING COLUMN(S)>] • List of columns are specified • Example: • SELECT StuId, StuName FROM STUDENT WHERE AdvId = “66102” • The tableResult of the query 978956 Al-Quasim 981267 Al-Helal Lecture 28

  15. Communication with DB from Java Application • UPDATE • Format • UPDATE <TABLE NAME> SET <COLUMN NAME> = <VALUE> [WHERE <CONDITION>]; --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 Lecture 28

  16. Communication with DB from Java Application • INSERT • Format • INSERT INTO <TABLE NAME> [(<COLUMN LIST>)] VALUES (<VALUE LIST>); • Example • INSERT INTO STUDENT VALUES (“994433”,”Al-Ghamdi”,”866-2687”,”66105”) • The table before insert The table after insert Lecture 28

More Related