Question: Problem 1 (60%): You are asked to design a database for Superfast Package Shipping (SPS) that satisfies the following requirements: a. SPS has several types

Problem 1 (60%): You are asked to design a database for Superfast Package Shipping (SPS) that satisfies the following requirements: a. SPS has several types of shipping service. Each type has a unique code, a rate, and a type of insurance. The rate is composite of a base rate for the first 20 ounces and an incremental rate for each additional ounce after that. b. The employee record includes social security number, name, address, phone number, date of birth, sex, and salary. Every employee works for a local office. c. Each local office has a unique number, a name, an address, a telephone number, and the types of ship- ping service available. Not all types of shipping service are available in every office. A specific employee manages a local office. d. The customer record includes a unique customer's number, a name, an address, and a telephone num- ber. Some queries access the records by the last name of the customer. Some queries access the zip code of the customer. e. Each package has a unique package number, a customer who sends the package, a customer who will receive the package, a date, the weight, the cost, a type of shipping service, the local office that accepts the package, and the employee who processes the package. f. Each package also has several tracking records. The tracking record has a sequence number that is unique for the package, the date of the record, the local office where the record is inserted, and the employee who inserts the record. Design the database using ER model. Show your design by an ER diagram
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
