Overview The purpose of this assignment and assignment 2 is for you to practice the four steps of the database design process. In this assignment, you will design an entity-relationship (ER) diagram using the content defined below. You must use either Lucidchart, Erwin, ERDPlus, or another software tool to create both diagrams (it is highly recommended to use Lucidchart for the diagramming, but any other diagramming software is fine). You do not need to purchase any of these softwares - the free versions will be sufficient for your purposes. Hand-written submissions will not be accepted and will receive a grade of zero; a diagramming software must be used. To begin, suppose that you are tasked with designing a (simplified) database for a national shipping and logistics company. This company operates throughout the continental United States. A customer will buy a shipment from a sales_person, where a shipment is defined as one entire tractor trailer load full of goods. A shipment requires the use of a tractor and either one or two trailers hitched in tandem, which are owned by someone called a contractor. Shipments are transported by drivers, and each shipment can have one or two drivers. MAKE SURE YOU READ ALL PARTS OF STEP 1 BEFORE CREATING YOUR DIAGRAM! Step 1: Specification of Data Requirements The first step in the database design process is the requirements analysis phase, which results in a specification of user/data requirements. For this assignment, suppose you have met with the owner of the logistics company and create / obtained the following list of user / data requirements. READ ALL REQUIREMENTS FIRST, THEN LOOK AT THE CREATING ENTITY SETS SECTION OF STEP 2 FOR HOW EACH REQUIREMENT SHOULD BE REPRESENTED

Part 1: Entity Set Descriptions You must create the following entity sets based on the descriptions given below using the exact names that are shown in boldface font. Do not add additional attributes, tables, or entity sets. Adding information will result in loss of points. shipments: A shipment is defined as the transportation of an entire tractor trailer load of goods from an origin to a destination; a shipment does not refer to individual boxes (like USPS, UPS, or Amazon).

A shipment entity is defined by the following attributes: shipment_ID: Every shipment is assigned a unique identification number. pickup_date: Date that the trailer was hooked to the capacity (tractor) from the origin address. pickup_time: Time that the trailer was hooked to the capacity (tractor) from the origin address. delivery _date: Date that the trailer was delivered to the destination address. delivery_time: Time that the trailer was delivered at the destination address. billed_date: Date that the customer was billed. paid_date: Date that the customer fully paid their bill. origin_address: The address of the location where the shipment trailer is hitched to the tractor. An address is a composite attribute that is composed of address_line_one, address_line_two, city, state, zip, and country. destination_address: The address of the location where the shipment trailer is to be unloaded or unhitched from the tractor. An address is a composite attribute that is composed of address_line_one, address_line_two, city, state, zip, and country. commodity: A commodity value is a short string indicating the type of cargo in tow, such as building materials, food products, or machinery. shipment_notes: Any special notes associated with a shipment.

customers: A customer is someone who pays to have goods shipped. A customer is defined by the following attributes: cust_ID: Every customer is assigned a unique identification number. cust_name: The name of the customer. buisness_name: The name of the customers business. email: A customer may have zero or more email addresses, which are associated with an integer value called email_rank used to indicate email address contact precedence. customer_contact_numbers: A customer may have one or more phone numbers, which are also associated with a phone_type that could be either cell, work, or home. address: A customer must have one address. An address is a composite attribute that is composed of address_line_one, address_line_two, city, state, zip, and country. cust_notes: Any special notes associated with a customer.

tractors: A tractor is the vehicle which hauls the trailers and possesses the following attributes: tractor_ID: Every tractor is assigned a unique identification number for internal use. make: Truck manufacturer (i.e. Mack, Volvo, Peterbilt, etc). make_year: Truck manufacturing year. model: Truck model. license_tag: Each truck is registered with the Department of Motor Vehicles for a specific state and is issued a license plate (i.e. tag) with a unique number (could also contain letters). license_tag_state: The abbreviation for the state wherein the license plate and truck is registered (i.e. PA for Pennsylvania). tractor_DOT_number: Every truck is issued a unique identification number by the Department of Transportation that must be clearly displayed on the truck body. last_inspected: Date of last inspection. mileage: Truck mileage. mileage_date: Date of last mileage recording.

trailers: A trailer is connected to the tractor and contains the shipment. Some shipments will have two trailers connected together which are both hauled by a single tractor. A trailer possesses the following attributes: trailer_ID: Every trailer is assigned a unique identification number. make: Trailer manufacturer. make_year: Trailer manufacturing year. model: Trailer model. trailer_DOT_number: Every trailer is issued a unique identification number by the Department of Transportation that must be clearly displayed on the trailer body. trailer_length: Length of the trailer in feet (typically, 53 feet). trailer_group: A less than five character code that indicates the class of trailer (FLAT for a flatbed truck, VAN for a van, REFR for refrigerated truck, etc) last_inspected: Date of last inspection.

drivers: A driver possesses a commercial drivers license (CDL) and drives the tractor. Each shipment will have at least one driver but longer trips often require two drivers who will trade off throughout the trip. Drivers are defined by the following attributes: driver_ID: Every driver is assigned a unique identification number. driver_license_number: The commercial drivers license number of the driver. ssn: Drivers social security number. first_name: Drivers first name. last_name: Drivers last name. address: The home address of a driver. An address is a composite attribute that is composed of address_line_one, address_line_two, city, state, zip, and country. contact_number: A driver may have one phone number. email: A driver may have one email address. start_date: Original hire date of driver. hourly_rate: Drivers hourly rate of compensation.

contractors: A contractor is the owner of one or more tractors, one or more trailers, or both. A contractor possesses the following attributes: contractor_ID: Every contractor is assigned a unique identification number. first_name: Contractors first name. last_name: Contractors last name. address: The address of a contractor. An address is a composite attribute that is composed of address_line_one, address_line_two, city, state, zip, and country. email: A contractor may have zero or more email addresses, which are associated with an integer value called email_rank used to indicate email address contact precedence. contractor_contact_numbers: A contractor may have one or more phone numbers, which are also associated with a phone_type that could be either cell, work, or home.

sales_persons: sales_ID: Every salesperson is assigned a unique identification number. ssn: Salespersons social security number. first_name: Salespersons first name. last_name: Salespersons last name. address: The home address of a salesperson. An address is a composite attribute that is composed of address_line_one, address_line_two, city, state, zip, and country. SP_contact_number: A sales_person may have one phone number. email: A sales_person can have one email address. start_date: Original hire date of salesperson. salary: Salespersons annual compensation rate.

Part 2: Relationship Set Descriptions READ ALL REQUIREMENTS FIRST, THEN LOOK AT THE CREATING RELATIONSHIP SETS SECTION FOR HOW EACH REQUIREMENT SHOULD BE REPRESENTED You must create the following relationship sets based on the descriptions given below using the exact names that are shown in boldface font. Do not add additional diagramming elements. sales_persons sell shipments: A salesperson is responsible for taking a shipping order from a customer, and selling a shipment of goods. customers buy shipments: A customer interacts with a salesperson to purchase a shipment, which is an entire truckload of goods. drivers transport shipments: Drivers operate tractor trailers. A single shipment can have up to two drivers that operate in shifts. shipments use tractors: A shipment requires a truck. Call this relationship set ship_tract. shipments use trailers: A shipment requires at least one trailer, although some shipments may employ two trailers hitched in tandem to a single tractor. Call this relationship set ship_trail. contractors own tractors: A person called a contractor owns the tractor, and is paid for its use (neglecting payment information in this assignment). Call this relationship set tract_own. contractors own trailers: A person called a contractor owns the trailer, and is paid for its use (neglecting payment information in this assignment). Call this relationship set trail_own. a sales_person (called an employee) has a manager, who is another sales_person. Call this relationship set manages.

Step 2: ER Diagram Creation The second step of the database design process is called the conceptual database design phase, wherein you create an ER diagram to represent the enterprise characterized in the requirements analysis phase. Another outcome of this phase is a specification of functional requirements, however I am not requiring you to submit a list of functional requirements. You must design an ER diagram that satisfies the above list of user/data requirements. Do not add any attributes, tables, or other ER diagramming elements that have not been described in the above specification of user/data requirements. Here is a list of the ER diagramming elements that we discussed in class. Please note that elements denoted by three asterisks will not appear in this assignment: Entity Sets Attributes Simple and Composite Single-Valued & Multivalued ***On a Relationship Set Relationship Sets Unary (w/ Role Indicators) Binary ***Ternary ***N-ary ***Weak Entity Sets Mapping Cardinalities ***One-to-One One-to-Many / Many-to-One Many-to-Many Primary Key Constraints ***Participation Constraints ***Total ***Partial ***Specialization/Generalization ***Aggregation