1 / 15

Integrating Relational Databases in SCONE: A Case Study Presentation

This presentation, delivered by Gordon Dunsire to CDL staff on Wed, Feb 5, 2003, explores the practical application of relational database theory using the SCONE project as a case study. It covers essential concepts such as collection-level description, entity-relationship models, and the implementation stages of mapping entities and relationships to relational database tables. The presentation emphasizes SQL for data retrieval, discusses attribute sets, and highlights the importance of effective database design. Gain insights into how SCONE utilizes a model developed by Michael Heaney for efficient data management.

cecily
Télécharger la présentation

Integrating Relational Databases in SCONE: A Case Study Presentation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. From model to service: relational databases and SCONE Presentation to CDLR staff on Wed 5 Feb 2003 By Gordon Dunsire

  2. Overview • From theory to practice • A bit about collection level description • A bit about relational database design • A bit about entity-relationship models • Not necessarily in that order! • But using SCONE as a case-study

  3. Implementation stages • Entity-relationship model • Attribute sets • Entities & relationships map to relational database tables • Attributes map to fields (columns) in table • Tables related using standard rdbms structures • Service data retrieved using SQL

  4. Entity-Relationship model • Defines entities (things) and relationships between them • Entity-Relationship-Entity syntax • Defines attributes (pieces of information) common to each entity and relationship • SCONE is based on a model developed by Michael Heaney for UKOLN

  5. Heaney’s Analytic Model

  6. Attribute sets • Entity: Location (physical repository) • Place; Identifier • Entity: Agent:Collector (person) • Name; Date; Biography • Relationship: Collects • Legal status; Accrual policy • Relationship: Administers • Access conditions

  7. Implementation in RDBMS • Map entities and relationships to tables • Entity Location (physical repository) maps to table LocationPhys • Relationship Collects maps to table Collects • Map attribute sets to fields in each table • Decomposition to finer detail data elements where appropriate • E.g. Location – place decomposes to building name, address, town, region, postcode, etc.

  8. Example: LocationPhys • Table fields from attributes • Address1 • Address2 • Address3 • Postcode • Plus fields from “experience tells me” attributes • Notes

  9. Example: LocationPhys • Plus fields for administrative metadata • LastDate • Plus fields for RDBMS structure • LocationPhysID (primary key) • TownID (secondary, foreign key) • Maps attribute Town to a lookup table (Town treated as an entity) matched by keys

  10. Relational databases LocationPhys Address1 Address2 Address3 TownID Postcode LocationPhysID LastDate Etc. Town Name TownID LastDate

  11. Relationships (model) Agent [AgentPers] Forename Surname Dates … AgentID … Administers AccessOpenHours … AdministersID … AgentID LocationID Location [LocationPhys] Address1 Address2 Address3 TownID … LocationID …

  12. Terminological inexactitudes • Relationships (model) are NOT the same as relationships (RDBMS) • But can often appear so! • Relationship (m) requires its own table (mostly); relationship (r) is the link between tables • Can sometimes simplify so relationship (m) is implicitly defined by relationship (r), but NOT if relationship (m) has its own attributes

  13. Assembling the record • Use Structured Query Language (SQL) to request fields from related tables matching specified criteria • E.g. to get location town and postcode: • SELECT Town.Name, LocationPhys.Postcode FROM LocationPhys INNER JOIN Town on Town.TownID=LocationPhys.TownID WHERE LocationPhys.LocationPhysID=23

  14. SCONE Service • Web page design and content by DreamWeaver • RDBMS data added to page content dynamically by ColdFusion • Fully integrated environment with DW • SQL scripts • Data processing (program flow) scripts • Data stored in SQL Server

  15. Views • SCONE data retrieved in different ways for different purposes using SQL • Data is stored once, used many times • Data updates are immediately visible • Data updated once, visible in many places • Single RDBMS supports multiple, overlapping services: • SCONE, SCAMP, SLIR (SWOP, ESH), RCO, CAIRNS, Cultural portal

More Related