L10 - ERM Application in Concept Flashcards
Descibe the components of a UML diagram
What is relationship degrees in UML diagrams
Number of participating entities involved in a realtionship
- Binary - 2 Entities (most common)
- Ternary - 3 Entities
- Quaternary - 4 Entities
- N-art - >4 Entities involved
What are recursive relationships?
- Only one entity participates
- More than once
- In different roles
- Supervisor is also a supervisee
- As all supervisors are staff
- In this case occurrences optionally participate in the role of supervisor
What are the three phases of database design?
- Conceptual Database design
- Logical Database design
- Physical Database Design
What is the conceptual phase
- Process of constructing a model of the data
- Used in an org independent of all physical considerations
- Accomplished by building an ER diagram
What are the steps involved with the conceptual phase?
- Identify entities
- Identify relationships
- Identify and associate attributes with entities or relationships
- Determine attribute domains
- Determine candidate, primary, and alternate key attributes
- Check model for redundancy
- Check model supports user transactions
- Review conceptual database design with users
What is the first step in conceptual design?
1 - Identify Entities
- Analyse system definition and mission statement
- Group related nouns help with this
- Use Pascal naming convention (ImTired)
- Gather requirements via use case descriptions
- Create user views
- Use cases that only apply to certain groups of users
- Create user views
- From those user views outline the different entities at play
- Create a data dictionary
- To hold metadata for the DB
What is the second step in conceptual design?
2 - Identify Relationships
- Identify associations between entities by analysing use case descriptions
- Start to think about what tables need to be connected based on entity association
- Aim to minimise redundancy as indirect relationships don’t require another link
- Take into account multiplicity (OTO, OTM)
What is the third step in conceptual design?
3 - Identify Attributes
- All entities and relationships have attributes
- What information needs to be held on X
- Eg for staff - Name, Email, Phone, Salary, ect
- Gathered from use case description
- Document in data dictionary from step 1
- Use camelCase
- Decide on if nulls allowed
- If the data is simple or composite
- Single or multi-valued
- Sometimes attributes may lack an entity (go back to 1)
- Merge entities with same attributes
- Attribute may be relationship so go back to step 2
What is the fourth step in conceptual database design?
4 - Determine Attribute Domains
- Domain - pool of values from which one or more attributes draw their values
- Eg Id is fixed 4 digits
- Complete data model should include
- Allowable set of values for each attribute
- Size and format of the attribute
What is the fifth step in conceptual database design?
5 - Determine Candidate, primary and alternate keys
- FIND CANDIDATE KEY, choose a Primary key if there are multiple candidate keys
- Must hold values that are together
- Are always unique to the occurrence
- Never holds null values
- Primary key should be
- The smallest candidate
- Easy to use
- Unlikely to change and loose uniqueness
What is the last step in conceptual database design?
6 - Check Model for redundancy
- Re-examine one-to-one relationships
- May have entity pairs that reflect the same class or objects and can be merged (warehouse + distribution centre)
- Remove redundant relationships
- Reduce unnecessary complexities
- Redundant if the same info can be determined using other relationships
