Data Modeling

1
Data Modeling

• ISYS 464

2
Database Design Process

• Conceptual database design
• The process of creating a data model independent
  of implementation details such as the target
  database model and physical considerations.
• Logical database design
• The process of designing database logical
  structure based on a specific database model
  (such as relational model), but independent of a
  particular DBMS and physical considerations.
• Physical database design
• The process of implementing the database on a
  secondary storage.

3
Requirements Collection and Analysis

• The process of collecting and analyzing
  information about the organization that is to be
  supported by the database system, and use this
  information to identify the requirements for the
  new system.

4
User Views

• A user view defines what is required of a
  database system in terms of the data to be held
  and transactions to be performed on the data from
  the perspective of a particular job role or
  enterprise application area.
• Identifying user views helps to ensure that no
  major users of the database are forgotten when
  developing the requirements for the new database
  system.

5
Fact-Finding Techniques

• Examining documentation
• Defining problem and need for database
• Internal memos, minutes of meetings, documents
  that describe the problem, organizational chart
• Describe the current system
• Various types of flowcharts and diagrams, data
  dictionary, database system design, program
  documentation
• Interviewing
• Observing the enterprise in operation
• Questionnaires

6
Conceptual Database Design Methodology

• Identify entity types.
• Identity relationship types between the entity
  types.
• Identify and associate attributes with entity or
  relationship types.
• Determine attribute domains.
• Determine candidate keys and primary key.
• Validate conceptual model
• Check for redundancy, support required
  transactions, review the model with user

7
Entity-Relationship Diagram

• ER modeling is a top-down approach to database
  design that begins by identifying the entities
  and relationships between entities that must be
  represented in the model.

8
Entity Type

• A group of objects with the same properties.
• Physical existence
• Customer, student, product, etc.
• Conceptual existence
• Bank accounts, sale
• Diagrammatic representation
• A rectangle labeled with the name of the entity.

9
Relationship Type

• A relationship type is a set of associations
  between one or more participating entity types.
• Degree of relationship
• The number of participating entity types in a
  relationship.
• Binary
• Ternary

10
Three kinds of Binary Relationship

• 11
• 1M
• MM
• Notations
• 0..1, 1..1
• 0.., 1..
• 3..5

11
Traditional ERD Notations
1
1
Student
Account
Has
M
M
M
Enroll
Advise
1
M
Faculty
1
Course
Teach
12
UML ERD Notations
Has
Student
Account
1..1
1..1
0..
Enroll
Advise
0..
0..
1..1
Faculty
Teach
Course
1..
1..1
13
Recursive Relationship

• A relationship type where the same entity type
  participates more than once in different roles.
• Examples
• Employee Supervise -- Employee
• Student -- Tutor Student
• Faculty Evaluate -- Faculty

14
Supervise
Supervisor
Employee
Superviswee
Employee
Supervise
15
Attributes

• Properties of an entity or a relationship.
• Simple and composite attributes
• AddressStreet address, City, State, ZipCode
• Street Address Number, Street, Apt
• Single-valued and multi-valued attributes
• Students Major attribute
• Facultys DegreeEarned attribute
• Vehicles Color attribute
• Others PhoneNumber, EmailAddress
• Derived attributes
• Keys
• Candidate key, primary key, composite key

16
Student
SID PK Sname Fname Lname Address
Street City State Zip Phone1..3 Sex Dat
eOfBirth /Age
17
Fname
Lname
Phone
DateOfBirth
SID
Sname
Age
Student
18
Domains of Attributes

• The set of allowable values for one or more
  attributes.
• Input validation
• Examples
• Sex F, M
• EmpHourlyWage Between 6 and 300
• EmpName 50 charcters

19
Attributes on Relationship

• Examples
• Student/Course Grade
• Order/Product Quantity
• Product/Country Date, Quantity

20
Enroll
Student
Course
0..
0..
SID
CID
Grade
Enroll
Student
Course
M
M
Grade
21
Problems with ER ModelsConnection Traps

• Fan traps Where a model represents a
  relationship between entity types, but the
  pathway between certain entity occurrences is
  ambiguous

Has
Oversees
Branch
Staff
Division
1..
1..1
1..1
1..
Which branch does Peter work?
Has
Oversees
Branch
Staff
Division
1..
1..
1..1
1..1
22
Chasm Traps

• Where a model suggests the existence of a
  relationship between entity types, but the
  pathway does not exist between certain entity
  occurrences.

Has
Oversees
Branch
Staff
PropertyFor Rent
1..
1..1
0..1
0..
Which properties are available at each branch?