Chapter6Logicaldatabasedesignandtherelationalmodel.pptVIP

Chapter 6: Logical database design and the relational model Objectives of logical design... Translate the conceptual design into a logical database design that can be implemented on a chosen DBMS Input: conceptual model (ERD) Output: relational schema, normalized relations Resulting database must meet user needs for: Data sharing Ease of access Flexibility Relational database components Data structure Data organized into tables Data manipulation Add, delete, modify, and retrieve using SQL Data integrity Maintained using business rules Why do I need to know this? Mapping conceptual models to relational schema is straight-forward CASE tools can perform many of the steps, but.. Often CASE cannot model complexity of data and relationship (e.G., Ternary relationships, supertype/subtypes) There are times when legitimate alternates must be evaluated You must be able to perform a quality check on CASE tool results Some rules... Every table has a unique name. Attributes in tables have unique names. Every attribute value is atomic. Multi-valued and composite attributes? Every row is unique. The order of the columns is irrelevant. The order of the rows is irrelevant. The key... Relational modeling uses primary keys and foreign keys to maintain relationships Primary keys are typically the unique identifier noted on the conceptual model Foreign keys are the primary key of another entity to which an entity has a relationship Composite keys are primary keys that are made of more than one attribute Weak entities Associative entities Implementing it What about relationships? Constraints Domain constraints Allowable values for an attribute as defined in the domain Entity integrity constraints No primary key attribute may be null Operational constraints Business rules Referential integrity constraints Referential integrity constraint Maintains consistency among rows of two entities matching of primary and foreign keys Enforcement options for deleting instances Restrict Cascade Se