1 / 14

Organisation methods 2: Relational Databases

Organisation methods 2: Relational Databases. CORE 2: Information systems and Databases. Relational Databases.

rosaline-yroz
Download Presentation

Organisation methods 2: Relational Databases

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. Organisation methods 2:Relational Databases CORE 2: Information systems and Databases

  2. Relational Databases • A relational database is a collection of tables that are linked so that changes in relative data can be processed throughout all entities. The actual tables themselves maintain the same internal structure as flat-file databases, using attributes and records to display information. However the way that they are linked follows a few unique structures that may be more effective at organising information in different situations. These relational structures are: one to one, one to many, and many to many.

  3. Relational Databases • This is managed by an RDBMS – Relational Database Management software. • An RDBMS initiates Information Processes on the linked tables as well as processes that create and modify the design of the tables themselves.

  4. Logical organization of Relational Databases • There are some key concepts to grasp in the organization of relational databases…. • TABLES • PRIMARY KEYS • RELATIONSHIPS • REFERENTIAL INTEGRITY

  5. Logical organization of Relational Databases TABLES The basic building block of all relational databases is the table. Few things about ‘tables’… • Each table is a set of rows (records) and columns (fields) • A single table is like a flat file database • Records can be called tuples and fields attributes • Tables are called entities and each row or record describes all of the data about a particular entity • Each record is unique, there are never two identical records

  6. Logical organization of Relational Databases PRIMARY KEYS Every Table within a relational database must have a Primary Key which is a field or combination of fields that uniquely identifies each record. • The Primary Key is either a single field or a combination of fields (a Composite key) • It is typically a single integer field • Must be unique • A Primary Key is joined to a Foreign Key in another table

  7. Logical organization of Relational Databases RELATIONSHIPS Tables are linked together via relationships. A relationship creates a join between the primary key in one table and the foreign key in another. We model these relationships uisng a SCHEMA or ERD (Entity Relationship Diagram) that looks like this…

  8. Logical organization of Relational Databases Or this…

  9. Logical organization of Relational Databases RELATIONSHIPS CONTD… • Database Schemas or schematic diagrams model relationships with a relational database and include each entity (table) together with its attributes (fields). The primary key is underlined. Lines between attributes represent the relationships and each line is labeled to identify the nature of the join. • Foreign Keys are fields that must contain data that matches the Primary Key of another table. Hence datatypes MUST match between PK’s and FK’s.

  10. Logical organization of Relational Databases RELATIONSHIPS CONTD… • There are three types of Relationships… • ONE to MANY (1:M) • ONE to ONE (1:1) • MANY to MANY (M:M)

  11. Logical organization of Relational Databases RELATIONSHIPS CONTD… ONE to MANY (1:M) • One to Many is by far the most common relationship and what it means is that for each record in the PK’s table, there can be multiple records in the FK’s table. • Eg. One Customer can have many products OR one Borrower can have many loans

  12. Logical organization of Relational Databases RELATIONSHIPS CONTD… ONE to ONE (1:1) • One to One relationships are seldom required however there are occasions where they may be included in a DB to improve performance or reduce storage. • Eg. One ‘Office_Worker’can have one ‘Office’ and therefore the relationship can reflect this.

  13. Logical organization of Relational Databases RELATIONSHIPS CONTD… MANY to MANY (M:M) • Many to Many relationships must always be resolved by creating a join table with two 1:M relationships. For example Many ‘Customers’ can have many ‘Loans’ but in reality this is impossible to create using our DBMS. To resolve this where ever we find a need for a M:M we replace it with two 1:M and a join table like a ‘Transaction’ table. • Come to page 132, 133 of your text for further explanation.

  14. Logical organization of Relational Databases REFERENTIAL INTEGRITY Referential integrity refers to when tables are synchronised so that data changes carry over all tables. It is ensured when each FK always matches a related PK. • When a Primary Key is updated then changes must be reflected throughout all tables (MS ACCESS call this ‘Cascade Update Related Fields’) • If a Primary Key is deleted then the orphaned FK’s need to be deleted also (MS ACCESS calls this ‘Cascade Delete Related Fields’)

More Related