《数据库系统概念02(对象-关系数据库)》由会员分享，可在线阅读，更多相关《数据库系统概念02(对象-关系数据库)（91页珍藏版）》请在金锄头文库上搜索。
1、数据库数据库(2)数据库数据库(2)第2讲:对象-关系数据库复习复习复习复习 Relational Database Structured Query Language: SQLygg2Example: Relational Databasestudents SidCnoMajorEnrollmentSidLnameFnameClassTelno. 1101No 1108Yes 2102Yes 3101N1JonesAllan2555-12342BrownJohn3555-1324 3101No 3108Yes 5102Yes 5105N3SmithAdward2555-32145WhiteH
2、arry3555-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 hav
3、e 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 St
4、ructured 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 BY
5、column; 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,
6、aname), where the customer places an order through the agent.Select distinct ame, 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
7、. 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: Ret
8、urn 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 accoun
9、t 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 embed
10、ded 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 n
11、ame 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) dataExtensibili
12、ty 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 t
13、ablename ( 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
14、), 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 colum
15、n 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 fname
16、t 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
