The Relational Model

The Relational Model Week 2, Day 1 (based on Ch 3 of Connolly and Begg) CMPT 355 Sept-Dec 2010 - w2d1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The Relational Model