Introduction to Relational Database and SQL Minder Chen CSU Channel Islands Minder Chen CSUCI EDU What is a RDBMS A Data Base DB is an integrated collection of shared data designed to meet the varied information needs of an organization A Data Base Management System DBMS consists of a collection of software program that receives and satisfies all requests for data A Relational Data Base Management System RDBMS is a DBMS which processes data with the Relational Data Model Minder Chen 1996 2011 RDBMS SQL 2 History of RDBMS The relational database model was originally developed by Dr E F Codd in the early 1970s The SQL language was originally developed by IBM in a prototype relational database management system System R in the mid 1970s The original SQL language SEQUEL 2 was described in IBM Journal of R D November 1976 In 1979 Oracle Corporation introduced the first commercially available implementation of SQL SQL has also been implemented in IBM s DB2 and SQL DS database systems in the mid 1980s Today SQL is widely implemented and is accepted as the industry standard database access language Minder Chen 1996 2011 RDBMS SQL 3 Database Technology Before Relational systems people has been using File Systems like ISAM VSAM and B Tree Hierarchy Database Network Database RDBMS has both a physical and a logical structure By doing so the physical storage of data can be managed without affecting the access to logical storage structures File Systems Minder Chen 1996 2011 Hierarchy Network Relational RDBMS SQL 4 Relational Database Introduction A relational data base is perceived by its users as a collection of tables Invented by E F Codd in 1969 Dominate the markets since late 1980s Strengths Simplicity End user orientation Standardization SQL Structured Query Language Value based instead of pointer based to provide data independence Endorsed by major computer companies Most CASE products support the development of relational data base centered applications Minder Chen 1996 2011 RDBMS SQL 5 SQL Terminology Product Product Table Table Row Set Theory Relation Attribute Tuple Domain Column p no name quantity price 101 Color TV 24 500 201 B W TV 10 250 202 PC 5 2000 Relational DB Table Column Row Data Type or Domain Minder Chen 1996 2011 File File Data item Record Data type Example Product table Product name Product 101 s info DATE RDBMS SQL 6 SQL Terminology CREATE TABLE PRODUCT p no name quantity price CHAR 5 NOT NULL CHAR 20 SMALLINT DECIMAL 10 2 INSERT INTO PRODUCT VALUES 101 Color TV 24 500 INSERT INTO PRODUCT VALUES 201 B W TV 24 500 INSERT INTO PRODUCT VALUES 202 PC 5 2000 SELECT p no name price FROM PRODUCT WHERE PRICE 1000 p no 202 name PC Minder Chen 1996 2011 price 2000 In In Access Access CREATE TABLE PRODUCT p no CHAR 5 NOT NULL Name CHAR 20 Quantity INTEGER Price Currency CONSTRAINT pk product PRIMARY KEY p no RDBMS SQL 7 SQL Data Manipulation Language DML SELECT SELECT UPDATE UPDATE INSERT INSERT DELETE DELETE p no name quantity price 101 201 202 Color TV B W TV PC 24 10 5 500 250 2000 SELECT p no name price FROM PRODUCT WHERE PRICE 1000 ORDER BY PRICE DESC Minder Chen 1996 2011 p no 101 201 name Color TV B W TV price 500 250 RDBMS SQL 8 SQL Principles The result of a SQL query is always in a table like format and is not stored View or Dynamic Table The definition of a query can be stored Rows in a table are considered to be unordered Columns sequence in a table is irrelevant in your database queries Can be used in interactive programming environments Provide both data definition language DDL data manipulation language DML and data control language DCL It is a non procedural language Can be embedded in 3GL third generation languages such as C COBOL etc Embedded SQL Embedding SQL statements in host languages Dynamic SQL Constructing SQL statements dynamically Minder Chen 1996 2011 RDBMS SQL 9 Database Application in the Client Server Environments Customer Management CUI client Networks Name John Doe Address 101 Dalmation Street Salary 43 000 Age 34 PF1 Help PF2 Add PF3 Update Enter details and press PF3 to update File GUI client Edit Customer Management Window Help Name Address John Doe 101 Dalmation St Salary 48 000 Age 34 Loan History Update Minder Chen 1996 2011 Cancel Database Server Disks Files RDBMS SQL 10 Database Server in the Client Server Architecture Server Machine Client Machine 1 User 5 2 Client Application 4 3 Database Server 3 File Server 1 User enters query through client application s user interface 2 Client application sends the formulated query to the data base server 3 Database server processes request Search all records 4 Database server returns only the results from the query 5 Client application s user interface displays retrieved information Mechanism Mechanism SQL via SQL Net ODBC Stored Procedures Remote Procedure Calls Access is not a database server Minder Chen 1996 2011 RDBMS SQL 11 Software Layers Using Oracle as Example Custom build Oracle Database Applications Developer 2000 Forms Reports Graphics JDeveloper Procedure Builder SQL Plus PL SQ SQL Designer 2000 L Oracle Server Operating Systems Data Visual Web Developer Visual Studio NET Minder Chen 1996 2011 RDBMS SQL 12 Users Na ve users tellers agents etc Application programmers Application interface Application programs object code Transaction manager Sophisticated users 3GL 4GL Embedded DML precompiler Query tools Database schema DML DDL precompiler interpreter Query evaluation engine Buffer manager Indices Data files DBMS Query processor Storage manager File manager 3GL Third Generation Languages such as C and COBOL 4GL Fourth Generation Language such as Visual Basic and PowerBuilder Database Administrator Statistical data Data dictionary Disk storage Database Structures The database structures should be understood in different views at different stages or for different viewers In 1974 the following conceptual framework was introduced in a conference at New Orleans city so called New Orleans Schema It is also known as ANSI X3 SPARC DBMS Framework External level Individual User View Application Schema 1 Conceptual Schema Conceptual View e g E R Diagram Community User View Internal Level Storage View Minder Chen 1996 2011 Application Schema N Logical Schema Physical Schema Logical Database Physical Database E R Diagram Entity Relationship Diagram RDBMS SQL 14 Benefits of RDBMS Integration of Data Data are organized into a single logical structure with logical relationships defined between
View Full Document
Unlocking...