Fundamental Relational Database Design By Bob Blair Mary

Fundamental Relational Database Design By Bob Blair & Mary Sweeney

The Day l What is Database Design l Tables l Table Relationships l Data Normalization l Data Integrity Levels

Database Design l The efficient organization of real world data into a properly functioning model of a business/organization based on relational database principles and conventions representing current business rules and practices.

Objectives of Good Design l l l Supports Required and ad hoc information retrieval and deletion. Contains efficiently constructed table structures. Since the database follows a well-formulated model, it behaves predictably following all current business rules and practices Imposes data integrity at the field, table, and relationship levels. The database is somewhat self documenting. Changes to the database schema are easy to make Lends itself to future growth.

Advantages of Good Design l Easy to modify and maintain structure. l Easy to modify data. l Easy to retrieve data. l Easy to develop and build user applications.

Types of Databases Operational l Operational databases are used to collect the day-to-day operational data of a business, institution or organization. Data needs to be collected, maintained, and modified. These databases collect dynamic data that changes from day to day.

Types of Databases Analytical l Analytical databases are used to track historical and time-dependent data. Data is used to track trends, view statistical data over long periods of time, or make long-term projections. Data is static, it never or rarely ever needs to be modified. Use static data stored in a data warehouse.

Data Base Design Methods l Requirements l Data Analysis Phase Modeling Phase l Normalization Phase

Good Design l Good database design is like the architect putting their design down on a blue print for the builder to read. It is a well thought out plan that prescribes to good design elements and existing codes.

Northwind Relationship Window (Blue Prints)

Database Design and Tables The example for the Northwind Database relationship window on the previous screen, you will notice is made up of tables and the relationships between those tables. l If the design is like the architects plan the foundation of that design are the tables that will be used to create those relationships l

Database Tables

Tables or Entities (or relations) Tables are also called entities or relations and are often referred to as such in database design literature.

Tables/Entities l Tables are containers that store data about objects or events. – Objects would include: Autos, people, products etc. Any entity that can be described. – Events would include: Doctors Appointments, a sale of a house, the transfer of goods from one warehouse to another, a scientific experiment, the production of a chemical product etc.

Tables/Entities l Tables are made up of columns and rows – Columns are called fields/attributes – Rows are called records/tuples

Rows and Columns Called fields Rows Called records

Tables Columns l Columns are called fields or attributes and contain the smallest values in a database. l Each field value must be atomic, that is broken into its smallest unit i. e. the field Name consisting of both First and Last Names must be broken into two fields First. Name and Last. Name to be atomic.

Atomic Values Field Name New Field First Name Last Name Frank Jones Not Atomic Value Mary Smith Atomic Value Mary Atomic Value Smith Ed Francis

Tables - Rows are called records (or sometimes tuples) and describe a single object or event. l Each row must have a field or combination of fields (columns) that will uniquely identify each record. l This uniquely identifying field or combination of fields is called a Primary Key. l Primary key fields are used to establish relationships between tables AND to establish table level integrity l

Primary Key Fields Employee. ID First. Name Last. Name 12345 Joan Smith 12346 Kim Allen Primary Key or PK uniquely identifies a record

A Table

Table Relationships

Relationships l There kinds are three – One-to-One – One-to-Many – Many-to-Many

Primary and Foreign Keys Primary Key – Uniquely identifies a single record in a table. Primary keys can be one or a combination of fields. A multiple field primary key is called a composite key. l Foreign Key - is a primary key field from table A inserted in table B to create the relationship between the two tables. l

One - to - One l Each record in table A relates to one and only one record in table B and each record in table B relates to one and only one record in table A. l Examples: – Hospital bed to patient – Athlete to locker

One-to-One

One-to-Many l For each record In table A there are none, one or many records that relate in table B l For each record in table B there is one and only one record that relates in table A

One - to - Many Primary Key Foreign Key

Many-to-Many l Many students have many courses and l Many courses have many students

Many - to - Many work. No Is. Willanot. No

Many-to-Many l A join between two tables each containing 1000 records will result 1, 000 combinations form this join. For you mathematicians the result is a Cartesian product. l This is the result of every record in table A relates to every record in table B and every record in table B relates to every record in table A

Many-to-many Solution l The solution is to create two one to many relationships with a Junction, Intersection, Association Table. This table would contain the primary key from each of the two tables in the many-to-many relation.

Intersection or Junction Table

Normalizing Data “The Key, the whole key, and nothing but the key so help me Codd “

Normalization l Is the process of simplifying the design of a database so that it achieves optimum structure.

Normalization l Ensures correctness of the design l Answers the following questions – Is the correct number of tables specified; are there any hidden tables ? – Are columns set up properly on the proper tables ? – Are the correct table relationships represented ?

Normal Forms l Normalization theory gives us the concept of normal forms to assist in achieving the optimum structure. The normal forms are a linear progression of rules that you apply to your database, with each higher normal form achieving a better, more efficient design.

Normal Forms l First Normal Form l Second Normal Form l Third Normal Form l Boyce Codd Normal Form l Fourth Normal Form l Fifth Normal Form

First Normal Form (1 NF) l. A table is in first normal form (1 FN) if there are no repeating groups

1 NF

1 NF Normalized

1 NF Normalized Practical Example

1 NF Not Normalized

1 NF Normalized

1 NF Practical Use

Second Normal Form (2 NF) l Second Normal Form requires that a table be in first normal form AND every non-key column be fully dependent on the entire primary key. l Hint: This normal form only applies to those tables with a composite primary key! You can ignore those that don’t have composite keys for this step.

l Is 2 NF this in 2 NF ? – Composite PK Order. ID and Order. Item# l The Order. Date and the Customer. ID columns reflect only the Order. ID and not the Combination of Order ID and Order. Item#

2 NF Normalized

Third Normal Form (3 NF) l Third Normal Form requires that every table be in second normal form AND that all non-key columns be mutually independent. l Think of this as: No tables within tables

3 Normal Form (3 NF) l Is this table in 3 NF ?

3 Normal Form (3 NF) l The Product. Description field is not mutually independent. l It is dependent on the Product. ID field for its value.

3 NF Normalized

3 NF l Is this table in 3 NF?

3 NF Normalized l The fields Gross and Net Pay are not mutually independent. They are calculated fields that are dependent on the fields Hours and Rates for data. Therefore they should not be included in the table. (they are NOT mutually independent.

3 NF and Calculated Fields l Rule of thumb – you do not store any data that can be calculated. This saves space in the database.

Normalization and the Real World l There are some times that you will want to break the rules governing normalization of tables, or denormalize tables. This is OK if and only if it will improve the efficiency of you database or application. Example: Splitting tables or combining tables to improve performance l Document why you did what you did.

Calculated Fields l Real world – store calculated field data only if it increases the efficiency of your database.

It is possible to stop here l For most business purposes, you can stop right here. If your tables are truly in 3 NF and you have implemented your table relationships correctly your database should function properly. The Boyce Codd and 4 and 5 NF’s deal special cases rather than something that would cause incorrect or inconsistent data.

Data Integrity l Field level – Choose correct datatypes – Add constraints for: Default value l Ranges (ie. , Price >= 0 and <= 5000. 00) l l Table Level – Primary Key constraint l Relationship Level (between tables) – Foreign key constraint

Querying l Select * from Products; Employeeid, Sales. ID, Firstname, Salesdate from Employees join Sales On Employees. Employeeid = Sales. Employeeid

The End