You may give an example of movie versus DVD – degree is two and cardinality is one to many. Required attribute; optional attribute. A required attribute must have a value for each entity instance, whereas an optional attribute may not have a value for every entity instance. You may give an example of degree versus major in student entity in a university student registration system – degree is mandatory attribute and major is optional. Data dependence; data Independence.
With data dependence, data descriptions are Included with the application programs that use the data, while with data independence the data descriptions are separated from the application programs. Give example of file processing system versus database systems. In file processing systems, the applications have to incorporate the data description of files and the logic to access them. Structured data; unstructured data. Structured data refers to facts related to objects and events of importance in the users environment and represent the traditional data that is easily stored and retrieved in traditional databases and data warehouses.
Unstructured data refers to multimedia data, such as images, sound ND video segments that are now stored as part of the user’s business environment. Data; Information. Data consist of facts, text, and other multimedia objects, while information is data that have been processed in such a way that it can increase the then add context to show how this data now makes sense. Question 2 – Write the business rules reflected in the following ERE diagram. Where maintenance indicates each maintenance visit of a car and Maintain line indicates each maintenance procedure in a maintenance visit. 10 marks) Answer 2 – Given the ERRED shown above, you can identify the following business rules: 1. A customer can own many cars. 2. Some customers do not own cars. 3. A car is owned by one and only one customer. 4. A car may generate one or more maintenance records. 5. Each maintenance record is generated by one and only one car. 6. Some cars have not (yet) generated a maintenance procedure. 7. Each maintenance procedure can use many parts. (Comment: A maintenance procedure may include multiple maintenance actions, each one of which may or may not use parts.
For example, 10,000-mile check may include the installation of a new oil filter and a new air filter. But tightening an alternator belt does not require a part. ) 8. A part may be used in many maintenance records. (Comment: Each time an oil change is made, an oil filter is used. Therefore, many oil filters may be used during some period of time. Naturally, you are not using the same oil filter each time – but the part classified as “oil filter” shows up in many maintenance records as time passes. ) Note that the apparent M:N relationship between MAINTENANCE and PART has been resolved through the use of the composite entity named MAINTAIN_LINE.
The MAINTAIN_LINE entity ensures that the M:N relationship between MAINTENANCE and PART has been broken up to produce the two I:M relationships shown in business ales 9 and 10. 9. Each maintenance procedure generates one or more maintenance lines. 10. Each part may appear in many maintenance lines. (Review the comment in business rule 8. ) Question 3 – Based on the relationship described in following two E-R diagram for two cities A and B, answer the following questions as City A , City B, or cannot tell as options a) Which city maintains data about only those volunteers who currently assist agencies? ) In which city would it be possible for a volunteer to assist more than one agency? C) In which city would it be possible for a volunteer to change which agency r agencies he or she assists? (4+3+3= 10 marks) Answer 3 – a) Cannot tell – The mandatory 1:1 cardinality at the agency suggests that every City B collects data on only those volunteers, who are associated with one and only one agency. However, it will exclude the volunteers who are associated with more than one agency. Therefore, city B does not maintain data of all the volunteers who currently assist agencies. If a student write City B and write about mandatory-I cardinality, s/he can be given 3 marks) b) City A – The ERRED for City A shows that a volunteer may assist one, none, or several agencies. ) Cannot tell – The native notation used in Reds does not show whether membership in a relationship can change (I. E. , whether a volunteer can change agencies or whether an agency can change its volunteers). One way of maintaining this data is to create the history of volunteer agency relation. For this one has to create an associative entity “Relationship History’ between Agency and Volunteer.
Question 4 – Prepare ERRED for a firm that lists property for sale. First prepare the definition of each entity type, attributes, and relationship on your diagram. Use following business rules for this organization 1) The firm has a number f sales offices in several states. Attributes of sales office include Office Number (Identifier) and location. 2) Each sales office is assigned to one or more employees but an employee must be assigned to only one sales office. Attributes of employee include Employee ID and Employee Name. 3) One employee is assigned to manage each sales office.
The employee can only manage the sales office that s/he is assigned to. 4) The firm lists property for sale. Attributes of property include Property ID and Location (Address, City, State, and Zip-code). 5) Each unit of property must be listed with one and only one sales office. A sales office may have many or none property listed. 6) Each unit of property has one or more owners. Attributes of owners are Owner ID and Owner Name. An owner may own one or more units. Attribute of the relationship between property and owner is Percent Owned.
Now develop entity clusters for the ERE diagram that you prepared for the real estate agency. Then redraw the ERE diagram using the entity clusters with appropriate relationships. (20 marks) Answer 4 – Entities: Employee: An employee of the firm. An employee works for one sales office and may manage one sales office. It is not explicitly indicated that the employee can only manage the office that he/she works for. This would require a business rule. Sales Office: The office where real estate is sold. Property: Buildings for sale, such as houses, condos and apartment buildings.
Owner: The individual who owns one or more properties. Attributes: Employee ID: A unique identifier for an employee. This attribute must be unique. Employee Name: The name of the employee. Attributes on Sales Office: Office Number: A unique identifier for the office. Office Location: The physical location of the sales office. This data may be made up of he city and state. Attributes on Property: Property ID: The unique identifier for the property. Property Location: A composite attribute that consists of the street address, city, state, and Zip Code.
Attributes on Owner: Owner ID: The unique identifier for the owner. Owner Name: The name of the owner. Relationship: Is Assigned: An employee is assigned to one sales office. A sales office may have many employees assigned but must have at least one employee. Manages: An employee may manage one sales office or no sales office. Each sales office is managed by one employee. A business rule is needed here in order to indicate that an employee can only manage the sales office in which he or she works. Lists: Each property is listed by only one sales office.
Each sales office can list one, none, or many properties. Owns: Each property has one or more owners. Each owner can own one or more properties. Percent Owned is an attribute on Owns; it tracks the percent of property that a particular owner owns. One way of implementing the business rule that the employee can manage only the sales office that s/he is assigned to, is to replace the relationship manage with an attribute manage in employee entity. This attribute has a binary yes Ion value to indicate whether the employee manages the assigned sales office or not.
The student may show the percent owned attribute of the relationship owns as an associative entity. That is correct. The Sales Unit cluster can be used by people only interested in how the business is managed, without concern for the properties listed. The Property Listing cluster can be used by people who are interested in property Question 5 – An entity person has three subtypes – Camper, Biker, and Runner. Draw separate ERE diagrams for each of the following situations 1) A person must be exactly one of these subtypes at a given time. 2) A person may or may not be one of these subtypes.
However, a person who is one of these subtypes cannot be at the same time be one of the other subtypes. 3) A person may or may not be one of these subtypes. A person could be any two or even three of these subtypes at the same time. 4) Ata given time a person must be at least one of these subtypes. Answer 5 – 2) 3) 4) Question 6 – Develop the ERE for the following situation A person may be employed by one or more organizations and each organization may be the employer of one or more persons. For persons and organizations we want to know their ID, name, address, and phone number.
For persons, we want to know their birth date, and for organization, we want to know their budget number. For each employment (of a person with the organization), we want to know the employment date, termination date and bonus. Employment of a person by an organization may result in the person holding many positions over time. For each position, we want to know its title, and each time someone holds that position, we need to know the start date, termination date, and salary. An organization is responsible for each position.
It is possible for a person to be employed by one organization and hold a position for which other organization is responsible. [Hint: Entities person and organization have a set of common attributes that may be used to create their supersede] (20 marks) should be given full marks (as the total employment history of a person in an organization can be obtained from her position history). Deduct 5 marks if a student does not show the direct relationship between organization and position. Note that one can infer the positions offered by the organization based on the position history f persons working in that organization.
However, to account for the possibility of an organization offering positions in other organizations, we have to show the direct relationship between entity types organization and position. Question 7- Draw the ERE diagram for the following description of a law firm. Each case handled by the firm has a unique case number; a date opened, date closed, and judgment description are also kept on each case. A case is brought by one or more plaintiffs, and the same plaintiff may be involved in many cases. A plaintiff has a quested Judgment characteristics.
A case is against one or more defendant, and the same defendant may be involved in many cases. A plaintiff or defendant may be a person or an organization. Over time, the same person or organization may be a defendant or plaintiff in cases. In either situation such legal entity are identified by the entity number, and other attributes are name and net worth. (20 marks) Answer 7 – Some students may also create the following ERRED. This is also K. Notes: 1) Person or Org attribute denotes Person or Organization type of Legal Entity.