数据库作业十一

数据库第十一次作业

Exercise 4.1.1

1. Design a database for a bank, including information about customers and their accounts. Information about a customer includes their name, address, phone, and Social Security number. Accounts have numbers,types (e.g., savings, checking) and balances. Also record the customer(s) who own an account. Draw the E /R diagram for this database. Be sure to include arrows where appropriate, to indicate the multiplicity of a relationship.
2.  Convert this diagram to a relational database schema.

为银行设计数据库

1、e-r图

2、数据库关系模型

User(ssNo,name,phone,address,account.number)

Account(number,balance,type,user.ssNo)

 

Exercise 4.1.9

(a)Design a database suitable for a university registrar. This database should include information about students, departments, professors, courses, which students are enrolled in which courses, which professors are teaching which courses, student grades, TA's for a course (TA's are students), which courses a department offers, and any other information you deem appropriate.

Note that this question is more free-form than the questions above, and you need to make some decisions about multiplicities of relationships, appropriate types, and even what information needs to be represented.

(b)Convert this diagram to a relational database schema.

1、Er图

2、物理模型

数据库关系模型

Student(name,email)

Take(email,number,grade)

Course(number,email,semester,section)

Teach(number,p_email)

Professor(name,p_email)

Department(p_email,number,name)

 

Exercise 4.4.2:

(a)Modify your solution to Exercise 4.1.1 as follows:

1)Change your diagram so an account can have only one customer.

2)Further change your diagram so a customer can have only one account.

3)Change your original diagram of Exercise 4.1.1 so that a customer can have a set of addresses (which are street-city-state triples) and a set of phones.

Further modify your diagram so that customers can have a set of addresses,and at each address there is a set of phones.

(b) Convert above all diagram to a relational database schema.

A、

1

2

3

4

B、物理模型

数据库关系模型

Address(state,city,street,phone.no,areacode)

Phone(no,address.areacode)

User(ssNo,state,city,street,name)

Acount(number,balance,type)

Owns(ssNo,number)