Logical model: mapping

Honest Bob’s Discount Warehouse of 23 Dubious Lane Cheapside NT 8023 needs a computerised invoicing system. Because Honest Bob is a cheapskate they are still using a paper-based system.

The organisation has a number of customers, which are all businesses (Honest Bob’s does not deal with the general public, which avoids all the problems with GST). Information kept on customers includes their ABN (CustABN), business name and address (which includes the address, suburb, state and postcode). Because Honest Bob’s is keen on keeping their customers and having them return time after time, the prices there are suspiciously cheap.

When a customer purchases products they are given an invoice that lists the items purchased. Generally customers purchase a number of different items of varying quantities each time. If a customer decides that they haven’t got enough of a particular item, instead of increasing the quantity for that item, Honest Bob’s staff just add it again to the bottom of the invoice and give the new, updated copy to the customer.

Details on the invoice include the customer details, an invoice number, a date, an item number, its description, price, quantity ordered, subtotals for each item line and an invoice total.

CustAddress in the entity CUSTOMER is the only composite attribute in the E-R diagram. There are no multi-value attributes.

We don’t map derived attributes as they are never stored in a database but calculated at run time. However, we need to know about them, because we incorporate these attributes in the physical implementation of the system (they go into queries, views, etc.).

We have a binary one-to-many relationship Receives involving CUSTOMER and INVOICE

We place the Primary Key from the entity on the one side of the relationship (CustABN from CUSTOMER) into the relation on the many side (INVOICE) as a Foreign Key.

If the designer chose to ignore the many-to-many problem whilst developing the E-R diagram, this is where it is resolved. Business rule 4 “An item can appear on an invoice more than once.� Now comes into effect.

We create a new relation INVOICEITEM (a combination of the names from the two relations involved in the relationship). Because of the business rule we create an artificial Primary Key and place the attribute Quantity in the relation. We the take the Primary Keys from the two relations involved and place them into the new relation as Foreign Keys.

We do not have any.

If on the other hand the designer decided to create an associative entity the relational model will still look the same.

We create a new relation INVOICEITEM (a combination of the names from the two relations involved in the relationship). The Primary Key is the identifier assigned on the E-R diagram. Quantity is added and the Primary Keys from the involved relations included as Foreign Keys.

A company has a number of employees. The attributes of employee include employee_id (identifier), name, address, and birthdate. The company also has several projects. attributes of project include project_id (identifier), project_name, and start_date. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employee assigned and may have any number of employees assigned. An employee’s billing rate may vary by project, and the company wishes to record the applicable billing rate (billing_rate) for each employee when assigned to a particular project.

• The firm has a number of sales offices in several states. Attributes of sales office include Office_Number (identifier) and Location.
• Each sales office is assigned one or more employees. Attributes of employee include Employee_ID (identifier) and Employee_Name. An employee must be assigned to only one sales office.
• For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which he or she is assigned.
• The firm lists property for sale. Attributes of property include Property_ID (identifier) and Location. Components of Location include Address, City, State and Zip_Code.
• Each unit of property must be listed with one (and only one) of the sales offices. A sales office may have any number of properties listed or may have no properties listed.
• Each unit of property has one or more owners. Attributes of owners are Owner_ID (identifier) and Owner_Name. An owner may own one or more units of property. Owners own a percentage of a property.

• An attorney is retained by one or more clients for each case.
• Attributes of attorney are: attorney_id, name, address, city, state, zip_code, specialty (may be more than one), bar (may be more than one).
• A client may have more than one attorney for each case.
• Attributes of client are client_id, name, address, city, state, zip_code, telephone, date of birth.
• A client may have more than one case.
• Attributes of case are case_id, case_description, case_type.
• An attorney may have more than one case.
• Each case is assigned to one and only one court.
• Attributes of court are court_id, court_name, city, state, zip_code.
• Each court has one or more judges assigned to it.
• Attributes of judge are judge_id, name, years in practice,
• Each judge is assigned exactly to one court.