Business Rules
A business rule is statement that imposes some form of constraint on a specific aspect of the database
Examples
A SHIP DATE cannot be prior to an ORDER DATE for any given order
Every customer must have a name
Each employee must work for one department
Each employee may be assigned to zero or more projects
Top-Down Approach to Database Design
Using the concepts of the Entity-Relationship (ER) model, beginning with the identification of entities and relationships between the entities, which are of interest to the organization
Starts with the development of data models that contain a few high-level entities and relationships and performs successive top-down refinements to identify lower-level entities, relationships, and the associated attributes
Appropriate strategy for the design of complex databases
Bottom-Up Approach to Database Design
Appropriate for the design of simple databases with a relatively small number of attributes
The process of normalization, which represents a bottom-up approach to database design
This approach becomes difficult when applied to the design of more complex databases with a larger number of attributes, where it is difficult to establish all the functional dependencies between the attributes
Data Modeling Using the Entity–Relationship (ER) Model
In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram
It is a high-level data model
This model is used to define the data elements and relationship for a specified system
It develops a conceptual design for the database
Entity-Relationship Diagram
In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram
It is a high-level data model
This model is used to define the data elements and relationship for a specified system
It develops a conceptual design for the database
Component of ER Diagram
Entity
An entity may be any object, class, person or place
In the ER diagram, an entity can be represented as rectangles
Weak Entity
An entity that depends on another entity called a weak entity
The weak entity is represented by a double rectangle
Attribute
The attribute is used to describe the property of an entity
For example, id, age, contact number, customer name, etc. can be attributes of a Customer
Key Attribute
The key attribute is used to represent the main characteristics of an entity
It represents a primary key
For example, Suppose we design a school database
In this database, the student will be an entity with attributes like address, name, id, age, etc.
The address can be another entity if it composed of attributes like street address, suburb, city, state, post code, etc.
Composite Attribute
An attribute that composed of many other attributes is known as a composite attribute
Multivalued Attribute
An attribute can have more than one value
These attributes are known as a multivalued attribute
For example, a student can have more than one phone number
Derived Attribute
An attribute that can be derived from other attribute is known as a derived attribute
It can be represented by a dashed ellipse
For example, A person's age changes over time and can be derived from another attribute like Date of birth
Relationship
A relationship is used to describe the relation between entities
Diamond is used to represent the relationship
One-to-One Relationship
When only one instance of an entity is associated another one, then it is known as one to one relationship
For example, A female can marry to one male, and a male can marry to one female
One-to-many relationship
When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship
Many-to-many relationship
When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship
For example, Employee can assign by many projects and project can have many employees
Recursive Relationship
A relationship between two entities of the same type, it is known as a recursive relationship
This means that the relationship between different instances of the same entity type
How to Draw ER Diagrams
Identify all the entities in the system. Create rectangles for all entities and name them properly
Identify relationships between entities. Connect them using a line and add a diamond shape in the middle describing the relationship
Add attributes for entities. Give meaningful attribute names so they can be understood easily