Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please answer the following question that related to distributed database management systems Q1. Build a Global Conceptual Schema (GCS) from the following given local conceptual
Please answer the following question that related to distributed database management systems
Q1. Build a Global Conceptual Schema (GCS) from the following given local conceptual schemas (problem 7.4)
we need to convert ER model to relation schema, and please explain each step in details
Problem 7.4 (**) Consider the two databases given in Figs. 7.17 and 7.18 and described below. Design a global conceptual schema as a union of the two databases by first translating them into the E-R model. 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: 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