Transcript CCLRC PowerPoint Template

CERIF COURSE
Session4: DataModel 2
Keith G Jeffery,
Director, IT CLRC
[email protected]
Anne Asserson,
University of Bergen
[email protected]
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
1
Structure
•
•
•
•
•
Full model – primary base entities
Person – secondary base entities
Person– Language-base entities
Person– Lookup tables
Person– linking relations: at one level,
recursion, at different levels
• Exchange Model and Metadata Model
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
2
Structure
•
•
•
•
•
Full model – primary base entities
Person – secondary base entities
Person– Language-base entities
Person– Lookup tables
Person– linking relations: at one level,
recursion, at different levels
• Exchange Model and Metadata Model
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
3
Full model – primary base entities
Base Entities
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
4
Full model – primary base entities
Project
Entity
Attribute
Type
Constraint
BASE TABLES
Comments / Meaning
Primary 3 object types (entities)
Note because of multilingual, text
fields are separated out into
language-field-base tables
Project
Project Id
char(32)
m, pk
Project
Start Date
date
m
Project
End Date
date
o
Project
Status
char(8)
m, enumlist
Project
URI
char(12
8)
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
stalled, completed…
project homepage
20021024
5
Full model – primary base entities
Person
Person
Person Id
char(32)
m, pk
Person
Family Names
char(32)
m
list, separated
Person
First Names
char(16)
o
list, separated
Person
Other Names
char(32)
o
list, separated
Person
Sex
char(1)
o, enumlist
M|F|
Person
Honorific Title
char(12)
o, enumlist
enumlist, separated
Person
Academic Title
char(12)
o, enumlist
enumlist, separated
Person
Qualifications
char(24)
o, enumlist
enumlist, separated
Person
Nationalities
char(16)
o, enumlist
enumlist, ISO 3166 standard, two
letter codes, separated
Person
URI
char(12
8)
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
Person homepage
20021024
6
Full model – primary base entities
OrgUnit
OrgUnit
OrgUnit Id
char(32)
m, pk
OrgUnit
Acronym
char(16)
o
OrgUnit
Type
char(8)
m, enumlist
enumlist, separated
OrgUnit
Headcount
char(2)
o
The harmonised package for the
European Union Fifth Framework
Programme provides definitions for
Organization/Company size, which
are used here.
OrgUnit
Turnover
float
o
total annual working budget
OrgUnit
OrgUnit
© Keith G Jeffery & Anne Asserson
Currency
URI
char(3)
m if value
else o
char(12
8)
CERIF Course: Data Model 1
3 letter ISO 4217 currency codes to
be used.
OrgUnit homepage
20021024
7
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
8
Full model – primary base entities
Project-Person
Project-Person
Project Id
char(32)
m,
fk,
pk(part)
Project-Person
Person Id
char(32)
m,
fk,
pk(part)
Project-Person
Role
char(16)
o, enumlist
Project-Person
Start Date
date
o
Project-Person
End Date
date
o
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
e.g. project leader, investigator,
accountant
20021024
9
Full model – primary base entities
Project-OrgUnit
Project-OrgUnit
Project Id
char(32)
m,
fk,
pk(part)
Project-OrgUnit
OrgUnit Id
char(32)
m,
fk,
pk(part)
Project-OrgUnit
Role
char(16)
o, enumlist
List of values are defined for EU
Fifth Framework Programme
CO : Co-ordinator (=scientific,
administrative and financial coordinator)
CF: Only financial co-ordinator (if
different from co-ordinator)
AC: Associate contractor
CR: Contractor (other than the coordinator)
Project-OrgUnit
Start Date
date
o
Project-OrgUnit
End Date
date
o
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
10
Person-OrgUnit
Person-OrgUnit
Person Id
char(32)
m,
fk,
pk(part)
Person-OrgUnit
OrgUnit Id
char(32)
m,
fk,
pk(part)
Person-OrgUnit
Role
char(16)
o, enumlist
Person-OrgUnit
Start Date
date
o
Person-OrgUnit
End Date
date
o
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
e.g. division head, group leader
20021024
11
Structure
•
•
•
•
•
Full model – primary base entities
Person – secondary base entities
Person– Language-base entities
Person– Lookup tables
Person– linking relations: at one level,
recursion, at different levels
• Exchange Model and Metadata Model
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
12
Person – secondary base entities
Person: in depth
• Take Base Entity Person
– Secondary base tables
– Secondary base tables and language base
tables
– Secondary base tables and lookup tables
(without links)
– Lookup tables and links
– Secondary Base tables and links
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
13
Level 2 Person Entity
Pers Con
Event
Event
Event
Event
Event
Event
Event
Event
Id
Type
Location
Start Date
End Date
Fee or Free
URL
Ev ent Per
CV
CV Id
CV URI
Per
Per
Per
Per
Per
Per
Per
Per
Per
Per
Per Exp Skill
Per Patent
Id
Family Names
First Names
Other Names
Sex
Academic Title
Qualifications
Nationalities
Prize Award
URI
Per Product
Result Patent
Patent Id
Patent Number
Patent Countries
Patent Reg Date
Patent Approval date
Patent URI
Contact
Id
Addrline1
Addrline2
Addrline3
AddrLine4
Addrline5
City Town
Province State
Postal code
Telephone
Fax
Email
URI
Person
CV Per
Expertise Skills
Exp Skill Id
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Result Product
Prod Id
Prod Int Id
Prod URI
Pers Equip
Pers Class
Particular Equipment
Equip Id
Equip Ow Inv Id
Equip OEM Id
Classification
Class
Class code
Class URL
General Facility
Facility ID
Facility URL
Pers Facility
Per Serv ice
Pers Pub
Result Publication
Pub Id
Pub Date
Pub Reference
Pub URI
Pub URI Type
Service
Service Id
Service URI
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
14
Person – secondary base entities
Secondary Base
Example: Contact
Contact
Contact Id
Contact
Addrline1
char(80)
o
Contact
Addrline2
char(80)
o
Contact
Addrline3
char(80)
o
Contact
Addrline4
char(80)
o
Contact
Addrline5
char(80)
o
Contact
City Town
char(32)
m
Contact
State
Country
char(32)
o
Contact
Postcode
char(16)
m
Contact
Country code
char(2)
m, enumlist
Contact
NUTS Code
char(10)
m, enumlist
Eurostat region locator code,
mandatory in EU
Contact
Telephone
char(24)
o
list, separated
Contact
Fax
char(24)
o
list, separated
Contact
Email
Contact
URI
o
contact URI
© Keith G Jeffery & Anne Asserson
char(32)
of
char(32)
char(12
8)
m, pk
o
CERIF Course: Data Model 1
20021024
15
Structure
•
•
•
•
•
Full model – primary base entities
Person – secondary base entities
Person– Language-base entities
Person– Lookup tables
Person– linking relations: at one level,
recursion, at different levels
• Exchange Model and Metadata Model
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
16
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Level 3 Person Entity
Event Name
Event Langauge
Event Trans Type
Event Name
Ev ent Name
Event
Event
Event
Event
Event
Event
Event
Event
Id
Type
Location
Start Date
End Date
Fee or Free
URL
Pers Con
Ev ent Per
Contact
Id
Addrline1
Addrline2
Addrline3
AddrLine4
Addrline5
City Town
Province State
Postal code
Telephone
Fax
Email
URI
Research Interest
Res Int Language
Res Int Trans Type
Res Int Keywords
Ev ent Description
CV
CV Id
CV URI
Event Description
Event Desc Language
Event Desc Trans Type
Event Description
Per Res Interest
CV Per
Expertise Skills Name
Exp Skill Name Language
Exp Skill Name Trans Type
Exp Skill Name
Per Product
Per
Per
Per
Per
Per
Per
Per
Per
Per
Per
Person
Id
Family Names
First Names
Other Names
Sex
Academic Title
Qualifications
Nationalities
Prize Award
URI
Equipment Description
Equip Desc Language
Equip Desc Trans Type
Equip Description
Pers Class
Equip Desc
Pers Equip
Particular Equipment
Equip Id
Equip Ow Inv Id
Equip OEM Id
Exp Skill Name
Expertise Skills
Exp Skill Id
Per Exp Skill
Classification
Class
Class code
Class URL
Exp Skill Desc
Expertise Skills Description
Exp Skill Desc Language
Exp Skill desc Trans Type
Exp Skill Description
Result Product
Prod Id
Prod Int Id
Prod URI
Result Patent
Patent Id
Patent Number
Patent Countries
Patent Reg Date
Patent Approval date
Patent URI
Pers Facility
Per Serv ice
Equip Name
Equipment Name
Equip Name Language
Equip Name Trans Type
Equip Name
General Facility
Facility ID
Facility URL
Result Publication
Pub Id
Pub Date
Pub Reference
Pub URI
Pub URI Type
Per Patent
Pers Pub
Relation_253
Patent Title
Patent Title Language
Patent Title Trans Type
Patent Title
Relation_254
Patent Abstract
Patent Abs Language
Patent Abs Trans Type
Patent Abstract
© Keith G Jeffery & Anne Asserson
Service
Service Id
Service URI
Serv ice Description
CERIF Course: Data Model 1
Serv ice Name
Service Name
Service Name Language
Service Name Trans Type
Service Name
Service Description
Service Dersc Language
SERVICE_TRANS_TYPE
Service Description
20021024
17
Person– Language-base entities
Language Field Base
Example: Research Interest
Person-Research
interest
Person Id
char(32)
m, pk(part)
Language
char(2)
m, pk(part)
Translation
Keywords
© Keith G Jeffery & Anne Asserson
char(1)
m, pk(part)
o(rig), h(uman), m(achine)
char(10
24)
CERIF Course: Data Model 1
20021024
18
Structure
•
•
•
•
•
Full model – primary base entities
Person – secondary base entities
Person– Language-base entities
Person– Lookup tables
Person– linking relations: at one level,
recursion, at different levels
• Exchange Model and Metadata Model
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
19
Country
Country Code
Country Name
Country Name English
NUTS Region
Region code
Region Name
Region Name English
Level 4 Person Entity
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Con
Country Contact
Event
Event
Event
Event
Event
Event
Event
Multimedia Type
Multimedia Type
Multimedia Type Full
Multimedia Format
Event
Id
Type
Location
Start Date
End Date
Fee or Free
URL
CV Multimedia
Pers Con
Ev ent Per
CV Per
Per Honor Title
Academic Title
ACADEMIC_TITLE
Academic Title Full
Per Academ Title
Per Qualif ication
Per Language
Language
Lang Code
Lang Full Name
Lang English Name
Expertise Skills
Exp Skill Id
Result Patent
Patent Id
Patent Number
Patent Countries
Patent Reg Date
Patent Approval date
Patent URI
Patent Status
Patent Ty pe
Patent Type
Patent Type
Patent Type Full
© Keith G Jeffery & Anne Asserson
Reg Con
CV
CV Id
CV URI
Honorofic Title
Honorific Title
Honorific Title Full
Qualification
Qualification
Qualification Full
Contact
Id
Addrline1
Addrline2
Addrline3
AddrLine4
Addrline5
City Town
Province State
Postal code
Email
Fax
Telephone
URI
Per
Per
Per
Per
Per
Per
Per
Per
Per
Per
Person
Id
Family Names
First Names
Other Names
Sex
Academic Title
Qualifications
Nationalities
Prize Award
URI
Per Product
Pers Equip
Pers Class
Pers Facility
Equip Ty pe
Equipment Type
Equip Type
Equip Type Full
Classification
Class
Class code
Class URL
General Facility
Facility ID
Facility URL
Facility Ty pe
Facility Type
FACILITY_TYPE
Facility Type Full
Per Serv ice
Per Exp Skill
Result Publication
Pub Id
Pub Date
Pub Reference
Pub URI
Pub URI Type
Pers Pub
Per Patent
Result Product
Prod Id
Prod Int Id
Prod URI
Prod Ty pe
Patent Status
Patent Status
Patent Status Full
Particular Equipment
Equip Id
Equip Ow Inv Id
Equip OEM Id
Pub Ty pe
Service
Service Id
Service URI
Publication Type
Pub Type
Pub Type Full
Product Type
PROD_TYPE
Prod Type Full
CERIF Course: Data Model 1
20021024
20
Person– Language-base entities
Lookup Table
Examples: Person-related
Person-honorific
title
Honorific Title
char (4)
m, pk(part)
Person-honorific
title
Title full
char(32)
m, pk(part)
Person-academic
title
Academic Title
char (4)
m, pk(part)
Person-academic
title
Title full
char(32)
m, pk(part)
Personqualification
Qualification
char(4)
m, pk(part)
Personqualification
Qualification
full
char(32)
m, pk(part)
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
Sir, Lady…
Dr, Prof…
ISCED (UNESCO International
Classification of Education, level of
education)
20021024
21
Level 5 Person Characteristics
MULTIMEDIA_TYPE
MULTIMEDIA_TYPE
VARCHAR2(16)
MULTIMEDIA_TYPE_FULL
VARCHAR2(64)
MULTIMEDIA_FORMAT
VARCHAR2(32)
MULTIMEDIA_TY PE = MULTIMEDIA_TY PE
CV_MULTIMEDIA_TYPE
MULTIMEDIA_TYPE
VARCHAR2(16)
CV_ID
CHAR(32)
CV_ID = CV_ID
CV_ID
CV_URI
CV
VARCHAR2(32)
VARCHAR2(512)
CV_ID = CV_ID
PERS_CV
CV_ID
CHAR(32)
PER_ID CHAR(32)
CON_ID
CONTACT
CON_ID
CHAR(32)
COUNTRY_CODE
VARCHAR2(4)
REGION_CODE
CHAR(10)
CON_ADDRLINE1
VARCHAR2(80)
CON_ADDRLINE2
VARCHAR2(80)
CON_ADDRLINE3
VARCHAR2(80)
CON_ADDRLINE4
VARCHAR2(80)
CON_ADDRLINE5
VARCHAR2(80)
CON_CITY_TOWN
VARCHAR2(32)
CON_PROVINCE_STATE
VARCHAR2(32)
CON_POSTAL_CODE
VARCHAR2(16)
CON_TELEPHONE
VARCHAR2(24)
CON_FAX
VARCHAR2(24)
CON_EMAIL
VARCHAR2(32)
= CON_ID
CON_URI
VARCHAR2(512)
ACADEMIC_TITLE
ACADEMIC_TITLE
CHAR(4)
ACADEMIC_TITLE_FULL
VARCHAR2(32)
PERS_CONTACT
PER_ID
CHAR(32)
CON_ID
CHAR(32)
PER_CON_ROLE
VARCHAR2(16)
PER_CON_START
DATE
PER_CON_END
DATE
ACADEMIC_TITLE = ACADEMIC_TITLE
PER_ID = PER_ID
ACADEMIC_TITLE_PER
ACADEMIC_TITLE
CHAR(4)
PER_ID
CHAR(32)
PER_ID = PER_ID
HONOROFIC_TITLE
HONORIFIC_TITLE
CHAR(4)
HONORIFIC_TITLE_FULL
VARCHAR2(32)
PER_ID = PER_ID
HONORIFIC_TITLE = HONORIFIC_TITLE
HONORIFIC_TITLE_PER
HONORIFIC_TITLE
CHAR(4)
PER_ID
CHAR(32)
PER_ID = PER_ID
PER_ID = PER_ID
QUAL_PERSON
QUALIFICATION
CHAR(4)
PER_ID
CHAR(32)
PERSON
PER_ID
CHAR(32)
PER_FAMILY_NAMES
VARCHAR2(32)
PER_FIRST_NAMES
VARCHAR2(16)
PER_OTHER_NAMES
VARCHAR2(32)
PER_SEX
CHAR(1)
PER_ACADEMIC_TITLE
VARCHAR2(8)
PER_QUALIFICATIONS
VARCHAR2(24)
PER_NATIONALITIES
VARCHAR2(16)
PER_PRIZE_AWARD
VARCHAR2(64)
PER_URI
VARCHAR2(512)
QUALIFICATION = QUALIFICATION
PER_ID = PER_ID
QUALIFICATION
QUALIFICATION
CHAR(4)
QUALIFICATION_FULL
VARCHAR2(32)
PERS_LANG
PER_ID
CHAR(32)
LANG_CODE
CHAR(2)
SKILL_READING
CHAR(1)
SKILL_WRITING
CHAR(1)
SKILL_SPEAKING
CHAR(1)
PER_ID = PER_ID
PERS_EXPERT_SKILL
PER_ID
CHAR(32)
EXP_SKILL_ID
CHAR(32)
PER_EXP_ROLE
VARCHAR2(16)
PER_EXP_CONDITIONS
VARCHAR2(1024)
PER_EXP_PRICE
NUMBER(12,2)
PER_EXP_CURRENCY
CHAR(3)
PER_EXP_AVAILABILITY
VARCHAR(64)
PER_EXP_START
DATE
PER_EXP_END
DATE
LANG_CODE = LANG_CODE
EXP_SKILL_ID = EXP_SKILL_ID
LANGUAGE
LANG_CODE
CHAR(2)
LANG_FULL_NAME
VARCHAR2(32)
LANG_ENGLISH_NAME
VARCHAR2(32)
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
EXPERTISE_SKILLS
EXP_SKILL_ID
CHAR(32)
20021024
22
Person– Language-base entities
Links to Lookup Tables
Example: Person-Related
• Note the diagram is CERIF2000
• A mistake was later noticed – Person
contains attributes academic title and
qualifications
• These are separated out and linked to
Person
• Therefore the attributes within Person
are redundant
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
23
Structure
•
•
•
•
•
Full model – primary base entities
Person – secondary base entities
Person– Language-base entities
Person– Lookup tables
Person– linking relations: at one level,
recursion, at different levels
• Exchange Model and Metadata Model
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
24
Level 5 Pers on Prim ary Links
P ROJ _PROJ E CT
P ROJ _ID
P RO_PROJ _I D
P ROJ _PROJ _ ROLE
P ROJ _PROJ _ ST ART
P ROJ _PORJ _ END
P ERS_P ERS
P ER_ID
P ER_PE R_ID
P ER_PE R_ROLE
P ER_PE R_S T ART
P ER_PE R_E ND
P ROJ _PE RS ON
P ROJ _ID
P ER_ID
P ROJ _PE R_ROLE
P ROJ _PE R_S T ART
P ROJ _PE R_E ND
PER_ID = PER_ID
PER_ID = PER_ID
PROJ_ID = PROJ _ID
PROJ_ID = PRO_PROJ_ID
P ERSON
PER_ID = PER_PER _ID
P ER_ID
P ER_FAMILY _NA ME S
P ER_FIRS T _ NA ME S
P ER_OT HE R_NA ME S
P ER_SE X
P ER_ACADE MIC_T IT LE
P ER_QUALIF ICA T IONS
P ER_NA T IONALIT IES
P ER_PRIZE_ AW ARD
P ER_URI
PROJ_ID = PROJ _ID
P ROJ ECT
P ROJ _ID
P ROJ _ST ART _DA T E
P ROJ _END_DA T E
P ROJ _URL
P ROJ _PRIZE _AW ARD
PER_ID = PER_ID
P ERS_ORG
P ER_ID
ORGUNIT ID
P ER_ORG_ROLE
P ER_ORG_S T ART
P ER_ORG_E ND
PROJ_ID = PROJ _ID
ORGUNITID = ORG UNITID
P ROJ _ORGUNIT
P ROJ _ID
ORGUNIT ID
P ROJ _ORG_ROLE
P ROG_ORG_S T A RT
P ROJ _ORG_E ND
ORGUNIT
ORGUNIT ID
ORG_ACRONYM
ORG_NA ME
ORG_HE ADCOUNT
ORG_T URNOVE R
ORG_T URNOVE R_CURR
ORG_PRIZE_ AW ARD
ORG_URL
ORGUNITID = ORG
ORGUNITID
UNITID = ORG UNITID
ORGUNITID = ORG _ORGUNITID
ORG_ORG
ORGUNIT ID
ORG_ORGUNIT ID
ORG_ORG_ROLE
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
25
Person– Language-base entities
Person-Person
Person-Person
Person Id
char(32)
m,
fk,
pk(part)
Person-Person
Person Id
char(32)
m,
fk,
pk(part)
Person-Person
Role
char(16)
o, enumlist
Person-Person
Start Date
date
o
Person-Person
End Date
date
o
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
e.g. co-worker, manager
20021024
26
L5 Pers on Results
EQUIP_ID = EQUIP_ID
E VE NT
E VE NT _ID
E VE NT _T Y PE
E VE NT _LOCAT ION
E VE NT _ST ART _DA T E
E VE NT _END_DA T E
E VE NT _FEE _OR_FREE
E VE NT _URL
P ART ICULA R_E QUIP ME NT
E QUIP_ID
E QUIP_OW_INV _ID
E QUIP_OEM_ID
P ERS_E QUIP
P ER_ID
E QUIP_ID
P ER_EQUIP _ROLE
P ER_EQUIP _CONDIT IONS
P ER_EQUIP _PRICE
P ER_EQUIP _CURRE NCY
P ER_EQUIP _AV A ILAB ILIT Y
P ER_EQUIP _ST A RT
P ER_EQUIP _END
EVENT_ID = EVENT_ID
P ERS_E VE NT
P ER_ID
E VE NT _ID
P ER_EV ENT _ROLE
GENERAL_FA CILIT Y
FA CILIT Y_ID
FA CILIT Y_T YP E
FA CILIT Y_URL
P ERS_FA CILIT Y
P ER_ID
FA CILIT Y_ID
P ER_FACIL_ROLE
P ER_FACIL_CONDIT IONS
P ER_FACIL_PRICE
P ER_FACIL_CURRE NCY
P ER_FACIL_AV A IL
P ER_FACIL_ST A RT
P ER_FACIL_END
PER_ID = PER_ID
FACILITY_ID = FACILITY_ID
PER_ID = PER_ID
P ERSON
P ER_ID
P ER_FAMILY_NA ME S
P ER_FIRS T _NA ME S
P ER_OT HE R_NA ME S
P ER_SE X
P ER_ACADEMIC_T IT LE
P ER_QUALIFICA T IONS
P ER_NA T IONA LIT IES
P ER_PRIZE_A W ARD
P ER_URI
PER_ID = PER_ID
PER_ID = PER_ID
P ERS_P UB
P ER_ID
P UB _ID
P ER_PUB_ROL E
P ER_PUB_COP Y RIGHT
PER_ID = PER_ID
RES ULT _PUBL ICA T ION
P UB _ID
P UB __DA T E
P UB _RE FERENCE
P UB _URI
P UB _URI_T Y PE
PUB_ID = PUB_ID
P ERS_P AT ENT
P ER_ID
P AT ENT _ID
P ER_PA T E NT _ROLE
P ER_PA T E NT _S T A RT
P ER_PA T E NT _E ND
PER_ID = PER_ID
PATENT_ID = PATENT_ID
PER_ID = PER_ID
P ERS_P RODUCT
P ER_ID
P ROD_ID
P ER_PROD_ROLE
P ER_PROD_IPR
P ER_PROD_CONDIT IONS
P ER_PROD_PRICE
P ER_PROD_CURRE NCY
P ER_PROD_AV A ILAB ILIT Y
P ERS_S ERVICE
P ER_ID
S ERVICE_ID
P ER_SE RV ICE _ROLE
P ER_SE RV ICE _CONDIT IONS
P ER_SE RV ICE _P RICE
P ER_SE RV ICE _CURRE NCY
P ER_SE R_V ICE _A VA ILAB ILIT Y
P ER_SE RV ICE _S T A RT
P ER_SE RV ICE _E ND
SERVICE_ID = SERVIC E_ID
© Keith G Jeffery & Anne Asserson
RES ULT _PA T E NT
P AT ENT _ID
P AT ENT _S T A T US
P AT ENT _T YP E
P AT ENT _NUMB ER
P AT ENT _COUNT RIES
P AT ENT _REG_DAT E
P AT ENT _A PP ROV AL_DAT E
P AT ENT _URI
RES ULT _PRODUCT
P ROD_ID
P ROD_T YP E
P ROD_INT _ID
P ROD_URI
PROD_ID = PROD_ID
S ERVICE
S ERVICE_ID
S ERVICE_URI
CERIF Course: Data Model 1
20021024
27
Person– Language-base entities
Secondary Base Links
Example: Person-Result_Publication
PersonResult_Publication
Person Id
char(32)
m,
fk,
pk(part)
PersonResult_Publication
Result_Publicat
ion Id
char(32)
m,
fk,
pk(part)
PersonResult_Publication
Role
char(16)
o, enumlist
PersonResult_Publication
Copyright
char(32)
m
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
UNIMARC Manual – Bibliographic
Format 1994 (IFLA-UBCIM)
20021024
28
Structure
•
•
•
•
•
Full model – primary base entities
Person – secondary base entities
Person– Language-base entities
Person– Lookup tables
Person– linking relations: at one level,
recursion, at different levels
• Exchange Model and Metadata Model
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
29
CERIF Exchange Model
CV
CV Id
CV URI
Contact
Con Id
Con Addrline1
Con Addrline2
Con Addrline3
Con AddrLine4
Con Addrline5
Con City Town
Con Province State
Con Postal code
Con URI
Region code
Country Code
Telephone
Fax
E Mail
FUNDING
Funding Program
Funding Id
Funding Start Date
Funding End Date
Funding Budget
Funding Currency
Funding URL
EXPERTISE
Classification
Class
Class code
Class URL
Pers CV
Person
Per Id
Per Family Names
Per First Names
Per Other Names
Per URI
Exp Skill Desc Language
Exp Skill Description
Funding Funding
Pers Contact
Proj Pers
Pers Org
Pers Pers
Particular Equipment
Equip Id
Equip Name Language
Equip Name
Proj Proj
Proj Patent
Proj Pub
Proj
Proj
Proj
Proj
Proj
Project
Id
Start Date
End date
URI
Status
Org Funding
Proj Funding
Proj Equip
Proj OrgUnit
Proj Facility
Org Cont
Org Equip
OrgUnit Pub
OrgUnit Prod
RESULTS
OrgUnit
Org Id
Org Acronym
Org Name
Org URI
Org Type
Org Org
Proj Prod
General Facility
Facility ID
Facility URI
Facility Name Language
Facility Name
Pers Patent
Pers Pub
Pers Prod
Result Patent
Patent Id
Patent Number
Patent URI
Patent Title Language
Patent Title
Patent Abs Language
Patent Abstract
© Keith G Jeffery & Anne Asserson
Result Publication
Pub Id
Pub Reference
Pub URI
Prod
Prod
Prod
Prod
Prod
Prod
Prod
Result Product
Id
URI
Type
Name Language
Name
Desc Language
Description
CERIF Course: Data Model 1
OrgUnit Facility
SERVICES AND EQUIPMENT
Service
Service
Service
Service
Service
Id
URI
Name Langauge
Name
Org Serv ice
OrgUnit Patent
20021024
30
Exchange Model and Metadata Model
Exchange Model
• Project and Contact Entities always
present
• Can specialise thereafter as
– Expertise
– Results
– Funding
– Services and Equipment
• Depending on kind of CRIS and need
for exchange
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
31
Expertise Exchange
Multimedia Type
Multimedia Type
Multimedia Type Full
Multimedia Format
CV MultiMedia
Pers Con
Contact
Con Id
Con Addrline1
Con Addrline2
Con Addrline3
Con AddrLine4
Con Addrline5
Con City Town
Con Province State
Con Postal code
Con URI
Region code
Country Code
Telephone
Fax
E Mail
Research Interest
Res Int Language
Res Int Trans Type
Res Int Keywords
CV
CV Id
CV URI
Pers CV
Expertise Skills Name
Exp Skill Name Language
Exp Skill Name Trans Type
Exp Skill Name
Exp Skill Name
Pers Expertise
Person
Per Id
Per Family Names
Per First Names
Per Other Names
Per Sex
Per Academic Title
Per Qualifications
Per Nationalities
Per Prize Award
Per URI
Pers Res Int
Pers OrgUnit
Expertise Skills
Exp Skill Id
Pers Proj
Exp Skill Desc
Expertise Skills Description
Exp Skill Desc Language
Exp Skill desc Trans Type
Exp Skill Description
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
Proj
Proj
Proj
Proj
Proj
OrgUnit
Org Id
Org Acronym
Org Name
Org URI
Org Type
Project
Id
Start Date
End date
URI
Status
20021024
32
Exchange Model and Metadata Model
Metadata Model
• A model which should be a subset of
any CRIS
• Used to provide an entry representing
an instance within the CRIS to a catalog
system
• Used to provide to harvesting engines
(WWW) a description of an instance
within the CRIS
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
33
Exchange Model and Metadata Model
Metadata Model
• A trade-off between
• Enough information for the purposes
– Data exchange
– Catalog providing homogeneous access
over heterogeneous information
• Minimal information
– Commercial exploitation of the full CRIS
represented by the metadata
– Data storage and communications costs
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
34
CONTACT
CON_ID
CON_ADDRLINE1
CON_ADDRLINE2
CON_ADDRLINE3
CON_ADDRLINE4
PERS_CONTACT
CON_ADDRLINE5
PER_ID
CON_CITY_TOWN
CON_ID
CON_PROVINCE_STATE
CON_ID = CON_ID
PER_CON_ROLE
CON_POSTAL_CODE
PER_CON_START
REGION_CODE
PER_CON_END
COUNTRY_CODE
CON_TELEPHONE
CON_FAX
CON_E_MAIL
CON_URI
PER_ID = PER_ID
CERIF Metadata Model
PERSONADDITIONAL
PER_ID
EXPERTISESKILLS
PER_ID = PER_ID
PERSON
PER_ID
PER_FAMILY_NAMES
PER_FIRST_NAMES
PER_OTHER_NAMES
CON_ID = CON_ID
ORG_CONTACT
ORGUNITID
CON_ID
ORG_CON_ROLE
ORG_CON_START
ORG_CON_END
PERS_ORG
PER_ID
ORGUNITID
PER_ORG_ROLE
PER_ID = PER_ID
PER_ORG_START
PER_ORG_END
ORGUNITID = ORGUNITID
ORGUNIT_TYPE
ORG_TYPE
ORGE_TYPE_FULL
PER_ID = PER_ID
ORGUNITID = ORGUNITID
PROJ_PERSON
PROJ_ID
PER_ID
PROJ_PER_ROLE
PROJ_PER_START
PROJ_PER_END
ORGUNIT_TRANSLATION
ORG_LANGUAGE
ORGUNITID
ORGUNIT_NAME
ORG_TY PE = ORG_TY PE
ORGUNITID = ORGUNITID
PROJECT_KEYWORDS
PROJ_KEYW_LANGUAGE
PROJ_ID
PROJ_KEYWORDS
ORGUNITID = ORGUNITID
ORGUNIT
ORGUNITID
ORG_ACRONYM
ORG_TYPE_ORG
ORG_TYPE
ORGUNITID
ORGUNITID = ORGUNITID
PROJ_ID = PROJ_ID
PROJECT
PROJ_ID
PROJ_START_DATE
PROJ_END_DATE
PROJ_STATUS
PROJ_ID = PROJ_ID
PROJ_ORGUNIT
PROJ_ID
ORGUNITID
PROJ_ORG_ROLE
PROG_ORG_START
PROJ_ORG_END
PROJ_ID = PROJ_ID
PROJECT_ABSTRACT
PROJ_ABS_LANGUAGE
PROJ_ID
PROJ_ABSTRACT
© Keith G Jeffery & Anne Asserson
ORGUNITADDITIONAL
ORGUNITID
RESULT_PUBLICATION
RESULT_PATENT
RESULT_PRODUCT
GENERAL_FACILITY
PARTICULAR_EQUIPMENT
SERVICE
PROJECT_TITLE
PROJ_TITLE_LANGUAGE
PROJ_ID
PROJ_TITLE
PROJ_ID = PROJ_ID
PROJ_ID = PROJ_ID
PROJ_ID = PROJ_ID
ORGUNITID = ORGUNITID
PROJECTADDITIONAL
PROJ_ID
RESULT_PUBLICATION
RESULT_PATENT
RESULT_PRODUCT
GENERAL_FACILITY
PARTICULAR_EQUIPMENT
SERVICE
CERIF Course: Data Model 1
20021024
35
Exchange Model and Metadata Model
Metadata Model
• Fewer attributes in base entities
– Person, Project, OrgUnit
•
•
•
•
Full attribute set in Contact
Minimum use of language base tables
Minimum use of lookup tables
Use of ‘X’Additional (where ‘X’ is base
table) to indicate which entities are
present in full CRIS
© Keith G Jeffery & Anne Asserson
CERIF Course: Data Model 1
20021024
36