Instruction for database A-E

Creating a Logical E-R Diagram To build an entity, use a rectangular box with two columns. Put the name of the entity in all-caps in the header. Below that, list each attribute, placing the primary key attribute(s) rst. If an attribute is a primary key or part of a composite primary key, place a pk in the column on the left. If the attribute is a foreign key, place a fk in the column on the left. If more than one foreign key exists, put a number after each designator, i.e. fk1, fk2, etc. If an attribute is required, place it in bold. Use a common prex when naming the attributes. For foreign key attributes, use the prex of the associated table. Use a verb phrase to express a relationship only if it is not intuitively obvious. We do not use the diamond notation for relationships with the Crows Foot notation. At the ends of the relationship lines, use the Crows Foot notation to indicate cardinality and participation constraints. If a cardinality level has a specic limit, show it using the notation (x, y) above the symbols, where x is the participation value and y is cardinality value. If you need to indicate additional constraints or have any notes, place them in a rectangular box somewhere on the diagram, preferably in a different shade. The example in the gure above maps out the business rules shown below. A good rule of thumb is that an entity has as many foreign keys as it does Crows Feet touching it. If an entity has no Crows Feet touching it, it should have no foreign key elds.

Exercise A: A local publisher publishes books written by its authors. Before a book is written, the author sits down with the publisher and a contract is written up. Part of the contract includes the percentage of the prots that the author will receive, i.e. royalties. Most of the time, a single author writes a particular book, but occasionally multiple authors team up to write a book. In that case, a contract is written up separately for each author so that the various authors involved can be given dierent royalty percentages if need be. The database needs to keep track of the authors that write a given book, along with the books title, copyright date, ISBN, and page count. The contract indicates the author, the book, and the royalty percentage the author receives after its release.

Exercise B: An insurance company has a special department for each type of insurance it oers. Each department has a group of agents who must be licensed with a valid license number on record. If a customer wants to purchase insurance, an agent is assigned to that customer who writes up a policy. The agent must obtain the name, address, date of birth, and social security number of the customer. The agent also needs to know the name, birthdate, and social security number of each dependent. If the customer is married, the name, birthdate, and social security number of the spouse must be recorded. The policy includes the date it was created, the date it goes into eect, the expiration date, type of insurance, annual premium, the customer it belongs to, and the agent who created it.

Exercise C: Hot Water (HW) is a small start-up company that sells spas it orders from a variety of manufacturers. Every manufacturer is identified by a manufacturer code and has a name, address, area code, phone number, and account number. Each manufacturer carries at least one brand. A brand has a name and a level (premium, mid-level, or entry-level), both of which must be stored. For example, Iguana Bay Spas is a manufacturer. They produce two brands Big Blue Iguana spas, a premium-level brand; and Lazy Lizard spas, an entry-level brand. Within a given brand, one or more models exist. When a customer makes a purchase, they are actually buying a specific model. Each model has a model number, the number of jets, number of motors, horsepower per motor, dry weight, water capacity, seating capacity, suggested retail price, and actual retail price. When a customer purchases a spa, the date of sale, model purchased, and the total cost of the spa must be recorded in the database.

Exercise D A DVD rental company has several branches throughout the United States. The database must hold the address of each branch, made up of street, city, state, zip code, and the telephone number. Each branch is given a branch number, which is unique throughout the company. Each branch is also allocated sta, including a manager who is is responsible for the day-to-day running of the branch. The database must hold the name of each sta member as well as his or her name, position, and salary. Each member of the sta is given a sta number, which is unique throughout the company. Each branch has a stock of DVDs. Each DVD has a catalog number, DVD number, title, category, daily rental amount, cost, status, the names of the main actors, and the director. The catalog number uniquely identies each DVD. In most cases, a given branch has several copies of each DVD. The individual copies are identied using the DVD number. A DVD is given a category such as Action, Adult, Children, Drama, Horror, or Sci-Fi. The status indicates whether a specic copy of a DVD is available for rent. Before a customer can rent a DVD, he or she must rst register as a member of their local branch. The database must store the the rst name, last name, and address of the member, as well as the date that the member registered at the branch. Upon registration, each member is given a member number, which is unique throughout all branches of the company. Once registered, a member is free to rent DVDs, up to a maximum of ten at any one time. For each rental, the database must hold the rental number, member number, the DVDs catalog number, the DVD number of the copy being rented, the daily rental amount, and the dates the DVD is rented out and returned.

Exercise E: A local medical clinic needs a database to manage its daily operations. A patient can schedule appointments with any doctor they want at whichever clinic they prefer, depending on doctor availability. Each doctor practices at a specic clinic. An appointment can be scheduled for a specic doctor at a particular date and time. Sometimes a patient comes in with an emergency, in which case any doctor who is available meets with the patient. If the patient comes in for the appointment, the doctor meets with the patient and creates a treatment plan consisting of a diagnosis and one or more treatments. A treatment may require one or more prescriptions. A prescription is for a single medication and includes the dosage and instructions for the patient. In any case, the patient is billed for each appointment. A patient can pay in installments. If a patient is insured, a claim can be submitted to the insurance company, who can send a payment in part or in full.