diff --git a/BDL/BDL.xml b/BDL/BDL.xml index d7cfd37..67c2928 100644 --- a/BDL/BDL.xml +++ b/BDL/BDL.xml @@ -6,12 +6,12 @@ System specification and details -

Better Digital, Ltd. (BDL) is a large U.S. company that produces a wide range of consumer electronics devices such as radios, stereo components, digital clocks, universal remotes, etc., which they sell in bulk to various retailers. The company has branches in several major U.S. cities, and employs about 9000 staff across six departments: Central Management, Sales Marketing, Personnel, Manufacturing, Inventory and Accounts.

+

Better Digital, Ltd. (BDL) is a large U.S.company that produces a wide range of consumer electronics devices such as radios, stereo components, digital clocks, universal remotes, etc., which they sell in bulk to various retailers. The company has branches in several major U.S. cities, and employs about 9000 staff across six departments: Central Management, Sales Marketing, Personnel, Manufacturing, Inventory, and Accounts.

-

BDL are currently designing and implementing a new corporate database that will be located at the company headquarters in Los Angeles. The requirements analysis phase of the project is complete, and you have been brought in as a lead database developer. It will be your task to implement an initial prototype of the specification resulting from the requirements analysis phase. An ERD of the proposed database is shown in , and more detailed specifications of the database requirements may be found in the following sections.

+

BDL are currently designing and implementing a new corporate database to be located at the company headquarters in Los Angeles. The requirements analysis phase of the project is complete, and you’ve been brought in as a lead database developer. It’ll be your task to implement an initial prototype of the specification resulting from the requirements analysis phase. An ERD of the proposed database is shown in , and more detailed specifications of the database requirements may be found in the following sections.

-
+
ERD of Better Digital’s database (Information Engineering notation) ERD of Better Digital’s database (Information Engineering notation) @@ -88,14 +88,14 @@ - As noted above, BDL has about 9000 employees. The usual details such as name, address and phone number will be recorded. The phone number must cater for both (U.S.) landline and mobile numbers. + As noted above, BDL has about 9000 employees. The usual details such as name, address, and phone number will be recorded. The phone number must cater for both (U.S.) landline and mobile numbers. - There are six possible departments, as follows: Central Management, Sales Marketing, Personnel, Manufacturing, Inventory and Accounts. + There are six possible departments, as follows: Central Management, Sales Marketing, Personnel, Manufacturing, Inventory, and Accounts. There are twenty possible employee positions, as - follows: CEO, CTO, CFO, CIO, Director, President, Vice-President, Manager, Personal Assistant, Secretary, Technician, Researcher, Designer, Assembler, Programmer, Contractor, Sales Representative, Accountant, Inventory and Assistant. + follows: CEO, CTO, CFO, CIO, Director, President, Vice-President, Manager, Personal Assistant, Secretary, Technician, Researcher, Designer, Assembler, Programmer, Contractor, Sales Representative, Accountant, Inventory, and Assistant. - Salary values are monthly. + Salary values are monthly, and are in U.S. dollars. @@ -123,7 +123,7 @@ 'Inventory', 'Assistant' ) ), Salary NUMBER(7,2) NOT NULL CONSTRAINT Staff_Salary_Range_Invalid - CHECK ( Salary BETWEEN 1000 and 99999.99 ), + CHECK ( Salary BETWEEN 1000 AND 99999.99 ), Comments VARCHAR2(4000), -- CONSTRAINT STAFF_PK PRIMARY KEY ( Staff_ID ) @@ -198,7 +198,7 @@ -

BDL’s customers are retail outlets of various sizes. BDL currently has about 400000 customers on its books, increasing by about 10000 per year. The usual details will be recorded for each customer: name, address, phone and optionally the name of a contact person and an email address. The phone number must cater for full international numbers, as many of BDL’s customers are based outside the U.S.

+

BDL’s customers are retail outlets of various sizes. BDL currently has about 400000 customers on its books, increasing by about 10000 per year. The usual details will be recorded for each customer: name, address, phone, and optionally the name of a contact person and an email address. The phone number must cater for full international numbers, as many of BDL’s customers are based outside the U.S.

@@ -279,7 +279,7 @@ -

BDL orders components from a small group of trusted suppliers (of which there are currently thirty-three). Many of these suppliers overlap in the components that they supply, thus enabling BDL to take advantage of special deals, and providing alternative sources for components. BDL normally buys components from the supplier offering the best deal at the time of ordering (pricing data will not be stored in the database because they change too frequently). The details to be recorded for suppliers are identical to those for customers.

+

BDL orders components from a small group (currently thirty-three) of trusted suppliers. Many of these suppliers overlap in the components that they supply, thus enabling BDL to take advantage of special deals, and providing alternative sources for components. BDL normally buys components from the supplier offering the best deal at the time of ordering (pricing data won’t be stored in the database because they change too frequently). The details to be recorded for suppliers are identical to those for customers.

@@ -368,14 +368,14 @@ BDL currently manufactures 16492 different products, which are assembled from various components (resistors, capacitors, controller chips, power supplies, cases, etc.). BDL can manufacture products on demand in response to a sale, but they also produce an ongoing supply of the most popular products. These are stored in a warehouse and used to fulfil sales. - Popular products have a restock and a minimum stock level. If the stock count for a product falls below the minimum level, an alert is raised by the inventory management application to Inventory staff, who then request Manufacturing to make more of the product. Manufacturing then produce enough units to bring the number in stock back up to at least the restock level (the smallest possible production run is fifty units). The minimum level must therefore be smaller than the restock level; it may be zero. + Popular products have a restock and a minimum stock level. If the stock count for a product falls below the minimum level, the inventory management application automatically raises an alert to Inventory staff, who then request Manufacturing to make more of the product. Manufacturing then produce enough units to bring the number in stock back up to at least the restock level (the smallest possible production run is fifty units). The minimum level must therefore be smaller than the restock level; it may be zero. - The database will contain the assembly instructions for each product, where applicable. The Assembly_Manual column will contain a PDF detailing the assembly procedure for each product in human-readable form. These PDFs will average about 150 KB each. The Assembly_Program column will contain a compiled Java class file that can be downloaded from the database and used to control one of BDL’s fifty automated assembly lines. These files will average about 50 KB each. + The database will contain the assembly instructions for each product, where applicable. The Assembly_Manual column will contain a PDF detailing the assembly procedure for each product in human-readable form. These PDFs will average about 150kB each. The Assembly_Program column will contain a compiled Java class file that can be downloaded from the database and used to control one of BDL’s fifty automated assembly lines. These files will average about 50kB each. + + Prices are in U.S.dollars. -

BDL orders components from a small group of trusted suppliers (of which there are currently thirty-three). Many of these suppliers overlap in the components that they supply, thus enabling BDL to take advantage of special deals, and providing alternative sources for components. BDL normally buys components from the supplier offering the best deal at the time of ordering (pricing data will not be stored in the database because they change too frequently). The details to be recorded for suppliers are identical to those for customers.

- CREATE TABLE Product @@ -455,7 +455,7 @@ The same component may be used in many different products, and multiple times in the same product. - The same component is often provided by more than one supplier. For example, 35 ohm resistor might be available from thirteen different suppliers, at varying prices (pricing data will not be stored in the database because they change too frequently). Although it leads to duplication of component descriptions, BDL have decided for practical reasons to individually store details of each component from each supplier (so in the example above, 35 ohm resistor would appear thirteen times). + The same component is often provided by more than one supplier. For example, 35 ohm resistor might be available from thirteen different suppliers, at varying prices (pricing data won’t be stored in the database because they change too frequently). Although it leads to duplication of component descriptions, BDL have decided for practical reasons to individually store details of each component from each supplier (so the 35 ohm resistor above would appear thirteen times). Since BDL has no control over how suppliers identify their components, different suppliers may sometimes use the same component codes for different components. For example, Western Electronics may use the code BC882719 for 35 ohm resistors, whereas Eastern Electrical Supplies may use the same code for large red LEDs. @@ -504,7 +504,7 @@ * Quantity - Number of units of this component used, 1–9999 + Number of units used of this component, 1–9999 @@ -512,7 +512,7 @@ - Each product can be made from at least two up to hundreds of distinct components. Some components (e.g., transistors) occur many times in the same product. + Each product can be made from at least two up to hundreds of distinct components. Some components (e.g., transistors or LEDs) occur many times in the same product. @@ -619,7 +619,7 @@ On a typical working day, BDL makes about seven thousand sales. Each sale is typically for some quantity (possibly several thousand) of each of about seven to ten different products. For example, a customer might buy twenty-three remote garage door openers and two burglar alarm systems in the same sale. - Products may sell for a different (usually lower) price from that listed (e.g., bulk discounts, cash payments, etc.). + Products may sell for a different (usually lower) price from that listed (e.g., bulk discounts, cash payments, etc.). Prices are in U.S. dollars. The status of a sale must be one of the following: pending, in progress, cancelled, backordered or shipped. @@ -708,6 +708,11 @@ Date that final shipment is due (after Order_Date) + o + Date_Completed + Date that final shipment is received (after Order_Date) + + * Status Current status of order (see below) @@ -761,9 +766,9 @@ BDL places an average of about 320 orders to suppliers per working day. Each order is typically for some quantity (possibly tens of thousands) of each of about fifteen to twenty different components. - Since pricing data will not be stored with the actual component data, the unit prices paid for components must be recorded on the order. Prices are in the range 0.009999.99. + While component pricing data won’t be stored in the database, the unit prices paid for components must still be recorded on the order. Prices are in the range 0.009999.99, and are in U.S. dollars. - Components arrive in shipments from a supplier, which may or may not correspond to the original order (for example, a single shipment might include components from several different orders to that supplier). When a shipment arrives, the Inventory staff need to note on the original order how many of each component they have received, so that they can determine when the order is complete (this happens when the number of components received is equal to the number of components ordered). This will also enable them to tell when they have received more components than they originally ordered. + Components arrive in shipments from a supplier, which may or may not correspond to the original order (for example, a single shipment might include components from several different orders to that supplier). When a shipment arrives, the Inventory staff need to note on the original order how many of each component they have received, so that they can determine when the order is complete. An order is complete when the number of components received is at least equal to the number of components ordered. This will also enable them to tell when they have received more components than they originally ordered. The status of an order must be one of the following: in progress or complete. @@ -772,15 +777,16 @@ CREATE TABLE Order_Head -( Order_Num NUMBER(10), - Order_Date DATE NOT NULL, - Due_Date DATE, - Status VARCHAR2(11) NOT NULL +( Order_Num NUMBER(10), + Order_Date DATE NOT NULL, + Due_Date DATE NOT NULL, + Date_Completed DATE, + Status VARCHAR2(11) NOT NULL CONSTRAINT Order_Head_Status_Invalid CHECK ( Status IN ( 'complete', 'in progress' ) ), - Staff_ID NUMBER(7) NOT NULL, - Supplier_ID NUMBER(7) NOT NULL, - Comments VARCHAR2(4000), + Staff_ID NUMBER(7) NOT NULL, + Supplier_ID NUMBER(7) NOT NULL, + Comments VARCHAR2(4000), -- CONSTRAINT Order_Head_Due_Date_Invalid CHECK ( Due_Date > Order_Date ), --