Download Logical Database Design and Relational Model, Physical Database Design | MIS 421 and more Assignments Deductive Database Systems in PDF only on Docsity!
Part III: Database Design
l Chapter 6: Logical Database Design and the Relational Model l Chapter 7: Physical Database Design
Chapter 6:
Logical Database Design and
the Relational Data Model
Basic Definitions
l Three components of the Relational Data Model l Data Structure l tables with rows and columns l Data Manipulation l operations used to manipulate (e.g., create, read, update, delete) data stored in the relations l Data Integrity l business rules that maintain the integrity of the data when they are manipulated
Relational Data Structure
l Relation: a named two-dimensional table
l … a set of named columns l … an arbitrary number of unnamed rows l An attribute is a named column of the relation l Each row of a relation corresponds to a record that contains attribute values for a single entity WORKER (WORKER-ID, NAME, HOURLY-RATE, SKILL-TYPE, SUPV-ID)
Keys
l Primary key: a set of attributes that uniquely identifies each row in a relation l Composite key: more than one attribute l Candidate key: any set of attributes WORKER (WORKER-ID, NAME, HOURLY-RATE, SKILL-TYPE, SUPV-ID)
Foreign Keys
l Foreign keys: l a set of attributes in one relation that constitutes a key in some other (or possibly the same) relation
l Referential integrity: the value of a non-null foreign key must be an actual key value in some relation l Operational constraints: business rules for which logic must be embedded in the system
SQL Table Definitions
CREATE TABLE CUSTOMER (CUSTOMER_ID VARCHAR(5) NOT NULL, CUSTOMER_NAME VARCHAR(25) NOT NULL, CUSTOMER_ADDRESS VARCHAR(30) NOT NULL, PRIMARY KEY (CUSTOMER_ID)); CREATE TABLE ORDER (ORDER_ID CHAR(5) NOT NULL, ORDER_DATE DATE NOT NULL, CUSTOMER_ID VARCHAR(5) NOT NULL, PRIMARY KEY (ORDER_ID), FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)); CREATE TABLE ORDER_LINE (ORDER_ID CHAR(5) NOT NULL, PRODUCT_ID CHAR(5) NOT NULL, QUANTITY INT NOT NULL, PRIMARY KEY (ORDER_ID, PRODUCT_ID), FOREIGN KEY (ORDER_ID) REFERENCES ORDER(ORDER_ID), FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID)); CREATE TABLE PRODUCT (PRODUCT_ID CHAR(5) NOT NULL, PRODUCT_DESCRIPTION VARCHAR(25), PRODUCT_FINISH VARCHAR(12), UNIT_PRICE DECIMAL(8,2) NOT NULL ON_HAND INT NOT NULL PRIMARY_KEY (PRODUCT_ID));
A Well-Structured Relation
l Contains minimal redundancy and allows users to insert, modify, and delete without errors or inconsistencies l Anomalies: l often occur when relation contains data about two entities
l update l deletion l insertion
Transforming EER Diagrams
into Relations
l Three types of entities: l Regular: independent existence, generally represent real-world objects l Weak: cannot exist except with an identifying relationship l Associative: formed from M:N relationships
Step 1: Map Regular Entities
l Each regular entity type is transformed into a relation l simple attribute Ø attribute l identifier Ø primary key l composite attribute Ø simple component attributes l multivalued attribute Ø two relations (1) attributes of entity except multivalued attribute (2) multivalued attribute
l Identifier assigned: use as a surrogate key l when natural identifier exists l when default does not uniquely identify instances of the associative entity
Step 5: Map Unary Relationships
l Recursive relationships l 1:M l recursive foreign key, same domain as primary key l M:N l create a new relation to represent the M:N relationship l primary key of new relation is primary key plus recursive key
Step 6: Map Ternary Relationships
l Create new associative relation l default primary key is primary keys of participating relations l primary keys of participating relations are also foreign keys
Step 7: Map Supertype/Subtype Relationships
l Create a separate relation for the supertype and each of its subtypes
l Assign the supertype relation common attributes, including primary key l Assign each subtype relation attributes unique to that subtype and the primary key of the supertype l Assign attribute(s) to supertype to serve as subtype discriminator
Normalization
l Normalization: a formal process for deciding which attributes should be grouped together in a relation l Minimizes data redundancy l Increases data integrity
Functional Dependency and Keys
l A constraint between two attributes (or sets of attributes) l A à B means that knowing A, we also know B l A is the determinant l B is functionally dependent on A l Candidate key: attribute (or combination) that uniquely identifies a row in a relation
First Normal Form
l All attribute values must be atomic.
- Combine relations that have the same FD.
Third Normal Form
l Every determinant is a key. BOAT (BOAT-ID, NAME, MODEL, LENGTH, BEAM, SLIP) l What’s the error? l What problems could arise? l transitive dependency: functional dependency between two or more nonkey attributes l calculated l “lookup”
Merging Relations
l View integration problems l synonyms (two names for same attribute) l homonyms (same name for two attributes) l transitive dependencies (merge two 3NF relations) l supertype/subtype relationships (merge two 3NF relations that do not represent the same entity even though they share same key attribute)
