CS 425 – Database Organization - 针对Homework 1

Part 2.1 Database Design (Total: 40 Points) Question 2.1.1 Modeling (20 Points) Build a conceptional model for a Factory Management System. The solution should be presented as an ER-diagram. Base your design on the following requirements.

Part 2.1 数据库设计（总分：40 分） 问题 2.1.1 建模（20 分） 建立工厂管理系统的概念模型。 解决方案应以 ER 图的形式呈现。 根据以下要求进行设计。

• The database should record information about Clients, Factories, Addresses, Products, Orders, WareHouses, Delivery Plans, and Payments.

• 数据库应记录有关客户、工厂、地址、产品、订单、仓库、交货计划和付款的信息。

 • A Client has a name which consists of firstName, middleName and lastName. Clients are identified by a unique clientID. A Client has an Address, phoneNo and email address. A Client may be assigned to a Factory.

• 客户的名称由名字、中间名和姓氏组成。 客户端由唯一的客户端 ID 标识。 客户有地址、电话号码和电子邮件地址。 客户可能被分配到工厂。

– Clients can place any number of Orders (including none). Client may provide Feedback for each order they have placed (optional). For every order, a Client has to make a Payment.

– 客户可以下任意数量的订单（包括无订单）。 客户可以为他们所下的每个订单提供反馈（可选）。 对于每个订单，客户都必须付款。

• A Factory is identified by the combination of name and type. A Factory may have one Address. A Factory has a type, phone number and a short description.

• 工厂由名称和类型的组合标识。 一个工厂可能有一个地址。 工厂有类型、电话号码和简短描述。

• An Address consists of a unique addrID, street, streetNumber, city, state and zipcode. The attributes city and state can be derived from the attribute zipcode.

• 地址由唯一的addrID、街道、街道编号、城市、州和邮政编码组成。 属性 city 和 state 可以从属性 zipcode 派生。

– There may be some Addresses which are not be associated with any Client or Factory.

– 可能有一些地址与任何客户或工厂无关。

• A Product is identified by its name. A Product has a price and size, and a description.

• 产品由其名称标识。 产品有价格、尺寸和描述。

 – Products are stored in warehouses. For each Product stored in a Warehouse we record the number of Products in stock produced by a certain Factory (e.g., 3 Macbooks in Warehouse 1 were produced by Factory 1).

– 产品存放在仓库中。 对于存储在仓库中的每个产品，我们记录某个工厂生产的库存产品数量（例如，仓库 1 中的 3 台 Macbook 由工厂 1 生产）。

• A Warehouse is identified by its warehouseId. A Warehouse has a capacity.

• 仓库由它的warehouseId 标识。 仓库具有容量。

– A Warehouse may service multiple Factories.

– There may be one or more Warehouses for a Factory.

– 一个仓库可以为多个工厂提供服务。

– 一个工厂可能有一个或多个仓库。

• An Order is uniquely identified by the orderId. An Order is created by a Client. For each order we store a Total Amount of ordered items and a Date.

• 订单由orderId 唯一标识。 订单由客户创建。 对于每个订单，我们都会存储订购商品的总量和日期。

 – An Order is associated with one or more Products. For each Product in an Order, we have to record how many items of this product are ordered (e.g., Order 1 contains 3 Spoons and 15 Forks). Note that the Total Amount can be computed as the sum of number of items for each ordered Product.

– 一个订单与一个或多个产品相关联。 对于订单中的每个产品，我们必须记录订购了该产品的数量（例如，订单 1 包含 3 个勺子和 15 个叉子）。 请注意，总金额可以计算为每个订购产品的项目数之和。

• A Delivery Plan is uniquely identified by the deliveryId. For a Delivery Plan we record a Shipment Date and Delivery Date.

• 交付计划由deliveryId 唯一标识。 对于交货计划，我们会记录发货日期和交货日期。

– Each Order is associated with one or more Delivery Plans. Each Delivery Plan is associated with exactly one Order.

– 每个订单都与一个或多个交付计划相关联。 每个交付计划都与一个订单相关联。

– A Delivery Plan consists of multiple lineitems. A lineitems encodes the shipment of a number of items of one Product which reside in one Warehouse. For example, consider an order of 10 Spoons. One possible way to service this order is to create 2 delivery plans: the first delivery plan ships 3 Spoons from Warehouse 1 and 2 Spoons from Warehouse 2 while the second delivery plan ships 5 Spoons from Warehouse 3.

– 交付计划由多个项目组成。 lineitems 对驻留在一个仓库中的一种产品的多个项目的装运进行编码。 例如，考虑 10 个勺子的订单。 为该订单提供服务的一种可能方法是创建 2 个交付计划：第一个交付计划从仓库 1 运送 3 个勺子，从仓库 2 运送 2 个勺子，而第二个交付计划从仓库 3 运送 5 个勺子。

• A Payment is identified by the Order for which the payment was made. It consists of the amountPaid and paymentMethod (Credit Card, E-Check, etc.)

• 付款由付款的订单标识。 它由amountPaid 和paymentMethod（信用卡、电子支票等）组成

Correction Guideline

 • It is acceptable to have additional relationships that are logically meaningful (e.g., email as a weak entity of Client).

• Deviations in cardinalities are acceptable as long as they make sense.

• Slight differences in naming of entities, attributes, and relationships are acceptable too • Alternative notation as shown in class is acceptable

 1 Points for each correct entity including attributes (7 total)

 1 Point for correct Weak Entity (1 total)

 0.5 Points for each correct relationship (5 total)

0.5 Points for each correct cardinalities of a correct relationship except pays for and in stock (4 total)

1 Points for each correct cardinalities of correct in stock and pays for (2 total)

1 Points for correct derived attribute attributes (1 total)

纠正指南

•可以接受具有逻辑意义的其他关系（例如，电子邮件作为一个弱实体）

（由客户提供）。

•只要有意义，基数偏差是可以接受的。

•实体、属性和关系的命名也可以有细微差异

•课堂上显示的替代符号是可以接受的

包括属性在内的每个正确实体得1分（共7分）

纠正薄弱环节得1分（共1分）

每个正确的关系得0.5分（共5分）

除了支付和库存之外，正确关系的每一个正确基数都得0.5分（共4分）

库存和付款的正确基数各得1分（共2分）

正确的派生属性得1分（共1分）

Question 2.1.2 Translation of ER into Relational Model (20 Points)

Take the following ER-model and translate it into a relational schema using the rules presented in class. Present the relational schema as an SQL script (assume that all attributes are of data type INT). Present the results of the following intermediate steps in this order:

  1. Translate strong entities + unnest composite attributes

2. Translate weak entities

3. Translated multi-valued attributes

  4. Translate relationships

问题2.1.2将ER转换为关系模型（20分）

采用下面的ER模型，并使用类中提供的规则将其转换为关系模式。将关系模式表示为SQL脚本（假设所有属性都是INT数据类型）。按以下顺序展示以下中间步骤的结果：

1.转换强实体+最不重要的复合属性

2.翻译弱实体

3.翻译的多值属性

4.翻译人际关系

Solution

1st Step (strong entities)

CREATE TABLE A (

   a2 INT ,

   a3 INT ,

   PRIMARY KEY (a2 , a3 )

);

CREATE TABLE B (

  b1 INT ,

  b2 INT ,

  PRIMARY KEY ( b1 , b2 )

 );

CREATE TABLE C (

   c2 INT PRIMARY KEY

 );

CREATE TABLE E (

  e1 INT PRIMARY KEY , e2 INT

);

2nd Step (weak entities)

CREATE TABLE A (

  a2 INT ,

  a3 INT ,

  PRIMARY KEY (a2 , a3 )

 );

CREATE TABLE B (

   b1 INT ,

   b2 INT ,

  PRIMARY KEY ( b1 , b2 )

);

CREATE TABLE C (

  c2 INT PRIMARY KEY

);

CREATE TABLE D (

  a2 INT ,

  a3 INT ,

  d1 INT ,

  PRIMARY KEY ( a2 , a3 , d1 ) ,

  FOREIGN KEY ( a2 , a3 ) REFERENCES A

 );

CREATE TABLE E (

  e1 INT PRIMARY KEY ,

   e2 INT

 );