Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please answer the following question that related to distributed database management systems Q2- Build a Global Conceptual Schema (GCS) from the following given local conceptual
Please answer the following question that related to distributed database management systems
Q2- Build a Global Conceptual Schema (GCS) from the following given local conceptual schemas (problem 7.4) Problem 7.4 (**) Consider the two databases given in Figs. 7.17 and 7.18 and described below. Design a global concentual schema as a union of the two databases by first translating them into Relational database Figure 7.17 describes a relational race database used by organizers of road races and Fig. 7.18 describes an entity-relationship database used by a shoe manufacturer. The semantics of each of these database schemas is discussed below. Figure 7.17 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. 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. 7.17 Road race database 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). Figure 7.18 illustrates an entity-relationship schema used by the sponsor's database system with the following semantics: 1ig. /.18 Sponsor datanase 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_SALARYStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started