TUR05 CISC3000 Tutorial 05

Topic: ER Diagram

Question 01

Construct an E-R diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.
Each insurance policy covers one or more cars, and has one or more premium payments associated with it.
Each payment is for a particular period of time, and has an associated due date, and the date when the payment was received.

Answer: :::danger One Possible E-R diagram is shown in Figure 6.101.
Payments are modeled as weak entities since they are related to a specific policy.
Note that the participation of accident in the relationship participated is not total,
since it is possible that there is an accident report where the participating car is unkown.

::: ```sql insurance company:

car (car_id, accident, foreign customer_id) customer (customer_id, car_id) policy (policy_id ,car, premium_amount) premium (premium_amount, time, date_due, date_received, policy_id)

create table customer ( customer_id )

<a name="KzWy0"></a>
## Question 02
Explain the distinctions 區別 among the terms primary key, candidate key, and superkey.
Answer:
:::danger
A superkey is a set of one or more attributes that, taken collectively, allows us to identify uniquely an entity in the entity set. A superkey may contain extraneous attributes. If K is a superkey, then so is any superset of K.
A superkey for which no proper subset is also a superkey is called a candidate key.
It is possible that several distinct sets of attributes could serve as candidate keys.
The primary key is one of the candidate keys that is chosen by the database designer as the principal means of identifying entities within an entity set.
:::
![](https://cdn.nlark.com/yuque/0/2020/svg/456757/1590723492305-648940b4-5ee2-48e2-8272-91f197042b89.svg)
Candidate Keys 候選鍵：<br />One or any attributes that can uniquely identify a record. It has unique one and minimum one.
Primary Keys 主鍵：<br />From Candidate Keys find one specific one key that can be representative most. It is also for sort.
Superkey 超鍵：<br />Any candidate key with any attributes in the table.
```sql
For example:学生资料表
(student_id, student_no, student_name, student_depid)
其中: 
student_id表示学生身分证字号
student_no表示学生学号
student_name表示学生姓名
student_depid表示学生的科系代号
则:
Candidate Key : 
就可以是 {student_id} 或者 {student_no}.
Primary Key :  
从Candidate Key挑选一个，至于挑选哪一个，就看你的系统特性.
Alternate(Secondary ) Key : 
就是没被挑中当成Primary Key的其他Candidate Key，
例如，如果挑选 {student_id}当成主键，Alternate(Secondary ) Key 就是{student_no}
Super key:  
就可以是 {student_id}、{student_no}、 {student_id, student_no}、{student_id, student_name}、
{student_id, student_depid} ... 等等，都符合唯一性的条件。
若存在科系资料表depentment (depid, dep_name)，
而且depid当成科系资料表的主键，学生资料表的 student_depid就是Foreign Key。

Question 03

Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors.
Associate with each patient a log of the various tests and examinations conducted.

Answer:

:::danger It shows a simple E-R diagram for the hospital in the below figure.
Although the diagram meets the specifications of the question, a real world hospital would have many more requirements, such as tracking patient admissions and visits, including which doctor sees a patient on each visit, recording results of tests in a more structured manner, and so on.

:::