1 / 24

The Relational Model

The Relational Model. Week 2, Day 1 (based on Ch 3 of Connolly and Begg). The Relational Model - Outline. Alternative Terms Database Schema Relational Database Properties Relational Keys Relational Integrity Views Relationships. Alternative Terms.

aqua
Télécharger la présentation

The Relational Model

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. The Relational Model Week 2, Day 1 (based on Ch 3 of Connolly and Begg) CMPT 355 Sept-Dec 2010 - w2d1

  2. The Relational Model - Outline • Alternative Terms • Database Schema • Relational Database Properties • Relational Keys • Relational Integrity • Views • Relationships CMPT 355 Sept-Dec 2010 - w2d1

  3. Alternative Terms Instructor’s rationalizations for preferred jargon • Relations can be confused with relationships • Files can be confused with the physical file that stores a database • Tuple is just plain weird jargon • Row and column are often mixed up • Field is increasingly being associated with physical layout design Text p74 Section 3.2.1 CMPT 355 Sept-Dec 2010 - w2d1

  4. Database Schema • Each relation/table/file is defined by its own schema • The set of all these schema for a given database is referred to as (guess what) the database schema (duh!) Text p76 Section 3.2.3 CMPT 355 Sept-Dec 2010 - w2d1

  5. Relational Database Properties • Naming • Each table must have a name that is distinct from all other names in the database’s schema • Each attribute has a distinct name (within its own table) • where the same name is used for attributes in different tables, it should refer to the same attribute Text p77 Section 3.2.4 CMPT 355 Sept-Dec 2010 - w2d1

  6. R. Database Properties – (Cont) • Ordering • Tables are NOT indexed arrays • The order of attributes in a record has no significance • The order of records has no significance, theoretically. • (However, in practice, the order may affect the efficiency of accessing records – but we won’t get into that or how to tune a db.) Text p77 Section 3.2.4 CMPT 355 Sept-Dec 2010 - w2d1

  7. R. Database Properties – (Cont) • Contents • Each record is (must be) distinct, • There are no duplicate records in a relational database • You could never get to duplicates (if they existed) • Each cell of a table contains exactly one atomic (single) value • This can be a value that can be used to find • multiple additional records in some other tables • The values of an attribute must all be • from the same domain • (of the same data type) Text p77 Section 3.2.4 CMPT 355 Sept-Dec 2010 - w2d1

  8. Relational Keys A key is an attribute, or set of attributes, that uniquely identifies records within a table • Candidate key • A key such that no proper subset is a key within the table • Primary key • An attribute, or set of attributes, that is selected to identify records uniquely within a table • Alternate key • A candidate key that has not been selected as the primary key • Foreign key • An attribute, or set of attributes, within one table that matches the candidate key of some (possibly the same) relation Text p78-79 Section 3.2.5 CMPT 355 Sept-Dec 2010 - w2d1

  9. Relational Integrity • Nulls • represent a value for an attribute that is currently unknown or is not applicable to this record • Are different from 0 or “ ” (a space) • Entity Integrity • No (attribute that is) part of a primary key be can be null • Referential Integrity • A foreign key must match a candidate key in its home table or all parts of the foreign key value must be null • Enterprise Constraints • Are additional rules specified by the users or DBAs of a database Text p81-83 Section 3.3 CMPT 355 Sept-Dec 2010 - w2d1

  10. Views A view is a virtual record derived from one or more other records • A view may limit access to only part of a record • A view may combine data from multiple records that are related to one another • A view may both combine and limit access to data CMPT 355 Sept-Dec 2010 - w2d1

  11. Views – (cont) Views are primarily for accessing data • Only views limited to individual records in a single table can be used to update the database • Updates are not allowed for views combining multiple records (from one or more table) since the system cannot guess how to break the combined update into a set of suitable individual updates. CMPT 355 Sept-Dec 2010 - w2d1

  12. Representing Relationships • Records may be related to each other in various ways • 1:1 • 1:n • n:1 • n:n CMPT 355 Sept-Dec 2010 - w2d1

  13. Representing 1:1 Relationships 1:1 Relationships • E.g. • each employee in an organization has • personal information • organizational information • In this case • all the data should be combined into a single database record • multiple views can be used for subsets of the record CMPT 355 Sept-Dec 2010 - w2d1

  14. Representing 1:1 Relationships 1:1 Relationships (cont.) Personal information Organizational information Employee information combined record Using individual views for each of personal and organizational information CMPT 355 Sept-Dec 2010 - w2d1

  15. Representing 1:n Relationships 1:n Relationships • E.g. • a sales slip has multiple lines of items being purchased • You can’t have multiple lines of items in an attribute • Remember that “each cell of a table contains exactly one atomic (single) value” • This requires that the “n” pieces of information each have their own record • These “n” records can have composite keys composed of the sales slip number/key plus a line number CMPT 355 Sept-Dec 2010 - w2d1

  16. Representing 1:n Relationships 1:n Relationships (cont.) sales slip has multiple lines of items being purchased CMPT 355 Sept-Dec 2010 - w2d1

  17. Representing n:1 Relationships N:1 Relationships • E.g. • multiple sales slips have the same customer • You can use the customer record’s key as a foreign key in the sales slip record CMPT 355 Sept-Dec 2010 - w2d1

  18. Representing n:1 Relationships N:1 Relationships (cont.) multiple sales slips have the same customer You can use the customer record’s key as a foreign key in the sales slip record CMPT 355 Sept-Dec 2010 - w2d1

  19. Representing n:n Relationships n:n Relationships • E.g. • Multiple customers have purchased multiple items • Multiple employees each have multiple skills • You can’t represent n:n relationships using single valued attributes in either of the records • Remember that “each cell of a table contains exactly one atomic (single) value” • The typical way of dealing with n:n relationships is to replace them with a pair of n:1 and 1:n relationships • However, you also don’t want to introduce redundant data into your database CMPT 355 Sept-Dec 2010 - w2d1

  20. Representing n:n Relationships n:n Relationships (cont.) • E.g. 1 • Multiple customers have purchased multiple items • Here we actually have two cases of 1:n • A customer purchases n items • An item was purchased by n customers • The easiest way to deal with this problem is to create a view using the keys of both records • This view can be searched using part of the key • E.g. = the customer key + the item key CMPT 355 Sept-Dec 2010 - w2d1

  21. Representing n:n Relationships n:n Relationships (cont.) multiple customers (in different sales) have purchased multiple items CMPT 355 Sept-Dec 2010 - w2d1

  22. Representing n:n Relationships n:n Relationships (cont.) can be accessed by a view containing the combined keys CMPT 355 Sept-Dec 2010 - w2d1

  23. Representing n:n Relationships n:n Relationships • E.g. 2 • Multiple employees each have multiple skills • Here we actually have two cases of 1:n • An employee may have multiple skills • A skill may be possessed by multiple employees • And we don’t have any intermediary record (like sales slips) • All we need is to do is to represent the relationship in one direction • The relational database can use this to find both 1:n relationships CMPT 355 Sept-Dec 2010 - w2d1

  24. Representing n:n Relationships n:n Relationships (cont.) multiple employees multiple skills a 1:n table of employees and their skills • We don’t need the skill table if we use their names without codes CMPT 355 Sept-Dec 2010 - w2d1

More Related