VITEC Database Systems Engineer Examination (Afternoon, Part 2)

Chia sẻ: Tran Le Kim Yen Tran Le Kim Yen | Ngày: | Loại File: PDF | Số trang:32

Thêm vào BST

Báo xấu

56
lượt xem 4
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

1. Examination Time-14:30-16:30 (120 minutes). 2. Questions must be answered in accordance with the following: Question Nos. Question Selection Q1-Q2 Select one of the two.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: VITEC Database Systems Engineer Examination (Afternoon, Part 2)

October, 2005 VITEC Database Systems Engineer Examination (Afternoon, Part 2) 1. Examination Time-14:30-16:30 (120 minutes). 2. Questions must be answered in accordance with the following: Q1-Q2 Question Nos. Select one of the two. Question Selection 3. Mark your examinee information and test answers in accordance with the instructions below. (1) In the space provided on the answer sheet, write your examinee number. If this item is not marked correctly, your test cannot be scored. (2) In the space provided on the answer sheet, write your date of birth exactly as they are printed on your examination admission card. It this item is not marked correctly, your test cannot be scored. (3) In the question selection column, circle the number of the question you select to answer. If the question is not circled correctly, your test cannot be scored. If you circle two numbers, only the first question will be graded. (4) Write each answer in the space specified for that question. (5) Write your answers clearly and neatly. Answers that are difficult to read will receive a lower score. 4. After the test, you may take this question booklet home with you. 5. Observe the rules for describing conceptual data models, relation schemas, and relational database tables provided at the beginning of the booklet. Do not open the exam booklet until instructed to do so. Inquiries about the exam questions will not be answered.
Company names and product names appearing in the test questions are trademarks or registered trademarks of their respective companies. Note that the ® and ™ symbols are not used within.
Notation Used in the Questions The notation for conceptual data models, relation schemas, and relational database table structures is given below. This notation applies unless otherwise noted in the text of a question. 1. Notation for Conceptual Data Models 1 to 1 Entity type name Entity type name 1 to many Entity type name Entity type name Many to many Entity type name Entity type name Fig. 1 Notation for Entity Types and Relationships (1) Entity types are indicated using rectangles. (2) The entity type name is written inside the rectangle. (3) The relationship between entity types is indicated using a line. (4) For a “1-to-1 relationship,” neither end of the line is an arrow. For a “1-to-many relationship,” one end of the line is an arrow. For a “many-to-many relationship,” both ends of the line are arrows. Supertype name Subtype name Subtype name Fig. 2 Notation for Supertypes and Subtypes (5) When representing supertypes and subtypes, lines are drawn between the supertype and the subtypes, and a “ ” is used at the branch point. i
entity type name attribute name 1, attribute name 2, ⋅⋅⋅ ⋅⋅⋅, attribute name n Fig. 3 Notation for the Attributes of Entity Types (6) When representing the attributes of an entity type, the rectangle is divided into two sections, upper and lower. The entity name is written in the upper section, while the attribute names are listed in the lower section. (7) When representing a primary key, a solid underline is used for the attribute name or group of attribute names that make up the primary key. (8) When representing a foreign key, a dotted underline is used for the attribute name or group of attribute names that make up the foreign key. Note, however, that a dotted underline is not used when some of the attributes that make up the primary key are used to make up the foreign key. 2. Notation for Relation Schemas relation name (attribute name 1, attribute name 2, ⋅⋅⋅, attribute name n) Fig. 4 Notation for Relation Schemas (1) A relation is represented by a relation name and a list of attribute names surrounded by parentheses to the right of the relation name. This is called a relation schema. (2) When representing a primary key, a solid underline is used for the attribute name or group of attribute names that make up the primary key. (3) When representing a foreign key, a dotted underline is used for the attribute name or group of attribute names that make up the foreign key. Note, however, that a dotted underline is not used when some of the attributes that make up the primary key are used to make up the foreign key. ii
3. Notation for Relational Database Table Structures Table Name 1 Column name 1 Column name 2 Column name 3 Column name 4 Column name 5 Table name 2 Fig. 5 Notation for Table Structures, Primary Keys, Foreign Keys and Reference Relationships (1) A table name is entered followed underneath by the column names that make up the table. Each column name is written inside a rectangle. (2) When representing a primary key, a solid underline is used for the column name or group of column names that make up the primary key. (3) When representing a foreign key, a dotted underline is used for the column name or group of column names that make up the foreign key. Note, however, that a dotted underline is not used when some of the attributes that make up the primary key are used to make up the foreign key. (4) When representing a table to be referenced by the foreign key, a line is drawn either above or below the column name or group of column names that make up the foreign key. A rectangle is drawn at the end and the name of the table to be referenced is entered inside. The end of the line on the foreign key side is an arrow. iii
Q1. Read the following description of a distribution system, and then answer the Subquestions 1 through 3. Company Y manufactures and sells all kinds of office furniture including desks and cabinets. These products are sold across the country through dealers such as stationery stores, furniture stores and others. An issue is how to deal with large variations in shipping frequency and volume. Work is therefore underway to improve distribution operations and to build an efficient distribution system. [Distribution Structure of Company Y] The distribution structure of Company Y is shown in Figure 1. This kind of distribution structure is generally called a “hub-and-spoke”. “Hub” refers to shipment centers and transshipment centers, while “spoke” refers to the delivery of products to destinations in the vicinity. Legend Shipment center Transshipment center Destination Trunk route Branch route Fig. 1 Company Y’s Distribution Structure The shipment centers and transshipment centers are collectively called “distribution centers”. The thick arrow routes between distribution centers are “trunk routes”, while the thin arrow routes between a distribution center and destinations are “branch routes.” Often, a customer’s store or warehouse is specified as the destination, but sometimes delivery is made directly to a relocated office or a newly opened office. [Overview of Distribution Centers] • A distribution center may have two functions: to “ship” products from inventory and to “transfer” products from trucks that travel the trunk routes to trucks that travel the branch routes. • A shipment center has both shipping and transfer functions, while a transshipment center has only a transfer function. -1-
• A given product is always stored in a designated shipment center. • There are 6 shipment centers across the country. There are 1 or 2 transshipment centers per district, a total of 60 throughout the country. [Relationships between Order Received and Shipment and between Shipment and Delivery] • An “order received” refers to a single order from a customer. It is a unit to be delivered to a specific destination on a specific day, and may contain multiple products. • A shipment is a received order’s subset consisting of products that are stored at a shipment center. • Products to be delivered to a destination are consolidated at the nearest distribution center before their delivery. • Figure 2 shows products example of relations between an order received and shipments. In this example, five products ordered in AAA-city are shipped from the BBB-city and CCC-city shipment centers and assembled into one shipment at the DDD-city transshipment center. (Transfer at DDD-city and delivery) (Shipment at BBB-city) (Order received from AAA-city) Transshipment center: Shipment center: BBB Sent by: AAA-city... DDD-city No. Product Qty. No. Product Qty No. Product Qty. (Shipment at CCC-city) Shipment center: CCC No. Product Qty. Fig. 2 Example of Order Received and Shipment [Product Delivery Patterns] • Product delivery patterns at Company Y are shown in Figure 3. To simplify matters, the figure shows only the movement of the products shipped from shipment center A. The five patterns through in Fig. 3 are explained in Table 1. -2-
• There are three combinations of product movement patterns from shipment to delivery to the customer, those being + , + and . Shipment center A Table 1 Product Delivery Patterns No. Shipping origin Shipping destination Route Center Function Shipment Shipment Shipment center Trunk Transshipment Shipment Shipment Trunk Fig. 3 Product Delivery center Patterns from Shipment Shipment Shipment Destination Branch Center A Shipment Transfer Destination Branch Transshipment Transfer Destination Branch center [Shipping Route Types and Area] • Products are carried by truck. • A section between origin and destination is called a shipping “route”. There are two types: trunk and branch. • For trunk routes, the number of days required for shipping (trunk route lead time) is set in advance. • A branch route is for delivering products to destination. Delivery scheduling is organized in such a way that one or more areas can be covered in a day. The “area” used here refers to a unit area defined by Company Y. There are about 200 areas across the country. [Types of Services and of Trucks] • Product delivery by a truck traveling a shipping route is referred to as a “service”. • Services come as either trunk services that run along a trunk route or branch services that run along a branch route. • The tonnage of trucks used for trunk services is 11t, 8t or 6t, while the tonnage of trucks used for branch service is 4t or 2t. • Each type of truck has a specific loading capacity, which includes some loss due to unused space. -3-
[Truck Assignment] • Trucks are allocated for trunk and branch services either by advance assignment or by additional assignment. • Advance assignment is based on a stable volume of loads. • Additional assignment is made on a daily basis for load volumes that do not fit into advance assignments. • A service number is issued in advance for trucks that are secured by advanced assignment, whereas a new service number is issued whenever additional trucks are needed. • A load volume is calculated by multiplying the product quantity by the product volume determined for each product. • Products come in a wide variety of shapes and sizes; therefore product volume is usually determined with some allowance provided. [Daily Distribution Operations] Figure 4 shows the flow of distribution operations. These operations are represented by boxes, and the information that flows between them as well as the direction of the flow are indicated by arrows. The box drawn with the dotted line is outside of the distribution operations. Branch Trunk Shipment Order S hipment Transfer service truck service truck scheduling received assignment assignment Delivery Fig. 4 Daily Distribution Operations Flow at Company Y In daily distribution operations, shipment units are determined based on order information provided by the sales department as a part of shipment scheduling operations. All subsequent operations are conducted on the basis of the use of these units. In shipment scheduling operations, shipment information is created for each shipment unit. This information is forwarded to subsequent operations so that they can be linked and coordinated. The shipment status contained in the shipment information will be modified as subsequent operations are performed. This is expressed as an attribute “shipment status” in the shipment information. -4-
(Shipment scheduling) • Order information is organized into units containing products which can be shipped from a single shipment center. Shipment information is generated by counting backwards the lead time required to deliver the products from the established delivery time and date. • Shipment information includes the originating distribution center, shipment date, trunk route, transfer distribution center, transfer date, and branch route. (Trunk service truck assignment) • The shipment load for each trunk route is calculated from the shipment information. If necessary, additional trucks are allocated. • The trunk route service to be used in respect of each shipment information is decided, and the service number of that shipment is recorded. • At this stage, the shipment status is listed as “trunk service truck assigned”. • Products that make up a shipment are handled as one unit and not divided up into multiple delivery service. (Branch service truck assignment) • The shipping load for each branch route is calculated on the basis of the shipment information. If necessary, additional trucks are allocated. • The branch route to be used in respect of the shipment information is decided, and the service number of that shipment is recorded. • At this stage, the shipment status is listed as “branch service truck assigned”. • To the greatest extent possible, the shipment for a single order is grouped and assigned to a single branch service, but the shipment may be split into several trucks if it exceeds the maximum load of a single truck. (Shipment) • Products are shipped as determined by the trunk service truck assignment or branch service truck assignment. • If delivery is to be made directly to a destination by branch service without using trunk service, a delivery slip is printed for that specific order before the products are shipped. • When the products are shipped, the shipping date is recorded in the shipment information. • At this stage, the shipment status is listed as “shipped”. (Transfer) • Whenever a service truck arrives, the individual packages are divided into respective branch service as instructed in the shipment information. • A delivery slip is printed for each specific order before the branch service trucks depart. -5-
• The individual branch service trucks depart as soon as they are loaded with same-day-delivery packages and their respective delivery slips distributed accordingly. • When the branch service trucks depart, the transfer date is recorded in the shipment information. • At this stage, the shipment status is listed as “transferred”. (Delivery) • The delivery slip is presented to the customer upon delivery at the destination. The customer acknowledges delivery by signing the delivery slip copy. • After a branch service truck returns to the distribution center, the delivery date written on the delivery slip copy is recorded in the shipment information. • At this stage, the shipping status is listed as “delivered.” (Coordination of loading space) At the shipping center, space is secured in the warehouse by product. Once the products are taken from the warehouse and marshaled, they are loaded onto the trucks. The space allocated for marshalling looses its efficiency whenever sales trends change. Therefore, marshalling space assignment is reviewed periodically, using following indicators calculated for each shipping center on a monthly basis. • Number of shipments per product • Average quantity per shipment per product (Conceptual data model of distribution operations) Fig. 5 shows a conceptual data model of the distribution operations, and Fig. 6 shows the list of the relational schemas in the distribution operations. (Both are partially incomplete.) -6-
Distribution Truck type center Shipment center Shipping route Product Branch route Trunk route Area Order received Truck assignment Shipment Order detail Shipment detail Fig. 5 Conceptual Data Model of Distribution Operations -7-
Distribution center (Distribution center code, Distribution center name, Shipment function flag) Shipment center (Distribution center code, Distribution center name) Truck type (Truck type code, Maximum load, Current load) Shipping route (Departure distribution center code, Route No, Trunk-Branch category) Trunk route (Departure distribution center code, Route No, Arrival distribution center code, trunk lead time) Branch route (Departure distribution center code, Route No) Area (Area code, Area name, Departure distribution center code, Route No) Product (Product code, Product name, Inventory distribution center code, Product volume) Order received (Order No, Area code, Destination name, Destination address, Delivery date) Order detail (Order No, Order detail No, Product code, Order Quantity) Truck assignment (Departure distribution center code, Route No, Service No, Assignment date, Truck type code) Shipment (Shipment status, Shipment No, Order No, Shipment distribution center code, Shipment date, Trunk service departure distribution center code, Trunk service route No, Trunk service No, Trunk service assignment date, Transfer distribution center code, Transfer date, Branch service departure distribution center code, Branch service route No, Branch service No, Branch service assignment date, actual shipment date, actual transfer date, actual delivery date) Shipping detail (Shipment No, Shipment detail No, Product code, Shipment Quantity, Order No, Order spec No) Note: “Shipment function flag” is an attribute that identifies the concerned distribution center as a shipment center. Fig. 6 Relation Schema List for Distribution Operations [New operation issues] After reviewing an analysis of current distribution operations, the following improvement requests were submitted. Improvement Request -- Direct shipment from the shipment center to the delivery destination When large shipments are made to one destination like large-scale deliveries, operations would be more efficient if transfer operations were eliminated entirely as transferring loads between trucks decreases efficiency. If the load of a single shipment exceeds the maximum load of a 4t truck, it is required to assign a separate truck for direct delivery and make delivery directly to the destination. -8-
It was decided to review operational functions and comply with this request. Though direct delivery and branch service would be mixed, it was decided not to change how delivery slips are issued and presented to the customer. Improvement Request -- Divided shipping via trunk services The “trunk service truck assignment” operation does not allow loading of products, covered under a single shipment, to be split into different trucks running the trunk service. This decreases load efficiency, therefore it would be better if a single shipment can be split and loaded onto multiple trunk services. It was decided to review the conceptual data model in line with these requests. Three ideas (A, B and C) were drawn up as ways of implementing these improvements. The original conceptual data model of the targeted area of improvement is shown in Fig. 7, while the new conceptual data models for ideas A, B and C are shown respectively in Fig. 8, 9 and 10. Order received Order received Truck assignment Truck assignment Shipment Shipment Order detail Shipping detail Order detail Shipping detail Shipment detail and truck assignment support Fig 7. Conceptual Data Model for Fig 8. Conceptual Data Model for Idea A Original Idea Order received Order received Truck assignment Truck assignment Shipment Shipping Shipping detail Order detail Order detail Shipping detail Fig 9. Conceptual Data Model for Idea B Fig 10. Conceptual Data Model for Idea C -9-
Follow the rules set forth in “Notational rules on relation schemas” given at the beginning for the interpretation of the conceptual data models and relation schemas. Subquestion 1 Answer the following questions about the conceptual data model for distribution operations given in Fig. 5 and the relation schema list of distribution operations given in Fig. 6. (1) In Fig. 5, some relationships are missing. Add those relationships to complete the schema. (2) Table 2 lists attributes of entity type “shipment”. What statuses should the values for attributes other than “shipment status” be synchronized with? Place a “a” in the appropriate spot to complete the table. - 10 -
Table 2 “Shipment” Attribute List Attribute Timing for setting value Trunk Branch Shipment service service Shipped Transferred Delivered Shipment status planned truck truck assigned assigned Shipment No Order No Shipment distribution center code Shipment date Trunk service departure distribution center code Trunk service route No Trunk service No Trunk service assignment date Transfer distribution center code Transfer date Branch service departure distribution center code Branch service route No Branch service No Branch service assignment date Actual shipping date Actual transfer date Actual delivery date Subquestion 2 Answer the following questions about Improvement Request . (1) Table 3 is the same as Table 1 but with the addition of Improvement Request pattern. Table 3 Product Shipping Patterns with Direct Shipment added Shipping Shipping origin destination Shipping route No. Remarks Center Function Center Shipment Shipment center Shipment Trunk route center Transshipment Shipment center Shipment Trunk route center Shipment center Shipment Destination Branch route Shipment center Transfer Destination Branch route Transshipment center Transfer Destination Branch route Direct shipping as per Shipment center Shipment Destination – Improvement Request - 11 -
To realize Improvement Request , it was decided to add entity type “Direct shipment” to the conceptual data model in Fig. 5. Part of the resulting conceptual data model is shown in Fig. 11. Truck assignment Order received A B Shipment Direct shipment Order detail Shipment detail Fig. 11 Conceptual Data Model with “Direct shipment” added Which of the product shipping patterns generated in shipment operations would apply to (b) and (c) in Fig. 11? Choose all that apply from the pattern through given in Table 3. Also, what would be the foreign key attribute of (a) that would correspond to (A) and what would be the foreign key attribute of (b) that would correspond to (B)? (2) Show the relation schema of the added entity type “Direct shipment”. In solving this, assume that attributes common to other subtypes be on the super type side, therefore exclude them. Indicate primary keys. No need to indicate foreign keys. (3) “Shipment schedule preparation” indicated in the operational flow in Fig. 4 generates an instance of either subtypes (b), (c) in Fig. 11 direct shipment. Here, in the implementation of Improvement Request , the conditions for generating an instance are arranged in Table 4 by subtype. Fill in (C) through (E) of Table 4 with the appropriate text. - 12 -
Table 4 Instance Generation Conditions Instance generation target Condition (b) (c) Direct shipment a (C) a (D) a (E) (4) By implementing Improvement Request , a conflict occurs with delivery operations. The biggest problem in particular is that in some cases the delivery slip will not be presented to the customer even though products are delivered to him/her. Describe which order data would create this kind of case, in as few words as possible. Also, describe the factors that would cause this case to occur, in as few words as possible. Subquestion 3 A data example of an order received and shipment is given in Fig. 12. In this example when Improvement Request was applied, it became possible to load all items in shipment detail No. 1 and seven items on shipment detail No. 2 onto the previous trunk service truck assigned as service No. “AT8”. Answer the following questions about this: [Order received] [Order detail] Ordered Quantity Order detail No Product code Order No Order No [Shipment detail] [Shipment] Shipment distribution Shipment Quantity Trunk service No Order detail No Product code Order No Order No center code Fig. 12 Example Order and Shipment Data - 13 -
(1) Describe the changes in schema structure that would occur in the entity type shown in Fig. 9 if idea B were adopted, in as few words as possible. (2) Fill in the empty boxes in Fig. 13 with the instance values for the “Shipment” and “Shipment detail” in Fig. 12, assuming idea C was adopted. However, all of the boxes in Fig. 13 may not necessarily be filled. Also, add the connecting lines between instances as in Fig. 12. Fig. 13 Values if Idea C were Adopted (3) Taking into consideration loading space coordination operations, which of the ideas would be the most suitable for realizing Improvement Request ? Also, explain why you think it is the best solution, in as few words as possible. - 14 -
Q2 Read the following text about database design for a sales management system, and then answer the Subquestions 1 through 4. Company W is an apparel retailer directly operating stores across the country. The operations and systems of Company W are as follows. [Basic matters of operations] 1. Handled products Company W assigns a unique product code to each product it sells. Product specifications are determined by various elements such as pattern, texture, design, raw materials and mixed yarn content, color, and size. Products having the same specifications, with the exception of color and size, are managed as being the same product. Products have the following attributes. The product planning manager specifies these attributes when registering a product in the product master. (1) Attributes that identify product features “Pattern”, “design” and “material” are attributes that represent product features. • “Pattern” is a category that contains attributes such as plain, striped and checked. • “Design” is a category that includes style, silhouette, collar and sleeve shape, etc. • “Material” is a category determined by material quality such as cotton, wool and leather. • These attributes serve not as specifications but as categories to analyze matters such as sales trends by product features. Accordingly, multiple products may have the same pattern, design and raw material attributes. (2) Attributes that represent product classification “Major class” and “intermediate class” are attributes that identify product classification. A given product belongs to a single intermediate class and a single intermediate class belongs to a single major class. Each major class and intermediate class is assigned a unique class code. • The “major classes” are men’s wear, ladies wear, children’s wear and sportswear. • The “intermediate classes” further classify products handled in the various major classes, but the contents of each intermediate class vary according to its major class. For example, the men’s wear class is further classified into suits, shirts, pants, etc. - 15 -