1. Use the contents of Figure 2.3 to work on the following problem.

The text in Figure 2.3 is reproduced below for your convenience:

FIGURE 2.3 Linking Relational Tables

Write the business rule(s) that governs the relationship between AGENT and CUSTOMER.

 

1. Given the business rule(s) you wrote in Problem 1, create the basic Crow’s Foot ERD.

 *****************************************************************

• 2. Identify and describe the components of the table shown in Figure Q3.4, using correct

terminology. Use your knowledge of naming conventions to identify the table’s probable foreign key(s).

 

EMP_NUM	EMP_LNAME	EMP_INITIAL	EMP_FNAME	DEPT_CODE	JOB_CODE
11234	Friedman	K	Robert	MKTG	12
11238	Olanski	D	Delbert	MKTG	12
11241	Fontein		Juliette	INFS	5
11242	Cruazona	J	Maria	ENG	9
11245	Smithson	B	Bernard	INFS	6
11248	Washington	G	Oleta	ENGR	8
11256	McBride		Randall	ENGR	8
11257	Kachinn	D	Melanie	MKTG	14
11258	Smith	W	William	MKTG	14
11260	Ratula	A	Katrina	INFS	5

                                FIGURE Q3.4  EMPLOYEE Table

 

 

Figure Q3.4's database table contains: LIST HERE

• 3. Suppose you are using the database composed of the two tables shown in Figure Q3.5.
•   Table Name: Director                                                                                                                                                          Table Name: Play

PLAY_CODE	PLAY_NAME	DIR_NUM
1001	Cat On a Cold, Bare Roof	102
1002	Hold the Mayo, Pass the Bread	101
1003	I Never Promised You Coffee	102
1004	Silly Putty Goes To Washington	100
1005	See No Sound, Hear No Sight	101
1006	Starstruck in Biloxi	102
1007	Stranger In Parrot Ice	101

 

DIR_NUM	DIR_LNAME	DIR_DOB
100	Broadway	12-Jan-65
101	Hollywoody	18-Nov-53
102	Goofy	21-Jun-62

 

 

 

FIGURE Q3.5                                                                                                                                                               The Ch03_Theater Database

• Identify the primary keys.
• Identify the foreign keys.

 

• Create the ERM.
•  
• Suppose you wanted quick lookup capability to get a listing of all plays directed by a given director. Which table would be the basis for the INDEX table, and what would be the index key?
•  

 

 

Transcribed Image Text:

Table name: AGENT (first six attributes) AGENT CODE AGENT LNAME AGENT FNAME AGENT_NTIAL AGENT AREACODE AGENT_PHONE Database name: Ch02_Insureco 501 Alby Alex 713 228-1249 502 Hahn Leah F 615 882-1244 503 Okon John 615 123-5589 Link through AGENT_CODE Table name: CUSTOMER CUS CODE CUS LNAME CUS FNAME CUS NTIAL CUS AREACODE CUS PHONE CUS_NSURE TYPE CUS NSURE AMT CUS RENEW DATE AGENT_CODE 10010 Ramas 615 844-2573 T1 05-Apr-2008 16-Jun-2008 Alfred A 100.00 502 10011 Dunne 10012 Smith Leona K 713 894-1238 T1 250.00 501 Kathy 615 894-2285 S2 150.00 29-Jan-2009 502 10013 Olowski Paul F 615 894-2180 S1 300.00 14-Oct-2008 502 10014 Orlando 615 Myron Amy 222-1672 T1 100.00 28-Dec-2008 501 10015 O'Brian 713 442-3381 T2 850.00 22-Sep-2008 503 10016 Brown James 615 297-1228 S1 120.00 25-Mar-2009 502 10017 Wliams George 615 290-2556 S1 250.00 17-Jul-2008 503 10018 Farriss Anne 713 382-7185 T2 100.00 03-Dec-2008 501 10019 Smith Olette 615 297-3809 S2 500.00 14-Mar-2009 503 K Table name: AGENT (first six attributes) AGENT CODE AGENT LNAME AGENT FNAME AGENT_NTIAL AGENT AREACODE AGENT_PHONE Database name: Ch02_Insureco 501 Alby Alex 713 228-1249 502 Hahn Leah F 615 882-1244 503 Okon John 615 123-5589 Link through AGENT_CODE Table name: CUSTOMER CUS CODE CUS LNAME CUS FNAME CUS NTIAL CUS AREACODE CUS PHONE CUS_NSURE TYPE CUS NSURE AMT CUS RENEW DATE AGENT_CODE 10010 Ramas 615 844-2573 T1 05-Apr-2008 16-Jun-2008 Alfred A 100.00 502 10011 Dunne 10012 Smith Leona K 713 894-1238 T1 250.00 501 Kathy 615 894-2285 S2 150.00 29-Jan-2009 502 10013 Olowski Paul F 615 894-2180 S1 300.00 14-Oct-2008 502 10014 Orlando 615 Myron Amy 222-1672 T1 100.00 28-Dec-2008 501 10015 O'Brian 713 442-3381 T2 850.00 22-Sep-2008 503 10016 Brown James 615 297-1228 S1 120.00 25-Mar-2009 502 10017 Wliams George 615 290-2556 S1 250.00 17-Jul-2008 503 10018 Farriss Anne 713 382-7185 T2 100.00 03-Dec-2008 501 10019 Smith Olette 615 297-3809 S2 500.00 14-Mar-2009 503 K

Answer rating: 100% (QA)

Question 317 EmpNum is primary key Employee is belongs to one department an... View the full answer