2.Database Design

Identifying User Requirements:-

One challenging aspect of designing a system is to determine the requirements. You must thoroughly understand the business needs before you can create a user files system. Excellent communication skill and experience are important to becoming a good designer.
One of the most important tasks in designing a database application is to correctly identify the data that needs to store. The DBMS makes it easy to create and modify reports. The three basic information for the initial design: a. the data that needs to be collected. b. the data type (domain), and c. the amount of data involved. The initial goal is to identify the business entities or objects and describe how they are related to each other.

Logical data modeling:-
A Logical Data Model (LDM) is a complete representation of data requirements and the structural business rules that govern data quality in support of project’s requirements. The LDM shows the specific entities, attributes and relationships involved in a business function and is the basis for the creation of the physical data model. Logical data models should be based on the structures identified in a preceding conceptual data model.
The term 'Logical Data Model' is sometimes used as a synonym of 'Domain Model' or as an alternative to the domain model.

Why Build Logical Data Model
a. Helps common understanding of business data elements and requirements
b. Provides foundation for designing a database
c. Facilitates avoidance of data redundancy and thus prevent data & business transaction inconsistency
d. Facilitates data re-use and sharing
e. Decreases development and maintenance time and cost
f. Confirms a logical process model and helps impact analysis.

Logical Modeling Benefits
a. Clarifies functional specifications and avoids assumption
b. Confirms business requirements
c. Facilitates business process improvement
d. Focuses on requirements independent of technology
e. Decreases system development time and cost
f. Becomes a template for the enterprise
g. Facilitates data re-use and sharing
h. Faster ROI
i. Gathers metadata
j. Foster seamless communication between applications
k. Focuses communication for data analysis and project team members
l. Establishes a consistent naming scheme

Logical Vs Physical Data Model
Logical Data Model Vs Physical Data Model
Includes entities/tables, attributes/columns/fields and relationships Includes tables, columns, keys, data types, validation rules, database triggers, stored procedures, domains, and access constraints
Uses business names for attributes Uses abbreviated column names limited by the database management system (DBMS)
Is independent of technology (platform, DBMS) Includes primary keys and indices for fast data access.
Is normalized to fourth normal form May be de-normalized to meet performance requirements based on the nature of the database. If the nature of the database is Online Transaction Processing (OLTP) or Operational Data Store (ODS) it is usually not de-normalized. De-normalization is common in Datawarehouses.

E-R diagram:-
An entity-relationship diagram is a data modeling technique that creates a graphical representation of the entities, and the relationships between entities, within an information system.
Or
Entity-relationship(ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. ER diagram often use symbols to represent three different types of information. Boxes are commonly used to rep[resent entities. Diamonds are normally used to represent relationship and ovals are used to represent attributes.
Also known as: E-R diagram, Entity-relationship model ER diagram.
Consider the example of a database that contains information on the residents of a city. The ER diagram shown in the image above contains two entities—people and cities. There is s single “Lives In” relationship. In our example, due to space constraints, there is only one attribute associated with each entity. People have names and cities have populations. In a real-world example, each one of these would likely have many different attributes.

Components of E-R diagram
Entity:-
The entity is a person, object, place or event for which data is collected. For example, if you consider the information system for a business, entities would include not only customers, but the customer's address, and orders as well.

Attributes:-
Attributes are the parameter that describes entity types. For example, for entity student, attributes are roll no, Name, address, faculty, etc.

Relationship:-
Relationship is the interaction between the entities. For example Teacher teaches students. Here ‘teacher’ is a relationship between two entity teacher and students.

Weak and strong entity
Weak entity VS Strong entity
1. A weak entity set has no primary key unless attributes of the strong entity set on which it depends are included. 1. A strong entity set has a primary key. All tuples in the set are distinguishable by that key.

2. Weak entity is the one that does not exit own its own (depend on other entity). 2. Strong entity is the one that is independent

3. It does not have a unique identifier (only a partial identifier)3. It has own unique identifier

4. Partial identifier underlined with double-line 4. Identifier underlined with single-line


Atomic attributes Vs Complex attributes
1.Simple, or atomic, attribute is one that cannot be decomposed into meaningful components. For example, an attribute for gender cannot be meaningfully decomposed into other attributes. Such an attribute will assume values such as Male or Female.
1.A composite attribute is an attribute that is shown as comprising two or more simpler attributes.
Consider an attribute such as empName that holds an employee’s first and last names. For example, suppose an employee’s name is John McKenzie. The first name is John and the last name is McKenzie.

2.A simple attribute cannot be subdivided. 2.A composite attribute is one that can be subdivided to yield additional attributes.
For example, a phone number can be subdivided into area code, exchange, and number.


Discuss the difference between a composite key and a composite attribute.

A composite key is one that consists of more than one attribute. A composite attribute is one that can be subdivided to yield attributes for each of its components. If the E-R diagram contains the attribute names for each of its entities, a composite key is indicated in the E-R diagram by the fact that more than one attribute name is underlined to indicate its participation in the primary key.


What is a derived attribute? Give an example.

Database designers who concentrate on designs that display "design elegance" are very reluctant to store derived attributes in the database. Instead, they prefer that these derived attribute values are computed through appropriate algorithms when they are needed in a query.
For example, a person's age may be calculated by using Julian dates to subtract the birth date from the current date and dividing the resulting number of days by 365.

Single Value attribute:-
Single-valued attributes are shown with a simple oval, see below. In all of our examples so far, we have assumed that each attribute was single-valued.
Consider employees for a typical business application where we need to store a value for gender. Each employee is either male or female, and so there is only one value to store per employee.

Attributes may also be single-valued or multivalued. As the name suggests, a single-valued attribute can have only one value.
For example, a person can have only one gender and one birth date. Multivalued attributes can have many values:
A person can have more than one college degree, and a household may have more than one phone.


What role does the E-R diagram play in the design process?

A completed E-R diagram is the actual blueprint of the database. Its composition must reflect an organization's operations accurately if the database is to meet that organization's data requirements. It forms the basis for a final check on whether the included entities are appropriate and sufficient, on the attributes found within those entities, and on the relationships between those entities. It is also used as a final crosscheck against the proposed data dictionary entries. The completed E-R diagram also lets the designer communicate more precisely with those who commissioned the database design.
Finally, the completed E-R diagram serves as the implementation guide to those who create the actual database.
In short, the E-R diagram is as important to the database designer as a blueprint is to the architect and builder.

What is a composite entity, and when is it used?
A composite entity, also known as a bridge entity, is one that is used to transform M:N relationships into sets of 1:M relationships.
The composite entity's primary key consists of the combination of primary keys from the entities it connects.


How are database models related to the level of data abstraction?

The higher the level of abstraction, the less need for the often difficult-to-understand physical details.
The hierarchical and network database models make use of all three levels of abstraction. In contrast, the relational model primarily makes use of high and medium degrees of abstraction. In fact, the relational model generally allows its users to focus on the highest and, therefore, easiest to understand, level of abstraction.
Given this focus, the relational implementation model lets us make better use of the conceptual data model. The conceptual model, being located at the abstraction apex, identifies and describes only the main data objects, avoiding the details. And it is this conceptual model that underlies the widely used E-R model whose use produces valuable database blueprints.

How is a composite entity represented in an E-R diagram, and what is its function?

In the Chen model, a composite entity, also known as a bridge entity, is represented through a diamond contained within a box. The diamond represents a relationship, while the box represents an entity. Thus the composite entity indicates that it translates a M:N relationship into two 1:M relationships through an entity. The label "bridge entity" is based on the fact that a composite entity serves as a connection between other entities.

The label "composite" is based on the fact that the composite entity contains at least the primary key attributes of each of the entities that are connected by it. The composite entity is an important component of the E-R model because relational database models should not contain M:N relationships.

Class Diagram:-
A class diagram is an illustration of the relationships and source code dependencies among classes in the Unified Modeling Language (UML). In this context, a class defines the methods and variables in an object. Class diagrams are useful in all forms of object-oriented programming (OOP). In a class diagram, the classes are arranged in groups that share common characteristics. The top rectangle contains the name of the class; the middle rectangle contains the attributes of the class; the lower rectangle contains the methods, also called operations, of the class. Lines, which may have arrows at one or both ends, connect the boxes. These lines define the relationships, also called associations, between the classes.

Member
UML provides mechanisms to represent class members, such as attributes and methods, and additional information about them.


Visibility
To specify the visibility of a class member (i.e., any attribute or method) there are the following notations that must be placed before the member's name.
+public:- visible to all elements that can access the contents of the namespace that owns it.

#protected:-visible to elements that have a generalization relationship to the namespace that owns it.

–Private”-only visible inside the namespace that owns it.

~package:- owned by a namespace that is not a package, and is visible to elements that are in the same package as its owning namespace. Only named elements that are not owned by packages can be marked as having package visibility. Any element marked as having package visibility is visible to all elements within the nearest enclosing package (given that other owning elements have proper visibility). Outside the nearest enclosing package, an element marked as having package visibility is not visible.

Scope
The UML specifies two types of scope for members: instance and classifier. In the case of instance members, the scope is a specific instance. For attributes, it means that its value can vary between instances. For methods, it means that its invocation affects the instance state, in other words, affects the instance attributes. Otherwise, in the classifier member, the scope is the class. For attributes, it means that its value is equal for all instances. For methods, it means that its invocation do not affect the instance state. Classifier members are commonly recognized as "static" in many programming languages. To indicate that a member has the classifier scope, its name must be underlined. Otherwise, as default, the instance scope is considered.