1. Engineering
  2. Computer Science
  3. create a set of business rules and build a logical...

Question: create a set of business rules and build a logical...

Question details

create a set of business rules and build a logical data model ERD for each exercises.

Below is an example of how each ERD diagram should be formatted.

CONSUMER pk cons id PURCHASE cons fname pk, fki cons id k, fk2 novel id cons Iname purch date purch price cons city cons postal s email cons phone purchased by AUTHOR NOVEL pk author id author fname author Iname pk novel id novel_title author addresswres fk author id author city author state author postal author email novel category novel isbn novel cost author.phone

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 profits 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 different 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 offers. 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 effect, 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 exampl 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 le, Iguana Bay Spas is a manufacturer. They produce two brands

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 staff, including a manager who is is responsible for the day-to-day running of the branch. The database must hold the name of each staff member as well as his or her name, position, and salary. Each member of the staff is given a staff 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 identifies each DVD. In most cases, a given branch has several copies of each DVD. The individual copies are identified 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 specific copy of a DVD is available for rent. Before a customer can rent a DVD, he or she must first register as a member of their local branch. The database must store the the first 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 specific clinic. An appointment can be scheduled for a specific 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 a patient is insured, a claim can be submitted to the insurance company, who can send a payment in part or in full. ppointment. A patient can pay in installments. If a

Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution