A Guide to MySQL

2 Database Design Fundamentals A Guide to MySQL

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 A Guide to MySQL

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 A Guide to MySQL

Introduction • Database design: process of determining the particular tables and columns that will comprise a database • Must understand database concepts • Process of normalization • Don’t be afraid to make mistakes as long as you are willing to make changes. A Guide to MySQL

Relational Database • A collection of tables • A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. • The relational database was invented by E. F. Codd at IBM in 1970. A Guide to MySQL

Relational Database (continued) A Guide to MySQL

A Guide to MySQL

Entities, Attributes, and Relationships • Entity (like a noun): person, place, thing or event • Attribute (like an adjective or adverb): property of an entity • Describes the entity ex. Fiction may be an attribute of Book • Belongs to the entity ex. Address may be associated with a Person • Relationship: association between entities A Guide to MySQL

Entities, Attributes, and Relationships (continued) • Cardinality: numeric relationships between occurrences of the entities on either end of a relationship line • One-to-one • One-to-many • One rep is related to many customers; but customers have a single rep • Implement by having a common column in two or more tables • Many-to-many A Guide to MySQL

Entities, Attributes, and Relationships (continued) • Repeating groups: multiple entries in an individual location (multiple entries in the same column of a single row) • Repeating groups merely take up space and resources and • Repeating groups add no additional information A Guide to MySQL

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 A Guide to MySQL

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) A Guide to MySQL

Entities, Attributes, and Relationships (continued) • Qualify a name: refers to the process of qualifying a name by concatenating the name of the table with the name of the attribute • It is always acceptable to qualify column names (attributes), even if there is no potential for confusion. • Example: a museum has a database with tables: customer, friends, and employees. How can confusion arise if attributes are not qualified in a discussion of this database? A Guide to MySQL

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 • Example: Compare a retail salesperson’s relationship to a customer with a drug representative visiting a doctor. In either case, explain how the relationship can be one-to-many or many-to-many. • Why are the business rules needed to answer this question? A Guide to MySQL

Functional Dependence A Guide to MySQL

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 A Guide to MySQL

Database Design • Design a database given a set of requirements that database must support • Requirements gathered through a process known as systems analysis • In many cases problems are not obvious at the early design steps • Have someone play devil’s advocate • Try to mentally “run” the data A Guide to MySQL

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 A Guide to MySQL

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 A Guide to MySQL

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 • Is complete normalization necessary in all cases? • Why or why not? • What is the bottom line for design? A Guide to MySQL

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 A Guide to MySQL

First Normal Form (1NF) • A relation is in first normal form (1NF) if it does not contain any repeating groups (multiple entries in a single row) • To convert an unnormalized relation to 1NF: expand PK to include PK of repeating group (effectively eliminating the repeating group from the relation) • Does first normal form indicate anything about cardinality? (Look at the next two slides and come back to this question) A Guide to MySQL

Second Normal Form • Redundancy causes problems • Example a part with multiple descriptions pg. 46/7 • Update anomalies • Update - If one description is changed; it doesn’t change the other description. • Inconsistent data - Part descriptions now disagree. • Additions – What description will be applied to a new part? • Deletions – instantiation vs. storage A Guide to MySQL

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 • If the primary key is a single column of a table in 1NF, then the table is automatically in 2NF. A Guide to MySQL

Third Normal Form • Update anomalies still possible • Determinant: an attribute (or collection) that functionally determines another attribute • 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 A Guide to MySQL

Diagrams for Database Design • Graphical illustration • Entity-relationship (E-R) diagram: • Rectangles represent entities • Arrows represent relationships A Guide to MySQL

Diagrams for Database Design (continued) A Guide to MySQL

A Guide to MySQL

A Guide to MySQL

Presentation Transcript