Relational Database Design

Bill Woolfolk Public Health Sciences University of Virginia [email protected]

Objectives

   Understand definition of modern relational database Understand and be able to apply a practical method for designing databases Recognize and avoid common pitfalls of database design

What’s a database?

  ◦ ◦ ◦ ◦ A collection of logically-related information stored in a consistent fashion Phone book Bank records (checking statements, etc) Library card catalog Soccer team roster The storage format typically appears to users as some kind of tabular list (table, spreadsheet)

What Does a Database Do?

   Stores information in a highly organized manner Manipulates information in various ways, some of which are not available in other applications or are easier to accomplish with a database Models some real world process or activity through electronic means ◦ Often called modeling a business process ◦ Often replicates the process only in appearance or end result

Databases and the Systems which manage them

 Modern electronic databases are created and managed through means of RDBMS:

R

elational

D

ata

B

ase

M

anagement

S

ystems

  An individual data storage structure created with an RDBMS is typically called a “database” A database and its attendant views, reports, and procedures is called an “application”

Database Applications

   Database (the actual DB with its attendant storage structure) SQL Engine - interprets between the database and the interface/application Interface or application – the part the user gets to see and use

Relational Database Management Systems

 Low-end, proprietary, specific purpose ◦ Email: Outlook, Eudora, Mulberry ◦ Bibliographic: Ref. Mgr., EndNote, ProCite   Mid-level ◦ Microsoft Access, Lotus Approach, Borland’s Paradox ◦ More or less total control of design allows custom builds High-end ◦ Oracle, Microsoft SQL Server, Sybase, IBM DB2 ◦ Professional level DBs: Banks, e-commerce, secure ◦ Amazon.com, Ebay.com, Yahoo.com

Problems with Bad Design

   Early computers were slow and had limited storage capacity Redundant or repeating data slowed operations and took up too much precious storage space Poor design increased chance of data errors, lost or orphaned information

Benefits of Good Design

    Computers today are faster and possess much larger storage devices Rigid structure of modern relational databases helped codify problems and solutions Design problems are still possible, because the DBMS software won’t protect you from poor practices Good design still increases efficiency of data processes, reduces waste of storage, and helps eliminate data entry errors

The Design Process

1) 2) 3) 4) 5) 6) 7) 8) Identify the purpose of the database Review existing data Make a preliminary list of fields Make a preliminary list of tables and enter fields Identify the key fields Draft the table relationships Enter sample data and normalize the data/tables Review and finalize the design

Database Modeling

  Refers to various, more-or-less formal methods for designing a database Some provide precision steps and tools ◦ Ex.: Entity-Relationship (E-R) Modeling  Widely used, especially by high-end database designers who can’t afford to miss things   Fairly complex process Extremely precise

1. Identify purpose of the DB

Clients can tell you what information they want but have no idea what data they need.

    “We need to keep track of inventory” “We need an order entry system” “I need monthly sales reports” “We need to provide our product catalog on the Web” Be sure to Limit the Scope of the database.

2. Review Existing Data

  ◦ ◦ Electronic ◦ Legacy database(s) Spreadsheets Web forms Manual ◦ Paper forms ◦ Receipts and other printed output

3. Make Preliminary Field List

   Make sure fields exist to support needs ◦ Ex. if client wants monthly sales reports, you need a date field for orders.

◦ ◦ Ex. To group employees by division, you need a division identifier Make sure values are atomic ◦ Ex. First and Last names stored separately Ex. Addresses broken down to Street, City, State, etc.

Do not store values that can be calculated from other values ◦ Ex. “Age” can be calculated from “Date of Birth”

4. Make Preliminary Tables

(and insert the fields into them)     Each table holds info about one subject Don’t worry about the quantity of tables Look for logical groupings of information Use a consistent naming convention

Naming Conventions

 ◦ ◦ ◦ ◦ ◦ ◦ Rules of thumb ◦ Table names must be unique in DB; should be plural ◦ Field names must be unique in the table(s) Clearly identify table subject or field data Be as brief as possible Avoid abbreviations and acronyms Use less than 30 characters, Use letters, numbers, underscores (_) Do not use spaces or other special characters

Naming Conventions (cont’d)

 ◦ ◦ Leszynski Naming Convention (LNC) ◦ Example: tblEmployees, qryPartNum tbl, qry = tag Employees, PartNum = basename  LNC at Microsoft Developers Network

5. Identify the Key Fields

   Primary Key(s) ◦ Can never be Null; must hold unique values ◦ Automatically indexed in most RDBMSs ◦ Values rarely (if ever) change ◦ Try to include as few fields as possible Multi-field Primary Key ◦ Combination of two or more fields that uniquely identify an individual record Candidate Key ◦ Field or fields that qualify as a primary key ◦ Important in Third and Boyce-Codd Normal Forms

6. Identify Table Relationships



Based on business rules being modeled



Examples:

◦ “each customer can place many orders” ◦ “all employees belong to a department” ◦ “each TA is assigned to one course”

Relationship Terminology

    Relationship Type ◦ One-to-one: expressed as 1:1 ◦ One-to-Many: expressed as 1:N or 1:M or 1:∞ ◦ Many-to-Many: expressed as N:N or M:M Primary or Parent Table ◦ Table on the left side of 1:N relationship Related or Child Table ◦ Table on the right side of 1:N relationship Relational Schema ◦ Diagram of table relationships in database

Relationship Terminology (cont’d)

   Join ◦ Definition of how related records are returned Join Line ◦ Visual relationship indicators in schema Key fields ◦ Primary Key: the linking field on the one side of a 1:N relationship ◦ Foreign Key: the primary key from one table that is added to another table so the records can be related ◦ Non-Key Fields: any field that is not part of a primary key, multi-field primary key, or foreign key

One-to-One (1:1)

   Each record in Table A relates to one, and only one, record in Table B, and vice versa.

Either table can be considered the Primary, or Parent Table Can usually be combined into one table, although may not be most efficient design

One-to-Many (1:N)

   Each record in Table A may relate to zero, one or many records in Table B, but each record in Table B relates to only one record in Table A.

The potential relationship is what’s important: there might be no related records, or only one, but there could be many.

The table on the One (or left) side of a 1:N relationship is considered the Primary Table.

Many-to-Many (N:N)

  A record in Table A can relate to many records in Table B, and a record in Table B can relate to many records in Table A.

Most RDBMSs do not support N:N relationships, requiring the use of a linking (or intersection or bridge) table that breaks the N:N relationship down into two 1:N relationships with the linking table being on the Many side of both new relationships.

Relational Schema

Table 1 Field1_1

Field1_2 Field1_3 Field1_4 1 N

Table 2 Field2_1

Field1_1 Field2_2 Field2_3

14-3 DATABASE ARCHITECTURE The American National Standards Institute/Standards Planning and Requirements Committee (ANSI/SPARC) has established a three-level architecture for a DBMS: internal , conceptual and external (Figure 14.2).

14.26

Figure 14.2 Database architecture 14.27

Internal level The internal level determines where data is actually stored on the storage devices. This level deals with low-level access methods and how bytes are transferred to and from storage devices. In other words, the internal level interacts directly with the hardware.

Conceptual level The conceptual level defines the logical view of the data.

The data model is defined on this level, and the main functions of the DBMS, such as queries, are also on this level. The DBMS changes the internal view of data to the external view that users need to see. The conceptual level is an intermediary and frees users from dealing with the internal level.

14.28

External level The external level interacts directly with the user (end users or application programs). It changes the data coming from the conceptual level to a format and view that is familiar to the users.

14.29

14-4 DATABASE MODELS A database model defines the logical design of data. The model also describes the relationships between different parts of the data. In the history of database design, three models have been in use: the hierarchical model, the network model and the relational model.

14.30