Chapter 02:Entity Relation Model

The Entity Relationship (ER) data model allows us to describe the data involved in a real world enterprise in terms of objects and their relationships and is widely used to develop an initial data base design. Within the larger context of the overall design process, the ER model is used in a phase called Conceptual database design .

 Database design and ER Diagrams:

      

The database design process can be divided into six steps. The ER model is most relevant to the first three steps.

 1. Requirement Analysis:

 The very first step in designing a database application is to understand what data is to be stored in the database, what application must be built in top of it, and what operations are most frequent and subject to performance requirements. In other words, we must find out what the users want from the database.  2. Conceptual database Design:

 The information gathered in the requirements analysis step is used to develop a high-level description of the data to be stored in the database, along with the constraints known to hold over this data. The ER model is one of several highlevel or semantic, data models used in database design.  3. Logical Database Design:

 We must choose a database to convert the conceptual database design into a database schema in the data model of the chosen DBMS. Normally we will consider the Relational DBMS and therefore, the task in the logical design step is to convert an ER schema into a relational database schema.

Beyond ER Design 4. Schema Refinement:

 This step is to analyze the collection of relations in our relational database schema to identify potential problems, and refine it.  5. Physical Database Design:

 This step may simply involve building indexes on some table and clustering some tables or it may involve substantial redesign of parts of database schema obtained from the earlier steps. 

6. Application and security Design:

 Any software project that involves a DBMS must consider aspects of the application that go beyond the database itself. We  must  describe the role of each entity (users, user group, departments)in every process that is reflected in some application task, as part of a complete workflow for the task. A DBMS Provides several mechanisms to assist in this step.

 Entity Types, Attributes and Keys:

Entities are specific objects or things in the mini-world that are represented in the database. 

For example, Employee or staff, Department or Branch , Project are called Entity. 

Attributes are properties used to describe an entity. 

For example an EMPLOYEE entity may have a Name, SSN, Address, Sex, BirthDate and Department may have a Dname, Dno, DLocation.  

A specific entity will have a value for each of its attributes.

For example a specific employee entity may have Name=’John

Smith’, SSN=’123456789’, Address =’731, Fondren, Houston, TX’,

Sex=’M’, BirthDate=’09-JAN-55

Each attribute has a value set (or data type) associated with it  e.g. integer, string, subrange, enumerated type,   

Types of Attributes:

•      Simple (Atomic) Vs. Composite 

•      Single Valued Vs. Multi Valued

•      S tored Vs. Derived

•      Null Values

 Simple Vs. Composite Attribute: 

l  Simple

 Attribute that are not divisible are called simple or atomic attribute.

For example, Street_name or Door_number. i.e. the division of composite attribute. 

l  Composite

 The attribute may be composed of several components. 

For example, Address (Apt#, House#, Street, City,

State, ZipCode, Country) or Name (FirstName, MiddleName, LastName). Composition may form a hierarchy where some components are themselves composite.

 Single Valued Vs. Multi Valued:

l  Single Valued

 An attribute having only one value. 

For example, Age, Date of birth, Sex, SSN

l  Multi-valued

 An entity may have multiple values for that attribute.

For example, Color of a CAR or Previous Degrees of a STUDENT. Denoted as {Color} or {PreviousDegrees}.Phone number of an employee.

Stored Vs. Derived

 In some cases two are more attributes values are related  for example the age and date of birth of a person. For a particular person entity, the value of age can be determined from the current (today s) date and the value of that person s Birthdate.

 The Age attribute is hence called a derived attribute and is said to be derivable from the Birthdate attribute , which is called stored attribute.

Null Values  

 In some cases a particular entity may not have an applicable value for an attribute. For example, a college degree attribute applies only to persons with college degrees. For such situation, a special value called null is created. 

 Key attributes of an Entity Type:

 An important constraint on the entities of an entity type is the Key or uniqueness constraint on attributes. An entity type 

the Key or uniqueness constraint on attributes. An entity type usually has an attribute whose values are distinct for each individual entity in the entity set. Such an attribute is called a Key attribute, and its values can be used to identify each entity uniquely.

For  exam ple name attribute is a key of the company entity type, because no two companies are allowed to have the same name. For the person entity type, a typical key attribute is socialSecurityNumber (SSN). In ER diagrammatic notation, each key attribute has its name underlined inside the oval.  Relationships and Relationship sets:

      

 A relationship is an association among two or more entities. For example we may have the relationship that Antony works in the Marketing department. A relationship type R among the n entity types E1,E2, ,En defines a set of associations or a relationship set- among entities from these entity types.  Informally each relationship instance r_i in R is an association of entities, where the association includes exactly one entity from each participating entity type. Each such relationship instance r_i represents the facts that the entities participating in r_i are related in some way in the corresponding mini world situation. For example consider a relationship type Works_for between the two entity types Employee and Department, which associates each employee with the department for which the employee works. Each relationship instance in the relationship set Works_for associates one employee entity and one department entity. 

Chapter 1:Overview of Data Base Management Systems

 Data bases and database systems have become an essential component of everyday life in modern society.

Examples for database Applications:

•      Purchases from the supermarket

•      Purchases using your credit card 

•      Booking a holiday at the travel agents 

•      Using the local library 

•      Taking out insurance 

•      Using the Internet 

•      Studying at university 

 Need to store data :

Data originates at one time and used later(i.e.) Store registrations for grading later, Store for future information needs, Governmental regulations requires access to past data, Data used later for auditing, evaluation purpose, Used more than once : save for future use  Limitations of manual methods:

Problems of speed, Problems of accuracy, Problems of consistency and reliability, Problems of poor response time, Problems of work-load handling capability, Problems of meeting ad hoc information needs, Problems of cost, Problems due to human frailties: (misplaced) loyalty, inconsistency, irregularity, difficulties in handling big tasks 

 Why computerized data processing?

Advantage of speed, Advantage of accuracy, Advantage of reliability and consistency, Advantage of storage and retrieval efficiency, Advantage of on-line-access to meet ad-hoc needs, Advantage of cost

Data Base : Collection of related data. By data, we mean known facts that can be recorded and that have implicit meaning. 

Definition of DBMS: A data base management system(DBMS) is a collection of programs that enables users to create and maintain a database. The DBMS is hence a general purpose sof tware  system that facilitate the process of defining, constructing , manipulating and sharing databases among the various users and applications.   Historical development of database Technologies:

•      Early Database Applications: The Hierarchical and Network Models were introduced in mid 1960 s and dominated during the seventies. A bulk of the worldwide database processing still occurs using these models.

•      Relational Model based Systems: The model that was originally introduced in 1970 was heavily researched and experimented with in IBM and the universities. Relational DBMS Products emerged in the 1980 s.

•      Object-oriented applications: OODBMSs were introduced in late 1980 s and early 1990 s to cater to the need of complex data processing in CAD and other applications. Their use has not taken off much.

•      Data on the Web and E-commerce Applications: Web contains data in HTML (Hypertext markup language) with links among pages. This has given rise to a new set of applications and E-commerce is using new standards like XML (eXtended  Markup Language).

 Extending Database Capabilities:

New functionality is being added to DBMSs in the following areas:

Scientific Applications, Image Storage and Management, Audio and Video data management, Data Mining, Spatial data management, Time Series and Historical Data Management  Transaction Management:

Ø  A transaction is a collection of operations that performs a single logical function in a database application

Ø  Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.

Ø  Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database.

A database transaction is a unit of interaction with a database management system or similar system that is treated in a coherent and reliable way independent of other transactions that must be either entirely completed or aborted. 

In some systems, transactions are also called LUW for Logical Units of Work.

In database products the ability to handle transactions allows A single transaction might require several queries, each reading and/or writing information in the database. When this happens it is usually important to be sure that the database is  not  left with only some of the queries carried out. For example, when doing a money transfer, if the money was debited from one account, it is important that it also be credited to the depositing account. Also, transactions should not interfere with each other.  Storage Management:

Ø  Storage management is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.

Ø  The storage manager is responsible to the following tasks: 

v  interaction with the file manager 

v  efficient storing, retrieving and updating of data

 Database Administrator:

A database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In general, these include:

Ø  Recoverability - Creating and testing Backups 

Ø  Integrity - Verifying or helping to verify data integrity 

Ø  Security - Defining and/or implementing access controls to the data 

Ø  Availability - Ensuring maximum uptime 

Ø  Performance - Ensuring maximum performance given budgetary constraints 

Ø  Development and testing support - Helping programmers and engineers to efficiently utilize the database. 

The role of a database administrator has changed according to the technology of database management systems (DBMSs) as well as the needs of the owners of the databases.

Types of Databases and Database Applications

•      Numeric and Textual Databases (Traditional Database)

•      Multimedia Databases (Video clips, pictures, sound message)

•      Geographic Information Systems (GIS)(Weather data, map analysis, satellite images)

•      Data Warehouses (Decision making)

•      Real-time and Active Databases (Internet based (World wide web)) 

Advantages of Using the Database Approach

•      Controlling redundancy in data storage and in development and maintenance efforts.

o   Duplication is wasteful. It costs time and money to enter the data more than once.

o   It takes up additional storage space, again with associated costs.

o   Perhaps more importantly, duplication can lead to loss of data integrity. 

•      Sharing of data among multiple users.

•      Restricting unauthorized access to data.

o When multiple users shares a large database, it is likely that most users will not be authorized to access all information in the database.

•      Providing persistent storage for program Objects o A complex object in C++ can be stored permanently in an Object Oriented DBMS. Such an object is said to be persistence, since it survives the termination of the program execution and can later be directly retrieved by another C++ program.

•      Providing backup and recovery services.

•      Providing multiple interfaces to different classes of users.

•      Representing complex relationships among data.

Database Users:

o   Database administrators: responsible for authorizing access to the database, for co-ordinating and monitoring its use, acquiring software, and hardware resources, controlling its use and monitoring efficiency of operations.

o   Database Designers: Responsible to define the content, the structure, the constraints, and functions or

transactions against the database. They must communicate with the end-users and understand their needs.

o   End users : End users are the people whose jobs require  acc ess to the database for querying, updating, and generating reports; the database primarily exists for their use. There are several categories of end users:

 o Casual End User: access database occasionally when needed. But they may need different information each time.

o   Na ve or Parametric End user : they make up a large section of the end-user population. They use previously well-defined functions in the form of  canned transactions against the database. Examples are bank-tellers or reservation clerks who do this activity for an entire shift of operations.

o   Sophisticated End User : These include business analysts, scientists, engineers, others thoroughly familiar with the system capabilities. Many use tools in the form of software packages that work closely with the stored database.

o   Stand-alone End User : Mostly maintain personal databases using ready-to-use packaged applications. An example is a tax program user that creates his or her own internal database.