DBMS-4,Entiry Relationship Model - Part II

Entiry Relationship Model - Part II

A relationship set consists of all relations of a given relationship type. Relationships may be binary, linking two entities, ternary, linking three entities, or n-ary, linking n entities. Binary relationship instances can be represented as ordered pairs, ternary instances as ordered triples, and n-ary instances as ordered n-tuples. A relationship set is a subset of Cartesian product of the related entity sets. A relationship set always has primary keys of the related entity sets as attributes, but some also has descriptive attributes as well. Descriptive attributes appear in an oval connected to the relationship diamond.   Relationships have cardinality constraints, which specify how many entity instances may be related.  These may be one-to-one, one-to-many, many-to-one, or many-to-many.  In a relationship, each entity has a function called its role in the relationship.  When a single entity is related to itself then the relationship is recursive.

In student-Course-Enroll database, two entity sets are student and course. The entity set student is described with four attributes, namely rollno, name, address and  city. The entity set course is described with course-no, and Course-name and credits attributes.

 SAMPLE ER DIAGRAM  

This is a typical Entity-Relationship diagram representing Conceptual Model of a university database. The university has four entities – Student, Department, Class and Faculty. Each of these has several attributes like a student has a name (StuName), a faculty has a designation (Rank) etc.   
The entities and attributes are:

Student: StuId, StuName, Major, and Credits.

Assumption: Each student has a unique ID and has at most one major.

Department: DeptName, Office.

Assumption:  Each department has a unique name and that each department has one office designated as departmental office.

Faculty: FacId, FacName, DeptName, Rank.

Assumption: FacId is unique and that every faculty member must belong to a department, so faculty is a weak entity with referential dependent on Department.

Class: Course#, Ctitle, FacId, Sched, Room.

Assumptions: The course consists of a department code, a number that identifies the course within the department, and a section code. Also each class is taught by only one faculty member.

Four relationships – Enroll, Dept-Student, Dept-Faculty, Faculty-Class

Dept-Student: is a one-to-many relationship that connects to their major departments.

Assumption: Students have only one major.

Enroll: is a many-many relationship that connects students to the classes in which they are enrolled.

Assumption: Only current enrollments are kept in the database. Grade is a descriptive attribute for this relationship set.

Dept-Faculty: is a one-to-many relationship that connects departments to faculty members assigned to them.

In the above recursive relationship,

Faculty-Class: is a one-to-many relationship that connects faculty members to the Class they teach.

Chair-Member: is a one-to-many recursive relationship on faculty that connects the chairperson of each department to the members of that department.

ER diagram for composite, multi-valued, derived and single attribute for an entity customer is shown below:

 

n the above diagram, age is a derived attribute, phone number is multi valued attribute and name is composite attribute, etc.

Roles:

Entity sets of a relationship need not be distinct. The labels “manager” and “worker” are called roles; they specify how employee entities interact via the works-for relationship set. Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles. Role labels are optional, and are used to clarify semantics of the relationship. The following is another example for defining roles.

 Participation of entity set in relationship set can be either total or partial.  Total participation is denoted by double line. When every entity in the entity set participates in at least one relationship in the relationship set, then it is called total.  In Partial participation, some entities may not participate in any relationship in the relationship set.

For further reading, on this topic, readers  are advised to refer  Chapter 7 from the  prescribed book entitled “ Database System Concepts”,  by Abraham Silberschatz, Henry F Korth and S Sudarshan, Sixth edition, Mc Graw Hill International edition, 2011.

DBMS-III,Entiry Relationship Model - I

Entiry Relationship Model - Part 1

            The title of the module is Entity Relationship model, which is considered as one kind of data model. A data model is basically a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints.  In this lecture, we cover the ER model and discuss how its features allow us to model a wide range of data faithfully.

               Entity – Relationship model was developed by Chen to facilitate database design by allowing the designer to express the logical properties of the database in an enterprise schema. The word enterprise means the organization for which the database is kept. ER model basically  translates the informal description into more detailed and precise  description suitable for implementation by DBMS.               

An enterprise schema is a description that corresponds to the conceptual schema in the three-layer architecture of databases. The model is valid for any kind of DBMS and will remain correct even if the if the DBMS is changed. Entity-Relationship model uses ER diagrams to represent enterprise schema, a conceptual level description that is independent of DBMS. The diagrams are basically design tools and documentation for the system.

                A data model that attempts to capture meaning as well as structure is termed as Semantic Data Model. The items in the model represent “things” in the real - world and the relationships between real - world “things” are expressed by relationships in the model. The environment of the model is described in terms of entities, attributes and relationships. 

                 
     An entity is any distinguishable object in the real - world. Entity is categorized into entity types, and a collection of entities of the same type forms an entity set.  Attributes are representation of properties of the real-world entities. The set of values permitted for an attribute is its domain.  An attribute is mapping of the entity set into the domain of attribute.  Null values occur when an entity instance is missing a value for a particular attribute. For example: Names of  courses offered in a university  are values for course attribute course-name. These values are termed as domain. How each entity is described? Since each entity has several attributes, entity is described by a set of (attribute, data value) pairs. Attribute values describing an entity form a significant portion of the date stored in  the database.

Attribute Types: There are two types of attributes, i) simple and ii) composite. Each attribute in an entity set can be characterized by these  attribute types. 

Simple: when the value of the attribute is not divided, then it is called simple. Otherwise it is termed as composite. Example: name attribute : ( first, middle, last) is

example for composite attribute. Each component attribute can further be divided into subcomponents.

Single valued and multi valued attributes: When an attribute has a set of values for a specific entity, then it is termed as multi valued attribute. Otherwise it is single valued attribute. Phone number is an example for multi valued type attribute whereas rollno is a single valued.

Derived attribute: when we derive the value of a an attribute based on the values of other related attributes or entities then we call derived attribute. 

Key(s): An attribute or set of attributes which uniquely distinguish one entity with another entity. That means No two entities are allowed to have exactly the same values for all attributes. Keys are also useful to identify relationships uniquely and therefore we distinguish relationships from  each other

A relationship is an association or interaction between entities. For example, the two entities Instructor and Course are related by the fact that an Instructor teaches a Course. This is modeled using a ’relationship’ called ‘teaches’.

             A relationship set consists of all relations of a given relationship type. Relationships may be binary, linking two entities, ternary, linking three entities, or n-ary, linking n entities. Binary relationship instances can be represented as ordered pairs, ternary instances as ordered triples, and n-ary instances as ordered n-tuples. A relationship set is a subset of Cartesian product of the related entity sets. A relationship set always has primary keys of the related entity sets as attributes, but some also has descriptive attributes as well. Descriptive attributes appear in an oval connected to the relationship diamond.   Relationships have cardinality constraints, which specify how many entity instances may be related.  These may be one-to-one, one-to-many, many-to-one, or many-to-many.  In a relationship, each entity has a function called its role in the relationship.  When a single entity is related to itself then the relationship is recursive.

            In student-Course-Enroll database, two entity sets are student and course. The entity set student  is described with Four aftributes, namely rollno, name, address and city. The entity set course is  described with course-no, and Course-name and credits attributes.

View of Data, Schemas & Instances, and Data Models ,DBMS-II

View of Data, Schemas & Instances and Data Models

DBMS is a collection of interrelated data and a set of programs that allow users to access and modify the interrelated data. The major purpose of DBMS using DBMS is providing an abstract view of the data is the major purpose of DBMS. Data must be retrieved efficiently from the systems in order the system to be usable. This has led the designers to use complex data structures for data representation in the database. Complexity of the development is hidden from the users who use the database using the concept of abstraction. The purpose of using such abstraction is to simply user’s e interaction with the system.

There are three levels of abstractions. They are

Physical level: Lowest level and describes how the data are actually stored. It describes in detail what complex data structures are needed to store the data on disk.

Logical Level:  This is the middle level of abstraction. Its purpose is to describe what data are stored in the database and what relationships exist among those data. This level describe the entire database in simple structure. Of course each simple structure at logical level may use complex physical level structures. Users who use logical level need not worry about or need not know about this representation of physical structure. Generally what information need to put in the database is decided by administrators using this logical level.   

The ability to change scheme definition at one level without changing schema definition at the next higher level is termed as Data Independence.

Logical data independence:  The ability to change scheme  definition conceptual level  without changing the scheme definition at the view level is termed as logical data independence. 

Physical data independence: The ability to change scheme definition at physical level without changing the scheme definition at the conceptual level is termed as physical data independence.

View level:  This is the highest level of abstraction used to describe only small part of the database.  Most of the users who use the database need to know only  part of the database only. This abstraction simplifies the  user’s interaction with the system .  That means  view describes only part of the data at logical level. Also different views for different users possible for same database.  For example,    Student address not visible to person computing grades based on the marks secured in different tests.  So here the address of the student is not important in deciding grades.

Instance and schema : A collection of information  stored in the database at a particular point in time is termed as an Instance of the database. The complete design of the database is called the database schema. Changes in schema is very rarely changed and it is more or less permanent whereas the instance of the database schema change when new information is added or deleted.  Database system can  several schemas, partitioned into levels of abstraction.

Data Models: The logical model is the heart of the database. The logical model is the heart of the database. A good logical design is easy to implement and supports the desired external views. Using data model, we can describe the design of database at all three levels, namely, physical, conceptual and logical levels.

 Relational, network and hierarchical data models belong to record based kind. ER model is of semantic data model. Hierarchical data model uses the a tree structure to hold data in its nodes or segments. Each occurrence of the structure begins with the root node. The entire database extension consists of multiple occurrences of the tree each of which starts with an occurrence of the root node.

The network model uses a network or plex structure which consists of nodes and branches. Unlike a tree a network allows a node to have more than one parent or owner node o which it is dependent. Pointers are used to represent relationships in the network model. Network and hierarchical data models are very close to implementation and complicates the task of modeling data.

Relational Data Model: This model uses a collection of tables for representing data and the relationships among the data. Tables are termed as relations.  Each table is associated with fixed set of fields and types. More details about this model will be discussed in subsequent lectures.

A DBMS based on the relational data model is termed as Relational DBMS. A RDBMS not only manages the data but also responsible, but not limited to, for managing the following important functions:

a)      Managing of data and relationships stored in the database. System creates data dictionary as a user created database. Data dictionary is a system oriented dictionary and stores metadata ( data about data).  Information about  tables names, attribute names, data types, physical space, relationships, and so on are part of this meta data.

b)      DBMS system manages the day – day transactions

c)       DBMS system allows the users to specify validation rules.  Enforcing only possible values for sex are only male( M) and female (F) for attribute SEX is an example for validation rule.  Users can set validation rules to keep incorrect values for being accepted. That means we can enforce only correct values to enter into the database.

d)      Access through passwords, encryption and restricted user rights are possible using DBMS.

e)      Provides mechanism to use  backup and recovery procedures for physical security of data is another function which is important in DBMS.

f)       Sharing of data with data locking possibilities is allowed in DBMS

g)      DBMS transforms logical requests to match physical data structures is taken care very well in DBMS.

There are two types of data independence. These are useful for insulating the applications

There are two types of logical models. Relational, network and hierarchical data models belong to record based type and the other kind of model is of type semantic data model.  Entity Relationship model belong to semantic data model.

Database Languages: To specify the database schema, Data definition language is used and data manipulation language is used to write queries and update the data stored in the database. In fact both are part of the most widely used query language called Structured Query Language. Various types of access possible are retrieval information from the database, inserting new information into the database, deleting the existing information from the database and modification of information that present in the database. There are two types of DMLs. One is called procedural, which require a user to specify what data are needed and how to get those data. The other one is called Declarative  DMLs, in which user specify what data are needed without specifying how to get those data.  Relational algebra and relational calculus are theoretical languages where as SQL, QUEL, are of commercial query languages.  In subsequent lectures, we study more about relational algebra, relational calculus and SQL in detail for query writing and how to get the data from the database.