Week 6 Overview

Normalization

read the weekly overview
read the lecture notes posted in weekly modules
participate in the weekly discussion forums
review the supplement documents and example code posted in the weekly modules
attempt the weekly practice exercise
complete the weekly assignment
complete the weekly quiz

Highlights and Outcomes This Week

Normalizing a Relational Schema

What normalization is and what role it plays in the database design process

About the normal forms 1NF, 2NF, 3NF

How normal forms can be transformed from lower normal forms to higher normal forms.

That normalization and ER modeling are used concurrently to produce a good database design

That some situations require denormalization to generate information efficiently

Homework

Normalizing a Relational Schema

Readings

Chapter 6 in the Textbook

Read the document on cardinality, this will assist you in this week's discussion forums

Example Exercise

We will continue to use SQL script file to introduce applied sql this week

Discussions for the Week

Week 6 Chapter 6 Discussion 1

Describe the concept of the Multi-Tiered Topology as it relates to the modern database management system.

Week 6 Chapter 6 Discussion

Discuss your understanding of second and third normal forms. Provide an example of each of these.

Database Project

Announced during lecture

Weekly Quiz

Weekly Quiz for Week 6 Chapter 6

Important Reminder

The correct process for responding to discussions is to first post your own reply to the discussion topic. Other participant’s replies will not be visible until you post your first individual reply. Second, you must post a brief reply to at least two other students’ replies. This along with a relevant individual reply will give you full credit in discussion assignments

To provide additional flexibility for students that do not have their own personal computer resources, I am extending the deadline for assignment submissions from Sundays at midnight to Mondays at midnight.

Highlights This Week

This week continues the pivotal process of understanding relational modeling and diagramming databases using ER diagrams. We continue the effort of reinforcing very important concepts in Relational Database Management. We will continue to use the Entity Relationship Modeling Language to illustrate designs for relational databases.In addition to this, we will introduce the concept of normalizing a relational schema.

The major focus of this chapter is adding more semantic constructs and clarity to the Translated ER Diagram, the conceptual construct of the database. The translated ER Diagram becomes the schema, the logical diagram of the database.

This chapter the reinforcement of the importance of the primary key. The primary key is the most important characteristic of a database. It is from a single attribute or some combination of attributes. The main purpose of the primary key is to guarantee entity integrity. A primary key uniquely identifies an instance in a table. Primary keys and foreign keys work together to implement relationships and enforce referential integrity, a topic we will shortly discuss during lecture in detail.

We continue to reinforce that data modeling and design requires skills acquired through experience. You will not be a great database designer in the beginning. You will learn through iterative experiences. Experience is acquired through practice. That is why we continue to practice these skills every week. The four special design cases are: the importance of flexible design, proper identification of primary keys, the placement of foreign keys. Foreign keys work with primary keys to properly implement relationships in relational model . You always put the primary key of the “one” side on the “many” and the foreign key on the many side. The primary key in the parent entity and the foreign key is the dependent entity.

In 1:1 relationship, there are several options: place a foreign key in both entities however, this is not really recommended, place a foreign key in one of the entities, the one least used is recommended, or combine the entities into a single entity.

Functional Dependency

Functional Dependency

By definition, a functional dependency is a constraint between two sets of attributes in a relation from a database. The "function" being discussed in "functional dependency" is the function of identification of related information. The determination of functional dependencies is an important part of designing databases in the relational model. A functional dependency occurs when one attribute uniquely determines another attribute in a relation or one attribute in a relation uniquely determines another attribute in another relation. This can be written A -> B which would be the same as stating "B is functionally dependent upon A."

Examples of functional dependency:

In a table listing employee attributes including Social Security Number (SSN) and name, it can be said that name is functionally dependent upon SSN (or SSN -> name) because an employee's name can be uniquely determined from their SSN. However, the reverse statement (name -> SSN) is not true because more than one employee can have the same name but different SSNs. an employee name cannot distinguish the SSN because more than one employee could have the same name.

Referential Integrity

The very important concept of Referential Integrity is covered in this chapter.

Referential integrity is a property of data in a relational database which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table). This means that when there is a primary and foreign key cross reference system, the primary key record must exist before the foreign key record can be inserted into the foreign key table. We will discuss this during lecture this week.

For referential integrity to hold in a relational database, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. This means that you cannot delete a primary key record while there are still records in the foreign key table point to the primary key row.

Some relational database management systems (RDBMS) can enforce referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. When the database deletes the foreign key records automatically when the primary key is removed, the process is called a cascade delete. This is not encouraged for the beginner working with databases. Typically a database will not perform a cascade deleted unless explicitly given the command. Usually the delete will not be executed if the system detects a referential integrity constraint violation.

Cardinality

In data modeling, the cardinality of one data table with respect to another data table is a critical aspect of database design. Relationships between data tables define cardinality when explaining how each table links to another. In the relational model, tables can be related as any of: many-to-many, many-to-one (rev. one-to-many), or one-to-one. These particular aspects are said to be the cardinality of a given table in relation to another.

This Week

This week we explore normalizing a relational schema.

Chapter six identifies a variety of normalization forms. Each successive form relies on the successful implementation of the previous form. Second normal form requires the successful implemtation of firt normal form. Third normal form requires the successful implementation of second normal form. Chapter 6 discusses various forms of normalization, in this class we will examine normalization forms 1 through 3. However, we will limit ourselves to implementing first normal form.

We will continue exploring relational database modeling in more depth. Once again this week, we will continue fortifying our understanding of writing single table queries in SQL. We will review correct queries for last week’s information requests on the Agent Table. Over the next several weeks we will begin to add additional modifiers to the single table SELECT query statements to add additional criteria to the selection process when the query is executing. This week we will to explore using MySQL and execute real time queries.

Remember, relationships can have degrees. The degree of a relationship indicates number of entities or participants associated with a relationship.

There can be a unary relationship. This is when the association or relationship is maintained within single entity. In lecture it was discussed that if there is an employee entity that a supervisor can supervise other employees in the employee entity.

There can be Binary Relationships. This is when two entities are associated. In lectured it was discussed that there can be a relationship between an employee and the department that the employee is in. The example that a customer can have many accounts was also discussed. This is the relationship between the entity customer and the entity account.

There can be Ternary Relationship. This is when three or more entities are associated. In lecture it was discussed that there can be a relationship between a Project Entity, a Supplier Entity, and a Supplies Entity. In practice however, it is preferred to have many binary relationships

The textbook discusses Associative (Composite) Entities. These are more commonly known as bridge entities or mapping tables. These are used to implement M:N relationships between two entities. Typically the primary key of his entity is composed of the primary keys of each of the
entities to be connected. This is a composite key. Typically, it also contains additional descriptive attributes that play no role in connective process between the two entities.

If referential integrity is being enforced in a database system, a primary key row cannot be removed from a table in referential key rows from another tables are associated with the primary key record. Additionally, a foreign key row cannot be inserted into a foreign key table is the primary key row is not inserted into the primary key table first. In the context of our discussions, a customer cannot be deleted from the cleaner’s database while the customer has material not picked up. A person accepting articles of clothing from a customer cannot input the each article of clothing for the customer, if the customer’s information has not been entered first. Design is of little value unless it delivers all specified query and reporting requirements. Some design and implementation problems do not yield “clean” solutions.

Homework Assignments This Week

Normalization of a Relational Scema

Readings

Chapter 6 in the Textbook

In Class Example Exercise

Normalization Example Practice