MIS480 1

Chapter2_480.pdf

Home >Information Systems homework help >MIS480 1

• Week 2 & 3

Modeling Data in the Organization Chapter 2 (pp89-133)

It is essential you read the book. These slides represent a summary of what was presented in the class and summary of what is

covered in the book. Relying purely on the slides will not guarantee you will pass this course.

Lesson Content:

• What is Data Definition?

• Understanding the importance of data modeling.

• Components of E-R diagram: • Naming and defining entities, relationships, and

attributes

• Multiplicity: Distinguish unary, binary, and ternary relationships

• Model different types of attributes, entities, relationships, and cardinalities

• Drawing E-R diagrams for common business situations

• Breaking the many-to-many relationships to associative entities.

• Using Time Stamps to model time-dependent data. 2

Database Design. p.91

• Entity Relationship Diagram (E-R): • It is a tool to model databases. • Excellent when modeling a relational

Databases. • It shows Entities (Tables),

Relations(Links), and lists Attributes of that entity (Fields)

• “A logical representation of the data for an organization or for a business area, using entities for categories of data and relationships for association between entities.” p.91

E-R: Cardinalities p.92

• Minimum specifies the minimum number of instances of the related entity.

• Maximum specifies the maximum number of instances of the related entity.

A B

Cardinatlity

Optionality

Figure 2-17 Examples of cardinality constraints (cont.)

b) One optional, one mandatory

An employee can be assigned to any number of projects, or may not be assigned to any at all

A project must be assigned to at least one employee, and may be assigned to many

This of the following examples:

• Employee would belong to one Department at a time.

• Department can have many Employees.

• An Employee can work on many Projects.

• A Project can have many Employees.

Employee Department

Project

Problem

The problem

101 Fadi Salmya B

102 Hamid Hawali C

103 Lana Egaela B

Employee(ID, Name, Address, Grade)

05 Financial 02/2014 20,000

06 Marketing 04/2014 10,000

07 IT 07/2014 50,000

Project (ID, Type, Date,Budget )

EmployeeProject

The solution

101 Fadi Salmya B

102 Hamid Hawali C

103 Lana Egaela B

Employee(ID, Name, Address, Grade)

05 Financial 02/2014 20,000

06 Marketing 04/2014 10,000

07 IT 07/2014 50,000

Project (ID, Type, Date,Budget )

Proj_Emp (ID, Proj_ID, Emp_ID)

1 05 101

2 06 101

3 07 101

4 05 103

5 06 103

EmployeeProject Proj_Emp

Figure 2-11a A binary relationship with an attribute

Here, the date completed attribute pertains specifically to the employee’s

completion of a course…it is an attribute of the relationship.

Figure 2-11b An associative entity (CERTIFICATE)

Associative entity is like a relationship with an attribute, but it is also considered to be

an entity in its own right.

Note that the many-to-many cardinality between entities in Figure 2-11a has been

replaced by two one-to-many relationships with the associative entity.

Data Modeling Concepts: Attributes

Attribute – a descriptive property or characteristic of an entity.

• Just as a physical student can have attributes, such as hair color, height, etc., data entity has data attributes

• Classifications of attributes:

• Required versus Optional Attributes

• Simple versus Composite Attribute

• Single-Valued versus Multivalued Attribute

• Stored versus Derived Attributes

• Identifier Attributes

8-11

Figure 2-8 Entity with multivalued attribute (Skill) and derived attribute (Years Employed)

Multivalued an employee can have more than one skill

Derived Calculated from date employed and current date

Multi-valued and Derived Attributes

Multivalued – may take on more than one value for a given entity (or relationship) instance

Derived – values can be calculated from related attribute values (not physically stored in the database)

Primary Key:

• When modelling a database, the developer must identify the keys for each table !!

• The primary key is usually underlined. • If a table has only one unique key, then that key is the primary key.

• If we have more than one unique key, we need to decide on one to be our primary key.

Student ( StudentID, Name, Civil_ID, Age, Address, Course)

Primary key

Figure 2-9 Simple and composite identifier attributes

The identifier is

boldfaced and

underlined

Foreign Keys:

A foreign key is an attribute in a relation that is not the primary key in that relation but it is the primary key in another relation.

What do they mean: A foreign key is a field in a table that is not a primary key in that table but it is a primary key in another table!

The reason why we create foreign keys is in order to link tables together and it is the most important part of relational modelling. Maybe it is better if we see it in an example.

Foreign Keys:

• Why do we use Foreign keys. Take a look at the following two tables of library system:

101 01/01/14 4 weeks Nasser Salmya Accounting 01/02/14

102 02/01/14 1 week Khalil Kuwait City Finance 05/01/14

103 02/01/14 1 week Nasser Salmya Accounting 03/01/14

Bookloan(LoanID, Date, Period, Name, Address, Course, return_date)

Student ( StudentID, Name, Address, Course)

1 Samar Hawali MIS

2 Nasser Salmya Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

Foreign Keys: • Problems with such design:

• Duplication of data. • Data redundancies. • Cause of errors*.

101 01/01/14 4 weeks Nasser Salmya Accounting 01/02/14

102 02/01/14 1 week Khalil Kuwait City Finance 05/01/14

103 02/01/14 1 week Nasser Salmya Accounting 03/01/14

Bookloan(LoanID, Date, Period, Name, Address, Course, return_date)

Student ( StudentID, Name, Address, Course)

1 Samar Hawali MIS

2 Nasser Hawali Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

Foreign Keys:

• Solution: Create a foreign key in BookLoan table that links to the primary key in the Student record table.*

101 01/01/14 4 weeks 2

102 02/01/14 1 week 3

103 02/01/14 1 week 2

Bookloan(LoanID, Date, Period,Student_ID)

Student ( StudentID, Name, Address, Course)

1 Samar Hawali MIS

2 Nasser Hawali Accounting

3 Khalil Kuwait City Finance

4 Samar Salmya Engineering

Strong entity Weak entity

Figure 2-5 Example of a weak identity and its identifying relationship

Degree of relationships – from Figure 2-2

Entities of two

different types

related to each

other Entities of three

different types

related to each other

One entity related

to another of the

same entity type

Figure 2-19 Simple example of time-stamping

The Price History attribute is both multivalued and composite.Time stamp – a time value that is associated with a data value,

often indicating when some event occurred that affected the data value

Figure 2-20c E-R diagram with associative entity for

product assignment to product line over time

The Assignment associative entity shows the date range of a product’s assignment to a particular product line.

Modeling time-dependent data has become more important due to regulations such as HIPAA and Sarbanes-Oxley.

Figure 2-22

Data model for Pine Valley

Furniture Company in

Microsoft Visio notation

Different modeling software tools may have different notation for the same constructs.