CS 425 – Database Organization - Homework 1

Part 3.1 Modelling (Total: 60 Points)

Question 3.1.1 (60 Points) Build a conceptional model for a Cab Management System. The solution should be presented as an ER-diagram. Base your design on the following requirements.

问题 3.1.1（60 分）建立一个出租车管理系统的概念模型。 解决方案应以 ER 图的形式呈现。 根据以下要求进行设计。

• The database should record information about Customer, Cab, Ride, booking, Driver, Feedback, and Payments.

• 数据库应记录有关客户、出租车、乘车、预订、司机、反馈和付款的信息。

• A Customer has a name which consists of firstName, middleName and lastName. Customers are identified by a unique customerID. A Customer has an Address, phoneNo and email .

• 客户的姓名由firstName、middleName 和lastName 组成。 客户由唯一的 customerID 标识。 客户有一个地址、电话号码和电子邮件。

Customers have at one address and multiple customers can live in the same place.

客户拥有一个地址，多个客户可以住在同一个地方。

• An Address consists of a unique addrID, street and has streetNumber, city, state and zipcode. The attributes city and state can be derived from the attribute zipcode. – There may be some Addresses which are not be associated with any Customer or Destination

• 地址由唯一的addrID、街道和街道编号、城市、州和邮政编码组成。 属性 city 和 state 可以从属性 zipcode 派生。 – 可能有一些地址与任何客户或目的地无关

Address locations are tracked along with the address type (i.e. office, home…)

地址位置与地址类型（即办公室、家庭……）一起跟踪

• Employee is identified by an employee number. An employee can be a driver or a staff member. An employee has a name and address and birth year.

• 员工由员工编号标识。 员工可以是司机或工作人员。 员工有姓名、地址和出生年份。

The staff has a salary.

员工有工资。

A driver can available, and drives a car. Cars are associated with a customer.

一个司机可以提供，并驾驶汽车。 汽车与客户相关联。

Each of the driver’s car has number, a color capacity, and a license plate

每辆司机的车都有号码、颜色容量和车牌

Driver  may own one or multiple cars, a car is used by one driver

司机可能拥有一辆或多辆汽车，一辆汽车由一个司机使用

Customer can request a ride by sharing their departure location and destination as well as the number of rider– Customers can place any number of rides (including none)

客户可以通过分享他们的出发地点和目的地以及骑手人数来叫车——客户可以安排任意数量的行程（包括没有）

Customers may provide Feedback for each ride they have placed (optional). For every ride, a has to make a Payment.

客户可以为他们放置的每个行程提供反馈（可选）。 每次乘车，都必须付款。

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

付款由付款的预订标识。它包括支付金额和支付方式（信用卡、电子支票等）

The application should be able to track the rides date and payment paid for it

该应用程序应该能够跟踪乘车日期和支付的费用

Problem 2 (25 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). Present the results of the following intermediate steps presented in class:

问题2（25分）

采用下面的ER模型，并使用类中提供的规则将其转换为关系模式。将关系模式表示为SQL脚本）。在课堂上展示以下中间步骤的结果：

1. Translate strong entities (including the ISA relation) + unnest composite attributes转换强实体（包括ISA关系）+最不重要的复合属性
2. Translate weak entities  转换弱实体
3. Translated multi-valued attributes 转换多值属性
4. Translate relationships 转换关系