1

KIT712 Final (Part 1)
1. Lecture 13

1.1 题型对比

以往 今年
题型 占比 题型 占比
客观题 判断题 1 分*5
填空题 2 分*5
选择题 3 分*5
30 分（25%） 判断题 1 分*5
选择题 2.5 分*14
40 分（33%）
小题 简答题 5 分*10 50 分（42%） 简答题 5 分*4 20 分（17%）
大题 大题 20 分*2 40 分（33%） 大题 15 分+45 分 60 分（50%）
总计 120 分 120 分
1.2 影响分析
⚫ 相对于去年来说，今年考试的整体结构发生了巨变
⚫ 影响最大的应该是目标分数较高的同学（DN、HD）
◼ 因为开卷考试直接导致客观题（判断、选择）和简单题的难度提高
2

◼ 不再是背就行了，需要充分理解相关概念（因为老师明确指出很难在网上查到答案）
⚫ 对于目标分数较低的同学（PP、CR），影响不大，甚至说有点好处
◼ 因为大题占比很高（ER 图的题占 45%），而 ER 图的题是能通过充分复习掌握大部分的
◼ 而且客观题的比重由 25%提到了 33%，也是比较利于目标分数低的同学的
1.3 复习方向
客观题
⚫ 复习基本概念（熟悉 lecture 的 ppt）
⚫ 复习平时 quiz 时候的客观题
⚫ 复习往年试卷的客观题（question 1-15）
小题（简答题）
⚫ 以往年试卷的小题（question 16-25）来复习相关知识点（一般不是琐碎的小点）
⚫ 复习 ppt 中相关的知识点
大题
⚫ 复习第三次 quiz 的问题：trigger、cursor 和 exemption
⚫ 复习 ER 图+ ER 转换+建表相关知识（可以参考往年试卷的 26 题）
总结
⚫ 往年试卷并不是没有参考意义（主要是 27 题参考意义不大了）
⚫ 但也不要依赖于往年试卷的固定知识点
◼ 按老师的说法，应该不会出现太相似的题目，一定不要报太大期望
⚫ 要根据往年的考法，拓展学习相关知识点
3

2. 大题 2（ER 图+ER 转换+建表）
⚫ ER 图：20 分
⚫ ER 转换：10 分
⚫ 建表：15 分
2.1 ER 图
Diagramming Conventions
⚫ An Entity is represented by a rectangle
⚫ A Relationship is represented by a line joining two entities
⚫ Attributes are written in a list next to the entity or relationship to which they belong
⚫ Identifiers are placed at the top of the list of attributes and are underlined
Diagramming Conventions – Entity
⚫ An Entity is represented by a rectangle
⚫ The name of an entity should be a noun or a noun phrase
⚫ The name of an entity is written in UPPERCASE
⚫ The name of an entity is written inside the rectangle representing the entity
Diagramming Conventions – Attributes
⚫ Attributes are written in a list next to the entity or the relationship to which they belong
⚫ The name of an attribute is usually a noun or a noun phrase – sometimes the name is an adjective
⚫ An attribute is written in Lowercase With Initial Capitals
⚫ Identifiers are placed at the top of the list of attributes and are Underlined
Example of a Entity

Diagramming Conventions – Relationship
⚫ A Relationship is represented by a line joining two entities
4

◼ All relationships are binary (or recursive)- no n-nary (eg ternary 三元) relationships
⚫ The name of a relationship should be a verb or a verb phrase
⚫ The name of a relationship is written in lowercase – preferably also in italics
⚫ The name of a relationship is near the line representing the relationship
Example of a Relationship


Diagramming Conventions – Relationship Cardinality(基数, 集的势，如果不理解这两概
念，就简单的认为 Cardinality 是描述关系的一个名词：一对一，一对多)
⚫ The cardinality of a relationship indicates the number of possible occurrences of an entity participating
in a given relationship
⚫ We will add crows feet to relationship lines to indicate cardinality
Diagramming Conventions – Crows Feet for Cardinality
⚫ Crows feet(鸡爪印) indicate that many (zero or more) instances of the entity adjacent to the crows feet
may be associated with each instance of the entity at the other end of the relationship line
⚫ An absence of crows feet indicates that zero or one instances of the entity adjacent to the absence of
crows feet may be associated with each instance of the entity at the other end of the relationship line
5

Example of Relationship Cardinality – one-to-many

Example of Relationship Cardinality – many-to-many

Diagramming Conventions – Relationship Participation
⚫ Participation indicates whether all, or only some, entity occurrences participate in a relationship
⚫ We use the | symbol on relationship lines to indicate mandatory participation
⚫ We use the O symbol on relationship lines to indicate optional participation
Another Example of Relationship Participation

Example of Relationship Participation

6

Updated Example of Relationship Participation

When to Indicate Participation
⚫ We will add O or | to our relationship lines only if the scenario specifically indicates that participation is
optional or mandatory
⚫ Some people choose to mark every end of every relationship line with either | or O
⚫ Some people choose to use only | (mandatory) symbols
ER Diagram – Example 1

Notes on ER Diagram – Example 1
⚫ ACTIVITY is a subordinate entity
(also called a weak entity)
◼ Each instance of ACTIVITY must be associated with an instance of PROJECT
◆ Note the mandatory symbol (|) near PROJECT
◼ The identifier of ACTIVITY is derived from the identifier of PROJECT
◆ The identifier of ACTIVITY is {Project-id, Activity-no}
◆ The identifier of PROJECT is {Project-id}
7

ER Diagram – Example 2

Notes on ER Diagram – Example 2
⚫ must precede is a recursive relationship
(also called a unary relationship)
◼ Each instance of TASK may be associated with many instances of TASK
⚫ is part of is a binary relationship
◼ The two entities in the relationship are TASK and PROJECT
◼ Each instance of PROJECT may be associated with many instances of TASK
Our ER Naming Conventions for (Conceptual) ER Diagrams
⚫ The name of an entity is written in UPPERCASE (also known as ALL CAPITALS)
⚫ The name of a relationship is written in lowercase – preferably also in italics
⚫ The name of an attribute is written in Lowercase With Initial Capitals
⚫ Note that each type of name is written differently
2.2 ER 转换
Relational Terminology - Candidate and Primary Keys
⚫ A candidate key is a minimal subset of attributes in a relation which uniquely identifies each tuple in
the relation
⚫ One of the candidate keys of a relation is chosen to be the primary key of the relation
◼ If there is only one candidate key
for a relation, then the candidate key automatically becomes the primary key
⚫ Each value taken by a primary key must be unique and must not be null
Relational Terminology - Foreign Keys
⚫ Foreign keys are used to link tuples to represent a relationship between the tuples
◼ foreign key usually references a primary key
(but may reference any set of attributes which has had a unique constraint applied to it)
⚫ Despite what some textbooks and websites say:
◼ The primary key referenced by a foreign key may be in the same relation as the foreign key
◼ A foreign key may be null
8


Steps in the ER to Relational Conversion Algorithm
Step 1: Each entity becomes a relation
Step 2: Each many-to-many relationship becomes a relation
Step 3: Each one-to-many relationship is represented by a foreign key
Step 4: Write out the final relational schema
Step 1. Each entity becomes a relation
⚫ Give the relation the same name as the entity
⚫ All attributes belonging to the entity become attributes of the relation
⚫ The identifier of the entity becomes the primary key of the relation
OWNER (MemberNumber, [pk] FirstName, Surname, PhoneNumber)
CAT (CatId, [pk] Name, Sex, Age, SpayedStatus)
ADDRESS (AddressId, [pk] StreetNumber, StreetName, Suburb, State, Postcode)
Step 2. Many-to Many relationship Conversion
⚫ Give the relation the same name as the relationship
⚫ The primary key of the relation is formed by concatenating the identifiers of the entities on either end
of the relationship line which represents the many-to-many relationship
⚫ Each of these identifiers becomes a foreign key
◼ The relation will have a composite primary key which consists of two foreign keys
⚫ Any attributes belonging to the relationship are added to the relation
OWNS (MemberNumber [fk1], CatId [fk2], [pk] PrimaryContact)
9

Step 3. Many-to-One relationship Conversion
⚫ For each relation formed in Step 1, if the relation was formed from an entity which is at the many end
of a relationship line which represents a one-to-many relationship, then we update the relation
⚫ The primary key of the relation formed from the entity at the one end of the relationship line becomes
a foreign key in the relation formed from the entity at the many end of the relationship line
◼ If the primary key of the relation formed from the entity at the one end of the relationship line
already exists in the relation formed from the entity at the many end of the relationship line, then
we designate (指定) it as a foreign key
◼ If the primary key of the relation formed from the entity at the one end of the relationship line does
not already exist in the relation formed from the entity at the many end of the relationship line,
then we add it as a foreign key at the end of the list of attributes for the relation formed from the
entity at the many end of the relationship line
OWNER (MemberNumber, [pk] FirstName, Surname, PhoneNumber, AddressId [fk])
Step 4. Final Relational Model
⚫ Give the final relational schema the same sas the ER diagram which is being converted
⚫ List the final version of each relation
◼ All the relations from Steps 2 and 3 will be in the final relational schema
◼ The relations from Step 1 which were not updated in Step 3 will be in the final relational schema
OWNS (MemberNumber [fk1], CatId [fk2], [pk] PrimaryContact)
OWNER (MemberNumber, [pk] FirstName, Surname, PhoneNumber, AddressId [fk])
CAT (CatId, [pk] Name, Sex, Age, SpayedStatus)
ADDRESS (AddressId, [pk] StreetNumber, StreetName, Suburb, State, Postcode)
2.3 建表
https://www.techonthenet.com/oracle/tables/create_table.php
https://www.techonthenet.com/oracle/datatypes.php

10



⚫ 我们先对上面的 case 建表练习一下

OWNS (MemberNumber [fk1], CatId [fk2], [pk] PrimaryContact)
OWNER (MemberNumber, [pk] FirstName, Surname, PhoneNumber, AddressId [fk])
CAT (CatId, [pk] Name, Sex, Age, SpayedStatus)
ADDRESS (AddressId, [pk] StreetNumber, StreetName, Suburb, State, Postcode)
⚫ 建表结果：
CREATE TABLE CAT
( CatId number(10) NOT NULL,
11

Name varchar2(50),
Sex varchar2(50),
Age number(10),
SpayedStatus varchar2(50),
CONSTRAINT CAT_pk PRIMARY KEY (CatId)
);

CREATE TABLE ADDRESS
( AddressId number(10) NOT NULL,
StreetNumber number(10),
StreetName varchar2(50),
Suburb varchar2(50),
State varchar2(50),
Postcode varchar2(50),
CONSTRAINT ADDRESS_pk PRIMARY KEY (AddressId)
);

CREATE TABLE OWNER
( MemberNumber number(10) NOT NULL,
FirstName varchar2(50),
Surname varchar2(50),
PhoneNumber number(10),
AddressId number(10) NOT NULL,
CONSTRAINT OWNER_pk PRIMARY KEY (MemberNumber),
CONSTRAINT fk1_ADDRESS
FOREIGN KEY (AddressId)
REFERENCES ADDRESS(AddressId)
);

CREATE TABLE OWNS
( MemberNumber number(10) NOT NULL,
CatId number(10) NOT NULL,
PrimaryContact varchar2(50),
CONSTRAINT OWNS_pk PRIMARY KEY (MemberNumber, CatId),
CONSTRAINT fk1_OWNER
FOREIGN KEY (MemberNumber)
REFERENCES OWNER(MemberNumber),
CONSTRAINT fk2_CAT
FOREIGN KEY (CatId)
REFERENCES CAT(CatId)
);
12


几种常见错误

◼ 一般是先建含有外键的表所致，注意建表顺序！！！
◆ 一定要先建没有外键的表，再建有外键的表

◼ 现在指定外键 reference 的表中，指定外键并不是主键
◆ 一般是 reference 的表中主键并不是当前外键所致

◼ 这种错误一般是名字打错所致，Ctrl+F 查一下错这个名字在哪里
小技巧
◼ 一般建表如果出错：名字打错等，返回去改所有表比较麻烦，可以直接清空重建

13


◼ Schema-Actions-Drop All Objects
◆ 这个操作通常会卡住，等 10 秒左右，直接刷新浏览器即可
2.4 往年考试题
2014Q26



14

⚫ Answer

⚫ 扩展
Step 1: Each entity becomes a relation
MUSICIAN (MusicianName, [pk] Other_info)
CD (CDIdentifier, [pk] CD_name, Year_released, Year_bought)
PUBLISHER (PublisherName, [pk] Country_registered, Date_created, Date_ceased)
ARTIST (ArtistCode, [pk] Artist_name, Other_facts, Artist_type)

Step 2: Each many-to-many relationship becomes a relation
PLAYON (MusicianName [fk1], CDIdentifier [fk2], [pk])

Step 3: Each one-to-many relationship is represented by a foreign key
CD (CDIdentifier, [pk] CD_name, Year_released, Year_bought, PublisherName [fk1], ArtistCode [fk2])

Step 4: Write out the final relational schema
MUSICIAN (MusicianName, [pk] Other_info)
CD (CDIdentifier, [pk] CD_name, Year_released, Year_bought, PublisherName [fk1], ArtistCode [fk2])
PUBLISHER (PublisherName, [pk] Country_registered, Date_created, Date_ceased)
ARTIST (ArtistCode, [pk] Artist_name, Other_facts, Artist_type)
PLAYON (MusicianName [fk1], CDIdentifier [fk2], [pk])

CREATE TABLE MUSICIAN
( MusicianName varchar2(50) NOT NULL,
Other_info varchar2(50),
CONSTRAINT MUSICIAN_pk PRIMARY KEY (MusicianName)
);
15


CREATE TABLE PUBLISHER
( PublisherName varchar2(50) NOT NULL,
Country_registered varchar2(50),
Date_created date,
Date_ceased date,
CONSTRAINT PUBLISHER_pk PRIMARY KEY (PublisherName)
);

CREATE TABLE ARTIST
( ArtistCode number(10) NOT NULL,
Artist_name varchar2(50),
Other_facts varchar2(50),
Artist_type varchar2(50),
CONSTRAINT ARTIST_pk PRIMARY KEY (ArtistCode)
);

CREATE TABLE CD
( CDIdentifier number(10) NOT NULL,
CD_name varchar2(50),
Year_released varchar2(50),
Year_bought varchar2(50),
PublisherName varchar2(50) NOT NULL,
ArtistCode number(10) NOT NULL,
CONSTRAINT CD_pk PRIMARY KEY (CDIdentifier),
CONSTRAINT fk1_PUBLISHER
FOREIGN KEY (PublisherName)
REFERENCES PUBLISHER(PublisherName),
CONSTRAINT fk2_ARTIST
FOREIGN KEY (ArtistCode)
REFERENCES ARTIST(ArtistCode)
);

CREATE TABLE PLAYON
( MusicianName varchar2(50) NOT NULL,
CDIdentifier number(10) NOT NULL,
CONSTRAINT PLAYON_pk PRIMARY KEY (MusicianName, CDIdentifier),
CONSTRAINT fk1_MUSICIAN
FOREIGN KEY (MusicianName)
REFERENCES MUSICIAN(MusicianName),
CONSTRAINT fk2_CD
FOREIGN KEY (CDIdentifier)
16

REFERENCES CD(CDIdentifier)
);
2015Q26

⚫ Answer
Each patient has a unique patient ID. Information of patient recorded is: surname, initials, street of residence,
city of residence, postcode, admission date, next of kin, family doctor. Each patient must take one kind of
drug, but it is not mandatory that a patient is in a ward. A patient can take many kinds of drugs. If a patient
is in a ward, they can not be in another ward.
Each drug has its own unique drug ID. Category and quantity in-stock of a drug is also recorded. Not every
drug is used by a patient. A kind of drug can be used by many patients.
Each ward has a unique ward name. Number of beds, sister in-charge, extension number are also recorded
for a ward. A ward can contain many patients.

⚫ Step 1: Each entity becomes a relation
PATIENT (Patient_id, [pk] Surname, Initials, Street_of_residence, City_of_residence, Postcode,
Admission_date, Next_of_kin, Family_doctor)
DRUG (Drug_id, [pk] Category, Qty_in_stock)
WARD (Ward_name, [pk] No_of_beds, Sister_in_charge, Extension_no)

⚫ Step 2: Each many-to-many relationship becomes a relation
TAKES (Patient_id [fk1], Drug_id [fk2], [pk])

⚫ Step 3: Each one-to-many relationship is represented by a foreign key
PATIENT (Patient_id, [pk] Surname, Initials, Street_of_residence, City_of_residence, Postcode,
Admission_date, Next_of_kin, Family_doctor, Ward_name [fk])

17

⚫ Step 4: Write out the final relational schema
PATIENT (Patient_id, [pk] Surname, Initials, Street_of_residence, City_of_residence, Postcode,
Admission_date, Next_of_kin, Family_doctor, Ward_name [fk])
DRUG (Drug_id, [pk] Category, Qty_in_stock)
WARD (Ward_name, [pk] No_of_beds, Sister_in_charge, Extension_no)
TAKES (Patient_id [fk1], Drug_id [fk2], [pk])
⚫ 扩展
CREATE TABLE DRUG
( Drug_id number(10) NOT NULL,
Category varchar2(50),
Qty_in_stock number(10),
CONSTRAINT DRUG_pk PRIMARY KEY (Drug_id)
);

CREATE TABLE WARD
( Ward_name varchar2(50) NOT NULL,
No_of_beds number(10),
Sister_in_charge varchar2(50),
Extension_no number(10),
CONSTRAINT WARD_pk PRIMARY KEY (Ward_name)
);

CREATE TABLE PATIENT
( Patient_id number(10) NOT NULL,
Surname varchar2(50),
Initials varchar2(50),
Street_of_residence varchar2(50),
City_of_residence varchar2(50),
Postcode varchar2(50),
Admission_date date,
Next_of_kin varchar2(50),
Family_doctor varchar2(50),
Ward_name varchar2(50),
CONSTRAINT PATIENT_pk PRIMARY KEY (Patient_id),
CONSTRAINT fk_WARD
FOREIGN KEY (Ward_name)
REFERENCES WARD(Ward_name)
);

CREATE TABLE TAKES
( Patient_id number(10) NOT NULL,
18

Drug_id number(10) NOT NULL,
CONSTRAINT TAKES_pk PRIMARY KEY (Patient_id, Drug_id),
CONSTRAINT fk1_PATIENT
FOREIGN KEY (Patient_id)
REFERENCES PATIENT(Patient_id),
CONSTRAINT fk2_DRUG
FOREIGN KEY (Drug_id)
REFERENCES DRUG(Drug_id)
);
2016Q26

⚫ Answer
Each task has a unique task id and an expected duration. Tasks must precede their preceding tasks with a
lead time. Each task muse be part of a project rather than many projects.
Each project has a unique project id and a budget. A project can have many tasks.

⚫ Step 1: Each entity becomes a relation
TASK (Task_id, [pk] Expected_duration)
PROJECT (Project _id, [pk] Budget)

⚫ Step 2: Each many-to-many relationship becomes a relation
MUST_PRECEDE (Task _id [fk1], Preceding _Task _id [fk2], [pk] Lead_time)

⚫ Step 3: Each one-to-many relationship is represented by a foreign key
TASK (Task_id, [pk] Expected_duration, Project_id [fk])

⚫ Step 4: Write out the final relational schema
TASK (Task_id, [pk] Expected_duration, Project_id [fk])
PROJECT (Project_id, [pk] Budget)
MUST_PRECEDE (Task_id [fk1], Preceding_Task _id [fk2], [pk] Lead_time)

⚫ 扩展
19

CREATE TABLE PROJECT
( Project_id number(10) NOT NULL,
Budget number(10),
CONSTRAINT PROJECT_pk PRIMARY KEY (Project_id)
);

CREATE TABLE TASK
( Task_id number(10) NOT NULL,
Expected_duration number(10),
Project_id number(10),
CONSTRAINT TASK_pk PRIMARY KEY (Task_id),
CONSTRAINT fk_PROJECT
FOREIGN KEY (Project_id)
REFERENCES PROJECT(Project_id)
);

CREATE TABLE MUST_PRECEDE
( Task_id number(10) NOT NULL,
Preceding_Task_id number(10) NOT NULL,
CONSTRAINT MUST_PRECEDE_pk PRIMARY KEY (Task_id, Preceding_Task_id),
CONSTRAINT fk1_TASK
FOREIGN KEY (Task_id)
REFERENCES TASK(Task_id),
CONSTRAINT fk2_TASK
FOREIGN KEY (Preceding_Task_id)
REFERENCES TASK(Task_id)
);
2017Q26
20

⚫ Answer



CREATE TABLE Quiz
( QID number(10) NOT NULL,
StartDate date,
EndDate date,
TotalMarks number(10),
MaxAttempt number(10),
QuizType varchar2(50),
CONSTRAINT Quiz_pk PRIMARY KEY (QID)
);
21


CREATE TABLE Student
( StudentID number(10) NOT NULL,
Student_details varchar2(50),
CONSTRAINT Student_pk PRIMARY KEY (StudentID)
);

CREATE TABLE Attempt
( AID number(10) NOT NULL,
StudentID number(10) NOT NULL,
Date1 date,
Marks number(10),
QID number(10) NOT NULL,
CONSTRAINT Attempt_pk PRIMARY KEY (AID),
CONSTRAINT fk1_Student
FOREIGN KEY (StudentID)
REFERENCES Student(StudentID),
CONSTRAINT fk2_Quiz
FOREIGN KEY (QID)
REFERENCES Quiz(QID)
);

CREATE TABLE Questions
( QuesID number(10) NOT NULL,
Descrip varchar2(50),
DifficultLevel varchar2(50),
QID number(10) NOT NULL,
CONSTRAINT Questions_pk PRIMARY KEY (QuesID),
CONSTRAINT fk_Quiz
FOREIGN KEY (QID)
REFERENCES Quiz(QID)
);

CREATE TABLE Attempted
( StudentID number(10) NOT NULL,
QID number(10) NOT NULL,
CONSTRAINT Attempted_pk PRIMARY KEY (StudentID, QID),
CONSTRAINT fk1_Student_ated
FOREIGN KEY (StudentID)
REFERENCES Student(StudentID),
CONSTRAINT fk2_Quiz_ated
FOREIGN KEY (QID)
22

REFERENCES Quiz(QID)
);

CREATE TABLE consists
( AID number(10) NOT NULL,
QuesID number(10) NOT NULL,
Answer varchar2(50),
CorrectWrong varchar2(50),
CONSTRAINT consists_pk PRIMARY KEY (AID, QuesID),
CONSTRAINT fk1_Attempt
FOREIGN KEY (AID)
REFERENCES Attempt(AID),
CONSTRAINT fk2_Questions
FOREIGN KEY (QuesID)
REFERENCES Questions(QuesID)
);
参考文献
kit712lecutre2_2020.pptx
ConversionAlgm_Handout_1.docx
2014.pdf
2015.pdf
2016.pdf
2017.pdf

欢迎咨询51作业君