#assignment #sdsc5003

原文

NOTE: The university policy on academic dishonesty and plagiarism (cheating) will be taken very seriously in this course. Everything submitted should be your own writing or coding. You must not let other students copy your work. Discussions of the assignment are okay, e.g. understanding the concepts involved. This assignment is an individual one. Upload your work as a single archive file with name A1-XXXX-YYYY.zip where XXXX is your name and YYYY is your student ID. Remember to include all files in the archive file.

Part I. ER Modelling (50 points)

Submit a diagram drawn by a drawing program (any program you like, you can use those ER diagramming tools from http://en.wikipedia.org/wiki/Entity-relationship_model), NOT hand drawn.
If you feel unsure about some of your design choices, you can add explanations.
Upload your diagram and optional explanations in a single pdf file called pdf.

Draw a single ER diagram that represents the specifications listed below.

A banking enterprise needs to store information about Customers (identified by cid, with cname attributes) and Accounts (identified by aid, with an overdraft limit amount). (8 points)
A customer is either a company with its street, city, or an individual with his/her gender, age. (10 points)
Customers own accounts. For each account that a customer owns, we want to store a start date, at which the account was opened, and a pin number that gives the customer access to the account. A customer can have multiple accounts, but an account can only be owned by one customer. (7 points)
Let us add three more entities. A loan has the following attributes: loan number, loan type, and amount. Each loan has a unique loan number. The attributes of a loan payment are date, amount and payment number. For a given loan, the payment number identifies a unique payment, but payments for different loans may share the same number. For example, payment #1 for Jack Smith identifies a unique payment in the amount of $100, but payment #1 for Jackie Chan may be a different payment in the amount of $1,000. A branch has the attributes, branch number, city, and street. Each branch has a unique branch number. (15 points)
Each loan is taken at a single unique branch. Customers borrow loans; a customer may have more than one loan, and a given loan may be associated with more than one customer (e.g., a couple may be co-signers on a mortgage). (8 points)
Presentation quality including submission file formats and diagram presentation. (2 points)

Note: If you feel that not all the details that you need to know have been completely specified, use your common sense. If in doubt, explain explicitly what assumptions you make and how they shape your model.

Part II: Creating Relational Schemas in SQL for Part I (50 points)

Please install DB Browser for SQLite on your computer and use it to run all your SQL statements. We use SQLite because it is free and probably the best lightweight database.
Submit a single SQL script that contains all your SQL statements. This should be executable in SQLite. Name the file part2.sql .
You may add optional explanations (e.g., why some constraints cannot be represented in SQL in your opinion). Please write these to another explanation_part2.pdf file.

Write SQL statements that translate your ER model for Part I into the relational model.

Create a Relational Schema. Create one table for each of the relations required to represent this banking enterprise. You may want to first translate the ER model into a relational schema, then the relational schema into SQL commands. E.g. in Chapter 3.1. the Students table is defined by “Students (sid: string, name: string, login: string, age: integer, gpa: real)”.You are not required to write down the relational schema in this format. We will not grade you on the schema, only on the SQL statements for creating the tables. We just think the schema will help you. (20 points)
Impose Integrity Constraints. Impose the constraints implied or stated, such as domains (e.g., integer for dno), key constraints, foreign key constraints, and participation constraints (using NOT NULL). Be sure to incorporate as many constraints mentioned in part I as possible. If there is a constraint mentioned in part I that you cannot capture in SQL statements, describe what the constraint is and explain why it cannot be captured. (25 points)
Presentation Quality. (5 points)

注意：大学关于学术不端和抄袭（作弊）的政策在本课程中将受到高度重视。所有提交内容必须是您自己的写作或代码。您不得让其他学生复制您的作品。讨论作业是可以的，例如理解相关概念。本作业为个人作业。请将您的作品打包为一个名为 A1-XXXX-YYYY.zip 的压缩文件上传，其中 XXXX 是您的姓名，YYYY 是您的学号。请确保压缩文件中包含所有文件。

第一部分：ER建模（50分）

提交由绘图程序绘制的图表（可使用任何您喜欢的程序，例如来自 实体关系模型工具 的 ER 绘图工具），不接受手绘图。如果您对某些设计选择不确定，可以添加解释说明。将您的图表和可选解释上传到一个名为 pdf 的 PDF 文件中。

绘制一个单一的 ER 图来表示以下规格说明：

  • 一家银行企业需要存储客户（由 cid 标识，属性包括 cname）和账户（由 aid 标识，属性包括透支限额金额）的信息。（8分）

  • 客户可以是公司（属性包括街道、城市）或个人（属性包括性别、年龄）。（10分）

  • 客户拥有账户。对于每个客户拥有的账户，我们需要存储开户日期（账户开通日期）和 PIN 码（客户访问账户的密码）。一个客户可以有多个账户，但一个账户只能由一个客户拥有。（7分）

  • 添加三个实体：贷款（属性包括贷款号、贷款类型和金额，每个贷款有唯一的贷款号）、贷款支付（属性包括日期、金额和支付号）和分行（属性包括分行号、城市和街道，每个分行有唯一的分行号）。对于给定贷款，支付号标识唯一的支付，但不同贷款的支付号可能相同（例如，Jack Smith 的支付 #1 是金额 $100 的唯一支付，而 Jackie Chan 的支付 #1 可能是金额 $1,000 的不同支付）。（15分）

  • 每个贷款在单一唯一的分行办理。客户借款贷款；一个客户可能有多个贷款，而一个贷款可能与多个客户关联（例如，一对夫妇可能共同签署抵押贷款）。（8分）

  • 提交文件格式和图表呈现质量。（2分）

注：如果您认为某些细节未完全指定，请运用常识判断。如有疑问，请明确说明您的假设及其如何影响模型。

第二部分：为第一部分创建 SQL 关系模式（50分）

请在您的计算机上安装 DB Browser for SQLite，并使用它运行所有 SQL 语句。我们使用 SQLite 是因为它是免费的，且可能是最好的轻量级数据库。提交一个包含所有 SQL 语句的单一 SQL 脚本文件。该文件应在 SQLite 中可执行。将文件命名为 part2.sql。您可以添加可选解释（例如，为什么某些约束无法在 SQL 中表示）。请将这些写入另一个名为 explanation_part2.pdf 的文件中。

  • 创建关系模式：为表示该银行企业所需的每个关系创建一个表。您可能需要先将 ER 模型转换为关系模式，再将关系模式转换为 SQL 命令。例如，在章节 3.1 中，学生表定义为 “Students (sid: string, name: string, login: string, age: integer, gpa: real)”。您无需以这种格式写出关系模式；我们仅根据创建表的 SQL 语句评分，而非模式本身。但我们认为模式会帮助您。（20分）

  • 施加完整性约束：施加隐含或声明的约束，如域（例如，dno 为整数）、键约束、外键约束和参与约束（使用 NOT NULL）。请尽可能包含第一部分中提到的约束。如果第一部分中有无法在 SQL 语句中捕获的约束，请描述该约束并解释原因。（25分）

  • 呈现质量。（5分）