5.3 DATABASE DEVELOPMENT LIFE CYCLE

 Whenever you are given a task to design a database system, then the logical steps, you will follow is to design that system is known as life cycle of the system.

First of all a written document is prepared where you keep all the data on which the system runs, For this purpose you can divide your work in the following steps:

a) Analysis: Analysis of the data means how frequently the data in the system is changing, what are the sources for data etc.

b) Objective and Constraints: You must know the objectives, problems and constraints of the system by defining these things for the system to be developed, you will be in position to tell that is the new database is to perform, what are the activities this new database system is going to do and in what way these activities to be done.

c) Database Design: In this activity you will decide the master files (where data is not frequently changing), transaction files (where data is changing very often and some calculation has to be performed on them). On the basis of this, database is designed conceptually, physically and logically.

d) Define scope and boundaries: here, you have to decide what is to be stored on new database system and what to be is stored elsewhere.

e) Testing of database: It is to checking whether the new proposed database developed by you fulfills all client requirements or not, in other words functionality of the system are checked during testing.

f) Maintenance of database system: After the database system is developed, it need to be maintained, it includes correction of some mistake, adding new components into the system or adding some new features as per new needs of the client etc.

Now let us see some details of conceptual, physical and logical database modeling.

Conceptual Data Model

A conceptual data model identifies the highestlevel relationships between the management systems different entities. Conceptual data model include:

· the important entities and the relationships among them.

· no attribute of entities are specified at this level.

· primary key is specified, at this level.

The Figure 4 is an example of a conceptual data model:


From the Figure 4, you can see that the only information shown via the conceptual data model is the entities which describe the data and the relationships between those entities and other information (such as attributes, primary key) is shown. 

Logical Data Model

A logical data model describes the data in as much detail as possible, without considering to how they will be physical implemented in the database. Features of a logical data model include:

  • · All entities and relationships among them.
  • · All attributes for each entity.
  • · The primary key for each entity.
  • · Foreign keys (keys which are used for identifying the relationship between different entities).
  • · Normalization is done at this level.

The steps for designing the logical data model are as follows:

1) Specify primary keys for all entities.

2) Find the relationships between different entities.

3) Find all attributes for each entity.

4) If there are manytomany relationships in the system, those are resolved here.

5) Normalization is done as per need.

The Figure 5 is an example of a logical data model where you can observe various attributes of Product and Store tables are identified as (Product_ID, Prod_des, Category etc):


Basic differences between Conceptual Data Model and Logical Data Model are:

  • · In a logical data model, primary keys are present, whereas in a conceptual data model, no primary key is present.
  • · In a logical data model, all attributes are specified within an entity. No attributes are specified in a conceptual data model.
  • · Relationships between entities are specified using primary keys and foreign keys in a logical data model. In a conceptual data model, the relationships are simply stated, not specified, so we simply know that two entities are related, but we do not know that what attributes are used for this relationship.

Physical Data Model

Physical data model represents how the model designed actually will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Features of a physical data model include:

· Specification of all tables and their columns.

· Foreign keys which are used to identify relationships between tables.

· Denormalization may occur based on user requirements. Also physical considerations may cause the physical data model to be quite different from the logical data model. Physical data model will be different for different RDBMS.

For example, data type for a column may be different between MySQL and SQL Server.

In Figure 6 memory to be occupied by various Tables and their attributes are shown.

For example Product will take (28 bytes)


5.3.1 Fundamentals of Data Modeling

Data Models are basically a way of structuring or describing data, also defines a set of operations which can be performed on the data, such as selection, some tuples from a table or joining of two database tables etc. When you recognize anything in a highlevel or abstract view of database is called logical model.

You can categorize Data Models as a collection of conceptual tools for describing data, data  relationships and data semantics (meaning of data) and data constraints.

Objectbased Logical Models: It describes data at conceptual and view levels. We will study EntityRelationship (ER) modeling in the next section. ER model represents the relationship between different entities in the system.

Recordbased Logical Models: Record based models are so named because the database is structured in fixed format records of several types. Each record type defines a fixed number of fields, or attributes and each field is usually of fixed length.

5.3.2 Entity Relationship Models

As the name suggests Entity Relationship Models illustrates the relationships between Entities.An Entity is a Real time Object like Shop, Customer, School, Department, Supplier, Order, Buyer, Shopkeeper etc.Here, the Data Modeling the focuses on the representation of data in the form of diagrams.

See, what a relation is; you are going to a particular shop again and again for purchasing same kind of items. Next time when ever you will go, he will recognize you. That means you have set up some relation with that shopkeeper. This is a relation between two entities say Kalpana (Buyer) and Srikant (Shopkeeper). And between them the relation is of purchase goods.

Entityrelationship model: A logical representation of the data for an organization or for a business area. The ER model consists of entities which have attributes and relationships between these entities.

ERModel was originally proposed by Peter in 1976. Simply stated, the ER model is a conceptual data model that views the real world as entities and relationships.

You will find it very interesting in drawing the relationships between various entities.

Advantages of ERModel

ERModel is useful because it facilitates communication between the database designer and the end user during requirement analysis. Following are the main advantages of ER models:

(a) It provides the clear and readable relationship between entities in the system for the database designer as well as for the end user.

(b) It views an enterprise as being composed of entities that have relationships between them.

(c) It maps well to the relational model. The data model can easily be transformed into relational tables.

For designing ER model for any system you need three features of the system:

1) Entities: It specifies real world items in the system.

2) Relationships: It signifies the meaningful dependencies between entities of the systems.

3) Attributes: It specifies properties of entities and relationships.

The graphical representation of these features are :


Entity Type: Generally a collection of entities which share common properties or characteristics. For example, in the database of a University, STUDENT is an entity type. There are many students in a University and they share common characteristics such as: each one has a name, a roll number, department etc.

On the basis of characteristics of entities, they are divided into two categories:

  • · Strong Entity
  • Weak Entity

Strong Entity: It is defined as “An entity type whose existence does not depend on some other entity type”. For example Student entity is a strong entity.

Weak Entity: A weak entity is defined as “An entity type whose existence depends on some other entity type”.

For example, in the database of a company EMPLOYEE is an entity type. Some companies keep information about the dependents of each employee. In this case, the entity DEPENDENT is a weak entity, since the existence of one of its instances depends on the existence of the corresponding instance in the EMPLOYEE entity type.

Attribute: A property or characteristic of an entity type that is of interest to the organization. For example: name is an attribute of the entity STUDENT, similarly roll number also is an attribute of entity STUDENT.

Key Attribute: An attribute that uniquely identifies individual instances of an entity type. For example, roll number is a key attribute of the STUDENT entity type. Each student has a unique roll number.

Derived Attribute: An attribute whose value can be calculated from another attribute is known as derived attribute. For example, the attribute “Years of Experience” for the entity type EMPLOYEE, can be derived from the difference of the current year with the value at the attribute “Start Date” (start date of employment).

Multivalued Attribute: An attribute that may take on more than one value for a given entity instance. For example, “Skills” is an attribute of the entity type EMPLOYEE, that may take more than one value, that is why it is a multivalued attribute.

An ER Diagram is used for representing ERModel.

There are some standard symbols which are used in ERdiagram for representing entity, attributes etc.

Following is a list of symbols used in ERD:


The various types of Relationships are called the Degree of a Relationship which reflect the number of entities associated with a relationship. The nary relationship is the general form for degrees n. Special cases are the binary and ternary where the degree is 2 or 3 respectively.



5.3.3 ER Models and Database Designs

Whenever you are solving any problem of a database design or when you need to save some data in the database, you need to prepare a preliminary list of functions and transactions which the proposed system model must support. For this a activity some of the things need to be done are:

 a) Identify the list of entities and select the key or identifier for each, entity.

b) Prepare a list of relevant attributes for each entity.

c) Identify the relationships between entities in the system.

d) Finally draw a complete ERDiagram which will be the blue print of Database Design.

For example in a University system which keeps a detailed database of the students information. Make an ER diagram for students who are having History as a subject:

1) In this case there are two entities Student and History.

2) Relationship between Student and History subject is that he has history as a subject.

3) Attributes of Student entity are ID, Name , Address, Course.

4) Attributes of History are Course, Teacher, Semester and Department.


In the designing of a database the entities are converted into corresponding table names, and their attributes are defined as the fields of the respective table. The relationship between the entities identify the Primary Key and Foreign Keys or the reference key with which two tables can be connected.

Mapping of ER Diagram into a Database Design

If we take the above example into database design, we will have two tables.

  • · STUDENT
  • · HISTORY

Attributes of the table STUDENT are: ID, Name and Address, and Course and attributes of Table HISTORY are: Course, Teacher, Semester, Department.

These two entities are having relationship (Has). You may identify the Primary and Foreign key in a relation ship is used for the referencing of data between two tables.

The student id which is the primary key in the Student table can be referred as the foreign key in History table.

If we take another example of a software company which handles many software projects. The company controls these projects which are managed by employees of the company. An employee supervises other employees who works on a project.

For designing database for this system, first of all identify the entities in this system and their attributes. Then identify relationships between entities and finally an ERDiagram may be drawn.

Here in this system we may have mainly three entities:

  • · COMPANY
  • · EMPLOYEE
  • · PROJECT

 


The various relationships which hold among these entities are:

– Employee works for a Project

– Company controls Projects

– Employee works for company

– Employee is assigned a Project

An ERDiagram showing relationship among entities Company, Employee and Project is shown in Figure 8:



Comments

Post a Comment

Popular posts from this blog

3.8 SECURE NETWORK DEVICES

 In this unit, we have already learnt that the firewall is only one entry point to your network. Modems, if you allow them to answer incoming calls, can provide an easy means for an attacker to sneak around, your front door (or, firewall). Just as castlesweren't built with moats only in the front, your network needs to be protected at all of its entry points. Secure Modems, DialBack Systems If modem access is to be provided, this should be guarded carefully. The terminal server, or network device that provides dialup access to your network needs to be actively administered, and its logs need to be examined for strange behavior. Its passwords need to be strong not ones that can be guessed. Accounts that aren't actively used should be disabled. In short, it's the easiest way to get into your network from remote: guard it carefully. There are some remote access systems which have the feature of a twopart procedure to establish a connection. The first part is the remote user di...
Read more

3.5 SECURITY ISSUES FOR SMALL AND MEDIUM SIZED BUSINESSES

 Small and medium sized businesses use the Internet and networked applications to reach new customers and serve their existing ones more effectively. At the same time, new security threats and legislation puts increased pressure on business networks to be reliable and secure. Business Challenges According to recent studies, security is the biggest challenge facing small and mediumsized businesses. Everchanging security threats from both inside and outside the business network can wreak havoc on business operations, affecting profitability and customer satisfaction. Small and mediumsized businesses must also comply with new regulations and laws created to protect consumer privacy and secure electronic information. Security issues for small and medium – sized businesses are classified into 5 basic categories: Worms and Viruses As per research, Computer worms and viruses remain the most common security threat, with 75 percent of small and medium businesses affected by it.. Worms and v...
Read more

3.6 TOOLS FOR NETWORK SECURITY

There are many strong tools available for securing a computer network. Tools to protect your enterprise network have been evolving for the last two decades, roughly the same amount of time that people have been trying to break into computer networks. These tools can protect a computer network at many levels, and a wellguarded enterprise deploys many different types of security technologies. The most obvious element of security is often times the most easily overlooked: physical security—namely, controlling access to the most sensitive components in your computer network, such as a network administration station or the server room. No amount of planning or expensive equipment will keep your network secure if unauthorized personnel can have access to central administration consoles. Even if a user does not have evil intent, an untrained user may unknowingly provide unauthorized outside access or override certain protective configurations. Let us briefly discuss few of these network secur...
Read more