Database Design Fundamentals (Mid Module 2) PDF

2 Database Design Fundamentals Assi.Prof. JOHN MICHAEL LEGASPI Faculty, Computer and Electronics Department  Objectives Understand the terms entity, attribute, and relationship Understand the terms relation and relational database Understand functional de...

2 Database Design Fundamentals Assi.Prof. JOHN MICHAEL LEGASPI Faculty, Computer and Electronics Department  Objectives Understand the terms entity, attribute, and relationship Understand the terms relation and relational database Understand functional dependence and be able to identify when one column is functionally dependent on another Understand the term primary key and identify primary keys in tables  Objectives (continued) Design a database to satisfy a set of requirements Convert an unnormalized relation to first normal form Convert tables from first normal form to second normal form Convert tables from second normal form to third normal form Create an entity-relationship diagram to represent the design of a database  Introduction Database design: process of determining the particular tables and columns that will comprise a database Must understand database concepts Process of normalization  Database Concepts Entity Attribute Relationship Functional dependence Primary key  Relational Database A collection of tables Relational Database (continued)  Entities, Attributes, and Relationships Entity (like a noun): person, place, thing or event Attribute (like an adjective or adverb): property of an entity Relationship: association between entities  Entities, Attributes, and Relationships (continued) One-to-many relationship: – One rep is related to many customers – Implement by having a common column in two or more tables Repeating groups: multiple entries in an individual location  Entities, Attributes, and Relationships (continued) A relation is a two-dimensional table: – Entries in the table are single-valued – Each column has a distinct name – All values in a column are values of the same attribute – The order of the columns is immaterial – Each row is distinct – The order of the rows is immaterial  Entities, Attributes, and Relationships (continued) Use shorthand representation to show tables and columns REP (REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, ZIP, COMMISSION, RATE) CUSTOMER (CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, REP_NUM) ORDERS (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM) ORDER_LINE (ORDER_NUM, PART_NUM, NUM_ORDERED, QUOTED_PRICE) PART (PART_NUM, DESCRIPTION, ON_HAND, CLASS, WAREHOUSE, PRICE)  Functional Dependence An attribute, B, is functionally dependent on another attribute (or collection), A, if a value for A determines a single value for B at any one time Functional dependencies cannot determine from sample data; must know the users’ policies Functional Dependence  Primary Keys Unique identifier for a table Column (attribute) A (or a collection of columns) is the for a table (relation) R if: – Property 1: all columns in R are functionally dependent on A – Property 2: no subcollection of the columns in A (assuming that A is a collection of columns and not just a single column) also has Property 1  Database Design Design a database given a set of requirements that database must support Requirements gathered through a process known as systems analysis  Design Method Read requirements, identify entities (objects) involved, and name the entities. Identify unique identifiers for entities identified above Identify the attributes for all entities Identify functional dependencies that exist among attributes Use functional dependencies to identify tables by placing each attribute with attribute or minimum combination of attributes on which it is functionally dependent Identify any relationships between tables Database Design Requirements For Premiere Products: – Store data about sales reps, customers, parts, orders, and order line items – Must enforce certain constraints; for example: There is only customer per order Quoted price may differ from actual price Database Design Process Example Apply requirements to six steps in design method  Normalization Identify the existence of potential problems Provides a method for correcting problems Goal: convert unnormalized relations (tables that contain repeating groups) into various types of normal forms  Normalization (continued) First normal form (1 NF): better than unnormalized Second normal form (2 NF): better than 1 NF Third normal form (3 NF): better than 2 NF  First Normal Form (1NF) A relation is in first normal form (1NF) if it does not contain any repeating groups To convert an unnormalized relation to 1NF: expand PK to include PK of repeating group (effectively eliminating the repeating group from the relation)  Second Normal Form Redundancy causes problems Update anomalies – Update – Inconsistent data – Additions – Deletions  Second Normal Form (continued) A relation is in second normal form (2NF) if it is in 1NF and no nonkey attribute is dependent on only a portion of the primary key Or: all nonkey attributes are functionally dependent on entire primary key  Second Normal Form (continued) A 1NF relation with a primary key that is a single field is in 2NF automatically  Third Normal Form Update anomalies still possible Determinant: an attribute (or collection) that functionally determines another attribute  Third Normal Form (continued) A relation is in third normal form (3NF) if it is in 2NF and the only determinants it contains are candidate keys Boyce-Codd normal form (BCNF) is the true name for this version of 3NF  Diagrams for Database Design Graphical illustration Entity-relationship (E-R) diagram: – Rectangles represent entities – Arrows represent relationships Diagrams for Database Design (continued) Diagrams for Database Design (continued) Diagrams for Database Design (continued)  Summary Definitions – Entity – Attribute – Relationship – Relation – Functional dependence – Primary key Database Design Method  Summary (continued) Normalization Unnormalized (repeating groups) First normal form (INF) Second normal form (2NF) Third normal form (3NF) Entity-relationship (E-R) diagram