Database Entities and Entity Relationship Explain the difference between a weak and a strong entity set. Answer: A Answer: A strong entity set has a primary key. All tuples(rows) in the set are distinguishable by that key. A weak entity set has no primary key unless attributes of the strong entity set on which it depends are included. Tuples in a weak entity set are partitioned according according to their t heir relationship relationship with tuples in a strong entity. We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets? Answer: We Answer: We have weak entities for several reasons: We want to avoid the data duplication and consequent possible inconsistencies caused by duplicating duplicating the key of the strong entity. Weak entities reflect the logical structure of an entity being dependent on another entity. Weak entities can be deleted automatically when their strong entity is deleted. Weak entities can be stored physically with their strong entities. •
• • •
When designing an E-R diagram for a particular enterprise, enterprise, you have several alternatives from which to choose. What criteria should you consider in making the appropriate choice? Answer: The Answer: The criteria to use are intuitive design accurate e!pression of the real"world concept and efficiency. A model which clearly outlines the ob#ects and relationships in an intuitive manner is better than one which does not because it is easier to use and easier to change. $eciding between between an attribute and an entity set to represent an ob#ect and deciding between an entity set and relationship relationship set influence the accuracy with which the real"world concept is e!pressed. %f the right design choice is not made inconsistency and&or loss of information will result. A model which can be implemented implemented in an efficient manner is to be preferred for obvious reasons.
Entity Relationship Diagram Exercises 'or each of the following narratives draw an $ diagram which indicates the entities and their attributes (if noted). %dentify and label significant relationships relationships between pairs of entities.
Exercise *reate a $ for each of the following descriptions. a) ach of the +winby +winby *orporation,s *orporation,s divisions divisions is is composed of many departments. ach ach of the departments has many employees assigned to it but each employee works for only one department. ach department is managed by one employee employee and each of these managers can manage only one department at a time. b) $uring $uring some period period of time time a customer customer can rent many many videotape videotapes s from the -igid -igid store. ach of the t he -igid,s videotapes can be rented to many customers during that period of time. c) An airline airline can be assigned assigned to fly many flights flights but each each flight is flown flown by only only one airline. d) The /wikTite /wikTite *orporation *orporation operates operates many factories. factories. ach factory is located in a region. ach region can be 0home0 to many of /wikTite,s factories. ach factory employs many employees but each of these employees employees is employed by only one factory. e) An employee employee may have have earned earned many many degrees degrees and each each degree degree may have been been earned by many employees.
Exercise ! 1ick and +hovel *onstruction *ompany is a multi"state building contractor speciali2ing in medium"priced town homes. Assume that 1ick and +hovel,s main entities are its customers employees pro#ects and equipment. A customer can hire the company for more than one pro#ect and employees sometimes work on more than one pro#ect at a time. quipment however is assigned to only one pro#ect at a time. $raw an $ showing those entities.
Exercise " 'ast'light Airlines is a small air carrier operating in three north"eastern states. 'ast'light is in the process of computerising its passenger reservation system. The f ollowing data items have been identified: reservation code, flight number, flight date, origin, destination, departure time, arrival time, passenger name, seat number, reservation agent number, and reservation agent name. 'or e!ample flight number 343 which is scheduled every Tuesday and Thursday leaves Augusta 5aine at 6:73am and arrives in 8ashua 8ew 9ampshire at 4:;am.
Exercise # 1repare an ntity elationship $iagram for 1atty,s $aycare *entre. 'or each entity indicate the primary key by underlining attribute(s) making up primary key and also specify additional significant attribute. %dentify and label significant relationships between pairs of entities (either : : 5 or 5:8). 1atty,s 1layschool is a child daycare centre. A parent registers their child or children at the school using a registration form. A parent can submit more than one registration form. ach room in the daycare is assigned an age group. 'or e!ample an infant is under yearof age and toddlers are from to 3 years of age. A child is assigned to a room based on their age and availability of space. A room may be assigned one or more employees. An employee can only be assigned to one room. The minimum number of employees required for a room is determined by the number of children assigned to the room and the child:staff ratio identified by the government. 'or e!ample one employee can care for = infants or > toddlers.
$ore exercises% . A large bank operates several divisions. %nformation Technology (%T) is operated as one of these divisions. Within the %T division are many departments that are managed by one manager and all %T employees belong to one of these departments. The %T division assigns it?s employees to one or more on going pro#ects in the bank. A pro#ect may be planned but not have any employees assigned to it for several months. ach pro#ect will have a single employee assigned who acts as a pro#ect leader. 7. A hardware store sells several home workshop products to the public (such as power saws and sanders). ach product has several different manufacturers who manufacture it and prices are different for products made by different manufacurers. ach t ime one or more products are sold to a customer an invoice is created which lists the date items purchased and their prices and then the total purchase and ta! amounts. 3. The 5inistry of Transportation (5@T) supplies department keeps track of all the items (furniture and equipment such as a chair or printer) in the 5inistry offices. There are several 5@T buildings and each one is given a different name to identify it. ach item is assigned a unique %$ when it is purchased. This %$ is used to keep track of the item
which is assigned to a room within a building. ach room within a building is assigned to a department and each department has a single employee as it?s manager. . A cooking club organi2es several dinners for it?s members. The purpose of the club is to allow several members to get together and prepare a dinner for the other members. The club president maintains a database that plans each meal and tracks which members attends each dinner and also keeps track of which members creates each dinner. ach dinner serves many members and any member is allowed to attend. ach dinner has an invitation. This invitation is mailed to each member. The invitation includes the date of the dinner and location. ach dinner is based on a single entrBe and a single dessert. This entrBe and dessert can be used again for other dinners. =. A-* *onsulting is a small"si2ed consulting firm in the %T industry. A-*,s business is managing several +ystems $evelopment pro#ects by assigning staff consultants to these pro#ects as their skills are needed. ach employee is designated to have one primary skill but there may be other employees with the same primary skill. A consultant may work on one or more pro#ects or may not yet be assigned to a pro#ect. The company charges for each pro#ect by billing each consultant?s hours worked by the billing rate. T he hourly billing rate is dependant on the employee,s primary #ob skill. C. A company purchases products and sells them to it?s customers. ach time a sale occurs an invoice is created listing the customer name and a list of purchase product descriptions the supplier name for the products and the price of each product. The product number identifies each product and will appear again if another customer purchases the same product. ach supplier can supply many products which we can sell but each product has only one supplier. ;. . A company operates a warehouse parts supply business. The company has several warehouses located in Toronto which each store several hundreds of automotive parts. We need to keep a record of how many parts are 0on hand0 " meaning inventory levels that tell us how many we have for each part. To help us organi2e our parts each part is assigned a specific classification. There are classifications that we use to organi2e hundreds of parts. 6. To keep track of office furniture computers printers and so on the '@D8$%T company requires the creation of a simple database. ach piece of office furniture computer or printer is given an identification number. ach item is then placed in a room of one of three buildings. The building manager is responsible for the items in their building.
Reference http:&&cs.senecac.on.ca&Edbs74&pages&$.htm
$ore Exercises &cont' . *onstruct an " diagram for a car"insurance company whose customers own one or more cars each. ach car has associated with it 2ero to any number of recorded accidents. 7. *onstruct an " diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and e!aminations conducted. 3. A university registrars office maintains data about the following entities: (a) courses including number title credits syllabus and prerequisitesF (b) course offerings including course number year semester section number instructor(s) timings and classroomF (c) students including student"id name and programF and (d) instructors including identiGcation number name department and title. 'urther the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. *onstruct an " diagram for the registrar,s office. $ocument all assumptions that you make about the mapping constraints.
Recommended answers% 1. Car insurance tables: person (driver"id name address) car (license yearmodel) accident (report"number date location) participated(driver"id license report"number damage"amount) 2. ospital tables: patients (patient"id name insurance date"admitted date"checked"out) doctors (doctor"id name speciali2ation) test (testid testname date time result) doctor"patient (patient"id doctor"id) test"log (testid patient"id) performed"by (testid doctor"id) !. "ni#ersity registrar.s tables: student (student"id name program) course (courseno title syllabus credits) course"offering (courseno secno year semester time room) instructor (instructor"id name dept title) enrols (student"id courseno secno semester year grade) teaches (courseno secno semester year instructor"id) requires (maincourse prerequisite) . $esign an " diagram for keeping track of the e!ploits of your favourite sports team.
Reference http:&&www.cs.ucla.edu&classes&fall4&cs3&solutions&chap7.pdf
