ENTITY RELATIONSHIP

¡A relationship: Is connection between entity classes.

lE.g. PERSONS AND AUTOMOBILES

lPEOPLE OWNS AUTOMOBILES.

¡TYPES OF RELATIONSHIPS

lONE-ONE RELATIONSHIP

lONE-MANY ___,,_____

lMANY-MANY ___,,____

¡ONE – ONE

lFor each entity instance in parent entity class there is at most one associated entity instance in the dependent entity class.

¡E.g In Christian marriage: each husband there is at most one current legal wife. A wife has at most one current legal husband.

¡ONE – MANY

lOne entity instance in parent entity class is associated with zero or more entity instances in the dependent class, but each entity instance in the dependent entity class is associated with at most entity instance in parent entity class.

E.g Mother may have many children but child has only one birth mother.

¡MANY – many

lThere are no restrictions on how many entity instances in either entity class are associated with a single entity instance in the other table.

¡E.g students and Courses

3. LOGICAL DATA MODEL

¡To build the logical data model you need entities and data items identified in the requirements, then determine if there are any relationships between the entities.

¡

¡Entity Relationship diagram contains:

1.Boxes for entities

2.Circles for Relationship types.

3.Lines for Relationships between entities, beginning with the parent Entity

l---------- is optional relationship

l________ is required relationship

ENTITY RELATIONSHIP DIAGRAM

¡ONE – TO – ONE

lRelationship is single valued in both directions.

¡ONE – TO – MANY

lRelationship is multi-valued in one direction – one row in the parent table is associated with many rows in the dependent table.

¡MANY – TO – MANY

lRelationship is multi-valued in one direction – one in the dependent table is associated with many rows in the parent table.

¡MANY- TO – MANY

lRelationships are multi-valued in both directions.

Note: Entities that o not relate to another entity may result in “stand alone” table with no relationships defined.

¡One to one Relationship

lManager manages one department; a department has only one manager.

Read More

FOREIGN KEYS

¡Is a column or set of columns that is a primary key in another table

¡PROPERTIES

lvalue can be null,

lvalue can change 

lvalue can be duplicated

¡RULES FO FK

lValue must refer to the existing PK

DATABASE DEVELOPMENT

¡Database development is a process consisting of many steps.

1.Data modeling.

2.Description of the data to the db

3.Physical implementation of the db

1. CUSTOMER REQUIREMENTS

¡Customer Questions:

lAsk specific questions during the interviews to help define the requirements.

li.e who: who are the end users of he system?

lWho is requiring output form the system etc

lWhat: what is the primary purpose for this system?

lWhat does our end user want? Etc

lWhy: why do they need a db?

lHow do data in the system be inserted?

lWhere will the db system be maintained?

¡Customer agreement

lFrom your customer interviews, you have documented and agreed upon a set of system requirements for the new database.

l

lNow, the next step is to translate these requirements into a db model.

2. ENTITIES IDENTIFICATION

¡From customer requirements you can determine entities and data attributes also you can determine the relationships btwn the entities.

¡ENTITIES:

lAn entity defines a thing that exists and distinguishable from which data will be collected. E.g person, place, object or concept

lEntities are basic building blocks of the database design.

lEntity Instance: Is a particular occurrence of an entity eg. Person etc

lEntity set: A group of similar entities is called an entity class or entity type.

¡

Read More

HIERARCHICAL DATABASE

¡Fields or records structured in nodes

¡Viewed as branches of an upside-down tree

¡Each item is subordinate to its parent node

¡Only one parent per node

¡The subordinate item is the child node to the parent

¡If parent node is deleted, all the child nodes are as well deleted

¡New parent node must be created before adding a new child node

¡No direct relationships between child nodes

NETWORK DATABASE

¡Also has hierarchical node arrangement

¡But here child nodes may have more than one parent node, or a many-to-many relationship

¡The interconnected design allows for access via multiple pathways

RELATIONAL DATABASE

¡No pre-determined access paths

¡Data stored in a collection of columns and rows called a table, or a relation

¡Tables may be electronically linked via a key field containing common data

¡Easy to add, delete and modify the data and the table structures

Relational Terminologies 

¡Table or Relation

¡Null values

¡Duplicate Values

¡Changeable Values

¡Primary Keys

¡Foreign Keys

TABLE OR RELATION

¡Table will store information for a particular entity

¡Table name must be unique

¡ The Table name should be descriptive

¡Column Name must be unique within the Table

¡Rows must be unique

NULL VALUES

¡missing or unknown value in a column of a table

¡Nulls are not the same as zeros

¡Most arithmetic operations can be performed on zero values

¡nulls must be excluded from mathematical manipulations

DUPLICATE VALUES

¡A duplicate value is a value in a column of a table that exactly matches some other values within the same column.

CHANGEABLE VALUES

¡value in a table that may vary over time.

¡Most values in most tables are Changeable

¡You can prevent changes when it is desirable to prevent changes in a given column of a table

PRIMARY KEYS

¡Uniquely identify each row of that table.

¡ Every table must have only one Primary key

RULES FOR PRIMARY KEY

¡Must always have a value (null values are not allowed)

¡Value should be unique (duplicate values are not allowed)

¡Value should not change over time   

Read More

Database Administrator

A database administrator (DBA) controls and manages the database.

Function of a DBA

¡Make decisions concerning the content of the database

¡Plan storage structures and access strategies.

¡Provides support to users

¡Defines security and integrity checks

¡Interprets backup and recovery strategies.

TYPES OF DATABASES

¡Flat Databases

¡Hierarchical Database

¡Network Database

¡Relational Database

 FLAT DATABASES

¡A single kind of record with a fixed number of fields.

¡A way of organizing all information in a single table.

¡Suitable for extremely simple databases.

¡Inherit data redundancy 

FLAT DATABASES

Read More