數(shù)據(jù)庫(kù)基礎(chǔ)與應(yīng)用課件

對(duì)象關(guān)系數(shù)據(jù)庫(kù)

1關(guān)系模型的限制2新的數(shù)據(jù)類(lèi)型3用戶(hù)自定義類(lèi)型4存儲(chǔ)對(duì)象的表5子表和超表關(guān)系模型的限制關(guān)系模型要求：屬性不能是復(fù)合類(lèi)型屬性的值不能是多值不支持IsA語(yǔ)義編號(hào)姓名職稱(chēng)工

資扣

除實(shí)發(fā)基本工齡職務(wù)房租水電86051陳平講師120550801601201055………………………………………………姓

名電話(huà)號(hào)碼王

林8636xxxx(H)，8797xxxx(O)，139xxxxx001張大民133xxxxx125，138xxxxx878Product(model,

maker,

type)PC(model,

speed,

ram,

hd,

cd,

price)Laptop(model,

speed,

ram,

hd,

screen,

price)Printer(model,

color,

type,

price)新的數(shù)據(jù)類(lèi)型-Array例10.5創(chuàng)建Sales表，記錄商品12個(gè)月的銷(xiāo)售量。CREATE

TABLE

Sales(

ITEM_NO

CHAR(20)，

--商品號(hào)

QTY

INTEGER

ARRAY［12］，

--整型數(shù)組，存放12個(gè)月的銷(xiāo)售額

PRIMARY

KEY(ITEM_NO)

);INSERT

INTO

Sales(ITEM_NO，QTY)VALUES

('T-shirt2000'，

array［200，150，200，100，50，70，80，200，10，20，100，200］);SELECT

ITEM_NO

FROM

Sales

--從Sales表中選出滿(mǎn)足下面條件的商品號(hào)WHERE

QTY［3］>100;

--QTY數(shù)組的第3個(gè)值大于100新的數(shù)據(jù)類(lèi)型-multiset例10.6CREATE

TABLE

Student

(

sno

char(7),

Sname

char(8),

Ssex

char(2),

Sage

smallint,

Sdept

char(20),

PhoneList

varchar(20)

multiset);INSERT

INTO

Student

VALUES('2000012','王林','男',19,'計(jì)算機(jī)',

multiset('12345678',

'139xxxxxxxx'));SELECT

S.SnameFROM

Student

SWHERE

'12345678'

in

unnest(S.PhoneList);新的數(shù)據(jù)類(lèi)型-rowCREATE

TABLE

person

(

pno

int,

name

row(first_name

VARCHAR2(30),

last_name

VARCHAR2(30)),

address

row(street

VARCHAR2(50),

city

VARCHAR2(50)),

birthday

date);pnonameaddressbirthdayfirst_namelast_namestreetcity………………………………SELECTaddress.cityFROMperson創(chuàng)建新的數(shù)據(jù)類(lèi)型-CREATETYPE新的數(shù)據(jù)類(lèi)型：用戶(hù)自定義類(lèi)型類(lèi)類(lèi)CREATE

TYPE

StudentType

AS

(

Sno

char(7),

Sname

char(8),

Ssex

char(2),

Sage

int,

Sdept

char(20));CREATE

TYPE

StudentType

AS

(

Sno

char(7),

Sname

char(8),

Ssex

char(2),

Sage

int,

Sdept

char(20))methodage(OnDatedate)returnsintervalyear;

CREATE

INSTANCE

METHOD

age(OnDate

date)

FOR

PersonTypeLANGUAGE

SQLBEGIN

return

OnDate

–

self.dayOfBirth;END;子類(lèi)/超類(lèi)CREATE

TYPE

post-studentType

under

StudentType

(Supervisor

char(8));新的數(shù)據(jù)類(lèi)型-ref例10.7定義Student類(lèi)型和Course類(lèi)型，二者之間存在參照關(guān)系。

CREATE

TYPE

StudentType

AS

(

Sno

char(7),

Sname

char(8),

Ssex

char(2),

Sage

int,

Sdept

char(20),

Courses

ref(CourseType)

multiset);CREATE

TYPE

CourseType

AS

(

Cno

char(4),

Cname

char(40),

Cpno

char(4),

Ccredit

int,

Students

ref(StudentType)

multiset);aStudentTypeaCourseTypeaCourseTypeaCourseTypeaCourseTypeaStudentTpeaStudentType對(duì)象關(guān)系(表)CREATE

TABLE

OStudent

OF

StudentType創(chuàng)建表OStudentOStudent是StudentType對(duì)象的集合refobjectoid.........SELECT

S.SnoFROM

OStudent

SWHERE

S.Sname

=

'王林';SELECT

S.Sno()FROM

OStudent

SWHERE

S.Sname()

=

'王林';對(duì)象關(guān)系(表)INSERT

INTO

OStudentVALUES(new

StudentType()

.Sno('2000012')

.Sname('王林')

.Ssex('男')

.Sage(19)

.Sdept('計(jì)算機(jī)')

);子表/超表CREATE

TABLE

OPostStudent

OF

post-studentType

under

OStudent

類(lèi)型StudentTypepost-studentType表OStudentOPostStudentIsA關(guān)系：apoststudentisastude