Question 1: System Documentation (40 marks)
Advent Insurance Company of Newton, Auckland, processes automobile insurance policies on their in-house computer system. Customers send requests for auto insurance into the Newton sales office where sales clerks prepare policy request forms. They file a copy of the form and forward the original to the input preparation section where data entry clerks use networked PCs to key and key verify the data contained on the documents to a disk (policy requests).
Each evening, the computer retrieves the policy request data from the network, edits the data on the computer for accuracy (e.g. all required fields completed), sorts the data in policy number sequence, and prints a summary report listing the edited policy requests (there is an error routine, not described here, for those requests that do not pass the computer edits). The summary report is sent to the sales office where the sales clerks compare the report to the copy of the policy request form that they had previously filed. If everything checks out, they notify computer operations to proceed with processing. When notified, the computer processes the correct policy request data against the policyholder master data to create a new policy record. Each evening, a file which was created during the processing run is used to print premium notices that are sent to the customer.
- Draw a context diagram. (5 marks)
- Draw a physical data flow diagram (DFD). (10 marks)
- Draw a level 0 logical DFD. (10 marks)
- Draw a system flowchart. (15 marks)
Question 2: Data Organisation (45 marks)
Property Me is a property management company that employs ten staff members to deal with rental inquiries and three inspectors to conduct building inspections at the end of each month. Each building has one, two, and three-bedroom apartments, which are rented on a monthly basis to its tenants, with a 12-month lease as a standard requirement. Property Me has asked you to design its database to capture the following data elements:
- For each building: BuildingNo., Address
- For each apartment: ApartmentNo., No.of Bedrooms
- For each tenant: TenantNo., TenantName, TenantPhoneNo.
- For each staff member: StaffNo., StaffName
- For each inspector: InspectorNo., InspectorName
The following business rules apply:
- Each building has more than one apartment, which is rented to tenants. Tenants can rent more than one apartment, but there are vacant apartments.
- Each staff member manages more than one apartment, but each apartment is managed by exactly one staff.
- Each inspector inspects more than one building, and each building is inspected by one or many inspectors.
- For each building that is inspected by a particular inspector, the dates of the last inspection and the next future inspection are kept.
- Prepare an entity-relationship diagram, including all described data attributes and cardinalities based on the description. (31 marks
- Transform the following table into third normal form. Show your results for each normal form, including 0 normal form. (14 marks)
EasyDue™ 支持PayPal, AliPay, WechatPay, Taobao等各种付款方式!
E-mail: firstname.lastname@example.org 微信:easydue