Database Development Lifecycle

^head Database Development Lifecycle

1: Database Planning §

• Enterprise Data Model

2: Systems Definition §

• Specifying scope and boundaries
  • Users
  • Application areas
• How does the system interfere with other organisational systems

3: Requirements Definition & Analysis §

• Collection and analysis of requirements for the new system
• Gathering the prompt i.e. business rules
• Estimated Table Growth is calculated.

4: Conceptual Design §

5: Logical Design §

Logical Database Design

* Construction of a *relational* model of the data based on the [conceptual database design](Conceptual%20Database%20Design.md) stage.
* Enforces stuff like keys, not-null, etc.

• STILL independent of a specific database and other physical considerations

Relational Data Model §

See Relational Data Model

From Conceptual Database Design §

To convert from a conceptual database design, we need to:

1. Flatten all composite attributes and multi-valued attributes in any entity set
2. If participation is total the key cannot be NULL.

For One-to-Many Binary Relationships (i.e. with key constraints) §

1. Delete the relationship attribute, and move all relationship attributes into the ‘one’ entity, i.e. the entity with the key constraint.
2. Add the primary key of the ‘many’ entity as a foreign key into the ‘one’ entity

The primary key of the ‘many’ entity becomes the foreign key of the ‘one’ entity

Customer & Accounts

Take the business rule: “An account belongs to one customer”. Create a logical schema from it. Let’s define:

$$\text{Customer(CustomerID, Name)}$$

$$\text{Account(AccountID, DateCreated, Amount)}$$

We know that for each account, it can only relate to one customer, so the key constraint is on the account entity. Therefore, we store the primary key of the ‘many’ entity (which is Customer) and use it as a foreign key:

$$\text{Account(AccountID,emphCustomerID, DateCreated, Amount)}$$

Trying to store an account with multiple customers will be an illegal instance, since the primary key $\text{AccountID}$ will be repeated. However, we can have the same customer having multiple accounts, since the foreign key $\text{CustomerID}$ can be repeated as long as the primary key is not

For Many-to-Many Binary Relationships §

1. Create a relationship entity to store the relationship attributes.
2. Take primary keys from both entities in the relationship and store them in the relationship entity as foreign keys

The primary keys of the participating entities become foreign keys of the relationship entity

For One-to-One Binary Relationships §

1. Put the primary key of one side as the foreign key of the other side.
2. The relationship attributes go to that same entity

The choice of entity to have the foreign key and relationship attributes should factor in:

• Which entity would have the least NULL values?
• The optional entity gets the foreign key

For Unary Relationships §

For both unary one-to-one and one-to-many relationships, simply add a foreign key.

For a unary many-to-many relationship, do a similar process as for many-to-many binary relationships and create an associate entity, and store two foreign keys.

Link to source

6: Physical Design §

Physical Database Design

• Implementing the logical design for a specific DBMS
• Defines:
  • Data types
  • Indices
  • Keys
• Data Dictionaries are useful.
• Relational Data Model has datatypes in the schema
• The actual database is still not created, but is almost ‘fully’ modelled.

Link to source

Lookup Tables §

Storing reference to another entity, instead of using it as a datatype. Ensures data integrity and reduced storage (with many entries) at the cost of time.

8: Implementation §

9: Data Conversion & Loading §

• Transfer existing data into the database
• Conversion from old systems
• Non trivial task

10: Testing §

11: Operational Maintenance §

• Monitor disk usage and transaction load