Chapter 2 Modelling using EntityRelationship Diagram Introduction Before

Chapter 2: Modelling using Entity-Relationship Diagram • Introduction Before designing a database, the data processing needs of the organisation should be determined. This can be achieved through interviewing employees or by holding discussions or circulating questionnaires. Not only the current needs of the organisation should be taken into account but as far as possible its future needs should also be ascertained. It is therefore important to note that – Users information need should be carefully analysed before designing a database. Any misinterpretation would result in database that does not meet user’s requirements.

Chapter 2: Modelling using Entity-Relationship Diagram • Once the user’s requirement is established, designing of the database can begin. The two components of a database design are logical design and physical design. In order to create logical database design – objects, entities and relationships needs to be identified.

Chapter 2: Modelling using Entity-Relationship Diagram • For example, suppose a database is needed to be designed to store the details about the people (stakeholders) associated with the organisation. In this case, people associated could be identified as suppliers, customers, employees, consultants, legal advisors. Once all the stake holders are identified and the relationship between them is established, it is then represented using a diagram.

Chapter 2: Modelling using Entity-Relationship Diagram • The diagram that helps in logical designing of a database is referred to as Entity Relationship Attribute (ERA) model. This model helps in identifying and organising data logically. Figure

Chapter 2: Modelling using Entity-Relationship Diagram • Entities, Attributes and Identifiers Consider Dg. GLASS Inc. , as a manufacturing unit that designs and makes bottles of various sizes, colours and capacities. Its client includes leading manufacturers of cosmetics, soft drinks and pharmaceuticals. The business cycle at Dg. GLASS Inc. can be illustrated as:

Chapter 2: Modelling using Entity-Relationship Diagram • An Entity is an object with a distinct set of properties. An entity could be a place, a person, a concept or an activity for which data collection is possible. In simple terms, entity can described as something in the real world that is needed to be tracked. An entity has one or more characteristics.

Chapter 2: Modelling using Entity-Relationship Diagram • In the case of Dg. GLASS Inc. , the Suppliers of the organisation are different from the customers of the organisation. Therefore, suppliers could be listed as one entity and the customers as second entity. Similarly, departments or units, items (raw material and finished goods) and marketing channel (Distributors, Retailers) can be identified as other entities with respect to Dg. GLASS Inc. The entities identified here are just the partial list and can be extended to include other entities that impact the business.

Chapter 2: Modelling using Entity-Relationship Diagram • Each of the entity is expressed using its attributes or properties. An attribute is a quality or characteristic of an entity that describes or identifies an entity. For example, the customer entity can have the attributes such as customer code, customer name, customer address, customer contact number and credit rating.

Chapter 2: Modelling using Entity-Relationship Diagram • The details pertaining to the characteristic (or quality) of an entity is termed as an instance of the entity. For example, data on customer Divyanshu Jha will include his code (C 001), address (A 1/502 Neelyog Apts. , ), contact number (2 456 1234) and credit rating (A+). Thus, C 001, Divyanshu Jha, A 1/502 Neelyog Apts. , 2 456 1234, A+ - becomes an instance of the entity customer.

Chapter 2: Modelling using Entity-Relationship Diagram • A key or an identifier is an attribute used to identify unique instances of entities or to set the relationship between the entities. The various keys that an RDBMS support was introduced in the first chapter and will be discussed further in the subsequent chapters.

Chapter 2: Modelling using Entity-Relationship Diagram • Associations, Relationship and mapping A relationship is an association between two entities. For example, with respect to Dg. GLASS Inc. . , Works In is a relationship between the entities Employees and Department

Chapter 2: Modelling using Entity-Relationship Diagram • Identifying associations or relationships among different entities forms an important step in designing databases. Not only two entities could be related to each other but also attributes within an entity can be related to each other. Associations or relationship represent business rules – so it is important to carefully interact with users to identify the relationships. Correctly identifying relationships is important to properly designing a database application.

Chapter 2: Modelling using Entity-Relationship Diagram • There can be three types of relationship that can exist between two entities – one-to-one (1: 1), Oneto-Many or Many-to-one (1: N) and Many-to-many (N: N) - some other design methods use, the asterisk (*) or the letter ‘M’ to read as “many” or the multiplicity in an association.

Chapter 2: Modelling using Entity-Relationship Diagram • One-to-one Relationship (1: 1): One -to-one relationship can be best described as - for every instance of the first entity there exists exactly one instance of second entity.

Chapter 2: Modelling using Entity-Relationship Diagram • One-to-Many Relationship: The entities are related in a one-tomany relationship (1: N), where for every instance of the first entity, there can be zero, one or second instances of second entity. And for every instance of the second entity there is exactly one instance of the first entity.

Chapter 2: Modelling using Entity-Relationship Diagram • Many-to-many Relationship: Two entities are related in a Many-tomany relationship (N: N), when for ever instance of the first entity, there could be multiple instances of the second entity. Also, for every instance of the second entity, there could be multiple instances of the first entity.

Chapter 2: Modelling using Entity-Relationship Diagram • It is clear that a customer can place many order and each of these order may contain zero (not so possible), exactly one or many items. However, it does not specify as to how many customers can be involved with one particular order? Many businesses would specify that each of the order can come from only one customer and hence there could be one-to-many relationship (remember, every one-to-one relationship is also an example of one-to-many relationship) while some organisation might have multiple customers on one order, which creates a many-to-many relationship. Such a mapping is also referred to as a complex mapping.

Chapter 2: Modelling using Entity-Relationship Diagram • A virtual model (referred to as E-R diagram or class diagram, these are further explained in detail in later section) of the entities and association for the above example can be illustrated as:

Chapter 2: Modelling using Entity-Relationship Diagram • Association Specifics: N-ary Associations A relationship, as described in the earlier section, is an association between two entities and each relationship is identified by a name that describes the relationship. Ideally relationship name is an active verb, but passive verbs are also used. Verbs such as paints, learns and manages make good relationship names.

Chapter 2: Modelling using Entity-Relationship Diagram Thus, the relationships 1: M, M: N and 1: 1 can be summarised as:

Chapter 2: Modelling using Entity-Relationship Diagram • A relationship’s degree indicates the number of associated entities or participants. • A Unary relationship exists when an association is maintained within a single entity.

Chapter 2: Modelling using Entity-Relationship Diagram • In the case of unary relationship shown in figure 2. 14, a member within the MEMBER entity is a prerequisite for another member within that entity. In this case existence of a member prerequisite means that a MEMBER requires a MEMBER – that is an individual before becoming a member has to be introduced by another member – MEMBER has a relationship with itself. Such a relationship is also known as a recursive relationship.

Chapter 2: Modelling using Entity-Relationship Diagram • A binary relationship exists when two entities are associated

Chapter 2: Modelling using Entity-Relationship Diagram • A ternary relationship exists when three entities are associated. The ternary relationship is briefly explained below: • Many-to-many associations between entities being complex cause problems in the database design and therefore have to be split into one-to -many relationship.

Chapter 2: Modelling using Entity-Relationship Diagram • Consider a computer assembly station in which technicians assemble components into final products (a customised computer workstation). The entities identified are technicians (having attributes such as tech_id, name…), components (having attributes such as comp_id, type, name…), and products (having attributes such as prod_id, type, name…). There exist two many-tomany relationship as represented below

Chapter 2: Modelling using Entity-Relationship Diagram • The Complexity that arises out of many to many relationships can be understood by visualising a situation were the company wants to know which technician used what components while assembling a product. This design specific (the three entities and the corresponding attributes) does not help in keeping track of which technician worked on what product and which of the components went into each product.

Chapter 2: Modelling using Entity-Relationship Diagram • Since, it is possible that a technician may use more than one component to assemble a single or many products. The three main entities, technicians, components and products, have to be actually related through an association (say assembly). These associations are represented using diamond sign and are described with its own set of attributes. When two or more entities are related, the relationship is called an n-ary association. Figure 2. 16 shows the relationship between the three main entities

Chapter 2: Modelling using Entity-Relationship Diagram • The association assembly will have attributes such as tech_id, prod_id, comp_id. Each of the process is captured by the association assembly. The assembly association solves the many-to-many problem, since each row of the association assembly hold a data for one technician, one component and one product.

Chapter 2: Modelling using Entity-Relationship Diagram • In the actual business scenario, the manyto-many relationships must be converted to a set of one-to-many relationship by adding different associations between the entities that would help link them. The associations usually represent the activities within the business and often includes date/time stamp to record the moment when the transaction actually occurs. For example, a date/time column when included in association assembly will help when each event occurred.

Chapter 2: Modelling using Entity-Relationship Diagram • The basic definitions can be summarised as follows:

Chapter 2: Modelling using Entity-Relationship Diagram • Entity Relationship Diagram ER diagrams are tools for a database designer to graphically display the logical database design of a system. It is one of the best known semantic modeling approach, introduced in 1976, by Chen and has been continuously refined and modified since then.

Chapter 2: Modelling using Entity-Relationship Diagram • An ER diagram represents entities, the relationships that exist between the entities and the attributes of these entities.

Chapter 2: Modelling using Entity-Relationship Diagram • It also helps the designer in identification of primary keys, type of mapping between relations, naming of relations etc, Most importantly it help in identifying the things or entities that requires continuous observation and tracking.

Chapter 2: Modelling using Entity-Relationship Diagram • Using the standard symbols a conceptual schema or overall logical design is created. E-R diagrams are often reviewed repeatedly till it satisfies all of the user’s information requirements.

Chapter 2: Modelling using Entity-Relationship Diagram • After finalising the logical model, it is converted into a physical model which is then implemented as a relational database. Implementation of E-R diagram is normally done by DBA using a Data Definition Language.

Chapter 2: Modelling using Entity-Relationship Diagram • Any business comprises of many processes and each of the processes is further tracked with the help of transactions. The main processes of the business are referred to as Master Business Processes. For example, in the case of Commercial banking business model the master processes could be related to Savings Account, Current Accounts, Fixed Deposits, Equity Management, Loan Accounts… while for Dg. GLASS Inc. , the master processes could pertain to Procurement of raw material, Shop floor Management, Warehousing, Customer Management and so on.

Chapter 2: Modelling using Entity-Relationship Diagram • Each of the master processes of the commercial banking business model and Dg. GLASS Inc. can be described as an entity of the business model. Each of the entity are characterised by fixed set of attributes and are further decomposed into various subprocesses and activities. The processes of decomposition of tables are explained in later chapters.

Chapter 2: Modelling using Entity-Relationship Diagram • Entities can either exist independently or be related to each other. The independent entities are referred to as strong or regular entities while the dependent entities are known as weak entities.

Chapter 2: Modelling using Entity-Relationship Diagram • A weak entity is thus, an entity that is existence dependent on some other entity and it cannot exist if that some other entity does not exist. For example, Employees Dependents could be a weak entity – they would exist only if the relevant employee exists.

Chapter 2: Modelling using Entity-Relationship Diagram • It therefore becomes important not only to identify the entities and their attributes but also the relationship that exists between the entities. The ER diagrams have conventionally been used to describe the entities, all its attributes and the relationships between the entities.

Chapter 2: Modelling using Entity-Relationship Diagram • The ER diagram provides a simple and readily understood means of communicating the salient features of the design of any given database. Once the ER diagram is made, the designer using a DBMS can transform them into tables. These tables are then used to record, store, retrieve and manipulate the entity’s business data. Symbols

Chapter 2: Modelling using Entity-Relationship Diagram • Illustration-1: Every business has some items to offer and each one of them is expected to have customers: A simple ER diagram representing the process of customer buying the items is shown in figure 2. 17

Chapter 2: Modelling using Entity-Relationship Diagram • Illustration-2: Ternary relationship as described in figure 2. 16 can be further represented and expanded to include table structure (as in Figure 2. 18). In this illustration many-tomany relationship is converted to a set of one-to-many relationship with an n-ary association.

Chapter 2: Modelling using Entity-Relationship Diagram Problems 1. In a hospital a set of doctors are available to check the patients that visits the hospital during the OPD. For every test conducted on a patient a log is maintained. Draw an E-R diagram for the hospital management system representing the above scenario.

Chapter 2: Modelling using Entity-Relationship Diagram 2. A motor insurance company provides insurance against accidents to the owners of cars. The company has large number of policy holders and would like to store details about the policy holders, the cars they own and the accidents as and when it happens. Construct an ER diagram representing all of the above

Chapter 2: Modelling using Entity-Relationship Diagram 3. The Public Works Department (PWD) intends to store some of it’s very critical data and automate it’s information system. The activity begins with requirement analysis phase. The findings of the requirement analysis are formerly recorded and listed as:

Chapter 2: Modelling using Entity-Relationship Diagram • The PWD in its first phase of automation is inclined towards keeping track of employees, departments and projects. The working of the PWD is as follows: • The activities of PWD are taken care by many inter-linking departments. For the ease of administration each department is positioned at more than one location. Each department has unique department number, a name and an employee who heads the department (Manager).

Chapter 2: Modelling using Entity-Relationship Diagram • At any given instance of time several construction projects are handled by the PWD. Every project is controlled and monitored by exactly one department though it may get the task performed by other department/s. Some of the departments are categorised as purely non -technical and these departments are not assigned the task of controlling and monitoring the project – they just participate in project. Each project is identified by an unique project code, project name, department number of the controlling department, and its location.

Chapter 2: Modelling using Entity-Relationship Diagram • Each department has number of employees with different skill set to carry out the task associated with the projects. The employees details include employee code, name, designation, address, date of birth, basic salary. Since most of the employees perform critical assignments, the PWD would like to store the details of the dependents of the employees for the purpose of insurance. Every employee reports to a supervisor and is allotted a department. An employee may be assigned a task in one or several projects and these projects may or may not be controlled by his/her own department.

Chapter 2: Modelling using Entity-Relationship Diagram • With respect to the above findings, construct an ER model that will include identification of entities, primary keys, relationships and mapping, cardinality ratios and other constraints. • The entities identified in the findings of requirement analysis are: ER Diagram

Chapter 2: Modelling using Entity-Relationship Diagram 4. A database needs to be created to track the movies being played in several theatres across various towns of the state. The information about STATE, CITY, THEATRE NAME, LOCATION, MOVIE NAME, ACTORS, DIRECTOR, TICKET PRICE, SHOW TIME, RATING OF MOVIE and REVIEW OF MOVIES needs to be stored. Design a conceptual schema using ER modelling that will include all of the following: • Grouping of related attributes into several entities • Identification of various possible associations between the several entities • Identification of key attributes • Identification of the cardinality ratio for every relation. Entity identified and relationship ER Diagram

Chapter 2: Modelling using Entity-Relationship Diagram REVIEW QUESTIONS 1. Draw ER diagram for the following: • An educational institute has 10 departments. Departments have diploma, degree, post graduate courses. Every department may or may not have all the courses. Students register for a course, Course is divided between 6 to 8 semesters. Every semester has well defined syllabus. Syllabus may have 1 to 6 subjects and/or project. Every department has some faculty associated with it. Every faculty member is able to teach upto 4 subjects offered for that semester. Every faculty member is assigned upto 2 subjects per semester depending on his/her ability and department’s need. Minimum 1 subject is assigned to every faculty member. Depending on syllabus definition every subject has between 2 to 5 lectures and few lab periods. Lectures are arranged in class rooms belonging to department. Lab periods are arranged in laboratories which are attended and managed by non-teaching staff associated with every department. Teachers are expected to supervise the lab periods. The lecture and lab supervisors load for every teacher is well defined in his/her time table.

Chapter 2: Modelling using Entity-Relationship Diagram • All the students who have registered for the course form a class. Class has a time table which gives schedule for covering all the subjects of that course. At the end of semester teacher submits a report of every student, books referred for the subject, assignment given and marks secured by the students, (University of Mumbai, MCA – April 2001)

Chapter 2: Modelling using Entity-Relationship Diagram 2. Draw ER diagram for the college library which has about 3000 college embers of different categories (post graduates, under graduates, junior colleges etc. , ), 150 staff members. The facilities as well as charges differ for different categories. Library subscribes for periodicals (of different periods) which are bound once every year and are issued as books. Number of books in the library is about 100000. Different editions of same book may be in stock. Tracking a book by broad subject definition and also by author’s name is requirement. (University of Mumbai, MCA – Nov 2001)

Chapter 2: Modelling using Entity-Relationship Diagram 3. Draw ER diagram for: - A simplified reservation system for airline. Airline has different type of airplanes and more than one airplane of each type. Individual airplane is described an unique id and number of seats installed in three classes. • Airplane type has a type name and manufacturing company. Airports which are described by code and complete address are supportive of landing/take-off of certain specific types of aircrafts. • Departures and arrivals having time attribute are of a specified flight leg instance. (A leg is a non stop portion of a flight). A leg instance is further specified by date and number of available seats. Customers make reservations for a particular class and particular leg instance. • The actual arrival/departure times may be different from scheduled arrival/departure times. • Entire flight consists of many flight legs and reservations could be for more than one leg of some flight. (University of Mumbai, MCA – May 2002)

Chapter 2: Modelling using Entity-Relationship Diagram 4. For a college library requirement draw an ER diagram with all mapping constraints library maintains number of books, many with multiple copies. Books are given sequential accession numbers, when purchased. Each book has unique ISBN number subscribers are entered when they pay fees along with their email addresses. Main functions to be supported are • Enter new book details as they are displayed. • Access copies of the books bought • Borrow, return and make claims on the book • Enter new subscription • Collect fine when books are after the due date • Make any assumptions that are required and state that clearly. ((University of Mumbai, MCA – May 2000)

Chapter 2: Modelling using Entity-Relationship Diagram 5. Draw an ER diagram appropriate for the following scenario: • An insurance company wishes to create a database to keep track of its operations. Many policy holders may hold policy. An Agent sells policies, will all policies being sold by only agents. Only one agent services a policy holder. An agent normally works in one office, although some agents work from home. An office may have several agents; Policy details held include the policy number (unique), policy date, policy type and insurance amount. • Policyholder details include policyholder number (unique), name, address and phone number. Agent details include code (unique), agent name, salary and commission (3% of total policy insurance amount sold). Office details include the office name (unique), office address, office phone number and manager name. (University of Mumbai, MCA – April 2004)

Chapter 2: Modelling using Entity-Relationship Diagram 6. Draw an ER diagram appropriate for a Railway Ticketing System. (University of Mumbai, MCA – May 2005)

Chapter 2: Modelling using Entity-Relationship Diagram 7. “A large bank has several branches at different places. Each branch maintains the accounts details of the customers. The customers may open joint or single account. The bank also provides the loan to the customer for different purposes. Bank keeps record of each transaction by the customer to his account. All of the branches have employees and some employees are managers. ” • Construct an ER diagram for the Banking Application. Document all the assumptions that you make about the mapping constraints. (University of Mumbai, MCA – Nov 2006)

Chapter 2: Modelling using Entity-Relationship Diagram 8. Consider the following scenario: A brewery wishes to maintain a database of its delivery operations over the current year. The brewery has a number of pubs, with each pub having one manager. A manager manages only one pub, although some pubs have no managers for some periods. Every working day, each number of lorries makes a number of deliveries to different pubs in its daily run and a pub gets many deliveries in the year. Different drivers may drive a lorry on different days with some drivers driving many lorries over the year. Pub details include Pub name, number of bars and address. Manager details include manager number, manager name and salary. Delivery details include delivery number (unique only within a run), delivery date and number of kegs delivered. Run details include run number, run date, number of miles traveled. Lorry details include lorry number, make, model and date driver drives the lorry. Driver details include driver number, driver name and salary. You may assume, unless otherwise stated, that the identification numbers contain unique values. • Draw an ER diagram appropriate for the above • Modify the ER diagram to change to the following requirements: “the delivery can be made using either a lorry or a tempo and each vehicle is driven by the same driver on all occasions. ” (University of Mumbai, MCA – April 2004)

Chapter 2: Modelling using Entity-Relationship Diagram 9. Explain the difference between the weak and string entity set. Also outline what sort of redundancy will result if we make a weak entity set into string entity set by adding to it attributes the primary key attributes of its identifying entity set. (University of Mumbai, MCA – April 2004)

Chapter 2: Modelling using Entity-Relationship Diagram 10. For the following ER diagram which models an online bookstore: • List the entity set and their primary keys • If the book-store adds music cassette and compact disks to its collection, same music items may be present in cassette or compact disk format, with different prices. Extend the ER diagram to model this addition, ignoring the effect on shopping baskets. • Extend ER diagram, to model the case where a shopping basket may contain any combination of books, music cassettes or compact disks. (University of Mumbai, MCA – April 2004)

Chapter 2: Modelling using Entity-Relationship Diagram