E-R Model - Entities

The modeling, design and creation of a databse is an iterative, top-down process.

First, the designer must gather information to define specific requirements. This will involve interviews with clients, and review of any documentation about the project or compnay.

The second step is building the Entity-relations (E-R) model. The E-R model is a graphical representation of the database logic and includes a detailed description of all entities, relationships and constraints.

Third, the designer creates table instance charts, one per entity, that contain information about the data types and some sample data.

Finally, the tables are defined and created in the RDBMS.

An E-R diagram is like a blueprint for a house. It is the key to understanding and creating the database design. It is in the E-R diagram that we conceptualize the database and ensure that all the customer requirements are met. The tables are normalized and dependencies are identified. The database model is easy to change at this point. Once complete, the model can be mapped to any RDBMS.

The building blocks of the E-R model are entities, attributes and relationships:

Entitiesthe objects of significance for the organization about which information needs to be knownAttributesThe characteristics that describe or qualify an entity

Entities are represented by rounded boxes with unique names in capital letters

Attributes are the items of information about each entity, written in lowercase letters within the box and must be unique

Example: Identify two entities that might be important for a retail business. List at least 3 attributes for each entity. THen show what the entities and attributes would look like in an E-R diagram.

Entity 1: emloyee. Attributes: ID Number, last name, first name, salary

Entity 2: customer. Attributes: name, address, city, state, zip, phone

So our graphical E-R representation of these two entities would be: (all attributes not included due to space constraints)

EMPLOYEE id last name first name salary ...

CUSTOMER last name first name phone number address ...

In the E-R model, an * means the field is requied, and a # denotes a primary key field. The Employee ID is an obvious choice for a unique (primary) key in the employee table. For the customer table, since the name field might not be unique, we opt to create a customer ID field. If we update our E-R diagram to include primary keys and required fields, here's what we have

EMPLOYEE #*id last name first name salary ...

CUSTOMER #* id last name first name phone number ...

Identifying the correct entities for a databse is critical. We said that first step in the design process is to gather information. Once we look at the narrative of these requirements, we can begin to pick out the entities. A general rule is that the the significant nouns will turn into the entities and the adjectives will be the attributes. For example, let's look at a narrative for a church database:

Grace church needs to keep track of its members. The members need to receive the newsletter regularly. The church campus consists of a gymnasium, fellowship hall and worship centerfor various sizes and types of events. Activities are help throughout the week. There are several comittees within the church, and each person may serve on only one committee. The finance committee wants to record the amount of money that individual members give to the church and report the total to them at the end of the year. The church needs to purchase supplies such as paper and bulletin covers. The secretary, a member of the staff, deals with several different vendors.

Some of the nouns have been highlighted in blue. Some of the adjectives are in yellow. We can identify MEMBER and COMMITTEE as attributes. As we evaluate gymnasium, fellowship hall and worship center, these are really categories of buildings. So a third entity would be BUILDING. Some of the attributes of the BUILDING would be size or capacity and equipment. For example, does the building have multi-media capacity, or bleachers? Based on our analysis of the narrative, we move on to step 2, creating the start of an E-R diagram:

MEMBER #*id last name first name contribution ...

COMMITTEE #* name chair Meeting day ...

BUILDING #* Bid capactity equipment ...

Notice that while we can see the entities, we have no way to determine relationships between the. For example, which members serve on what committees. In the next section, we will see how to represent relationships in our E-R diagrams.

Student Exercise 4

You will answer questions related to the following narrative in this exercise

A downtown furniture boutique sells all kinds of couches. Hide-a-beds, futons, love seats and reclining sofas are some of the items they sell. They offer products for a variety of price ranges to suit many customers. The manager has 10 years of experience in the furniture business, and full-time employees get a special discount and bonus pay for exceeding sales quotas.

Review the grace church narrative above. Using the E-R graphics attached in this module, (or creating your own) build an additional entitiy with at least 3 attributes and an identified primary key

Review the furniture boutique narrative in this exercise. Using the E-R graphics attached in this module, (or creating your own) build two entities with at least 3 attributes each and an identified primary key

NOTE: you can copy and paste the diagrams into a single word document, or save them as a jpg file

First graphical: Employee(id, lastname, first name, salary) Customer (lastname, firstname, phone #, address. Second G : Employee (#id, lastname, firstname, salary) Customer: (#id, lastname, firstname, phone) Third G: Memeber: (#id, lastname, firstname, contribution) Committee: (#id, chair, meetingdate) Building: (#id, capacity, equipment)