Introduction to Relational Database and SQLWhat is a RDBMS?History of RDBMSDatabase TechnologyRelational Database IntroductionSQL TerminologySQL TerminologySQL: Data Manipulation Language (DML)SQL PrinciplesDatabase Application in the Client/Server EnvironmentsDatabase Server in the Client-Server ArchitectureSoftware Layers Using Oracle as ExampleSlide 13Database StructuresBenefits of RDBMSSlide 16Slide 17The Relational ModelSlide 19Slide 20Slide 21Slide 22Slide 23Foreign Keys & Primary Keys in a Sample Database (ACCESS)SQL ProgrammingSlide 26CREATE TABLECreate a Table in AccessBasic ElementsData TypesNULLSlide 32OperatorsPRIMARY KEYFOREIGN KEYSlide 36Sample DatabaseRelationships in Access DatabaseEdit Relationships in AccessDDLSlide 41DMLDCLCREATE TABLE: Data Type in AccessSlide 45Slide 46Slide 47Slide 48Create IndexCreate ViewUsing a ViewSlide 52Slide 53Access DMBS: DMBS vs DatabaseBasic Structures and Contents of a DatabaseRelationships among Access Database ObjectsStakeholders of a Database EnvironmentQuerySELECTSlide 60Create a new query: Choose Create > Query DesignSelect ColumnsSQL Select and Query Design in AccessSlide 64Slide 65Slide 66SELECT with a calculated columnSELECT CriteriaSlide 69SELECT Criteria -- Date formatSlide 71Slide 72Slide 73Slide 74Slide 75Selecting Rows ...LIKE...SELECT Values Within a RangeSelecting Values Within a RangeSELECT ORDER BY: Showing Values in SequenceSELECT: ORDER BY clauseSELECT: ORDER BY column numberTop N RowsSELECT: ORDER BY sort-list (multiple columns)ORDER BY the Position of ColumnsSlide 85SELECT: INSlide 87INSERTINSERT ExamplesAccess Append Query (Insert)INSERT Multiple RowsUPDATE: Modify One or More ColumnsUPDATEDELETE: Deleting One or More RowsFUNCTIONSCOUNTCount()Use Query Design ViewSlide 99MAXMINSUMAVGUse Several Functions TogetherSELECT with Distinct ValuesCount and Distinct Statement LimitationHow to count distinct valuesAccess Report WizardGroup By  Control BreakGroup BySELECT: Grouping RowsGroup By … Having ...SELECT Groups with ConditionsSUBSTR(char, m [,n])TRUNC(n, d)Advanced SQL ProgrammingJoinJOINSlide 119JOIN: Access Query and SQLQualifying Column NamesSlide 122JOIN and Aggregation FunctionExamplesSlide 125Alternative Join SyntaxSlide 127Inner & Outer JoinsLeft Outer JoinOuter JoinMultiway JoinSlide 132Server Generated Sequence NumbersSlide 134Create Forms in AccessCreate a Simple FormForm Layout and StyleSlide 138Detail Form in Datasheet LayoutSlide 140Slide 141Slide 142LOCKSlide 144COMMIT & ROLLBACKIndexing & Performance: Retrieval vs. UpdateAPPENDIX A. Sample Database TablesSlide 148Physical Data ModelNormalizationSlide 151Slide 152Introduction to Relational Database and SQLMinder ChenCSU Channel [email protected]&SQL - 2 ©Minder Chen, 1996-2011What 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.RDBMS&SQL - 3 ©Minder Chen, 1996-2011History 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.RDBMS&SQL - 4 ©Minder Chen, 1996-2011Database TechnologyBefore Relational systems people has been using:Before Relational systems people has been using:- File Systems like ISAM, VSAM and B-Tree;- File Systems like ISAM, VSAM and B-Tree;- Hierarchy Database;- Hierarchy Database;- Network Database.- Network Database.RDBMS has both a physical and a logical structure. By RDBMS has both a physical and a logical structure. By doing so, the physical storage of data can be managed doing so, the physical storage of data can be managed without affecting the access to logical storage without affecting the access to logical storage structures.structures.File Systems Hierarchy Network RelationalRDBMS&SQL - 5 ©Minder Chen, 1996-2011Relational 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 applicationsRDBMS&SQL - 6 ©Minder Chen, 1996-2011SQL Terminologyp_no name quantity price101 Color TV 24 500201 B&W TV 10 250202 PC 5 2000 Product TableProduct TableRow ColumnSet Theory Relational DB File ExampleRelation Table File Product_tableAttribute Column Data item Product_nameTuple Row Record Product_101's info.Domain Data Type Data type DATEor DomainRDBMS&SQL - 7 ©Minder Chen, 1996-2011SQL Terminology CREATE TABLE PRODUCT(p_no CHAR(5) NOT NULL, name CHAR(20), quantity SMALLINT, price 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 name price--------- ----------- ----------202 PC 2000CREATE TABLE PRODUCT(p_no CHAR(5) NOT NULL, Name CHAR(20), Quantity INTEGER, Price Currency, CONSTRAINT pk_productPRIMARY KEY (p_no));In Access In AccessRDBMS&SQL - 8 ©Minder Chen, 1996-2011SQL: Data Manipulation Language (DML)SELECT UPDATE INSERTDELETE SELECT UPDATE INSERTDELETE p_no name quantity price101 Color TV 24 500201 B&W TV 10 250202 PC 5 2000 SELECT p_no, name, price FROM PRODUCT WHERE PRICE < = 1000 ORDER BY PRICE DESC; p_no name price------- --------------- ----------101 Color TV 500201 B&W TV 250RDBMS&SQL - 9 ©Minder Chen, 1996-2011SQL Principles•The result of a SQL query is always in a table like format and is not