Identify all entities the manager wants to keep track of. Typical entities are persons, things, places, and events (like transactions).

Identify all entities the manager wants to keep track of. Typical entities are persons, things, places, and events (like transactions).

This is a summary of the process for creating a data model. See the MySongsNow Parts 1 and 2 Tegrity videos, in which I walk you through this process. The PowerPoint file developed in the videos is also posted in this folder. A data model is a representation of real things the organization needs to track.

Identify all entities the manager wants to keep track of. Typical entities are persons, things, places, and events (like transactions).
2.       Describe each entity by creating a list of attributes that describe each entity. These attributes will become fields or columns in your database tables. Just include attributes necessary to this scenario (just the data you will want to keep track of).
3.       Determine the properties for each attribute:
·         Data type (text, numeric, date, etc.)

Length or size (number of  characters for text attributes, the number type for numeric fields like integer, long integer, single precision, double precision, currency, etc.)
·         Required value … whether a value in the attribute is required or optional when entering a new record for this object. This is a common sense technique used to make sure necessary data gets recorded in the database and thereby help to ensure the integrity of the database.

For example, the ‘required’ property for the student T number field in the ATU student database is set to ‘yes’. That means we cannot create a record for a new student if the T number field is blank. For an example closer to your scenario, say that the Customer Last Name attribute of the Customer object has the ‘required’ property set to ‘yes’. In that case once the data model is implemented in a database, they would not be able to add a record for a new customer without entering in a value for last name.

4.       Identify an attribute in each entity, which can act as a unique identifier (eventually a primary key field in a table in a database).
5.       Identify any direct relationships that might exist between various pairs of entities.
6.       Make sure you can actually link any related entities. This means they must have a field in common. If a common field does not exist, place an identifier attribute of one entity into the other entity as an additional attribute. In a database, this means put the primary key field of one table into the other table where it is referred to as a foreign key.

7.       Describe the properties of each relationship. These properties address minimum/maximum cardinality:  mandatory/optional for minimum and one-to-one (1:1), one-to-many (1:M), or many-to-many (N:M) for maximum.
8.       Normalize the data model.

Remove any many-to-many (N:M) relationships by adding an intersection entity. This intersection entity consists of at least the identifier attributes of each of the related entities. In a database, this would mean an intersection table consisting of at least the primary keys of the two tables that have the many-to-many relationship.

It may make sense to include other attributes in this intersection entity. Consider whether this intersection entity represents something in the real world that you really do want to keep track of. If so, then the intersection entity will likely have a more meaningful name and more than those two attributes. You will easily see what other attributes need to be included based on the real world item you realize the intersection entity represents.

Remember that you need to designate a unique identifier for each entity (what will become a primary key in a database table). In this case, you may need more than one attribute. That is, you may need to combine two or more attributes to get a unique value for each instance of this entity. To state this idea in terms of a database, you may need to combine two or more fields to create a primary key that will uniquely identify each record in the table. We call this a composite key in a database. I will explain in the next assignment how to accomplish this in Access.
·         Remove any derived attributes. Derived attributes are those whose value can be figured or otherwise established from the values of other attributes.

For example, in a sales transaction, customers may by several of a particular item. If they buy three tubes of the same toothpaste, the total due for that item can be calculated by multiplying the number of tubes by the price of each tube. In the sales transaction table, we just record one tube, its price, and the number of tubes bought. We don’t include the total due for the three tubes of toothpaste in the sales transaction record, as that can be calculated any time we need it.

GPA is another example. We do not store your GPA in your student record. We store the total earned hours (3 earned hours for each 3-credit course, for example) and total earned quality points (4 for each ‘A’, 3 for each ‘B’, and so forth). If we need to display or print your GPA, the system just calculates it on the spot from those two fields.
·         Make sure all attributes are in the right entity. Sometimes, in the heat of designing the data model, an attribute is mistakenly placed in the wrong entity. Consider whether each attribute belongs directly to its entity or really belongs to another one. One good clue is to consider whether the attribute relates directly to its entity key. If an attribute does not directly describe the entity in which it is located, it needs to be moved to the attribute it does describe. For example, you might have listed the vendor of a product in a sales transaction entity. Really, the vendor should be in the product inventory entity, as vendor better describes the product than it does the sales transaction.