L20 - Logical Design Flashcards
What is database design?
What is the top down approach?
- Conceptual database design
- Logical database design
- Physical database design
What are the steps for conceptual phase?
- Constructing a model of data
- Used in org independent of all physical considerations
- Primarily done through building an ER model
What steps are involved when creating a ER model?
- Identify entities
- Identify Relationships
- Identify and associate attributes with entities or relationships
- Determine attribute domains
- Determine candidate, primary and alternate key attributes with entities or relationships
- (Optional) specialise/generalise entities
- Check model for redundancy
- Check model supports user transactions
- Review conceptual database design
Explain realationship degrees
- Number of participating entities
- Binary = 2 (most common)
- Ternary - three entities involved
- N-ary - >4 entities involved
What is logical database design?
- Process of constructing a model of data used in an organisation based on a specific data model
- But independent of a particular DBMS
- And other physical considerations
What is the logical database design objective?
- To create tables based on
- The ER model
- To check that these tables are both
- Well-structured
- Support the required transactions
What is the steps involved in logical database design?
- Create tables
- Normalise table structure
- Check user transactions support for tables
- Check integrity constraints
- Review the LDD with users
How do you represent entities?
- Create a table for each entity in the ER model
- List only simple attributes
- Break down composite attributes
- Identify primary key
- Identify alternate candidate key
What is an alternate way of representing entities?
What is the general way to represnet realtionships between entities?
- Completed through the use of primary and foreign keys
- Includes parent and child entities
- Type of relationship depends on
- The degree - unary, binary,complex
- Participation contraints
- Single or multi-valued
How does 1:1 realttionships work
1:1 Binary Relationships
- Differentiation of parent child is harder due to
- Both sides having equal cardinality
- Therefore participation is used (lower values in multiplicity ranges) to decide
- To merge entities into single table
- Create separate tables related by foreign keys
How does mandatory participation on BOTH sides of a 1:1 relationship work?
- Entities combined into single table
- 1 primary key retains primary key
- 1 primary key converts into alternate key
How does Mandatory Participation of ONE side of a 1:1 relationship work?
- Parent entity has the optional participation
- Parent = 0..1
- Child has the mandatory participation
- Child primary key used as foreign key in parent entity
- Child = 1..1
How does Optional Participation on BOTH sides of the 1:1 relationship work?
- Both parent and child have optional participation
- 0..1
- Where the child now takes the parents primary key
- As an foreign key
How does 1:* unary relationships work?
- AKA one to many recursive relationships
- Same entity is parent and child
- Foreign key needed to represent the relationship
- Through copying the primary key
- Creating a second column to act as a foreign key
