Database & SQL introduction

Database & SQL introduction

اسلاید 1: Web programming course, Winter 2005Database & SQL introductionWeb Programming course Acknowledgement : this presentation uses examples from the w3c website; available at - http://www.w3schools.com

اسلاید 2: Web programming course, Winter 2005OutlineDatabase introductionMotivationHistory ImplementationSQL introduction

اسلاید 3: Web programming course, Winter 2005Utility of DatabasesData have value independent of useOrganized approach to data management (e.g., data mining)AdvantagesEliminate redundancy in dataShare data Archive data Security of dataIntegrity of data

اسلاید 4: Web programming course, Winter 2005DEFINITIONA collection of application programs that perform services to end users. Each program defines and manages its own data.File Based Systems

اسلاید 5: Web programming course, Winter 2005Data Entry& ReportsFile handlingRoutinesFile DefinitionSales FilesData Entry& ReportsFile handlingRoutinesFile DefinitionLease FilesFile Based Processingphysical structure and storage of the data files are defined in the program code

اسلاید 6: Web programming course, Winter 2005Data DependenceDuplication of DataIncompatible file formatsLimitations of File Based SystemsHow can these problems be resolved?

اسلاید 7: Web programming course, Winter 2005A shared collection of logically related data designed to meet the information requirements of an organisationThe Database Approach

اسلاید 8: Web programming course, Winter 2005Data Entry& reportsData Entry& reportsDBMSSalesLeasesApplicationProgramsApp. ProgramsDatabaseDatabase Processing

اسلاید 9: Web programming course, Winter 2005DEFINITIONA software system that enables users to define, create and maintain the database and which provides controlled access to the databaseDatabase Management System (DBMS)

اسلاید 10: Web programming course, Winter 2005Allows users to define the database (DDL)Allows users to insert, update, delete & retrieve data (DML)Provides controlled accessa security systeman integrity systema concurrency control systema recovery systemFacilities of a DBMSWhat does it mean to define a database?

اسلاید 11: Web programming course, Winter 2005HardwareSoftwareDataProceduresPeopleComponents of a DBMS

اسلاید 12: Web programming course, Winter 2005Minimal data redundancyConsistency of dataIntegration of dataImproved integrityConsistent securityStandardsIncreased productivityAdvantages

اسلاید 13: Web programming course, Winter 2005ComplexityAdditional Hardware CostsSizePerformanceExperts -Specialised PersonnelPotential organisational ConflictHigher impact of failure (centralized data source failure)Disadvantages

اسلاید 14: Web programming course, Winter 2005Database Interfacesweb server & interface progrelational databaseweb browserdedicated applicationSQL command interfaceSQLSQLSQL

اسلاید 15: Web programming course, Winter 2005Interaction and FeedbackTransaction: non-decomposable unit of data manipulationexample: purchasing an airline ticket on-linetypically small and fast for commercial applicationsmay be long and involved in engineering applicationsRollback: if any part of a transaction fails, all completed parts are “rolled back” or undoneexample: if you haven’t provided your credit card number, airline ticket purchase on-line transaction failsrollback ensures integrity of databaseautomatically done by DBMS

اسلاید 16: Web programming course, Winter 2005Relational Database ModelDatabaseDatabase is a collection of tables (relations)Data are stored in tablesTablesEach table has a name Each table has a set of columns (fields) and rows of data (records)All operations process a table to produce a new table Each table has a fixed number of columns Each table has an arbitrary number of rowsBased on set theory SQL (Structured Query Language) DBMS independent languageWhy?

اسلاید 17: Web programming course, Winter 2005Weather Sample TableCityState High LowPhoenixArizona10590TusconArizona10192FlagstaffArizona8869San DiegoCalifornia7760Albuquerque New Mexico8060

اسلاید 18: Web programming course, Winter 2005Database Columns (Fields)Columns Each column has a nameColumns are accessed by nameNo standard column ordering Does not make sense to say “the third column” like it does in a “paper” table or spreadsheetData in a column belongs to a particular domain Columns are the “attributes” of the datasetEach value in a column is from the same domain Each value in a column is of the same data type

اسلاید 19: Web programming course, Winter 2005Database Rows (Records)Rows Each row entry is either a simple value or empty (null) Rows are sets of values for the columns (attribute values) Primary key: a set of columns that uniquely identifies each rowEach row must be unique given the primary key (no duplicates) Rows are referenced by the primary keyRow order cannot be determined by the user (Does not make sense to say “the fourth row” like it does in a “paper” table or spreadsheet)

اسلاید 20: Web programming course, Winter 2005Data TypesEach row value is an instance of a primitive data type Integer Real (e.g., number, currencyCharacter (e.g., text, hyperlink, yes/no)Date/TimeNo complex types in standard DBMS (matrix, drawing) Object oriented databases may allow objects and structuresNon existent value is “null”

اسلاید 21: Web programming course, Winter 2005Domain typeschar(n): fixed length char stringvarchar(n): variable-length char stringint or integersmallintnumeric(p,d): fixed-point number of given precisionreal, double precisionfloat(n): floats with a given precisiondate: containing year,month, and datetime: in hours, minutes, and secondsNull value is part of each domain

اسلاید 22: Web programming course, Winter 2005SQLStructured Query LanguageCommunicate with databasesUsed to created and edit databases.Also used to create queries, forms, and reports

اسلاید 23: Web programming course, Winter 2005DDL and DMLSQL consists of two types of statementsSQL Data Definition Language (DDL) permits database tables to be created or deleted inserts new data into a database table CREATE TABLE - creates a new database table ALTER TABLE - alters (changes) a database table DROP TABLE - deletes a database table SQL Data Manipulation Language (DML)SELECT - extracts data from a database table UPDATE - updates data in a database table DELETE - deletes data from a database table INSERT INTO - inserts new data into a database table

اسلاید 24: Web programming course, Winter 2005Define new domains and tablesCREATE DOMAIN personDom CHAR(20);CREATE TABLE emp (enamepersonDom, dnoint default 0, salreal);

اسلاید 25: Web programming course, Winter 2005“Select” clause 1Specify attributes to project onto Emp (ename, dno, sal)SELECT *FROM EmpWHERE Emp.Sal < 60K;use ‘*’ to denote all attributes:SELECT columns FROM table WHERE condition ;

اسلاید 26: Web programming course, Winter 2005“Select” clause 2“SELECT” does not automatically eliminate duplicates. Emp (ename, dno, sal)Select dnoFrom Emp;Use keyword distinct to explicitly remove duplicatesSelect distinct dnoFrom Emp;

اسلاید 27: Web programming course, Winter 2005“FROM” clauseSpecify relationsE1: Emp (ename, dno, sal)Dept(dno, dname, mgr)E2: Emp (ename, dno, sal)SELECT columns FROM table WHERE condition ;SELECT E1.enameFROM Emp as E1, Dept, Emp as E2WHERE E1.dno = Dept.dno AND Dept.mgr = E2.ename AND E1.sal > E2.sal;Renaming relations: Use “as” to define “tuple variables,” to disambiguate multiple references to the same relation?Who makesmore moneythan his boss?

اسلاید 28: Web programming course, Winter 2005“WHERE” clauseSpecify optional conditions“Employees who work for Sally and have a salary < 90K”SELECT enameFROM Emp, DeptWHERE Emp.dno=Dept.dno AND D.mgr = ‘Lisa’ AND sal < 90000; Emp (ename, dno, sal)Dept(dno, dname, mgr)Lisa

اسلاید 29: Web programming course, Winter 2005Conditions Used In Where Clause=equals>greater than<less than>=greater than or equal to<=less than or equal to<>not equal to

اسلاید 30: Web programming course, Winter 2005LikeUsed to make complex searching easy. If you are trying to find all people’s names which begin with E for example: SELECT firstname FROM employee WHERE firstname LIKE E%;

اسلاید 31: Web programming course, Winter 2005Ordering output tuplesOrder the tuples by dno. Within each dept, order salaries from highest to lowest. For salary ties, use alphabetical order on the name.SELECT * FROMEmporder by dno, sal, ename;

اسلاید 32: Web programming course, Winter 2005Set OperationsUnion: . Intersect: . Except: - (select mgr from D where dname=‘toy’)union(select mgr from D where dname = ‘sells’);(select mgr from D where dname=‘toy’)intersect(select mgr from D where dname = ‘sales’);(select mgr from D where dname=‘toy’)except(select mgr from D where dname = ‘sells’);“Find names of people who are managers of either the toy or the sales department.”“Find names of people who are managers of both the toy and the sales departments.”“Find names of people who are managers of the toy but of the sales department.”

اسلاید 33: Web programming course, Winter 2005Conserving DuplicatesThe UNION, INTERSECT, and EXCEPT operators use the set semantics, not bag semantics. To keep duplicates, use “ALL” after the operators:UNION ALL, INTERSECT ALL, EXCEPT ALLStudent (ssno, name)TA (ssno, name)Result(SELECT ssno FROM student)UNION(SELECT ssno FROM ta);ALL

اسلاید 34: Web programming course, Winter 2005Aggregation functionsMIN, MAX, SUM, COUNT, AVGinput: collection of numbers/strings (depending on operation)output: relation with a single attribute with a single row“What is the minimum, maximum, average salary of employees in the toy department”SELECT MIN(sal), MAX(sal), AVG(sal)FROM Emp, DeptWHERE Emp.dno = Dept.dno and D.dname = ’Toy’;

اسلاید 35: Web programming course, Winter 2005JOINSometimes we have to select data from two or more tables to make our result complete. We have to perform a join Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.

اسلاید 36: Web programming course, Winter 2005INNER JOIN The INNER JOIN returns all rows from both tables where there is a match. Employee_IDName01Hansen, Ola02Svendson, Tove03Svendson, Stephen04Pettersen, KariOrders:Prod_IDProductEmployee_ID234Printer01657Table03865Chair03Employees:SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID ResultNameProductHansen, OlaPrinterSvendson, StephenTableSvendson, StephenChairIf there are rows in Employees that do not have matches in Orders, those rows will not be listed

اسلاید 37: Web programming course, Winter 2005LeftRight JoinThe LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_IDNameProductHansen, OlaPrinterSvendson, Tove Svendson, StephenTableSvendson, StephenChairPettersen, Kari NameProductHansen, OlaPrinterSvendson, StephenTableSvendson, StephenChair

اسلاید 38: Web programming course, Winter 2005INSERT INTOThe INSERT INTO statement is used to insert new rows into a tableINSERT INTO table_name VALUES (value1, value2,....) INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)Or specify the columns for which you want to insert data:

اسلاید 39: Web programming course, Winter 2005Insert Into ExampleLastNameFirstNameAddressCityPettersenKariSto 20StavINSERT INTO Persons  VALUES (Hetland, Camilla, Hagabakka 24, Sandnes)LastNameFirstNameAddressCityPettersenKariSto 20StavHetlandCamillaHaga 24Sandnes

اسلاید 40: Web programming course, Winter 2005UPDATEThe UPDATE statement is used to modify the data in a table.UPDATE table_name SET column_name = new_value WHERE column_name = some_value

اسلاید 41: Web programming course, Winter 2005Person:LastNameFirstNameAddressCityNilsenFredKirk 56StavRasmussen Sto 67 LastNameFirstNameAddressCityNilsenFredKirk56StavRasmussenNinaSto 67 UPDATE Person SET FirstName = Nina WHERE LastName = RasmussenUPDATE Person SET Address = Stien 12, City = Stav WHERE LastName = Rasmussen LastNameFirstNameAddressCityNilsenFredKirk56StavRasmussenNinaStien 12 Stav

اسلاید 42: Web programming course, Winter 2005DELETEThe DELETE statement is used to delete rows in a table.DELETE FROM table_name WHERE column_name = some_valueLastNameFirstNameAddressCityNilsenFredKirk 56Stav DELETE FROM Person WHERE LastName = RasmussenLastNameFirstNameAddressCityNilsenFredKirk56StavRasmussenNinaStien 12 Stav

اسلاید 43: Web programming course, Winter 2005SummaryWhy uses databases instead of file systems, what are the pro-cons, when to use each alternative?What is a relational database? How can you define its semantics?What is SQL? What are DDL and DML? What statements are related to each SQL rule

اسلاید 44: Web programming course, Winter 2005Questions?