To be successful you should follow these tasks weekly
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
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
Assignment Extensions
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 is a pivotal week for us. Many very important concepts in Relational Database Management will be introduced this week. It is very important that everyone review the documents posted this week. These documents contain key information relate to concepts that will carry us throughout the remainder of this course. One key concept we will focus on is Data Modeling. We will use the Entity Relationship Modeling Language to illustrate designs for relational databases. In order to create these designs we must understand
Key Concepts that will be introduced this week:
Foreign Key
Cardinality
Functional Dependency
Referential Integrity
ER Diagraming
Chapter 3 is a very comprehensive chapter that contains a significant number of concepts and skills that has to be learned. It will take us a couple of weeks going over these skills and concepts.
In our last face to face class, we did a survey of a relational model while examining the Access School Database. This week we will explore relational database modeling in more depth. This is a topic that we will continue to revisit throughout the remainder of the semester. The Entity Relationship (ER) Model will be the cornerstone of building a functional database. Chapter 3 reviews several types of ER diagrams. It talks about the Chen Model, the Crows Foot Model, and the UML Model. Over the next few weeks we will focus on primarily on two of these models; the Chen Model, the Crows Foot Model. Once again, we will continue fortifying our understanding of writing sing table queries in SQL. 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.
I am hoping that your registration into Dream Spark will be complete by the beginning of this week. In class this week, I will be illustrating the use of Microsoft Visio to create ER Diagrams. I will show how to make both Chen Models, the Crows Foot Models in Visio. Additionally, I will also demonstrate Microsoft Word as a tool to make less sophisticated Chen models if are working from home and for some reason we are not able to get the class registered by this third week of the semester.
This chapters goes back and reviews the fundamental structure of tables as being a grid consisting of rows and columns reflecting the attributes and instances of the table. It describes the concept of functional dependency as being a type of data structure that has a key that determines the rest of the information. This is similar to your account number of a bill determining your profile and billing information. This chapter also talks about the null value representing having nothing or no data in an attribute.
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.
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.
In conclusion 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.
The Chapter reviews the concept of Union, Intersection, Difference and Join. You should begin to study these concepts now. We will not seriously apply these concepts until after the midterm when we start studying multiple table queries. However, you should begin to understand the concepts conceptually now. We will examine this over the next two weeks.
Cardinality is also covered in this chapter. Here is a brief overview of cardinality that is explained in chapter 3.
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.
Here are a few examples. Example 1, consider a database designed to keep track of hospital records. Such a database could have many tables like:
a Doctor table full of doctor information
a Patient table with patient information
a Department table with an entry for each department of the hospital.
In that model:
There is a many-to-many relationship between the records in the doctor table and records in the patient table (Doctors have many patients, and a patient could have several doctors); In M:N relationships a mapping/translation table is created that maps the primary keys of the two entities involved in the M:M relationship. This creates two 1 to many relationships towards the mapping table.
There is a one-to-many relation between the department table and the doctor table (each doctor works for one department, but one department could have many doctors). . When there is a one to one relationship, one typically leaves it intact. It is the desirable cardinality in a relational database
A one-to-one relationship is mostly used to split a table in two in order to optimize access or limit the visibility of some information. In the hospital example, such a relationship could be used to keep apart doctors' personal or administrative information. In data modeling, collections of data elements are grouped into data tables. Each entity has a collection of data elements. In the stable translation method, the entities and relationships are translated into tables. The data tables contain groups of data known as attributes. Tables are linked by a cross referencing system using key fields. A primary key attribute that uniquely identifies an instance in the table assigns that field's special order to a table: for example, the Doctor ID field might be assigned as the primary key of the Doctor table. Primary Keys are supposed to be unique. People can have same last name. A table can also have a foreign key which indicates that that field is linked to the primary key of another table. When there is a one to one relationship, one of two actions typically occur. The first is to combine the two tables into a single table. The second is to keep the two tables separated. When this action is taken one table becomes the primary key table and the other becomes the foreign key table. The most frequently accessed table is typically assigned as the primary key table in the relationship
Finally, this chapter revisits the two main concepts that we have been covering since chapter 1, the concepts of the primary key and the foreign key.
Primary Keys
We are beginning to start working on the process of creating ER diagrams and translating those diagrams into a relational schema and metadata that will ultimately be used to create a database. In an ER diagram of a data model, one or more unique identifiers may be declared for each data entity to uniquely identify an instance in the entity. Typically, entities in a relational model will become tables in the database. The set of unique attributes declared for a data entity is often referred to as the candidate keys for that data entity. Thinks like social security number, your driver’s license number, your school identification number are examples of attributes that can uniquely identify an instance of you in a table. These can be considered as candidate keys that may become a primary key. From the set of candidate keys, a single unique key is selected and declared the primary key for that data entity. Each unique key may be composed from one or more data attributes of that data entity. In an entity relationship diagram, each entity relationship uses a unique key, most often the primary key, of one data entity and copies the unique key data attributes to another data entity to which it relates. This inheritance of the unique key data attributes is referred to as a foreign key and is used to provide data access paths between data entities. Once the data model is instantiated into a database, each data entity usually becomes a database table, unique keys become unique indexes associated with their assigned database tables, and entity relationships become foreign key constraints. The primary key of a relational table uniquely identifies each record in the table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key.
Foreign Keys
We are studying relational database management systems (RDBMS). In the context of relational databases, a foreign key is a referential constraint between two tables. A foreign key is a field in a relational table that matches a unique key of another table. Usually this is the primary key in another table. The foreign key can be used as a bridge to cross-reference tables.
For example, say we have two tables, a CUSTOMER table that includes all customer data for cleaner service, and an ORDER table that includes all customer clothes that they handed over to e processed and cleaned.. The intention here is that all orders must be associated with a customer that is already in the CUSTOMER table. To do this, we will place a foreign key in the ORDER table and have it relate to the primary key of the CUSTOMER table.
The foreign key identifies a column or set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must reference the columns of the primary key or other key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the foreign table cannot contain values that don't exist in the primary table. This way references can be made to link information together when writing and executing queries. This is an essential part of database organization and normalization. Multiple rows in the foreign key table may refer to the same row in the primary key table. Most of the time, it reflects the one (parent table or primary key table) to the many (child table, or foreign table)
Homework Assignments This Week
Visio Tutorial
Single Table Query - Sales Agent
Readings
Chapter 3 in the Textbook
ER Modeling Lecture Notes
MS Visio Tutorial
In Class Example Exercise
ER Diagram Real Estate Practice
Visio Example
Review Chapter 3 Lecture Slides
Discussions for the Week
Week 3 Chapter 3 Discussion 1
Why are entity integrity and referential integrity important in a database?
Week 3 Chapter 3 Discussion 2
What is the difference between a database and a table?
Database Project
None
Weekly Quiz
Complete Week 3 Chapter 3 Quiz Online
Previous Module: Data Models Week 2 - 3 and 4 Lecture Notes a Terms.pdf