Read the following:
- Case Study 2 – HBP “Mudra Communications”
- MSAD Ch. 9 “Designing Databases”
- ISACA “Auditors and Large Software Projects, Part 2”
Case Study Assignment (Answers due Saturday March 9 11:59 AM, Comments due Monday March 11 11:59 AM)
Answer the 3 Case Study 2 questions found within the posts for Unit 08: Database Design under WEEKLY DISCUSSIONS on the class website. For participation credit – be sure to also post 3 comments discussing your classmates’ answers to the questions with them.
Unit #8 Assignment (Due Sunday March 10 11:59 PM)
In addition to answering and commenting on the case study questions you are also required to complete the Unit Assignment described below and upload a PDF file to Canvas for Unit#8. Instructions are provided below, and the instructions can also be found in the Unit Assignment post directly under the Case Study 2 questions.
Instructions: In this assignment you will leverage what you have learned about data requirements modeling and database design from MSAD’s Chapters 8 & 9. In this assignment you are provided a scenario describing the business process of a fictional package delivery service (a courier company) called On the Spot Courier Services.
Study the scenario and then using Visio’s Crow’s Foot Database Notation Shapes create an entity-relationship database model that shows the relationship between the various data entities that should be included in the package tracking and delivery system. Your diagram should include at least 6 data entities and show the relationships among them using the crows foot notation.
Use the noun technique and identify all the entities and their attributes that are important for this system.
After you identified the entities determine what the relationships should be among the entities. Add multiplicity constraints, being especially cognizant of zero-to-many versus one-to-many differences.
Finalize the database model by including all the necessary tables, attributes, primary keys, relationships, and multiplicity constraints.
By Sunday March 10 11:59 PM submit your Visio diagram to Unit#8 Assignment in Canvas as a PDF formatted file with the following naming convention of Unit8_YourName_DatabaseModel.pdf.
Scenario:
When Bill got an order, at first, only on his phone, he recorded when he received the call and when the shipment would be ready for pickup. Sometimes, customers wanted immediate pickup; sometimes, they were calling to schedule a later time in the day for pickup.
Once he arrived at the pickup location, Bill collected the packages. It was not uncommon for the customer to have several packages for delivery. In addition to the name and address of the delivery location, he also recorded the time of pickup. He noted the desired delivery time, the location of the delivery, and the weight of the package to determine the courier cost. When he picked up the package, he printed out a label with his portable printer that he kept in the delivery van.
At first, Bill required customers to pay at the time of pickup, but he soon discovered that there were some regular customers who preferred to receive a monthly bill for all their shipments. He wanted to be able to accommodate those customers. Bills were due and payable upon receipt.
To help keep track of all the packages, Bill decided that he needed to scan each package as it was sorted in the warehouse. This would enable him to keep good control of his packages and avoid loss or delays.
The delivery of a package was fairly simple. Upon delivery, he would record information about when the delivery was made and who received it. Because some of the packages were valuable, it was necessary in those instances to have someone sign for the package.
On the Spot courier services grew and changed over the years. At first, Bill received requests for package pickups on his mobile phone, recorded that information in a log, and would then drive around to retrieve all the packages later in the day. However, he soon discovered that with another driver, it was difficult to coordinate pickups between the two of them from his van. It was not long before he reorganized his business and turned the warehouse employee into a driver. Then, he stayed in the warehouse himself, and his two employees made all the pickups and deliveries. This worked well because he could control and coordinate the pickups and deliveries better. It was also easier for him to receive pickup requests working at a desk rather than trying to do it while driving a delivery van.
As he thought about how his business was growing and the services he provided to his customers, Bill began to itemize the kinds of information he would need to maintain.
Of course, he needed to maintain information about his customers. Some of his customers were businesses; some were individuals. He needed to have basic address and contact information for every customer. Also, for his corporate customers, he needed to identify a primary contact person. It was mostly his corporate customers who wanted to receive monthly statements listing all their shipments during the month and the total cost. Bill needed to distinguish which customers paid cash and which wanted monthly statements. In fact, for those that paid monthly, he needed to keep a running account of such things as when they were last billed, when they paid, and any outstanding balances. Finally, when payments were received, either for individual shipments or from monthly invoices, he needed to record information about the payment: type of payment, date, and amount. Although this was not a sophisticated billing and payment system, Bill thought it would suffice for his needs.
Next, he started thinking about his packages and shipments. At the time that a request for a pickup came in, he needed to keep track of it as some type of delivery request or delivery order. At that point in time, Bill mostly needed to know who the customer was, where the pickup location was, and what date and time the package(s) would be ready for pickup. He also recorded the date and time that he received the order. A delivery order was considered “open” until the delivery van arrived at the pickup location and the packages were all retrieved. At that point, the delivery order was satisfied.
Once the packages were retrieved, each package needed to be uniquely identified. Bill needed to know when it was picked up and which delivery person picked it up. Other important information was the “deliver to” entity name and the address. He also needed to identify the type of delivery. Some packages were high priority, requiring same-day delivery. Others were overnight. Of course, the weight and cost were recorded so the customer could either pay or have it added to the monthly invoice.
In the courier and delivery business, one of the most important information requirements is the date and time stamp. For each package, it is important to know when it was picked up, when it arrived at the warehouse, when it went back out on the delivery run, and when it was delivered. When possible, it is also important to have names associated with each of these events.