数据库数据库(2)数据库数据库(2)第2讲:对象-关系数据库复习复习复习复习 Relational Database Structured Query Language: SQLygg2Example: Relational Databasestudents SidCnoMajorEnrollmentSidLnameFnameClassTelno. 1101No 1108Yes 2102Yes 3101N1JonesAllan2555-12342BrownJohn3555-1324 3101No 3108Yes 5102Yes 5105N3SmithAdward2555-32145WhiteHarry3555-31425105NoCnoCnameCroomCtimecourse101Database1-1021000am102OS1-3020800am105Eli h2 2020230105English2-2020230pm108French3-4020230pm返回返回关系规则关系规则关系规则关系规则Rule 1: First Normal Form RuleRule 1: First Normal Form Rule ?All attributes take simple values. Columns have multi- valued attributes or have any internal structure notvalued attributes or have any internal structure not permitted Rule 2: Access Rows by Content Only Ruleyy ?Retrieve rows only by their attributes ?The order of tuples does not have any meaning.?The order of tuples does not have any meaning.4复习复习复习复习 Relational Database Structured Query Language: SQLygg5Basic SELECT StatementBasic SELECT StatementSELECTALL|DISTINCT *| expr AS c_alias ,expr AS c_alias FROMt blASFROMtablename AS corr_name ,tablename AS corr_name WHERE search_condition GROUP BYgroup by expressionGROUP BYgroup_by_expression HAVINGgroup_condition ORDER BYcolumn; SELECT identifies what columns FROM idtifihi h t bl FROM identifies which table WHERE identifies conditions6The CAP Database CUSTOMERSCUSTOMERSPRODUCTSPRODUCTSCUSTOMERSCUSTOMERSPRODUCTSPRODUCTSORDERSORDERSAGENTSAGENTS7ExampleExampleTt illtti ()To retrieve all customer-agent name pairs(cname,aname), where the customer places an order through the agent.Select distinct customers.cname, agents.aname from customers orders agentsfrom customers, orders, agents where customers.cid=orders.cid and orders.aid=agents.aid8Query ExamplesQuery ExamplesExample. Determine all pairs of customers based in theExample. Determine all pairs of customers based in the same citySELECT c1 cidc2 cidSELECT c1.cid, c2.cid FROM customers c1, customers c2 WHERE c1.city=c2.city and c1.cid 1SQL 2003 Table F nctionsSQL 2003: Table FunctionsSQL:2003 added functions that return a relation as a result-table function Example: Return all accounts owned by a given customer create function accounts_of (customer_name char(20) returns table ( account number char(10)returns table ( account_number char(10), branch_name char(15), balance numeric(12,2) return table (select account_number, branch_name, balance from accountfrom account where exists ( select * fdfrom depositor where depositor.customer_name = accounts_of.customer_name and depositor.account number = account.account number )36p_)select * from table (accounts_of (Smith)SQL ProceduresQPdb ik d ithfSQLProcedures can be invoked either from an SQL procedure or from embedded SQL, using the call statementstatement. declare a_count integer; call account count proc( Smith a count);call account_count_proc( Smith , a_count); Procedures and functions can be invoked also from dynamic SQLdynamic SQL SQL:1999 allows more than one function/procedure of the same name (called name overloading) asof the same name (called name overloading), as long as the number of arguments differ, or at least the types of the arguments differ37the types of the arguments differ小结小结: Object-Relational Model小结小结: Object Relational ModelE tensibilit of neser defined (abstract) dataExtensibility of new user-defined (abstract) data types ClbjComplex objects ?Collection type ?Structured type ?Large object type Inheritance Procedure and functionProcedure and function Fully relational compatible38主要内容主要内容主要内容主要内容对象 关系数据模型 对象-关系数据模型 对象-关系 SQLQ ?对象和表(Object CREATE TABLE tablenameCREATE TABLE tablename ( colname datatype not null, colname typename,colname datatype not null.);DROP TABLE tablename; DROP TYPE typename;42创建对象类型创建对象类型 (CiO j)(Creating Object Types)Example Create object type name t.Example Create object type name_t. create type name_t as object ( lnamevarchar(30)lnamevarchar(30), fname varchar(30), michar(1) ); /-for sql*plus, not part of SQL syntax Example Create a table teacher.create table teachers ( ( tid int, tname name_t, i t43room int );使用对象类型使用对象类型 (iO j)(Using Object Type)S ltilbjtinsert into teachers values (1234, name t(Einstein, Albert, E),120);Selecting column objectsinsert into teachers values (1234, name_t( Einstein , Albert , E ),120);select t.tid, t.tname.lname from teachers t where t.room = 120;You should always include a table alias.select tid, tname.fname, tname.lname from teachers where room=123; -DOESNT WORKDOESN T WORKselect t tidt tname fnamet tname lname from teachers t where44select t.tid, t.tname.fname, t.tname.lname from teachers t where room=123; 创建对象类型和对象表创建对象类型和对象表 (CiO jDROP TABLE tablename DROP TYPE typename45对象表对象表(Object Table)对象表对象表(Object Table)Example Create type person t.Example Create type person_t.create type person_t as object ( ssnoint