Reference no: EM133230887

Consider the two databases given in Figures 2.1 and 2.2 and described below. Design a global conceptual schema as a union of the two databases by first translating them into the E-R model.

DIRECTOR(NAME, PHONE NO, ADDRESS)

LICENSES(LIC NO, CITY, DATE, ISSUES, COST, DEPT, CONTACT)

RACER(NAME, ADDRESS, MEM NUM)

SPONSOR(SP NAME, CONTACT)

RACE(R NO, LIC NO, DIR, MAL WIN, FRM WIN, SP NAME)

Fig. 2.1 Road Race Database

Figure 2.1 describes a relational race database used by organizers of road races and Figure 2.2 describes an entity-relationship database used by a shoe manufacturer. The semantics of each of these database schemas is discussed below. Figure 2.1 describes a relational road race database with the following semantics:

DIRECTOR is a relation that defines race directors who organize races; we assume that each race director has a unique name (to be used as the key), a phone number, and an address.

LICENSES is required because all races require a governmental license, which is issued by a CONTACT in a department who is the ISSUER, possibly contained within another government department DEPT; each license has a unique LIC NO (the key), which is issued for use in a specific CITY on a specific DATE with a certain COST.

RACER is a relation that describes people who participate in a race. Each person is identified by NAME, which is not sufficient to identify them uniquely, so a compound key formed with the ADDRESS is required. Finally, each racer may have a MEM NUM to identify him or her as a member of the racing fraternity, but not all competitors have membership numbers.

SPONSOR indicates which sponsor is funding a given race. Typically, one sponsor funds a number of races through a specific person (CONTACT), and a number of races may have different sponsors.

RACE uniquely identifies a single race which has a license number (LIC NO) and race number (R NO) (to be used as a key, since a race may be planned without acquiring a license yet); each race has a winner in the male and female groups (MAL WIN and FEM WIN) and a race director (DIR).

Fig. 2.2 Sponsor Database

Figure 2.2 illustrates an entity-relationship schema used by the sponsor's database system with the following semantics:

SHOES are produced by sponsors of a certain MODEL and SIZE, which forms the key to the entity.

MANUFACTURER is identified uniquely by NAME and resides at a certain ADDRESS.

DISTRIBUTOR is a person that has a NAME and ADDRESS (which are necessary to form the key) and a SIN number for tax purposes.

SALESPERSON is a person (entity) who has a NAME, earns a COMMISSION, and is uniquely identified by his or her SIN number (the key). Makes is a relationship that has a certain fixed production cost (PROD COST). It indicates that a number of different shoes are made by a manufacturer, and that different manufacturers produce the same shoe. Sells is a relationship that indicates the wholesale COST to a distributor of shoes. It indicates that each distributor sells more than one type of shoe, and that each type of shoe is sold by more than one distributor.

Contract is a relationship whereby a distributor purchases, for a COST, exclusive rights to represent a manufacturer. Note that this does not preclude the distributor from selling different manufacturers' shoes. Employs indicates that each distributor hires a number of salespeople to sell the shoes; each earns a BASE SALARY.