* E-R Model: The entity relationship data model is based on perception of the real world as a collection of basic objects called entities and relationship among them. The E-R model is one of several semantic data models and represents the overall logical structure of the data base.
Basic concept:
Entity: An entity is an object, thing or event about which we store information. An entity may be concrete (tangible) such as person, a book or it may be abstract (non tangible) such as a loan, bank account, holiday, accident or a concept. An entity has a set of properties these are called attributes (or fields). Some attributes uniquely identify an entity. For example, the entity is customer and its attributes are customer number, name, street and city. Here the attribute customer number uniquely identifies a customer and hence it is called entity identifier.
Entity set: an entity set is a set of entities of same type. For example, the set of all customers can be defined as an entity set customer.
Customer
Each entity has a value for each of its attributes. For example, for the first entity customer number has value 101, name has value sachin etc. customer number uniquely identifies a customer. However city = Mumbai does not identify uniquely a record but a group of customer. Entity set need not be disjoint.Domain (value set): Each attribute has a permitted set of values called the domain of the attribute. For example, the domain of the attribute customer might be the set of all text strings of certain length.
For example, for the attribute marks obtain in a subject the domain could be 0 to 100.
Relationship: A relationship is an association among several entities. For example, the entity set customer and loan can be associated by a relationship cost-loan.
Attribute Types:
(1) Simple attribute: an attribute which is not divided into subparts is called simple attribute. For example, in our example all attribute are simple.
(2) Composite attribute: An attribute which can be divided into sub parts i.e. other attribute is called a composite attribute. For example, the attribute name can be structure as a composite attribute consisting of first name. Composite attribute date can be sub divided as date-month-year. Composite attribute group together related attributes which makes a modeling leaner.
(3) Single valued attribute: An attribute which has only one value is called a single valued attribute. In our e.g. each attribute is single is single valued.
(4) Multi valued attribute. (5) Derive attribute.
Updating: In certain situation we may wish to change a value in tuple. Without changing all values in a tuple. We can be the generalized projection separator to do this task.
R ← ∏ f1, f2, ------ fr (r)
Where, each fi is either the ith attrinutes 0 or r, if the ith attribute is not update or if the attribute is to be update fi is an expression involving only constants and the attribute or r thatr gives the new value for the attribute. Example suppose that interest payments re being made and that all branches are to be increased by 5 present, we write ← ∏ account number branch name, balance * 05.
*Relationship: Relationship is an association among entities. A relationship is a reason for associating two entity types. These relationships sometimes called binary relationships. Since they involve two entity types. For example, A person has lunch at the hotel, is an association between person lunch and hotel. The entities involved in a given relationship are said to be the participants in that relationship. The number of participants in that relationship is called the degree of that relationship. A relationship is information about group of entities. There are three types of relationships are as follows:
(1) One-to-one: In a one to one relationship one entity relates to the other entity. For example,
City sale salesman
In above example, salesman can sale in only one city and city can have only one salesman.
(2) One to many: This relationship connects one entity to many entities. For example,
Salesman sale city
In above example, the salesman can sale in many cities but one city can have only one salesman hence the relation is one to many.
(3) Many to many: this relationship connects many entities to many other entities. For example,
City sale salesman
In above relationship one city can have many salesmen and one salesman can sale in many cities.
*Relation: A relation is a collection of domains. A relation of two parts. (1) A heading. (2) A body.
A heading part consists of a attributes and a body consists of a set of tuples that may vary with times.
Properties of relation:
(i) No duplicate rows: While following a relational concept a table can not have duplicate rows. Since there are no duplicate rows there is always a primary key. (ii) Rows are unordered: In relation the rows are unordered. (iii) Attributes are unordered: The attribute in relation are also unordered.
Domain: The domain is the set of data values are of same type. Thus we can real domain values from which the actual values in the attributes are drawn.
Example: In domain cities all the name of cities are placed and both the table named as customer and product holds the name of cities and both these two columns drawn their values from the same domain
Customer Domain: Cities Product
City London City
London Delhi Madras
Delhi Singapore Hong Kong
Singapore Paris, Berlin Singapore
Geneva New York, Geneva
Madras
*Keys: A key allows us to identify a set of attributes that suffice to distinguish entities from each other. Keys also help to uniquely identify relationship and this distinguish relationships from each other
Super key: It is a set of one or more attributes that, taken collectively, allows us to identify uniquely an entity in the entity set.
Candidate key: It is a minimal super key i.e. a super key which does not have any proper subset which is also a super key.
Primary key: It is a candidate key that is chosen by the database designer as the principle means of uniquely identifying entities within entity set.
* Weak and Strong Entity set: An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set. The weak entity set must be part of a one to many relationship set. The concept of strong and weak entity sets are related to the existence dependencies. A member of strong entity set is by definition a dominant entity where as member of a weak entity set is a subordinate entity. A weak entity set does not have a primary key. The discriminator of a weak entity set is a set of attribute that allows this distinction to be made. The discriminator of a weak entity set is also called the partial key of the entity set. The primary key a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependence plus the weak entity set discriminator. The identifying dominate entity set is said to own the weak entity set that it identifies. The relationship that associated the weak entity set with an owner is the identifying relationship.A weak entity set is indicated in E-R diagram by doubly out lined box and the corresponding identifying relationship by a doubly outlined diamond. In above diagram, the weak entity set payment is dependent on the strong entity set loan via the relationship set loan payment. A weak entity set may participate as owner in an identifying relationship with another weak entity set. Some cases the database designer may choose to express a weak entity set as a multi-valued, composite attribute of the owner entity set.
Representation Of strong entity set: A strong entity set E with descriptive attribute a1, a2, ------, an is represented by a table called e with n distinct column each of which corresponds to one of the attributes of E. each row in this table corresponds to one entity by inserting a row into the table. We may delete or modify rows.
Representation of weak entity set: Let A be a weak entity set with attributes a1, a2, -------ar. Assume that the subset {a1, a2,-----ak} of the setoff attributes of A is the discriminator. Let B be the set of attributes {b1, b2, ----bn}. The weak entity set A is represented by a table called A iwith one column for each attribute of the following set of attribute {a1, a2, -------ar} U{b1, b2,----bn}.
* E-R Diagram: The E-R model consist of the following major components.
(1) Rectangles, which reresent entity set.
(2) Ellipses, which represent attributes.
(3) Diamonds, which represent relationship set.
(4) Lines, which link attribute to entity sets and entity sets to relationship sets.
(5) Double ellipses, which represent multi valued attribute.
(6) Dashed ellipses, which denote attributes.
(7) Double lines, which indicate total participation of an entity in a relationship set.
Attribute of an entity set that are member of the primary key are underlined. Consider the entity relationship, which consists of two entity sets, customer and loan, related through a binary relationship set borrower. The attribute associated with customer are customer name, social-security, customer-street and customer-city. The attributes associated with loan are loan-number and amount. The relationship set borrower may be many to many, one to many, many to one or one to one. To distinguish among these, we draw either a directed line (←) or an undirected (-) between the relationship set and the entity set in question.
(1) A directed line from the relationship se borrower to the entity set loan specifies that borrower is either a one to one or many to one relationship set from customer to loan. (2) An undirected line from the relationship set borrower to the entity set loan specifies that borrower is either a many to many or one to many relationship set, from customer to loan. Returning to the E-R diagram we see that the relationship set borrower is many to many. If the relationship set borrower were one to many from customer to loan then the line from borrower to customer would be directed, with an arrow pointing to the customer entity set. If the relationship set borrower were one to one then both lines from borrower would have arrow one pointing to the loan entity set and one to customer entity set.
* The schema and Sub schema: It is necessary to describe the organization of the database description are used by the database management software in extracting from the database those data items which users require. The overall logical database description is referred to as a schema. The world schema and sub schema were first brought into wide usage by the CODASYL data base task group. (CODASYL- Conference on data system language). It is sometimes also referred to as an overall model of the data, a conceptual model, or a conceptual schema. A schema is a chart of the types of a data that are used. Names of the entities and attribute, and specifies the relations between them. It is frame work into which the values of the data items can be fitted.
UNIT 1 Ends Here Oracle for B.SC Final Year
<<