INFS5710 Home Work 2 讲解 1. ER 图画法复习 (Crow’s foot notation)
ER 图: 实体关系图
Attribute 属性
数据库中的 Entity 是现实世界 中实体的“数据化”。
Copyright By PowCoder代写 加微信 powcoder
数据库中的 Attribute“依附”于 Entity 而存在，Entity 的本质 亦是由一个个 Attributes 组成。同其字面意思一样， Attributes 是 Entity 的若干特征。
Entity 可以是一个人，可以是 一家工厂，亦或是一间餐厅… 只要这个处在现实中的实体的 特征可以被总结和归纳，它就 可以成为 Entity。
若 Entity 是一个人，Attributes 便可以是他的姓名、身高、 或是其从事的职业，甚至是他的面部图像(像素矩阵);若 Entity 是一个工厂，Attributes 便可以是工厂的地址、工厂内 员工的姓名、人数、或是工厂制造的产品的数量…
*可以简单粗暴地将 Entity 理 解为数据表
*可以简单粗暴地将 Attribute 理解为数据表中的各数据列
一个数据库中的 Entities 们之间可能存在着各种各样的 Relationship (关系):
关系可以是一对多，一对一，多对多，用以下符号表示
Supertype & Subtype
第一步:定义 Entity 和他们的 primary key
• 以下是思路举例，答案不唯一，合理即可。
• Primary Key 可以用 xxxxID 或者 xxxxNo 都可以, 比如 plane_no。
• 词组建议用下划线隔开 xxx_xxx，比如 aircraft_type。下面例子中用的是比较传统的命
名法 aircraftType，请大家自行改名。
Entity: Blue
Attributes: Orange
Fly High Airlines is a leading airline organisation that provides commercial and freight flight services across the globe. They have a fleet of passenger aircraft and a fleet of commercial freight aeroplanes. Passenger and freight planes are different from each other. That is, a passenger plane will not fly for freight goods and a freight plane will not carry passengers. Each plane has a unique identifier and includes details such as weight, types of aircraft (e.g., Boeing 737 Max or Airbus 380), year of construction, year of purchase and passenger/freight capacity.
请使用自己定义的 entity 和 attributes 的名称。
• 有些 entity 的设计比较灵活，需要根据自己设定的 assumption 去设计。请尽可能使用
自己的设计方式，以免被查重。
• plane (planeID, weight, aircraftType, yearOfConstruction, yearOfPurchase, planeType) ，
-Subtypes of Plane-
• passenger_plane(planeID)
o passenger_plane_seats(planeID, classID, name, numOfSeats)
此处 passenger plane 的 seats 设计灵活，设计方式不唯一 • freight_plane(planeID, maxWeight)
此处我假设客机的 seats 和货机的 maxWeight 就是 plane 的 capacity
所以这里没有 capacity 这个 attribute。
Each flight has a unique identifier, and other attributes such as departure date and time, departure airport, arrival airport, type of flight (domestic/international and passenger/freight). Each destination has a unique identifier, and other information includes its name, country, population, whether a Covid Vaccine is required for passengers. [Note: you can assume there is no stopover for flight.] Passenger flight includes total number of seats, and number of seats for each of the classes (such as economy, premium economy, business and first), whereas freight flight requires a maximum take-off weight.
• flight (flightID, planeID, dapartureDate, departureTime, departueAirport, arrivalAirport, flightType)
• location(locationID, name, country, population, ifVaccineRequired)
此处可用 locaiton 代替 desptination location 和 departure location，也是比较灵活的。
此处假设 flight 的座位和承重和每个 plane 一样
这样假设时，可以不用再定义 subtypes: passenger_flight 和 freight_flight。Feel free 用不同的假设。
Customer details includes unique identifier, name, address, contact number, date of birth, nationality, which flight they are booking for, name of emergency contact. A customer may have booked several flights in the past or may have booked for several flights in future. The fare each customer pays for each flight booking is also stored in this database. Food and drinks are served during the passenger flight but the customer must order before flight (due to Covid-19). The details of the orders include product number, product name, product category, and price.
• customer (customerID, name, address, contactNumber, DOB, nationality, emergencyContact)
• passengerBooking (bookingID, customerID, flightID, date, fare)
Order 的处理也有多种设计方式, 这里我假设了一个 booking 中乘客可以有多个 food orders
• order(orderID, bookingID)
• orderList(orderListID, productNo, quantity, orderID)
• products(productNo, name, category, price)
For freight services, customers are organisations. So, for each freight booking, you need the details of the organisation name, weight to be carried for each booking, type of shipping (ordinary/express/next day delivery etc), departure location, destination location, etc.
• organisation (organisationID, name)
• freightBooking(bookingID, organisaitonID, weight, shippingType, date, fare, departureLocationID, destinationLocationID)
第二步: 定义 Entity 之间的 Relationship
1. 找到 Relationship
2. 定义 Foreign Key
3. 定义 Relationship Constraints (一对一，一对多，多对多)
4. 处理 super type/sub type
• plane (planeID, weight, aircraftType, yearOfConstruction, yearOfPurchase, planeType) -Subtypes of Plane-
• passenger_plane(planeID)
o passenger_plane_seats(planeID, classID, name, numOfSeats)
• freight_plane(planeID, maxWeight)
• flight (flightID, planeID, dapartureDate, departureTime, departueAirport, arrivalAirport, flightType)
• location(locationID, name, country, population, ifVaccineRequired)
• customer (customerID, name, address, contactNumber, DOB, nationality, emergencyContact)
• passengerBooking (bookingID, customerID, flightID, date, fare)
o order(orderID,bookingID)
o orderList(orderListID,productNo,quantity,orderID) o products(productNo,name,category,price)
• organisation (organisationID, name)
• freightBooking(bookingID, organisaitonID, weight, shippingType, date, fare, departureLocationID, destinationLocationID)
第三步: 根据定义的 Entity 画 ER 图 画图用的工具比较自由，可以用软件 Microsoft Visio。
这里可以用各种线上制图工具, 比如 https://app.diagrams.net/。 其实定义好 Entity 和 Relationship 之后，画图是比较简单的部分。
作业建议 ER 图画在一页 A3 page 上,。
假设用 A4 写，12 号字，也不能超过过一页。
可以跟参考教科书的图例(比如下图):
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com