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. 

Introduction to DBMS

Database Management Systems – Purpose

Today, Knowledge about database systems is an integral part of education in every field.

A database is a collection of data typically describes the activities pertaining either within the organization or across related organizations.  

A DBMS is software designed to assist in maintaining and utilizing large collections of data. Databases are so widely used today and can be found in organizations of all sizes ranging from large environment agencies   to small businesses and homes.  Everyday activities bring all of us to contact with databases.

We all know that databases are used in different walks of life.  Databases make life much simpler. Railway reservations, hotel reservations, student records in a university are examples with which all of us are familiar. We do not have to stand in long queues and the reservations clerk does not have to fiddle with many files and fill up forms. Different organizations of the governments use databases in a big way. Tax records, crime records, passports, etc. are examples for this kind. All big corporate houses use databases in their day-to-day functioning in HRD, accounts, sales, etc.

When we used credit card the sales person usually waits for computer approval of your purchase before you sign your receipt. This approval process involves consulting a database to verify that your card has been not stolen or lost and to find your card limit, content balance and amount of purchases already approved. The database automatically updated to reflect the new approved amount. Similarly if you visit a doctor you may find that your medical records and billing data have been entered into the database.  If you have prescription filled, your pharmacist may use a database to record data about the prescription check for interactions with drugs you are using currently and print the label and receipt.  Both the doctor and pharmacist may use their databases to do third party billing for you, automatically generating insurance claims for covering expenditures.

Therefore, databases are used satisfy the information needs of many organizations and individuals in a variety of areas. However, a poorly designed database may fail to provide the required information or may provide outdated, flawed, or contradictory information. To maximize the potential benefits, it is important to understand the theoretical foundations internal structure, design, and management of databases. 

If you observe the applications discussed, information is important and hence we need set of concepts and techniques for managing data.

File Systems (late 1960s): As you can see the field of Data Collection and Database Creation begins with file systems during 1960’s. Typical Applications include Pay rolls, Utility bills and Tenth class   examination results of Andhra Pradesh. All of these applications have some Common Characteristics, they are

Batch requirements: That entire means is we have data, which is generated over an interval and based on that you prepare some reports like payrolls. So, you have set of employees and then every month you generate their pay slips, etc.

 Now these applications without responding to any query, it only generates some reports. These reports are rigid formatted and they have one user and one application. They are generally developed in COBOL.

In a typical file system, each department has its own set of applications and its own files. The file is designed specifically for these applications. Also some times the department specifically working with the data processing staff sets policies or standards for the format and maintenance of its files.  For example, in a college environment the admissions office may have programs to keep track of potential students and to generate letters and reports concerning their applications. The admission office decides what information is to be stored what data may be left blank in record, what abbreviations are to be used, how long records are to be maintained and under what situations file is to be updated. Since the department owns the file, this office also controls the access to the information. 

The figure shown on the slide depicts example for the traditional file processing system. In this diagram the accepted student data file, owned by the admissions office is used in a matriculation program to generate a list of matriculated students.  The matriculated students’ data file contains the information about the all active matriculated students. The Course Data file has information about the courses. These two files owned by registrar’s  office along with a temporary file  students ‘s course enrollment requests are used by scheduling program to produce  individual student schedules and class lists. The dean’s office may have its own files for student and course data as well as Deans faculty data file used to assign a final teaching schedule to each faculty. The faculty payroll data file has information needed for calculating pay and producing pay slips  and other pay roll reports. Each program contains details of the physical structure and storage of the file its accesses.

Consider the amount of duplication of labor and storage that these separate files represent. We noted that the records of accepted students may have to be re entered when they matriculate. In the process, new errors may be introduced.  The course data file includes some faculty information which is again repeated in the Dean’s faculty data file. In addition to wasting data entry time and storage space, this duplication leads to data inconsistency since an update such as a change of address may be performed on one file and may not be done in another file. Also when data is isolated in separate files, it is difficult to access information  that should be available.

 Integrated Database Environment:

Use of DBMS will help to overcome the problems of File-processing systems. File system is unsatisfactory because of DATA DEPENDENCE.    Change in requirement may force to change in representation.  Such changes may also indirectly change in programs as well.  Other reasons for using DBMS are Data isolation -   Data scattered in various files in various formats and difficult to write programs, Data redundancy & inconsistency:    Due to storage of data at multiple places. In the presence of updates, these lead to Inconsistency.

Compared to File base approach, the integrated database environment has a single large repository of data called database which is used simultaneously by many departments and users. Instead of disconnected files with redundant data, all data is stored together with minimum amount of repetition. Several different types of records may appear in the database along with logical connections among the data items and records.  The database is not owned by a single department but is shared resource. It is managed by an individual or group called the DBA. DBS is responsible for designing, creating and maintaining the database to satisfy the needs of users.

All access to the database is controlled by a sophisticated software package called DBMS. IT has programs to set up the storage structures load the data, accept data requests from programs and users format the retrieved data, hide the data allow concurrent use of data without having users to interfere  with each other and perform backup and recovery procedures automatically.

The following figure shows how the integrated database environment will support the drawbacks of file system approach.

Advantages of DBMS:  Various advantages of using DBMS are sharing of data, control of redundancy,    Data consistency,    Improved data standards, Better data security,    Improved data integrity, More control over concurrency, Better backup and recovery procedures,   Balancing conflicting requirements and   Better data accessibility.

Disadvantages of DBMS: Various disadvantages of DBMS are High cost of DBMS, Higher hardware costs, higher programming costs, Slower processing of some applications and Increased vulnerability.

Startup with C

Most of you had already know that,C programming is basic for all programming languages.Ofcourse,it it true.Here I would like to share "how to begin your c programming?".This is for beginners only.
  C programming was invented by Dennis Ritchie in Bell laboratories(1969 to 1973).C is developed from beginning as a system programming language for unix.Because of its huge applications,it becomes popular in world wide.

Ok,lets focus our discussion into practical.There are lots of compilers available for C programming.But I recommended to use Borlands Dev-C++ compiler.Check this website to download this compiler.
www.bloodshed.net/download.html

Why Programming Hero?

Programming Hero makes you mr.perfect in programming.Here you can find programming tips and tricks,projects and other stuff related to it.