Relational DB Components MMG 508 Conceptual DB Design

Relational DB Components MMG 508

Conceptual DB Design Components l To get started, look at the Conceptual Database Diagram on page 26 of the text, and on the next slide. l Components Entities l Attributes l Relationships l

Movie Rental Conceptual Design

Relationship Types l One-to-One l Relationships The generic definition of 1: 1 relationships is: This is an association where an instance of one entity can be associated with at most one instance of the other entity and vice versa l These are rare and anomalies l These are normally corrected by combining the entities l Figure 2 -2 on page 29 applies l

One-to-One Conditional Figure on next slide shows a different “flavor” of one-to-one relationship that is optional (some say conditional) in both directions l The dealership issues automobiles to some employees, typically sales staff, for them to drive for a finite period. l We can read the relationship between the Employee and Automobile entities as follows: l l l “At any point in time, each employee can have zero or one automobiles issued to him or her, and each automobile can be assigned to zero or one employee. ” Note the clause “At any point in time. ” If an automobile is taken back from one employee and then reassigned to another, this would still be a one-to-one relationship, albeit a transferable one. This is because when we consider relationships, we always think in terms of a snapshot taken at an arbitrary point in time.

One-to-One Conditional (Cont’d)

Relationship Types (Cont’d) l One-to-Many relationships (1: m) Basic definition states that the 1: m relationship is an association between two entities where any instance of the first entity may be associated with one or more instances of the second any instance of the second entity may be associated with at most one instance of the first l See next slide l

One-to-Many (1: M) Diagram

Relationship Types (Cont’d) l Many-to-Many Relationships (n: m) l Defined as an association between two entities where any instance of the first entity may be associated with zero, one, or more instances of the second and vice versa. l Look at Figure 2 -1, p. 26 (and next slide) l Most common relationship

Many-to-Many (M: N) Relationship

Relationship Types (Cont’d) l Recursive Relationships Can be 1: 1; 1: n, n: m relationships l Are relationships within one entity l See Figure 2 -4 p. 32 l

Business Rules l These are policies mandated by the organization l Look at Figure 2 -1 on page 26 l This Business Rule states that “Customers with overdue amounts may not book new orders” l Can be manually enforced l Can be enforced via program coding

Tables l This is the primary unit of storage for any RDBMS and is two dimensional l Relational tables are logical storage structures and usually do not exist in tabular form in the physical layer l Tables have unique names assigned by the DBA l See Figure 2 -5 on page 34

Table Attributes l Table attributes are: Columns l Data types l Constraints l Primary Key constraints l Referential constraints l l l e. g. foreign keys Intersection Tables (p. 42)

Integrity Constraints l Integrity l NOT NULL constraints l l Constraints Essentially, not null is used where a specific column cannot be blank CHECK constraints Used for value validation in columns l See p. 44 for example – also Figure 2 -11 l

Views l. A View is a virtual table This is a logical table which give a user access to specific columns in a table or tables l MS Access view illustrated on p. 46, Figure 2 -12 l