Question

A trucking company has many different trucks and many different drivers. For a given delivery trip, trucks are assigned to drivers. As time passes, a driver will make trips with many different trucks and any given truck will be driven by many different drivers. The following tables contain the data for the firm's Driver population and for the firm's population of Trucks.

DRIVER

EMP_NUM	EMP_TITLE	EMP_LNAME	EMP_FNAME	EMP_INITIAL	EMP_DOB	EMP_HIRE_DATE
100	Mr.	Kolmycz	George	D	6/15/42	3/15/85
101	Ms.	Lewis	Rhonda	G	3/19/65	4/25/86
102	Mr.	Vandam	Rhett		11/14/58	12/20/90
103	Ms.	Jones	Anne	M	10/16/74	8/28/94
104	Mr.	Lange	John	P	11/8/71	10/20/94
105	Mr.	Williams	Robert	D	3/14/75	11/8/94
106	Mrs.	Duzak	Jeanine	K	2/12/68	1/5/89
107	Mr.	Diante	Jorge	D	8/21/74	7/2/94
108	Mr.	Wiesenbach	Paul	R	2/14/66	11/18/92
109	Ms.	Travis	Elizabeth	K	6/18/61	4/14/89
110	Mrs.	Genkazi	Leighla	W	5/19/70	12/1/90

TRUCK

TRUCK_NUM	BASE_CODE	TYPE_CODE	TRUCK_MILES	TRUCK_BUY_DATE	TRUCK_SERIAL_NUM
1001	501	1	32123.5	11/8/94	AA-322-12212-W11
1002	502	1	76984.3	3/23/92	AC-342-22134-Q23
1003	501	2	12346.6	12/27/95	AC-445-78656-Z99
1004		1	894.3	2/21/96	WQ-112-23144-T34
1005	503	2	45673.1	4/15/94	FR-998-32245-W12
1006	501	2	93245.7	8/30/93	AD-456-00845-R45
1007	502	3	32012.3	12/1/94	AA-341-96573-Z84
1008	502	3	44213.6	9/21/94	DR-559-22189-D33
1009	503	2	10932.9	1/16/96	DE-887-98456-E94

1. Draw the simplified conceptual ERD for this model (only two entities above and their relationship are needed, no need to list attributes).
2. Convert into an E-R model that displays only 1:1 or 1:M relationships and draw the Crow’s Foot ERD (including all attributes, keys, etc.).

Answer 1

Simplified Conceptual ERD diagram for given model is

For instance, in the Driver-Truck example as given, there is a M:M relationship between Driver and Truck. A driver can own many different trucks, and a truck can be driven by many different drivers. In order to convert the relationship into two 1:M we can use intersection entity between driver and truck entities.Intersection is resolution for M:M relationship.

Crow's foot daigram represent entities as boxes, and relationships as lines between the boxes. Different shapes at the ends of these lines represent the relative cardinality of the relationship.Inorder to draw Crow's Foot ERD we use cardinality and modality for association between two entities.

• Cardinality refers to the maximum number of times an instance in one entity can be associated with instances in the related entity. It can be 1 or Many
• Modality refers to the minimum number of times an instance in one entity can be associated with an instance in the related entity. It can be 1 or 0
  • zero or more
  • 1 or more
  • 1 and only 1 (exactly 1)
  • zero or 1

Crow's Foot ERD

• 1:1 Relationship
  • One driver can own a one truck at a time on TRUCK_BUY_DATE so I stored TRUCK_NUM,TRUCK_BUY_DATE,TRUCK_SERIAL_NUM in entity (we can include all attributes).It also has an identifier, which uniquely identifies one instance of an entity. The attribute which acts as the identifier is marked with an asterisk.
    • 
• 1:M Relationship
  • One driver can own different trucks at any time,similarly one truck can be driven by different drivers at any time.
    •