Database Development Lifecycle

• Introduction to Database & Data Modeling: Week 2

Database Development Lifecycle

• Create a Data Model from gathered requirements using an Entity-Relationship Model.
• Transpose the Data Model into Relation(s).
• Normalize Relations.
• Create the database structure within the Database Management System which is considered Metadata.
• Define Relationships between Tables, which is also Metadata.
• Define forms, queries, reports, and menus which is Application Metadata, if your DBMS or external application programs support it.
• Maintenance is necessary because change is inevitable, so plan for it.
• Maintenance involves dealing with User Needs, DBMSs, and Hardware.

Expanded Database Development Lifecycle

• The expanded lifecycle includes creating the database
• The expanded lifecycle includes creating the table(s).
• It also involves populating the database with User Data, and the process of determining responsibility will depend on the situation

Entity (Class) Review

• An entity class is a set of data of interest
• Corresponds to rectangles in an E-R diagram
• Corresponds to tables in a database schema

Entity (Instance) Review

• An entity instance refers to an occurrence of an entity class
• Not usually shown in an E-R diagram
• Corresponds to a row in a database table

Identifiers

• Entity instances have identifiers which are one or more attributes that distinguish an entity instance from others.
• Identifiers can be unique to only identify one instance from the set of entity instances, like a Social Security Number.
• Non-unique identifiers identify more than one row, like a name which may identify several employees.
• A non-unique identifier identifies a set of entity instances in the entity class.
• A composite identifier is when two or more attributes are contained in an identifier, like first name, middle initial, and last name.

Step 1: Data Model Creation

• The first step in the Lifecycle Example is to create a data model.
• The data model is based on the Contact Management System (Conman).

Step 2: Transpose Data Model into Relation(s)

• The name of the entity becomes the name of the "relation".
• Attributes of the entity become attributes of the "relation", except for composite and derived attributes.
• Use Relational Schema Notation, which is a one-dimensional view using RELATION(attr1, attr2, attr3, ...), for example, A(2, 3, 4, 5).
• The identifier of the entity becomes the proposed primary key of the "relation", denoted by underlining, for example, RELATION(attr1, attr2, attr3...).
• Use Relational Schema Notation, for example, A(2, 3, 4, 5) where a composite primary key is formed by 2 and 3.
• There is one primary key in A.

Transposing Example: EMPLOYEE

• The Crow's Foot Notation shows the transposed relation, which is a one dimensional view.
• The transposed relation is showed as EMPLOYEE(SSN, Name, DOB).

Relation

• A relation is a two dimensional structure that holds data pertaining to an object of interest, such as an Employee with SSN, Name, and DOB.

Step 3: Normalize Relation(s)

• Purpose is to ensure relations are fit for implementation in a DBMS.
• Check that the “relation” is in First Normal Form (1NF).
• Here are the characteristics for the “relation”:
• Rows contain data about tuples or instances of the relation
• Cells of the table hold a single value (no arrays)
• Columns are attributes of the relation
• All entries in a column are of the same domain (physical and logical)
• Each column has a unique name
• Order of the attributes is unimportant
• The order of the tuples is unimportant
• No two tuples may be identical (Must have a primary key)
• Natural primary key (already existing attribute) vs Surrogate primary key (used only to guarantee uniqueness)
• If the relation meets the characteristics, then the relation is in 1NF.
• If not, then the relation must be modified so that it does meet the characteristics.

Valid Relation?

• In order to be valid, no two entries may be identical
• Furthermore, all column entries need to be of the same type

Equivalent Relations?

• The order of rows in the table does not matter

Natural Primary Key

• In EMPLOYEE(SSN, Name, DOB), the SSN is a natural primary key.
• This is because the SSN the attribute has meaning to the organiation and the values for the attribute would be unique.

Surrogate Primary Key

• In EMPLOYEE(EmployeeID, SSN, Name, DOB) the EmployeeID is the surrogate primary key.
• This is because it only provides a unique value to each instance of employee and the values have no meaning to the organization.

Is the CONTACT INFO relation in 1NF?

• Here are the characteristics:
• Rows contain data about tuples or instances of the relation
• Cells of the table hold a single value
• Columns are attributes of the relation
• All entries in a column are of the same domain
• Each column has a unique name
• The order of the attributes is unimportant
• The order of the tuples is unimportant
• No two tuples may be identical