课程名称︰资料库管理 课程性质︰必修 课程教师︰杨立伟 开课学院：管理学院 开课系所︰工商管理学系科技管理组 考试日期（年月日）︰2009.11.11 考试时限（分钟）：120 是否需发放奖励金：是 （如未明确表示，则不予发放） 试题 : Part I. E-R diagram (20%) 1. List 2 conditions when a relationship should be modeled as an associative entity instead. 2. List the 4 types of cardinality constraints, and draw an example of each. 3. Give an example for each of the following: (a) Derived attribute (b) Multivalued attribute (c) Composite attribute 4. Draw an example for each of the following: (a) Ternary relationship (b) Unary relationship Part II. Relational Database and SQL (20%) 5. Define each of the following terms briefly: (a) Referential integrity (b) Correlated subquery 6. In a relational database, describe 3 types of anomalies that may arise in a table, and give an example of each. 7. Referential integrity is supported in SOL. Explain how the ON UPDATE RESTRICT, ON UPDATE CASCADE, and ON UPDATE SET NULL clauses differ. 8. In SQL, under what conditions can a UNION clause be used? Part III. Exercise 9. According to the following business rules, please draw an E-R diagram, which includes entities, relationships, and cardinalities. ‧ There are schools(学院), students(学生), and clubs(社团). ‧ Every student attends one and only one school; every school has more than one student. ‧ Every student may optionally join one club; every club has more han one student. ‧ Every club belongs to one school; some schools have more than one club, some schools have none. ‧ Any student may work as an assistants(助理) for only one school; every school may have one assistant at most. 10. Transform each of the following E-R diagrams to a relational schema that shows referential integrity constraints, and functional dependencies. If any of the relations are not in 3NF, transform those relations to 3NF.(20%) (a) Fig 3-11a ┌───────┐ │Date_Completed│ └───────┘ | ┌───────┐ | ┌───────┐ │ EMPLOYEE │Ｂ | Ａ│ COURSE │ │Employee_ID │╲ | ／│Course_ID │ │￣￣￣￣￣￣ ├──────────────────┤￣￣￣￣￣ │ │Employee_Name │／ Completes ＼│Course_Title │ │Birth_Date │ │{Topic} │ └───────┘ └───────┘ (b) Fig 3-15b ┌─────────────┐ │ EPLOYEE │ │Employee_ID │ │￣￣￣￣￣￣ │ │Employee_Name │ │{Skill (Skill_Code, │ │Skill_Title, Skill_Type)} │ └─────────────┘ 11. The following relation called GRADE Report for a university. (10%) ┌────────────────────────────────────── │GRADE REPORT ├────────────────────────────────────── │Student_ID Student_Name Campus_Address Major Course_ID Course_Title ├────────────────────────────────────── │168300458 Williams 208 Brooks IS IS 350 Database Mgt │168300458 Williams 208 Brooks IS IS 465 Systems Analysis │543291073 Baker 104 Phillips Acctg IS 350 Database Mgt │543291073 Baker 104 Phillips Acctg Acct 201 Fund Acctg │543291073 Baker 104 Phillips Acctg Mkgt 300 Intro Mktg └────────────────────────────────────── ───────────────────────┐ │ ───────────────────────┤ Instructor_Name Instructor_Location Grade │ ───────────────────────┤ Codd B 104 A │ Parsons B 317 B │ Codd B 104 C │ Miller H 310 B │ Bennett B 212 A │ ───────────────────────┘ (a) Please indicate the functional dependencies in the relation. (b) Decompose the relation into a set of 3NF relations. 12. 某电信公司徵求顾客关系管理人员1位，需熟稔顾客资料操作。顾客资料表格如下，请 以SQL进行以下7个查询动作。 (20%) 顾客 分店 Customer branch 门号 姓名 生日 居住地区 分店代号 名称 所在地区 phone_no name birthdate area branch_code name area char(10) char(10) char(8) char(8) char(2) char(10) char(8) 0935000003 张三 20001020 台北 1 台北总店 台北 0935000004 李四 20010120 台北 2 新竹店 新竹 0918000005 王五 20001120 新竹 3 高雄店 高雄 0939000006 陈六 20001220 高雄 异动 资费 action reteplan 异动代号 名称 资费代号 名称 单价(秒) action_code name rate_code name unit_price char(2) char(10) char(2) char(10) integer 1 新装 A 学生族 2 2 变更资费 B 商务人士 1 3 退装 通话记录 log_call 发话门号 受话门号 开始日期 开始时间 持续秒数 phone_no1 phone_no2 date time duration char(10) char(10) char(8) char(4) integer 0935000003 0935000004 20091121 0800 60 0935000003 0918000005 20091122 0900 120 0935000003 0939000006 20091122 1000 120 0918000005 0939000006 20091125 1300 180 0935000004 0918000005 20091127 0800 180 0939000006 0932000007 20091129 1400 120 异动记录 log_action 门号 分店代号 异动代号 资费代号 异动日期 phone_no branch_code action_code rate_code date char(10) char(2) char(2) char(2) char(8) 0935000003 1 1 A 20091005 0935000004 2 1 B 20091015 0918000005 2 1 A 20091025 0939000006 3 1 B 20091105 0935000004 1 2 A 20091115 0939000006 3 3 20091130 (a) 列出住在台北的顾客姓名 (b) 列出门号开头为0935的顾客姓名 (c) 列出各居住地区的顾客人数，由人数多至少排序 (d) 列出曾发话给2个门号以上的发话门号 (e) 列出所有曾「新装」为「学生族」资费的门号与异动日期 (f) 列出不是在居住地区「新装」门号的顾客姓名 (g) 列出曾发话给网外(不是本公司门号)的发话门号 --

※ 发信站: 批踢踢实业坊(ptt.cc) ◆ From: 140.112.247.63