CSS 103 Introduction to Databases Module 6 Logical

CSS 103 Introduction to Databases Module – 6 Logical Design – The Relational Model www. gbs-ge. ch Switzerland | Spain | Russia | Saudi Arabia | United Arab Emirates

Logical Design - The Relational Model • Objectives: • describe & use the terms relation, tuple, attribute, domain • describe the various types of keys (primary, candidate, alternate, composite/concatenated) • explain the role of foreign keys • describe how relationships of different cardinality & degree are implemented using foreign keys

Objectives (cont’d) • Describe how supertypes & subtypes are represented using relations • describe how the entity-relationship concept of existence dependency is handled • list 5 properties of relations • transform an ERD to a logically equivalent set of relations

Logical Design • Logical design is the process of transforming the conceptual model (in our case the ERD) into a logical model. (Mc. Fadden, et al. , 2002, p 165)

Relational Model • Relational data model has 3 main components: • data structure - data organised into tables with rows and columns • data manipulation - operations (using SQL language) used to manipulate stored data • data integrity - facilities are included to specify business rules to maintain integrity of data as they are being manipulated

Six Properties of Relations (Mc. Fadden et al. , 2002, p 168) • each relation within a database has a unique name • an entry at each row & column intersection is singlevalued (atomic) • each row is unique • each attribute in a relation has a unique name • sequence of columns from left to right is insignificant • sequence of rows from top to bottom is insignificant

Some Definitions • Relation - named, two-dimensional table of data • • fixed number of columns variable number of rows single, simple value at each intersection of row & column shorthand text notation for its data structure is: EMPLOYEE 1(Emp. ID, Name, Dept. Name, Salary)

Definitions (cont’d) • graphic representation of a relation is shown as: EMPLOYEE 1 Emp. ID Name Dept. Name Salary • Attributes (columns) - data items that are contained in the relation • represent the data items that need to be stored for an entity

More Definitions • Row (tuple) - represents an entity instance • 1 row (tuple) for each relation instance currently in the database (can be zero) • NULL value • an attribute in a relation may not currently have a value associated with it • represents the absence of a value; ie the value is unknown • the primary key cannot be null

Relational Keys • Key • any attribute or group of attributes that can uniquely identify 1 row (tuple) in a relation • Candidate key • any attribute that could act as a key for the relation • often there is more than 1 attribute that could serve this purpose

Relational Keys • Primary Key • the attribute(s) selected to function as the unique identifier for the relation • should never contain more than the absolute minimum number of attributes required to uniquely identify a row • Alternate Keys • attributes that were candidate keys but were not selected to be the primary key

Relational Keys • Composite (Concatenated) Key • when more than 1 attribute of a relation is chosen to function as the primary key; eg FLIGHT(Flight. ID, Date, Passenger. No)

Relational Keys • Foreign Key • attribute in a relation of the database that serves as the primary key of another relation within the database • occurs when relationships between 2 tables (relations) must be represented EMPLOYEE 1(Emp. ID, Name, Dept. Name, Salary) DEPARTMENT(Dept. Name, Location, Fax)

Entity Integrity • Designed to assure that every relation has a primary key with valid data values (guarantees primary key cannot be null) • Entity integrity rule: • No primary key attribute (or component of a primary key attribute) may be null.

Referential Integrity • The relational model defines associations between tables through use of a foreign key

Referential Integrity Rule • If there is a foreign key in a relation, either each foreign key value must match a primary key value in the other relation or else the foreign key value must be null

Operational Constraints • Business rules generally identify operational constraints; eg • “A person can only rent a video if that person has been entered as a customer. ”

Anomalies • 3 type of anomalies (using EMPLOYEE 2 table figure 52(b) p 168 of text as an example): • insertion anomaly - to insert a new row, user must supply values for Emp. ID and Course. Title (concatenated primary key) • user should be able to enter an employee’s information without supplying course data

Anomalies (cont’d) • deletion anomaly - if data for employee number 140 are deleted, then – • information about the course the employee completed will be lost • the fact that the course offering completed on 12/8/199 x will also be lost

Anomalies (cont’d) • modification anomaly - if employee number 100 gets a salary increase, more than 1 row of the table needs to be updated to reflect this increase or the data in the relation will be inconsistent

Transforming EERD into Relations • Logical design involves transforming entity-relationship (and Extended ERD) diagrams developed during conceptual design into relational database schemas • process has a set of well-defined rules (steps) to complete the conversion

Step 1: Map Regular Entities • Recall that regular entities have independent existence • Each regular entity is transformed into a relation • name given to the relation is usually that of the entity type • each simple attribute becomes an attribute in the relation • entity type identifier becomes primary key of the relation

Step 1: Map Regular Entities (cont’d) • for composite attributes, only the simple attributes are included in the relation • for multi-valued attributes, 2 new relations are created - • the first relation containing all of the simple attributes • a second relation containing the primary key attribute of the first relation as an attribute & an attribute for the multi-valued attribute • in the second relation these 2 attributes will become a concatenated primary key (see figure 5 -10 p 177 of text for an example)

Dealing with Multi-Valued Attributes (an example) Name Player. ID Membership Address GOLFER Player. ID Name Address GOLF_MEMBERSHIP Player. ID Membership This relation will have 1 row for each club in which a player has membership

Step 2: Map Weak Entities • Recall that a weak entity cannot exist independently • has an identifying owner • does not have its own complete identifier • does have a partial identifier to distinguish among instances

Step 2: Map Weak Entities (cont’d) • For each weak entity: • create a new relation containing - • all the simple attributes of the weak entity • only the simple attributes of any composite attributes • include the primary key of the owner relation as a foreign key attribute • make the primary key from the owner relation & the partial identifier of the weak entity the concatenated primary key for the new relation (see figure 5 -11 p 178 of text)

Step 3: Map Binary Relationships • Procedure for this dependent on relationship degree & cardinalities • Map Binary One-to-Many Relationships: • for each 1: M relationship • first create the two participating entity relations • include the primary key attribute of the 1 relation as an attribute (foreign key) in the M relation (many side) (see figure 5 -12 p 179 of text)

Map Binary Many-to-Many Relationships • For each M: N (many-to-many) relationship • create the relations for the entities • create a new relation - • add the primary keys of the participating entity types as foreign keys • become the concatenated primary key of the new relation • add any non-key attributes associated with the M: N relationship

Map Binary One-to-One Relationships • Special case of 1: M relationships • Create 1 relation for each of the participating entities • add the primary key of the entity type that has mandatory participation to the relation for the entity type that has optional participation in the relationship • add any relationship attributes to the relation with the foreign key (see figure 5 -14 p 181 of text)

Step 4: Map Associative Entities • First create a relation for each participating entity type and the associative entity • include the primary keys of the two regular entity relations as attributes of the associative relation • these keys become the concatenated primary key if the associative relation has no identifier • these keys become foreign keys if the associative relation has an identifier (see figures 5 -15 p 182 & 5 -16 p 183 of text for examples of the above two situations)

Step 5: Map Unary Relationships • Recall that a unary relationship is a relationship between instances of a single entity type • Unary 1: M Relationships: • create a relation for the entity type • within the relation you just created add the primary key attribute as a foreign key attribute (needs to have a different name) (see figure 5 -17 p 184 of text)

Unary M: N Relationships • First create 2 relations • one to represent the entity type in the relationship • one to represent the M: N relationship • Associative relation has primary key consisting of 2 attributes (with different names) that both take their values from primary key of the relation created for the entity type • add any nonkey attributes of the relationship to the associative relation (see figure 5 -18 p 185 of text)

Step 7: Map Supertype/ Subtype Relationships • These relationship types are not directly supported by relational data model • Most common strategy employed is: • create a separate relation for the supertype and each subtype • assign the attributes common to all subtypes to the supertype • for each subtype add the primary key of the supertype & attributes unique to itself • assign one (maybe more) attributes of the supertype to function as subtype discriminator (see figures 5 -20 & 5 -21 p 188 of text)

Summary • The objective of logical design is to transform the conceptual model (ERD or EERD) into a logical data model with wellstructured relations that minimise redundancies & inconsistencies (anomalies) • Integrity constraints assist in maintaining the accuracy & integrity of data in the resulting database

Summary (cont’d) • Each entity type & relationship in the entity-relationship diagram is mapped into corresponding relations • This mapping process has a well-defined sequence of steps that identify how to map each entity & relationship type into wellstructured relations

Summary (cont’d) • Supertype/subtype relationships are not directly supported by the relational data model but, • can be represented by creating a relation for the supertype & each subtype and including a subtype discriminator attribute in the supertype